MySql数据库再学习——使用强化版的自定义连接池连接数据库

一：两个重点，

一是获取properties文件的路径，总结在注释里，我当时获取了好半天，总是找不到文件。

二是理清这几个class之间的联系，以及本次demo的思路逻辑关系：总的来说就是，在原有的driverManager获取的基础上——>创建一个池子（池子中存储若干个基础的Connection对象）

——>书写一个强化类实现Connection接口（强化的是close（）方法）——>在主程序中调用的时候调用的是从池子中取出来的，强化后的 Connection对象。

dbUtil.class

  1 public class dbUtil {
  2 
  3     /*初始化需要用到的变量*/
  4     private static Connection conn;
  5     private static PreparedStatement pst;
  6     private static ResultSet rs;
  7 
  8     private static String driver;
  9     private static String url;
 10     private static String username;
 11     private static String password;
 12     private static InputStream in;
 13     private static ClassLoader cl;
 14 
 15     static {
 16         try {
 17             //获取当前类的类加载器
 18             //cl = Thread.currentThread().getContextClassLoader();
 19 
 20             //获取配置文件的输入流
 21             // in = cl.getResourceAsStream("db.properties");
 22 
 23             //切忌使用中文命名文件夹，否则dbUtil.class.getResource("/db.properties").getFile()会找不到文件路径
 24             FileInputStream in = new FileInputStream(dbUtil.class.getResource("/db.properties").getFile());
 25 
 26             //创建一个properties文件对象
 27             Properties ppt = new Properties();
 28             //加载输入流
 29             ppt.load(in);
 30             //获得相关参数
 31             driver = ppt.getProperty("driver");
 32             url = ppt.getProperty("url");
 33             username = ppt.getProperty("username");
 34             password = ppt.getProperty("password");
 35         } catch (Exception e) {
 36             e.printStackTrace();
 37         }
 38     }
 39 
 40     private MyDbConnection conn1;
 41 
 42 
 43     public static Connection getConnection() throws ClassNotFoundException, SQLException {
 44         //注册驱动
 45         Class.forName(driver);
 46         //连接数据库
 47         Connection connection = DriverManager.getConnection(url, username, password);
 48         return connection;
 49     }
 50 
 51 
 52     public static void release(MyDbConnection conn, PreparedStatement pst, ResultSet rs) {
 53         if (rs != null) {
 54             try {
 55                 rs.close();
 56             } catch (Exception e) {
 57                 e.printStackTrace();
 58             }
 59         }
 60         if (pst != null) {
 61             try {
 62                 pst.close();
 63             } catch (Exception e) {
 64                 e.printStackTrace();
 65             }
 66         }
 67         if (conn != null) {
 68             try {
 69                 conn.close();
 70             } catch (Exception e) {
 71                 e.printStackTrace();
 72             }
 73         }
 74     }
 75 
 76     @Test
 77     public void test() {
 78 
 79 
 80         String sql = "select * from test where name=?";
 81         LinkPool pool = new LinkPool();
 82         for (int i=0;i<25;i++){
 83             try {
 84                 //conn=getConnection();
 85                 //这个链接是从池子里取的，此时尺子中的连接是强化过得 战斗力翻倍.:)
 86                 conn1 = (MyDbConnection) pool.getConnection();
 87                 System.out.println(conn1.getClass());
 88                 //这个prepareStatement方法也是重写过得 否则重写过的conn是得不到该方法的
 89                 pst = conn1.prepareStatement(sql);
 90                 pst.setString(1, "张浩");
 91                 rs = pst.executeQuery();
 92                 while (rs.next()) {
 93                     System.out.println(rs.getString("name"));
 94                 }
 95             } catch (Exception e) {
 96                 e.printStackTrace();
 97             } finally {
 98                 //release对象中的close方法也是重写过的
 99                 release(conn1, pst, rs);
100             }
101         }
102 
103     }
104 
105 }

LinkPool.class

 1 public class LinkPool implements DataSource {
 2         //定义一个池子对象
 3 
 4     private static LinkedList<Connection> pool = new LinkedList<>();
 5     public  LinkPool(){
 6 
 7             for (int i = 0; i < 5; i++) {
 8                 try {
 9                     pool.add(new MyDbConnection(dbUtil.getConnection(), pool));
10                 } catch (ClassNotFoundException e) {
11                     e.printStackTrace();
12                 } catch (SQLException e) {
13                     e.printStackTrace();
14                 }
15             }
16             System.out.println("池子中的个数为： "+pool.size());
17         }
18 
19 
20 
21 
22 
23     @Override
24     public Connection getConnection() throws SQLException {
25 
26         System.out.println("当前pool: "+pool.size());
27         if (pool.size() == 0) {
28             for (int i = 0; i < 5; i++) {
29                 try {
30                     //想池子中添加Myconnection对象
31                     pool.add(new MyDbConnection( dbUtil.getConnection(), pool));
32 
33                 } catch (Exception e) {
34                     e.printStackTrace();
35                 }
36             }
37         }
38       /* Connection  conn=(MyDbConnection)pool.remove(0);*/
39         //System.out.println("当前pool1: "+pool.size());
40         return  new MyDbConnection(pool.remove(0),pool);
41     }}

MyDbConnection.class

 1 public class MyDbConnection implements Connection {
 2     //为了通过构造方法 将普通的connection对象传进来，然后对close方法进行强化
 3     //pool对象也是如此
 4     private Connection conn;
 5     private LinkedList<Connection> pool;
 6 
 7     public MyDbConnection(Connection conn, LinkedList<Connection> pool) {
 8         this.conn = conn;
 9         this.pool = pool;
10     }
11 
12     @Override
13     public void close() throws SQLException {
14         System.out.println("对象归还");
15         //实现pool的对象补充
16         pool.add(conn);
17     }
18 
19     @Override
20     public PreparedStatement prepareStatement(String sql) throws SQLException {
21         //重写prepareStatement方法
22         return conn.prepareStatement(sql);
23     }
24 
25 }