Chapter 1: JDBC Overview

Source: www.bilibili.com/video/BV1eJ…

1.1 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 “solidifying” data in memory onto hard disk, and persistence is mostly done through various relational databases.

The main application of persistence is to store in-memory data in a relational database, as well as disk files and XML data files.

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

1.3 the JDBC introduction

Java Database Connectivity (JDBC) is a common interface (set of apis) that is independent of a specific Database management system. It defines a standard Java class library for accessing databases. (java.sql,javax.sql) Use these libraries to easily access database resources in a standard way.
JDBC provides a unified way to access different databases, shielding developers from some of the details.
The goal of JDBC is to enable Java programmers to use JDBC to connect to any database system that provides a JDBC driver, which greatly simplifies and speeds up the development process by eliminating the need for programmers to know too much about the characteristics of a particular database system.

Without JDBC, a Java program accessing a database looks like this:

With JDBC, Java programs access databases like this:

The summary is as follows:

1.4 JDBC Architecture

The JDBC interface (API) consists of two layers:
- Application-oriented apis: Java apis, abstract interfaces, for application developers to use (connect to databases, execute SQL statements, get results).
- Database-oriented API: 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

1.5 JDBC programming procedure

Addendum: ODBC(Open Database Connectivity), is Microsoft launched under the Windows platform. The consumer only needs to call the ODBC API in the program, and the ODBC driver translates the call into a call request to a specific database.

Chapter 2: Getting a database connection

2.1 Element 1: Driver interface implementation class

2.1.1 Driver Interfaces

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.
- Oracle driver: Oracle JDBC. Driver. OracleDriver
- Driver for mySql5.7: com.mysql.jdbc.driver
- MySql8 Driver: com.mysql.cj.jdbc.driver

Copy the above JAR package to a directory in the Java project, and create a new lib folder as a custom. Mysql8.0 driver jar package, of course, you can also directly create Maven project import related Mysql dependencies can be.

Add to Build Path(idea = Add library)

Note: For Dynamic Web projects, place the driver JAR in the Lib directory in the Web-INF directory of the WebContent (some development tools are called WebRoot) directory

2.1.2 Loading and Registering 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
- You usually do not explicitly call the registerDriver() method of the DriverManager class to register an instance of a Driver class, because the Driver classes of the Driver interface contain static code blocks in which, Invokes the DriverManager. RegisterDriver () method to register an instance of itself. MySQL Driver implementation class:

2.2 Element 2: 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 between subprotocols, and the purpose of using subnames is to provide enough information to locate the database. Contains the host name (corresponding to the server IP address), port number, and database name
For example:

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

2.3 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

2.4 Database Connection Examples

2.4.0 Preparations

Use Navicat to import SQL script into mysql database

2. Create a common Java project or Maven project to demonstrate: IDEA

3. If it is a common Java project, create a libs package in the project directory to put the mysql driver JAR package

4, If you are using Maven project, import dependency to POM file:

// Mysql5.7 can be found in Maven's repository<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.11</version>
</dependency>
Copy the code

Mysql5.7 and Mysql8.0 drivers are available as follows

Mysql5.7 JDBC configuration

# Better use a prefix
user=root
password=XXXXXXXXX
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver
Copy the code

Mysql8.0 JDBC configuration

jdbc.driver=Com.mysql.cj.jdbc.driver # note: cj....
jdbc.url=jdbc:mysql://localhost:3306/test? serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false
jdbc.username=root
jdbc.password=XXXXXXXXX
Copy the code

2.4.1 Connection Mode 1

Mysql5.7 demo:

@Test
public void testConnection1(a) {
    try {
        //1. An object that provides the java.sql.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 Properties();
        info.setProperty("user"."root");
        info.setProperty("password"."abc123");

        //4. Call connect() on the driver to obtain the connection
        Connection conn = driver.connect(url, info);
        System.out.println(conn);
    } catch(SQLException e) { e.printStackTrace(); }}Copy the code

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

Mysql8.0 demo

package com.lemon.connection;

import org.junit.Test;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.Properties;


//jdbc.driver=com.mysql.cj.jdbc.Driver
//jdbc.url=jdbc:mysql://localhost:3306/test? serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false

public class ConnectionTest {
    @Test
    public void testConnection1(a) {
        try {
            
            //1. Provide java.sql.Driver interface implementation class object: mysql Driver
            Driver driver = null;
            driver = new com.mysql.cj.jdbc.Driver();

            //2. Provide the url to specify the specific operation data: here we connect to the test database we created
            String url = "jdbc:mysql://localhost:3306/test? serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false";

            //3. Provide the Properties object, specifying the user name and password to encapsulate
            Properties info = new Properties();
            info.setProperty("user"."root");
            info.setProperty("password"."##ajt19980607");

            //4. Call driver connect() to obtain mysql database connection
            Connection conn = driver.connect(url, info);
            System.out.println(conn);//com.mysql.cj.jdbc.ConnectionImpl@3c0f93f1
        } catch(SQLException e) { e.printStackTrace(); }}}Copy the code

2.4.2 Connection Mode 2

//mysql5.7, 8.0 likewise
@Test
public void testConnection2(a) {
    try {
        1. Instantiate the Driver
        String className = "com.mysql.jdbc.Driver";
        Class clazz = Class.forName(className);
        Driver driver = (Driver) clazz.newInstance();

        //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 Properties();
        info.setProperty("user"."root");
        info.setProperty("password"."abc123");

        //4. Call connect() on the driver to obtain the connection
        Connection conn = driver.connect(url, info);
        System.out.println(conn);//com.mysql.jdbc.ConnectionImpl@3c0f93f1

    } catch(Exception e) { e.printStackTrace(); }}Copy the code

Note: In contrast to method 1, reflection instantiation Driver is used here, and third-party database API is not reflected in the code. It embodies the thought of interface – oriented programming.

2.4.3 Connection Mode 3

@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)//com.mysql.cj.jdbc.ConnectionImpl@617faa95
    } catch(Exception e) { e.printStackTrace(); }}Copy the code

Use DriverManager to connect the database. Experience the four basic elements necessary for obtaining connections.

2.4.4 Connection Mode 4

@Test
public void testConnection4(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. Load Driver (① instantiate Driver ② register Driver)
        Class.forName(driverName);


        //Driver driver = (Driver) clazz.newInstance();
        //3. Register the driver
        //DriverManager.registerDriver(driver);
        Can / * comment out the reason of the above code, because the mysql Driver class declarations are: static {try {DriverManager. RegisterDriver (new Driver ()); } catch (SQLException var1) { throw new RuntimeException("Can't register driver!" ); }} * /


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

Note: There is no need to explicitly register the driver. Because static code blocks already exist in the DriverManager source code to implement driver registration.

2.4.5 Connection Mode 5 (Final Version)

@Test
public void testConnection5(a) throws Exception {
    //1. Load the 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("jdbc.user");
    String password = pros.getProperty("jdbc.password");
    String url = pros.getProperty("jdbc.url");
    String driverClass = pros.getProperty("jdbc.driverClass");

    //3. Load the driver
    Class.forName(driverClass);

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

}
Copy the code

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

# 5.7
user=root
password=XXXXXXXXX
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver
# 8.0
jdbc.driverClass=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test? serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false
jdbc.user=root
jdbc.password=XXXXXXXXX
Copy the code

Use the configuration file to save the configuration information, load the configuration file in the code

Benefits of using profiles:

The code and data are separated. If you need to modify the configuration information, you can modify it directly in the configuration file without going into the code
If the configuration information is modified, the recompilation process is omitted.

Chapter 3: Implement CRUD operations using PreparedStatement

3.1 Operating and Accessing the Database

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

3.2 Disadvantages of using Statement 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, the 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
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′)
For Java, to protect against SQL injection, simply replace a Statement with a PreparedStatement(which is an extension of a Statement).
Code demo:

public class StatementTest {

	// The disadvantages of using Statement are that you need to spell SQL statements and there are problems with SQL injection
	@Test
	public void testLogin(a) {
		Scanner scan = new Scanner(System.in);

		System.out.print("Username:");
		String userName = scan.nextLine();
		System.out.print("Password:");
		String password = scan.nextLine();

		// SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '='1' or '1' = '1';
		String sql = "SELECT user,password FROM user_table WHERE user = '" + userName + "' AND password = '" + password
				+ "'";
		User user = get(sql, User.class);
		if(user ! =null) {
			System.out.println("Successful landing!");
		} else {
			System.out.println("Wrong username or password!"); }}// Use Statement to query tables
	public <T> T get(String sql, Class<T> 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);

			// 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 the driver
			Class.forName(driverClass);

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

			st = conn.createStatement();

			rs = st.executeQuery(sql);

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

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

			if (rs.next()) {

				t = clazz.newInstance();

				for (int i = 0; i < columnCount; i++) {
					// //1. Obtain the column name
					// String columnName = rsmd.getColumnName(i+1);

					// 1. Get the column alias
					String columnName = rsmd.getColumnLabel(i + 1);

					// 2. Obtain the data in the corresponding table according to the column name
					Object columnVal = rs.getObject(columnName);

					// 3. Encapsulate the data in the table into an object
					Field field = clazz.getDeclaredField(columnName);
					field.setAccessible(true);
					field.set(t, columnVal);
				}
				returnt; }}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

In conclusion:

3.3 Use of PreparedStatement

3.3.1 PreparedStatement is introduced

A preparedStatement object can be obtained by calling the preparedStatement(String SQL) method of the Connection 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

3.3.2 rainfall distribution on 10-12 PreparedStatement vs the 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
Blob-type fields can also be inserted

3.3.3 Data Type Conversion table for Java and SQL

Java type	SQL type
boolean	BIT
byte	TINYINT
short	SMALLINT
int	INTEGER
long	BIGINT
String	CHAR,VARCHAR,LONGVARCHAR
byte array	BINARY , VAR BINARY
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP

3.3.4 ORM Relational Mapping

ORM relational mapping:

A data table corresponds to a Java class
A record in a table corresponds to a Java object
A field in the table corresponds to a Java property

The name of each field in the table must be the same as the name of an attribute in the Java class. Otherwise, you must alias the field in the table to the name of an attribute in the Java class

The attributes of the fields in the table and the attribute type conversion in Java are shown in the table above

3.3.5 Add, Delete, and modify operations using PreparedStatement

Take the customer table as an example. The ID is the primary key and increases automatically

Take adding a record to the Customers table:

package com.lemon.connection;

import org.junit.Test;

import java.io.InputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Properties;

/ * * *@author lemon
 * @create2022-01-27 19:53 * TO: A handful of green plums for wine */
public class PreparedStatementTest {
    @Test
    public void testInsert(a){

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            //1. Load the configuration file
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties pros = new Properties();
            pros.load(is);

            //2. Read configuration information
            String user = pros.getProperty("jdbc.user");
            String password = pros.getProperty("jdbc.password");
            String url = pros.getProperty("jdbc.url");
            String driverClass = pros.getProperty("jdbc.driverClass");

            //3. Load the driver
            Class.forName(driverClass);

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

            // add a SQL statement
            String sql = "insert into customers(name,email,birth)values(? ,? ,?) ";
            // Precompile the SQL statement and return an instance of PreparedStatement
            ps = conn.prepareStatement(sql);
            // Populate the placeholders
            ps.setString(1."Sun Wukong");
            ps.setString(2."[email protected]");

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            java.util.Date date = sdf.parse("2008-01-01");
            ps.setDate(3.new Date(date.getTime()));

            //6. Execute SQL statement
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            // Close the resource
            try {
                if(ps ! =null) { ps.close(); }}catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(conn ! =null){ conn.close(); }}catch(SQLException e) { e.printStackTrace(); }}}}Copy the code

View database result:

3.3.6 Package: JDBCUtils Tool Class (V1)

public class JDBCUtils {
    // Get the connection
    public static Connection getConnection(a) throws Exception{
        //1. Load the 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("jdbc.user");
        String password = pros.getProperty("jdbc.password");
        String url = pros.getProperty("jdbc.url");
        String driverClass = pros.getProperty("jdbc.driverClass");

        //3. Load the driver
        Class.forName(driverClass);

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

    }

    // Close the connection
    public static void closeResource(Connection conn, PreparedStatement ps){
        try {
            if(ps ! =null) { ps.close(); }}catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn ! =null){ conn.close(); }}catch(SQLException e) { e.printStackTrace(); }}}Copy the code

3.3.7 General add, Delete, and modify operations

// General add, delete, modify operations (reflect 1: add, delete, modify; Embodiment 2: For different tables)
public void update(String sql,Object ... args){  // deformable parameter
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        //1. Obtain the database connection
        conn = JDBCUtils.getConnection();
        //2. Get an instance of PreparedStatement (or: precompiled SQL statement)
        ps = conn.prepareStatement(sql);
        //3. Populate placeholders
        for(int i = 0; i < args.length; i++){ ps.setObject(i +1, args[i]);
        }
        //4. Execute the SQL statement
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        //5. Close the resourceJDBCUtils.closeResource(conn, ps); }}Copy the code

Verify: Delete the record with ID 1 from the customers table

@Test
public void testUpdate(a){
    String sql = "delete from customers where id = ?";  // If the table name is a keyword, it should be marked with ' '
    update(sql,1);
}
Copy the code

3.3.8 Performing a query in PreparedStatement – Returns an object

Query the record whose ID =3 and encapsulate it as a javaBean

public class CustomerForQuery {

    @Test
    public void testQuery1(a){

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            // 1. Obtain the database connection
            conn = JDBCUtils.getConnection();

            // 2. Precompile SQL statements to obtain the PreparedStatement object
            String sql = "select id,name,email,birth from customers where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1.3);
            

            //3. Execute executeQuery() to obtain the ResultSet: ResultSet: this ResultSet does not contain column names
            rs = ps.executeQuery();

            //4
            if (rs.next()) { // Determine if the next entry in the result set has data, like next, where true means the pointer moves down
                // Get column values
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String email = rs.getString(3);
                Date birth = rs.getDate(4);

                // Encapsulate the data into a Javabean
                Customer cus = newCustomer(id,name,email,birth); System.out.println(cus); }}catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 7. Close resources
            JDBCUtils.closeResource(conn, ps, rs);// Need to reload}}}Copy the code

The Customer class

package com.lemon.bean;

import java.util.Date;

/ * * *@author lemon
 * @create2022-01-27 21:32 * TO: A handful of green plums for wine */
public class Customer {
    //ORM relational mapping
    // Make sure it matches the name of the field in the database, otherwise the SQL statement uses an alias
    private int id;
    private String name;
    private String email;
    private Date birth;

    public Customer(a) {}public Customer(int id, String name, String email, Date birth) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }

    public int getId(a) {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName(a) {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail(a) {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirth(a) {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString(a) {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\' ' +
                ", email='" + email + '\' ' +
                ", birth=" + birth +
                '} '; }}Copy the code

Results:

3.3.9 Generic query operation against the Customers table — returns a record

It’s basically the difference between if and while

public Customer queryForCustomers(String sql, Object... args){

    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        // 1. Obtain the database connection
        conn = JDBCUtils.getConnection();

        // 2. Precompile SQL statements to obtain the PreparedStatement object
        ps = conn.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }

        //3. Execute executeQuery() to obtain the ResultSet: ResultSet: this ResultSet does not contain column names
        rs = ps.executeQuery();

        // get the metadata of the result set
        ResultSetMetaData rsmd = rs.getMetaData();
        // To get the column number, we need to know how many columns there are
        int columnCount = rsmd.getColumnCount();

        //5
        if (rs.next()) { // Determine if the next entry in the result set has data, like next, where true means the pointer moves down

            Customer cus = new Customer();// Create an object with an empty parameter constructor,
            for (int i = 0; i < columnCount; i++) {// Iterate over each column

                // Get column values
                Object columnVal = rs.getObject(i + 1);
                // Get the column alias: The alias of the column, using the class attribute name if no alias is set
                // You can also use getColumnName, but this method does not get the alias, which is set to the field property name
                String columnLabel = rsmd.getColumnLabel(i + 1);

                // 6.2 Using reflection, assign values to the corresponding properties of the object. The column name (alias, to be exact) is the name of the property in Java.
                Field field = Customer.class.getDeclaredField(columnLabel);//clazz=Customer.class
                field.setAccessible(true);
                field.set(cus, columnVal);
            }
            returncus; }}catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 7. Close resources
        JDBCUtils.closeResource(conn, ps, rs);
    }
    return null;
}
Copy the code

Testing:

@Test
public void testQuery(a){
    String sql = "select id,name,email,birth from customers where id = ?"; // Alias the field
    Customer customer = queryForCustomers(sql,3);
    System.out.println(customer);

}
Copy the code