解决资源浪费问题,提高代码开发的性能:
概念:
常见的连接池:DBCP、C3P0(使用占80%左右);java为数据库连接池提供了公共的接口:javax.sql.DataSource
自定义连接池:
第一步:新建一个项目创建lib并导入相应文件,在包中创建properties文件与JDBC抽取工具类
第二步:新建一个包与类,类中事项DataSource接口(是javax.sql下的):
MyDataSource:
package cn.itheima.jdbc.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.logging.Logger;
import javax.sql.DataSource;
import cn.itheima.jdbc.utils.JDBCUtils_V3;
public class MyDataSource implements DataSource{
//1.创建1个容器用于存储Connection对象
private static LinkedList<Connection> pool = new LinkedList<Connection>();
//2.创建5个连接放到容器中去
static{
for (int i = 0; i < 5; i++) {
Connection conn = JDBCUtils_V3.getConnection();
pool.add(conn);
}
}
/**
* 重写获取连接的方法
*/
@Override
public Connection getConnection() throws SQLException {
Connection conn = null;
//3.使用前先判断
if(pool.size()==0){
//4.池子里面没有,我们再创建一些
for (int i = 0; i < 5; i++) {
conn = JDBCUtils_V3.getConnection();
pool.add(conn);
}
}
//5.从池子里面获取一个连接对象Connection
conn = pool.remove(0);
return conn;
}
/**
* 归还连接对象到连接池中去
*/
public void backConnection(Connection conn){
pool.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 cn.itheima.jdbc.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import javax.sql.DataSource;
import org.junit.Test;
import cn.itheima.jdbc.DataSource.MyDataSource;
import cn.itheima.jdbc.DataSource.MyDataSource1;
import cn.itheima.jdbc.utils.JDBCUtils_V3;
public class TestMyDataSource {
/**
* 添加用户
* 使用未改造过的connection
*/
@Test
public void testAddUser() {
Connection conn = null;
PreparedStatement pstmt = null;
// 1.创建自定义连接池对象
MyDataSource dataSource = new MyDataSource();
try {
// 2.从池子中获取连接
conn = dataSource.getConnection();
String sql = "insert into tbl_user values(null,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "吕布");
pstmt.setString(2, "貂蝉");
int rows = pstmt.executeUpdate();
if (rows > 0) {
System.out.println("添加成功!");
} else {
System.out.println("添加失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
dataSource.backConnection(conn);
}
}
}
上述方法的增强:
可以进行方法增强的办法总结:
使用装饰者设计模式实现方法增强:
MyConnection.java:
package cn.itheima.jdbc.DataSource;
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.LinkedList;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;
//1.实现同一个接口Connection
public class MyConnection implements Connection {
//3.定义一个变量
private Connection conn;
private LinkedList<Connection> pool;
// 2.编写一个构造方法(参数使用了面相对象的多态特性)
public MyConnection(Connection conn,LinkedList<Connection> pool) {
this.conn=conn;
this.pool=pool;
}
//4.书写需要增强的方法
@Override
public void close() throws SQLException {
pool.add(conn);
}
/**
* 此方法必须覆盖!否则会出现空指针异常!!!
*/
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
return conn.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;
}
}
MyDataSource.java:
package cn.itheima.jdbc.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.logging.Logger;
import javax.sql.DataSource;
import cn.itheima.jdbc.utils.JDBCUtils_V3;
public class MyDataSource1 implements DataSource{
//1.创建1个容器用于存储Connection对象
private static LinkedList<Connection> pool = new LinkedList<Connection>();
//2.创建5个连接放到容器中去
static{
for (int i = 0; i < 5; i++) {
Connection conn = JDBCUtils_V3.getConnection();
//放入池子中connection对象已经经过改造了
MyConnection myconn = new MyConnection(conn, pool);
pool.add(myconn);
}
}
/**
* 重写获取连接的方法
*/
@Override
public Connection getConnection() throws SQLException {
Connection conn = null;
//3.使用前先判断
if(pool.size()==0){
//4.池子里面没有,我们再创建一些
for (int i = 0; i < 5; i++) {
conn = JDBCUtils_V3.getConnection();
//放入池子中connection对象已经经过改造了
MyConnection myconn = new MyConnection(conn, pool);
pool.add(myconn);
}
}
//5.从池子里面获取一个连接对象Connection
conn = pool.remove(0);
return conn;
}
/**
* 归还连接对象到连接池中去
*/
public void backConnection(Connection conn){
pool.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;
}
}
测试类:
TestMyDataSource.java:
package cn.itheima.jdbc.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import javax.sql.DataSource;
import org.junit.Test;
import cn.itheima.jdbc.DataSource.MyDataSource;
import cn.itheima.jdbc.DataSource.MyDataSource1;
import cn.itheima.jdbc.utils.JDBCUtils_V3;
public class TestMyDataSource {
/**
* 添加用户
* 使用改造过的connection
*/
@Test
public void testAddUser1() {
Connection conn = null;
PreparedStatement pstmt = null;
// 1.创建自定义连接池对象
DataSource dataSource = new MyDataSource1();
try {
// 2.从池子中获取连接
conn = dataSource.getConnection();
String sql = "insert into tbl_user values(null,?,?)";
//3.必须在自定义的connection类中重写prepareStatement(sql)方法
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "吕布1");
pstmt.setString(2, "貂蝉1");
int rows = pstmt.executeUpdate();
if (rows > 0) {
System.out.println("添加成功!");
} else {
System.out.println("添加失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils_V3.release(conn, pstmt, null);
}
}
}
外置连接池----C3P0(很重要):
1:lib文件夹中导入 c3p0-0.9.1.2.jar 右键Build Path-->add to build path出现小奶瓶
配置文件:
2:src中添加一个c3p0-config.xml文件
3:编写测试类:
1:创建一个工具类(可选):
package cn.itheima.jdbc.utils;
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("itheima");
public static DataSource getDataSource() {
return dataSource;
}
public static Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
2:测试类:
package cn.itheima.jdbc.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import cn.itheima.jdbc.utils.C3P0Utils;
import cn.itheima.jdbc.utils.JDBCUtils_V3;
public class TestC3P0 {
@Test
public void testAddUser1() { //抽取工具类后的
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 2.从池子中获取连接
conn = C3P0Utils.getConnection();
String sql = "insert into tbl_user values(null,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "吕布3");
pstmt.setString(2, "貂蝉3");
int rows = pstmt.executeUpdate();
if (rows > 0) {
System.out.println("添加成功!");
} else {
System.out.println("添加失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils_V3.release(conn, pstmt, null);
}
}
@Test
public void testAddUser() { //未抽取工具类的:
Connection conn = null;
PreparedStatement pstmt = null;
// 1.创建自定义连接池对象
ComboPooledDataSource dataSource = new ComboPooledDataSource();// 加载默认的配置
// ComboPooledDataSource dataSource = new
// ComboPooledDataSource("itheima");//加载有名称的配置
try {
// 2.从池子中获取连接
conn = dataSource.getConnection();
String sql = "insert into tbl_user values(null,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "吕布2");
pstmt.setString(2, "貂蝉2");
int rows = pstmt.executeUpdate();
if (rows > 0) {
System.out.println("添加成功!");
} else {
System.out.println("添加失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtils_V3.release(conn, pstmt, null);
}
}
}
DBCP连接池:
也是一个开源的连接池,是Apache Common的成员之一,在企业开发汇总比较常见,tomcat内置的连接池
第一步:导入jar包(两个)并右击build path添加path:
配置文件:
package cn.itheima.jdbc.utils;
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"); //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 cn.itheima.jdbc.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import cn.itheima.jdbc.utils.DBCPUtils;
public class TestDBCP {
@Test
public void testUpdateUserById(){
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBCPUtils.getConnection();
String sql ="update tbl_user set upassword=? where uid=?";
pstmt= conn.prepareStatement(sql);
pstmt.setString(1, "柳岩");
pstmt.setInt(2, 20);
int rows = pstmt.executeUpdate();
if(rows>0){
System.out.println("更新成功!");
}else{
System.out.println("更新失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}