1.实现java提供的 DataSource接口(规范)
2. 创建连接池(在加载类或者构造函数时就创建)
3.获取连接
4. 用完后要归还,不然 会用完连接
5.扩容
6.问题:
装饰器模式。
package com.itheima.uitl;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
import javax.sql.DataSource;
/**
* 这是一个数据库连接池
* 一开始先往池子里面放10个连接
*
* 1. 开始创建10个连接。
*
* 2. 来的程序通过getConnection获取连接
*
* 3. 用完之后,使用addBack 归还连接。
*
* 4. 扩容。
*
*
* 问题:
*
* 1. sun公司针对数据库连接池定义的一套规范。
*
* 1. 需要额外记住 addBack方法
*
* 2. 单例。
*
* 3. 无法面向接口编程。
*
* UserDao dao = new UserDaoImpl();
* dao.insert();
*
*
* DataSource dataSource = new MyDataSource();
*
* 因为接口里面没有定义addBack方法。
*
* 4. 怎么解决?
*
*/
public class MyDataSource implements DataSource {
List <Connection> list = new ArrayList<Connection>();
public MyDataSource() {
for (int i = 0; i < 10; i++) {
Connection conn = JDBCUtil.getConn();
list.add(conn);
}
}
// 该连接池对外公布的获取连接的方法
@Override
public Connection getConnection() throws SQLException {
//来拿连接的时候,先看看,池子里面还有没有。
if(list.size() == 0 ){
for (int i = 0; i < 5; i++) {
Connection conn = JDBCUtil.getConn();
list.add(conn);
}
}
//remove(0) ---> 移除第一个。 移除的是集合中的第一个。 移除的是开始的那个元素
Connection conn = list.remove(0);
//在把这个对象抛出去的时候, 对这个对象进行包装。装饰器模式
Connection connection = new ConnectionWrap(conn, list);
return connection;
}
/**
* 用完之后,记得归还。
* @param conn
*/
public void addBack(Connection conn){
list.add(conn);
}
//----------------------------
@Override
public PrintWriter getLogWriter() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public int getLoginTimeout() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
// TODO Auto-generated method stub
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
// TODO Auto-generated method stub
return null;
}
}
package com.itheima.uitl;
import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;
public class ConnectionWrap implements Connection{
Connection connection = null;
List <Connection> list ;
public ConnectionWrap(Connection connection , List <Connection> list) {
super();
this.connection = connection;
this.list = list;
}
@Override
public void close() throws SQLException {
//connection.close();
System.out.println("有人来归还连接对象了。 归还之前,池子里面是:"+list.size());
list.add(connection);
System.out.println("有人来归还连接对象了。 归还之后...,池子里面是:"+list.size());
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
return connection.prepareStatement(sql);
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public Statement createStatement() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public CallableStatement prepareCall(String sql) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public String nativeSQL(String sql) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public boolean getAutoCommit() throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public void commit() throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void rollback() throws SQLException {
// TODO Auto-generated method stub
}
@Override
public boolean isClosed() throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public DatabaseMetaData getMetaData() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public void setReadOnly(boolean readOnly) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public boolean isReadOnly() throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public void setCatalog(String catalog) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public String getCatalog() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public void setTransactionIsolation(int level) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public int getTransactionIsolation() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
@Override
public SQLWarning getWarnings() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public void clearWarnings() throws SQLException {
// TODO Auto-generated method stub
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Map<String, Class<?>> getTypeMap() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setHoldability(int holdability) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public int getHoldability() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
@Override
public Savepoint setSavepoint() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Savepoint setSavepoint(String name) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public void rollback(Savepoint savepoint) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void releaseSavepoint(Savepoint savepoint) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency,
int resultSetHoldability) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency,
int resultSetHoldability) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Clob createClob() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Blob createBlob() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public NClob createNClob() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public SQLXML createSQLXML() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public boolean isValid(int timeout) throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public void setClientInfo(String name, String value) throws SQLClientInfoException {
// TODO Auto-generated method stub
}
@Override
public void setClientInfo(Properties properties) throws SQLClientInfoException {
// TODO Auto-generated method stub
}
@Override
public String getClientInfo(String name) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Properties getClientInfo() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public void setSchema(String schema) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public String getSchema() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public void abort(Executor executor) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public int getNetworkTimeout() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
}
package com.itheima.uitl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.junit.Test;
public class TestPool {
@Test
public void testPool(){
Connection conn = null;
PreparedStatement ps = null;
MyDataSource dataSource = new MyDataSource();
try {
conn = dataSource.getConnection(); //返回的是已经装饰过的conn
String sql = "insert into account values (null , 'xilali' , 10)";
ps = conn.prepareStatement(sql);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
//归还连接
//conn.close();
//dataSource.addBack(conn);
JDBCUtil.release(conn, ps);
}
}
}
数据库连接池(DBCP)简化数据库连接的创建
数据库连接池只能帮助创建连接。
1.不使用配置文件
2.使用配置文件(常用):
C3P0
1.不使用配置文件
2.使用用配置文件(常用):
DBUtils(简化CURD)
查询单个元素(实现结口):
查询单个元素(利用现有类):
查询多个元素:
JDBC+DBUtils实现分析。
1. JDBCUtils.getDataSource()首先得到一个C3P0连接池。
2.
2中的构造函数,也就是声明一个连接池。
这里会调用C3P0Config,会调用静态代码块。
3.数据库配置文件的加载
的静态代码块中,
这样加载完