Use the Apache DBCP connection pool to reconstruct DBUtility to make database connection more effective and secure. dbcpdbutility

Use the Apache DBCP connection pool to reconstruct DBUtility to make database connection more effective and secure. dbcpdbutility

Avoid the following risks when you directly access the database using JDBC:

1. For each data operation request, you must establish a database connection, open a connection, access data, and close the connection. Establishing and opening a database connection is a resource-consuming and time-consuming process. Frequent database operations will inevitably degrade the system performance.

2. The connection object represents the connection process of the database system and is a limited resource. If the system is used by many users, it may exceed the limits of the database server and cause the system to crash.

The database connection pool is the most common solution to the above problems. The so-called connection pool allows you to create and hold database connection components. The connection pool can create and encapsulate some connection objects in advance and cache them. When you need to use a connection object, you can "borrow" a connection from the connection pool, after use, return it to the connection pool.

The main functions of the database connection pool are as follows:

1. Create and release connection pool objects.

2. When the server is started, a specified number of database connections are created.

3. provide available connections for user requests. If there is no idle connection and the number of connections does not exceed the maximum value, create a new database connection.

4. Identify a connection that is no longer in use as a available connection and wait for requests from other users.

5. When there are too many idle connections, release the connection object.

The connection pool components generally need to implement the javax. SQL. DataSource interface in the JDBC specification. The DataSource interface defines the getConnection method for obtaining connections.

Common connection pool components include DBCP, c3p0, and proxool. Here we use the Apache DBCP component as an example to implement the database connection pool.

 

Create a java project and a configuration file as follows:

 

Under the current project, import the jar packages required to use the DBCP component, including the commons-dbcp.jar and the commons-pool.jar. The names of these jar Packages may vary depending on the version, the name ends with version information, for example, a commons-dbcp-1.2.1.jar.

 

Tool class DBUtility:

 

1 package com. daliu. jdbc; 2 3 import java. io. IOException; 4 import java. SQL. connection; 5 import java. SQL. SQLException; 6 import java. util. properties; 7 8 import org. apache. commons. dbcp. basicDataSource; 9/** 10 * tool class 11 * @ author daliu_it 12*13 */14 public class DBUtility {15 private static BasicDataSource = null; 16 17 public DBUtility () {18} 19 public static void init () {20 21 Properties dbProps = new Properties (); 22 // you can modify the configuration file according to your needs. load (DBUtility. class. getClassLoader (). getResourceAsStream (25 "com/daliu/jdbc/db. properties "); 26} catch (IOException e) {27 e. printStackTrace (); 28} 29 30 try {31 String driveClassName = dbProps. getProperty ("jdbc. driverClassName "); 32 String url = dbProps. getProperty ("jdbc. url "); 33 String username = dbProps. ge TProperty ("jdbc. username "); 34 String password = dbProps. getProperty ("jdbc. password "); 35 36 String initialSize = dbProps. getProperty ("dataSource. initialSize "); 37 String minIdle = dbProps. getProperty ("dataSource. minIdle "); 38 String maxIdle = dbProps. getProperty ("dataSource. maxIdle "); 39 String maxWait = dbProps. getProperty ("dataSource. maxWait "); 40 String maxActive = dbProps. getProperty ("dataSou Rce. maxActive "); 41 42 dataSource = new BasicDataSource (); 43 dataSource. setDriverClassName (driveClassName); 44 dataSource. setUrl (url); 45 dataSource. setUsername (username); 46 dataSource. setPassword (password); 47 48 // Number of initialized connections 49 if (initialSize! = Null) 50 dataSource. setInitialSize (Integer. parseInt (initialSize); 51 52 // minimum idle connection 53 if (minIdle! = Null) 54 dataSource. setMinIdle (Integer. parseInt (minIdle); 55 56 // maximum idle connection 57 if (maxIdle! = Null) 58 dataSource. setMaxIdle (Integer. parseInt (maxIdle); 59 60 // timeout recovery time (in milliseconds) 61 if (maxWait! = Null) 62 dataSource. setMaxWait (Long. parseLong (maxWait); 63 64 // maximum number of connections 65 if (maxActive! = Null) {66 if (! MaxActive. trim (). equals ("0") 67 dataSource. setMaxActive (Integer. parseInt (maxActive); 68} 69} catch (Exception e) {70 e. printStackTrace (); 71 System. out. println ("An error occurred while creating the connection pool! Check settings !!! "); 72} 73} 74 75/** 76 * database Connection 77 * @ return 78 * @ throws SQLException 79 */80 public static synchronized Connection getConnection () throws SQLException {81 if (dataSource = null) {82 init (); 83} 84 Connection conn = null; 85 if (dataSource! = Null) {86 conn = dataSource. getConnection (); 87} 88 return conn; 89} 90 91/** 92 * shut down database 93 * @ param conn 94 */95 public void closeConnection (Connection conn) {96 if (conn! = Null) {97 try {98 conn. close (); 99} catch (SQLException e) {100 System. out. println ("failed to close the resource"); 101 e. printStackTrace (); 102} 103} 104} 105 106}

 

 

 

Reconstructs the db. properties file and adds the information required to create a database connection pool, including the number of initial connections, the maximum number of idle connections, the maximum number of connections, and the timeout recovery time. The file content is as follows:

 1 #Oracle 2 #jdbc.driverClassName=oracle.jdbc.OracleDriver 3 #jdbc.url=jdbc:oracle:thin:@localhost:1521:orcl 4 #jdbc.username=root 5 #jdbc.password=123456 6  7 #Mysql 8 jdbc.driverClassName=com.mysql.jdbc.Driver 9 jdbc.url=jdbc:mysql://localhost:3306/csdn10 jdbc.username=root11 jdbc.password=12345612 13 dataSource.initialSize=1014 dataSource.maxIdle=2015 dataSource.minIdle=516 dataSource.maxActive=5017 dataSource.maxWait=1000

 

Test class testCase:

1 package com. daliu. test; 2 3 import java. SQL. SQLException; 4 5 import org. junit. test; 6 7 import com. daliu. jdbc. DBUtility; 8 9 10 public class testCase {11 12/** 13 * test whether to connect 14 * @ throws SQLException15 */16 @ Test17 public void testgetConnection () throws SQLException {18 DBUtility db = new DBUtility (); 19 System. out. println (db. getConnection (); 20} 21}

 

EmpDAO class:

1 package com. daliu. jdbc; 2 import java. SQL. connection; 3 import java. SQL. resultSet; 4 import java. SQL. SQLException; 5 import java. SQL. statement; 6 7 public class EmpDAO {8 public static void main (String [] args) {9 EmpDAO dao = new EmpDAO (); 10 dao. findAll (); 11} 12 13/** 14 * query all information in the database table 15 */16 public void findAll () {17 18 Connection con = null; 19 Statement stmt = null; 20 ResultSet rs = null; 21 2 2 23 try {24 // 1. Get Connection 25 con = DBUtility. getConnection (); 26 // 2. Get database operation object Statement through the createStatement () method of Connection. 27 stmt = con. createStatement (); 28 // 3. Run the SQL Statement by calling the executeQuery METHOD OF THE Statement object. 29 rs = stmt30. executeQuery ("select empno, ename, sal, hiredate from emp"); 31 // 4. if the ResultSet object does not have the next row, false is returned. Therefore, you can use it in the while loop to iterate the result set 32 while (rs. next () {33 System. out. println (rs. getInt ("empno") + "," 34 + rs. getString ("ename") + "," + "," 35 + rs. getDouble ("sal") + "," + rs. getDate ("hiredate"); 36} 37 38 39} catch (SQLException e) {40 System. out. println ("database access exception! "); 41 throw new RuntimeException (e); 42} finally {43 try {44 45 // 5. In the finally block, close the ResultSet object, Statement object, and Connection object in sequence. 46 if (rs! = Null) {47 rs. close (); 48} 49 if (stmt! = Null) {50 stmt. close (); 51} 52 if (con! = Null) {53 con. close (); 54} 55} catch (SQLException e) {56 System. out. println ("an exception occurred when releasing resources"); 57} 58} 59} 60}

 

 

Mysql script:

 1 create database csdn; 2  3 use csdn; 4  5 CREATE TABLE emp( 6     empno int(4), 7     ename VARCHAR(10), 8     job VARCHAR(9), 9     mgr int(4),10     hiredate DATE,11     sal double(7,2),12     comm double(7,2),13     deptno double(2,0)14 );15 16 INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);17 INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);18 INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);19 INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);20 INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-9-21',1250,1400,30);21 INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);22 INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);23 INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-4-19',3000,NULL,20);24 INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);25 INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);26 INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-5-27',1100,NULL,20);27 INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-1',950,NULL,30);28 INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);29 INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);30 31 select * from emp;

 

Reprinted please mark: http://www.cnblogs.com/liuhongfeng/p/4174661.html