JDBC addition, deletion, query and modification of Java

preface

JDBC is not only the specification of Java operating database, but also the core foundation. The principle of the future framework also encapsulates JDBC and uses reflection, annotation and design patterns to complete simplification.

1, JDBC architecture

  • JDBC interface (API) includes two levels:
  • Application oriented API: Java API, abstract interface, for application developers to use (connect to the database, execute SQL statements, and obtain results).
  • Database oriented API: Java Driver API for developers to develop database drivers.

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

How Java operates the database is as follows:

2, JDBC programming steps

3, JDBC connection

package com.xhu.java.jdbc;

import com.mysql.jdbc.Driver;
import org.junit.Test;

import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * Please turn on the service
 */
public class TestConnection {
    @Test
    public void testConn1() {
        //Set the properties file to be logged in
        Properties pro = new Properties();
        pro.setProperty("user", "root");
        pro.setProperty("password", "lls");
        //Get connection
        try (Connection conn = new Driver().connect("jdbc:mysql://localhost:3306/test", pro);) {
            System.out.println(conn);
            if (conn != null)
                conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Test
    public void testConn2() throws Exception {
        //Shielding specific third-party implementation, using reflection
        //1. Get class object
        Class driverC = Class.forName("com.mysql.jdbc.Driver");
        //2. Set the attribute file to be logged in
        Properties pro = new Properties();
        pro.setProperty("user", "root");
        pro.setProperty("password", "lls");
        //3. Get the constructor through reflection
        Constructor dc = driverC.getDeclaredConstructor();
        //4. Instance the object through the obtained constructor
        java.sql.Driver driver = (Driver) dc.newInstance();
        //5. Obtain the connection according to the object of the instance
        Connection conn = driver.connect("jdbc:mysql://localhost:3306/test", pro);

        System.out.println(conn);
        if (conn != null)
            conn.close();
    }

    @Test
    public void testConn3() throws Exception {
        //Using DriverManager
        //1. Get class object
        Class driverC = Class.forName("com.mysql.jdbc.Driver");
        //2. Set the attribute file to be logged in
        Properties pro = new Properties();
        pro.setProperty("user", "root");
        pro.setProperty("password", "lls");
        //3. Get the constructor through reflection
        Constructor dc = driverC.getDeclaredConstructor();
        //4. Instance the object through the obtained constructor
        java.sql.Driver driver = (Driver) dc.newInstance();
        //5. Register driver
        DriverManager.registerDriver(driver);
        //6. Use the registered manager to obtain the connection
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", pro);
        System.out.println(conn);
        conn.close();
    }

    @Test
    public void testConn4() throws Exception {
        //Using DriverManager, MySQL provides static code blocks to register drivers
        //1. Load the class. At this time, the JVM loads the class, links and initializes. During initialization, the static code block and static assignment statement are combined into the clinit() method and executed.
        /*static {
            try {
                DriverManager.registerDriver(new Driver());
            } catch (SQLException var1) {
                throw new RuntimeException("Can't register driver!");
            }
        }*/
        Class.forName("com.mysql.jdbc.Driver");
        //2. Set the attribute file to be logged in
        Properties pro = new Properties();
        pro.setProperty("user", "root");
        pro.setProperty("password", "lls");
        //3. Use the registered manager to get the connection
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", pro);
        System.out.println(conn);
        conn.close();
    }

    @Test
    public void testConn5() throws Exception {
        //Instead of writing code, let the code read the configuration file
        //1) In this way, you don't have to recompile the Java file, just change the configuration file. 2) Code and data are separated, and the coupling is low (non hard coding).
        //1. Create a node stream for the target file
        InputStream is = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        //2. Use Properties to read in the file
        Properties pro = new Properties();
        pro.load(is);
        is.close();
        //3. Get value in properties
        String driverClass = pro.getProperty("driverClass");
        String url = pro.getProperty("url");
        String user = pro.getProperty("user");
        String password = pro.getProperty("password");
        //4. Get the Class object for reflection operation.
        Class driverC = Class.forName(driverClass);
        //5. Use the registered manager to get the connection
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
        conn.close();
    }
}

4, PreparedStatement implements CRUD operations

1. Operating and accessing databases

  • The database connection is used to send commands and SQL statements to the database server and accept the results returned by the database server. In fact, a database connection is a Socket connection.

  • In the java.sql package, there are three interfaces that define different ways to call the database:

    • Statement: an object used to execute a static SQL statement and return the results generated by it. SQL injection problems will occur.
    • PrepatedStatement: the SQL statement is precompiled and stored in this object, which can be used multiple times to execute the statement efficiently.
    • CallableStatement: used to execute SQL stored procedures

2,CRUD

package com.xhu.java.jdbc;

import org.junit.Test;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Properties;

public class TestCRUD {
    @Test
    public void test_JDBCUtils() {
        //The alias must correspond to the property name in the POJO class before you can inject data with reflection.
        String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order`";
        List<Order> orders = JDBCUtils.getMultiInstance(Order.class, sql);
        orders.forEach(System.out::println);
    }

    @Test
    public void test_getMultiInstance() throws Exception {
        String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order`";
        List<Order> orders = getMultiInstance(Order.class, sql);
        System.out.println(Arrays.toString(orders.toArray()));
        orders.forEach(System.out::println);
    }

    /**
     * @param c
     * @param sql
     * @param args
     * @param <T>
     * @return
     * @throws Exception
     * @function Get multiple records
     */
    public <T> List<T> getMultiInstance(Class<T> c, String sql, Object... args) throws Exception {
        //1. Get connection
        Connection conn = getConn();
        //2. Get preprocessed SQL object
        PreparedStatement ps = conn.prepareStatement(sql);
        //3. Placeholder assignment
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        //4. Execute SQL
        ResultSet res = ps.executeQuery();
        //5. Processing result set
        /**
         * @Reflect
         * res Encapsulate the field data of the queried data, i.e. metadata information, in meta data
         * Get the metadata, and then get the number of columns and field names of the query
         * Get the corresponding value of the field through res
         * The corresponding value is dynamically injected through reflection + field name
         */
        List<T> resList = new ArrayList<>();
        while (res.next()) {
            ResultSetMetaData metaData = res.getMetaData();
            int cCount = metaData.getColumnCount();
            T t = c.getConstructor(null).newInstance();
            //class objects required for reflection injection
            Class cusC = t.getClass();
            for (int i = 0; i < cCount; i++) {
                //Get field name
                String columnName = metaData.getColumnLabel(i + 1);//Get the POJO class corresponding to the alias. If there is no alias, it is the original name
                Object columnValue = res.getObject(i + 1);
                //Reflection enables dynamic injection of data
                //Get fields with arbitrary permissions
                Field field = cusC.getDeclaredField(columnName);
                //Get private permission
                field.setAccessible(true);
                //Set attribute value
                field.set(t, columnValue);
            }
            resList.add(t);
        }

        //6. Close resources
        closeQuery(ps, conn, res);
        //7. Return query results
        return resList;
    }

    @Test
    public void test_getInstance() throws Exception {
        String sql = "select id,name,email,birth from customers where id = ?";
        System.out.println(getInstance(Customer.class, sql, 1));
    }

    /**
     * @param c
     * @param sql
     * @param args
     * @param <T>
     * @return T
     * @throws Exception
     * @function General query of various classes
     */
    public <T> T getInstance(Class<T> c, String sql, Object... args) throws Exception {
        //1. Get connection
        Connection conn = getConn();
        //2. Get preprocessed SQL object
        PreparedStatement ps = conn.prepareStatement(sql);
        //3. Placeholder assignment
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        //4. Execute SQL
        ResultSet res = ps.executeQuery();
        //5. Processing result set
        /**
         * @Reflect
         * res Encapsulate the field data of the queried data, i.e. metadata information, in meta data
         * Get the metadata, and then get the number of columns and field names of the query
         * Get the corresponding value of the field through res
         * The corresponding value is dynamically injected through reflection + field name
         */
        T t = null;
        if (res.next()) {
            ResultSetMetaData metaData = res.getMetaData();
            int cCount = metaData.getColumnCount();
            t = c.getConstructor(null).newInstance();
            //class objects required for reflection injection
            Class cusC = t.getClass();
            for (int i = 0; i < cCount; i++) {
                //Get field name
                String columnName = metaData.getColumnLabel(i + 1);//Get the POJO class corresponding to the alias. If there is no alias, it is the original name
                Object columnValue = res.getObject(i + 1);
                //Reflection enables dynamic injection of data
                String setMethodName = "set" + (char) (columnName.charAt(0) - 32) + columnName.substring(1);
                String getMethodName = "get" + (char) (columnName.charAt(0) - 32) + columnName.substring(1);
                Method method = cusC.getDeclaredMethod(setMethodName, cusC.getDeclaredMethod(getMethodName, null).getReturnType());
                method.invoke(t, columnValue);
                /**
                 * Mode II
                 * //Get fields with arbitrary permissions
                 * Field field = cusC.getDeclaredField(columnName);
                 * //Get private permission
                 * field.setAccessible(true);
                 * //Set attribute value
                 * field.set(cus, columnName);
                 */

            }
        }
        //6. Close resources
        closeQuery(ps, conn, res);
        //7. Return query results
        return t;
    }

    @Test
    public void selectBySQL() throws Exception {
        //Check by id
        String sql = "select id,name,email,birth from customers where id = ?";
        Customer customer = selectCommon(sql, 1);
        System.out.println(customer);
        //Check by name
        String sql2 = "select id,name,email,birth from customers where name = ?";
        customer = selectCommon(sql2, "Jay Chou");
        System.out.println(customer);
    }

    /**
     * @param sql
     * @param args
     * @return Customer class
     * @throws Exception
     * @function General operation of a class
     */
    public Customer selectCommon(String sql, Object... args) throws Exception {
        //1. Get connection
        Connection conn = getConn();
        //2. Get preprocessed SQL object
        PreparedStatement ps = conn.prepareStatement(sql);
        //3. Placeholder assignment
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        //4. Execute SQL
        ResultSet res = ps.executeQuery();
        //5. Processing result set
        /**
         * @Reflect
         * res Encapsulate the field data of the queried data, i.e. metadata information, in meta data
         * Get the metadata, and then get the number of columns and field names of the query
         * Get the corresponding value of the field through res
         * The corresponding value is dynamically injected through reflection + field name
         */
        Customer cus = null;
        if (res.next()) {
            System.out.println("There are results");
            ResultSetMetaData metaData = res.getMetaData();
            int cCount = metaData.getColumnCount();
            cus = new Customer();
            //class objects required for reflection injection
            Class cusC = cus.getClass();
            for (int i = 0; i < cCount; i++) {
                //Get field name
                String columnName = metaData.getColumnLabel(i + 1);//Get the POJO class corresponding to the alias. If there is no alias, it is the original name
                Object columnValue = res.getObject(i + 1);
                //Reflection enables dynamic injection of data
                String setMethodName = "set" + (char) (columnName.charAt(0) - 32) + columnName.substring(1);
                String getMethodName = "get" + (char) (columnName.charAt(0) - 32) + columnName.substring(1);
                Method method = cusC.getDeclaredMethod(setMethodName, cusC.getDeclaredMethod(getMethodName, null).getReturnType());
                method.invoke(cus, columnValue);
                /**
                 * Mode II
                 * //Get fields with arbitrary permissions
                 * Field field = cusC.getDeclaredField(columnName);
                 * //Get private permission
                 * field.setAccessible(true);
                 * //Set attribute value
                 * field.set(cus, columnName);
                 */

            }
        }
        //6. Close resources
        closeQuery(ps, conn, res);
        //7. Return query results
        return cus;
    }

    @Test
    public void select() throws Exception {
        //1. Get connection
        Connection conn = getConn();
        //2. Get the object of precompiled SQL
        String sql = "select * from customers where id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        //3. Assignment
        ps.setObject(1, 1);
        //4. Query results obtained by execution
        ResultSet res = ps.executeQuery();
        //5. Treatment results
        /**
         * ResultSet The next method of the object is used to judge whether there is a next value. If so, move the pointer down and return true. Otherwise, return false.
         * Iterator The function of the hasNext method of the object is to simply judge whether there is a next one. If there is, it returns true; otherwise, it returns false. The next method moves the pointer down and gets the element.
         */
        Customer cus = null;
        if (res.next()) {
            int id = res.getInt(1);
            String name = res.getString(2);
            String email = res.getString(3);
            Date birth = res.getDate(4);
            cus = new Customer(id, name, email, birth);
        }
        System.out.println(cus);
        //6. Close resources
        closeQuery(ps, conn, res);
    }

    /**
     * @param ps
     * @param conn
     * @param res
     * @function Close the connection, preprocess the SQL, and obtain the three stream resources from the result set
     */
    private void closeQuery(PreparedStatement ps, Connection conn, ResultSet res) {
        close(ps, conn);
        try {
            if (res != null) res.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Test
    public void insert() {
        //1. Get connection
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConn();
            //2. Get a precompiled operation SQL object PreparedStatement by connecting + SQL
            String sql = "insert into customers(name,email,birth) values(?,?,?)";//? As placeholder
            ps = conn.prepareStatement(sql);
            //3. Set values for placeholders in precompiled SQL
            ps.setString(1, "lls");
            ps.setString(2, "lls@qq.com");
            ps.setDate(3, new Date(new SimpleDateFormat("yyyy-MM-dd").parse("1998-8-8").getTime()));
            //4. Execute SQL
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5. Close the connection
            close(ps, conn);
        }
    }

    @Test
    public void update() throws Exception {
        //1. Get database connection
        Connection conn = getConn();
        //2. Get the object of SQL operation
        String sql = "update customers set name=? where id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        //3. Assign values to placeholders
        ps.setString(1, "LLS");
        ps.setInt(2, 19);
        //4. Execute SQL
        ps.execute();
        //5. Close resources
        close(ps, conn);
    }

    private Connection getConn() throws Exception {
        //Instead of writing code, let the code read the configuration file
        //1) In this way, you don't have to recompile the Java file, just change the configuration file. 2) Code and data are separated, and the coupling is low (non hard coding).
        //1. Create a node stream for the target file
        InputStream is = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        //2. Use Properties to read in the file
        Properties pro = new Properties();
        pro.load(is);
        is.close();
        //3. Get value in properties
        String driverClass = pro.getProperty("driverClass");
        String url = pro.getProperty("url");
        String user = pro.getProperty("user");
        String password = pro.getProperty("password");
        //4. Get the Class object for reflection operation.
        Class driverC = Class.forName(driverClass);
        //5. Use the registered manager to get the connection
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }

    private void close(PreparedStatement ps, Connection conn) {
        try {
            if (ps != null) ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (conn != null) conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Test
    public void testUpdate() {
        Object[] args = new Object[]{"lls", 19};
        String sql = "update customers set name=? where id = ?";
        JDBCUtils.update(sql, args);
    }
}

3,JDBCUtils

package com.xhu.java.jdbc;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @author lls
 * @version 1.0
 * @describle Database operation tool class
 * @date 2021-11-27
 * @method connection, insert, ...
 */
/**
 * What are the benefits of using PreparedStatement?
 * 1.SQL Injection problem: when the object is obtained, the incoming SQL statement is logically precompiled, and the logic will not be changed when injecting data, so as to prevent SQL injection problem.
 * 2.The efficiency of batch inserting data. When batch inserting data, the object has performed syntax verification on the SQL during precompiling, and then the object will not be verified if it executes the equivalent SQL. However, if an ordinary Statement does not execute an SQL, it will have to recompile and verify the syntax once.
 * 3.Write binary problem to the database. The object can write binary files by assigning IO streams to placeholders.
 */
/*
 * ORM object relational mapping
 * A data table corresponds to a java class
 * A record in the table corresponds to an object of the java class
 * A field in the table corresponds to an attribute of the java class
 *
 */
public class JDBCUtils {
    /**
     * @param c
     * @param sql
     * @param args
     * @param <T>
     * @return
     * @throws Exception
     * @function Get multiple records, common to all types
     */
    public static <T> List<T> getMultiInstance(Class<T> c, String sql, Object... args) {
        //1. Get connection
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet res = null;
        //Return results
        List<T> resList = new ArrayList<>();
        try {
            conn = getConn();
            //2. Get preprocessed SQL object
            ps = conn.prepareStatement(sql);
            //3. Placeholder assignment
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            //4. Execute SQL
            res = ps.executeQuery();
            //5. Processing result set
            /**
             * @Reflect
             * res Encapsulate the field data of the queried data, i.e. metadata information, in meta data
             * Get the metadata, and then get the number of columns and field names of the query
             * Get the corresponding value of the field through res
             * The corresponding value is dynamically injected through reflection + field name
             */

            while (res.next()) {
                ResultSetMetaData metaData = res.getMetaData();
                int cCount = metaData.getColumnCount();
                T t = c.getConstructor(null).newInstance();
                //class objects required for reflection injection
                Class cusC = t.getClass();
                for (int i = 0; i < cCount; i++) {
                    //Get field name
                    String columnName = metaData.getColumnLabel(i + 1);//Get the POJO class corresponding to the alias. If there is no alias, it is the original name
                    Object columnValue = res.getObject(i + 1);
                    //Reflection enables dynamic injection of data
                    //Get fields with arbitrary permissions
                    Field field = cusC.getDeclaredField(columnName);
                    //Get private permission
                    field.setAccessible(true);
                    //Set attribute value
                    field.set(t, columnValue);
                }
                resList.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        //6. Close resources
        closeQuery(ps, conn, res);
        //7. Return query results
        return resList;
    }

    /**
     * @param sql  SQL statement to execute
     * @param args SQL Parameters to populate in
     * @function Unified operation of addition, deletion and modification
     */
    public static void update(String sql, Object... args) {
        //1. Get connection
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConn();
            //2. Get a precompiled operation SQL object PreparedStatement by connecting + SQL
            ps = conn.prepareStatement(sql);
            //3. Set values for placeholders in precompiled SQL
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            //4. Execute SQL
            ps.execute();
        } catch (Exception e) {
            System.out.println("Insert exception");
            e.printStackTrace();
        } finally {
            //5. Close the connection
            close(ps, conn);
        }
    }

    /**
     * @param c An object instance corresponds to a record in the database
     * @function insert data
     */
    public static void insert(Customer c) {
        //1. Get connection
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConn();
            //2. Get a precompiled operation SQL object PreparedStatement by connecting + SQL
            String sql = "insert into customers (`name`,email,birth) values(?,?,?)";//? As placeholder
            ps = conn.prepareStatement(sql);
            //3. Set values for placeholders in precompiled SQL
            ps.setString(1, c.getName());
            ps.setString(2, c.getEmail());
            ps.setDate(3, c.getBirth());
            //4. Execute SQL
            ps.execute();
        } catch (Exception e) {
            System.out.println("Insert exception");
            e.printStackTrace();
        } finally {
            //5. Close the connection
            close(ps, conn);
        }
    }

    /**
     * @return Database connection
     * @throws Exception
     * @function Get database connection
     */
    private static Connection getConn() throws Exception {
        //Instead of writing code, let the code read the configuration file
        //1) In this way, you don't have to recompile the Java file, just change the configuration file. 2) Code and data are separated, and the coupling is low (non hard coding).
        //1. Create a node stream for the target file
        InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        //2. Use Properties to read in the file
        Properties pro = new Properties();
        pro.load(is);
        is.close();
        //3. Get value in properties
        String driverClass = pro.getProperty("driverClass");
        String url = pro.getProperty("url");
        String user = pro.getProperty("user");
        String password = pro.getProperty("password");
        //4. Get the Class object for reflection operation.
        Class driverC = Class.forName(driverClass);
        //5. Use the registered manager to get the connection
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }

    /**
     * @param ps
     * @param conn
     */
    private static void close(PreparedStatement ps, Connection conn) {
        try {
            if (ps != null) ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (conn != null) conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * @param ps
     * @param conn
     * @param res
     * @function Close the connection, preprocess the SQL, and obtain the three stream resources from the result set
     */
    private static void closeQuery(PreparedStatement ps, Connection conn, ResultSet res) {
        close(ps, conn);
        try {
            if (res != null) res.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

4. Data type conversion table corresponding to Java and SQL

Java typeSQL type
booleanBIT
byteTINYINT
shortSMALLINT
intINTEGER
longBIGINT
StringCHAR,VARCHAR,LONGVARCHAR
byte arrayBINARY , VAR BINARY
java.sql.DateDATE
java.sql.TimeTIME
java.sql.TimestampTIMESTAMP

5. JDBC API summary

  • Two thoughts

    • The idea of interface oriented programming

    • ORM (object relational mapping)

      • A data table corresponds to a java class
      • A record in the table corresponds to an object of the java class
      • A field in the table corresponds to an attribute of the java class

    sql needs to be written in combination with column names and table attribute names. Note the alias.

  • Two technologies

    • Metadata of JDBC result set: ResultSetMetaData
      • Get the number of columns: getColumnCount()
      • Get alias of column: getColumnLabel()
    • Through reflection, create the object of the specified class, obtain the specified attribute and assign a value

summary

1) JDBC introduction and connection.
2) Universal CRUD for JDBC.

reference

[1] JDBC Silicon Valley

Tags: JDBC reflection Driver

Posted on Sun, 28 Nov 2021 02:37:32 -0500 by fukas