We may often use Hibernate, Mybatis, jpa and other frameworks in our study and work. These frameworks have a good encapsulation of the database connection pool and may ignore the underlying implementation of the database. Today, let's take a look at how to write a simple database connection pool. Before that, let's recall the steps of java connecting to the database:
- Register and load jdbc database driver
First, load the driver into memory; Second. Take the initiative to register the currently loaded driver with DriverManager, which is the only Java class in the JDBC specification.
Code embodiment:-
Class.forName(DriverName)
-
//Where DriverName=Driver package name.
-
//DriverName = "oracle.jdbc.driver.OracleDriver" of Oracle.
-
//MySql DriverName = "com.mysql.jdbc.Driver";
-
- Get the Connection object Connection
-
/*To connect to the database, you need to request and obtain the Connection object from java.sql.DriverManager, which represents the Connection of a database.
-
Use the getconnection (string URL, string username, string password) method of DriverManager to pass in the path of the specified database to be connected, the username and password of the database.
-
For example: / / connect to the MySql database. Both username and password are root
-
*/
-
String url = "jdbc:mysql://localhost:3306/test";
-
String username = "root";
-
String password = "root" ;
-
try{
-
Connection con =DriverManager.getConnection(url ,username , password ) ;
-
} catch(SQLException se){
-
System.out.println( "Database connection failed!");
-
se.printStackTrace();
-
}
-
-
-
-
/*1,DriverManager In the JDBC specification, it is a class rather than an interface. It is a service class used to manage JDBC drivers and provide getConnection() Method to establish the connection between the application and the database. When the jdbc driver is loaded into memory, it will actively register with the DriverManager. This line of code sends a connection request, and the DriverManager class will use the registered jdbc driver to create a connection to the database.
-
-
2,DriverManager.getConnection()Is a static method.
-
-
3,DriverManager In the java.sql package, when we call any method of any class (including interface) in the SQL package, a compile time exception SQLException will be reported. Here, we use a try block followed by multiple catch blocks to solve the problem.
-
-
4,Method parameter URL: uniform resource locator. Which host is the database we are connected to (this is determined by ip address) , the host may have several database software installed, such as sqlserver, mysql and oracle. The port number is also called the service number listening number. Sqlserver is 1433, mysql is 3306 and oracle is 1521:. The following table lists the URL writing methods of commonly used database software:
-
-
Oracle: jdbc:oracle:thin:@ip:1521:dbName；
-
-
MySql: jdbc:mysql://ip:3306:dbName；
-
SQLServer: jdbc:sqlserver://ip:1443;databaseName=dbName；
-
-
5,When using the local ip address to connect, you need to turn off the firewall. Otherwise, you can't connect. If you use localhost or 127.0.0.1, you don't need to turn off the firewall.
-
*/
-
- Create Statement object
-
/*1,Run a static SQL Statement. Usually implemented through a Statement instance.
-
2,Run dynamic SQL statements. Usually implemented through PreparedStatement instances.
-
3,Run a database stored procedure. Usually implemented through a CallableStatement instance.*/
-
-
-
//Detailed implementation: sql injection cannot be prevented
-
Statement stmt = con.createStatement();
-
-
-
//Can prevent sql injection
-
PreparedStatement pstmt=con.prepareStatement(sql);
-
-
CallableStatement cstmt =con.prepareCall( "{CALLdemoSp(? , ?
-
-
)}") ;
-
-
Write and execute sql statements
-
/*Statement Interface provides three methods to run SQL statements: executeQuery, executeUpdate, and execute
-
-
1,ResultSet executeQuery(String sql): Runs an SQL statement that queries the database. Returns a ResultSet object.
-
2,int executeUpdate(String sql): It is used to run INSERT, UPDATE or DELETE statements and SQL DDL statements, such as create table and DROP TABLE
-
3,execute(sql):Used to run statements that return multiple result sets, multiple update counts, or a combination of both.*/
-
-
//Detailed implementation code:
-
ResultSet rs = stmt.executeQuery( "SELECT * FROM ...") ;
-
int rows = stmt.executeUpdate( "INSERTINTO ...") ;
-
boolean flag =stmt.execute(String sql) ;
-
-
There are two cases of processing results:
-
/* 1,Running update returns the number of records affected by this operation.
-
-
2,The result returned by running the query is a ResultSet object.
-
-
ResultSet It includes all rows that meet the conditions in the SQL statement, and it provides access to the data in these rows through a set of get methods.*/
-
-
//Get data using the access method of the ResultSet object:
-
-
while(rs.next()){
-
-
String name = rs.getString( "name") ;
-
-
String pass = rs.getString( 1) ; // This method is more efficient
-
-
}
-
(Columns are numbered from left to right, and 1 (start)
-
-
Close resource release resource
-
/*After the operation is completed, close all the JDBC objects used to release JDBC resources. The closing order is opposite to the declaration order:
-
-
1,Close Recordset
-
-
2,Close declaration
-
-
3,Close connection object
-
*/
-
-
if(rs != null){ // Close Recordset
-
-
try{
-
-
rs.close() ;
-
-
} catch(SQLException e){
-
-
e.printStackTrace() ;
-
-
}
-
-
}
-
-
if(stmt != null){ // Close declaration
-
-
try{
-
-
stmt.close() ;
-
-
} catch(SQLException e){
-
-
e.printStackTrace() ;
-
-
}
-
-
}
-
-
if(conn != null){ // Close connection object
-
-
try{
-
-
conn.close() ;
-
-
} catch(SQLException e){
-
-
e.printStackTrace() ;
-
-
}
-
-
}
Every time the client accesses the database, the database returns the client data, accompanied by the creation and destruction of a connection. The main purpose of introducing connection pool is to improve performance, Because creating a new JDBC connection will lead to networking operations and certain jdbc driver overhead, followed by reducing resource overhead.
When a thread needs to use JDBC to perform operations on MySQL or other databases, it needs to use the connection provided by the connection pool. After using the connection to complete the thread, the thread will return the connection to the connection pool so that the connection can be used by other threads that need to use the connection. Borrow from the connection pool When connecting, the connection is only used by the thread requesting it. From a programming point of view, its effect is equivalent to calling DriverManager.getConnection() every time a JDBC connection is required. However, using connection pooling technology, the thread can be ended by using a new or existing connection. Connection pooling technology can significantly increase the performance of Java applications and reduce resource utilization.
The mainstream connection pools on the market include c3p0, dbcp, druid, Tomcat JDBC, hikaricp (springboot 2.0 default connection pool), proxool, etc
Here is a simple database connection pool:
The connection pool is actually a container. Multiple threads may acquire and release connections at a certain time, so thread safety needs to be guaranteed
-
package cn.itcats.thread.datasoursepool;
-
/**
-
* Custom database connection pool
-
* @author fatah
-
*/
-
-
import java.sql.Connection;
-
import java.sql.DriverManager;
-
import java.sql.SQLException;
-
import java.util.LinkedList;
-
import java.util.concurrent.locks.Condition;
-
import java.util.concurrent.locks.Lock;
-
import java.util.concurrent.locks.ReentrantLock;
-
-
public class MyDataSourcePool {
-
-
// Use container - there are few use environment query criteria, mainly for adding, deleting and modifying operations
-
private LinkedList<Connection> pool = new LinkedList<>();
-
-
private static final int INIT_CONNECTIONS = 10;
-
private static final String DRIVER_CLASS = "";
-
private static final String USERNAME = "";
-
private static final String PASSWORD = "";
-
private static final String URL = "";
-
private Lock lock = new ReentrantLock();
-
private Condition condition = lock.newCondition();
-
-
static {
-
// Register driver
-
try {
-
Class.forName( "com.mysql.jdbc.Driver");
-
} catch (ClassNotFoundException e) {
-
e.printStackTrace();
-
}
-
}
-
-
// Initialize the connection through the construction method
-
public MyDataSourcePool() {
-
for ( int i = 0; i < INIT_CONNECTIONS; i++) {
-
try {
-
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
-
pool.addLast(conn);
-
} catch (SQLException e) {
-
e.printStackTrace();
-
}
-
}
-
}
-
-
// Get database connection
-
public Connection getConnection() {
-
Connection conn = null;
-
lock.lock();
-
try {
-
while (pool.size() < 0) {
-
try {
-
condition.await();
-
} catch (InterruptedException e) {
-
e.printStackTrace();
-
}
-
}
-
if (!pool.isEmpty()) {
-
conn = pool.removeFirst();
-
}
-
return conn;
-
} finally {
-
lock.unlock();
-
}
-
}
-
-
// Release database connection
-
public void releaseConnection(Connection conn) {
-
if (conn != null) {
-
lock.lock();
-
try {
-
// Releasing a connection is the process of putting the connection back into the connection pool
-
pool.addLast(conn);
-
condition.signal();
-
} finally {
-
lock.unlock();
-
}
-
}
-
}
-
}
-