1, JDBC introduction

• JDBC(Java Database Connectivity) is a common interface (a set of API s) independent of a specific database management system and general SQL database access and operation. It defines the standard Java class libraries used to access the database (java.sql,javax.sql). These class libraries can be used to access database resources in a standard and convenient way.
• JDBC is a set of interfaces provided by sun company for database operation. java programmers only need to program for this set of interfaces. Different database manufacturers need to provide different implementations for this set of interfaces. The collection of different implementations is the driver of different databases———— Interface oriented programming
  JDBC programming steps
• JDBC provides a unified way to access different databases and shields some details for developers.
• The goal of JDBC is to enable Java programmers to use JDBC to connect to any database system that provides JDBC drivers, so that programmers do not need to know too much about the characteristics of a specific database system, which greatly simplifies and speeds up the development process.
• If there is no JDBC, the Java program accesses the database as follows:
  
• With JDBC, the Java program accesses the database as follows:
• JDBC programming steps
  Supplement: ODBC(**Open Database Connectivity * *) is launched by Microsoft under the Windows platform. The user only needs to call ODBC API in the program, and the ODBC driver converts the call into a call request to a specific database.

2, Get MySQL database connection

Get the three elements of MySQL database connection

1, Driver interface implementation class

1. Driver interface introduction

• The java.sql.Driver interface is the interface that all JDBC drivers need to implement. This interface is provided for database manufacturers. Different database manufacturers provide different implementations.

• In the program, you do not need to directly access the classes that implement the Driver interface, but the Driver manager class (java.sql.DriverManager) calls these Driver implementations.

  • Oracle driver: oracle.jdbc.driver.OracleDriver
  • mySql driver: com.mysql.jdbc.Driver
    

• Copy the above jar package to a directory of the Java project. It is customary to create a new lib folder.
  
  Note: unzip the jar package after importing it

2. Loading and registering JDBC drivers

Class.forName("com.mysql.jdbc.Driver");

2, URL

JDBC URL is used to identify a registered driver. The driver manager selects the correct driver through this URL to establish a connection to the database.
The standard JDBC URL consists of three parts separated by colons.

• jdbc: sub protocol: sub name
• Protocol: the protocol in the JDBC URL is always jdbc
• Sub protocol: the sub protocol is used to identify a database driver
• Subname: a method of identifying a database. The sub name can be changed according to different sub protocols. The purpose of using the sub name is to provide sufficient information for locating the database. Including host name (corresponding to the ip address of the server), port number and database name
  
• If you are accessing a local database: jdbc:mysql:///test

3, User name and password

• User and password can tell the database by "attribute name = attribute value"
• You can call the getConnection() method of the DriverManager class to establish a connection to the database

Connection database code implementation

Method 1:

	@Test
    public void testConnection() {
        try {
            //1. Four basic elements of database connection:
            String url = "jdbc:mysql://localhost:3306/test";
            String user = "root";
            String password = "abc123";
            String driverName = "com.mysql.jdbc.Driver";

            //2. Load Driver (① instantiate Driver ② register Driver)
            Class.forName(driverName);


            //Driver driver = (Driver) clazz.newInstance();
            //3. Register driver
            //DriverManager.registerDriver(driver);
            /*
            The reason why the above code can be commented out is that the Driver class of mysql declares:
            static {
                try {
                    DriverManager.registerDriver(new Driver());
                } catch (SQLException var1) {
                    throw new RuntimeException("Can't register driver!");
                }
            }

             */


            //3. Get connection
            Connection conn = DriverManager.getConnection(url, user, password);
            System.out.println(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

Note: there is no need to explicitly register the driver. Because the static code block already exists in the source code of DriverManager, the driver registration is realized.

Method 2: permanent storage of configuration file is realized

	@Test
    public  void testConnection5() throws Exception {
    	//1. Load configuration file
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        
        //2. Read configuration information
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //3. Load drive
        Class.forName(driverClass);

        //4. Get connection
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);

    }

• The configuration file is declared in the src directory of the project: [jdbc.properties]
  

Packaging tool class

Finally, we can put the method of obtaining the connection of the database in the tool class, and take the connection as the return value:

import java.util.Properties;
public class JDBCUtils {
    public static Connection getConnection() throws Exception {
        //Read 4 basic information in the configuration file
        InputStream is1 = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.propertices");

        // FileInputStream fis=new FileInputStream("src//jdbc.propertices");
        Properties pro=new Properties();
        pro.load(is1);
        String user = pro.getProperty("user");
        String url = pro.getProperty("url");
        String password = pro.getProperty("password");
        String driver = pro.getProperty("driver");
        //Load driver
        Class.forName(driver);

        //Get connection
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
}

3, PreparedStatement implements operations on tables in the database (CRUD)

PreparedStatement introduction

• The PreparedStatement object can be obtained by calling the preparedStatement(String sql) method of the Connection object

• PreparedStatement interface is a sub interface of Statement, which represents a precompiled SQL Statement

• The parameters in the SQL statement represented by the PreparedStatement object are represented by a question mark (?). Call the setXxx() method of the PreparedStatement object to set these parameters. The setXxx() method has two parameters. The first parameter is the index of the parameter in the SQL statement to be set (starting from 1), and the second is the value of the parameter in the SQL statement to be set

• Close the resource after the PreparedStatement is used

PreparedStatement vs Statement

• Code readability and maintainability.

• PreparedStatement can manipulate Blob type data

• PreparedStatement maximizes performance:

  • DBServer provides performance optimization for precompiled statements. Because the precompiled statement may be called repeatedly, the execution code of the statement compiled by the DBServer compiler is cached. As long as it is the same precompiled statement in the next call, it does not need to be compiled. As long as the parameters are directly passed into the compiled statement execution code, it will be executed.
  • In the statement statement statement, even if it is the same operation, because the data content is different, the whole statement itself cannot match, and there is no meaning of caching the statement. In fact, no database will cache the execution code after the compilation of ordinary statements. In this way, the incoming statement will be compiled once every execution.
  • (syntax check, semantic check, translation into binary commands, cache)

• PreparedStatement prevents SQL injection
  

General addition, deletion and modification

1. Code understanding

public void testInsert() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //Read 4 basic information in the configuration file
            //ClassLoader.getSystemClassLoader(): get the system class loader
            InputStream is1 = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.propertices");

            // FileInputStream fis=new FileInputStream("src//jdbc.propertices");
            Properties pro = new Properties();
            pro.load(is1);
            String user = pro.getProperty("user");
            String url = pro.getProperty("url");
            String password = pro.getProperty("password");
            String driver = pro.getProperty("driver");
            //Load driver
            Class.forName(driver);

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

            //4. Precompile the sql statement and return the instance of Preparedstatement
            String sql = "insert into book(bid,bname,price,btypeId) values(?,?,?,?);";//?: placeholder 
            ps = conn.prepareStatement(sql);
            //Fill placeholder
            ps.setInt(1, 224);
            ps.setString(2, "Zhang San");
            ps.setFloat(3, 226);
            ps.setInt(4, 11);

            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

• , the encapsulation closing operation is written into the tool class (JDBC utils), and the code is as follows:

    public static void closeResource(Connection conn, Statement s){
        try {
            conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            s.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

2. General addition, deletion and modification operations are used to iteratively optimize the above code, and the results are as follows

//General addition, deletion and modification
    public static void update(String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.propertices");
            Properties p = new Properties();
            p.load(is);
            //Get connection
            conn = JDBCUtils.getConnection();
            //Precompile the sql statement and return an instance of Preparedstatement
            ps = conn.prepareStatement(sql);

            //Fill placeholder
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            //implement
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //close resource
            JDBCUtils.closeResource(conn, ps);
        }

    }

General query operation

ResultSet and ResultSetMetaData (resources need to be closed)

ResultSet

• The query needs to call the executeQuery() method of PreparedStatement, and the query result is a ResultSet object

• The ResultSet object encapsulates the result set of database operation in the form of logical table, and the ResultSet interface is provided and implemented by the database manufacturer

• What the ResultSet returns is actually a data table. There is a pointer to the front of the first record in the data table.

• The ResultSet object maintains a cursor pointing to the current data row. Initially, the cursor is before the first row and can be moved to the next row through the next() method of the ResultSet object. Call the next () method to check whether the next line is valid. If valid, the method returns true and the pointer moves down. Equivalent to a combination of hasNext() and next () methods of the Iterator object.

• When the pointer points to a row, you can get the value of each column by calling getXxx(int index) or getXxx(int columnName).

  • For example: getInt(1), getString("name")
  • Note: the indexes in the relevant Java API s involved in the interaction between Java and database start from 1.

• Common methods of ResultSet interface:

  • boolean next()

  • getString()

  • ...
    

ResultSetMetaData

• An object that can be used to get information about the types and properties of columns in a ResultSet object
• ResultSet sc=new ResultSet();
• ResultSetMetaData rsmd = rs.getMetaData();
  • getColumnName(int column): get the name of the specified column (not recommended)
  • getColumnLabel(int column): gets the alias of the specified column
  • getColumnCount(): returns the number of columns in the current ResultSet object.
    
    

General query operations for tables

• Since the ResultSet also needs to release resources, we write the code to close resources into the tool class (JDBC utils):

    public static void closeResource(Connection conn, Statement s, ResultSet rs){
        try {
            conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            s.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
           rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

Query any row number of any table as follows:

//Data query of any number of rows for any table
    public <T> List<T> getInstance(Class<T> clazz, String sql, Object...args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();//Get result set

            ResultSetMetaData rsmd = rs.getMetaData();//Get metadata of result set

            int columnCount = rsmd.getColumnCount();//Get the number of columns in the result set through ResultSetMetaData

            ArrayList<T> list = new ArrayList<>();
            while (rs.next()) {//rs has a value and returns true
                T t = clazz.newInstance();
                //Process each column in a row of data
                for (int i = 0; i < columnCount; i++) {
                    Object value = rs.getObject(i + 1);//Get the specific value of each column
                    // String columnName = rsmd.getColumnName(i + 1);// Gets the column name of each column. (not recommended)
                    String columnLabel = rsmd.getColumnLabel(i + 1);//Get the alias of each column. If there is no alias, the column name is obtained by default
                    //The columnName property specified to the book object is assigned columnValue: through reflection
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);

        }
        return null;
    }