1. Introduction to jdbs

JDBC (Java DataBase Connectivity), that is, Java database connection, is a Java API for executing SQL statements. It can provide unified access to a variety of relational databases. It is composed of a group of classes and interfaces written in Java language. JDBC provides a benchmark by which more advanced tools and interfaces can be built to enable database developers to write database applications.

Java is strong, safe, easy to use, easy to understand and can be automatically downloaded from the network. It is an outstanding language for writing database applications. All you need is a way for Java applications to talk to different databases.

JDBC can use Java on various platforms, such as Windows, Mac OS and various versions of UNIX. The JDBC library includes API s for each of the following tasks that are typically related to database usage:

• Connect to the database.
• Create SQL or MySQL statements.
• Execute SQL or MySQL queries in the database.
• View and modify generated records.

2.JDBC architecture

JDBC API supports two-tier and three-tier processing models for database access, but generally, JDBC architecture consists of two layers:

• JDBC API: This provides an application connection to the JDBC manager.
• Jdbc driver API: this supports JDBC manager to driver connections.

The JDBC API uses the driver manager and database specific drivers to provide transparent connections to heterogeneous databases.

3 JDBC core components

DriverManager: this class manages the list of database drivers. Use the communication sub protocol to match the connection request from the java application with the appropriate database driver.

Driver: this interface handles the communication with the database server. We rarely interact directly with the driver object. Instead, you use the DriverManager object to manage this type of object.

Connection: this interface has all the methods used to contact the database. The connection object represents the communication context, that is, all communication with the database only through the connection object.

Statement: submit SQL statements to the database using objects created from this interface. In addition to executing stored procedures, some derived interfaces also accept parameters.

ResultSet: after executing SQL queries using Statement objects, these objects save the data retrieved from the database. It acts as an iterator and allows us to move its data.

SQLException: this class handles any errors that occur in the database application

4 JDBC usage

Building a JDBC application involves the following six steps:

• Import package: a package containing JDBC classes required for database programming. In most cases, use import Java sql.* That's enough.
• Register JDBC Driver: you are required to initialize the driver so that you can open the communication channel with the database.
• Open Connection: drivermanager is required The getconnection () method creates a Connection object that represents a physical Connection to the database.
• Execute query: you need to use an object of type Statement to build and submit SQL statements to the database.
• Extract data from the result set: you need to use the corresponding resultset The getxxx () method retrieves data from the result set.
• Free resources: you need to explicitly close all database resources without relying on JVM garbage collection.

4.1 JDBC connection

4.1.1 import JDBC package

Use import to import the required classes in Java code

4.1.2 register JDBC Driver

Causes the JVM to load the required driver implementation into memory so that it can satisfy JDBC requests.

1. Class.forName();

The most common way to register drivers is to use Java's class The forname() method dynamically loads the class file of the driver into memory
And automatically register it

try {
	Class.forName("com.mysql.cj.jdbc.Driver");
}catch(ClassNotFoundException ex) {
	System.out.println("Error: unable to load driver class!");
	System.exit(1);
}

2. DriverManager.registerDriver();

The second method is to use static drivermanager Registerdriver() method.

try {
	Driver myDriver = new com.mysql.cj.jdbc.Driver();
	DriverManager.registerDriver( myDriver );
}catch(ClassNotFoundException ex) {
	System.out.println("Error: unable to load driver class!");
	System.exit(1);
}

4.1.3 database URL configuration

Create a properly formatted address that points to the database you want to connect to.

After loading the driver, you can use drivermanager The getconnection () method establishes a connection. Three overloaded drivermanagers are listed below Getconnection() method:

• getConnection(String url)
• getConnection(String url，Properties prop)
• getConnection(String url，String user，String password)

RDBMS	JDBC driver name	URL format
MYSQL8	com.mysql.cj.jdbc.Driver	jdbc:mysql://hostname:3306/databaseName?serverTimezone=UTC
MySQL	com.mysql.jdbc.Driver	jdbc:mysql://hostname:3306/databaseName
ORACLE	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@hostname:port Number: databaseName
DB2	COM.ibm.db2.jdbc.net.DB2Driver	jdbc:db2:hostname:port Number / databaseName
SYBASE	com.sybase.jdbc.SybDriver	jdbc:sybase:Tds:hostname:port Number / databaseName

4.1.4 create database connection object

Call the getConnection() method of the DriverManager object to establish the actual database connection.

String URL = "jdbc:mysql://localhost:3306/db3?serverTimezone=UTC";
String USER = "root";
String PASS = "123456"
Connection conn = DriverManager.getConnection(URL, USER, PASS);

DriverManager. Another form of the getconnection () method requires a database URL and a Properties object

DriverManager.getConnection(String url, Properties info);

import java.util.*;
String URL = "jdbc:mysql://localhost:3306/yhp2?serverTimezone=UTC";
//Add property values for properties
Properties info = new Properties( );
info.put( "user", "root" );
info.put( "password", "123456" );
Connection conn = DriverManager.getConnection(URL, info);

4.1.5 close database connection

To ensure that the connection is closed, you can provide a "finally" block in your code. A finally block is always executed regardless of whether an exception occurs. To close the connection opened above, you should call the close() method as follows:

conn.close();

4.2 executing SQL statements with JDBC

Once the connection is obtained, we can interact with the database. The JDBC Statement and PreparedStatement interfaces define methods and properties that can send SQL commands and receive data from the database.

Interface	Recommended use
Statement	Used for general access to the database. It is useful when using static SQL statements at run time. The Statement interface cannot accept parameters.
PreparedStatement	Use when you plan to use SQL statements multiple times. The PreparedStatement interface accepts input parameters at run time.

4.2.1 statement

Create statement object
Before using the Statement object to execute an SQL Statement, you need to create one using the createStatement() method of the Connection object.

After creating the Statement object, you can use it to execute an SQL Statement, which has three execution methods:

• boolean execute(String SQL): if the ResultSet object can be retrieved, a Boolean value of true will be returned; Otherwise, false is returned. Use this method when executing SQL DDL statements or when you need to use real dynamic SQL.

• int executeUpdate(String SQL): returns the number of rows affected by SQL statement execution. Use this method to execute SQL statements that are expected to affect multiple rows, such as INSERT, UPDATE, or DELETE statements.

• ResultSet executeQuery(String SQL): returns a ResultSet object. Use this method when you want to get a result set, such as a SELECT statement.

Close the Statement object

Just as we close a Connection object to save database resources, for the same reason, we should also close the Statement object. Calling the close() method will execute the job. If you close the Connection object first, it also closes the Statement object. However, you should always explicitly close the Statement object to ensure proper cleanup.

Statement stmt = null;
try {
	stmt = conn.createStatement( );
	ResultSet resultset = stmt. executeQuery("select * from student");
	· · ·
} catch (SQLException e) {
	. . .
} finally {
	stmt.close();
}

4.2.2 Preparedstatement

The PreparedStatement interface extends the Statement interface, which provides an additional function that the general Statement object does not have. This Statement can provide parameters dynamically.

All parameters in JDBC are controlled by? Symbols, which are called parameter markers. You must provide a value for each parameter before executing the SQL statement. Bind the value to the parameter using the setXXX() method, where XXX represents the Java data type to be bound to the value of the input parameter. If you forget to provide a value, you will receive an SQLException. Each parameter tag is referenced by its sequential position. The first mark represents position 1, the next position 2······

Close the PreparedStatement object

Just like closing the Statement object, if you close the Connection object first, the PreparedStatement object will close. However, you should always explicitly close the PreparedStatement object to ensure proper cleanup.

PreparedStatement pstmt = null;
try {
	String SQL = "Update Employees SET age = ? WHERE id = ?";
	pstmt = conn.prepareStatement(SQL);
	
	String uname="admin";
	String pword="123";
	//Assign setXXX (subscript, content) to the placeholder
	pstmt.setString(1, uname);
	pstmt.setString(2, pword);
	
	resultSet = pstmt.executeQuery();
. . .
} catch (SQLException e) {
. . .
} finally {
pstmt.close();
}

4.2.3 compare statement and PreparedStatement;

(1) statement belongs to the state channel and PreparedStatement belongs to the pre state channel
(2) The pre status channel compiles sql statements first and then executes them, which is more efficient than statement execution
(3) Pre status channels support placeholders, When assigning a value to a placeholder, the position starts with 1
(4) The pre status channel can prevent sql injection. The reason is that the pre status channel processes values in the form of strings

4.3 SQL injection

By inserting the SQL command into the web form to submit or enter the query string of the domain name or page request, we can finally deceive the server to execute the malicious SQL command. Specifically, it uses existing applications to inject (malicious) SQL commands into the background database engine for execution. It can get a database on a website with security vulnerabilities by entering (malicious) SQL statements in a web form, rather than executing SQL statements according to the designer's intention. For example, many previous film and television websites leaked VIP member passwords mostly through web forms, and the query characters were exposed. Such forms are particularly vulnerable to SQL injection attacks.

The following code is the login through user name and password authentication:

String username ="admin";
String password=" 'abc'";
String sql="select * from users where username= '"+username+"' and password="+password;
resultset = statement.executeQuery(sql);
if(resultSet.next()){
	System.out.println("Login successful");
}else{
	System.out.println("Login failed");
}

As shown in the following code, the password contains "or 1=1", in which "1 = 1" represents constant, which means that the execution result of the sql statement must not be empty regardless of whether the user name or password exists in the database

String username ="aaa";
String password=" '' or 1=1 ";
String sql="select * from users where username= '"+username+"' and password="+password;
resultset = statement.executeQuery(sql);
if(resultSet.next()){
	System.out.println("Login successful");
}else{
	System.out.println("Login failed");
}

The solution to SQL injection is the PreparedStatement above, because the PreparedStatement processes values in the form of strings, "or 1=1" no longer makes the equation tenable

4.4 ResultSet

The SELECT statement is a standard way to SELECT rows from a database and view rows in a result set. The Java sql. The interface in the ResultSet represents a result set database query. "Result set" refers to the row and column data contained in the ResultSet object.

If no ResultSet type is specified, you will automatically get a TYPE_FORWARD_ONLY.

type	describe
ResultSet.TYPE_SCROLL_INSENSITIVE	The cursor can scroll forward and backward, and the result set is not sensitive to other changes to the database that occur after the result set is created.
ResultSet.TYPE_SCROLL_SENSITIVE	The cursor can scroll forward and backward, and the result set is sensitive to changes made by other databases after the result set is created.
ResultSet.TYPE_FORWARD_ONLY	The cursor can only move forward in the result set.

try {
	Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
}catch(Exception ex) {
	...
}finally {
	...
}

4.5 application examples

//0. Import sql package
import java.sql.*;
//...

public static void main(String[] args) {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultset = null;

    try {
        //1. Load drive
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2. Get links
        String username = "root";
        String password = "123456";
        String url = "jdbc:mysql://localhost:3306/mydb1?serverTimezone=UTC";
        connection = DriverManager.getConnection(url, username, password);
        //3. Define sql and create status channels (send sql statements)
        statement = connection.createStatement();
        resultset = statement.executeQuery("select * from emp1");//executeQuery(sql)
        //4. Retrieve the result set information
        while(resultset.next()){
            //Fetch data resultset GetString ("column name");
            System.out.println("full name"+resultset.getString("ename")+"， Salary:"+resultset.getDouble("sal")+
                    "， Date of employment:"+resultset.getDate("hiredate"));
        }
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally{
        try {
            if (resultset != null) {
                resultset.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

4.6 JDBC step summary

time	function	sentence
First step	Load driver	Class.forName("driver information")
Step 2	Create connection	Connection conn = DriverManager.getConnection(url, username, password)
Step 3	Create (pre) status channel	Statement stmt = conn.createStatement(); / PreparedStatement pstmt = conn.prepareStatement(String SQL)
Step 4	Get result set ()	ResultSet rs = stmt.executeQuery(String SQL); / rs = pstmt.executeQuery();
Step 5	Fetch result set information	Using iterators: while(rs.next()) {...}

4.7 adding, deleting and modifying JDBC

Replace the executeQuery(String SQL) used to obtain the result set above with executeUpdate(String SQL). Note that its return value is the number of affected rows (int type)

String sql = "insert into emp1(ename,hiredate,sal) values('aa','2020-1-1',2000)";
String sql1 = "update emp1 set sal=888;";
String sql2 = "delete from emp1 where ename='aa')";
//Returns the number of rows affected
int result = statement.executeUpdate(sql);

5 Java operation two table relationship

5.1 one to many

1 create data table

Create student and teacher tables:

CREATE TABLE `student` (
`stuid` int(11) NOT NULL AUTO_INCREMENT,
`stuname` varchar(255) DEFAULT NULL,
`teacherid` int(11) DEFAULT NULL,
PRIMARY KEY (`stuid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `student` VALUES ('1', 'aaa', '3');
INSERT INTO `student` VALUES ('2', 'bb', '1');
INSERT INTO `student` VALUES ('3', 'cc', '3');
INSERT INTO `student` VALUES ('4', 'dd', '1');
INSERT INTO `student` VALUES ('5', 'ee', '1');
INSERT INTO `student` VALUES ('6', 'ff', '2');
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `teacher` VALUES ('1', 'Miss Zhang San');
INSERT INTO `teacher` VALUES ('2', 'Miss Li Si');
INSERT INTO `teacher` VALUES ('3', 'Wang Wu');

2 create entity class

In the case of one to many, a list type attribute should be set on one party to store the data of multiple parties

public class Teacher {
    private int tid;
    private String tname;
    //Create a collection on one side that stores multi-party data
    private List<Student> studentList;
    
    public int getTid() {return tid;}
    public void setTid(int tid) {this.tid = tid;}
    public String getTname() {return tname;}
    public void setTname(String tname) {this.tname = tname;}
    public List<Student> getStudentList() {return studentList;}
    public void setStudentList(List<Student> studentList) {this.studentList = studentList;}
}

public class Student {
    private int stuid;
    private String stuName;
    private int teacherId;

    public int getStuid() {return stuid;}
    public void setStuid(int stuid) {this.stuid = stuid;}
    public String getStuName() {return stuName;}
    public void setStuName(String stuName) {this.stuName = stuName;}
    public int getTeacherId() {return teacherId;}
    public void setTeacherId(int teacherId) {this.teacherId = teacherId;}
}

3. Define dao interface

public interface TeacherDao {
    //Define operation method
    //1. Define a method to query the teacher's information (student's information) according to the teacher's id
    public Teacher getById(int tid);
}

4. Define implementation classes

Similar to the above JDBC operations, load driver - > create connection - > create status Channel - > obtain and process data

public class TeacherDaoImpl implements TeacherDao {
    @Override
    public Teacher getById(int tid) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
        	//Load driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            //Create connection
            String username = "root";
            String password = "123456";
            String url = "jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, username, password);
            //Establish status channel
            String sql = "SELECT * from student s,teacher t where s.teacherid=t.tid and t.tid=?";
            pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, tid);
            resultSet = pstmt.executeQuery();
            //Processing data
            Teacher teacher = new Teacher();
            List<Student> students = new ArrayList<>();
            while(resultSet.next()){
                teacher.setTid(resultSet.getInt("tid"));
                teacher.setTname(resultSet.getString("tname"));

                Student student = new Student();
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));
                students.add(student);
            }
            //Establish contact between teachers and students
            teacher.setStudentList(students);
            return teacher;
            
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally{
            try {
                if (resultSet != null) {resultSet.close();}
                if (pstmt != null) {pstmt.close();}
                if (connection != null) {connection.close();}
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
}

5 define test classes

public static void main(String[] args) {
	//Create an object that implements the class interface
    TeacherDao dao = new TeacherDaoImpl();
    Teacher teacher = dao.getById(1);
    System.out.println("Teacher name:"+teacher.getTname());
    List<Student> studentList = teacher.getStudentList();
    for (Student s: studentList) {
        System.out.println("\t"+"Student Name:"+s.getStuName()+"Student No.:"+s.getStuid());
    }
}

5.2 many to one

1 create data table

The creation of the data table is the same as before

2 entity class creation

Note: at this time, multiple parties need to store objects whose attribute value is * * Party's data * *

public class Teacher {
    private int tid;
    private String tname;
    
    public int getTid() {return tid;}
    public void setTid(int tid) {this.tid = tid;}
    public String getTname() {return tname;}
    public void setTname(String tname) {this.tname = tname;}
}

public class Student {
    private int stuid;
    private String stuName;
    private int teacherId;
    private Teacher teacher;

    public Teacher getTeacher() {return teacher;}
    public void setTeacher(Teacher teacher) {this.teacher = teacher;}
    public int getStuid() {return stuid;}
    public void setStuid(int stuid) {this.stuid = stuid;}
    public String getStuName() {return stuName;}
    public void setStuName(String stuName) {this.stuName = stuName;}
    public int getTeacherId() {return teacherId;}
    public void setTeacherId(int teacherId) {this.teacherId = teacherId;}
}

3. Define dao interface

public interface TeacherDao {
    public Teacher getById(int tid);
    public List<Student> getAll();
}

4. Define implementation classes

public List<Student> getAll() {
    //Operation database
    Connection connection = null;
    PreparedStatement pstmt = null;
    ResultSet resultSet = null;
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String username = "root";
        String password = "123456";
        String url = "jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
        connection = DriverManager.getConnection(url, username, password);
        String sql = "SELECT * from student s,teacher t where s.teacherid=t.tid";
        pstmt = connection.prepareStatement(sql);
        resultSet = pstmt.executeQuery();
        List<Student> students = new ArrayList<>();
        while(resultSet.next()){
            Student student = new Student();
            student.setStuid(resultSet.getInt("stuid"));
            student.setStuName(resultSet.getString("stuname"));
            
            Teacher teacher = new Teacher();
            teacher.setTid(resultSet.getInt("tid"));
            teacher.setTname(resultSet.getString("tname"));
            
            student.setTeacher(teacher);
            students.add(student);
        }
        return students;
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally{
        try {
            if (resultSet != null) {resultSet.close();}
            if (pstmt != null) {pstmt.close();}
            if (connection != null) {connection.close();}
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    return null;
}

5 define test classes

TeacherDao dao = new TeacherDaoImpl();
List<Student> students = dao.getAll();
for (Student student : students) {
    System.out.println(student.getStuName()+","+student.getTeacher().getTname());
}

5.3 one to one

Entity class establishment

Create an object with the attribute value * * data of the other party * * on both sides

5.4 many to many

Entity class establishment

The two parties save the list to each other

6 database transactions

6.1 transaction overview

Database transaction refers to a series of operations performed as a single logical unit of work, either completely or not. Transaction processing ensures that data oriented resources are not permanently updated unless all operations within the transactional unit are successfully completed. By combining a set of related operations into a unit that either succeeds or fails, you can simplify error recovery and make your application more reliable. To become a transaction, a logical unit of work must meet the so-called ACID (atomicity, consistency, isolation and persistence) attributes. Transaction is the logical work unit in database operation, and the transaction management subsystem in DBMS is responsible for transaction processing.

Transaction started at:

• Connect to the database and execute a DML statement insert, update, or delete
• After the previous transaction, another DML statement is entered

Transaction ended on:

• Execute a commit or rollback statement.
• Execute a DDL statement, such as the create table statement. In this case, the commit statement will be executed automatically.
• Execute a DCL statement, such as grant statement, in which case the commit is automatically executed.
• Disconnect from database
• A DML statement is executed, but the statement fails. In this case, the rollback language will be executed for the invalid DML statement
  Sentence.

6.2 four characteristics of transactions (ACID)

• Atomicity
  Indicates that all operations within a transaction are a whole, either all successful or all failed
• Consistency
  Indicates that when an operation fails in a transaction, all changed data must be rolled back to the state before modification
• Isolation
  When a transaction views data, the state of the data is either the state before it is modified by another concurrent transaction or the state before it is modified by another transaction
  After changing its state, the transaction will not view the data in the intermediate state.
• Durability
  After a persistent transaction is completed, its impact on the system is permanent.

6.3 transaction application in JDBC

If the JDBC Connection is in auto submit mode, by default, each SQL statement will be submitted to the database after completion. To enable manual transaction support instead of the automatic commit mode used by the jdbc driver by default, use the setAutoCommit() method of the Connection object. If you pass boolean false to setAutoCommit(), auto commit is turned off. We can reopen it by passing a Boolean value of true.

6.3.1 transaction submission and rollback

After completing the changes, we should commit the changes and then call the commit () method on the connection object. Otherwise, we should use the database to rollback the update:

try {
       Class.forName("com.mysql.cj.jdbc.Driver");
       ...
       connection = DriverManager.getConnection(url, username, password);
       //Note: Auto submit is turned off here
       connection.setAutoCommit(false);
       statement = connection.createStatement();
       int result = statement.executeUpdate("SQL sentence");
       ...
       //The transaction is committed manually here
       connection.commit();
       ...
   } catch (Exception e) {
       e.printStackTrace();
       //If an exception occurs, roll back to the initial state
       try {
           connection.rollback();
       } catch (SQLException throwables) {
           throwables.printStackTrace();
       }
   }
   ...

6.3.2 Savepoints

When setting a savepoint, you can define a logical rollback point in a transaction. If an error occurs through a savepoint, you can undo it using the rollback method
All changes or only changes made after the savepoint are saved.
The Connection object has two new ways to help you manage savepoints

• setSavepoint (String savepointName): defines a new Savepoint. It also returns a Savepoint object.
• Release Savepoint (Savepoint savepointName): deletes a Savepoint. Note that it requires a Savepoint object as a parameter. This object is usually a Savepoint generated by the setSavepoint () method.

try{
	...
	int result = statement.executeUpdate("SQL sentence");
	//Add savepoint abc
	Savepoint abc = connection.setSavepoint("abc");
	int result2 = statement.executeUpdate("SQL Statement 1");
	System.out.println(5/0);
	connection.commit();
	...
} catch (Exception e) {
     e.printStackTrace();
     try {
     	//Rollback to the location of the savepoint abc
         connection.rollback(abc);
         //Actions before resubmitting the savepoint
         connection.commit();
     } catch (SQLException throwables) {
         throwables.printStackTrace();
     }
}
...

6.3.3 case: transfer

The program may contain some logic. If the function fails, the whole transaction needs to be rolled back.

try{
	...
	connection.setAutoCommit(false);
	statement = connection.createStatement();
	int result = statement.executeUpdate("update money set yue=yue-100 where userid=1");
	int result2 = statement.executeUpdate("update money set yue=yue+100 where userid=2");
	connection.commit();
	...
} catch (Exception e) {
     e.printStackTrace();
     try {
         connection.rollback();
     } catch (SQLException throwables) {
         throwables.printStackTrace();
     }
}
...

7 JDBC batch processing

Batch processing allows you to group related SQL statements into batches and submit them with a single call to the database.
When you send multiple SQL statements to the database at a time, you can reduce the overhead of connecting to the database and improve performance.

7.1 Statement batch processing

The following is a typical sequence of steps for batch processing using statement objects

• Use the createStatement () method to create a Statement object.
• Use setAutoCommit() to set auto commit to false.
• Use the addBatch () method to add your favorite SQL statements to the batch on the created statement object.
• Use the executeBatch() method to execute all SQL statements on the created statement object.
• Finally, commit all changes using the commit () method.

try {
	...
    connection.setAutoCommit(false);
    statement = connection.createStatement();
    //Define sql
    String sql1 = "insert into teacher(tname) values('aa1')";
    statement.addBatch(sql1);
    String sql2 = "insert into teacher(tname) values('aa2')";
    statement.addBatch(sql2);
    String sql3 = "insert into teacher(tname) values('aa3')";
    statement.addBatch(sql3);
    String sql4 = "insert into teacher(tname) values('aa4')";
    statement.addBatch(sql4);
    int[] ints = statement.executeBatch();
    connection.commit();
    for (int anInt : ints) {
        System.out.println(anInt);
    }
} catch (Exception e) {
    e.printStackTrace();
    try {
        connection.rollback();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}

7.2 PreparedStatement batch processing

1. Create SQL statements using placeholders.
2. Use the preparestatement () method to create a preparestatement object.
3. Use setAutoCommit() to set auto commit to false.
4. Use the addBatch () method to add your favorite SQL statements to the batch on the created statement object.
5. Use the executeBatch() method to execute all SQL statements on the created statement object.
6. Finally, commit all changes using the commit () method.

try {
    ...
    connection.setAutoCommit(false);
    pps = connection.prepareStatement("insert into teacher(tname) values(?)");
    //Define sql
    pps.setString(1,"LISI");
    pps.addBatch();
    pps.setString(1,"LISI1");
    pps.addBatch();
    pps.setString(1,"LISI2");
    pps.addBatch();
    pps.setString(1,"LISI3");
    pps.addBatch();
    int[] ints = pps.executeBatch();
    connection.commit();
    for (int anInt : ints) {
        System.out.println(anInt);
    }
} catch (Exception e) {
    e.printStackTrace();
    try {
        connection.rollback();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}

8 reflection processing result set

It is very troublesome to obtain the attribute value of the object through the set method every time, so you can use the reflection method to directly reflect the column name of the database into the corresponding attribute. Therefore, when defining the attribute name of the class, it should be consistent with the corresponding column name in the data table (case can be ignored).

...
String sql = "SELECT * from student";
pstmt = connection.prepareStatement(sql);
resultSet = pstmt.executeQuery();
List students = new ArrayList();
ResultSetMetaData metaData = resultSet.getMetaData();//Save result set information
int columnCount = metaData.getColumnCount();//Get the number of columns
String[] columnNames = new String[columnCount];
for (int i=0; i < columnCount; i++){
    columnNames[i] = metaData.getColumnLabel(i+1);
    System.out.println("column"+columnNames[i]);
}
//Get all the methods in the class
Method[] declaredMethods = cla.getDeclaredMethods();
while(resultSet.next()){
    try {
        Object stu = cla.newInstance();
        for (String columnName : columnNames) {
            String methodName = "set"+columnName;
            for (Method declaredMethod : declaredMethods) {
            	//When the property name of the required set is the same as that of the database result set, activate the corresponding setter method to add the property value to the object used
                if(declaredMethod.getName().equalsIgnoreCase(methodName)){
                    declaredMethod.invoke(stu,resultSet.getObject(columnName));
                    break;
                }
            }
        }
        students.add(stu);
    } catch (InstantiationException e) {
        e.printStackTrace();
    } catch (IllegalAccessException e) {
        e.printStackTrace();
    } catch (InvocationTargetException e) {
        e.printStackTrace();
    }
}
return students;

TeacherDao dao = new TeacherDaoImpl();
List<Student> allStudent = dao.getAllStudent(Student.class);
for (Student student : allStudent) {
         	System.out.println(student.getStuid()+","+
         	student.getStuName()+","+student.getTeacherId();
}

9 packaging tools

It's useful to simplify repetitive code.
For the definition of a tool class, protected is used to allow only its subclasses to use the methods in it:

public class DBUtils {
    private Connection connection;
    private PreparedStatement pps;
    private ResultSet resultSet;
    private int count;

    private String username = "root";
    private String password = "123456";
    private String url = "jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";

    //Load driver
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //Create connection
    protected Connection getConnection(){
        try {
            connection = DriverManager.getConnection(url,username,password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }
    //Create status channel
    protected PreparedStatement getPps(String sql){
        try {
            pps = getConnection().prepareStatement(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return pps;
    }
    //list saves the value overwritten by the placeholder and assigns a value to the pre state channel (pass parameter)
    protected void param(List list){
        if(list != null && list.size()>0){
            for (int i=0; i<list.size(); i++) {
                try {
                    pps.setObject(i+1,list.get(i));
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
    //DML
    protected int update(String sql, List list){
        getPps(sql);
        param(list);
        try {
            count = pps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count;
    }
    //DQL
    protected ResultSet query(String sql, List list){
        getPps(sql);
        param(list);
        ResultSet resultSet = null;
        try {
            resultSet = pps.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return resultSet;
    }
	//Close connection
    protected void closeAll(){
        try {
            if (connection != null) {
                connection.close();
            }
            if (pps != null) {
                pps.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

public class TeacherDaoImpl extends DBUtils implements TeacherDao {
    @Override
    public Student getByStuId(int id) {
        Student student = new Student();
        try {
            String sql = "select * from student where stuid=?";
            List list = new ArrayList();
            list.add(id);
            ResultSet rs = query(sql,list);

            while(rs.next()){
                student.setStuid(rs.getInt("stuid"));
                student.setStuName(rs.getString("stuname"));
            }
            return student;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }
}

9.1 the properties file saves database information

• Feature: key value storage mode

Store the required values in dB Properties file

driver=com.mysql.cj.jdbc.Driver
username=root
password=123456
url=jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC

Static code block used to load driver:

private static String username;
private static String password;
private static String url;
private static String dirverName;
    
static {
    try {
        InputStream inputStream = DBUtils.class.getClassLoader()
                .getResourceAsStream("db.properties");
        Properties properties = new Properties();
        properties.load(inputStream);
        dirverName = properties.getProperty("driver");
        url = properties.getProperty("url");
        username = properties.getProperty("username");
        password = properties.getProperty("password");
        Class.forName(dirverName);
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

static{
	//The parameter only needs to write the attribute file name, and there is no need to write the suffix
	ResourceBundle bundle = ResourceBundle.getBundle("db");
	driver = bundle.getString("driver");
	url = bundle.getString("url");
	username = bundle.getString("user");
	password = bundle.getString("password");
}

10 connection pool

1. User defined connection pool

Principle of data connection pool

The basic idea of connection pool is to store the database connection as an object in memory during system initialization. When users need to access the database, they do not establish a new connection, but take out an established idle connection object from the connection pool. After use, the user does not close the connection, but puts the connection back into the connection pool for access by the next request. The establishment and disconnection of connections are managed by the connection pool itself. At the same time, you can also set the parameters of the connection pool to control the initial number of connections in the connection pool, the upper and lower limits of connections, as well as the maximum usage times and maximum idle time of each connection. You can also monitor the performance of database connections through its own management mechanism
Quantity, usage, etc.

1.1 custom connection pool

We can implement connection pooling in a customized way! The analysis connection pool class should contain specific properties and methods!
Properties: Collection drop Connection
Method: get connection method
Recycling connection method
Specific implementation code: