JDBC——三种创建数据库连接池的方式

c3p0连接池

• 获取连接的方式一

  	@Test
      //方式一
      public void test() throws Exception {
          ComboPooledDataSource cpds = new ComboPooledDataSource();
          cpds.setDriverClass( "com.mysql.cj.jdbc.Driver" );
          cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false&rewriteBatchedStatements=true" );
          cpds.setUser("root");
          cpds.setPassword("Hkx123");
  
          Connection conn = cpds.getConnection();
          System.out.println(conn);
      }
  

  上述方法将用户名、密码等信息写入在开发代码中，且不说安全性，实用性也是非常之差。我们参考jdbc连接时的方法，将数据写入一个properties文件，运行时再读取即可。

• 获取连接的方式二

  参考官方文档，让我们在src目录下创建一个c3p0-config.xml配置文件

  c3p0-config.xml

  <?xml version="1.0" encoding="utf-8" ?>
  <c3p0-config>
      <named-config name="helloc3p0">
          <!-- c3p0数据库连接池的连接设置 -->
          <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
          <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?serverTimezone=UTC&amp;characterEncoding=utf-8&amp;useSSL=false&amp;rewriteBatchedStatements=true</property>
          <property name="user">root</property>
          <property name="password">Hkx123</property>
          <!-- c3p0数据库连接池的其他设置 -->
          <!-- 当数据库连接池里的连接数不够的话，一次增加连接数的数量 -->
          <property name="acquireIncrement">5</property>
          <!-- c3p0数据库连接池默认创建时的连接数 -->
          <property name="initialPoolSize">10</property>
          <!-- c3p0数据库连接池维护的最少连接数 -->
          <property name="minPoolSize">10</property>
          <!-- c3p0数据库连接池维护的最大连接数 -->
          <property name="maxPoolSize">100</property>
          <!-- c3p0数据库连接池最多维护的Statements数量 -->
          <property name="maxStatements">50</property>
          <!-- 每个连接最多可以使用的Statements数量 -->
          <property name="maxStatementsPerConnection">5</property>
      </named-config>
  </c3p0-config>
  

  创建连接

  	@Test
      public void test1() throws SQLException {
          ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
          Connection conn = cpds.getConnection();
          System.out.println(conn);
      }
  

  我们可以继续维护一下我们的jdbcUtil工具类

  	private static ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
      public static Connection getConnectionByc3p0() throws Exception {
          Connection conn = cpds.getConnection();
          return conn;
      }
  

DBCP连接池

• 获取连接的方式一

   	@Test
      public void test1() throws SQLException {
          BasicDataSource source = new BasicDataSource();
          //配置数据
          source.setDriverClassName("com.mysql.cj.jdbc.Driver");
          source.setUrl("jdbc:mysql://localhost:3306/test?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false&rewriteBatchedStatements=true");
          source.setUsername("root");
          source.setPassword("Hkx123");
  
          Connection conn = source.getConnection();
          System.out.println(conn);
      }
  

  还是吸取上面的方法，我们尽量把一些数据写到配置文件中，而不是在代码里

• 获取连接的方式二

  1. 创建dbcp.properties配置文件，注意路径是在src下

  driverClassName=com.mysql.cj.jdbc.Driver
  url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true
  username=root
  password=Hkx123
  

  2. 读取配置文件获取连接，这里有两种方式

  	@Test
      public void test2() throws Exception {
          Properties prop = new Properties();
          //方式一：
          //InputStream is = ClassLoader.getSystemResourceAsStream("dbcp.properties");
          //方式二：
          FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
          prop.load(is);
          DataSource source = BasicDataSourceFactory.createDataSource(prop);
          Connection conn = source.getConnection();
          System.out.println(conn);
      }
  

  3. 以上方法我们发现，打开一个连接，则创建一个线程池，我们还需要维护一下JDBCutils工具类

  	//dbcp数据库连接池
      private static DataSource source = null;
      static {
          try {
              Properties prop = new Properties();
              //方式一：
              //InputStream is = ClassLoader.getSystemResourceAsStream("dbcp.properties");
              //方式二：
              FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
              prop.load(is);
              source = BasicDataSourceFactory.createDataSource(prop);
          } catch (Exception e) {
              e.printStackTrace();
          }
      }
      public static Connection getConnectionByDBCP() throws Exception {
          Connection conn = source.getConnection();
          return conn;
      }
  

Druid连接池

• 获取连接的最终方法

  1. 先创建druid.properties配置文件

  driverClassName=com.mysql.cj.jdbc.Driver
  url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false&rewriteBatchedStatements=true
  username=root
  password=Hkx123
  

  2. 读取配置文件

    	@Test
      public void test1() throws Exception {
          Properties properties = new Properties();
          InputStream is = ClassLoader.getSystemResourceAsStream("druid.properties");
          properties.load(is);
          DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
  
          Connection connection = dataSource.getConnection();
          System.out.println(connection);
      }
  

  3. 将以上代码维护进JDBCUtils工具类，方便使用

  	//druid数据库连接池
      private static DataSource dataSource = null;
      static {
          try {
              Properties properties = new Properties();
              InputStream is = ClassLoader.getSystemResourceAsStream("druid.properties");
              properties.load(is);
              dataSource = DruidDataSourceFactory.createDataSource(properties);
          } catch (Exception e) {
              e.printStackTrace();
          }
      }
      public static Connection getConnectionByDruid() throws Exception {
          Connection connection = dataSource.getConnection();
          return connection;
      }