The last blog just talked about how to customize a database connection pool. Of course, this customized database connection pool is very simple, and can not write excellent connection pool by its own ability. However, don't worry, we can use open source database connection pools, which is the advantage of open source.
There are three open source database connection pools in Java that provide independent implementations of data sources:
- DBCP database connection pool
- C3P0 database connection pool
- Apache Tomcat's built-in connection pool (apache dbcp)
1. DBCP database connection pool
DBCP is an open source connection pool implementation under Apache Software Fund. Using DBCP data source, applications should add the following two jar files to the system:
- commons-dbcp.jar
- commons-pool.jar
Today's jar package resources are also on the disks. Link: https://pan.baidu.com/s/1-Rj4TQLI_5DGLdfb45N62g
Extraction code: ba3a
Duplicate this content and open Baidu Disk Mobile App. It's more convenient to operate.
The jar package is ready, so let's use it.
Create a new Java project.
Then create a new test class DBCPTest and write the test code
@Test
public void demo1(){
//First create the connection pool using Basic Data Source
BasicDataSource basicDataSource = new BasicDataSource();
try {
//Getting connections from connection pools
Connection connection = basicDataSource.getConnection();
String sql = "select * from account";
PreparedStatement stmt = connection.prepareStatement(sql);
ResultSet set = stmt.executeQuery();
while(set.next()){
System.out.println(set.getString("name"));
}
//Releasing resources
JDBCUtils.release(stmt, connection);
} catch (SQLException e) {
e.printStackTrace();
}
}Now run the test code, can you query the table data?
Obviously, even the database parameters are not given, this test code will surely report errors.
Now let's set the parameters and modify the test code.
@Test
public void demo1(){
//First create the connection pool using Basic Data Source
BasicDataSource basicDataSource = new BasicDataSource();
//Creating a connection requires four parameters
basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
basicDataSource.setUrl("jdbc:mysql:///test");
basicDataSource.setUsername("root");
basicDataSource.setPassword("123456");
try {
//Getting connections from connection pools
Connection connection = basicDataSource.getConnection();
String sql = "select * from account";
PreparedStatement stmt = connection.prepareStatement(sql);
ResultSet set = stmt.executeQuery();
while(set.next()){
System.out.println(set.getString("name"));
}
//Releasing resources
JDBCUtils.release(stmt, connection);
} catch (SQLException e) {
e.printStackTrace();
}
}Now run the test code
Table data was successfully queried. However, there are limitations in writing this way. The database parameters should be written as configuration files to meet the flexible and changeable user needs.
Create a new configuration file dbcp.properties under the src directory
driverClassName = com.mysql.jdbc.Driver url = jdbc:mysql:///test username = root password =123456
Write test code
@Test
public void demo2() throws Exception{
//Read dbcp.properties
Properties properties = new Properties();
properties.load(new FileInputStream(this.getClass().getResource("/dbcp.properties").getFile()));
DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
//Getting connections from connection pools
Connection connection = dataSource.getConnection();
String sql = "select * from account";
PreparedStatement stmt = connection.prepareStatement(sql);
ResultSet set = stmt.executeQuery();
while(set.next()){
System.out.println(set.getString("name"));
}
//Releasing resources
JDBCUtils.release(stmt, connection);
}Then run
The operation was successful.
Attach a more comprehensive configuration file.
#connections setting up driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbc username=root password= #<! - Initialize the connection - > initialSize=10 #Maximum number of connections maxActive=50 #<! - Maximum idle connection maxIdle=20 #<! - Minimum idle connection - > minIdle=5 #<! - Overtime waiting time in milliseconds is 6000 milliseconds/1000 equal to 60 seconds - > maxWait=60000 #The format of the join attribute attributes attached to the JDBC driver when establishing a connection must be as follows: [attribute name = property;] #Note that the attributes "user" and "password" will be passed explicitly, so they need not be included here. connectionProperties=useUnicode=true;characterEncoding=gbk #Specifies the auto-commit state of the connection created by the connection pool. defaultAutoCommit=true #driver default specifies the transaction level (Transaction Isolation) of the connection created by the connection pool. #Available values are one of the following: (Details can be found in javadoc.) NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE defaultTransactionIsolation=READ_UNCOMMITTED
2. C3P0 database connection pool
jar package download address: https://sourceforge.net/projects/c3p0/
Official address: https://www.mchange.com/projects/c3p0/
There are also download portals and database connection pool tutorials on the official website.
Documentation tutorials on the official website are very detailed, so it's not too much to elaborate on.
Take the case directly.
Write test code
@Test
public void demo1() throws Exception{
//Create a connection pool
ComboPooledDataSource dataSource = new ComboPooledDataSource();
//Set four parameters
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql:///test");
dataSource.setUser("root");
dataSource.setPassword("123456");
Connection connection = dataSource.getConnection();
String sql = "select * from account";
PreparedStatement stmt = connection.prepareStatement(sql);
ResultSet set = stmt.executeQuery();
while(set.next()){
System.out.println(set.getString("name"));
}
JDBCUtils.release(stmt, connection);
}It should be noted that if the ClassNotFoundExecution exception is reported at runtime, it means that your jar package version is after c3p0-0.9.2, and a secondary package, mchange-commons-java-0.2.3.4.jar, needs to be added to the later version.
This is the download address of the jar package. Link: https://pan.baidu.com/s/17o0s92Us-UPQPJFejpOzQ
Extraction code: lt2v
Duplicate this content and open Baidu Disk Mobile App. It's more convenient to operate.
Similarly, use configuration files to implement it.
New file c3p0-config.xml in src directory
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///test</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="automaticTestTable">con_test</property>
<property name="checkoutTimeout">30000</property>
<property name="idleConnectionTestPeriod">30</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
<user-overrides user="test-user">
<property name="maxPoolSize">10</property>
<property name="minPoolSize">1</property>
<property name="maxStatements">0</property>
</user-overrides>
</default-config>
<!-- This app is massive! -->
<named-config name="intergalactoApp">
<property name="acquireIncrement">50</property>
<property name="initialPoolSize">100</property>
<property name="minPoolSize">50</property>
<property name="maxPoolSize">1000</property>
<!-- intergalactoApp adopts a different approach to configuring statement
caching -->
<property name="maxStatements">0</property>
<property name="maxStatementsPerConnection">5</property>
<!-- he's important, but there's only one of him -->
<user-overrides user="master-of-the-universe">
<property name="acquireIncrement">1</property>
<property name="initialPoolSize">1</property>
<property name="minPoolSize">1</property>
<property name="maxPoolSize">5</property>
<property name="maxStatementsPerConnection">50</property>
</user-overrides>
</named-config>
</c3p0-config>This is copied directly from official documents. Four parameters for database configuration are added.
Then write the test code
@Test
public void demo2() throws Exception{
//Using the c3p0 configuration file
ComboPooledDataSource dataSource = new ComboPooledDataSource();
Connection connection = dataSource.getConnection();
String sql = "select * from account";
PreparedStatement stmt = connection.prepareStatement(sql);
ResultSet set = stmt.executeQuery();
while(set.next()){
System.out.println(set.getString("name"));
}
JDBCUtils.release(stmt, connection);
}Note that the c3p0 database connection pool does not need to load the configuration files manually. c3p0 is very human, and this step has been encapsulated. After creating the ComboPooledDataSource object, the connection can be obtained by calling the getConnection() method directly. The premise is that your configuration file name must be c3p0-config.xml, and the file must be placed in the src directory. As for other configuration information, there are explanations in the document, you can read and learn by yourself.
There are two ways to create a ComboPooled DataSource object. Calling a parametric construct uses the default configuration in the configuration file. If a parametric construct is called, the name attribute value of the tag in the configuration file is passed in, and the configuration information in the tag will be used.
3. Tomcat's built-in connection pool
What you need to know is that Tomcat's content connection pool is DBCP.
We know that there are three ways to deploy a web project to Tomcat:
- Configure server.xml and add tags
- Configure separate xml files and add tags
- Copy the website directory directly to Tomcat's webapps directory
A virtual directory is required for any project to run in Tomcat. What does virtual directory mean? It refers to the Context element. When we need to use connection pooling, we need to configure Context as follows
<Context path="/DBTest" docBase="DBTest"
debug="5" reloadable="true" crossContext="true">
<!-- maxActive: Maximum number of dB connections in pool. Make sure you
configure your mysqld max_connections large enough to handle
all of your db connections. Set to -1 for no limit.
-->
<!-- maxIdle: Maximum number of idle dB connections to retain in pool.
Set to -1 for no limit. See also the DBCP documentation on this
and the minEvictableIdleTimeMillis configuration parameter.
-->
<!-- maxWait: Maximum time to wait for a dB connection to become available
in ms, in this example 10 seconds. An Exception is thrown if
this timeout is exceeded. Set to -1 to wait indefinitely.
-->
<!-- username and password: MySQL dB username and password for dB connections -->
<!-- driverClassName: Class name for the old mm.mysql JDBC driver is
org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
-->
<!-- url: The JDBC connection url for connecting to your MySQL dB.
-->
<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/javatest"/>
</Context>This is copied from the official website.
So where should I configure the Context element?
Configure the three locations of the Context element:
- Tomcat installation directory / conf / context. XML (if the connection pool is configured to this file, it will be valid for any project within the current Tomcat virtual host)
- Tomcat installation directory / conf/Catalina / virtual host directory / context. XML (if the connection pool is configured to this file, it will be valid for any current virtual host project)
- The root directory of the web project / META-INF/context.xml (if the connection pool is configured to this file, it will only be valid for the current project)
Next, let's demonstrate the third configuration.
Create a new file context.xml in the META-INF folder under WebRoot
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/DBTest" docBase="DBTest" debug="5" reloadable="true"
crossContext="true">
<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000" username="root"
password="123456" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/test" />
</Context>After the context.xml file is written, when the Tomcat server is started, Tomcat will automatically load the file and create a database connection pool, which is stored in the Tomcat container. At this point, we will use JNDI technology to access the database connection pool.
Children's shoes without knowledge of JNDI technology can be mentioned here a little.
JNDI(Java Naming and Directory Interface), Java Naming and Directory Interface, which corresponds to the javax.naming package in J2SE,
The main function of this API is that it can place Java objects in a container (supporting JNDI container Tomcat) and give a name to the Java objects in the container. In order to obtain Java objects later, the program only needs to retrieve them by name.
Its core API is Context, which represents the JNDI container, and its lookup method is to retrieve the object of the corresponding name in the container.
Matters needing attention:
Tomcat creates a connection pool, which requires a database driver to connect to the database. To copy mysql.jar to the lib folder in the Tomcat directory.
Java programs must run in the same JNDI container if they want to access objects through JNDI. That is to say, the Java program must run inside Tomcat, so programs using JNDI are usually Servlet s or JSP s.
Demonstrate.
Create a new Servlet file in the project as Tomcat Servlet
/**
* In this program, the connection pool inside Tomcat is accessed through JNDI
* @author Administrator
*
*/
public class TomcatServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
try {
//Create a retrieved object
Context initCtx = new InitialContext();
//The default search for the top-level Java name string is fixed: java:comp/env
Context envCtx = (Context) initCtx.lookup("java:comp/env");
//Find connection pool objects by setting name
DataSource ds = (DataSource) envCtx.lookup("jdbc/TestDB");
//Get a connection in the connection pool
Connection conn = ds.getConnection();
String sql = "select * from account";
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet set = stmt.executeQuery();
while(set.next()){
System.out.println(set.getString("name"));
}
JDBCUtils.release(stmt, conn);
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}Don't forget the database driver. Copy mysql-connector-java-5.1.47-bin.jar to the lib folder in the Tomcat installation directory, then deploy the project in MyEclipse and run it. Then access the Servlet program in the browser
Explain the success of the query.