JDBC之自定义数据库资源连接池并封装到数据库工具类

Java为连接池实现提供了一个规范(接口),规范的写法,我们需要实现DataSource接口!

1.定义数据库的资源连接池 MyDbPool

package com.scc.utils; import javax.sql.DataSource; import java.io.InputStream; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.SQLFeatureNotSupportedException; import java.util.ArrayList; import java.util.Collections; import java.util.List; import java.util.Properties; import java.util.logging.Logger; /** * scc */ public class MyDbPool implements DataSource { private static String driverClass = null; private static String url = null; private static String user = null; private static String password = null; private static List<Connection> connections; static { connections= Collections.synchronizedList(new ArrayList<>()); try { //加载属性文件 InputStream is = MyDbPool.class.getClassLoader().getResourceAsStream("db.properties"); //创建一个集合 Properties properties = new Properties(); //从流中加载 properties.load(is); //获取相应的属性值 driverClass = properties.getProperty("driverClass"); url = properties.getProperty("url"); user = properties.getProperty("user"); password = properties.getProperty("password"); System.out.println(driverClass+" "+url+"..."+user+"..."+password); //注册驱动 Class.forName(driverClass); for (int i = 0; i < 20; i++) { connections.add(DriverManager.getConnection(url, user, password)); } System.out.println("数据库连接池初始化完成"); } catch (Exception e) { e.printStackTrace(); } } @Override public Connection getConnection() throws SQLException { synchronized (connections){ if(connections.size()>0){ Connection connection = connections.remove(0); System.out.println("使用了:"+connection.toString()); return connection; } } return null; } //释放资源 public static void release(Connection conn){ System.out.println("归还了:"+conn.toString()); connections.add(conn); } @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; } }

2.封装DButils工具类

package com.scc.utils; import java.beans.PropertyDescriptor; import java.lang.reflect.Method; import java.sql.*; import java.util.ArrayList; import java.util.List; /** * scc * 1.注册驱动 * 2.获取连接 * 3.释放资源 * 4.执行增删改 * 5.查 */ public class DBUtils { private static MyDbPool mypool; //注册 static { mypool=new MyDbPool(); } //数据库连接 public static Connection getConnection() { try { return mypool.getConnection(); } catch (SQLException e) { e.printStackTrace(); System.out.println("连接失败"); } return null; } //关流 public static void closeAll(PreparedStatement pstat, Connection conn, ResultSet res) { try { if (res != null) { res.close(); } if (pstat != null) { pstat.close(); } if (conn != null) { mypool.release(conn);//归还连接 } } catch (SQLException e) { e.printStackTrace(); System.out.println("没有开流，不用关"); } } //增删改操作 public static int executeUpdate(String sql, Object[] params) { Connection conn = null; PreparedStatement pstat = null; if (sql != null) { try { //获取连接 conn = getConnection(); //创建命令 pstat = conn.prepareStatement(sql); //遍历参数并赋值 if (params != null) { for (int i = 0; i < params.length; i++) { pstat.setObject(i + 1, params[i]); } } //执行方法 return pstat.executeUpdate(); //返回值为int类型 } catch (SQLException e) { e.printStackTrace(); } finally { closeAll(pstat, conn, null); } } return -1; } //获取单条数据 参数可以变化 public static <T> T findSingle(String sql, Object[] params, Class<T> clazz) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = getConnection(); ps = conn.prepareStatement(sql); if (params != null) { for (int i = 0; i < params.length; i++) { ps.setObject(i + 1, params[i]); } } rs = ps.executeQuery(); T obj = clazz.newInstance();//创建实体类的实例对象 if (rs.next()) { ResultSetMetaData metaData = rs.getMetaData();//获取ResultSet的返回值对象的元数据集合 System.out.println("列数：" + metaData.getColumnCount()); for (int i = 0; i < metaData.getColumnCount(); i++) { String columnName = metaData.getColumnName(i + 1); PropertyDescriptor pd = new PropertyDescriptor(columnName, clazz);//返回的是一个对象 System.out.println("PropertyDescriptor的对象，pd:" + pd); if (pd != null) { Method writeMethod = pd.getWriteMethod(); writeMethod.invoke(obj, rs.getObject(columnName)); } } } return obj; } catch (Exception e) { e.printStackTrace(); System.out.println("查询异常"); } finally { closeAll(ps, conn, rs); } return null; } //查找 多条数据 public static <T> List<T> findAll(String sql, Object[] params, Class<T> clazz) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; List<T> list = new ArrayList<>(); try { conn = getConnection(); ps = conn.prepareStatement(sql); if (params != null) { for (int i = 0; i < params.length; i++) { ps.setObject(i + 1, params[i]); } } rs = ps.executeQuery(); // T obj = clazz.newInstance();//这句话不能放在外面，因为要把对象的地址放到list集合里，集合里的所有数据都单独对应一个地址 ResultSetMetaData metaData = rs.getMetaData();//这个是获取SQL语句中要查询的列名对象，放在while里面和外卖都行 while (rs.next()) { T obj = clazz.newInstance();//没创建一个对象，就把相应的地址引用方法list集合里 for (int i = 0; i < metaData.getColumnCount(); i++) { String columnName = metaData.getColumnName(i + 1); Object value = rs.getObject(columnName); PropertyDescriptor pd = new PropertyDescriptor(columnName, clazz); Method writeMethod = pd.getWriteMethod(); writeMethod.invoke(obj, value); } list.add(obj); } return list; } catch (Exception e) { e.printStackTrace(); System.out.println("查询异常"); } finally { closeAll(ps, conn, rs); } return null; } } 

3.测试类

package com.scc.demo; import com.scc.utils.DBUtils; import java.sql.Connection; /** * scc */ public class PoolTest { public static void main(String[] args) { for (int i = 0; i < 20; i++) { Connection conn = DBUtils.getConnection(); System.out.println("获取连接："+conn.toString()); DBUtils.closeAll(null, conn, null); } } } 

运行结果

"C:\Program Files\Java\jdk1.8.0_152\bin\java.exe" "-javaagent:D:\Program Files\JetBrains\IntelliJ IDEA 2018.3.3\lib\idea_rt.jar=65478:D:\Program Files\JetBrains\IntelliJ IDEA 2018.3.3\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\Java\jdk1.8.0_152\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_152\jre\lib\rt.jar;E:\IdeaWorkplace\JDBC2\out\production\JDBC2;E:\IdeaWorkplace\JDBC2\lib\mysql-connector-java-5.1.41-bin.jar;E:\Software02\repository\junit\junit\4.12\junit-4.12.jar;E:\Software02\repository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar" com.scc.demo.PoolTest com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/student...root...123456 数据库连接池初始化完成 使用了:com.mysql.jdbc.JDBC4Connection@4ccabbaa 获取连接：com.mysql.jdbc.JDBC4Connection@4ccabbaa 归还了:com.mysql.jdbc.JDBC4Connection@4ccabbaa 使用了:com.mysql.jdbc.JDBC4Connection@4bf558aa 获取连接：com.mysql.jdbc.JDBC4Connection@4bf558aa 归还了:com.mysql.jdbc.JDBC4Connection@4bf558aa 使用了:com.mysql.jdbc.JDBC4Connection@2d38eb89 获取连接：com.mysql.jdbc.JDBC4Connection@2d38eb89 归还了:com.mysql.jdbc.JDBC4Connection@2d38eb89 使用了:com.mysql.jdbc.JDBC4Connection@5fa7e7ff 获取连接：com.mysql.jdbc.JDBC4Connection@5fa7e7ff 归还了:com.mysql.jdbc.JDBC4Connection@5fa7e7ff 使用了:com.mysql.jdbc.JDBC4Connection@4629104a 获取连接：com.mysql.jdbc.JDBC4Connection@4629104a 归还了:com.mysql.jdbc.JDBC4Connection@4629104a 使用了:com.mysql.jdbc.JDBC4Connection@27f8302d 获取连接：com.mysql.jdbc.JDBC4Connection@27f8302d 归还了:com.mysql.jdbc.JDBC4Connection@27f8302d 使用了:com.mysql.jdbc.JDBC4Connection@4d76f3f8 获取连接：com.mysql.jdbc.JDBC4Connection@4d76f3f8 归还了:com.mysql.jdbc.JDBC4Connection@4d76f3f8 使用了:com.mysql.jdbc.JDBC4Connection@2d8e6db6 获取连接：com.mysql.jdbc.JDBC4Connection@2d8e6db6 归还了:com.mysql.jdbc.JDBC4Connection@2d8e6db6 使用了:com.mysql.jdbc.JDBC4Connection@23ab930d 获取连接：com.mysql.jdbc.JDBC4Connection@23ab930d 归还了:com.mysql.jdbc.JDBC4Connection@23ab930d 使用了:com.mysql.jdbc.JDBC4Connection@4534b60d 获取连接：com.mysql.jdbc.JDBC4Connection@4534b60d 归还了:com.mysql.jdbc.JDBC4Connection@4534b60d 使用了:com.mysql.jdbc.JDBC4Connection@3fa77460 获取连接：com.mysql.jdbc.JDBC4Connection@3fa77460 归还了:com.mysql.jdbc.JDBC4Connection@3fa77460 使用了:com.mysql.jdbc.JDBC4Connection@619a5dff 获取连接：com.mysql.jdbc.JDBC4Connection@619a5dff 归还了:com.mysql.jdbc.JDBC4Connection@619a5dff 使用了:com.mysql.jdbc.JDBC4Connection@1ed6993a 获取连接：com.mysql.jdbc.JDBC4Connection@1ed6993a 归还了:com.mysql.jdbc.JDBC4Connection@1ed6993a 使用了:com.mysql.jdbc.JDBC4Connection@7e32c033 获取连接：com.mysql.jdbc.JDBC4Connection@7e32c033 归还了:com.mysql.jdbc.JDBC4Connection@7e32c033 使用了:com.mysql.jdbc.JDBC4Connection@7ab2bfe1 获取连接：com.mysql.jdbc.JDBC4Connection@7ab2bfe1 归还了:com.mysql.jdbc.JDBC4Connection@7ab2bfe1 使用了:com.mysql.jdbc.JDBC4Connection@497470ed 获取连接：com.mysql.jdbc.JDBC4Connection@497470ed 归还了:com.mysql.jdbc.JDBC4Connection@497470ed 使用了:com.mysql.jdbc.JDBC4Connection@63c12fb0 获取连接：com.mysql.jdbc.JDBC4Connection@63c12fb0 归还了:com.mysql.jdbc.JDBC4Connection@63c12fb0 使用了:com.mysql.jdbc.JDBC4Connection@b1a58a3 获取连接：com.mysql.jdbc.JDBC4Connection@b1a58a3 归还了:com.mysql.jdbc.JDBC4Connection@b1a58a3 使用了:com.mysql.jdbc.JDBC4Connection@6438a396 获取连接：com.mysql.jdbc.JDBC4Connection@6438a396 归还了:com.mysql.jdbc.JDBC4Connection@6438a396 使用了:com.mysql.jdbc.JDBC4Connection@e2144e4 获取连接：com.mysql.jdbc.JDBC4Connection@e2144e4 归还了:com.mysql.jdbc.JDBC4Connection@e2144e4 Process finished with exit code 0