jdbc连接池

1. java访问数据库

String url = "jdbc:mysql://localhost:3306/db02?serverTimezone=Asia/Shanghai";
String userName = "root";
String password = "root";
//1. 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");

//2. 获取连接对象
Connection connection = DriverManager.getConnection(url, userName, password);

//3. 准备sql语句(执行sql)
String sql = "select * from student where id>?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, 2);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
    System.out.println(resultSet.getString("name"));
}

//4. 释放资源
resultSet.close();
statement.close();
connection.close();

---

2. 以上原始方式访问数据库存在的弊端

---

---

3. 优化方案

建立一个池子,用来存放连接对象,默认给定一个池子的大小;

所以我们基于上面提出的池子技术,业内就有好多的解决方案;数据库的连接池;

数据库的连接池技术其实我们一般也称为数据库的池化技术;

连接池可以实现连接对象的复用;避免了连接对象的重复创建和销毁;

---

4. 业内的连接池技术

• Apche—>Dbcp连接池(一般不用)
• C3P0连接池(一般也不用)
• 阿里巴巴的Druid连接池
• Spring家族的hikari

连接池有个专业术语叫 数据源(DataSource)

---

5. Druid连接池技术

• 导入jar包

  

• 编写连接池工具类

  package com.xzy;
  
  import com.alibaba.druid.pool.DruidDataSource;
  
  import java.sql.Connection;
  import java.sql.SQLException;
  
  public class DataSourceUtils {
      private static final String className = "com.mysql.cj.jdbc.Driver";
      private static final String url = "jdbc:mysql://localhost:3306/db02?serverTimezone=Asia/Shanghai";
      private static final String userName = "root";
      private static final String password = "root";
      private static DruidDataSource druidDataSource;
      static {
          //创建连接池对象
          druidDataSource = new DruidDataSource();
          druidDataSource.setDriverClassName(className);
          druidDataSource.setUrl(url);
          druidDataSource.setUsername(userName);
          druidDataSource.setPassword(password);
          druidDataSource.setMaxActive(10);
      }
  
      private DataSourceUtils() {
  
      }
  
  
      public static Connection getConnection() {
          try {
              return druidDataSource.getConnection();
          } catch (SQLException e) {
              e.printStackTrace();
          }
          return null;
      }
  }

• 使用连接池获取连接对象

  //1. 获取连接对象
  Connection connection = DataSourceUtils.getConnection();
  
  //2. 准备sql语句(执行sql)
  String sql = "select * from student where id>?";
  PreparedStatement statement = connection.prepareStatement(sql);
  statement.setInt(1, 2);
  ResultSet resultSet = statement.executeQuery();
  while (resultSet.next()) {
      System.out.println(resultSet.getString("name"));
  }
  
  //3. 释放资源(connection对象的的开启和释放不应该我们手动的进行操作)
  resultSet.close();
  statement.close();

---

6. 通过配置文件直接使用Druid

• 编写druid的配置文件 db.properties

  key必须是固定的不能随便写

  driverClassName=com.mysql.cj.jdbc.Driver
  url=jdbc:mysql://localhost:3306/db02?serverTimezone=Asia/Shanghai
  username=root
  password=root
  maxActive=10

• 创建连接池

  package com.xzy;
  
  import com.alibaba.druid.pool.DruidDataSource;
  import com.alibaba.druid.pool.DruidDataSourceFactory;
  
  import java.io.IOException;
  import java.sql.Connection;
  import java.sql.SQLException;
  import java.util.Properties;
  
  public class DataSourceUtils {
  
      private static DruidDataSource druidDataSource;
  
      static {
          try {
  
              Properties properties = new Properties();
              properties.load(DruidDataSource.class.getClassLoader().getResourceAsStream("db.properties"));
              druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
          } catch (Exception e) {
              e.printStackTrace();
          }
      }
  
      private DataSourceUtils() {
  
      }
  
  
      public static Connection getConnection() {
          try {
              return druidDataSource.getConnection();
          } catch (SQLException e) {
              e.printStackTrace();
          }
          return null;
      }
  }