Catalogue of series articles

The purpose of this article is to explain in detail the six steps of connecting to a database using JDBC

  The first step is to register the explanation link of the driver: JDBC - MySQL registers the driver (reflection) in the way of class loading_ m0_56164356 blog - CSDN blog

        // 1. Register driver
        // 2. Get connection
        // 3. Get database operation object
        // 4. Execute sql statement
        // 5. Get query result set
        // 6. Release resources

  By explaining the code of these six steps, this paper takes the reader step by step to deeply analyze and understand the alternative writing method of the six steps of JDBC connecting to the database, and analyze its use scenario and performance to know why! After learning all the six steps, we need to optimize the most commonly used code, and encapsulate the three steps of registering drivers, obtaining connections and releasing resources in the above six steps into tool classes. How DBUtil is encapsulated into tool classes and related knowledge points are explained in detail in the previous blog post: This article takes you to understand how JDBC connects to MySQL & using JDBC to realize MySQL fuzzy query_ m0_56164356 blog - CSDN blog

preface   Interface questions during interview:

[an interface is a collection of methods]

In this article, we need to know JDBC, so we need to know why. The process of connecting to the database using JDBC is to call the implementation class (driver) of the interface to perform operations. So what is an interface? Why do we use interface oriented programming in JDBC?

  Original interview question: about the use specification of the interface:

1. Constants can be defined in the interface, but variables cannot be defined. If you define attributes in the interface, you can see that they will be automatically modified with public static final through decompilation. The attributes in the interface are global static constants. The constants in the interface must specify the initial value during definition. The data in the interface is defined as constants and called globally.

2. All methods in the interface are abstract methods, and the methods in the interface will be automatically modified with public abstract, that is, there are only global abstract methods in the interface.

3. The interface cannot be instantiated, and there cannot be constructs in the interface.

4. The inheritance relationship between interfaces can be realized through extensions. An interface can inherit multiple interfaces, but the interface cannot inherit classes.

5. The implementation class of the interface must implement all methods of the interface, otherwise it must be defined as an abstract class.

Interface oriented programming

Interfaces have callers and implementers. Interface oriented calling and interface oriented writing implementation classes belong to interface oriented programming.

If interfaces and abstract classes can perform the same functions, try to use interfaces, which is interface oriented programming. Interface oriented programming has the advantage of using polymorphism, assigning the implementation class object to the interface type variable, shielding the implementation differences between different implementation classes, so as to achieve universal programming.

1, What is JDBC?

JDBC refers to Java Database Connectivity, which is a standard Java application programming interface (JAVA API). JDBC is a set of interfaces defined by Sun company, and JDBC is essentially a set of interfaces formulated by Sun company (interface)! Every database manufacturer needs to implement this interface. We only need to call it to connect Java programming language and a wide range of databases.

The JDBC API library contains each of the tasks mentioned below, which are common usage related to the database.

• Make a connection to the database.
• Create SQL or MySQL statements.
• Execute SQL or MySQL query database.
• View and modify the generated records.

Fundamentally speaking, JDBC is a specification that provides a complete set of interfaces to allow portable access to the underlying database. Therefore, different types of executable files can be written in Java, such as:

• Java application
• Java Applets
• Java Servlets
• Java ServerPages (JSPs)
• Enterprise JavaBeans (EJBs)

All these different executable files can use JDBC driver to access the database, which can easily access data.

JDBC has the same performance as ODBC, allowing Java programs to contain database independent code.

2, Use steps




1. Register driver

The implementation class written by Java programmers of database manufacturers is called Driver

Register driver

The code of the first registration method is as follows: (not commonly used)

public class Register driver {
    public static void main(String[] args) {
        try {
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}

The second method uses the characteristics of reflection to register the driver in the loading process.

Supplement on Reflection:   Soul reflection mechanism in Java Xiaotao's blog without code today - CSDN blog

Link to detailed explanation of registering driver (reflection) by class loading in JDBC MySQL:

JDBC - MySQL registers the blog of driver (reflection) _m0_56164356 by class loading - CSDN blog

class.forName(com.mysql.jdbc.Driver);

The above line of code can call the class by reflecting this action to load the Driver class, but it needs to be surrounded by try and catch statement blocks

2. Get connection

URL to connect to the database----   String   url="jdbc:mysql://localhost:3306/test? "+   "Useunicode = true & characterencoding = utf8"; / / prevent garbled code
User name to connect to the database----   String   user="xxxx";
Password to connect to the database----   String   pass="xxxx";

Next, let's analyze the url:
"jdbc (this is a protocol that starts with jdbc): MySQL (this is a sub protocol, which is called by the database management system): / / localhost (database source address): 3306 (target port) / test (table name of the table to be queried)?"
"Useunicode = true & characterencoding = utf8"; this is added to prevent garbled code and specify the use of Unicode character set  ， And use UTF-8 to edit.

            /*
                 Which parts does the url include:
                     agreement
                    IP
                    Port
                     Resource name

                eg: http://180.101.49.11:80/index.html
                     http: / / communication protocol
                     180.101.49.11 IP address
                     80 port number
                     index.html resource name
            */

 // 2. Get connection
			/*
				url Which parts are included:
					agreement
					IP
					Port
					Resource name

				eg: http://180.101.49.11:80/index.html
					http:// communication protocol
					180.101.49.11 IP address
					80 Port number
					index.html Resource name
			*/
            // static Connection getConnection(String url, String user, String password)
            String url = "jdbc:mysql://127.0.0.1:3306/hello";
            String user = "root";
            System.out.println(" ");
            String password = "rota";
            conn = DriverManager.getConnection(url,user,password);
            System.out.println("Database connection object:     " + conn);	//Database connection object com.mysql.jdbc.com JDBC4Connection@1ae369b7

3. Get database operation object

            // 3. Get database operation object
            // createStatement() in the Statement class creates a Statement object to send SQL statements to the database.
            stmt = conn.createStatement();

            // 4. Execute sql statement
            // int executeUpdate(String sql)
            // Dedicated execution of DML statements
            // The return value is "affect the number of records in the database"
            int count = stmt.executeUpdate("update dept set dname = 'Sales Department',loc = 'Hefei' where deptno = 20;");
            System.out.println(count == 1 ? "Saved successfully":"Save failed");

4. Execute sql statement

            // 4. Execute sql statement
            // int executeUpdate(String sql)
            // Dedicated execution of DML statements
            // The return value is "affect the number of records in the database"
            int count = stmt.executeUpdate("update dept set dname = 'Sales Department',loc = 'Hefei' where deptno = 20;");
            System.out.println(count == 1 ? "Saved successfully":"Save failed");

5. Processing query result set

rs = stmt.executeQuery("select empno,ename,sal from emp");

            while(rs.next()){
				/*
				String empno = rs.getString(1);
				String ename = rs.getString(2);
				String sal = rs.getString(3);
				System.out.println(empno + "," + ename + "," + sal);
				*/

				/*
				// Press the subscript to take it out, and the program is not robust
				String empno = rs.getString("empno");
				String ename = rs.getString("ename");
				String sal = rs.getString("sal");
				System.out.println(empno + "," + ename + "," + sal);
				*/

				/*
				// Fetch in the specified format
				int empno = rs.getInt(1);
				String ename = rs.getString(2);
				double sal = rs.getDouble(3);
				System.out.println(empno + "," + ename + "," + (sal + 100));
				*/

                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                double sal = rs.getDouble("sal");
                System.out.println(empno + "," + ename + "," + (sal + 200));
            }

The return value of the method performing addition, deletion and modification is of type int

The return value of the method executing the query is the operation result set object, even if the instantiation object of the ResultSet!

6. Release resources

finally {
            // 6. Release resources
            // Close from small to large
            //The statements in the finally statement block must be executed!
            if(stmt != null) {
                try	{
                    stmt.close();
                }
                catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null) {
                try	{
                    conn.close();
                }
                catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

The above six steps are consistent:

First optimization: (compare two registration driven methods)

import java.sql.*;

public class JDBCTest01 {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;//The connection object and operation object are created first and the reference is empty, so that the life cycle of the object variable is not limited to the try statement block, but in the whole main method, so as to facilitate the release of resources in the subsequent finally statement block
        try{
            // 1. Register driver
            Driver driver = new com.mysql.jdbc.Driver();	//Polymorphic, the parent type reference points to the child type object
            DriverManager.registerDriver(driver);

            // 2. Get connection
			/*
				url Which parts are included:
					agreement
					IP
					Port
					Resource name

				eg: http://180.101.49.11:80/index.html
					http:// communication protocol
					180.101.49.11 IP address
					80 Port number
					index.html Resource name
			*/
            // static Connection getConnection(String url, String user, String password)
            String url = "jdbc:mysql://127.0.0.1:3306/hello";
            String user = "root";
            System.out.println(" ");
            String password = "rota";
            conn = DriverManager.getConnection(url,user,password);
            System.out.println("Database connection object:     " + conn);	//Database connection object com.mysql.jdbc.com JDBC4Connection@1ae369b7

            // 3. Get database operation object
            // createStatement() in the Statement class creates a Statement object to send SQL statements to the database.
            stmt = conn.createStatement();

            // 4. Execute sql statement
            // int executeUpdate(String sql)
            // Dedicated execution of DML statements
            // The return value is "affect the number of records in the database"
            int count = stmt.executeUpdate("update dept set dname = 'Sales Department',loc = 'Hefei' where deptno = 20;");
            System.out.println(count == 1 ? "Saved successfully":"Save failed");

            // 5. Process query result set

        } catch(SQLException e) {
            e.printStackTrace();
        } finally {
            // 6. Release resources
            // Close from small to large
            //The statements in the finally statement block must be executed!
            if(stmt != null) {
                try	{
                    stmt.close();
                }
                catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null) {
                try	{
                    conn.close();
                }
                catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Second optimization: (compare two registration driven methods)

package com.zdx.source.code.jdbc;

/*
	JDBC Finish Delete
*/
import java.sql.*;

public class JDBCTest02 {
    public static void main(String[] args) {
        // 1. Register driver
        // 2. Get connection
        // 3. Get database operation object
        // 4. Execute sql statement
        // 5. Get query result set
        // 6. Release resources

        Connection conn = null;
        Statement stmt = null;
        try {
            Driver driver = new com.mysql.jdbc.Driver();
            DriverManager.registerDriver(driver);

            String url = "jdbc:mysql://127.0.0.1:3306/mydatabase";
            String user = "root";
            String password = "146";
            conn = DriverManager.getConnection(url,user,password);

            stmt = conn.createStatement();

            int count = stmt.executeUpdate("delete from dept where deptno = 50");

            System.out.println(count == 1? "Delete succeeded":"Deletion failed");

        } catch(SQLException e){
            e.printStackTrace();
        } finally {
            if(conn != null) {
                try {
                    conn.close();
                } catch(SQLException e){
                    e.printStackTrace();
                }
            }
            if(stmt != null) {
                try {
                    stmt.close();
                } catch(SQLException e){
                    e.printStackTrace();
                }
            }
        }
    }
}

The third Optimization: (the best registration driver obtains the connection)

package com.zdx.source.code.jdbc;

/*
	Another way to register drivers
*/

import java.sql.*;

public class JDBCTest03 {
    public static void main(String[] args) {
        try{
            // Register driver
            Class.forName("com.mysql.jdbc.Driver");

            // Get connection
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase","root","146");
            System.out.println(conn);

        } catch(SQLException e){
            e.printStackTrace();
        } catch(ClassNotFoundException e){
            e.printStackTrace();
        }
    }
}

Fourth Optimization: (using resource binder)

package com.zdx.source.code.jdbc;

/*
	Using the resource binder
*/

import java.sql.*;
import java.util.*;

public class JDBCTest04 {
    public static void main(String[] args) {

        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        String driver = bundle.getString("driver");
        String url = bundle.getString("url");
        String user = bundle.getString("user");
        String password = bundle.getString("password");

        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName(driver);

            conn = DriverManager.getConnection(url,user,password);

            stmt = conn.createStatement();

            int count = stmt.executeUpdate("insert into dept(deptno,dname,loc) values(50,'Ministry of Personnel','Beijing');");

            System.out.println(count == 1? "Saved successfully":"Save failed");

        } catch(SQLException e){
            e.printStackTrace();
        } catch(ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if(conn != null) {
                try {
                    conn.close();
                } catch(SQLException e){
                    e.printStackTrace();
                }
            }
            if(stmt != null) {
                try {
                    stmt.close();
                } catch(SQLException e){
                    e.printStackTrace();
                }
            }
        }
    }
}

The fifth Optimization: (processing of operation result set)

package com.zdx.source.code.jdbc;

/*
	Execute DQL statement
*/

import java.sql.*;
import java.util.*;

public class JDBCTest05 {
    public static void main(String[] args) {
        // 1. Register driver
        // 2. Establish connection
        // 3. Get database operation object
        // 4. Execute sql statement
        // 5. Get query result set
        // 6. Release resources
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try{
            ResourceBundle rb = ResourceBundle.getBundle("jdbc");
            String driver = rb.getString("driver");
            String url = rb.getString("url");
            String user = rb.getString("user");
            String password = rb.getString("password");

            Class.forName(driver);

            conn = DriverManager.getConnection(url,user,password);

            stmt = conn.createStatement();

            rs = stmt.executeQuery("select empno,ename,sal from emp");

            while(rs.next()){
				/*
				String empno = rs.getString(1);
				String ename = rs.getString(2);
				String sal = rs.getString(3);
				System.out.println(empno + "," + ename + "," + sal);
				*/

				/*
				// Press the subscript to take it out, and the program is not robust
				String empno = rs.getString("empno");
				String ename = rs.getString("ename");
				String sal = rs.getString("sal");
				System.out.println(empno + "," + ename + "," + sal);
				*/

				/*
				// Fetch in the specified format
				int empno = rs.getInt(1);
				String ename = rs.getString(2);
				double sal = rs.getDouble(3);
				System.out.println(empno + "," + ename + "," + (sal + 100));
				*/

                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                double sal = rs.getDouble("sal");
                System.out.println(empno + "," + ename + "," + (sal + 200));
            }

        } catch(Exception e){
            e.printStackTrace();
        }finally{
            if(rs != null){
                try{
                    rs.close();
                } catch (Exception e){
                    e.printStackTrace();
                }
            }
            if(stmt != null){
                try{
                    stmt.close();
                } catch (Exception e){
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try{
                    conn.close();
                } catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }
}

Summary:

In the process of optimizing the code for the above five times, for these six steps

        // 1. Register driver
        // 2. Get connection
        // 3. Get database operation object
        // 4. Execute sql statement
        // 5. Get query result set
        // 6. Release resources

The registration driver in the first step finally uses reflection, which has reached the optimal level

The second step is to obtain the optimal connection, and has the ability to complete the encapsulation of the tool class for JDBC connection to the database

See here, you can learn step by step -- > Tool class encapsulation!

Note:

In the third step of obtaining the database operation object, we use the Statement interface

public interface Statement extends Wrapper, AutoCloseable 

It can also be optimized to be PreparedStatement

public interface PreparedStatement extends Statement

In the actual development process, 99.9% of the actual development uses PreparedStatement because PreparedStatement can prevent injection and the characteristics of precompiled SQL statements improve the robustness of the program. This is later. Because the encapsulation tool class mainly encapsulates the registration driver, obtains connections and releases resources, I will write a blog to discuss PreparedStatement later

In addition, in the actual development, in addition to mastering the above six steps, you also need to master the trilogy of transaction commit rollback.

The subsequent JDBC series will update the detailed explanation of PreparedStatement, the automatic submission after closing the database and opening the connection, and the transaction submission rollback three piece set!

The old fellow who likes love can pay attention. The follow-up will work harder!