MySQL数据库（6）--数据库连接池
1 数据库连接池
2 自定义连接池 2.1 简单的连接池
1.链接：
提取码：1234
在百度网盘内导入jar包
2.java.sql.接口：数据源(数据库连接池)。java官方提供的数据库连接池规范(接口)
/*自定义连接池类
*/
public class MyDataSource implements DataSource{//定义集合容器，用于保存多个数据库连接对象private static List pool = Collections.synchronizedList(new ArrayList());//静态代码块，生成10个数据库连接保存到集合中static {for (int i = 0; i < 10; i++) {Connection con = JDBCUtils.getConnection();pool.add(con);}}//返回连接池的大小public int getSize() {return pool.size();}//从池中返回一个数据库连接@Overridepublic Connection getConnection() {if(pool.size() > 0) {//从池中获取数据库连接return pool.remove(0);}else {throw new RuntimeException("连接数量已用尽");}}@Overridepublic Connection getConnection(String username, String password) throws SQLException {return null;}@Overridepublic T unwrap(Class iface) throws SQLException {return null;}@Overridepublic boolean isWrapperFor(Class iface) throws SQLException {return false;}@Overridepublic PrintWriter getLogWriter() throws SQLException {return null;}@Overridepublic void setLogWriter(PrintWriter out) throws SQLException {}@Overridepublic void setLoginTimeout(int seconds) throws SQLException {}@Overridepublic int getLoginTimeout() throws SQLException {return 0;}@Overridepublic Logger getParentLogger() throws SQLFeatureNotSupportedException {return null;}
}自定义连接池测试
public class MyDataSourceTest {public static void main(String[] args) throws Exception{//创建数据库连接池对象MyDataSource dataSource = new MyDataSource();System.out.println("使用之前连接池数量：" + dataSource.getSize());//获取数据库连接对象Connection con = dataSource.getConnection();System.out.println(con.getClass()); // （结果）JDBC4Connection//查询学生表全部信息String sql = "SELECT * FROM student";PreparedStatement pst = con.prepareStatement(sql);ResultSet rs = pst.executeQuery();while(rs.next()) {System.out.println(rs.getInt("sid") + "\t" + rs.getString("name") + "\t" + rs.getInt("age") + "\t" + rs.getDate("birthday"));}//释放资源rs.close();pst.close();//目前的连接对象close方法，是直接关闭连接，而不是将连接归还池中con.close();System.out.println("使用之后连接池数量：" + dataSource.getSize());}
} 2.2 归还连接 （1）错误演示
为什么我们要有归还连接呢？
通过上面的代码运行后发现，运行前连接池的数量是10，运行后连接池的数量为9.而close方法是直接关闭连接，而不是将连接归还池中。所以我们有了归还连接，通过上述的案例我们中有一个con.方法，用来获取调用该方法的对象的类，发现是。所以我们有了一个想法，自定义一个类，继承这个类，咱们去重写close方法。
/*自定义Connection类*/
public class MyConnection1 extends JDBC4Connection {//声明连接对象和连接池集合对象private Connection con;private List pool;//通过构造方法给成员变量赋值public MyConnection1(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url,Connection con,List pool) throws SQLException {super(hostToConnectTo, portToConnectTo, info, databaseToConnectTo, url);this.con = con;this.pool = pool;}//重写close()方法，将连接归还给池中@Overridepublic void close() throws SQLException {pool.add(con);}
} 在MyDataSource中修改。
//将之前的连接对象换成自定义的子类对象
private static MyConnection1 con;//4.获取数据库连接的方法
public static Connection getConnection() {try {//等效于：MyConnection1 con = new JDBC4Connection(); 语法错误！con = DriverManager.getConnection(url,username,password);//报错，不对应} catch (SQLException e) {e.printStackTrace();}return con;
}最终：但是这种方式行不通，通过查看JDBC工具类获取连接的方法我们发现：我们虽然自定义了一个子类，完成了归还连接的操作。但是获取的还是这个对象，并不是我们的子类对象。而我们又不能整体去修改驱动包中类的功能！
（2）用装饰设计模式去解决
/*自定义Connection类。通过装饰设计模式，实现和mysql驱动包中的Connection实现类相同的功能！实现步骤：1.定义一个类，实现Connection接口2.定义Connection连接对象和连接池容器对象的变量3.提供有参构造方法，接收连接对象和连接池对象，对变量赋值4.在close()方法中，完成连接的归还5.剩余方法，只需要调用mysql驱动包的连接对象完成即可*/
public class MyConnection2 implements Connection {//2.定义Connection连接对象和连接池容器对象的变量private Connection con;private List pool;//3.提供有参构造方法，接收连接对象和连接池对象，对变量赋值public MyConnection2(Connection con,List pool) {this.con = con;this.pool = pool;}//4.在close()方法中，完成连接的归还@Overridepublic void close() throws SQLException {pool.add(con);}
//注意下面代码的有返回值，自动生成后需要修改返回值。@Overridepublic Statement createStatement() throws SQLException {return con.createStatement();}@Overridepublic PreparedStatement prepareStatement(String sql) throws SQLException {return con.prepareStatement(sql);}@Overridepublic CallableStatement prepareCall(String sql) throws SQLException {return con.prepareCall(sql);}@Overridepublic String nativeSQL(String sql) throws SQLException {return con.nativeSQL(sql);}@Overridepublic void setAutoCommit(boolean autoCommit) throws SQLException {con.setAutoCommit(autoCommit);}@Overridepublic boolean getAutoCommit() throws SQLException {return con.getAutoCommit();}@Overridepublic void commit() throws SQLException {con.commit();}@Overridepublic void rollback() throws SQLException {con.rollback();}@Overridepublic boolean isClosed() throws SQLException {return con.isClosed();}@Overridepublic DatabaseMetaData getMetaData() throws SQLException {return con.getMetaData();}@Overridepublic void setReadOnly(boolean readOnly) throws SQLException {con.setReadOnly(readOnly);}@Overridepublic boolean isReadOnly() throws SQLException {return con.isReadOnly();}@Overridepublic void setCatalog(String catalog) throws SQLException {con.setCatalog(catalog);}@Overridepublic String getCatalog() throws SQLException {return con.getCatalog();}@Overridepublic void setTransactionIsolation(int level) throws SQLException {con.setTransactionIsolation(level);}@Overridepublic int getTransactionIsolation() throws SQLException {return con.getTransactionIsolation();}@Overridepublic SQLWarning getWarnings() throws SQLException {return con.getWarnings();}@Overridepublic void clearWarnings() throws SQLException {con.clearWarnings();}@Overridepublic Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {return con.createStatement(resultSetType,resultSetConcurrency);}@Overridepublic PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {return con.prepareStatement(sql,resultSetType,resultSetConcurrency);}@Overridepublic CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {return con.prepareCall(sql,resultSetType,resultSetConcurrency);}@Overridepublic Map> getTypeMap() throws SQLException {return con.getTypeMap();}@Overridepublic void setTypeMap(Map> map) throws SQLException {con.setTypeMap(map);}@Overridepublic void setHoldability(int holdability) throws SQLException {con.setHoldability(holdability);}@Overridepublic int getHoldability() throws SQLException {return con.getHoldability();}@Overridepublic Savepoint setSavepoint() throws SQLException {return con.setSavepoint();}@Overridepublic Savepoint setSavepoint(String name) throws SQLException {return con.setSavepoint(name);}@Overridepublic void rollback(Savepoint savepoint) throws SQLException {con.rollback(savepoint);}@Overridepublic void releaseSavepoint(Savepoint savepoint) throws SQLException {con.releaseSavepoint(savepoint);}@Overridepublic Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {return con.createStatement(resultSetType,resultSetConcurrency,resultSetHoldability);}@Overridepublic PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {return con.prepareStatement(sql,resultSetType,resultSetConcurrency,resultSetHoldability);}@Overridepublic CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {return con.prepareCall(sql,resultSetType,resultSetConcurrency,resultSetHoldability);}@Overridepublic PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {return con.prepareStatement(sql,autoGeneratedKeys);}@Overridepublic PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {return con.prepareStatement(sql,columnIndexes);}@Overridepublic PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {return con.prepareStatement(sql,columnNames);}@Overridepublic Clob createClob() throws SQLException {return con.createClob();}@Overridepublic Blob createBlob() throws SQLException {return con.createBlob();}@Overridepublic NClob createNClob() throws SQLException {return con.createNClob();}@Overridepublic SQLXML createSQLXML() throws SQLException {return con.createSQLXML();}@Overridepublic boolean isValid(int timeout) throws SQLException {return con.isValid(timeout);}@Overridepublic void setClientInfo(String name, String value) throws SQLClientInfoException {con.setClientInfo(name,value);}@Overridepublic void setClientInfo(Properties properties) throws SQLClientInfoException {con.setClientInfo(properties);}@Overridepublic String getClientInfo(String name) throws SQLException {return con.getClientInfo(name);}@Overridepublic Properties getClientInfo() throws SQLException {return con.getClientInfo();}@Overridepublic Array createArrayOf(String typeName, Object[] elements) throws SQLException {return con.createArrayOf(typeName,elements);}@Overridepublic Struct createStruct(String typeName, Object[] attributes) throws SQLException {return con.createStruct(typeName,attributes);}@Overridepublic void setSchema(String schema) throws SQLException {con.setSchema(schema);}@Overridepublic String getSchema() throws SQLException {return con.getSchema();}@Overridepublic void abort(Executor executor) throws SQLException {con.abort(executor);}@Overridepublic void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {con.setNetworkTimeout(executor,milliseconds);}@Overridepublic int getNetworkTimeout() throws SQLException {return con.getNetworkTimeout();}@Overridepublic T unwrap(Class iface) throws SQLException {return con.unwrap(iface);}@Overridepublic boolean isWrapperFor(Class iface) throws SQLException {return con.isWrapperFor(iface);}
} public class MyDataSource implements DataSource{//定义集合容器，用于保存多个数据库连接对象private static List pool = Collections.synchronizedList(new ArrayList());//静态代码块，生成10个数据库连接保存到集合中static {for (int i = 0; i < 10; i++) {Connection con = JDBCUtils.getConnection();pool.add(con);}}//返回连接池的大小public int getSize() {return pool.size();}//从池中返回一个数据库连接@Overridepublic Connection getConnection() {if(pool.size() > 0) {//从池中获取数据库连接Connection con = pool.remove(0);//通过自定义连接对象进行包装MyConnection2 mycon = new MyConnection2(con,pool);//返回包装后的连接对象return mycon;}else {throw new RuntimeException("连接数量已用尽");}}
} （3）适配器模式
通过之前连接类我们发现，发现装饰设计模式还是麻烦，有很多个需要实现的方法。这个时候我们就可以使用适配器设计模式了。提供一个适配器类，实现接口，将所有功能进行实现(除了close方法)。自定义连接类只需要继承这个适配器类，重写需要改进的close()方法即可！
public abstract class MyAdapter implements Connection {// 定义数据库连接对象的变量private Connection con;// 通过构造方法赋值public MyAdapter(Connection con) {this.con = con;}// 所有的方法，均调用mysql的连接对象实现@Overridepublic Statement createStatement() throws SQLException {return con.createStatement();}@Overridepublic PreparedStatement prepareStatement(String sql) throws SQLException {return con.prepareStatement(sql);}@Overridepublic CallableStatement prepareCall(String sql) throws SQLException {return con.prepareCall(sql);}@Overridepublic String nativeSQL(String sql) throws SQLException {return con.nativeSQL(sql);}@Overridepublic void setAutoCommit(boolean autoCommit) throws SQLException {con.setAutoCommit(autoCommit);}@Overridepublic boolean getAutoCommit() throws SQLException {return con.getAutoCommit();}@Overridepublic void commit() throws SQLException {con.commit();}@Overridepublic void rollback() throws SQLException {con.rollback();}@Overridepublic boolean isClosed() throws SQLException {return con.isClosed();}@Overridepublic DatabaseMetaData getMetaData() throws SQLException {return con.getMetaData();}@Overridepublic void setReadOnly(boolean readOnly) throws SQLException {con.setReadOnly(readOnly);}@Overridepublic boolean isReadOnly() throws SQLException {return con.isReadOnly();}@Overridepublic void setCatalog(String catalog) throws SQLException {con.setCatalog(catalog);}@Overridepublic String getCatalog() throws SQLException {return con.getCatalog();}@Overridepublic void setTransactionIsolation(int level) throws SQLException {con.setTransactionIsolation(level);}@Overridepublic int getTransactionIsolation() throws SQLException {return con.getTransactionIsolation();}@Overridepublic SQLWarning getWarnings() throws SQLException {return con.getWarnings();}@Overridepublic void clearWarnings() throws SQLException {con.clearWarnings();}@Overridepublic Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {return con.createStatement(resultSetType,resultSetConcurrency);}@Overridepublic PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {return con.prepareStatement(sql,resultSetType,resultSetConcurrency);}@Overridepublic CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {return con.prepareCall(sql,resultSetType,resultSetConcurrency);}@Overridepublic Map> getTypeMap() throws SQLException {return con.getTypeMap();}@Overridepublic void setTypeMap(Map> map) throws SQLException {con.setTypeMap(map);}@Overridepublic void setHoldability(int holdability) throws SQLException {con.setHoldability(holdability);}@Overridepublic int getHoldability() throws SQLException {return con.getHoldability();}@Overridepublic Savepoint setSavepoint() throws SQLException {return con.setSavepoint();}@Overridepublic Savepoint setSavepoint(String name) throws SQLException {return con.setSavepoint(name);}@Overridepublic void rollback(Savepoint savepoint) throws SQLException {con.rollback(savepoint);}@Overridepublic void releaseSavepoint(Savepoint savepoint) throws SQLException {con.releaseSavepoint(savepoint);}@Overridepublic Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {return con.createStatement(resultSetType,resultSetConcurrency,resultSetHoldability);}@Overridepublic PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {return con.prepareStatement(sql,resultSetType,resultSetConcurrency,resultSetHoldability);}@Overridepublic CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {return con.prepareCall(sql,resultSetType,resultSetConcurrency,resultSetHoldability);}@Overridepublic PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {return con.prepareStatement(sql,autoGeneratedKeys);}@Overridepublic PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {return con.prepareStatement(sql,columnIndexes);}@Overridepublic PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {return con.prepareStatement(sql,columnNames);}@Overridepublic Clob createClob() throws SQLException {return con.createClob();}@Overridepublic Blob createBlob() throws SQLException {return con.createBlob();}@Overridepublic NClob createNClob() throws SQLException {return con.createNClob();}@Overridepublic SQLXML createSQLXML() throws SQLException {return con.createSQLXML();}@Overridepublic boolean isValid(int timeout) throws SQLException {return con.isValid(timeout);}@Overridepublic void setClientInfo(String name, String value) throws SQLClientInfoException {con.setClientInfo(name,value);}@Overridepublic void setClientInfo(Properties properties) throws SQLClientInfoException {con.setClientInfo(properties);}@Overridepublic String getClientInfo(String name) throws SQLException {return con.getClientInfo(name);}@Overridepublic Properties getClientInfo() throws SQLException {return con.getClientInfo();}@Overridepublic Array createArrayOf(String typeName, Object[] elements) throws SQLException {return con.createArrayOf(typeName,elements);}@Overridepublic Struct createStruct(String typeName, Object[] attributes) throws SQLException {return con.createStruct(typeName,attributes);}@Overridepublic void setSchema(String schema) throws SQLException {con.setSchema(schema);}@Overridepublic String getSchema() throws SQLException {return con.getSchema();}@Overridepublic void abort(Executor executor) throws SQLException {con.abort(executor);}@Overridepublic void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {con.setNetworkTimeout(executor,milliseconds);}@Overridepublic int getNetworkTimeout() throws SQLException {return con.getNetworkTimeout();}@Overridepublic T unwrap(Class iface) throws SQLException {return con.unwrap(iface);}@Overridepublic boolean isWrapperFor(Class iface) throws SQLException {return con.isWrapperFor(iface);}
} 自 定 义 连 接 类
/*自定义Connection连接类。通过适配器设计模式。完成close()方法的重写1.定义一个类，继承适配器父类2.定义Connection连接对象和连接池容器对象的变量3.提供有参构造方法，接收连接对象和连接池对象，对变量赋值4.在close()方法中，完成连接的归还*/
public class MyConnection3 extends MyAdapter {//2.定义Connection连接对象和连接池容器对象的变量private Connection con;private List pool;//3.提供有参构造方法，接收连接对象和连接池对象，对变量赋值public MyConnection3(Connection con,List pool) {super(con); // 将接收的数据库连接对象给适配器父类传递this.con = con;this.pool = pool;}//4.在close()方法中，完成连接的归还@Overridepublic void close() throws SQLException {pool.add(con);}
} 自定义连接池类
public class MyDataSource implements DataSource{//定义集合容器，用于保存多个数据库连接对象private static List pool = Collections.synchronizedList(new ArrayList());//静态代码块，生成10个数据库连接保存到集合中static {for (int i = 0; i < 10; i++) {Connection con = JDBCUtils.getConnection();pool.add(con);}}//返回连接池的大小public int getSize() {return pool.size();}//从池中返回一个数据库连接@Overridepublic 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("连接数量已用尽");}}
} Test类
public class MyDataSourceTest {public static void main(String[] args) throws Exception{MyDataSource dataSource = new MyDataSource();System.out.println("使用之前连接池数量：" + dataSource.getSize());Connection con = dataSource.getConnection();System.out.println(con.getClass());// JDBC4ConnectionString sql = "SELECT * FROM student";PreparedStatement pst = con.prepareStatement(sql);ResultSet rs = pst.executeQuery();while(rs.next()) {System.out.println(rs.getInt("sid") + "\t" + rs.getString("name") + "\t" + rs.getInt("age") + "\t" + rs.getDate("birthday"));}rs.close();pst.close();con.close();System.out.println("使用之后连接池数量：" + dataSource.getSize());}
}（4）动态代理
经过我们适配器模式的改进，自定义连接类中的方法已经很简洁了。剩余所有的方法已经抽取到了适配器类中。但是适配器这个类还是我们自己编写的，也比较麻烦！所以可以使用动态代理的方式来改进。
自 定 义 数 据 库 连 接 池
public class MyDataSource implements DataSource{//定义集合容器，用于保存多个数据库连接对象private static List pool = Collections.synchronizedList(new ArrayList());//静态代码块，生成10个数据库连接保存到集合中static {for (int i = 0; i < 10; i++) {Connection con = JDBCUtils.getConnection();pool.add(con);}}//返回连接池的大小public int getSize() {return pool.size();}//动态代理方式@Overridepublic 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方法，则将连接还回池中如果不是，直接执行实现类的原有方法*/@Overridepublic 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("连接数量已用尽");}}//从池中返回一个数据库连接/*@Overridepublic 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("连接数量已用尽");}}*/
} 2.3 开源连接池的使用 （1）C3p0
PS：jar包
提取码：1234
/*使用C3P0连接池1.导入jar包2.导入配置文件到src目录下3.创建c3p0连接池对象4.获取数据库连接进行使用*/
public class C3P0Demo1 {public static void main(String[] args) throws Exception{//创建c3p0连接池对象DataSource dataSource = new ComboPooledDataSource();//获取数据库连接进行使用Connection con = dataSource.getConnection();//查询全部学生信息String sql = "SELECT * FROM student";PreparedStatement pst = con.prepareStatement(sql);ResultSet rs = pst.executeQuery();while(rs.next()) {System.out.println(rs.getInt("sid") + "\t" + rs.getString("name") + "\t" + rs.getInt("age") + "\t" + rs.getDate("birthday"));}//释放资源rs.close();pst.close();con.close(); // 将连接对象归还池中}
}（2）Druid（德鲁伊）
过程：
1.导入jar包
提取码：1234
2.编写配置文件druid.（在链接里，下面我也再书写了一次），放在里面
3.通过集合加载配置文件
用一个File命名为：druid.
=com.mysql.jdbc.
url=jdbc:mysql://:3306/db15
=root
==*jo&
#初始连接数
=5
#最大连接数
=10
#最大等待时间（毫秒），超了之后就爆错了
=3000
public class DruidDemo1 {public static void main(String[] args)throws Exception {//通过Properties集合加载配置文件InputStream is = DruidDemo1.class.getClassLoader().getResourceAsStream("druid.properties");Properties prop = new Properties();prop.load(is);//通过Druid连接池工厂类获取数据库连接池对象DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);//获取数据库连接，进行使用Connection con = dataSource.getConnection();//查询全部学生信息String sql = "SELECT * FROM student";PreparedStatement pst = con.prepareStatement(sql);ResultSet rs = pst.executeQuery();while(rs.next()) {System.out.println(rs.getInt("sid") + "\t" + rs.getString("name") + "\t" + rs.getInt("age") + "\t" + rs.getDate("birthday"));}//释放资源rs.close();pst.close();con.close(); // 将连接对象归还池中}
}2.4 数据库连接池工具类
/*数据库连接池工具类*/
public class DataSourceUtils {//1.私有构造方法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 class DruidDemo2{public static void main(String[] args) throws Exception {Connection con =DataSourceUtils.getConnection();String sql ="SELECT * FROM student";PreparedStatement pst =con.prepareStatement(sql);ResultSet rs = pst.executeQuery();while(rs.next()){System.out.println(rs.getInt("sid")+"\t"+rs.getString("NAME")+"\t"+rs.getInt("age")+"\t"+rs.getDate("birthday"));}rs.close();pst.close();con.close();}
}2 JDBC框架 2.1 为什么要有JDBC框架
dao层的重复代码
定义必要的信息、获取数据库的连接、释放资源都是重复的代码！
而我们最终的核心功能仅仅只是执行一条sql语句而已啊！
所以我们可以抽取出一个JDBC模板类，来封装一些方法(、query)，专门帮我们执行增删改查的sql语句！
将之前那些重复的操作，都抽取到模板类中的方法里。就能大大简化我们的使用步骤！
2.2 自定义JDBC框架 （1）数据库的源信息 （2）练习
/*学生实体类*/
public class Student {private Integer sid;private String name;private Integer age;private Date birthday;public Student() {}public Student(Integer sid, String name, Integer age, Date birthday) {this.sid = sid;this.name = name;this.age = age;this.birthday = birthday;}public Integer getSid() {return sid;}public void setSid(Integer sid) {this.sid = sid;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}@Overridepublic String toString() {return "Student{" +"sid=" + sid +", name='" + name + '\'' +", age=" + age +", birthday=" + birthday +'}';}
}ResultSetHandler接口
/*用于处理结果集的接口*/
public interface ResultSetHandler {//处理结果集的抽象方法。 T handler(ResultSet rs);
}BeanHandler实现类
/*实现类1：用于完成将查询出来的一条记录，封装到Student对象中*/
public class BeanHandler implements ResultSetHandler {//1.声明对象类型变量private Class beanClass;//2.有参构造对变量赋值public BeanHandler(Class beanClass) {this.beanClass = beanClass;}/*将ResultSet结果集中的数据封装到beanClass类型对象中*/@Overridepublic T handler(ResultSet rs) {//3.声明对象T bean = null;try{//4.创建传递参数的对象bean = beanClass.newInstance();//5.判断是否有结果集if(rs.next()) {//6.得到所有的列名//6.1先得到结果集的源信息ResultSetMetaData rsmd = rs.getMetaData();//6.2还要得到有多少列int columnCount = rsmd.getColumnCount();//6.3遍历列数for(int i = 1; i <= columnCount; i++) {//6.4得到每列的列名String columnName = rsmd.getColumnName(i);//6.5通过列名获取数据Object columnValue = rs.getObject(columnName);//6.6列名其实就是对象中成员变量的名称。于是就可以使用列名得到对象中属性的描述器(get和set方法)PropertyDescriptor pd = new PropertyDescriptor(columnName.toLowerCase(),beanClass);//6.7获取set方法Method writeMethod = pd.getWriteMethod();//6.8执行set方法，给成员变量赋值writeMethod.invoke(bean,columnValue);}}} catch (Exception e) {e.printStackTrace();}//7.将对象返回return bean;}
}BeanListHandler实现类
/*实现类2：用于将结果集封装到集合中*/
public class BeanListHandler implements ResultSetHandler {//1.声明对象变量private Class beanClass;//2.有参构造为变量赋值public BeanListHandler(Class beanClass) {this.beanClass = beanClass;}@Overridepublic List handler(ResultSet rs) {//3.创建集合对象List list = new ArrayList<>();try{//4.遍历结果集对象while(rs.next()) {//5.创建传递参数的对象T bean = beanClass.newInstance();//6.得到所有的列名//6.1先得到结果集的源信息ResultSetMetaData rsmd = rs.getMetaData();//6.2还要得到有多少列int columnCount = rsmd.getColumnCount();//6.3遍历列数for(int i = 1; i <= columnCount; i++) {//6.4得到每列的列名String columnName = rsmd.getColumnName(i);//6.5通过列名获取数据Object columnValue = rs.getObject(columnName);//6.6列名其实就是对象中成员变量的名称。于是就可以使用列名得到对象中属性的描述器(get和set方法)PropertyDescriptor pd = new PropertyDescriptor(columnName.toLowerCase(),beanClass);//6.7获取set方法Method writeMethod = pd.getWriteMethod();//6.8执行set方法，给成员变量赋值writeMethod.invoke(bean,columnValue);}//7.将对象保存到集合中list.add(bean);}} catch (Exception e) {e.printStackTrace();}//8.返回结果return list;}
}ScalarHandler实现类
/*实现类3：用于返回一个聚合函数的查询结果*/
public class ScalarHandler implements ResultSetHandler {@Overridepublic Long handler(ResultSet rs) {//1.声明一个变量Long value = null;try{//2.判断是否有结果if(rs.next()) {//3.获取结果集的源信息ResultSetMetaData rsmd = rs.getMetaData();//4.获取第一列的列名String columnName = rsmd.getColumnName(1);//5.根据列名获取值value = rs.getLong(columnName);}} catch(Exception e) {e.printStackTrace();}//6.将结果返回return value;}
}JDBCTemplate类public class JDBCTemplate {private DataSource dataSource;private Connection con;private PreparedStatement pst;private ResultSet rs;public JDBCTemplate(DataSource dataSource) {this.dataSource = dataSource;}/*专用于执行聚合函数sql语句的方法*/public Long queryForScalar(String sql, ResultSetHandler rsh, Object...objs) {Long result = null;try{con = dataSource.getConnection();pst = con.prepareStatement(sql);//获取sql语句中的参数源信息ParameterMetaData pData = pst.getParameterMetaData();int parameterCount = pData.getParameterCount();//判断参数个数是否一致if(parameterCount != objs.length) {throw new RuntimeException("参数个数不匹配");}//为sql语句中的?占位符赋值for (int i = 0; i < objs.length; i++) {pst.setObject(i+1,objs[i]);}//执行sql语句rs = pst.executeQuery();//通过ScalarHandler方式对结果进行处理result = rsh.handler(rs);} catch(Exception e) {e.printStackTrace();} finally {//释放资源DataSourceUtils.close(con,pst,rs);}//将结果返回return result;}/*专用于查询所有记录sql语句的方法*/public List queryForList(String sql, ResultSetHandler rsh, Object...objs) {List list = new ArrayList<>();try{con = dataSource.getConnection();pst = con.prepareStatement(sql);//获取sql语句中的参数源信息ParameterMetaData pData = pst.getParameterMetaData();int parameterCount = pData.getParameterCount();//判断参数个数是否一致if(parameterCount != objs.length) {throw new RuntimeException("参数个数不匹配");}//为sql语句中的?占位符赋值for (int i = 0; i < objs.length; i++) {pst.setObject(i+1,objs[i]);}//执行sql语句rs = pst.executeQuery();//通过BeanListHandler方式对结果进行处理list = rsh.handler(rs);} catch(Exception e) {e.printStackTrace();} finally {//释放资源DataSourceUtils.close(con,pst,rs);}//将结果返回return list;}/*专用于执行查询一条记录sql语句的方法*/public T queryForObject(String sql, ResultSetHandler rsh, Object...objs) {T obj = null;try{con = dataSource.getConnection();pst = con.prepareStatement(sql);//获取sql语句中的参数源信息ParameterMetaData pData = pst.getParameterMetaData();int parameterCount = pData.getParameterCount();//判断参数个数是否一致if(parameterCount != objs.length) {throw new RuntimeException("参数个数不匹配");}//为sql语句中的?占位符赋值for (int i = 0; i < objs.length; i++) {pst.setObject(i+1,objs[i]);}//执行sql语句rs = pst.executeQuery();//通过BeanHandler方式对结果进行处理obj = rsh.handler(rs);} catch(Exception e) {e.printStackTrace();} finally {//释放资源DataSourceUtils.close(con,pst,rs);}//将结果返回return obj;}public int update(String sql,Object...objs){int result = 0;try {con = dataSource.getConnection();pst = con.prepareStatement(sql);//获取sql语句中的参数源信息ParameterMetaData pData = pst.getParameterMetaData();//获取sql语句中参数的个数int parameterCount = pData.getParameterCount();//判断参数个数是否一致if (parameterCount != objs.length){throw new RuntimeException("参数个数不匹配");}//为sql语句中的占位符赋值for (int i = 0;i< objs.length;i++){pst.setObject(i+1,objs[i]);}result = pst.executeUpdate();}catch (Exception e){e.printStackTrace();}finally {DataSourceUtils.close(con,pst);}return result;}
}TEST类测试public class JDBCTemplateTest {//创建JDBCTemplate对象JDBCTemplate template = new JDBCTemplate(DataSourceUtils.getDataSource());@Testpublic void selectScalar() {//查询student表的记录条数String sql = "SELECT COUNT(*) FROM student";Long count = template.queryForScalar(sql, new ScalarHandler());System.out.println(count);}@Testpublic void selectAll() {//查询所有学生信息String sql = "SELECT * FROM student";List list = template.queryForList(sql, new BeanListHandler(Student.class));for(Student stu : list) {System.out.println(stu);}}@Testpublic void selectOne() {//查询张三这条记录String sql = "SELECT * FROM student WHERE sid=?";//通过BeanHandler将结果封装成一个Student对象Student stu = template.queryForObject(sql, new BeanHandler(Student.class), 1);System.out.println(stu);}@Testpublic void insert() {//新增周七记录String sql = "INSERT INTO student VALUES (?,?,?,?)";Object[] params = {5,"周七",27,"2007-07-07"};int result = template.update(sql, params);System.out.println(result);}@Testpublic void delete() {//删除周七这条记录String sql = "DELETE FROM student WHERE sid=?";int result = template.update(sql, 5);System.out.println(result);}@Testpublic void update() {//修改张三的年龄为33String sql = "UPDATE student SET age=? WHERE name=?";Object[] params = {33,"张三"};int result = template.update(sql,params);System.out.println(result);}
} 
