1. C3P0
C3P0是一个开源的JDBC连接池,目前使用它的开源项目有Hibernate,Spring等。
数据库连接池的基本思想就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接,当需要建立数据库连接时,只需从“缓冲池”中取出一个,使用完毕之后再放回去。我们可以通过设定连接池最大连接数来防止系统无尽的与数据库连接。获取一个连接,系统要在背后做很多消耗资源的事情,大多时候,创建连接的时间比执行sql语句的时间还要长。用户每次请求都需要向数据库获得链接,而数据库创建连接通常需要消耗相对较大的资源,创建时间也较长。
使用方法:
1.导入jar包:c3p0和mysql
2.配置xml文件
c3p0 模板
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///jdbctest</property>
<property name="user">root</property>
<property name="password">950811</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<named-config name="">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///web08</property>
<property name="user">root</property>
<property name="password">root</property>
</named-config>
</c3p0-config>
案例:使用c3p0连接池,完成数据库的表更新操作
public class C3p0testDemo {
public static void main(String[] args) {
//
Connection conn = null;
PreparedStatement pstmt = null;
// 1.创建自定义连接池对象
ComboPooledDataSource dataSource = new ComboPooledDataSource();// 加载默认的配置
// ComboPooledDataSource dataSource = new
// ComboPooledDataSource("itheima");//加载有名称的配置
try {
// 2.从池子中获取连接
conn = dataSource.getConnection();
String sql = "insert into userinfo values('16',?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "刘璐");
pstmt.setString(2, "21");
int rows = pstmt.executeUpdate();
if (rows > 0) {
System.out.println("添加成功!");
} else {
System.out.println("添加失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils_V2.release(conn, pstmt, null);
}
}
}
public class JDBCUtils_V2 {
private static String driver;
private static String url;
private static String username;
private static String password;
/**
* 静态代码块加载配置文件信息
*/
static{
ResourceBundle bundle = ResourceBundle.getBundle("db");
driver = bundle.getString("driver");
url = bundle.getString("url");
username = bundle.getString("username");
password = bundle.getString("password");
}
/**
* 获取连接方法
*
* @return
*/
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
结果显示:
c3p0 utils
public class C3P0Utils {
private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
public static DataSource getDataSource() {
return dataSource;
}
public static Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
测试Test:
public class C3p0UtilsTest {
public static void main(String[] args) {
//
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1.从池子中获取连接
conn = C3P0Utils.getConnection();
String sql = "insert into userinfo values('29',?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "罗星鑫");
pstmt.setString(2, "21");
int rows = pstmt.executeUpdate();
if (rows > 0) {
System.out.println("添加成功!");
} else {
System.out.println("添加失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils_V2.release(conn, pstmt, null);
}
}
}
结果显示: