JDBC_ Core technology

Chapter 1: JDBC Overview

Persistence of data

• Persistence: Saving data to a fail-safe storage device for later use. In most cases, especially in enterprise applications, data persistence means “curing” data in memory onto hard disk, and persistence is achieved through various methodsRelational databaseTo complete.
• The main application of persistence is to store in-memory data in a relational database, as well as disk files and XML data files.

Data storage technology in Java

In Java, database access technologies fall into the following categories:

• JDBC directly accesses the database
• Java Data Object (JDO) technology
• Third party O/R tools, such as Hibernate, Mybatis, etc

JDBC is the cornerstone of Java accessing database, JDO, Hibernate, MyBatis and so on just better encapsulate JDBC.

JDBC is introduced

• Java Database Connectivity (JDBC) is oneA common interface (set of apis) for accessing and manipulating SQL databases, independent of a specific database management systemSQL,javax.sql), which allows easy access to database resources in a standard way.
• JDBC provides a way to access different databasesUnified approach“, shielding developers from some of the details.
• The goal of JDBC is to make it possible for Java programmers to connect to anything using JDBCThe JDBC driver is providedDatabase system.

JDBC architecture

The JDBC interface (API) consists of two layers:

• Application-oriented apis: Java API, abstract interface for application developers to use (connect to database, execute SQL statements, get results).
• Database-oriented APIS: Java Driver API for developers to develop database drivers.

JDBC is a set of interface for database operation provided by Sun. Java programmers only need to program for this interface.

Different database vendors need to provide different implementations for this interface. A collection of different implementations, i.e. drivers for different databases. ———— Interface oriented programming

Chapter 2: Getting a database connection

Element 1: Driver interface implementation class

Driver Interface Introduction

• The java.sql.Driver interface is the interface that all JDBC drivers need to implement. This interface is intended for use by database vendors, which provide different implementations.
• Instead of directly accessing the classes that implement the Driver interface, the Driver manager class (java.sql.DriverManager) calls these Driver implementations.

Load and register the JDBC driver

• Loading the driver: Loading the JDBC driver requires calling the static method forName() of the Class Class, passing it the Class name of the JDBC driver to load
  • Class.forname (“. Com. Mysql. JDBC Driver “);
• Register drivers: the DriverManager class is the DriverManager class that manages drivers
  • Use the DriverManager. RegisterDriver (. Com. Mysql. JDBC Driver) to register the Driver

Element two: URL

• The JDBC URL identifies a registered driver, and the driver manager uses this URL to select the correct driver to establish a connection to the database.

The JDBC URL standard consists of three parts, separated by colons:

• JDBC: subprotocol: subname
• Protocol: The protocol in the JDBC URL is always JDBC
• Subprotocols: Subprotocols are used to identify a database driver
• Child name: A way to identify a database. Subnames can vary depending on the subprotocol. The purpose of using subnames is toLocating databaseProvide enough information. containsHost name (corresponding to the server IP address), port number, database name

JDBC urls for several common databases

• MySQL > connect to URL

  • JDBC :mysql:// host name :mysql server port number/database name? Parameter = value & parameter = value
  • jdbc:mysql://localhost:3306/atguigu
  • jdbc:mysql://localhost:3306/atguigu? UseUnicode =true&characterEncoding= UTF8
  • jdbc:mysql://localhost:3306/atguigu? user=root&password=123456

• Oracle 9i connection URL

  • JDBC: Oracle :thin:@ Host name: Oracle Service port number: database name
  • jdbc:oracle:thin:@localhost:1521:atguigu

• SQLServer connection URL

  • JDBC :sqlserver:// host name :sqlserver service port number :DatabaseName= DatabaseName
  • jdbc:sqlserver://localhost:1433:DatabaseName=atguigu

Element 3: User name and password

• User and password can be told to the database in the form of “Attribute name = attribute value”
• You can establish a connection to the database by calling the getConnection() method of the DriverManager class

Example of database connection mode

Connection Mode 1

@Test
public void testConnection1 (a) {
    try {
        // 1. The object that provides the Driver interface implementation class
        Driver driver = null;
        driver = new com.mysql.jdbc.Driver();
        // 2. Provide the URL to specify the specific operation data
        String url = "jdbc:mysql://localhost:3306/test";
        // 3. Provide the Properties object, specifying the user name and password
        Properties info = new propertes();
        info.setProperty("user"."root");
        info.setProperty("password"."root");
        // 4. Call connect() on the driver to obtain the connection
        Connection connect = driver.connect(url, info);
        System.out.print(connect);
    } catch(SQLException e) { e.printStackTrace(); }}Copy the code

Note: Third-party database apis appear explicitly in the above code

Connection Mode 2

• Iterate on approach one (using reflection), programming to the interface, without third-party apis
• There is no third-party API in the following program, which makes the program more portable

    // Approach 2: Iterate over approach 1 (using reflection), interface oriented programming, no third party related apis
    // There is no third party API in the following program, making the program more portable
    @Test
    public void testConnection2 (a) throws Exception {
        // 1. Get the Driver implementation object and use reflection
        Class clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();

        // 2. Provide the database to connect to
        String url = "jdbc:mysql://localhost:3306/test";

        // 3. Provide the user name and password required for connection
        Properties info = new Properties();
        info.setProperty("user"."root");
        info.setProperty("password"."root");

        // 4. Get the connection
        Connection connect = driver.connect(url, info);
        System.out.println(connect);

    }
Copy the code

Connection Mode 3

• Replace the Driver with DriverManager (Driver management class, mainly used for JDBC drivers)
• Experience the four basic elements necessary for obtaining connections.

    // Method 3: Use DriverManager (mainly used for JDBC drivers) to replace the Driver
	@Test
    public void testConnection3(a) {
        try {
            //1. The 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. Instantiate Driver
            Class clazz = Class.forName(driverName);
            Driver driver = (Driver) clazz.newInstance();
            //3. Register the driver
            DriverManager.registerDriver(driver);
            //4. Get the connection
            Connection conn = DriverManager.getConnection(url, user, password);
            System.out.println(conn);
        } catch(Exception e) { e.printStackTrace(); }}Copy the code

Connection Mode 4

• Optimization of mode 3, can just load the driver, do not show the registration of the driver

    // Method 4: optimization of method 3, can only load the driver, do not display the registration of the driver
    @Test
    public void testConnection4 (a) throws ClassNotFoundException, SQLException {

        // 1. Provide basic information about the three connections
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "root";

        // 2. Load the Driver
// Class clazz = Class.forName("com.mysql.jdbc.Driver");
        Class.forName("com.mysql.jdbc.Driver");
        /* * static block of code that is executed as the class is loaded

        // Compared with method 3, the following operations can be omitted
// Driver driver = (Driver) clazz.newInstance();
// // 3. Register the driver
// DriverManager.registerDriver(driver);
        // The reason is as follows: In the mysql implementation class, the following operations are declared
// static {
// try {
// java.sql.DriverManager.registerDriver(new com.mysql.jdbc.Driver());
// } catch (SQLException E) {
// throw new RuntimeException("Can't register driver!" );
/ /}
/ /}

        // 3. Obtain the connection
        Connection connect = DriverManager.getConnection(url, user, password);
        System.out.println(connect);

    }
Copy the code

Connection Mode 5 (Final version)

Advantages:

1. Realize the separation of data and code, realize decoupling
2. If configuration file information needs to be modified, repackaging can be avoided

@Test
public void testConnection5 (a) {
    // 1. Load the configuration file
    // Load the jdbc.properties resource through the current class's loader. The default path is under SRC
    InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
    Properties pros = new Properties();
    pros.load(is);
    
    2. Read the configuration file
    String user = props.getProperty("user");
    String password = pros.getProperty("password");
    String url = pros.getProperty("url");
    String driverClass = pros.getProperty("driverClass");
    
    // 3. Load the driver
    Class.forName(driverClass);
    // 4. Get the connection
    Connection connect = DriverManager.getConnection(url, user, password);
    System.out.print(connect);
}
Copy the code

• The configuration file is declared in the SRC directory of the project: jdbc.properties

user=root
password=abc123
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver
Copy the code

Chapter 3: Implement CRUD operations using PreparedStatement

Manipulate and access databases

• The database connection is used to send commands and SQL statements to the database server and to receive the results returned by the database server. A database connection is a Socket connection.

• There are three interfaces in the java.sql package that define different ways to call a database:

  • Statement: An object used to execute a static SQL Statement and return the result it generates.
  • PrepatedStatement: THE SQL statement is precompiled and stored in this object, which can be used to execute the statement efficiently multiple times.
  • CallableStatement: Used to execute SQL stored procedures

Disadvantages of using statements to manipulate tables

• This object is created by calling the createStatement() method of the Connection object. This object is used to execute static SQL statements and returns the results of the execution.
• The Statement interface defines the following methods to execute SQL statements:

Int excuteUpdate(String SQL) : Performs the update operationINSERT,UPDATE,DELETE
ResultSet executeQuery(String sql) : Performs the query operationSELECT
Copy the code

• However, there are drawbacks to manipulating tables using Statement:

  • Problem 1: There are spread-string operations, cumbersome
  • Problem two: SQL injection problems exist

• For Java, to protect against SQL injection, simply replace a Statement with a PreparedStatement(which is an extension of a Statement).

• Example:

package com.atguigu.statement.crud; import org.junit.Test; import java.io.InputStream; import java.lang.reflect.Field; import java.sql.*; import java.util.Properties; import java.util.Scanner; Public class StatementTest {public class StatementTest {public class StatementTest {public class StatementTest; Statement @test public void testLogin2 () {Scanner scan = new Scanner(system.in); System.out.println(" username: "); String userName = scan.next(); System.out.println(" password: "); String password = scan.nextLine(); String sqlStr = "SELECT user.password FROM user_table WHERE user = '" + userName + "' AND password = '" + password + "'"; User user = get(sqlStr, User.class); if (user ! = null) { System.out.println("Login successful"); } else { System.out.println("Login failed, user name or password is wrong!"); Public 
      
        T get(String SQL, Class
       
         clazz) {T T = null; Connection conn = null; Statement st = null; ResultSet rs = null; Try {/ / 1. Load the configuration file InputStream is = StatementTest. Class. GetClassLoader () getResourceAsStream ("
       
      jdbc.properties"); Properties pros = new Properties(); pros.load(is); String user = pros.getProperty("user"); String password = pros.getProperty("password"); String url = pros.getProperty("url"); String driverClass = pros.getProperty("driverClass"); // 3. Load driver class. forName(driverClass); / / 4. Get connected conn = DriverManager. GetConnection (url, user, password); st = conn.createStatement(); rs = st.executeQuery(sql); ResultSetMetaData RSMD = rs.getMetadata (); Int columnCount = rsmd.getColumnCount(); int columnCount = rsmd.getColumnCount(); if (rs.next()) { t = clazz.newInstance(); for (int i = 0; i < columnCount; I ++) {// //1. Obtain the columnName // String columnName = rsmd.getcolumnname (I +1); String columnName = rsmD. getColumnLabel(I + 1); Object columnVal = rs.getobject (columnName); // 2. Field Field = clazz.getDeclaredField(columnName); field.setAccessible(true); field.set(t, columnVal); } return t; } } catch (Exception e) { e.printStackTrace(); } finally {// Close the resource if (rs! = null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (st ! = null) { try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn ! = null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } return null; }}Copy the code

The use of a PreparedStatement

PreparedStatement is introduced

• This can be done by calling the Connection objectpreparedStatement(String sql)The PreparedStatement object () method gets the PreparedStatement object
• The PreparedStatement interface is a subinterface of Statement and represents a precompiled SQL Statement
• The parameters in the SQL statement represented by the PreparedStatement object are marked with question marks (?). The setXxx() method takes two parameters. The first parameter is the index (starting from 1) of the parameter in the SQL statement to be set, and the second parameter is the value of the parameter in the SQL statement to be set

PreparedStatement vs Statement

• Readability and maintainability of code.

• PreparedStatement maximizes performance:

  • DBServer provides performance optimizations for precompiled statements. Because a precompiled statement can be called repeatedly, the statement is cached in the executing code compiled by the DBServer compiler, so the next call does not need to be compiled as long as the same precompiled statement is passed the parameters directly into the compiled statement executing code.
  • In a statement, even though the operation is the same, the data content is different. Therefore, the entire statement itself cannot match, so it does not have the meaning of caching statements. The fact is that no database caches the code executed after compiling ordinary statements. This will compile the incoming statement each time it is executed.
  • (Syntax checking, semantic checking, translation into binary commands, caching)

• A PreparedStatement prevents SQL injection

The advantages of the PreparedStatement

• Solve the problems of Statement string and SQL injection
• A PreparedStatement can manipulate Blob data, but a Statement cannot
• Preparedstatements enable more efficient batch operations

Create, delete, and modify operations using PreparedStatement

• Simple implementation:

    // Add a record to the Customers table
    @Test
    public void testInsert (a) {
        / / PreparedStatementUpdateTest. Class. GetClassLoader () : obtain the class loader (custom class loader is the class system)
// InputStream is = PreparedStatementUpdateTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros = new Properties();
        try {
            pros.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        Connection connect = null;
        try {
            Class.forName(driverClass);
            // 3. Obtain the connection
            connect = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

// System.out.println(connect);

        // 4. Precompile SQL statements and return an instance of PreparedStatement; ? : a placeholder
        String sqlStr = "INSERT INTO customers(name, email, birth)VALUES(? ,? ,?) ";
        PreparedStatement pps = null;
        try {
            pps = connect.prepareStatement(sqlStr);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // 5. Fill in placeholders
        try {
            pps.setString(1."Which zha");
            pps.setString(2."[email protected]");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            java.util.Date date = sdf.parse("1000-01-01");
            pps.setDate(3.new java.sql.Date(date.getTime()));
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        }

        6. Execute the SQL statement
        try {
            pps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(pps ! =null)
                    pps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(connect ! =null)
                    connect.close();
            } catch(SQLException e) { e.printStackTrace(); }}}Copy the code

• Extract the common action class JDBCUtils

    // Modify a record to the Customers table, using the JDBCUtils class
    @Test
    public void testUpdate (a) {
        // 1. Obtain the database connection
        Connection connect = null;
        PreparedStatement pps = null;
        try {
            connect = JDBCUtils.getConnection();
            // 2. Precompile the SQL statement and return an instance of PreparedStatement
            String updateSql = "UPDATE customers SET name = ? WHERE id = ?";
            pps = connect.prepareStatement(updateSql);
            // 3. Populate placeholders
            pps.setObject(1.Mozart);
            // pps.setInt(2, 18);
            pps.setObject(2.18);
            4 / / execution
            pps.execute();

        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. Resource shutdownJDBCUtils.closeResource(connect, pps); }}/ / JDBCUtils class
package com.atguigu.util;

/ * * *@author lv
 * @createThe 2021-06-10 "* /

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.Properties;

/** * Tool class to operate database: */
public class JDBCUtils {
    /** * Get database connection *@return
     * @throws IOException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection (a) throws IOException, ClassNotFoundException, SQLException {

        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros = new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        Connection connect = null;
        Class.forName(driverClass);
        // 3. Obtain the connection
        connect = DriverManager.getConnection(url, user, password);

        return connect;
    }

    /** * Close the resource *@param connect
     * @param pps
     */
    public static void closeResource (Connection connect, Statement pps) {
        if(connect ! =null) {
            try {
                connect.close();
            } catch(SQLException e) { e.printStackTrace(); }}if(pps ! =null) {
            try {
                pps.close();
            } catch(SQLException e) { e.printStackTrace(); }}}}Copy the code

• Based on the JDBCUtils class, enableGeneralization of add, delete and change operation

    // Test the general add, delete, and change method updates
    @Test
    public void testUpdates (a) {

// String sql = "DELETE FROM customers WHERE id = ?" ;
// updates(sql, 3);
        /* * order: is a keyword, cannot be used for table names, can be used to avoid SQL parsing error * */
        String sql = "UPDATE `order` SET order_name = ? WHERE order_id = ?";
        updates(sql, "DD".2);
    }
    // Add, delete, modify
    public void updates (String sqlStr, Object ... args) {

        Connection connect = null;
        PreparedStatement pps = null;
        try {
            // 1. Obtain the database connection
            connect = JDBCUtils.getConnection();
            // 2. Precompile the SQL statement and return an instance of PreparedStatement
            pps = connect.prepareStatement(sqlStr);
            // 3. Populate placeholders
            for (int i = 0; i < args.length; i++) {
                pps.setObject(i + 1, args[i]);
            }
            4 / / execution
            pps.execute();

        } catch (Exception e) {
            // System.out.println(e.getStackTrace());
            e.printStackTrace();
        } finally {
            // 5. Resource shutdownJDBCUtils.closeResource(connect, pps); }}Copy the code

The query operation is implemented using PreparedStatement

    @Test
    public void testQuery1 (a) {
        Connection connect = null;
        PreparedStatement pps = null;
        ResultSet resultSet = null;
        try {
            // Get the connection
            connect = JDBCUtils.getConnection();
            // Preprocess SQL
            String sql = "SELECT id, name, email, birth FROM customers WHERE id = ?";
            pps = connect.prepareStatement(sql);
            pps.setInt(1.1);
            // Execute, and return the result
            resultSet = pps.executeQuery();
            // next() : call the next() method to check if the next line is valid; If valid, this method returns true and the pointer moves down.
            if (resultSet.next()) {
                // Get the value of each field of the current data
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String email = resultSet.getString(3);
                Date birth = resultSet.getDate(4);

// Method 1:
// System.out.println("id = " + id + ", name = " + name);
// Method 2:
// Object[] cus = new Object[]{id, name, email, birth};
// Method 3:
                Customer customer = newCustomer(id, name, email, birth); System.out.println(customer); }}catch (Exception e) {
            e.printStackTrace();
        } finally {
            // Close the resourceJDBCUtils.closeResource(connect, pps, resultSet); }}Copy the code

The ResultSet and ResultSetMetaData

ResultSet

• The query calls the executeQuery() method in PreparedStatement, and the result is a ResultSet object
• A ResultSet object encapsulates the ResultSet of a database operation in the form of a logical table. The ResultSet interface is implemented by the database vendor
• What a ResultSet returns is essentially a table of data. There is a pointer to the first record in the data table.
• A ResultSet maintains a cursor pointing to the current row of data. Initially, the cursor precedes the first row and can be moved to the next row using the Next () method of the ResultSet. Call the next() method to check if the next line is valid. If valid, this method returns true and the pointer moves down. Equivalent to a combination of the hasNext() and next() methods of Iterator.
• When the pointer points to a row, you can obtain the value of each column by calling getXxx(int index) or getXxx(int columnName).
  • For example: getInt(1), getString(“name”)
  • Note: Indexes in the relevant Java apis involved in Java’s interaction with databases all start at 1.
• Common methods of the ResultSet interface:
  • boolean next()
  • getString()

ResultSetMetaData

• Object that can be used to get information about the types and attributes of columns in a ResultSet object
• ResultSetMetaData meta = rs.getMetaData();
  • GetColumnName (int column) : Obtains the name of the specified column
  • GetColumnLabel (int column) : Gets the alias of the specified column
  • GetColumnCount () : Returns the number of columns in the current ResultSet object.
  • GetColumnTypeName (int column) : Retrieves the database-specific type name of the specified column.
  • GetColumnDisplaySize (int column) : indicates the maximum standard width of the specified column, in characters.
  • IsNullable (int column) : Indicates whether a value in a specified column can be null.
  • IsAutoIncrement (int column) : Indicates whether the specified column is automatically numbered so that the column is still read-only.

ResultSet and ResultSetMetaData instances

Things to know:

1. Retrieves the number of columns per row: ResultSetMetaData of the result set
2. Gets the name of each column
3. Assign values to corresponding columns: reflection

    // Test the common query operation for the Customers table: queryForCustomers
    @Test
    public void testQueryForCustomers (a) {
        String sql = "select id, name, email, birth from customers where id = ?";
        Customer rsCustomer = queryForCustomers(sql, 13);
        System.out.println(rsCustomer);
        sql = "select id, name from customers where email = ?";
        Customer customer1 = queryForCustomers(sql, "[email protected]");
        System.out.println(customer1);

    }
    /** * Generic query operations on the Customers table ** problem: * 1. Get the number of columns per row: ResultSetMetaData * 2. Get the name of each column *. 3. Assign the corresponding column: reflection * */
    public Customer queryForCustomers (String sql, Object ... args) {
        Connection connect = null;
        PreparedStatement pps = null;
        ResultSet rs = null;

        try {
            connect = JDBCUtils.getConnection();

            pps = connect.prepareStatement(sql);

            for (int i = 0; i < args.length; i++) {
                pps.setObject(i + 1, args[i]);
            }
            rs = pps.executeQuery();
            ResultSetMetaData retrieves the ResultSetMetaData
            ResultSetMetaData rsmd = rs.getMetaData();
            ResultSetMetaData retrieves the number of columns in the result set
            int columnCount = rsmd.getColumnCount();
            if (rs.next()) {
                Customer customer = new Customer();
                // Process each column of a row of data in the result set
                for (int i = 0; i < columnCount; i++) {
                    Object colVal = rs.getObject(i + 1);
                    // Now is the hardest part
                    // 1. Get column name
                    String columnName = rsmd.getColumnName(i + 1);

                    // 2. Assign the columnName attribute of customer to val -> By reflection
                    Field field = Customer.class.getDeclaredField(columnName);
                    // Make sure the attributes in Customer are accessible
                    field.setAccessible(true);
                    field.set(customer, colVal);
                }
                returncustomer; }}catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connect, pps, rs);
        }

        return null;

    }
Copy the code

Common query operations for different tables (Focus on)

• The use of generic methods
• Application of reflection

1. Returns a record in a data table

package com.atguigu.preparedstatement.crud;

import com.atguigu.bean.Customer;
import com.atguigu.bean.Order;
import com.atguigu.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

/ * * *@author lv
 * @create* * Implement general query operations for different tables in PreparedStatement */
public class PreparedStatementQueryTest {

    // Test a generic query operation for different tables with the generic method getInstance()
    @Test
    public void testGetInstance (a) {
        String sql = "SELECT id, name, email FROM customers WHERE id = ?";
        Customer customer = getInstance(Customer.class, sql, 1);
        System.out.println(customer);

        String sql2 = "select order_id orderId, order_name orderName from `order` where order_id = ?";
        Order order = getInstance(Order.class, sql2, 1);
        System.out.println(order);
    }
    * * Use PreparedStatement to implement general query operations on different tables * * */
    // Generic methods
    public <T> T getInstance (Class
       
         clazz, String sql, Object ... args)
        {

        Connection connect = null;
        PreparedStatement pps = null;
        ResultSet rs = null;
        try {
            connect = JDBCUtils.getConnection();
            pps = connect.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pps.setObject(i + 1, args[i]);
            }
            rs = pps.executeQuery();
            if (rs.next()) {
                T t = clazz.newInstance();
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
                for (int i = 0; i < columnCount; i++) {
                    Object colVal = rs.getObject(i + 1);
                    String colLabel = rsmd.getColumnLabel(i + 1);
                    Field field = clazz.getDeclaredField(colLabel);
                    field.setAccessible(true);
                    field.set(t, colVal);
                }
                returnt; }}catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connect, pps, rs);
        }

        return null; }}Copy the code

2. Returns multiple records in a table: List

    /* * test a generic query operation getForList() * */ that returns a List
    @Test
    public void testGetForList (a) {
        // consumer
        String sql = "SELECT id, name, email FROM customers WHERE id > ?";
        List<Customer> customerList = getForList(Customer.class, sql, 10);
        // System.out.println(customerList);
        // Lambda expressions
        // forEach(Consumer) : Consumer Consumer, no return
        customerList.forEach(System.out::println);
        
        // order
        String sql2 = "select order_id orderId, order_name orderName from `order`";
        List<Order> orderList = getForList(Order.class, sql2);
// System.out.println(order);
        orderList.forEach(System.out::println);
    }
    // A generic query that returns a List
    public <T> List<T> getForList (Class
       
         clazz, String sql, Object ... args)
        {
        Connection connect = null;
        PreparedStatement pps = null;
        ResultSet rs = null;
        try {
            connect = JDBCUtils.getConnection();
            pps = connect.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pps.setObject(i + 1, args[i]);
            }
            rs = pps.executeQuery();

            ArrayList<T> list = new ArrayList<>();

            while (rs.next()) {
                T t = clazz.newInstance();
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();

                for (int i = 0; i < columnCount; i++) {

                    Object colVal = rs.getObject(i + 1);
                    String colLabel = rsmd.getColumnLabel(i + 1);
                    Field field = clazz.getDeclaredField(colLabel);
                    field.setAccessible(true);
                    field.set(t, colVal);
                }

                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connect, pps, rs);
        }

        return null;
    }
Copy the code

Resource Release

• Release a ResultSet, Statement, or Connection.
• A database Connection is a rare resource that must be released immediately after being used up. If the Connection is not closed properly in time, the system will break down.The rule for using a Connection is to create it as late as possible and release it as early as possible.
• You can close it in finally to ensure that the resource is closed even if other code fails.

The JDBC API summary

• Two kinds of thoughts

  • The idea of interface oriented programming JDBC
  • Relational Relational Mapping
    • A data table corresponds to a Java class
    • A record in a table corresponds to an object of a Java class
    • A field in a table corresponds to a property of a Java class

  SQL is written by combining column names with table attribute names. Pay attention to nicknames.

• Two kinds of technology

  • Metadata for JDBC result sets: ResultSetMetaData
    • GetColumnCount ()
    • Get the column alias: getColumnLabel()
  • By reflection, an object of the specified class is created, the specified property is obtained, and the value is assigned

• Query operation flow

Chapter practice

Insert a row of data from the console into the customers table of the database with the following structure:

• pps.executeUpdate(); Returns the number of rows affected in SQL after add, delete, or modify is executed
• The string “2012-09-01”; There is an implicit conversion to the Date type of java.sql.Date

package com.atguigu.exer;

import com.atguigu.util.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Scanner;

/ * * *@author lv
 * @createThe manner of 2021-06-15 * /
public class ExerTest1 {

    // Practice 1: Insert a data item into the customers table
    @Test
    public void testInsert (a) {
        Scanner scan = new Scanner(System.in);
        System.out.print("Please enter user name:");
        String name = scan.next();
        System.out.print("Please enter email address:");
        String email = scan.next();
        System.out.print("Please enter date of birth:");
        String birth = scan.next();

// String name = "lv";
// String email = "[email protected]";
// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
// Date parse = sdf.parse("2012-09-01");
// java.sql.Date birth = new java.sql.Date(parse.getTime());
// String birth = "2012-09-01"; // There is an implicit conversion, java.sql.date
        String sql = "INSERT INTO customers(name, email, birth)VALUES(? ,? ,?) ";
        int updates = updates(sql, name, email, birth);
        if (updates > 0) {
            System.out.println("Success");
        } else {
            System.out.println("Fail");
        }
// try {
//
// } catch (ParseException e) {
// e.printStackTrace();
/ /}
    }
    // Add, delete, modify - General operation:
    public int updates (String sql, Object ... args) {
        Connection connect = null;
        PreparedStatement pps = null;
        try {
            connect = JDBCUtils.getConnection();
            pps = connect.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pps.setObject(i + 1, args[i]);
            }
            // Method 1:
// pps.execute();

            // Method 2: Return the number of rows affected in SQL after add, delete, or modify is executed
            return pps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connect, pps);
        }
        return 0; }}Copy the code

Enter the id number or admission ticket number to query the basic information of students

• “A”.equalsignorecase (next) : Avoid null pointer exceptions

package com.atguigu.exer;

import com.atguigu.bean.ExamStudent;
import com.atguigu.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Scanner;

/ * * *@author lv
 * @createIn 2021-06-15 an * /
public class ExerTest2 {

    // Query student information
    @Test
    public void testSqlQuery (a) {
        System.out.println("Please select the type you want to enter:");
        System.out.println("A: Admission Ticket Number");
        System.out.println("B: ID number");
        System.out.println();
        Scanner scan = new Scanner(System.in);
        String select = scan.next();
        System.out.println(select);
        // "a". EqualsIgnoreCase (next) : avoid null pointer exceptions
        if ("a".equalsIgnoreCase(select)) {
            System.out.println(Please enter your admission ticket number:);
            String examCard = scan.next();
            String sql = "select FlowId flowId, Type type, IDCard idCard, ExamCard examCard, StudentName studentName, Location location, Grade grade from examstudent where ExamCard = ?";
            ExamStudent examStudent = sqlQuerys(ExamStudent.class, sql, examCard);
            System.out.println(examStudent);
        } else if ("b".equalsIgnoreCase(select)) {
            System.out.println(Please enter your ID number:);
            String idCard = scan.next();
            String sql = "select FlowId flowId, Type type, IDCard idCard, ExamCard examCard, StudentName studentName, Location location, Grade grade from examstudent where IDCard = ?";
            ExamStudent examStudent = sqlQuerys(ExamStudent.class, sql, idCard);
            System.out.println(examStudent);
        } else {
            System.out.println("Input error, please re-enter program!"); }}// General query operation
    public <T> T sqlQuerys (Class
       
         clazz, String sql, Object ... args)
        {
        Connection connect = null;
        PreparedStatement pps = null;
        ResultSet rs = null;
        try {
            connect = JDBCUtils.getConnection();
            pps = connect.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pps.setObject(i + 1, args[i]);
            }
            rs = pps.executeQuery();
            if (rs.next()) {
                T t = clazz.newInstance();
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
                for (int i = 0; i < columnCount; i++) {
                    Object colVal = rs.getObject(i + 1);
                    String colLabel = rsmd.getColumnLabel(i + 1);
                    Field field = clazz.getDeclaredField(colLabel);
                    field.setAccessible(true);
                    field.set(t, colVal);
                }
                returnt; }}catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connect, pps, rs);
        }
        return null;
    }
    // Information entry operation
    @Test
    public void testInsert (a) {
        Scanner scan = new Scanner(System.in);
        System.out.println("Please enter grade 4/6:");
        int type = scan.nextInt();
        System.out.println(Please enter your ID card number:);
        String idCard = scan.next();
        System.out.println(Please enter the admission ticket number:);
        String examCard = scan.next();
        System.out.println("Please enter student name:");
        String studentName = scan.next();
        System.out.println("Please enter the field:");
        String location = scan.next();
        System.out.println("Please enter the result:");
        int grade = scan.nextInt();
        String sql = "insert into examstudent(Type, IDCard, ExamCard, StudentName, Location, Grade)values(? ,? ,? ,? ,? ,?) ";
        int updatesCount = updates(sql, type, idCard, examCard, studentName, location, grade);
        if (updatesCount > 0) {
            System.out.println("Information entry successful!");
        } else {
            System.out.println("Information entry failure!"); }}public int updates (String sql, Object ... args) {
        Connection connect = null;
        PreparedStatement pps = null;
        try {
            connect = JDBCUtils.getConnection();
            pps = connect.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pps.setObject(i + 1, args[i]);
            }
            return pps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connect, pps);
        }
        return 0; }}Copy the code

Chapter 4 Manipulating BLOB type fields

MySQL BLOB

• In MySQL, a BLOB is a binary large object, a container that can store a large amount of data. It can hold data of different sizes.
• You must use a PreparedStatement to insert BLOB data because BLOB data cannot be written using string concatenation.
• The four BLOB types of MySQL (they are equivalent except that they differ in the maximum amount of information stored)

type	Size (bytes)
TinyBlob	255
Blob	64K
MediumBlob	16M
LongBlob	4G

• In practice, different BLOB types are defined based on the size of the data that needs to be stored.
• It is important to note that database performance deteriorates if you store too many files.
• Ini = max_allowed_packet=16M; max_allowed_packet=16M; Also note: after modifying the my.ini file, you need to restart the mysql service.

Insert big data types into data tables

    // Insert a field of type Blob into the CUSTOMERS table
    // mysql for insert Blob type volume "1M, need to configure mysql, as follows
    Ini file and add the following configuration parameters: max_allowed_packet=16M
    @Test
    public void testInsert (a) {
        Connection connect = null;
        PreparedStatement pps = null;
        try {
            connect = JDBCUtils.getConnection();
            String sql = "insert into customers(name, email, birth, photo)values(? ,? ,? ,?) ";
            pps = connect.prepareStatement(sql);
            pps.setObject(1."lv");
            pps.setObject(2."[email protected]");
            pps.setObject(3."2012-12-12");
            // new File() : the default root path, the same as SRC
            FileInputStream fis = new FileInputStream(new File("charset_UTF-8.png"));
            pps.setBlob(4, fis);

            int i = pps.executeUpdate();
            if (i > 0) {
                System.out.println("success");
            } else {
                System.out.println("fail"); }}catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{ JDBCUtils.closeResource(connect, pps); }}Copy the code

Read large data types from data tables

    // Query the Blob field in the data table
    @Test
    public void testQuery (a) {
        Connection connect = null;
        PreparedStatement pps = null;
        ResultSet rs = null;
        InputStream is = null;
        FileOutputStream fos = null;
        try {
            connect = JDBCUtils.getConnection();
            String sql = "select id, name, email, birth, photo from customers where id = ?";
            pps = connect.prepareStatement(sql);

            pps.setInt(1.16);
            rs = pps.executeQuery();
            if (rs.next()) {

                // Method 1:
// int id = rs.getInt(1);
// String name = rs.getString(2);
// String email = rs.getString(3);
// Date birth = rs.getDate(4);

                // Method 2:
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date birth = rs.getDate("birth");
                Customer cust = new Customer(id, name, email, birth);
                System.out.println(cust);

                // Download the Blob field and save it locally as a file
                Blob photo = rs.getBlob("photo");
                // InputStream
                is = photo.getBinaryStream();
                // new File("zhuyin.jpg")
                // FileOutputStream
                fos = new FileOutputStream("zhuyin.jpg");
                byte[] buffer = new byte[1024];
                int len;
                while((len = is.read(buffer)) ! = -1) {
                    fos.write(buffer, 0, len); }}}catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            JDBCUtils.closeResource(connect, pps, rs);

            if(is ! =null) {
                try {
                    is.close();
                } catch(IOException e) { e.printStackTrace(); }}if(fos ! =null) {
                try {
                    fos.close();
                } catch(IOException e) { e.printStackTrace(); }}}}Copy the code

Chapter 5 Batch Insert

Batch execute SQL statements

When you need to insert or update records in batches, you can use Java’s batch update mechanism, which allows multiple statements to be submitted to the database for batch processing at once. It is usually more efficient than commit processing alone

JDBC bulk processing statements include the following three methods:

• AddBatch (String) : adds SQL statements or parameters to be processed in batches.
• ExecuteBatch () : Executes batch processing statements;
• ClearBatch (): clears the cached data

There are usually two types of batch execution of SQL statements:

• Batch processing of multiple SQL statements;
• Batch parameter transfer of an SQL statement;

Batch insert method 2: Use PreparedStatement

• Batch insert method 1: Use Statement

    // Batch insert method 2: Use PreparedStatement
    @Test
    public void testBatchInsert1 (a) {
        Connection con = null;
        PreparedStatement pps = null;
        try {
            long start = System.currentTimeMillis();
            con = JDBCUtils.getConnection();
            String sql = "insert into goods(name)values(?) ";
            pps = con.prepareStatement(sql);
            for (int i = 0; i <= 10000; i++) {
                pps.setObject(1."name_" + i);
                pps.execute();
            }
            long end = System.currentTimeMillis();

            System.out.println("times: " + (end - start)); // times: 292330
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{ JDBCUtils.closeResource(con, pps); }}Copy the code

Batch insertion mode 3:

1. Use: addBatch(), executeBatch(), clearBatch()
2. Mysql server disables batch processing by default. We need to pass a parameter to enable batch processing in mysql. ? RewriteBatchedStatements =true after the URL of the configuration file
3. Use the newer mysql driver: mysql-connector-java-5.1.37-bin.jar

    // Batch insert mode 3:
    AddBatch (), executeBatch(), clearBatch() ** 2. The mysql server disables batch processing by default. You need to set a parameter to enable batch processing. *? RewriteBatchedStatements =true write after the URL of the configuration file * * 3. Use the newer mysql driver: mysql-connector-java-5.1.37-bin.jar */
    @Test
    public void testBatchInsert2 (a) {
        Connection con = null;
        PreparedStatement pps = null;
        try {
            long start = System.currentTimeMillis();
            con = JDBCUtils.getConnection();
            String sql = "insert into goods(name)values(?) ";
            pps = con.prepareStatement(sql);
            for (int i = 1; i <= 10000; i++) {
                pps.setObject(1."name_" + i);
                // pps.execute();
                / / 1. Save the SQL
                pps.addBatch();

                if (i % 500= =0) {
                    // 2. Run batch
                    pps.executeBatch();
                    // 3. Delete batchpps.clearBatch(); }}long end = System.currentTimeMillis();

            System.out.println("times: " + (end - start)); // times: 1115

        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{ JDBCUtils.closeResource(con, pps); }}Copy the code

Batch insert mode 4 :(final solution) set the connection to not allow automatic submission of data

• Use Connection setAutoCommit(false)/commit()

    // batch insert mode 4 :(final solution) set the connection does not allow automatic submission of data
    /** * setAutoCommit(false)/commit() ** /
    @Test
    public void testBatchInsert3 (a) {
        Connection con = null;
        PreparedStatement pps = null;
        try {
            long start = System.currentTimeMillis();
            con = JDBCUtils.getConnection();
            // Set not to allow automatic submission of data
            con.setAutoCommit(false);
            String sql = "insert into goods(name)values(?) ";
            pps = con.prepareStatement(sql);
            for (int i = 1; i <= 10000; i++) {
                pps.setObject(1."name_" + i);
                // pps.execute();
                / / 1. Save the SQL
                pps.addBatch();

                if (i % 500= =0) {
                    // 2. Run batch
                    pps.executeBatch();
                    // 3. Delete batchpps.clearBatch(); }}// Finally, commit all data uniformly
            con.commit();

            long end = System.currentTimeMillis();

            System.out.println("times: " + (end - start)); // times: 673

        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{ JDBCUtils.closeResource(con, pps); }}Copy the code

Question:

Persistence of data:

Save data to a power-off storage device for later use

JDBC:

It is a common interface for accessing and operating SQL databases, independent of a specific database management system.

JDBC is a set of APIS provided by SUN. Using this API, you can perform operations on specific databases (obtaining connections, closing connections, DML, DDL, and TCL).

Database driver:

A collection of concrete implementation classes provided by database vendors for the JDBC interface

Ideas of interface oriented programming:

Different database vendors need to provide different implementations for this interface. A collection of different implementations, i.e. drivers for different databases.

JDBCUtils gets and closes connections:

Disadvantages of Statement:

There are drawbacks to using Statement to manipulate tables:

• Problem 1: There are spread-string operations, cumbersome
• Problem two: SQL injection problems exist
• Other questions:

1. Statement cannot operate on Blob type variables
2. When Statement is inserted in batches, it is inefficient

SQL injection is the injection of invalid SQL statements or commands into user input data (e.g. : SELECT user, password FROM user_table WHERE user=’a’ OR 1 =’ AND password =’ OR ‘1’ =’ 1′)

PreparedStatement is introduced

• The PreparedStatement interface is a subinterface of Statement
• It represents a pre-compiled SQL statement
• You can solve SQL injection problems and string problems in Statement

Two kinds of thoughts

• The idea of interface oriented programming
• Relational Relational Mapping
  • A data table corresponds to a Java class
  • A record in a table corresponds to an object of a Java class
  • A field in a table corresponds to a property of a Java class

Two kinds of technology

• Metadata for JDBC result sets: ResultSetMetaData
  • GetColumnCount ()
  • Get the column alias: getColumnLabel()
• By reflection, an object of the specified class is created, the specified property is obtained, and the value is assigned

PreparedStatement operates on variables of type Blob

PreparedStatement enables batch operations

The differences and similarities between PreparedStatement and Statement