資料庫連線池的概念
-
資料庫連線背景
- 資料庫連線是一種關鍵的、有限的、昂貴的資源，這一點在多使用者的網頁應用程式中體現得尤為突出。對資料庫連線的管理能顯著影響到整個應用程式的伸縮性和健壯性，影響到程式的效能指標。資料庫連線池正是針對這個問題提出來的。
-
資料庫連線池
- 資料庫連線池負責分配、管理和釋放資料庫連線，它允許應用程式重複使用一個現有的資料庫連線，而不是再重新建立一個。這項技術能明顯提高對資料庫操作的效能。
-
資料庫連線池的原理
-
沒有使用資料庫連線池：一個訪問建立一個連線，使用完關閉連線。而頻繁的建立和關閉連線非常耗時
-
使用資料庫連線池之後：提前準備一些資料庫連線，使用時從池中取出，用完歸還連線池
-
自定義連線池
初探連線池
自定義JDBC工具類
配置檔案 config.properties
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://主機名:3306/資料庫名
username=使用者名稱
password=密碼
JDBCUtils工具類
public class JDBCUtils {
private JDBCUtils() {} //建構函式私有化
private static String driverClass;
private static String url;
private static String username;
private static String password;
private static Connection con;
static {
try {
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("config.properties");
Properties properties = new Properties();
properties.load(is);
driverClass = properties.getProperty("driverClass");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
Class.forName(driverClass);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() { //獲取連線物件
try {
con = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
//關閉連線（有查詢結果集）
public static void close(Connection con, Statement stat, ResultSet res) {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (res != null) {
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//關閉連線（無查詢結果集）
public static void close(Connection con, Statement stat) {
close(con, stat, null);
}
}
實現連線池類
定義一個連線池類並實現java.sql.DataSource 介面。
Connection getConnection(); //獲取資料庫連線物件
public class MyDataSource implements DataSource{
//定義集合容器，用於儲存多個資料庫連線物件
//使用Collections 工具類實現集合的執行緒同步
private static List<Connection> pool = Collections.synchronizedList(new ArrayList<Connection>());
//靜態程式碼塊，生成10個資料庫連線儲存到集合中
static {
for (int i = 0; i < 10; i++) {
Connection con = JDBCUtils.getConnection();
pool.add(con);
}
}
//返回連線池的大小
public int getSize() {
return pool.size();
}
//從池中返回一個資料庫連線
@Override
public Connection getConnection() {
if(pool.size() > 0) {
//從池中獲取資料庫連線
return pool.remove(0);
}else {
throw new RuntimeException("連線數量已用盡");
}
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
}
@Override
public int getLoginTimeout() throws SQLException {
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
}
自定義連線池的測試
public class MyDataSourceTest {
public static void main(String[] args) throws SQLException {
MyDataSource dataSource = new MyDataSource();
System.out.println("使用前連線池數量：" + dataSource.getSize());
Connection con = dataSource.getConnection();
String sql = "select * from emp";
PreparedStatement pst = con.prepareStatement(sql);
ResultSet res = pst.executeQuery();
while (res.next()) {
String ename = res.getString("ename");
String job = res.getString("job");
String hiredate = res.getString("hiredate");
System.out.println("ename:" + ename + "\t job:" + job + "\t hiredate:" + hiredate);
}
res.close();
pst.close();
con.close();
System.out.println("使用後連線池數量：" + dataSource.getSize());
}
}
輸出：
使用前連線池數量：10
ename:SMITH job:CLERK hiredate:1980-12-17
ename:ALLEN job:SALESMAN hiredate:1981-02-20
ename:WARD job:SALESMAN hiredate:1981-02-22
ename:JONES job:MANAGER hiredate:1981-04-02
ename:MARTIN job:SALESMAN hiredate:1981-09-28
ename:BLAKE job:MANAGER hiredate:1981-05-01
ename:CLARK job:MANAGER hiredate:1981-06-09
ename:SCOTT job:ANALYST hiredate:1987-04-19
ename:KING job:PRESIDENT hiredate:1981-11-17
ename:TURNER job:SALESMAN hiredate:1981-09-08
ename:ADAMS job:CLERK hiredate:1987-05-23
ename:JAMES job:CLERK hiredate:1981-12-03
ename:FORD job:ANALYST hiredate:1981-12-03
ename:MILLER job:CLERK hiredate:1982-01-23
使用後連線池數量：9
問題：雖然我們自定義了資料庫連線池，但是連線關閉以後並沒有歸還給資料庫連線池，還需要改進歸還連線的問題
繼承方式改進連線池
System.out.println(JDBCUtils.getConnection());
//com.mysql.jdbc.JDBC4Connection@470e2030
通過輸出Connection的地址發現Connection類的實現類是JDBC4Connection，是否能夠通過編寫一個類繼承JDBC4Connection，然後重寫close()方法，在關閉連線的同時歸還連線？
/*
自定義Connection類
*/
public class MyConnection1 extends JDBC4Connection {
//宣告連線物件和連線池集合物件
private Connection con;
private List<Connection> pool;
//通過構造方法給成員變數賦值
public MyConnection1(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url,Connection con,List<Connection> pool) throws SQLException {
super(hostToConnectTo, portToConnectTo, info, databaseToConnectTo, url);
this.con = con;
this.pool = pool;
}
//重寫close()方法，將連線歸還給池中
@Override
public void close() throws SQLException {
pool.add(con);
}
}
但是這種方式行不通，通過檢視JDBC工具類獲取連線的方法我們發現：我們雖然自定義了一個子類，完成了歸還連線的操作。但是DriverManager獲取的還是JDBC4Connection這個物件，並不是我們的子類物件。而我們又不能整體去修改驅動包中類的功能！
//將之前的連線物件換成自定義的子類物件
private static MyConnection1 con;
public static Connection getConnection() {
try {
//等效於：MyConnection1 con = new JDBC4Connection(); 子類引用指向父類物件，語法錯誤！
con = DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
裝飾設計模式改進連線池
自定義Connection類。通過裝飾設計模式，實現和mysql驅動包中的Connection實現類相同的功能！
實現步驟：
-
定義一個類，實現Connection介面
-
定義Connection連線物件和連線池容器物件的變數
-
提供有參構造方法，接收連線物件和連線池物件，對變數賦值
-
在close()方法中，完成連線的歸還
-
剩餘方法，只需要呼叫mysql驅動包的連線物件完成即可
public class MyConnection2 implements Connection {
//2.定義Connection連線物件和連線池容器物件的變數
private Connection con;
private List<Connection> pool;
//3.提供有參構造方法，接收連線物件和連線池物件，對變數賦值
public MyConnection2(Connection con,List<Connection> pool) {
this.con = con;
this.pool = pool;
}
//4.在close()方法中，完成連線的歸還
@Override
public void close() throws SQLException {
pool.add(con);
}
@Override
public Statement createStatement() throws SQLException {
return con.createStatement();
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
return con.prepareStatement(sql);
}
@Override
public CallableStatement prepareCall(String sql) throws SQLException {
return con.prepareCall(sql);
}
@Override
public String nativeSQL(String sql) throws SQLException {
return con.nativeSQL(sql);
}
@Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
con.setAutoCommit(autoCommit);
}
@Override
public boolean getAutoCommit() throws SQLException {
return con.getAutoCommit();
}
@Override
public void commit() throws SQLException {
con.commit();
}
@Override
public void rollback() throws SQLException {
con.rollback();
}
@Override
public boolean isClosed() throws SQLException {
return con.isClosed();
}
@Override
public DatabaseMetaData getMetaData() throws SQLException {
return con.getMetaData();
}
@Override
public void setReadOnly(boolean readOnly) throws SQLException {
con.setReadOnly(readOnly);
}
@Override
public boolean isReadOnly() throws SQLException {
return con.isReadOnly();
}
@Override
public void setCatalog(String catalog) throws SQLException {
con.setCatalog(catalog);
}
@Override
public String getCatalog() throws SQLException {
return con.getCatalog();
}
@Override
public void setTransactionIsolation(int level) throws SQLException {
con.setTransactionIsolation(level);
}
@Override
public int getTransactionIsolation() throws SQLException {
return con.getTransactionIsolation();
}
@Override
public SQLWarning getWarnings() throws SQLException {
return con.getWarnings();
}
@Override
public void clearWarnings() throws SQLException {
con.clearWarnings();
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
return con.createStatement(resultSetType,resultSetConcurrency);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
return con.prepareStatement(sql,resultSetType,resultSetConcurrency);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
return con.prepareCall(sql,resultSetType,resultSetConcurrency);
}
@Override
public Map<String, Class<?>> getTypeMap() throws SQLException {
return con.getTypeMap();
}
@Override
public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
con.setTypeMap(map);
}
@Override
public void setHoldability(int holdability) throws SQLException {
con.setHoldability(holdability);
}
@Override
public int getHoldability() throws SQLException {
return con.getHoldability();
}
@Override
public Savepoint setSavepoint() throws SQLException {
return con.setSavepoint();
}
@Override
public Savepoint setSavepoint(String name) throws SQLException {
return con.setSavepoint(name);
}
@Override
public void rollback(Savepoint savepoint) throws SQLException {
con.rollback(savepoint);
}
@Override
public void releaseSavepoint(Savepoint savepoint) throws SQLException {
con.releaseSavepoint(savepoint);
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return con.createStatement(resultSetType,resultSetConcurrency,resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return con.prepareStatement(sql,resultSetType,resultSetConcurrency,resultSetHoldability);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return con.prepareCall(sql,resultSetType,resultSetConcurrency,resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
return con.prepareStatement(sql,autoGeneratedKeys);
}
@Override
public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
return con.prepareStatement(sql,columnIndexes);
}
@Override
public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
return con.prepareStatement(sql,columnNames);
}
@Override
public Clob createClob() throws SQLException {
return con.createClob();
}
@Override
public Blob createBlob() throws SQLException {
return con.createBlob();
}
@Override
public NClob createNClob() throws SQLException {
return con.createNClob();
}
@Override
public SQLXML createSQLXML() throws SQLException {
return con.createSQLXML();
}
@Override
public boolean isValid(int timeout) throws SQLException {
return con.isValid(timeout);
}
@Override
public void setClientInfo(String name, String value) throws SQLClientInfoException {
con.setClientInfo(name,value);
}
@Override
public void setClientInfo(Properties properties) throws SQLClientInfoException {
con.setClientInfo(properties);
}
@Override
public String getClientInfo(String name) throws SQLException {
return con.getClientInfo(name);
}
@Override
public Properties getClientInfo() throws SQLException {
return con.getClientInfo();
}
@Override
public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
return con.createArrayOf(typeName,elements);
}
@Override
public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
return con.createStruct(typeName,attributes);
}
@Override
public void setSchema(String schema) throws SQLException {
con.setSchema(schema);
}
@Override
public String getSchema() throws SQLException {
return con.getSchema();
}
@Override
public void abort(Executor executor) throws SQLException {
con.abort(executor);
}
@Override
public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
con.setNetworkTimeout(executor,milliseconds);
}
@Override
public int getNetworkTimeout() throws SQLException {
return con.getNetworkTimeout();
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return con.unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return con.isWrapperFor(iface);
}
}
自定義連線池類
public class MyDataSource implements DataSource{
//定義集合容器，用於儲存多個資料庫連線物件
private static List<Connection> pool = Collections.synchronizedList(new ArrayList<Connection>());
//靜態程式碼塊，生成10個資料庫連線儲存到集合中
static {
for (int i = 0; i < 10; i++) {
Connection con = JDBCUtils.getConnection();
pool.add(con);
}
}
//返回連線池的大小
public int getSize() {
return pool.size();
}
//從池中返回一個資料庫連線
@Override
public Connection getConnection() {
if(pool.size() > 0) {
//從池中獲取資料庫連線
Connection con = pool.remove(0);
//通過自定義連線物件進行包裝
MyConnection2 mycon = new MyConnection2(con,pool);
//返回包裝後的連線物件
return mycon;
}else {
throw new RuntimeException("連線數量已用盡");
}
}
}
缺點：Connection 介面中要實現的方法太多了，程式碼繁雜
介面卡設計模式改進連線池
提供一個介面卡類，實現Connection介面，將所有功能進行實現(除了close()方法)，作為中間類。自定義連線類只需要繼承這個介面卡類，重寫需要改進的close()方法即可！
介面卡類不需要實現close()方法，所以定義為抽象類
public abstract class MyAdapter implements Connection {
// 定義資料庫連線物件的變數
private Connection con;
// 通過構造方法賦值
public MyAdapter(Connection con) {
this.con = con;
}
// 所有的方法，均呼叫mysql的連線物件實現
@Override
public Statement createStatement() throws SQLException {
return con.createStatement();
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
return con.prepareStatement(sql);
}
@Override
public CallableStatement prepareCall(String sql) throws SQLException {
return con.prepareCall(sql);
}
@Override
public String nativeSQL(String sql) throws SQLException {
return con.nativeSQL(sql);
}
@Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
con.setAutoCommit(autoCommit);
}
@Override
public boolean getAutoCommit() throws SQLException {
return con.getAutoCommit();
}
@Override
public void commit() throws SQLException {
con.commit();
}
@Override
public void rollback() throws SQLException {
con.rollback();
}
@Override
public boolean isClosed() throws SQLException {
return con.isClosed();
}
@Override
public DatabaseMetaData getMetaData() throws SQLException {
return con.getMetaData();
}
@Override
public void setReadOnly(boolean readOnly) throws SQLException {
con.setReadOnly(readOnly);
}
@Override
public boolean isReadOnly() throws SQLException {
return con.isReadOnly();
}
@Override
public void setCatalog(String catalog) throws SQLException {
con.setCatalog(catalog);
}
@Override
public String getCatalog() throws SQLException {
return con.getCatalog();
}
@Override
public void setTransactionIsolation(int level) throws SQLException {
con.setTransactionIsolation(level);
}
@Override
public int getTransactionIsolation() throws SQLException {
return con.getTransactionIsolation();
}
@Override
public SQLWarning getWarnings() throws SQLException {
return con.getWarnings();
}
@Override
public void clearWarnings() throws SQLException {
con.clearWarnings();
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
return con.createStatement(resultSetType,resultSetConcurrency);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
return con.prepareStatement(sql,resultSetType,resultSetConcurrency);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
return con.prepareCall(sql,resultSetType,resultSetConcurrency);
}
@Override
public Map<String, Class<?>> getTypeMap() throws SQLException {
return con.getTypeMap();
}
@Override
public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
con.setTypeMap(map);
}
@Override
public void setHoldability(int holdability) throws SQLException {
con.setHoldability(holdability);
}
@Override
public int getHoldability() throws SQLException {
return con.getHoldability();
}
@Override
public Savepoint setSavepoint() throws SQLException {
return con.setSavepoint();
}
@Override
public Savepoint setSavepoint(String name) throws SQLException {
return con.setSavepoint(name);
}
@Override
public void rollback(Savepoint savepoint) throws SQLException {
con.rollback(savepoint);
}
@Override
public void releaseSavepoint(Savepoint savepoint) throws SQLException {
con.releaseSavepoint(savepoint);
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return con.createStatement(resultSetType,resultSetConcurrency,resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return con.prepareStatement(sql,resultSetType,resultSetConcurrency,resultSetHoldability);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return con.prepareCall(sql,resultSetType,resultSetConcurrency,resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
return con.prepareStatement(sql,autoGeneratedKeys);
}
@Override
public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
return con.prepareStatement(sql,columnIndexes);
}
@Override
public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
return con.prepareStatement(sql,columnNames);
}
@Override
public Clob createClob() throws SQLException {
return con.createClob();
}
@Override
public Blob createBlob() throws SQLException {
return con.createBlob();
}
@Override
public NClob createNClob() throws SQLException {
return con.createNClob();
}
@Override
public SQLXML createSQLXML() throws SQLException {
return con.createSQLXML();
}
@Override
public boolean isValid(int timeout) throws SQLException {
return con.isValid(timeout);
}
@Override
public void setClientInfo(String name, String value) throws SQLClientInfoException {
con.setClientInfo(name,value);
}
@Override
public void setClientInfo(Properties properties) throws SQLClientInfoException {
con.setClientInfo(properties);
}
@Override
public String getClientInfo(String name) throws SQLException {
return con.getClientInfo(name);
}
@Override
public Properties getClientInfo() throws SQLException {
return con.getClientInfo();
}
@Override
public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
return con.createArrayOf(typeName,elements);
}
@Override
public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
return con.createStruct(typeName,attributes);
}
@Override
public void setSchema(String schema) throws SQLException {
con.setSchema(schema);
}
@Override
public String getSchema() throws SQLException {
return con.getSchema();
}
@Override
public void abort(Executor executor) throws SQLException {
con.abort(executor);
}
@Override
public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
con.setNetworkTimeout(executor,milliseconds);
}
@Override
public int getNetworkTimeout() throws SQLException {
return con.getNetworkTimeout();
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return con.unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return con.isWrapperFor(iface);
}
}
自定義連線類
通過介面卡設計模式。完成close()方法的重寫
-
定義一個類，繼承介面卡父類
-
定義
Connection連線物件和連線池容器物件的變數 -
提供有參構造方法，接收連線物件和連線池物件，對變數賦值
-
在
close()方法中，完成連線的歸還
public class MyConnection3 extends MyAdapter {
//2.定義Connection連線物件和連線池容器物件的變數
private Connection con;
private List<Connection> pool;
//3.提供有參構造方法，接收連線物件和連線池物件，對變數賦值
public MyConnection3(Connection con,List<Connection> pool) {
super(con); // 將接收的資料庫連線物件給介面卡父類傳遞
this.con = con;
this.pool = pool;
}
//4.在close()方法中，完成連線的歸還
@Override
public void close() throws SQLException {
pool.add(con);
}
}
自定義連線池類
public class MyDataSource implements DataSource{
//定義集合容器，用於儲存多個資料庫連線物件
private static List<Connection> pool = Collections.synchronizedList(new ArrayList<Connection>());
//靜態程式碼塊，生成10個資料庫連線儲存到集合中
static {
for (int i = 0; i < 10; i++) {
Connection con = JDBCUtils.getConnection();
pool.add(con);
}
}
//返回連線池的大小
public int getSize() {
return pool.size();
}
//從池中返回一個資料庫連線
@Override
public Connection getConnection() {
if(pool.size() > 0) {
//從池中獲取資料庫連線
Connection con = pool.remove(0);
//通過自定義連線物件進行包裝
MyConnection3 mycon = new MyConnection3(con,pool);
//返回包裝後的連線物件
return mycon;
}else {
throw new RuntimeException("連線數量已用盡");
}
}
}
缺點：自定義連線類中的方法已經很簡潔了。剩餘所有的方法已經抽取到了介面卡類中。但是介面卡這個類還是我們自己編寫的，也比較麻煩！所以可以使用動態代理的方式來改進。
動態代理
public class MyDataSource implements DataSource{
//定義集合容器，用於儲存多個資料庫連線物件
private static List<Connection> pool = Collections.synchronizedList(new ArrayList<Connection>());
//靜態程式碼塊，生成10個資料庫連線儲存到集合中
static {
for (int i = 0; i < 10; i++) {
Connection con = JDBCUtils.getConnection();
pool.add(con);
}
}
//返回連線池的大小
public int getSize() {
return pool.size();
}
//動態代理方式
@Override
public Connection getConnection() {
if(pool.size() > 0) {
//從池中獲取資料庫連線
Connection con = pool.remove(0);
Connection proxyCon = (Connection)Proxy.newProxyInstance(con.getClass().getClassLoader(), new Class[]{Connection.class}, new InvocationHandler() {
/*
執行Connection實現類所有方法都會經過invoke
如果是close方法，則將連線還回池中
如果不是，直接執行實現類的原有方法
*/
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
if(method.getName().equals("close")) {
pool.add(con);
return null;
}else {
return method.invoke(con,args);
}
}
});
return proxyCon;
}else {
throw new RuntimeException("連線數量已用盡");
}
}
//從池中返回一個資料庫連線
/*@Override
public Connection getConnection() {
if(pool.size() > 0) {
//從池中獲取資料庫連線
Connection con = pool.remove(0);
//通過自定義連線物件進行包裝
//MyConnection2 mycon = new MyConnection2(con,pool);
MyConnection3 mycon = new MyConnection3(con,pool);
//返回包裝後的連線物件
return mycon;
}else {
throw new RuntimeException("連線數量已用盡");
}
}*/
}
開源連線池的使用
C3P0連線池
-
匯入jar包
-
匯入配置檔案到src目錄下
-
建立c3p0連線池物件
-
獲取資料庫連線進行使用
配置檔案 c3p0-config.xml 注意該配置檔案的名字是固定的不要改，否則無法識別
-
initialPoolSize：初始化連線數量 -
maxPoolSize：最大連線數量，當連線數量超過初始化連線數量時，會在連線池內繼續建立連線，直到達到資料庫連線池所能容納的最大連線數量 -
checkoutTimeout：超過時間。如果使用的連線數量超過最大連線數量，編譯器會在checkoutTimeout時間以後報錯並終止程式。
<c3p0-config>
<!-- 使用預設的配置讀取連線池物件 -->
<default-config>
<!-- 連線引數 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://主機名:3306/資料庫名</property>
<property name="user">使用者名稱</property>
<property name="password">密碼</property>
<!-- 連線池引數 -->
<!-- 初始化連線數量 -->
<property name="initialPoolSize">5</property>
<!-- 最大連線數量 -->
<property name="maxPoolSize">10</property>
<!-- 超時時間 -->
<property name="checkoutTimeout">3000</property>
</default-config>
<!-- 建立資料庫連線池時指定名稱-->
<named-config name="otherc3p0">
<!-- 連線引數 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://主機名:3306/資料庫名</property>
<property name="user">使用者名稱</property>
<property name="password">密碼</property>
<!-- 連線池引數 -->
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">8</property>
<property name="checkoutTimeout">1000</property>
</named-config>
</c3p0-config>
C3P0資料庫連線池的使用
public static void main(String[] args) throws SQLException {
//建立c3p0連線池物件
DataSource dataSource = new ComboPooledDataSource();
//獲取資料庫連線進行使用
Connection con = dataSource.getConnection();
String s = "select *from emp";
PreparedStatement pst = con.prepareStatement(s);
ResultSet rs = pst.executeQuery();
while (rs.next()) {
String ename = rs.getString("ename");
String job = rs.getString("job");
String hiredate = rs.getString("hiredate");
System.out.println("ename:" + ename + " job:" + job + " hiredate:" + hiredate);
}
rs.close();
pst.close();
con.close(); // 將連線物件歸還池中
}
Druid連線池
-
匯入jar包
-
編寫配置檔案，放在src目錄下
-
通過Properties集合載入配置檔案
-
通過Druid連線池工廠類獲取資料庫連線池物件
-
獲取資料庫連線，進行使用
配置檔案druid.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://主機名:3306/資料庫名
username:使用者名稱
password:密碼
# 初始連線數量
initialSize=5
# 最大連線數量
maxActive=10
# 最長等待時間
maxWait=3000
Druid資料庫的使用
public static void main(String[] args) throws Exception {
//通過Properties集合載入配置檔案
InputStream is = demo01.class.getClassLoader().getResourceAsStream("druid.properties");
Properties prop = new Properties();
prop.load(is);
//通過Druid連線池工廠類獲取資料庫連線池物件
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//獲取資料庫連線，進行使用
Connection con = dataSource.getConnection();
PreparedStatement pst = con.prepareStatement("select *from emp");
ResultSet rs = pst.executeQuery();
while (rs.next()) {
String ename = rs.getString("ename");
String job = rs.getString("job");
String hiredate = rs.getString("hiredate");
System.out.println("ename:" + ename + " job:" + job + " hiredate:" + hiredate);
}
rs.close();
pst.close();
con.close();
}
抽取工具類
/*
資料庫連線池工具類
*/
public class DataSourceUtils {
//1.私有構造方法
private DataSourceUtils(){}
//2.定義DataSource資料來源變數
private static DataSource dataSource;
//3.提供靜態程式碼塊，完成配置檔案的載入和獲取連線池物件
static {
try{
//載入配置檔案
InputStream is = DruidDemo1.class.getClassLoader().getResourceAsStream("druid.properties");
Properties prop = new Properties();
prop.load(is);
//獲取資料庫連線池物件
dataSource = DruidDataSourceFactory.createDataSource(prop);
} catch(Exception e) {
e.printStackTrace();
}
}
//4.提供獲取資料庫連線的方法
public static Connection getConnection() {
Connection con = null;
try {
con = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
//5.提供獲取資料庫連線池的方法
public static DataSource getDataSource() {
return dataSource;
}
//6.提供釋放資源的方法
public static void close(Connection con, Statement stat, ResultSet rs) {
if(con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection con, Statement stat) {
close(con,stat,null);
}
}
工具類的使用
public static void main(String[] args) throws SQLException {
//利用工具類獲取DataSoure
DataSource dataSource = DataSourceUtils.getDataSource();
//獲取連線，並使用
Connection con = dataSource.getConnection();
String s = "select *from emp";
PreparedStatement pst = con.prepareStatement(s);
ResultSet rs = pst.executeQuery();
while (rs.next()) {
String ename = rs.getString("ename");
String job = rs.getString("job");
String hiredate = rs.getString("hiredate");
System.out.println("ename:" + ename + " job:" + job + " hiredate:" + hiredate);
}
DataSourceUtils.close(con, pst, rs);
}