c3p0 数据库连接池
在 lib 文件下导入 c3p0-0.9.1.2.jar 包
com.atguigu4.connection 包
package com.atguigu4.connection;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;
public class C3P0Test {
@Test
public void testGetConnection() throws Exception {
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "com.mysql.cj.jdbc.Driver" );
cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
cpds.setUser("root");
cpds.setPassword("123456");
cpds.setInitialPoolSize(10);
Connection conn = cpds.getConnection();
System.out.println(conn);
}
@Test
public void testGetConnection1() throws SQLException {
ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
Connection conn = cpds.getConnection();
System.out.println(conn);
}
}
com.atguigu4.util 包
package com.atguigu4.util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JDBCUtils {
public static Connection getConnection() throws Exception {
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
Class.forName(driverClass);
Connection conn = DriverManager.getConnection(url,user,password);
return conn;
}
private static ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
public static Connection getConnection1() throws Exception {
Connection conn = cpds.getConnection();
return conn;
}
public static void closeResource(Connection conn,Statement ps) {
try {
if(ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void closeResource(Connection conn,Statement ps,ResultSet rs) {
try {
if(ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
src 下的 c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<named-config name="helloc3p0">
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///test</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquireIncrement">50</property>
<property name="initialPoolSize">100</property>
<property name="minPoolSize">50</property>
<property name="maxPoolSize">1000</property>
<property name="maxStatements">0</property>
<property name="maxStatementsPerConnection">5</property>
</named-config>
</c3p0-config>
com.atguigu3.dao.junit 包
package com.atguigu3.dao.junit;
import static org.junit.Assert.*;
import java.sql.Connection;
import java.sql.Date;
import java.util.List;
import org.junit.Test;
import com.atguigu4.util.JDBCUtils;
import com.atguigu2.bean.Customer;
import com.atguigu3.dao.CustomerDAOImpl;
public class CustomerDAOImplTest {
private CustomerDAOImpl dao = new CustomerDAOImpl();
@Test
public void testInsert() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Customer cust = new Customer(1,"于小飞","xiaofei@126.com",new Date(23453453232L));
dao.insert(conn, cust);
System.out.println("添加成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testDeleteById() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
dao.deleteById(conn, 13);
System.out.println("删除成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testUpdateConnectionCustomer() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Customer cust = new Customer(18,"贝多芬","beiduofen@126.com",new Date(3425214134L));
dao.update(conn, cust);
System.out.println("修改成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testGetCustomerById() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection1();
Customer cust = dao.getCustomerById(conn, 19);
System.out.println(cust);
System.out.println("添加成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, null);
}
}
@Test
public void testGetAll() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
List<Customer> list = dao.getAll(conn);
list.forEach(System.out::println);
System.out.println("");