jdbcs 之连接池和框架
作者：楠羽
- 2022 年 9 月 08 日 福建
本文字数：22854 字
阅读完需：约 75 分钟
📦个人主页：楠羽
🏆简介：一个大二的科班出身的，主要研究 Java 后端开发
⏰座右铭：成功之前我们要做应该做的事情，成功之后才能做我们喜欢的事
💕 过客的你，可以给博主留下一个小小的关注吗？这是给博主最大的支持。以后博主会更新大量的优质的作品！！！！
一、数据库连接池
1.数据库连接池的概念
数据库连接背景
数据库连接是一种关键的、有限的、昂贵的资源，这一点在多用户的网页应用程序中体现得尤为突出。对数据库连接的管理能显著影响到整个应用程序的伸缩性和健壮性，影响到程序的性能指标。数据库连接池正是针对这个问题提出来的。
数据库连接池
数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个。这项技术能明显提高对数据库操作的性能。
数据库连接池原理
2.自定义连接池
java.sql.DataSource 接口：数据源(数据库连接池)。java 官方提供的数据库连接池规范(接口)
获取数据库连接对象：Connection getConnection();
自定义连接池
/* 自定义连接池类*/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) { //从池中获取数据库连接 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; }}
复制代码
3.自定义连接池测试
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()); }}
复制代码
4.归还连接
继承(无法解决)
通过打印连接对象，发现 DriverManager 获取的连接实现类是 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; //4.获取数据库连接的方法 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实现类相同的功能！ 实现步骤： 1.定义一个类，实现Connection接口 2.定义Connection连接对象和连接池容器对象的变量 3.提供有参构造方法，接收连接对象和连接池对象，对变量赋值 4.在close()方法中，完成连接的归还 5.剩余方法，只需要调用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("连接数量已用尽"); } } }
复制代码
适配器设计模式
通过之前 MyConnection2 连接类我们发现，有很多个需要实现的方法。这个时候我们就可以使用适配器设计模式了。提供一个适配器类，实现 Connection 接口，将所有功能进行实现(除了 close 方法)。自定义连接类只需要继承这个适配器类，重写需要改进的 close()方法即可！
适配器类
/* 适配器抽象类。实现Connection接口。 实现所有的方法，调用mysql驱动包中Connection连接对象的方法 */ 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); } }
复制代码
自定义连接类
/* 自定义Connection连接类。通过适配器设计模式。完成close()方法的重写 1.定义一个类，继承适配器父类 2.定义Connection连接对象和连接池容器对象的变量 3.提供有参构造方法，接收连接对象和连接池对象，对变量赋值 4.在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); //通过自定义连接对象进行包装 //MyConnection2 mycon = new MyConnection2(con,pool); 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("连接数量已用尽"); } }*/ }
复制代码
5.开源连接池的使用
C3P0
基本使用
/* 使用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(); // 将连接对象归还池中 } }
复制代码
配置演示
public class C3P0Demo2 { public static void main(String[] args) throws Exception{ //创建c3p0连接池对象 DataSource dataSource = new ComboPooledDataSource(); //获取数据库连接进行使用 for(int i = 1; i <= 11; i++) { Connection con = dataSource.getConnection(); System.out.println(i + ":" + con); if(i == 5) { con.close(); } } } }
复制代码
Druid
基本使用
/* Druid连接池 1.导入jar包 2.编写配置文件，放在src目录下 3.通过Properties集合加载配置文件 4.通过Druid连接池工厂类获取数据库连接池对象 5.获取数据库连接，进行使用 */ 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(); // 将连接对象归还池中 } }
复制代码
抽取工具类
/* 数据库连接池工具类 */ 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); } }
复制代码
二、JDBC 框架(JDBCTemplate)
1.分析前一天案例中的重复代码
dao 层的重复代码
定义必要的信息、获取数据库的连接、释放资源都是重复的代码！
而我们最终的核心功能仅仅只是执行一条 sql 语句而已啊！
所以我们可以抽取出一个 JDBC 模板类，来封装一些方法(update、query)，专门帮我们执行增删改查的 sql 语句！
将之前那些重复的操作，都抽取到模板类中的方法里。就能大大简化我们的使用步骤！
2.自定义 JDBC 框架
2.1 数据库的源信息
DataBaseMetaData(了解)：数据库的源信息
java.sql.DataBaseMetaData：封装了整个数据库的综合信息
例如：
String getDatabaseProductName()：获取数据库产品的名称
int getDatabaseProductVersion()：获取数据库产品的版本号
ParameterMetaData：参数的源信息
java.sql.ParameterMetaData：封装的是预编译执行者对象中每个参数的类型和属性
这个对象可以通过预编译执行者对象中的 getParameterMetaData()方法来获取
核心功能：
int getParameterCount()：获取 sql 语句中参数的个数
ResultSetMetaData：结果集的源信息
java.sql.ResultSetMetaData：封装的是结果集对象中列的类型和属性
这个对象可以通过结果集对象中的 getMetaData()方法来获取
核心功能：
int getColumnCount()：获取列的总数
String getColumnName(int i)：获取列名
2.2JDBCTemplate 类增删改功能的编写
public class JDBCTemplate { private DataSource dataSource; private Connection con; private PreparedStatement pst; private ResultSet rs;
public JDBCTemplate(DataSource dataSource) { this.dataSource = dataSource; }
//专用于执行增删改sql语句的方法 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]); }
//执行sql语句 result = pst.executeUpdate();
} catch(Exception e) { e.printStackTrace(); } finally { //释放资源 DataSourceUtils.close(con,pst); }
//返回结果 return result; }}
复制代码
2.3JDBCTemplate 类查询功能的编写
实体类
/* 学生实体类 */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; }
@Override public String toString() { return "Student{" + "sid=" + sid + ", name='" + name + '\'' + ", age=" + age + ", birthday=" + birthday + '}'; }}
复制代码
ResultSetHandler 接口
/* 用于处理结果集的接口 */public interface ResultSetHandler<T> { //处理结果集的抽象方法。 <T> T handler(ResultSet rs);}
复制代码
BeanHandler 实现类
/* 实现类1：用于完成将查询出来的一条记录，封装到Student对象中 */public class BeanHandler<T> implements ResultSetHandler<T> { //1.声明对象类型变量 private Class<T> beanClass;
//2.有参构造对变量赋值 public BeanHandler(Class<T> beanClass) { this.beanClass = beanClass; }
/* 将ResultSet结果集中的数据封装到beanClass类型对象中 */ @Override public 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<T> implements ResultSetHandler<T> {
//1.声明对象变量 private Class<T> beanClass;
//2.有参构造为变量赋值 public BeanListHandler(Class<T> beanClass) { this.beanClass = beanClass; }
@Override public List<T> handler(ResultSet rs) { //3.创建集合对象 List<T> 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<T> implements ResultSetHandler<T> { @Override public 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<Long> 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 <T> List<T> queryForList(String sql, ResultSetHandler<T> rsh, Object...objs) { List<T> 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> T queryForObject(String sql, ResultSetHandler<T> 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; }}
复制代码
2.4 测试自定义 JDBC 框架的使用
public class JDBCTemplateTest { //创建JDBCTemplate对象 JDBCTemplate template = new JDBCTemplate(DataSourceUtils.getDataSource());
@Test public void selectScalar() { //查询student表的记录条数 String sql = "SELECT COUNT(*) FROM student"; Long count = template.queryForScalar(sql, new ScalarHandler<Long>()); System.out.println(count); }
@Test public void selectAll() { //查询所有学生信息 String sql = "SELECT * FROM student"; List<Student> list = template.queryForList(sql, new BeanListHandler<Student>(Student.class)); for(Student stu : list) { System.out.println(stu); } }
@Test public void selectOne() { //查询张三这条记录 String sql = "SELECT * FROM student WHERE sid=?"; //通过BeanHandler将结果封装成一个Student对象 Student stu = template.queryForObject(sql, new BeanHandler<Student>(Student.class), 1); System.out.println(stu); }
@Test public 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); }
@Test public void delete() { //删除周七这条记录 String sql = "DELETE FROM student WHERE sid=?"; int result = template.update(sql, 5); System.out.println(result); }
@Test public void update() { //修改张三的年龄为33 String sql = "UPDATE student SET age=? WHERE name=?"; Object[] params = {33,"张三"}; int result = template.update(sql,params); System.out.println(result); }}
复制代码
划线
评论
复制
发布于: 刚刚阅读数: 4
版权声明: 本文为 InfoQ 作者【楠羽】的原创文章。
原文链接:【http://xie.infoq.cn/article/195fdb951362ca0e2d6d3c24d】。文章转载请联系作者。
楠羽
关注
还未添加个人签名 2022.08.04 加入
还未添加个人简介
评论