Java from introduction to actual combat Summary - 4.4, JDBC

Keywords: Java Database MySQL

Java from introduction to actual combat Summary - 4.4, JDBC

1. Introduction

JDBC (Java database connectivity) 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 tasks mentioned below 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.

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-GHAK7zvm-1635174152248)(... / images/0aba995af8555ab35f1e1e2703057068f9163ec6b44fb2e1fd60e80f6c3cd21f.png)]

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. Introduction to crud syntax (review)

SQL is a standardized language that allows you to perform operations on the database, such as creating projects, querying content, updating content, and deleting entries.

Create, Read, Update, and Delete are commonly referred to as CRUD operations.

The CREATE DATABASE statement is used to create a new database:

SQL> CREATE DATABASE DATABASE_NAME;

The DROP DATABASE statement is used to delete an existing database:

SQL> DROP DATABASE DATABASE_NAME;

The CREATE TABLE statement is used to create a new table. The syntax is:

SQL> CREATE TABLE Employees ( id INT NOT NULL, age INT NOT NULL, first VARCHAR(255), last VARCHAR(255), PRIMARY KEY ( id ) );

DROP TABLE StatementTo drop an existing table:

SQL> DROP TABLE table_name;

The syntax of INSERT is similar to the following, where column1, column2, etc. represent the new data to be displayed in the corresponding column

SQL> INSERT INTO table_name VALUES (column1, column2, ...);

The SELECT statement is used to retrieve data from a database. The syntax of SELECT is:

SQL> SELECT column_name, column_name, ... FROM table_name WHERE conditions;

WHERE clause can use comparison operators, such as =,! =, <, ><= And > =, and BETWEEN and LIKE operators.

The UPDATE statement is used to UPDATE data:

SQL> UPDATE table_name SET column_name = value, column_name = value, ... WHERE conditions;

WHERE clause can use comparison operators, such as =,! =, <, ><= And > =, and BETWEEN and LIKE operators.

DELETE statement is used to DELETE data from a table:

SQL> DELETE FROM table_name WHERE conditions;

WHERE clause can use comparison operators, such as =,! =, <, ><= And > =, and BETWEEN and LIKE operators.

5. Use steps

Building a JDBC application involves the following six steps:

  • 1. Import package: a package containing JDBC classes required for database programming. In most cases, using import java.sql. * is sufficient
    Yes.
  • 2. Register JDBC Driver: you are required to initialize the driver so that you can open the communication channel with the database.
  • 3. Open Connection: you need to use DriverManager.getConnection() method to create a Connection object, which is a table
    Shows the physical connection to the database.
  • 4. Execute query: you need to use an object of type Statement to build and submit SQL statements to the database.
  • 5. Extract data from the result set: you need to use the corresponding ResultSet.getXXX() method to retrieve data from the result set.
  • 6. Release resources: you need to explicitly close all database resources without relying on JVM garbage collection.

6. JDBC connection steps

The programming involved in establishing a JDBC connection is quite simple. These are four simple steps

  • Import JDBC package: add the import statement of the Java language to the Java code to import the required classes. (download here: https://search.maven.org/artifact/mysql/mysql-connector-java/8.0.26/jar)
  • Register JDBC Driver: this step will cause the JVM to load the required driver implementation into memory so that it can satisfy your JDBC request.
  • Database URL configuration: This is to create a properly formatted address pointing to the database to connect to.
  • Create a connection object: finally, call the DriverManager object getConnection () method to establish the actual database connection.

Class.forName();

The most common way to register a driver is to use Java's Class.forName() method to dynamically load the driver's class file into memory and register it automatically

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

DriverManager.registerDriver();

The second method is to use the 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);
}

Database URL configuration:

After loading the driver, you can use the DriverManager.getConnection() method to establish a connection. For reference, let me list three overloaded DriverManager.getConnection() methods-

  • getConnection(String url)
  • getConnection(String url，Properties prop)
  • getConnection(String url，String user，String password)
RDBMSJDBC driver nameURL format
MYSQL8com.mysql.cj.jdbc.Driverjdbc:mysql://hostname:3306/databaseName?serverTimezone=UTC
MySQLcom.mysql.jdbc.Driverjdbc:mysql://hostname:3306/databaseName
ORACLEoracle.jdbc.driver.OracleDriverjdbc:oracle:thin:@hostname:port Number: databaseName
DB2COM.ibm.db2.jdbc.net.DB2Driverjdbc:db2:hostname:port Number / databaseName
SYBASEcom.sybase.jdbc.SybDriverjdbc:sybase:Tds:hostname:port Number / databaseName

To create a database connection object:

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

Full connection address:
Version 1:

jdbc:mysql://localhost:3306 / database name? Usessl = false & useunicode = true & characterencoding = UTF-8

Version 2:

jdbc:mysql://localhost:3306/yhp2?serverTimezone=UTC

Use database URL s and property objects:

The third form of the DriverManager.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";
Properties info = new Properties( );
info.put( "user", "username" );
info.put( "password", "password" );
Connection conn = DriverManager.getConnection(URL, info);

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 or not.
To close the connection opened above, you should call the close () method as follows-

conn.close();

import com.mysql.cj.jdbc.Driver;

import java.sql.*;

public class Demo1 {
    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 = "";
            String url = "jdbc:mysql://localhost:3306/test_db?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");  //Execute query
            //4. Retrieve the result set information
            while(resultSet.next()) {
                //Fetch data: resultSet.getXXX("column name"); xxx indicates the data type
                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 {
                //5. Close resources
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

Select Ctrl + Alt + T (option + Command + T under MAC) to select try/catch, if, etc.

6.1 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 enable you to send SQL commands and receive data from the database.

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

6.2 Statement

Create statement object
Before using the Statement object to execute an SQL Statement, you need to use the createStatement() method of the Connection object to create one, as shown in the following example:

Statement stmt = null;
try {
    stmt = conn.createStatement( );
    . . . 
}catch (SQLException e) {
    . . .
}finally {
    . . .
}

After creating the Statement object, you can use it to execute an SQL Statement with one of 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 to execute 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 the result set, just like using the 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.

A simple call to 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( );
    . . .
}catch (SQLException e) {
    . . .
}finally {
    stmt.close();
}

6.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
It can get the 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.

String username ="admin";
String password=" 'abc' or 1=1 ";
String sql="select * from users where username= '"+username+"' and password= "+password;

7. Preparedstatement (pre state channel)

The PreparedStatement interface extends the Statement interface, which provides you with a general Statement object with two advantages and additional functions.

This statement enables you to provide parameters dynamically.

PreparedStatement pstmt = null;
try {
    String SQL = "Update Employees SET age = ? WHERE id = ?";
    pstmt = conn.prepareStatement(SQL);
    . . . 
}
catch (SQLException e) 
{ 
    . . .
}
finally {
    . . .
}

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.

The setXXX() method binds the value to the parameter, 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, and so on. This method is different from Java array index, starting from 0.

Close the PreparedStatement object

Just like closing the Statement object, you should also close the PreparedStatement object for the same reason.

A simple call to the close () method will execute the job. If you close the Connection object first, it also closes the PreparedStatement object. 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.preparedStatement(SQL);
    . . .
}catch (SQLException e) {
    . . .
}finally {
    pstmt.close();
}

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

8. ResultSet

The SELECT statement is a standard way to SELECT rows from a database and view rows in a result set. The interface in the java.sql.ResultSet represents the result set database query.

The ResultSet object maintains a cursor pointing to the current row in the result set. The term "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.

typedescribe
ResultSet.TYPE_SCROLL_INSENSITIVEThe 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_SENSITIVEThe 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_ONLYThe 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 {
    ....
}

9. Relationship between two tables of Java operation

Four: two way, one to one, one to many, many to one, many to many

Multi table relational data processing
(1) The database establishes the relationship between two tables through foreign keys
(2) Entity classes establish two table relationships through attributes

Entity class requirements: class name = table name, column name = attribute name

9.1 one to many (teacher - > student)

(1) Create data table

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

public class Teacher {
    private int tid;
    private String tname;
    private List<Student> list=new ArrayList<Student>();
    
    public List<Student> getList() {
        return list;
    }
    public void setList(List<Student> list) {
        this.list = list;
    }
    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;
    //Foreign key columns generally do not generate attributes
    //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;
    }
}

(3) Define dao interface

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

(4) Define implementation classes

public class TeacherDaoImpl implements TeacherDao { @Override public Teacher getById(int tid) {
    //Operation database
    Connection connection =null;
    PreparedStatement pps =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/yhp3?serverTimezone=UTC";
        connection = DriverManager.getConnection(url, userName, passWord);
        //3. Define sql and create pre status channels (send sql statements)
        String sql="select * from student s,teacher t where s.teacherid=t.tid and t.tid=?";
        pps = connection.prepareStatement(sql);
        //Assign a value (subscript, content) to the placeholder, starting with 1
        pps.setInt(1,tid);
        //Execute sql
        resultSet = pps.executeQuery();
        Teacher teacher = new Teacher(); List<Student> students=new ArrayList<Student>();
        while (resultSet.next()){
            //1. Take out their respective information
            teacher.setTid(resultSet.getInt("tid"));
            teacher.setTname(resultSet.getString("tname"));
            Student student = new Student(); 
            student.setStuId(resultSet.getInt("stuid"));
            student.setStuName(resultSet.getString("stuname"));
            //2. Establish the relationship between students and teachers
            students.add(student);
        }
        teacher.setStudentList(students);
        return teacher;
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        try {
            //5. Close resources
            if (resultSet != null) {
                resultSet.close();
            }
            if (pps != null) {
                pps.close();
            }if (connection != null) {
                connection.close();
            }
        } catch (SQLException throwables{
            throwables.printStackTrace();
        }
    }
    
    return null;
    }
}

(5) Define test class

public class Demo1 {
    public static void main(String[] args) {      
        TeacherDao dao= new TeacherDaoImpl(); Teacher teacher = dao.getById(1);
        System.out.println("Teacher name:"+teacher.getTname());
        List<Student> studentList = teacher.getStudentList();
        for (Student student : studentList) {   
            System.out.println("\t studentname="+student.getStuName());
        }
    }
}

9.2 many to one (student - > teacher)

The data sheet is the same as above
Entity class:

public class Student {
    private int stuid;
    private String stuname;
    //Foreign key columns generally do not generate attributes
    // private int teacherid;
    private 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 Teacher getTeacher() {
        return teacher;
    }
    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }
}

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

Interface:

//Query all students (including teacher information)
public List<Student> getAll();

Implementation class:

public List<Student> getAll() {
    //Operation database
    Connection connection =null;
    PreparedStatement pps =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/yhp3?serverTimezone=UTC";
        connection = DriverManager.getConnection(url, userName, passWord);
        //3. Define sql and create pre status channels (send sql statements) 
        String sql="select * from student s,teacher t where s.teacherid=t.tid";
        pps = connection.prepareStatement(sql);
        //Execute sql
        resultSet = pps.executeQuery();List<Student> students=new ArrayList<>();
        while (resultSet.next()){
            //1. Take out their respective information
            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"));
            //2. Establish the relationship between students and teachers
            student.setTeacher(teacher);
            students.add(student);
        }
        return students;
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        try {
            //5. Close resources
            if (resultSet != null) {
                resultSet.close();
            }if (pps != null) {
                pps.close();
            }if (connection != null) {
                connection.close();
            }
        } catch (SQLException throwables) {     
            throwables.printStackTrace();
        }
    }
    return null;
}

Test class:

public static void main(String[] args) {
    TeacherDao dao= new TeacherDaoImpl();
    List<Student> students = dao.getAll();
    for (Student student : students) {
        System.out.println(student.getStuName()+","+student.getTeacher().getTname());
    }
}

9.3 one to one (wife and husband)

data sheet:

CREATE TABLE `husband` (
    `husid` int(11) NOT NULL AUTO_INCREMENT,
    `husname` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`husid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO `husband` VALUES ('1', 'Deng Chao');

DROP TABLE IF EXISTS `wife`; CREATE TABLE `wife` (  
    `wifeid` int(11) NOT NULL AUTO_INCREMENT,`wifename` varchar(255) DEFAULT NULL,
    `hid` int(11) DEFAULT NULL, PRIMARY KEY (`wifeid`),
    UNIQUE KEY `uq_wife_hid` (`hid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO `wife` VALUES ('1', 'Sun Li', '1');

Entity class:

public class Husband {
    private int husid;
    private String husname;
    private Wife wife;
    public int getHusid() {
        return husid;
    }
    public void setHusid(int husid) {
        this.husid = husid;
    }
    public String getHusname() {
        return husname;
    }
    public void setHusname(String husname) {
        this.husname = husname;
    }
    public Wife getWife() {
        return wife;
    }
    public void setWife(Wife wife) {
        this.wife = wife;
    }
}

public class Wife {
    private int wifeid;
    private String wifeName;
    private Husband husband;
    public int getWifeid() {
        return wifeid;
    }
    public void setWifeid(int wifeid) {
        this.wifeid = wifeid;
    }
    public String getWifeName() {
        return wifeName;
    }
    public void setWifeName(String wifeName) { 
        this.wifeName = wifeName;
    }
    public Husband getHusband() {
        return husband;
    }
    public void setHusband(Husband husband) {
        this.husband = husband;
    }
}

Interface:

public interface WifeDao {
    //Query wife information (including husband information)
    public Wife getWife(int wid);
    //Query husband information (including wife information)
    public Husband getHus(int hid);
}

Implementation class:

public class WifeDaoImpl implements WifeDao { @Override public Wife getWife(int wid) {
    //Operation database
    Connection connection =null;
    PreparedStatement pps =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/yhp3?serverTimezone=UTC";
        connection = DriverManager.getConnection(url, userName, passWord);
        //3. Define sql and create pre status channels (send sql statements) 
        String sql="select * from wife w,husband h where w.hid=h.husid and w.wifeid=?";
        pps = connection.prepareStatement(sql);
        pps.setInt(1,wid);
        //Execute sql
        resultSet = pps.executeQuery();
        Wife wife = new Wife();
        while (resultSet.next()){
            //1. Take out their respective information
            wife.setWifeId(resultSet.getInt("wifeid"));
            wife.setWifeName(resultSet.getString("wifename"));
            Husband husband = new Husband(); husband.setHusId(resultSet.getInt("husid"));
            husband.setHusName(resultSet.getString("husname"));
            //2. Establish the relationship between wife and husband
            wife.setHusband(husband);
        }
        return wife;
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        try {
            //5. Close resources
            if (resultSet != null) {
                resultSet.close();
            }
            if (pps != null) {
                pps.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException throwables) { 
            throwables.printStackTrace();
        }
    }
        return null;
    }
    @Override
    public Husband getHus(int hid) {
        //Operation database
        Connection connection =null;
        PreparedStatement pps =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/yhp3?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3. Define sql and create pre status channels (send sql statements)
            String sql="select * from wife w,husband h where w.hid=h.husid and h.husid=?";
            pps = connection.prepareStatement(sql);
            pps.setInt(1,hid);
            //Execute sql
            resultSet = pps.executeQuery();
            Husband husband = new Husband();
            while (resultSet.next()){
                //1. Take out their respective information
                Wife wife = new Wife();
                wife.setWifeId(resultSet.getInt("wifeid"));
                wife.setWifeName(resultSet.getString("wifename"));
                husband.setHusId(resultSet.getInt("husid"));
                husband.setHusName(resultSet.getString("husname"));
                //2. Establish the relationship between wife and husband
                husband.setWife(wife);
            }
            return husband;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) { 
            throwables.printStackTrace();
        } finally {
            try {
                //5. Close resources
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) { 
                    connection.close();
                }
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            }
        }
        return null;
    }
}

Test class:

public static void main(String[] args) {           
    WifeDaoImpl wifeDao = new WifeDaoImpl();
    Wife wife = wifeDao.getWife(1);
    System.out.println(wife.getWifeName()+","+wife.getHusband().getHusName());
    Husband hus = wifeDao.getHus(1);
    System.out.println(hus.getHusName()+","+hus.getWife().getWifeName()); 
}

9.4 many to many (subject - student)

data sheet:

CREATE TABLE `middle` ( `middleid` int(11) NOT NULL AUTO_INCREMENT, `stuid` int(11) DEFAULT NULL, `subid` int(11) DEFAULT NULL, PRIMARY KEY (`middleid`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- -------------------------
-- Records of middle --
----------------------------

INSERT INTO `middle` VALUES ('1', '1', '1'); INSERT INTO `middle` VALUES ('2', '1', '2'); INSERT INTO `middle` VALUES ('3', '1', '3'); INSERT INTO `middle` VALUES ('4', '1', '5'); INSERT INTO `middle` VALUES ('5', '2', '2'); INSERT INTO `middle` VALUES ('6', '3', '2'); INSERT INTO `middle` VALUES ('7', '4', '2'); INSERT INTO `middle` VALUES ('8', '5', '2'); INSERT INTO `middle` VALUES ('9', '6', '2');

-- ----------------------------
-- Table structure for `student`
-- ----------------------------

DROP TABLE IF EXISTS `student`; 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;

-- ----------------------------
--Records of student -- ----------------------------

INSERT INTO `student` VALUES ('1', 'Zhang San', '3'); INSERT INTO `student` VALUES ('2', 'Li Si', '1'); INSERT INTO `student` VALUES ('3', 'Wang Wu', '3'); INSERT INTO `student` VALUES ('4', 'Zhao Liu', '1'); INSERT INTO `student` VALUES ('5', 'tearful', '1');
INSERT INTO `student` VALUES ('6', 'Xiaoxiao', '2');

-- ----------------------------
-- Table structure for `subject`
-- ----------------------------

DROP TABLE IF EXISTS `subject`; CREATE TABLE `subject` ( `subid` int(11) NOT NULL AUTO_INCREMENT, `subname` varchar(255) DEFAULT NULL, PRIMARY KEY (`subid`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of subject --
---------------------------
INSERT INTO `subject` VALUES ('1', 'java'); INSERT INTO `subject` VALUES ('2', 'ui'); INSERT INTO `subject` VALUES ('3', 'h5'); INSERT INTO `subject` VALUES ('4', 'c'); INSERT INTO `subject` VALUES ('5', 'c++'); INSERT INTO `subject` VALUES ('6', 'c#');

Entity class:

public class Subject {
    private int subid;
    private String subname;
    private List stulist;
    public int getSubid() {
        return subid;
    }
    public void setSubid(int subid) {
        this.subid = subid;
    }
    public String getSubname() {
        return subname;
    }
    public void setSubname(String subname) {
        this.subname = subname;
    }
    public List getStulist() {
        return stulist;
    }
    public void setStulist(List stulist) {
        this.stulist = stulist;
    }
}

public class Student {
    private int stuid;
    private String stuname;
    //Foreign key columns generally do not generate attributes
    // private int teacherid;
    private Teacher teacher;
    private List<Subject> subjects;
    public List<Subject> getSubjects() {
        return subjects;
    }
    public void setSubjects(List<Subject> subjects) {
        this.subjects = subjects;
    }
    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 Teacher getTeacher() {
        return teacher;
    }
    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }
}

Interface:

public interface SubjectDao {
    //Query a student's information (find the subject studied)
    public Student findById(int id);
    //Query a subject and the corresponding student name
    public Subject findBySubId(int subId);
}

Entity class:

public class SubjectDaoImpl implements SubjectDao {
    @Override public Student findById(int id) {
        //Operation database
        Connection connection =null;PreparedStatement pps =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/yhp3?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3. Define sql and create pre status channels (send sql statements)
            String sql="select * from student s,subject su,middle m where s.stuid=m.stuid and su.subid=m.subid and s.stuid=?";
            pps = connection.prepareStatement(sql);
            pps.setInt(1,id);
            //Execute sql
            resultSet = pps.executeQuery();
            Student student = new Student();List<Subject> subjects=new ArrayList<>();
            while (resultSet.next()){
                //1. Take out their respective information
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));
                Subject subject = new Subject(); subject.setSubId(resultSet.getInt("subid"));
                subject.setSubName(resultSet.getString("subname"));
                subjects.add(subject);
            }
            //2. Establish the relationship between students and subjects
            student.setSubjects(subjects);
            return student;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables){
            throwables.printStackTrace();
        } finally {
            try {
                //5. Close resources
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) { 
                    connection.close();
                }
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            }
        }
        return null;
        }
        @Override public Subject findBySubId(int subId) {
            //Operation database
            Connection connection =null;
            PreparedStatement pps =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/yhp3?serverTimezone=UTC";
                connection = DriverManager.getConnection(url, userName, passWord);
                //3. Define sql and create pre status channels (send sql statements)
                String sql="select * from student s,subject su,middle m where s.stuid=m.stuid and su.subid=m.subid and su.subid=?";
                pps = connection.prepareStatement(sql);
                pps.setInt(1,subId);
                //Execute sql
                resultSet = pps.executeQuery();
                Subject subject = new Subject(); List<Student> studentList=new ArrayList<>();
                while (resultSet.next()){
                    //1. Take out their respective information
                    Student student = new Student();
                    student.setStuId(resultSet.getInt("stuid"));
                    student.setStuName(resultSet.getString("stuname")); 
                    studentList.add(student);subject.setSubId(resultSet.getInt("subid"));
                    subject.setSubName(resultSet.getString("subname"));
                }
                //2. Establish the relationship between students and subjects
                subject.setStudentList(studentList);
                return subject;
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            } finally {
                try {
                    //5. Close resources
                    if (resultSet != null) { 
                        resultSet.close();
                    }
                    if (pps != null) {
                        pps.close();
                    }
                    if (connection != null) { 
                        connection.close();
                    } 
                } catch (SQLException throwables) { 
                    throwables.printStackTrace(); 
                    }
                }
                return null;
            }
}

Test class:

public static void main(String[] args) { 
    SubjectDaoImpl subjectDao = new SubjectDaoImpl();
    /* Student student = subjectDao.findById(1); 
    System.out.println(student.getStuName()); List<Subject> subjects = student.getSubjects();
    for (Subject subject : subjects) {
        System.out.println("\t"+subject.getSubName()); 
    }*/
    Subject subject = subjectDao.findBySubId(2); 
    System.out.println(subject.getSubName()); List<Student> studentList = subject.getStudentList();
    for (Student student : studentList) {
        System.out.println("\t"+student.getStuName());
    }
}

10. Database transactions

->A set of SQL statements that either execute successfully or fail at the same time. It is an execution unit of database operation.

10.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 on

  • 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 DDL statement, such as grant statement, in which case the commit will be executed automatically.
  • 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.

10.2 four characteristics of affairs

(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 another concurrent transaction modifies it or the state after another transaction modifies it. 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.

10.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.

Transactions enable you to control whether and when changes are applied to the database. It treats a single SQL statement or a group of SQL statements as a logical unit. If any statement fails, the whole transaction will fail.

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. I
You can reopen it by passing a Boolean value of true.

10.4 transaction commit and rollback

After completing the changes, we will commit the changes and then call the commit () method on the connection object, as shown below:

conn.commit();

Otherwise, to roll back the update using a database named conn, use the following code

conn.rollback();

try{
    //Assume a validconnection object conn conn.setAutoCommit(false);
    Statement stmt = conn.createStatement(); String SQL = "INSERT INTO Employees values (106, 20, 'Rita', 'Tez')";
    stmt.executeUpdate(SQL);
    //Submit a malformed SQL statement that breaks String SQL = "INSERTED IN Employees VALUES (107, 22, 'Sita', 'Singh')";
    stmt.executeUpdate(SQL);
    // If there is no error.
    conn.commit();
    } catch(SQLException se) {
        // If there is any error. 
        conn.rollback();
    }

10.5 Savepoints

The new JDBC 3.0 Savepoint interface provides you with additional transaction control.

When setting a savepoint, you can define a logical rollback point in a transaction. If an error occurs through a savepoint, you can use the rollback method to undo all changes or save only changes made after the savepoint.

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{
    //Assume a valid connection object conn
    conn.setAutoCommit(false);
    Statement stmt = conn.createStatement(); 
    
    Savepoint savepoint1 = conn.setSavepoint("Savepoint1");
    String SQL = "INSERT INTO Employees VALUES (106, 20, 'Rita', 'Tez')";
    stmt.executeUpdate(SQL);
    String SQL = "INSERTED IN Employees VALUES (107, 22, 'Sita', 'Tez')";
    stmt.executeUpdate(SQL); conn.commit();
}catch(SQLException se){
    conn.rollback(savepoint1);
}

1. To cancel JDBC auto commit: void setAutoCommit(boolean autoCommit)
2. Execute various SQL statements and add them to batch processing
3. If all statements are executed successfully, the transaction commit(); Rollback if an error occurs: rollback()

try {
    connection.setAutoCommit(false);
    add(connection);
    // int i = 1/0;
    sub(connection);
    System.out.println("==============="); connection.commit();
    } catch (Exception e) {
        // TODO Auto-generated catch block
        System.out.println("---------------");
        try {connection.rollback();
    } catch (SQLException e1) {
    // TODO Auto-generated catch block e1.
    printStackTrace();
    }
}

The above is the simplest version of java code using jdbc to operate the database. Database transactions usually rely on catch exception statements

11. 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.

11.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.
Statement stmt = conn.createStatement();
conn.setAutoCommit(false);
//sql1
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(200,'Zia', 'Ali', 30)";
stmt.addBatch(SQL);
//sql2
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(201,'Raj', 'Kumar', 35)";
stmt.addBatch(SQL);
//sql3
String SQL = "UPDATE Employees SET age = 35 WHERE id = 100";
stmt.addBatch(SQL);
int[] count = stmt.executeBatch();
conn.commit();

11.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.
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(SQL);
conn.setAutoCommit(false);
// Set the variables
pstmt.setInt( 1, 400 );
pstmt.setString( 2, "Pappu" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 33 );
// Add it to the batch
pstmt.addBatch();
// Set the variables pstmt.setInt( 1, 401 );
pstmt.setString( 2, "Pawan" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 31 );
// Add it to the batch
pstmt.addBatch();
//add more batches 
//Create an int[] to hold returned values
int[] count = stmt.executeBatch();
//Explicitly commit statements to apply changes 
conn.commit();

12. Reflection processing result set

Interface:

//Query all student information
public List<Student> findallstudent(Class cla);

Implementation class:

@Override
public List<Student> findallstudent(Class cla) { 
    Connection con = null;
    PreparedStatement pps = null;
    ResultSet rs =null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/yhp", "root", "123456");
        List list=new ArrayList();
        String sqla = "select * from student";
        pps = con.prepareStatement(sqla);
        rs = pps.executeQuery();
        //What are all the columns in the database?
        ResultSetMetaData metaData = rs.getMetaData();
        //Returns the relevant information in the database
        int count=metaData.getColumnCount();
        //Get the number of columns
        String[] columnnames=new String[count]; for (int i = 0; i < count; i++) {
            // System.out.println(metaData.getColumnName(i+1));// The position of the column starts at 1 
            columnnames[i]=metaData.getColumnName(i+1);
        }
        //Get all the methods in the entity class
        Method[] methods =cla.getDeclaredMethods();
        while(rs.next()){
            Object s=cla.newInstance();//Create object by calling parameterless construction
            for (String columnname : columnnames) {
                String name="set"+columnname;//setstuid
                for (Method method : methods) {
                    if(method.getName().equalsIgnoreCase(name)){ 
                        method.invoke(s,rs.getObject(columnname));//The corresponding set method is executed
                        break;
                    }
                }
            }
            list.add(s);
        }
        System.out.println("Successful execution");
        return list;
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (pps != null) {
                pps.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return null;
}

13. Definition of tools

public class DbUtils {
    //1. Define the required tool class objects
    protected Connection connection=null;
    protected PreparedStatement pps=null;
    protected ResultSet rs=null;
    protected int k=0;//Number of rows affected
    private String url="jdbc:mysql://localhost:3306/yhp";
    private String username="root";
    private String password="123456";
    //2. Load drive
    static{
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //3. Get connected
    protected Connection getConnection(){
        try {connection=DriverManager.getConnection(url,username,password);
    } catch (SQLException e) {
        e.printStackTrace();
    }
        return connection;
    }
    //4. Create channel
    protected PreparedStatement getPps(String sql){
        try {getConnection();//insert into users values(?,?,?,?,) pps=connection.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pps;
    }
    //5. Assign a value to the placeholder. The value assigned to the placeholder is saved in the list 
    private void setParams(List list){
        try {
            if(list!=null&&list.size()>0){
                for (int i=0;i<list.size();i++) { 
                    pps.setObject(i+1,list.get(i));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //6. Method of adding, deleting, modifying and retrieving
    protected int update(String sql,List params){  
        try {
            getPps(sql);
            setParams(params);
            k= pps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return k;
    }
    //7. Retrieve a method during query
    protected ResultSet query(String sql,List list){
        try {
            getPps(sql);
            setParams(list);
            rs=pps.executeQuery();
            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    //8. Close resources
    protected void closeall(){
        try {if (rs != null) {
            rs.close();
        }
        if (pps != null) {
            pps.close();
        }
        if (connection != null) {
            connection.close();
        }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

13.1 properties file saves database information - feature: key value storage method

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/yhp
user=root
password=123456

Read the properties file from the tool class

InputStream inputStream = Current class name.class.getClassLoader() .getResourceAsStream("db.properties");
Properties properties = new Properties(); properties.load(inputStream); dirverName = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("user");
password = properties.getProperty("password");

Mode 2:

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

explain:
Using ResourceBundle to access local resources

During design, we often need to access some configuration information suitable for local modification. If it is used as a static variable, we need to recompile a class every time. config is not suitable for saving such information. At this time, we need ResourceBundle.

Through ResourceBundle, we need to access a text type file with the suffix properties located in the / WEB-INF/classes directory to read the values we need.

14. Connection pool

14.1 custom 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 number and usage of database connections through its own management mechanism.

  • 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:

public class Pool{
    static LinkedList<Connection> list = new LinkedList<Connection>();
    static{
        for (int i = 0; i < 10; i++) { 
            Connection connection = JDBCUtils.newInstance().getConnection();
            list.add(connection);
        }
    }
    /*** How to get connections from the connection pool * @ return */ public static Connection getConnection(){
        if (list.isEmpty()) {
            //The JDBC utils class is a custom class that encapsulates the information code for connecting to the database
            Connection connection = JDBCUtils.newInstance().getConnection();
            list.addLast(connection);
        }
        Connection conn = list.removeFirst(); return conn;
    }
    /*** Return to connection pool */
    public static void addBack(Connection conn
    {    
        if (list.size() >= 10) {
            try {conn.close();
            } catch (SQLException e) {
            // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        else{
            list.addLast(conn); //10
        }
    }
    /*** Method to get the number of connections in the connection pool */
    public static int getSize(){
        return list.size();
    }
}
  • 1.2 implementation of connection pool in Java specification

Java provides a specification (Interface) for connection pool implementation. The specification is written in such a way that we need to implement the DataSource interface!

However, there is a disadvantage in implementing the DataSource interface, which does not provide a recycling link method! Here we will use decorator mode!

Decoration Connection! The specific implementation code is as follows:

  1. Create decorative Connection
public class MyConnection implements Connection{
    //Import the decorator
    private Connection conn;
    private LinkedList<Connection> list;
    public MyConnection(Connection conn, LinkedList<Connection> list) {
        super();
        this.conn = conn;
        this.list = list;
    }
    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return conn.unwrap(iface);
    }
    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return conn.isWrapperFor(iface);
    }
    @Override
    public Statement createStatement() throws SQLException {
        return conn.createStatement();
    }
    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return conn.prepareStatement(sql);
    }
    @Override
    public CallableStatement prepareCall(String sql) throws SQLException {
        return null;
    }
    @Override
    public String nativeSQL(String sql) throws SQLException {
        return null;
    }
    @Override
    public void setAutoCommit(boolean autoCommit) throws SQLException {

    }
    @Override
    public boolean getAutoCommit() throws SQLException {
        return false;
    }
    @Override
    public void commit() throws SQLException { 
        conn.commit();
    }
    @Override
    public void rollback() throws SQLException { 
        conn.rollback();
    }
    @Override
    public void close() throws SQLException {
        list.addLast(conn);
    }
    ...
}

Connection pool based on specification

/*** Create a canonical connection pool */
public class DataSourcePool implements DataSource{ 
    static LinkedList<Connection> list = new LinkedList<Connection>();
    static{
        for (int i = 0; i < 10; i++) {
            Connection connection = JDBCUtils.newInstance().getConnection();
            list.add(connection);
        }
    }
    public static int getSize(){
        return list.size();
    }
    @Override
    public Connection getConnection() throws SQLException {
        Connection conn = list.removeFirst(); MyConnection conn1 = new MyConnection(conn, list);
        return conn1;
    }
    @Override
    public PrintWriter getLogWriter() throws SQLException {
        return null;
    }
    @Override
    public void setLogWriter(PrintWriter out) throws SQLException {

    }
    @Override
    public void setLoginTimeout(int seconds) throws SQLException {

    }
    @Override
    public int getLoginTimeout() throws SQLException {
        return 0;
    }
    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        return null;
    }
    @Override public <T> T unwrap(Class<T> iface) throws SQLException {
        return null;
    }
    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return false;
    }
    @Override
    public Connection getConnection(String username, String password) throws SQLException{
        return null;
    }
}

Minimum connections:

Is the number of database connections maintained by the database. Therefore, if the application does not use database connections, a large number of database resources will be wasted.

Number of initialized connections:

The number of initialized database connections created when the connection pool started.

Maximum connections:

Is the maximum number of connections that the connection pool can apply for. If the database connection request exceeds this number, the subsequent database connection requests will be added to the waiting queue.

Maximum waiting time:

When there is no available connection, the connection pool waits for the maximum time for the connection to be returned. If the time exceeds, an exception is thrown. The parameter can be set to 0 or negative to make the connection wait indefinitely (according to different connection pool configurations).

Note 1: in the configuration of DBCP connection pool, there is also a maxIdle attribute, which indicates the maximum number of idle connections. The excess idle connections will be released. The default value is 8. The corresponding attribute is no longer used in the Druid connection pool, and configuration has no effect. c3p0 connection pool has no effect
Expected properties.

Note 2: the database connection pool will create initialSize connections during initialization. When there are database operations, one connection will be taken from the pool. If the number of connections in use in the current pool is equal to maxActive, it will wait for some time and wait for other operations to release a connection. If the waiting time exceeds maxWait, an error will be reported; If the number of connections currently in use does not reach maxActive, judge whether the current connection is idle. If yes, use the idle connection directly. If not, establish a new connection. After the connection is used, instead of closing its physical connection, it is put into the pool for reuse by other operations.

14.2 DBCP connection pool

DBCP is a database connection pool that relies on the Jakarta commons pool object pool mechanism. DBCP can be directly used in applications. The data source of Tomcat uses DBCP.

2.1 use of DBCP connection pool

2.1.1 import the corresponding jar package

mysql-jdbc.jar
commons-dbcp.jar
commons-pool.jar

2.1.2 hard coding using DBCP

The so-called hard coding method is to add configuration to the code

@Test
public void testHard() throws SQLException{
    // Hard coding uses DBCP connection pool
    BasicDataSource source = new BasicDataSource();
    //Set connection information
    source.setDriverClassName("com.mysql.jdbc.Driver");
    source.setUrl("jdbc:mysql://localhost:3306/day2");
    source.setUsername("root");
    source.setPassword("111");
    Connection connection = source.getConnection();
    String sql = "select * from student";
    Statement createStatement = connection.createStatement();
    ResultSet executeQuery = createStatement.executeQuery(sql);
    while (executeQuery.next()) {
        System.out.println(executeQuery.getString(2));
    }
    connection.close(); //recovery
}

2.1.4 DBCP is used for soft coding

The so-called soft coding is to add a configuration file to the project, so you don't need to add cooperation in the code every time! 1. Add configuration to the project

File name: info.properties
File location: under src

#Connection settings driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/day2
username=root
password=111
#<!--  Initialize connection -- >
initialSize=10
#Maximum number of connections
maxActive=50
#<!--  Maximum idle connections -- >
maxIdle=20
#<!--  Minimum idle connection -- >
minIdle=5
#<!--  Timeout wait time in milliseconds 6000 milliseconds / 1000 equals 60 seconds -- >
maxWait=6000

1.DButils tool class code implementation

//1. Create the tool class object of dbcp
static BasicDataSource datasource=new BasicDataSource();
//2. Load drive
static {
    try {
        //Load properties file
        //1. Use the tool class. The parameter is the file name of the property file (do not add suffix)
        ResourceBundle bundle = ResourceBundle.getBundle("db");
        driverClass = bundle.getString("driverclass");
        url = bundle.getString("url");
        username = bundle.getString("uname");password = bundle.getString("upass");init=bundle.getString("initsize");
        //2. Pass the driver address and other information to dbcp
        datasource.setDriverClassName(driverClass);
        datasource.setUrl(url);
        datasource.setUsername(username);datasource.setPassword(password);datasource.setInitialSize(Integer.parseInt(init));
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}
//3. Get connected
public static Connection getConn() {
    try {
        con= datasource.getConnection();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return con;
}

14.3 C3P0 connection pool

c3p0 is an open source JDBC Connection pool. It is published together with Hibernate in the lib directory, including the DataSources objects that implement the Connection and Statement pools described in the JDBC 3 and JDBC 2 extension specifications.

Differences between c3p0 and dbcp
1.
dbcp does not automatically recycle idle connections
c3p0 has the function of automatically reclaiming idle connections
2.
dbcp needs to set the configuration file manually
c3p0 does not need to be set manually
3.1 implementation mode
1. Set ComboPooledDataSource manually
2 loading configuration file mode

src/c3p0-config.xml(File name (fixed)
ComboPooledDataSource cpds = new ComboPooledDataSource();
Load file <default-config>Configuration in

ComboPooledDataSource cpds = new ComboPooledDataSource("aaa");
load <named-config name="aaa"> Configuration in

3.2 implementation steps

3.1.1 import jar package
c3p0-0.9.1.2.jar
mysql-connector-java-5.0.8.jar

3.1.2. Add profile

c3p0 is to add a configuration file externally. The tool is applied directly. Because it is directly referenced, it requires a fixed name and file location

File location: src
File name: c3p0-config.xml/c3p0-config.properties

<?xml version="1.0" encoding="utf-8"?> <c3p0-config> <!-- Default configuration. If not specified, this configuration is used --> <default-config> <!-- Basic configuration --> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/day2</property> <property name="user">root</property> <property name="password">111</property> <!-- Extended configuration -- > <-- An error is reported when the connection exceeds 30 seconds -- > < property name = "checkouttimeout" > 30000 < / property > <-- Check the idle connection in 30 seconds -- > < property name = "idleconnectiontestperiod" > 30 < / property > < property name = "initialpoolsize" > 10 < / property > <-- 30 seconds is not applicable to discard -- > < property name = "MaxIdleTime" > 30 < / property > < property name = "maxpoolsize" > 100 < / property > < property name = "minpoolsize" > 10 < / property > < property name = "maxstatements" > 200 < / property > < / default config > <-- Named configuration -- > < named config name = "ABC" > < property name = "driverclass" > com. Mysql. JDBC. Driver < / property > < property name = "jdbcurl" > JDBC: mysql://localhost:3306/day2 </property> <property name="user">root</property> <property name="password">111</property> <!--  If the data connections in the pool are insufficient, how many -- > < property name = "acquireincrement" > 5 < / property > < property name = "initialpoolsize" > 20 < / property > < property name = "minpoolsize" > 10 < / property > < property name = "maxpoolsize" > 40 < / property > < property name = "maxstatements" > 20 < / property > < property name = "maxstatementperconnection" >5</property> </named-config> </c3p0-config>

Note: the configuration file of c3p0 can contain named configuration file and default configuration file! The default is to select the default configuration! If you need to switch the naming configuration, you can fill in the naming when creating c3p0 connection pool!

3.1.3. Definition code

Connection con=null;
ComboPooledDataSource db=new ComboPooledDataSource("abc");
public Connection getCon(){
    try {con=db.getConnection();
    System.out.println("Number of links initialized:"+db.getInitialPoolSize());
} catch (SQLException e) {
    e.printStackTrace();
}
    return con;
}

14.4 Druid connection pool

Ali produces Taobao and Alipay dedicated database connection pool, but it is not only a database connection pool, it also includes a ProxyDriver (agent driven), a series of built-in JDBC component library, a SQL Parser(sql parser). Support all JDBC compatible databases, including Oracle, MySql, Derby, Postgresql, SQL Server, H2, etc.

Druid has made special optimizations for Oracle and MySql, such as Oracle's PS Cache memory usage optimization and MySql's ping detection optimization.

Druid provides complete support for SQL of MySql, Oracle, Postgresql and SQL-92. It is a handwritten high-performance SQLParser and supports Visitor mode, which makes it convenient to analyze the abstract syntax tree of SQL.

Simple SQL statement takes less than 10 microseconds, and complex SQL takes 30 microseconds.

Through the SQL Parser provided by Druid, you can intercept SQL at the JDBC layer and do corresponding processing, such as database and table segmentation, audit, etc. Druid's WallFilter for defending against SQL injection attacks is implemented through Druid's SQL Parser analysis semantics.

Druid is a popular OLAP framework with high performance and distributed column storage (specifically MOLAP). It has the following characteristics:

1, Sub second level query
druid provides fast aggregation capability and sub second OLAP query capability. Multi tenant design is the principle of user oriented analysis applications
Think of a way.

2, Real time data injection
druid supports stream data injection and provides event driven data to ensure the effectiveness and unity of events in real-time and offline environments

3, Scalable petabyte storage
druid clusters can be easily expanded to petabytes of data, with millions of data injections per second. Even when the data scale is increased, its effectiveness can be guaranteed

4, Multi environment deployment
druid can run on both commercial hardware and cloud. It can inject data from a variety of data systems, including hadoop, spark, kafka, storm and samza

5, Rich community
druid has a rich community for everyone to learn from

  • 4.1 use steps

4.1.1 import jar package

4.1.2 writing tools

/*** Alibaba's database connection pool
* Best performance
* Druid
* */
public class DruidUtils {
    //Declare connection pool object
    private static DruidDataSource ds;
    static{
        ///Instantiate database connection pool object
        ds=new DruidDataSource();
        //Instantiate configuration object
        Properties properties=new Properties(); try {
            //Load profile content
            properties.load(DruidUtils.class.getResourceAsStream("dbcpconfig.properties"));
            //Set the full name of the driver class
            ds.setDriverClassName(properties.getProperty("driverClassName"));
            //Set the connected database
            ds.setUrl(properties.getProperty("url"));
            //Set user name
            ds.setUsername(properties.getProperty("username"));
            //Set password
            ds.setPassword(properties.getProperty("password"));
            //Set the maximum number of connections
            ds.setMaxActive(Integer.parseInt(properties.getProperty("maxActive")));
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    //Get connection object
    public static Connection getConnection() {
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
}

Note: in the configuration of Druid connection pool, driverClassName can be configured or not. If it is not configured, dbtype (number) will be automatically identified according to the url
According to the library type), and then select the corresponding driverClassName.

Posted by scottrad on Mon, 25 Oct 2021 07:15:04 -0700

Hot Keywords

©2024 Programmer Group