/* 自定义连接池类 */ 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; } }
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()); } }
继承(无法解决)
/* 自定义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); } }
//将之前的连接对象换成自定义的子类对象 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("连接数量已用尽"); } } }
适配器设计模式
/* 适配器抽象类。实现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("连接数量已用尽"); } }*/ }
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); } }
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; } }
/* 学生实体类 */ 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 + '}'; } }
/* 用于处理结果集的接口 */ public interface ResultSetHandler<T> { //处理结果集的抽象方法。 <T> T handler(ResultSet rs); }
/* 实现类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; } }
/* 实现类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; } }
/* 实现类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; } }
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; } }
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); } }