C3P0连接池：
配置文件：c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config> <default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///mybase</property>
<property name="user">root</property>
<property name="password">xuyiqing</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config> <named-config name="yiqing">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///mybase</property>
<property name="user">root</property>
<property name="password">xuyiqing</property>
</named-config> </c3p0-config>
测试：
数据准备：
CREATE DATABASE mybase;
USE mybase;
CREATE TABLE users(
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(64),
upassword VARCHAR(64)
);
INSERT INTO users (username,upassword) VALUES("zhangsan","123"),("lisi","456"),("wangwu","789");
SELECT * FROM users;
自定义的JDBC工具类：
package demo; import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties; public class JDBCUtils3 {
public static String driver;
public static String url;
public static String username;
public static String password; static {
try {
ClassLoader classLoader = JDBCUtils3.class.getClassLoader();
InputStream is = classLoader.getResourceAsStream("db.properties");
Properties props = new Properties();
props.load(is);
driver = props.getProperty("driver");
url = props.getProperty("url");
username = props.getProperty("username");
password = props.getProperty("password");
} catch (Exception ex) {
ex.printStackTrace();
}
} public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
} catch (Exception ex) {
ex.printStackTrace();
}
return conn;
} public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
} }
}
配置文件：
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybase
username=root
password=xuyiqing
测试类：
package demo01; import java.sql.Connection;
import java.sql.PreparedStatement; import org.junit.Test; import com.mchange.v2.c3p0.ComboPooledDataSource; import demo.JDBCUtils3; public class TestC3P0 {
@Test
public void testAddUser1() {
Connection conn = null;
PreparedStatement pstmt = null;
ComboPooledDataSource dataSource = new ComboPooledDataSource();// 默认方式加载
// ComboPooledDataSource dataSource = new ComboPooledDataSource("yiqing");
try {
conn = dataSource.getConnection();
String sql = "insert into users values(null,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "张三");
pstmt.setString(2, "123");
int rows = pstmt.executeUpdate();
if (rows > 0) {
System.out.println("添加成功!");
} else {
System.out.println("添加失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils3.release(conn, pstmt, null);
}
}
}
成功！
可以将C3P0连接池抽取出工具类：
package demo01; import java.sql.Connection;
import java.sql.SQLException; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; 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);
}
}
}
测试：
package demo01; import java.sql.Connection;
import java.sql.PreparedStatement; import org.junit.Test; import demo.JDBCUtils3; public class TestC3P0 {
@Test
public void testAddUser1() {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = C3P0Utils.getConnection();
String sql = "insert into users values(null,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "李四");
pstmt.setString(2, "123123");
int rows = pstmt.executeUpdate();
if (rows > 0) {
System.out.println("添加成功!");
} else {
System.out.println("添加失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils3.release(conn, pstmt, null);
}
}
}
成功！
DBCP连接池：
自定义DBCP工具类：
package DBCP; import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; public class DBCPUtils {
private static DataSource dataSource;
static {
try {
// 1.加载找properties文件输入流
InputStream is = DBCPUtils.class.getClassLoader().getResourceAsStream("db.properties");
// 2.加载输入流
Properties props = new Properties();
props.load(is);
// 3.创建数据源
dataSource = BasicDataSourceFactory.createDataSource(props);
} catch (Exception e) {
throw new RuntimeException(e);
}
} public static DataSource getDataSource() {
return dataSource;
} public static Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
测试：
package DBCP;
import java.sql.Connection;
import java.sql.PreparedStatement; import org.junit.Test; public class TestDBCP { @Test
public void testUpdateUserById(){
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBCPUtils.getConnection();
String sql ="update users set upassword=? where uid=?";
pstmt= conn.prepareStatement(sql);
pstmt.setString(1, "456789");
pstmt.setInt(2, 1);
int rows = pstmt.executeUpdate();
if(rows>0){
System.out.println("更新成功!");
}else{
System.out.println("更新失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}