JDBC-02-授课笔记
...大约 20 分钟
JDBC-02-授课笔记
一、数据库连接池
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);
}
} 赞助
