mysql之数据连接池

数据库连接池 C3P0：

配置文件

<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <!-- 默认配置，如果没有指定则使用这个配置 --> <default-config> <property>root</property> <property>admin#1234</property> <property>jdbc:mysql://localhost:3306/jdbcStudy</property> <property>com.mysql.jdbc.Driver</property> <property>30000</property> <property>30</property> <property>3</property> <property>30</property> <property>100</property> <property>2</property> <property>200</property> </default-config> <!-- 命名的配置,可以通过方法调用实现 --> <named-config> <property>root</property> <property>admin#1234</property> <property>jdbc:mysql://localhost:3306/jdbcStudy</property> <property>com.mysql.jdbc.Driver</property> <!-- 如果池中数据连接不够时一次增长多少个 --> <property>5</property> <!-- 初始化数据库连接池时连接的数量 --> <property>20</property> <!-- 数据库连接池中的最大的数据库连接数 --> <property>25</property> <!-- 数据库连接池中的最小的数据库连接数 --> <property>5</property> </named-config> </c3p0-config>

工具类

package com.yeyue.lesson05.utils; import com.mchange.v2.c3p0.ComboPooledDataSource; import org.apache.commons.dbcp2.BasicDataSourceFactory; import javax.sql.DataSource; import java.io.InputStream; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class JdbcUtils_C3P0 { private static DataSource dataSource = null; static { try{ //配置文件写法 dataSource = new ComboPooledDataSource("mysql"); } catch (Exception e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { return dataSource.getConnection(); //从数据源中获取连接 } //释放连接资源 public static void release(Connection conn, Statement st, ResultSet rs){ if(rs!=null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(st!=null){ try { st.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(conn!=null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }

测试类

package com.yeyue.lesson05; import com.yeyue.lesson05.utils.JdbcUtils_C3P0; import com.yeyue.lesson05.utils.JdbcUtils_DBCP; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Date; public class TestC3P0 { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils_C3P0.getConnection(); //INSERT INTO `users` (id,NAME,PASSWORD,email) VALUES (6,\'wewwqw\',\'2323\',\'323wwqd\') String sql = "INSERT INTO `users` (id,NAME,PASSWORD,email,birthday) VALUES (?,?,?,?,?)"; st = conn.prepareStatement(sql); //预编译sql 不执行 st.setInt(1,5); st.setString(2,"wewwqw"); st.setString(3,"2323"); st.setString(4,"323wwqd"); //java.sql.Date 数据库 java.util.Date java new Date().getTime() 获取时间戳 st.setDate(5,new java.sql.Date(new Date().getTime())); int i = st.executeUpdate(); if(i>0){ System.out.println("插入成功"); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JdbcUtils_C3P0.release(conn,st,rs); } } } DBCP

配置文件

driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true username=root password=admin#1234

工具类

package com.yeyue.lesson05.utils; import org.apache.commons.dbcp2.BasicDataSourceFactory; import javax.sql.DataSource; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class JdbcUtils_DBCP { private static DataSource dataSource = null; static { try{ InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties"); Properties properties = new Properties(); properties.load(in); System.out.println(properties.getProperty("driverClassName")); System.out.println(properties.getProperty("url")); System.out.println(properties.getProperty("username")); System.out.println(properties.getProperty("password")); //创建数据源 工厂模式 -->创建 dataSource = BasicDataSourceFactory.createDataSource(properties); System.out.println("1"); } catch (Exception e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { return dataSource.getConnection(); //从数据源中获取连接 } //释放连接资源 public static void release(Connection conn, Statement st, ResultSet rs){ if(rs!=null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(st!=null){ try { st.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(conn!=null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }