MySQL database connection pool (04)

Database connection pool

Why database connection pooling? If you don’t use the connection pool every business requests from the client need to correspond to a database connection, if there is ten thousand requests have ten thousand the establishment of the database connection and disconnection, frequent switch connection affects the execution efficiency of program, through the database connection pool database connections can be reuse, thus improve the execution efficiency.

How to use it?

DruidDataSource ds = new DruidDataSource(); // Set database connection information ds.setDriverclassName (driver); ds.setUrl(url); ds.setUsername(username); ds.setPassword(password); // Set the initial number of connections ds.setInitialSize(integer.parseInt (initSize)); // Set the maximum number of connections ds.setMaxActive(integer.parseint (maxSize)); Connection conn = ds.getConnection();Copy the code

jdbc.properties

db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/newdb3? characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
db.username=root
db.password=123456
db.maxActive=10
db.initialSize=2
Copy the code

### Read the *.properties configuration file

Properties p = new Properties(); / / file input stream This writing will automatically go to the SRC/main/resources directory to find the file InputStream ips = Demo03. Class. GetClassLoader () .getResourceAsStream("my.properties"); // Pass the file input stream to the property object p.lloyd (ips); String name = p.gettProperty ("name"); // The getProperty method can only read the String String age = p.getProperty("age"); System.out.println(name+":"+age);Copy the code

Read the jdbc.properties configuration file

public class DBUtils {
	private static DruidDataSource ds;
	// Because the configuration file needs to be read only once, the connection pool object needs to be created only once
	static {
		// Read the data in the configuration file
		Properties p = new Properties();
		InputStream ips = DBUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
		try {
			p.load(ips);
		} catch (IOException e) {

			e.printStackTrace();
		}
		String driver = p.getProperty("db.driver");
		String url = p.getProperty("db.url");
		String username = p.getProperty("db.username");
		String password = p.getProperty("db.password");
		String maxActive = p.getProperty("db.maxActive");
		String initialSize = p.getProperty("db.initialSize");
		ds = new DruidDataSource();
		ds.setDriverClassName(driver);
		ds.setUrl(url);
		ds.setUsername(username);
		ds.setPassword(password);
		// Set the initial number of connections
		ds.setInitialSize(Integer.parseInt(initialSize));
		// Set the maximum number of connections
		ds.setMaxActive(Integer.parseInt(maxActive));
	}
	public static Connection getConn(a) throws SQLException {
		// Register the driver
		// Get a connection exception throw from the connection pool object
		Connection conn = ds.getConnection();
		System.out.println(conn);
		returnconn; }}Copy the code

Create user table use newdb3; create table user(id int primary key auto_increment,username varchar(20),password varchar(20))charset=utf8;

### Register function

import java.sql.Connection;
import java.sql.Statement;
import java.util.Scanner;
/** * Register function *@authorWanghao 😋 *@dateMay 7, 2020, 2:04:42 PM */
public class Demo04 {
	public static void main(String[] args) {
		Scanner sc = new Scanner(System.in);
		System.out.println("Please enter user name");
		String username = sc.nextLine();
		System.out.println("Please enter your password");
		String password = sc.nextLine();
		sc.close();
		// Get the connection
		try (Connection conn = DBUtils.getConn();) {
			Statement s = conn.createStatement();
			String sql = "insert into user values(null,'"+username+"', '"+password+"')";
			s.executeUpdate(sql);
			System.out.println("Registration successful");
		} catch(Exception e) { e.printStackTrace(); }}}Copy the code

### Login

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
/** * Login function *@authorWanghao 😋 *@dateMay 7, 2020, 2:11:29 PM */
public class Demo05 {
	public static void main(String[] args) {
		Scanner sc = new Scanner(System.in);
		System.out.println("Please enter user name");
		String username = sc.nextLine();
		System.out.println("Please enter your password");
		String password = sc.nextLine();
		
		// Get the connection
		try (Connection conn = DBUtils.getConn();) {
			Statement s = conn.createStatement();
// String sql =
// "select count(*) from user where username='"
// +username+"'and password='"+password+"'";
// ResultSet rs = s.executeQuery(sql);
			
			// Use PrepredStatement to resolve SQL injection problems
			String sql = "select count(*) from user where username=? and password=?";
			// The business logic of the SQL statement has been locked at compile time
			PreparedStatement ps = conn.prepareStatement(sql);
			// Replace SQL with?
			ps.setString(1, username);
			ps.setString(2, password);
			/ / SQL execution
			ResultSet rs = ps.executeQuery();
			// The query returns the number of items that match the criteria, so we can use if if the query returns is
			// Multiple pieces of data must be used while
			if(rs.next()) {
				// Retrieve the number of matches in the query
				int count =rs.getInt(1);
				if(count>0) {
					System.out.println("Login successful");
				}else {
					System.out.println("Login failed"); }}}catch(Exception e) { e.printStackTrace(); }}}Copy the code

# # # the Statement and a PreparedStatement

If you execute SQL statements that contain variables, use a PreparedStatement to avoid SQL injection
Statement is used if there are no variables in the SQL Statement

## batch operation

Batch operation combines multiple network data transfers during the execution of multiple SQL statements into one transfer to improve execution efficiency
The code is demo06.java

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
/** **@authorWanghao 😋 *@dateMay 7, 2020, 3:27:24 PM */
public class Demo06 {
	public static void main(String[] args) {
		// Get the connection
		try (Connection conn = DBUtils.getConn();) {
			String sql1=Insert into user values(null,' liu ','123')";
			String sql2=Insert into user values(null,' 123','123')";
			String sql3=Insert into user values(null,' 123','123')";
			Statement s = conn.createStatement();
// s.executeUpdate(sql1);
// s.executeUpdate(sql2);
// s.executeUpdate(sql3);
			// Merge multiple data transfers into a single batch operation
// s.addBatch(sql1);
// s.addBatch(sql2);
// s.addBatch(sql3);
// // Perform batch operations
// s.executeBatch();
// system.out. println(" execute done ");
			//preparedStatement batch operation
			String sql = "insert into user values(null,? ,?) ";
			PreparedStatement ps = conn.prepareStatement(sql);
			for (int i = 0; i<100; i++) { ps.setString(1."name"+i);
				ps.setString(2."pw"+i);
				// Add to batch operation
				ps.addBatch();
				// Execute every 20 times to avoid memory overflow
				if(i%20= =0) { ps.executeBatch(); }}// Perform batch operations
			ps.executeBatch();
			System.out.println("Execution completed");
		} catch(Exception e) { e.printStackTrace(); }}}Copy the code

### paging query

The code is demo07.java

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
/** ** *@authorWanghao 😋 *@dateMay 7, 2020, 4:22:49 PM */
public class Demo07 {
	public static void main(String[] args) {
		Scanner sc = new Scanner(System.in);
		System.out.println("Please enter page number of enquiry");
		int page = sc.nextInt() ;
		System.out.println("Please enter the number of queries");
		int count = sc.nextInt();
		if(page<1) {
			System.out.println("Input error");
		}
		// Get the connection
		try (Connection conn = DBUtils.getConn();) {
			String sql ="select username,password from user limit ? That?";
			PreparedStatement ps =conn.prepareStatement(sql);
			// The first one? Number of pages skipped = (requested page -1) * number of pages
			ps.setInt(1, (page-1)*count);
			// The second one? Number of requests (per page)
			ps.setInt(2,count);
			// Execute the query
			ResultSet rs = ps.executeQuery();
			while(rs .next()) {
			String username = rs.getString(1);
			String password = rs.getString(2);
			System.out.println( username+":"+password) ; }}catch(Exception e) { e.printStackTrace(); }}}Copy the code

Get the increment primary key value

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
/** * gets the autoincrement primary key *@authorWanghao 😋 *@dateMay 7, 2020, 4:40:33 */
public class Demo08 {
	public static void main(String[] args) {
		// Get the connection
		try (Connection conn = DBUtils.getConn();) {
			String sql =
					"insert into user values(null,'james','123456')";
			Statement s = conn.createStatement();
			When compiling SQL statements, you need to set the return autoincrement primary key value
			s.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
			// Get the primary key increment from the result set
			ResultSet rs  = s.getGeneratedKeys();
			if(rs.next()) {
				int id =rs.getInt(1);
				System.out.println("id="+id);
			}
			System.out.println("Insert complete");
		} catch(Exception e) { e.printStackTrace(); }}}Copy the code

Create table team(id int primary key auto_increment,name vARCHar (10))charset=utf8;
Create table player(id int primary key auto_increment,name vARCHar (10),teamId int)charset=utf8;

### Team practice

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
/ * * *@authorWanghao 😋 *@dateMay 7, 2020 5:17:59 PM */
public class Demo09 {
	public static void main(String[] args) {
		Scanner sc = new Scanner(System.in);
		System.out.println("Please enter the team name.");
		String teamName = sc.nextLine();
		System.out.println("Please enter player's name.");
		String playerName = sc.nextLine();
		// Get the connection
		try (Connection conn = DBUtils.getConn();) {
			String sql = "insert into team values(null,?) ";
			PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			/ / replace?
			ps.setString(1, teamName);
			/ / execution
			ps.executeUpdate();
			// Get the autoincrement primary key
			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				int teamId = rs.getInt(1);
				System.out.println("The team id."+teamId);
				String psql = "insert into player values(null,? ,?) ";
				PreparedStatement pps = conn.prepareStatement(psql);
				pps.setString(1, playerName);
				pps.setInt(2, teamId);
				pps.executeUpdate();
				System.out.println("保存完成!"); }}catch(Exception e) { e.printStackTrace(); }}}Copy the code

### Code introduction

Demo04 Registration function
Demo05 Login function exposed to the pre-compiled Sql execution object explained Sql injection (write value to the user, the user wrote into the Sql statement caused by the original logic changes)
Demo06 Batch operations
Demo07 paging query
Demo08 Gets the primary key value
Demo09 team players practice

Related Posts

Redis application scenarios and FAQs

Logback logs [10 minutes]

Design Patterns – Factory Patterns learning journey