上一篇博文是”基于mysql的JDBC的增删改查的封装 “:点击可查看
今天本仙在昨天JDBC封装增删改查的基础上实现自定义的数据库连接池:
为什么要使用数据库连接池：
数据库连接池 :顾名思义就是一个存放着数据库连接的游泳池,这些数据库连接初始化后都在这个池子里面遨游,当有请求要获取连接时,拿出去一个使用,用完后再放回这个池子里面,方便循环使用.数据库连接小鱼生生不息,有效的节省了资源,提高了处理效率,这个解释通俗易懂吧~
1 项目结构: 昨天有个’屎’嘲笑我的项目路径竟然包括中文,本仙受挫了,哼,但是他所说的对,于是乎决定全改成英文,本仙一定会好好学习英语的
2 省略的过程
项目的创建
jdbc驱动jar包的导入
数据库中用户表(user)的创建和添加数据(可见上一篇,传送门 )
实体类(User)的创建(可见上一篇,传送门 )
配置文件(db.properties)的创建(可见上一篇,传送门 )
3 自定义数据库连接池的实现(DB_pool.java) 实现下面目录中类的封装:
3.1 数据库连接池的初始化 Java为连接池实现提供了一个规范(接口),规范的写法,我们需要实现DataSource 接口(当然这个接口中的方法我们不用每个都实现,没有实现的方法我就不贴了)!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 public class DB_pool implements DataSource public static final int CONNCOUNT = 5 ; public static List connections; private static String driverClass; private static String url; private static String user; private static String password; static { connections = Collections.synchronizedList(new LinkedList<>()); try { InputStream inputStream = DB_Tools.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(inputStream); driverClass = properties.getProperty("driverClass" ); url = properties.getProperty("url" ); user = properties.getProperty("user" ); password = properties.getProperty("password" ); Class.forName(driverClass); System.out.println("注册驱动成功" ); for (int i = 0 ; i < CONNCOUNT; i++) { connections.add(DriverManager.getConnection(url, user, password)); } System.out.println("数据库连接池初始化完成" ); } catch (Exception e) { e.printStackTrace(); System.out.println("初始化失败" ); } } }
3.2 通过数据库连接池获取数据库连接 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 public class DB_pool implements DataSource @Override public Connection getConnection () throws SQLException synchronized (connections) { if (connections.size() > 0 ) { Connection connection = connections.remove(0 ); System.out.println("使用了Connection:" +connection.toString()); return connection; } } return null ; } }
3.3 断开数据库连接 数据库连接使用完毕之后,并不是将这个连接给close()掉,而是重新放入数据库连接池中(不然你在数据库连接池中拿一个连接,用完了就关闭不放回去….N轮之后数据库连接池就没有连接了,那数据库连接池就是去了它存在的意义)
1 2 3 4 5 6 7 8 public class DB_pool implements DataSource public void releaseConnection (Connection connection) System.out.println("归还了Connection:" +connection.toString()); connections.add(connection); } }
传送门:上一篇博客:简易JDBC增删改查的封装
4.1 初始化数据库连接池(修改) 1 2 3 4 5 6 7 8 9 public class DB_Tools private static DB_pool db_pool; static { db_pool = new DB_pool(); } }
4.2 获取数据库连接(修改) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 public class DB_Tools public static Connection getDBConnection () try { return db_pool.getConnection(); } catch (SQLException e) { e.printStackTrace(); } return null ; } }
4.3 释放资源(修改) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 public class DB_Tools public static void allClose (Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) try { if (preparedStatement != null ) { preparedStatement.close(); } if (resultSet != null ) { resultSet.close(); } if (connection != null ) { db_pool.releaseConnection(connection); } System.out.println("执行结束" ); } catch (SQLException e) { e.printStackTrace(); } } }
4.4 增删改查的方法(一点点修改) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 public class DB_Tools public static int excumentDBUpdate (String sql, Object[] objects) int updateResult = 0 ; Connection connection = null ; PreparedStatement preparedStatement = null ; try { connection = getDBConnection(); preparedStatement = connection.prepareStatement(sql); if (objects != null ) { for (int i = 0 ; i < objects.length; i++) { preparedStatement.setObject(i + 1 , objects[i]); } } updateResult = preparedStatement.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { allClose(connection, preparedStatement, null ); } return updateResult; } public static T getSingleResult (String sql, Object[] objects, Class tClass) { Connection connection = null ; PreparedStatement preparedStatement = null ; ResultSet resultSet = null ; T object = null ; try { connection = getDBConnection(); preparedStatement = connection.prepareStatement(sql); if (objects != null ) { for (int i = 0 ; i < objects.length; i++) { preparedStatement.setObject(i + 1 , objects[i]); } } resultSet = preparedStatement.executeQuery(); if (resultSet.next()) { object = getObject(resultSet, tClass); } } catch (Exception e) { e.printStackTrace(); } finally { allClose(connection, preparedStatement, resultSet); } return object; } public static List getComplexResult (String sql, Object[] objects, Class tClass) { Connection connection = null ; PreparedStatement preparedStatement = null ; ResultSet resultSet = null ; List tList = new ArrayList(); try { connection = getDBConnection(); preparedStatement = connection.prepareStatement(sql); if (objects != null ) { for (int i = 0 ; i < objects.length; i++) { preparedStatement.setObject(i + 1 , objects[i]); } } resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { T object = getObject(resultSet, tClass); tList.add(object); } } catch (Exception e) { e.printStackTrace(); } finally { allClose(connection, preparedStatement, resultSet); } return tList; } public static T getObject (ResultSet resultSet, Class tClass) throws Exception { T object = tClass.newInstance(); ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); for (int i = 0 ; i < resultSetMetaData.getColumnCount(); i++) { String colName = resultSetMetaData.getColumnName(i + 1 ); PropertyDescriptor propertyDescriptor = new PropertyDescriptor(colName, tClass); if (propertyDescriptor != null ) { Method method = propertyDescriptor.getWriteMethod(); method.invoke(object, resultSet.getObject(colName)); } } return object; } }
通过上面我们就分别封装好了自定义的数据库连接池和JDBC操作类,在这里写一个接口封装几个方法使用数据库连接池和JDBC封装的方法来实现:
5 封装接口(DbPoolsDao.java) 封装接口:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 public interface DbPoolsDao public User findSingleUserByPrimaryKey (int id) public List findUserAllUser () public void addUserInfo (User user) public void updateUserInfoByPrimaryKey (User user) public void deleteUserInfoByPrimaryKey (int id) }
6 实现上面的接口(DbPoolDaoImpl.java) 实现接口:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 public class DbPoolDaoImpl implements DbPoolsDao @Override public User findSingleUserByPrimaryKey (int id) String sql = "select * from user where id = ?" ; Object[] objects = {id}; return DB_Tools.getSingleResult(sql, objects, User.class ) ; } @Override public List findUserAllUser () String sql = "select * from user" ; return DB_Tools.getComplexResult(sql, null , User.class ) ; } @Override public void addUserInfo (User user) String sql = "insert into user values(?,?,?,?,?)" ; Object[] objects = {user.getId(),user.getUsername(),user.getPassword(),user.getAge(),user.getGendar()}; DB_Tools.excumentDBUpdate(sql, objects); } @Override public void updateUserInfoByPrimaryKey (User user) String sql = "update user set username=?,password=?,age=?,gendar=? where id=?" ; Object[] objects = {user.getUsername(),user.getPassword(),user.getAge(),user.getGendar(),user.getId()}; DB_Tools.excumentDBUpdate(sql, objects); } @Override public void deleteUserInfoByPrimaryKey (int id) String sql = "delete from user where id=?" ; Object[] objects = {id}; DB_Tools.excumentDBUpdate(sql, objects); } }
7 测试 上面写了那么多,到底结果如何呢,这里本仙分别用单元测试(简易版),测试数据库连接池的封装类(DB_pool)和接口实现(DbPoolDaoImpl)看看写的代码成功与否走你┏ (゜ω゜)=☞
7.1 测试数据库连接池封装类 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 public class DB_poolTest @Test public void testPool () for (int i = 0 ; i < 100 ; i++) { Connection connection = DB_Tools.getDBConnection(); DB_Tools.allClose(connection, null , null ); System.out.println(connection.toString() + "\n--------------------------------------------------" ); } } }
7.2 测试实现接口 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 public class Db_PoolDaoTest private static DbPoolsDao dbPoolsDao; @BeforeClass public static void beforeTest () dbPoolsDao = new DbPoolDaoImpl(); } @AfterClass public static void afterTest () dbPoolsDao = null ; } @Test public void findSingleUserByPrimaryKeyTest () User user = dbPoolsDao.findSingleUserByPrimaryKey(100 ); System.out.println(user); } @Test public void findUserAllUserTest () List users = dbPoolsDao.findUserAllUser(); for (User user : users) { System.out.println(user); } } @Test public void addUserInfoTest () dbPoolsDao.addUserInfo(new User(105 , "明明" , "mingming" , 20 , "male" )); System.out.println("添加成功" ); } @Test public void updateUserInfoByPrimaryKeyTest () dbPoolsDao.updateUserInfoByPrimaryKey(new User(105 , "明明" , "helloworld" , 18 , "male" )); System.out.println("修改成功" ); } @Test public void deleteUserInfoByPrimaryKeyTest () dbPoolsDao.deleteUserInfoByPrimaryKey(105 ); System.out.println("删除成功" ); } }
方法太多,测试结果我就不一个一个贴上来了,不过每个方法小编都是测试过的!如果哦除了问题,那肯定是你代码里有小臭虫了,本仙这里是通过的,若你再测试过程中遇到了问题,可以留言欧,我超级喜欢给别人找Bug的. 感谢您的阅读,希望您有收获,祝您生活愉快,开心快乐每一天~
