Java Web - Database Connection Pool and DBUtils Tools

Database Connection Pool

Definition

In JDBC programming, each time a Connection object is created and disconnected, it takes time and IO resources. This is because when a Java program establishes a connection to a database, the database side verifies the user name and password, and to allocate resources for the connection, the JAVA program needs to represent the Java of the connection. Sql. Connection objects are loaded into memory, so data connections are expensive, especially when there is a lot of concurrent access. If a website has 100,000 visits a day, then its servers need to be created, disconnected 100,000 times and created frequently. Disconnecting the database connection will inevitably affect the efficiency of database access and even cause the database to crash.
To avoid frequent creation of database connections, database connection pooling technology has emerged. The database connection pool is responsible for allocating, managing, and releasing database connections. It allows applications to reuse existing database connections instead of re-establishing the database connection pool. When initialized, it places a certain number of database connections in the connection pool. When an application accesses the database, it does not create connections directly. Instead, request a connection from the connection pool. If there is an idle Connection in the connection pool, it is returned, otherwise a new Connection is created. Once used, the connection pool recycles the connection and delivers it to other threads to reduce the number of database connections created and disconnected and to improve database access efficiency

DataSource interface

JDBC provides javax for obtaining database connection objects. Sql. The DataSource interface, which is responsible for establishing a connection to the database and defines a method that returns a Connection object. To get the database connection object,

1.Connection getConnection()

2.Connection getConnection(String username,String password)

DBCP Data Source

Get the connection information of the database and initialization information of the data source manually

public static void main(String[] args) throws SQLException {
		// Get Database Connection Object
		Connection conn = ds.getConnection();
		//Get database connection information
		DatabaseMetaData metaData = conn.getMetaData();
		//Print database connection information
		System.out.println(metaData.getURL()
             +",UserName="+metaData.getUserName()
             +","+metaData.getDriverName());
	}

Create data source object by reading configuration file

 #\u8FDE\u63A5\u8BBE\u7F6E
 driverClassName=com.mysql.jdbc.Driver
 url=jdbc:mysql://localhost:3306/jdbc
 username=root
 password=123456
 #\u521D\u59CB\u5316\u8FDE\u63A5
 initialSize=5
 #\u6700\u5927\u8FDE\u63A5\u6570\u91CF
 maxActive=10
 #\u6700\u5927\u7A7A\u95F2\u8FDE\u63A5
 maxIdle=10

C3P0 Data Source

C3p0 is one of the most popular open source database connection pools at present. When using c3p0, you can use the parametric and nonparametric methods of CombopooledDataSource (); Or create a data source object by reading the configuration file. The implementation class of the DataSource interface in C3P0, ComboPooledDataSource, is the core class of C3P0 and provides related methods for data source objects.

DBUtils Tools

DbUtils is a JDBC tool that is widely used to simplify JDBC code. It is an integral component of the Reference Database, which implements a simple encapsulation of JDBC and greatly simplifies the encoding workload of JDBC without affecting performance.
Core class: QueryRunner; ResultSetHandler (an interface that essentially completes ORM mapping and converts the result street to
java objects we need)
Core approach:

• update(); Used to execute DDL (DDL: create alert,drop;);
• query(); Used to execute DML (DML:insert update delete;);
• batch(); Used to perform batch processing;

package chapter10.jdbc.demo;
import java.sql.SQLException;
import java.util.List;

import chapter10.chapter10.example.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import chapter10.jdbc.utils.C3p0Utils;
public class DBUtilsDao {
	// Query all, return List collection
	public List findAll() throws SQLException {
		// Create QueryRunner Object
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		// Write SQL statements
		String sql = "select * from user";
		// Call Method
		List list = (List) runner.query(sql,
                     new BeanListHandler(User.class));
		return list;
	}
	// Query single, return object
	public User find(int id) throws SQLException {
		// Create QueryRunner Object
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		// Write SQL statements
		String sql = "select * from user where id=?";
		// Call Method
		User user = (User) runner.query(sql, 
                 new BeanHandler(User.class), new Object[] { id });
		return user;
	}
	// Add User Action
	public Boolean insert(User user) throws SQLException {
		// Create QueryRunner Object
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		// Write SQL statements
		String sql = "insert into user (name,password) values (?,?)";
		// Call Method
		int num = runner.update(sql,
				new Object[] { user.getName(), user.getPassword() });
		if (num > 0)
			return true;
		return false;
	}
	// Modify user's actions
	public Boolean update(User user) throws SQLException {
		// Create QueryRunner Object
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		// Write SQL statements
		String sql = "update  user set name=?,password=? where id=?";
		// Call Method
		int num = runner.update(sql, new Object[] { user.getName(),
                     user.getPassword(),user.getId() });
		if (num > 0)
			return true;
		return false;
	}
	// Delete user action
	public Boolean delete(int id) throws SQLException {
		// Create QueryRunner Object
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		// Write SQL statements
		String sql = "delete from user where id=?";
		// Call Method
		int num = runner.update(sql, id);
		if (num > 0)
			return true;
		return false;
	}
}

package chapter10.jdbc.demo;
import java.sql.SQLException;

import chapter10.chapter10.example.User;

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();
	}
}

package chapter10.jdbc.demo;
import java.sql.SQLException;

import chapter10.chapter10.example.User;

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();
	}
}