C3P0連接池： 配置文件：c3p0-config.xml 測試： 數據準備： CREATE DATABASE mybase; USE mybase; CREATE TABLE users( uid INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(6 ...
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;View Code
自定義的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); } } }View Code
測試：
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); } } }View Code
成功！
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); } } }View Code
測試：
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); } } }View Code
