Java数据库连接池之proxool_动力节点Java学院整理

时间:2022-04-08 07:19:21

Proxool是一种Java数据库连接池技术。sourceforge下的一个开源项目,这个项目提供一个健壮、易用的连接池,最为关键的是这个连接池提供监控的功能,方便易用,便于发现连接泄漏的情况。

目前是和DBCP以及C3P0一起,最为常见的三种JDBC连接池技术。

日前,Hibernate官方宣布由于Bug太多不再支持DBCP,而推荐使用 Proxool或C3P0。

下面通过一个Demo说明一下如何使用:

        项目结构如下:

        DBLink.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
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
package com.bjpowernode.util; 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class DBLink {
  private Connection connection;
  private PreparedStatement preparedStatement;
  private ResultSet resultSet;
  static{
    try {
      Class.forName("org.logicalcobwebs.proxool.ProxoolDriver"); //加载驱动 
    }catch (ClassNotFoundException e) {
      System.out.println("加载驱动失败!");
      e.printStackTrace();
    }
  }
  private Connection getConn(){
    try {
      if(connection==null||connection.isClosed()){
        connection = DriverManager.getConnection("proxool.mysqlsource");  //获取连接 
      }
    } catch (SQLException e) {
      System.out.println("获取连接失败!");
      e.printStackTrace();
    }
    return connection;
  }
  public List<Map<String, Object>> select(String sql, Object [] params){
    List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
    try{
      connection = this.getConn();
      preparedStatement = connection.prepareStatement(sql);
      if(params != null){
        for(int i =0; i<params.length; i++){
          preparedStatement.setObject(i+1, params[i]);//逐一给问号赋值
        }
      }
      resultSet = preparedStatement.executeQuery();
      ResultSetMetaData rsmd = resultSet.getMetaData();
      int columnCount = rsmd.getColumnCount();
      while(resultSet.next()){
        Map<String, Object> map = new HashMap<String, Object>();
        for(int i =1; i<=columnCount;i++){
          String columnName = rsmd.getColumnName(i);
          Object obj = null;
          if(rsmd.getColumnTypeName(i).equals("DATE")){
            obj = resultSet.getString(columnName);
          }else{
            obj = resultSet.getObject(columnName);
          }
          map.put(columnName, obj);
        }
        list.add(map);
      }
    }catch(SQLException e){
      e.printStackTrace();
    }finally{
      close();
    }
    return list;
  }
  private void close(){
    try {
      if(resultSet != null){
        resultSet.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    try {
      if(preparedStatement != null){
        preparedStatement.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    try {
      connection.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

        proxool.xml文件中的代码:

?
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
<?xml version="1.0" encoding="UTF-8"?>
<proxool-config>
  <proxool>
    <alias>mysqlsource</alias>
    <driver-url>jdbc:mysql://localhost:3306/test</driver-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <driver-properties>
      <property name="user" value="root" />
      <property name="password" value="root" />
    </driver-properties>
    <!-- 保留线程处于睡眠状态的最长时间 -->
    <house-keeping-sleep-time>60000</house-keeping-sleep-time>
    <maximum-connection-count>30</maximum-connection-count>
    <minimum-connection-count>10</minimum-connection-count>
    <!-- 连接池中可用的连接数量(空闲状态) -->
    <prototype-count>5</prototype-count>
    <maximum-new-connections>1</maximum-new-connections>
    <simultaneous-build-throttle>1</simultaneous-build-throttle>
    <!-- 连接池使用状态 -->
    <statistics>30s,10m,1d</statistics>
    <!-- 连接池日志类型 -->
    <statistics-log-level>ERROR</statistics-log-level>
    <!-- 如果为true,那么每个被执行的SQL语句将会在执行期被log记录(DEBUG LEVEL) -->
    <trace>true</trace>
    <house-keeping-test-sql>select CURRENT_DATE</house-keeping-test-sql>
  </proxool>
</proxool-config>

        web.xml文件中的代码:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
  http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
 
  <!-- 连接池 -->
  <servlet>
    <servlet-name>ServletConfigurator</servlet-name>
    <servlet-class>org.logicalcobwebs.proxool.configuration.ServletConfigurator</servlet-class>
    <init-param>
      <param-name>xmlFile</param-name>
      <param-value>WEB-INF/proxool.xml</param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
  </servlet>
   
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
</web-app>

        index.jsp文件中的代码:

?
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
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@page import="com.bjpowernode.util.DBLink,java.util.List,java.util.Map"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>首页</title>
  </head>
 
  <body>
    <table width="100%" border="1px">
      <tr>
        <th>序号  </th><th>班级名称</th><th>开班日期</th><th>结束日期</th><th>状态</th><th>操作</th>
      </tr>
      <%
        String sql = "SELECT id, class_name, begin_date, end_date, state FROM class ";
        DBLink db = new DBLink();
        List<Map<String, Object>> list = db.select(sql, null);
        int index = 0;
        for (Map<String, Object> map : list) {
          index = index + 1;
          int id = Integer.parseInt(map.get("id").toString());
          String className = map.get("class_name").toString();
          String beginDate = map.get("begin_date").toString();
          String endDate = map.get("end_date").toString();
          int state = Integer.parseInt(map.get("state").toString());
          String stateName = (state == 0 ? "未启用" : "启用");
      %>
      <tr align="center">
        <td><%=index%></td><td><%=className%></td><td><%=beginDate%></td><td><%=endDate%></td><td><%=stateName%></td><td>【<a href="###" rel="external nofollow" rel="external nofollow" rel="external nofollow" onclick="">修改</a>】【<a href="###" rel="external nofollow" rel="external nofollow" rel="external nofollow" onclick="">查看</a>】【<a href="###" rel="external nofollow" rel="external nofollow" rel="external nofollow" onclick="">删除</a>】</td>
      </tr>
      <%
        }
      %>
    </table>
  </body>
</html>

总结

以上所述是小编给大家介绍的Java数据库连接池之proxool,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持!