Database connection pool
DBCP data source
DBCP is the abbreviation of DataBase Connection Pool. It is an open source connection pool implementation under Apache organization. It is also a connection pool component used by Tomcat server. When using DBCP data source alone, it needs to be in the application
Import two JAR packages from the. The details are as follows.
1.commons-dbcp.jar package
commons-dbcp.jar package is the implementation package of DBCP data source, which contains all methods to operate database connection information and database connection pool initialization information, and implements the getConnection() method of DataSource interface.
2. commons-pool.jar package
commons-pool.jar package is the dependent package of DBCP database connection pool implementation package, which is commons DBCP The methods in the jar package provide support. It can be said that there is no dependent package, common DBCP Many methods in the jar package cannot be implemented.
The two JAR packages can be found on the Apache official website“ http://commons.apache.org/proper/ ”The query is downloaded to. Where, commons DBCP JAR contains two core classes, BasicDataSourceFactory and BasicDataSource, which both contain methods to obtain DBCP data source objects.
1. In Chapter 13 Create an Example01 class under example:
The code is as follows:
package chapter13.example;
import java.awt.image.DataBufferShort;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import java.sql.DatabaseMetaData;
import java.sql.Connection;
public class example01 {
public static DataSource ds = null;
static {
BasicDataSource bds = new BasicDataSource();
bds.setDriverClassName("com.mysql.cj.jdbc.Driver");
bds.setUrl( "jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC");
bds.setUsername("root");
bds.setPassword("123456");
bds.setInitialSize(5);
bds.setMaxActive(5);
ds = bds;
}
public static void main(String[] args) throws SQLException{
Connection conn =ds.getConnection();
DatabaseMetaData metaData = conn.getMetaData();
System.out.println(metaData.getURL()+"UserName="+metaData.getUserName()+","+metaData.getDriverName());
}
}
The operation results are as follows:
Create a data source object by reading the configuration file
In addition to using BasicDataSource to directly create a data source object, you can also use the BasicDataSourceFactory factory class to read the configuration file, create a data source object, and then obtain a database connection object.
Create dbcpconfig. In the src directory The properties file is used to set the connection information of the database and the initialization information of the data source;
Create an Example02 class, which uses the method of obtaining the connection information of the database and the initialization information of the data source from the configuration file;
package chapter13.example;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import java.sql.DatabaseMetaData;
import java.sql.Connection;
public class example02 {
public static DataSource ds = null;
static {
Properties prop = new Properties();
try {
InputStream in = new example02().getClass().getClassLoader().getResourceAsStream("dbcp.properties");
prop.load(in);
ds = BasicDataSourceFactory.createDataSource(prop);
} catch (Exception e) {
// TODO: handle exception
throw new ExceptionInInitializerError(e);
}
}
public static void main(String[] args) throws SQLException{
Connection conn = ds.getConnection();
DatabaseMetaData metaData = conn.getMetaData();
System.out.println(metaData.getURL()+"UserName="+metaData.getUserName()+","+metaData.getDriverName());
}
}
The running result is the same as above;
Create a data source object through the ComboPooledDataSource() construction method
Import JAR package c3p0-0.9 1.2. JAR creates an Example03 class, which obtains the Connection object by using the C3PO data source Manual Code:
package chapter13.example;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class example03 {
public static DataSource ds = null;
static {
ComboPooledDataSource cpds = new ComboPooledDataSource();
try {
cpds.setDriverClass("com.mysql.cj.jdbc.Driver");
cpds.setJdbcUrl("jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC");
cpds.setUser("root");
cpds.setPassword("123456");
cpds.setInitialPoolSize(5);
cpds.setMaxPoolSize(15);
ds = cpds;
} catch (Exception e) {
// TODO: handle exception
throw new ExceptionInInitializerError(e);
}
}
public static void main(String[] args) throws SQLException{
System.out.println(ds.getConnection());
}
}
The operation results are as follows:
Create a data source object by reading the configuration file
Use the ComboPooledDataSource (String configName) constructor to read c3p0-config.xml configuration
Set the file to create the data source object, and then obtain the database connection object. Create a c3p0 - config. In the src root directory XML file, used to set the connection information of the database and the initialization information of the data source:
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">
jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC
</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="checkoutTimeout">30000</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>
</default-config>
<named-config name="dashuju">
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">
jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC
</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">15</property>
</named-config>
</c3p0-config>
c3p0-config.xml is configured with two sets of data sources, < default config ></ The information in default config > is the default configuration. If no configuration is specified, this configuration is used by default to create C3p0 data source objects< The information in named config >... < / named config > is user-defined configuration. There can be zero or more user-defined configurations in a configuration file. When the user needs to use user-defined configuration, call the ComboPooledDataSource(String configName) method and pass in the value of the name attribute in the < named config > node to create a C3PO data source object. The advantage of this setting is that when the program changes the data source configuration later, it only needs to modify the corresponding name value in the construction method.
Create an Example04 class, which uses the C3P0 data source to obtain the Connection object from the configuration file:
The code is as follows:
package chapter13.example;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class example04 {
public static DataSource ds = null;
static {
ComboPooledDataSource cpds = new ComboPooledDataSource("dashuju");
ds = cpds;
}
public static void main(String[] args) throws SQLException{
System.out.println(ds.getConnection());
}
}
Operation results:
DBUtils tool
1. BeanHandler and BeanListHandler
BeanHandler and BeanListHandler implementation classes encapsulate the data in the result set into the corresponding JavaBean instance, which is also the most commonly used result set processing method in actual development.
Create the data table user in the database named jdbc with the following statement.
USE jdbc; CREATE TABLE user( id INT(3) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, password VARCHAR(20) NOT NULL );
Insert three pieces of data into the user table. The specific statements are as follows
INSERT INTO user (name, password) VALUES ('zhangsan','123456');
INSERT INTO user (name, password) VALUES ('lisi','123456');
INSERT INTO user (name, password) VALUES ('wangwu','123456');
The results are shown in the figure below:
Create a class named BaseDao, in which a general query method is written
package chapter13.example;
import java.sql.SQLException;
import org.apache.commons.dbutils.ResultSetHandler;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;
public class basedao {
//Optimize query
public static Object query(String sql,ResultSetHandler<?> rsh,Object...params) throws SQLException{
Connection conn = null;
PreparedStatement pstmt= null;
ResultSet rs =null;
Object obj = null;
try {
conn = JDBCUtils.getConnection();
pstmt = conn.prepareStatement(sql);
for (int i = 0; params !=null && i<params.length; i++) {
pstmt.setObject(i+1, params[i]);
}
rs = pstmt.executeQuery();
obj = rsh.handle(rs);
} catch (Exception e) {
// TODO: handle exception
return new Exception(e.getMessage());
}finally {
JDBCUtils.release(rs,pstmt,conn);
}
return obj;
}
}
Create an entity class User, which is used to encapsulate the User object
package chapter13.example;
public class User {
private int id;
private String name;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
Create a class ResultSetTest1, which is used to demonstrate the processing of the result set by the BeanHandler class
package chapter13.example;
import java.sql.SQLException;
import org.apache.commons.dbutils.handlers.BeanHandler;
public class ResultSetTest1 {
public static void testBeanHandler() throws SQLException{
basedao basedao = new basedao();
String sql = "select * from user where id =?";
Object object = basedao.query(sql, new BeanHandler(User.class), 1);
if (object!=null&& object instanceof User) {
User user = (User) object;
System.out.println("id Is 1 User Object name Value is:"+user.getName());
}else {
System.out.println("The query result is empty:"+object);
}
}
public static void main(String[] args) throws SQLException{
testBeanHandler();
}
}
The operation result is:
Create a class ResultSetTest2, which is used to demonstrate the processing of the result set by the BeanListHandler class
package chapter13.example;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.commons.dbutils.handlers.BeanListHandler;
public class ResultSetTest2 {
public static void testBeanListHandler() throws SQLException{
basedao basedao = new basedao();
String sql = "select * from user";
ArrayList<User> list = (ArrayList<User>) basedao.query(sql, new BeanListHandler(User.class));
for (int i = 0; i < list.size(); i++) {
System.out.println("The first"+(i+1)+"Of data userna Value is:"+list.get(i).getName());
}
}
public static void main(String[] args) throws SQLException{
testBeanListHandler();
}
}Operation results:
2. ScalarHandler
When using DBUtils to operate the database, if you need to output the specified field value of a row of data in the result set, you can use the ScalarHandler class.
Create a class ResultSetTest3, which is used to demonstrate the use of the ScalarHandler class
package chapter13.example;
import java.sql.SQLException;
import org.apache.commons.dbutils.handlers.ScalarHandler;
public class ResultSetTest03 {
public static void testScalarHandler() throws SQLException{
basedao basedao= new basedao();
String sql = "select * from user where id =?";
Object arr = (Object) basedao.query(sql, new ScalarHandler("name"), 1);
System.out.println(arr);
}
public static void main(String[] args) throws SQLException{
testScalarHandler();
}
}
Operation results:
Using DBUtils to add, delete, modify and query
1. Create C3p0Utils class
Create a named pidan jdbc. Utils package, and then create C3p0Utils class under the package, which is used to create data sources
package chapter13.example;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3p0Utils {
public static DataSource ds;
static {
ds = new ComboPooledDataSource();
}
public static DataSource getDataSource() {
return ds;
}
}
2. Create DBUtilsDao class
Create a named pidan jdbc. Demo, and then create a DBUtilsDao class under the package, which implements the basic operations of adding, deleting, modifying and querying the user table.
package chapter13.example;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
public class DBUtilsDao {
public List findAll() throws SQLException{
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "select * from user ";
List list = (List) runner.query(sql, new BeanListHandler(User.class));
return list;
}
public User find(int id) throws SQLException{
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "select * from user where id=?";
User user = (User) runner.query(sql, new BeanHandler(User.class),new Object[] {id});
return user;
}
public Boolean insert(User user) throws SQLException{
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "insert into user (name,password) values (?,?)";
int num =runner.update(sql,new Object[] {user.getName(),user.getPassword()});
if (num>0)
return true;
return false;
}
public Boolean update(User user) throws SQLException{
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "update user set name=?,password=? where id=?";
int num = runner.update(sql,new Object[] {user.getName(),user.getPassword(),user.getId()});
if (num>0)
return true;
return false;
}
public Boolean delete(int id) throws SQLException{
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "delete from user where id=?";
int num = runner.update(sql,id);
if (num>0)
return true;
return false;
}
}
In the above code, the method with parameters in the QueryRunner class is used to pass the data source to the QueryRunner object, and the QuervRunner object is used to create and close the database connection.
In this way, the basic operation of database with DBUtils tool is realized. It should be noted that in the query method, BeanHandler and BeanListHandler implementation classes are used to process the result set. The BeanHandler class that can process one row of data is used to query one piece of data, and the BeanListHandler class that can process all rows of data is used to query all data. Do not use it incorrectly, otherwise the program will report an error.
3. Test the addition, deletion, modification and query operations in DBUtilsDao class
Create class DBUtilsDaoTest1 to test the addition operation:
package chapter13.example;
import java.sql.SQLException;
public class DBUtilsDaoTest1 {
private static DBUtilsDao dao = new DBUtilsDao();
public static void testInsert() throws SQLException{
User user = new User();
user.setName("zhaoliu");
user.setPassword("666666");
boolean b =dao.insert(user);
System.out.println(b);
}
public static void main(String[] args) throws SQLException {
testInsert();
}
}
The operation results are as follows:
Create test class DBUtilsDaoTest2:
package chapter13.example;
import java.sql.SQLException;
public class DBUtilsDaoTest2 {
private static DBUtilsDao dao = new DBUtilsDao();
public static void testUpdate() throws SQLException{
User user = new User();
user.setName("zhaoliu");
user.setPassword("666777");
user.setId(4);
boolean b = dao.update(user);
System.out.println(b);
}
public static void main(String[] args) throws SQLException {
testUpdate();
}
}
The operation results are as follows
Create test class DBUtilsDaoTest3
The code is as follows:
package chapter13.example;
import java.sql.SQLException;
public class DBUtilsDaoTest3 {
private static DBUtilsDao dao = new DBUtilsDao();
public static void testfind() throws SQLException{
User user = dao.find(2);
System.out.println(user.getId()+","+ user.getName()+ ","+user.getPassword());
}
public static void main(String[] args) throws SQLException{
testfind();
}
}
The operation results are as follows:
As can be seen from the figure, the console has successfully displayed the data information with id 2, which indicates that the query method has also been successfully executed. So far, you have completed the basic operations on the database using the DBUtils tool. From the code, we can see that DBUtils tool not only reduces the amount of code, but also enhances the regularity and readability of the code.