##Database connection pool
The code logic in the previous section is equivalent to opening a restaurant, recruiting a waiter connection to connect to the database service when the guest comes, and firing the close when the guest leaves So we should recruit a few employees. It's a great waste of resources
1. Concept: it is actually a container (Collection) that stores database connection s.
After the system is initialized, the container is created, and some connection objects will be applied in the container. When the user accesses the database, the connection objects will be obtained from the container. After the user accesses, the connection objects will not be close d, but will be returned to the container. The access speed is fast and the resources are saved.
2. Benefits:
1. Save resources (connection objects are reused)
2. Efficient user access
3. Achieve:
1. Standard interface: DataSource Under javax.sql package
1. Method:
* Get connection: getConnection()
* Return Connection: Connection.close(). If the Connection object Connection is obtained from the Connection pool, the Connection will not be closed by calling the Connection.close() method. Instead, return the Connection
2. These connection pools are generally not implemented by us, but by database manufacturers Two database connection pool technologies:
1. C3P0: database connection pool technology
2. Druid: database connection pool implementation technology, provided by Alibaba
4. C3P0: database connection pool technology
* Steps:
1. Import jar packages (two) c3p0-0.9.5.2.jar and mchange-commons-java-0.2.12.jar ，
* Don't forget to import the database driver jar package
2. Define the configuration file properties:
* Name: c3p0.properties or c3p0-config.xml
* Path: directly put the file in the src directory.
3. Create the core object: the database connection pool object ComboPooledDataSource
4. Get connection: getConnection
* code:
// 1. Create database connection pool object
DataSource ds = new ComboPooledDataSource();
// 2. Get the connection object
Connection conn = ds.getConnection();
package cn.itcast.datasource.c3p0;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
/**
* c3p0 Presentation of
*/
public class C3P0Demo1 {
public static void main(String[] args) throws SQLException {
//1. Create database connection pool object
DataSource ds = new ComboPooledDataSource();
//2. Get the connection object
Connection conn = ds.getConnection();
//3. Printing
System.out.println(conn);
}
}
Output red log information White is the conn object address value string
Demonstration of c3p0 database connection pool configuration:
code:
At this time, there can only be ten cycles. If it exceeds, an error will be reported
However, if a connection is returned soon, it can be executed eleven times
The two ticked are the same object connection object
In another case, the default is not used Get the parameterized database connection pool core object This is not often the case
DataSource ds = new ComboPooledDataSource("otherc3p0");
Because there are two groups in the configuration file
Running for eight times and waiting for 1000 milliseconds will report an error
5. Druid: database connection pool implementation technology, provided by Alibaba
1. Steps:
1. Import the jar package druid-1.0.9.jar
2. Define profile:
* It is in the form of properties
* You can call any name and put it in any directory
3. Load the configuration file. Properties c3p0 as long as the name pair and are placed under src, you don't need to load it
4. Get database connection pool object: through the factory DruidDataSourceFactory
5. Get connection: getConnection
* code:
// 3. Load configuration file
Properties pro = new Properties();
InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(is);
// 4. Get connection pool object
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
// 5. Get connection
Connection conn = ds.getConnection();
package cn.itcast.druid;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
/*
Druid demonstration
*/
public class DruidDemo {
public static void main(String[] args) throws Exception {
//1. Import jar package
//2. Define the configuration file druid.properties and put it at will
//3. Load configuration file
Properties pro=new Properties();
InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(is);//is into load(); You can load the file and load the property name and property value pairs in the file into the properties collection
//3. Get connection pool object
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
//4. Get connection
Connection conn = ds.getConnection();
System.out.println(conn);
}
}
2. Define tool classes
1. Define a class JDBCUtils
2. Provide static code blocks, load configuration files, and initialize connection pool objects
3. Provide 3 methods
1. Get connection method: get connection through database connection pool
2. Release resources
3. Method of obtaining connection pool
package cn.itcast.jdbcUtils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/*
Druid Tool class for connection pool
*/
public class JdbcUtils {
//Static code block
//1. Define member variable DataSource
private static DataSource ds;
static {
try {
//1. Load configuration file
Properties pro = new Properties();
pro.load(JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
//2. Get DataSource
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//Get connection
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
//Release resources
public static void close(Statement stmt,Connection conn){ //dml operation stmt conn
if (stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();//Note that this is return, not release
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet rs,Statement stmt,Connection conn){ // dql rs stmt conn
if (rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();//Note that this is return, not release
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//Method to get connection pool
public static DataSource getDataSource(){
return ds;
}
Tool class usage demonstration
package cn.itcast.datasource.druid;
import cn.itcast.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Use new tool classes
*/
public class DruidDemo2 {
public static void main(String[] args) {
/*
* Complete the add operation: add a record to the account table
*/
Connection conn = null;
PreparedStatement pstmt = null;
try {
//1. Get connection
conn = JDBCUtils.getConnection();
//2. Define sql
String sql = "insert into account values(null,?,?)";
//3. Get pstmt object
pstmt = conn.prepareStatement(sql);
//4. Here? assignment
pstmt.setString(1,"Wang Wu");
pstmt.setDouble(2,3000);
//5. Execute sql
int count = pstmt.executeUpdate();
System.out.println(count);
} catch (SQLException e) {
e.printStackTrace();
}finally {
//6. Release resources
JDBCUtils.close(pstmt,conn);
}
}
}
Output 1 indicates that the addition is successful
If you do a query, you should encapsulate the data as an object
For example:
##Under the Spring framework JDBC
* Spring framework's simple encapsulation of JDBC. Provides a JDBC template object to simplify JDBC development
* Steps:
1. Import jar package
2. Create a JdbcTemplate object. Dependent on DataSource datasource
* JdbcTemplate template = new JdbcTemplate(ds);
Here, the parameter is passed ds=DataSource jdbc utils specially wrote a method to obtain jdbc connection pool
3. Call the method of JdbcTemplate to complete CRUD operation
* update(): execute DML statement. Add, delete and modify statements
* queryForMap(): The query result encapsulates the result set as a map set, the column name as the key, the value as the value, and the record as a map set
* Note: the length of the result set of this method query can only be 1
* queryForList(): Query results encapsulate the result set as a list set
* Note: encapsulate each record into a Map set, and then load the Map set into the List set
* query(): The query result is encapsulated as a JavaBean object
* Parameter of query: RowMapper
* Generally, we use the BeanPropertyRowMapper implementation class. It can complete the automatic encapsulation of data to JavaBean s
* New beanpropertyrowmapper < type > (type. class)
* queryForObject(): query results, which are encapsulated as objects
* It is generally used for queries of aggregate functions