Use of database connection pool proxool
Analysis of Database Connection Pool
What is a database connection pool? Why use database connection pool? This is the first thing we think about when we look at database connection pools. The following author will make a simple analysis of the database connection pool with his own understanding.
Database connection pool: As the name implies, a pool connecting to a database. So, you might ask why you use a pool to connect to a database, and what is there in that pool? Yes, why don't we use a pool instead of a database connection? This is because database connection is a very expensive resource. Every database connection will cost a lot of space and time. Therefore, we set up a pool in which we can store the connected database objects. When we use the database, we can use the connected objects directly from the database connection pool to avoid it. The cost of connecting the database first for each operation.-
Schematic diagram:
- Talking about proxool:
Proxool is a Java database connection pool technology. It is an open source project under sourceforge. This project provides a robust and easy-to-use connection pool. The most important thing is that this connection pool provides monitoring function, which is easy to use, and easy to detect connection leakage. Using proxool:
1. Importing jar packages
Including the connection jar package of database mysql, the core jar package of proxool, and most importantly, the jar package of log common and log4j, otherwise nullPoint null pointer exception will be reported.
2. Establishing the proxool.xml file of the same level in web.xml under WEB-INF
proxool.xml file content:
<?xml version="1.0" encoding="UTF-8"?>
<something-else-entirely>
<proxool>
<!-- To configure proxool alias -->
<alias>mp</alias>
<!-- Configure the connection information of the database ?characterEncoding=UTF-8 -->
<driver-url>jdbc:mysql://localhost:3306/flowershop?characterEncoding=UTF-8</driver-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<driver-properties>
<property name="user" value="user"/>
<property name="password" value="user"/>
</driver-properties>
<!-- maximum connection -->
<maximum-connection-count>100</maximum-connection-count>
<!-- Minimum number of connections -->
<minimum-connection-count>10</minimum-connection-count>
<house-keeping-test-sql>select CURRENT_DATE</house-keeping-test-sql>
</proxool>
</something-else-entirely>3. Modify the database connection tool class DbUtil.java
package cn.com.mp.utils;
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.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
public class DbUtil {
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("Database-driven load exception,Please check!");
e.printStackTrace();
}
}
private Connection conn = null;
private Statement stat = null;
private PreparedStatement ps = null;
private ResultSet rs = null;
private ResultSetMetaData rst = null;
private Connection setConn() {
try {
//Modify getConnection ("proxool. alias")
conn = DriverManager.getConnection("proxool.mp");
} catch (Exception e) {
System.out.println("Data Source Connection Failed,Please check!");
}
return conn;
}
private PreparedStatement setPs(String sql) {
setConn();
try {
ps = conn.prepareStatement(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("Failed to create query result set,Please check!");
e.printStackTrace();
}
return ps;
}
private void toClose() {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("Query result set shutdown failed!");
e.printStackTrace();
}
}
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("Statement object closure failed!");
e.printStackTrace();
} finally {
stat = null;
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("Statement object closes exception!");
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("Data source shutdown failed!");
e.printStackTrace();
}
}
}
public int update(String sql, Object... obj) {
ps = setPs(sql);
try {
for (int i = 0; i < obj.length; i++) {
ps.setObject(i + 1, obj[i]);
}
return ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("Update failed,Please check!");
e.printStackTrace();
} finally {
toClose();
}
return -1;
}
public int update(String sql) {
return update(sql, new Object[] {});
}
public List<Map<String, Object>> query(String sql, Object... obj) {
List<Map<String, Object>> list = new LinkedList<Map<String, Object>>();
setPs(sql);
//System.out.println("Ha ha ha ha ha ha");
//System.out.println(obj.length);
try {
for (int i = 1; i <= obj.length; i++) {
//System.out.println(obj[i - 1]+"**//");
ps.setObject(i, obj[i - 1]);
//System.out.println(ps);
}
//System.out.println(ps.getMetaData());
/*The error is: rs.next() jumps out of the while loop directly for false*/
rs = ps.executeQuery();
rst = rs.getMetaData();
while (rs.next()) {
//System.out.println("haiglfdh");
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= rst.getColumnCount(); i++) {
String key = rst.getColumnName(i);
map.put(key, rs.getString(key));
//System.out.println("map:"+map.get(key));
}
list.add(map);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("Query abnormal!");
e.printStackTrace();
} finally {
toClose();
}
//System.out.println("list:"+list);
return list;
}
public List<Map<String, Object>> query(String sql) {
return query(sql, new Object[] {});
}
public void batch(String strs[]) {
try {
stat = setConn().createStatement();
conn.setAutoCommit(false);
for (String st : strs) {
stat.addBatch(st);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
stat.executeBatch();
} catch (SQLException e) {
// TODO Auto-generated catch block
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
conn.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
toClose();
}
}
- summary
Whatever the size of the project, the database will be used, and then the operation cost of the database is very high. Using the objects already connected in the database connection pool and avoiding repeated connection operations can greatly improve the overall efficiency of the project to some extent. It is necessary to learn how to use the database connection pool. Of course, the database connection pool is not only P. There are many kinds of roxool, such as C3P0 and so on, but the principle is the same. The difference is that you can choose to use them according to your preferences.
Above is the author's simple self-learning, if there is something wrong, also ask for advice.