JDBC

The concept of JDBC

target

1. Learn the concepts of JDBC
2. Learning the Role of JDBC

The Way of Client Operating MySQL Database

1. Use DOS command line mode
2. Use third-party clients to access MySQL: SQLyog, Navicat, SQLWave, MyDB Studio, EMS SQL Manager for MySQL
3. Accessing MySQL database through Java, what we're going to learn today

What is JDBC

Java Data Base Connectivity: Java database connection, JDBC is the specification of Java operating database

The Role of JDBC

JDBC enables Java to manipulate databases

The Origin of JDBC

1. Direct Code Writing Operates Database
   
   The problems of directly writing code to operate database are as follows:

   1. I don't know how to operate MySQL database and how to parse it.
   2. Code is tedious and difficult to write.
   3. Other databases, such as MySQL and Oracle, operate and parse differently. Each database has to write a set of code.
   4. Other databases such as MySQL and Oracle have trouble switching to each other

2. JDBC specification defines interfaces, and the specific implementation is implemented by major database vendors.
   JDBC is the Standard Specification for Java to access databases. How to really operate the database also needs specific implementation classes, that is, database driver. Each database manufacturer compiles its own database driver according to the communication format of its own database. So we just need to call the method in the JDBC interface. Database drivers are provided by database vendors.
   

Benefits of JDBC

1. We just need to call the method in the JDBC interface. It's easy to use.
2. Using the same set of Java code, you can access other JDBC-supported databases with minor modifications

Summary

1. Name the concept of JDBC?

   JAVA database connection, JDBC is the specification of Java operating database (interface)
   

2. Name the role of JDBC?

   JDBC enables Java to manipulate databases
   

Introduction of JDBC Core API

target

Learning the Four Core Objects of JDBC

Packages that JDBC will use

1. Java.sql: JDBC's basic package for accessing databases, such as java.sql.Connection
2. javax.sql: JDBC Extension Package for Accessing Database
3. The drive of database is realized by major database manufacturers. For example: MySQL driver: com.mysql.jdbc.Driver

Four core objects of JDBC

These classes are in the java.sql package

1. Driver Manager: Used to Register Drivers
2. Connection: Represents a connection to a database
3. Statement: Object to execute SQL statements
4. ResultSet: ResultSet or a virtual table
   

Steps of JDBC accessing database

1. Register drivers through DriverManager
2. Get Connection
3. Through Statement, the SQL statements we write are shipped to the database for execution.
4. ResultSet Returns the result set

Summary

Four core objects of JDBC?

Driver Manager: Register Driver

Connection: Connection between Java programs and databases (bridge)

Statement: Objects that execute SQL statements (vans)

ResultSet: ResultSet, which saves the queried data

JDBC registration driver

Our Java programs need to be database-driven to connect to the database, so we need to register the driver.

target

Learn to import mysql-driven Jar packages

Learning JDBC Register Database Driver

You need to import the driver's Jar package before registering the driver

Import Driver Jar Packet

Registration Driver

Our Java programs need to be database-driven to connect to the database, so we need to register the driver.
The entry class of MySQL driver is com.mysql.jdbc.Driver.

Introduction to API

The java.sql.DriverManager class is used to register drivers. Provide the following method registration driver

static void registerDriver(Driver driver) 
Register the given driver with DriverManager.

Use steps

1. DriverManager. registerDriver (driver object); just pass in the corresponding parameters.

Case code

public class Demo01 {
	public static void main(String[] args) throws Exception {
		// Registration Driver
		DriverManager.registerDriver(new com.mysql.jdbc.Driver());
	}
}

By querying the source code of com.mysql.jdbc.Driver, we find that the Driver class "actively" registers itself.

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    static {
        try {
            // Self-registration
            java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
        }
    }
    public Driver() throws SQLException {
    }
}

Use Class.forName("com.mysql.jdbc.Driver"); load the driver so that the driver registers only once

public class Demo01 {
	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver"); // Later, you can write the "com.mysql.jdbc.Driver" string in the file.
	}
}

Demo: Class.forName("package name. class name"); will walk the static code block of this class

Usually we use Class.forName() load driver for development. Class.forName("com.mysql.jdbc.Driver"); walks through the static code block of the Driver class. Register a drive in a static block of code.

Summary

1. Import mysql driver Jar package

2. Register database drivers through JDBC?

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

Get Connections and Statement s

Connection represents the connection between Java programs and databases. Only when you get Connection can you operate the database.

target

Learning JDBC to Get Database Connections

Learning to acquire Statement objects

Introduction to API

There are the following methods in the java.sql.DriverManager class to get the database connection

static Connection getConnection(String url, String user, String password) 
Connect to the given database URL and return the connection. 

Description of parameters

1. String url: The URL to connect to the database to indicate where to connect to the database
2. String user: database account
3. String password: database password

URL Address Format for Connecting to Database: Protocol Name: Subprotocol: //Server Name or IP Address: Port Number/Database Name

MySQL: jdbc:mysql://localhost:3306/day16
If it is a local server and the port number is the default 3306, it can be abbreviated as: jdbc: mysql://day16

Matters needing attention

Use steps

1. Registration Driver
2. Call method: DriverManager.getConnection(url, user, password); just pass in the corresponding parameters.

Case code

public class Demo01 {
	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		// Connect to MySQL
		// URL: The URL to connect to the database
		// user: database account
		// Password: The password of the database
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day24", "root", "root");
		System.out.println(conn);
	}
}

Case effect

1. Successful connection

2. Connection failure

Summary

1. Which API does JDBC use to get database connections?

   In the DriverManager class
    Connection getConnection (database URL, database account, database password);
   

2. Writing the URL to connect mysql through JDBC?

   jdbc:mysql://Host name: port number / database name
   

JDBC realizes adding, deleting and changing data of single form

target

Learning JDBC to Achieve Data Increase, Delete and Change in Single Form

Preparing data

-- Create a Category Table
CREATE TABLE category (
  cid INT PRIMARY KEY AUTO_INCREMENT,
  cname VARCHAR(100)
);
-- Initialization data
INSERT INTO category (cname) VALUES('household electrical appliances');
INSERT INTO category (cname) VALUES('Clothes & Accessories');
INSERT INTO category (cname) VALUES('Cosmetics');

JDBC realizes adding, deleting and changing data of single form

We need to add, delete, modify and check the database. We need to use the Statement object to execute the SQL statement.

Introduction to API

Get the Statement object

In the java.sql.Connection interface, there are the following methods to get the Statement object

Statement createStatement() 
Create a Statement object to send SQL statements to the database

Introduction to API of Statement

1. boolean execute(String sql)
   To execute any SQL statement, if the query returns true, if not false; usually not
   

2. int executeUpdate(String sql)
   Used to execute statements such as additions, deletions, and alterations; returns the number of rows affected
   

3. ResultSet executeQuery(String sql)
   Used to execute query statements; returns the result set of the query
   

   executeQuery: Used to execute query SQL

   executeUpdate: Used to execute SQL other than queries

Use steps

1. Registration Driver
2. Get the connection
3. Get the Statement object
4. Executing SQL statements using Statement objects
5. Releasing resources

Case code

public class Demo03 {
	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");

		Connection conn = DriverManager.getConnection("jdbc:mysql:///day24", "root", "root");
		System.out.println(conn);

		// Get a Statement object from the connection
		Statement stmt = conn.createStatement();

		// 1. Insert records
		String sql = "INSERT INTO category (cname) VALUES ('Mobile phone');";
		int i = stmt.executeUpdate(sql);
		System.out.println("Number of rows affected:" + i);

		// 2. Modification of records
		sql = "UPDATE category SET cname='automobile' WHERE cid=4;";
		i = stmt.executeUpdate(sql);
		System.out.println("Number of rows affected:" + i);

		// 3. Delete records
		sql = "DELETE FROM category WHERE cid=1;";
		i = stmt.executeUpdate(sql);
		System.out.println("Number of rows affected:" + i);
		
		// Releasing resources
		stmt.close();
		conn.close();
	}
}

Case effect

Summary

Steps for JDBC to implement additions, deletions and modifications

1. Registration Driver
2. Get the connection
3. Getting a pickup truck
4. Execute SQL statements
5. close resource

Method of Statement Object for Executing SQL Statements Except Queries
int executeUpdate(String sql);

JDBC Implements Query of Single Table Data

target

JDBC Implements Query of Single Table Data

ResultSet is used to save the results of executing query SQL statements.
We can't pull out all the data at once. We need to pull it out one line at a time.

Principle of ResultSet

1. There is a pointer inside the ResultSet that records which rows of data are retrieved.
2. Calling the next method, the ResultSet internal pointer moves to the next row of data
3. We can get a row of data getXxx from the ResultSet to get a column of data.
   

API for ResultSet Data Acquisition

In fact, the API for ResultSet to get data is a regular get followed by a data type. We call it getXXX()

Steps for querying data in a database using JDBC

1. Registration Driver
2. Get the connection
3. Get Statement
4. Executing SQL with Statement
5. ResultSet processing results
6. close resource

Case code

public class Demo04 {
	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		
		Connection conn = DriverManager.getConnection("jdbc:mysql:///day24", "root", "root");
		Statement stmt = conn.createStatement();
		
		String sql = "SELECT * FROM category;";
		ResultSet rs = stmt.executeQuery(sql);
		
		// There is a pointer inside, only the record pointed by the pointer can be taken.
		while (rs.next()) { // The pointer moves one line and returns true only if there is data
			// Extract data
			int cid = rs.getInt("cid");
			String cname = rs.getString("cname");
			
			System.out.println(cid + " == " + cname);
		}
		
		// close resource
		rs.close();
		stmt.close();
		conn.close();
	}
}

Case effect

Summary

1. Can you pull all the data out of the ResultSet at once?

   No, only one line at a time.

2. How to retrieve data through ResultSet

   while (rs.next()) {
   	Get this row of data
   }
   

JDBC transaction

target

Learning JDBC Operating Transactions

explain

Previously, we used MySQL commands to operate transactions. Next, we use JDBC to operate bank transfer transactions.

Preparing data

CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	balance DOUBLE
);
-- Add data
INSERT INTO account (NAME, balance) VALUES ('Zhang San', 1000), ('Li Si', 1000);

Introduction to API

Transaction-related methods in the Connection interface

1. void setAutoCommit(boolean autoCommit) throws SQLException;
   false: Open a transaction. ture: Close the transaction
   

2. void commit() throws SQLException;
   Submission transaction
   

3. void rollback() throws SQLException;
   Roll back transactions
   

Use steps

1. Registration Driver
2. Get the connection
3. Open a transaction
4. Get Statement
5. Executing SQL with Statement
6. Submit or roll back transactions
7. close resource

Case code

public class Demo05 {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			// Get the connection
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql:///day24", "root", "root");
			
			// Open a transaction
			conn.setAutoCommit(false);
			
			Statement pstmt = conn.createStatement();
			
			// Zhang Sanjian 500
			String sql = "UPDATE account SET balance = balance - 500 WHERE id=1;";
			pstmt.executeUpdate(sql);
			// Simulated anomaly
			// int i = 10 / 0;
			
			// Li Sijia 500
			sql = "UPDATE account SET balance = balance + 500 WHERE id=2;";
			pstmt.executeUpdate(sql);
			
			pstmt.close();
			// Success, Submission
			System.out.println("Success,Submission transaction");
			conn.commit();
		} catch (Exception e) {
			// Failure, rollback transaction
			try {
				System.out.println("There was an abnormality.,Roll back transactions");
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

Case effect

Summary

Transaction-related API s in JDBC?

The method in the Connection interface.

1. setAutoCommit(false); Open transactions
2. commit(); commit a transaction
3. rollback(); rollback transactions

Writing JDBC Tool Classes

target

Write JDBC to get and close connection tool classes

Through the above case requirements, we will find that every time we execute the SQL statement, we need to register the driver, get the connection, get the Statement, and release the resources. Finding a lot of duplicate work, we can define duplicate code into the method of a class. Calling methods directly simplifies the code.
Next, we define a JDBCUtils class. Put the registration driver, get the connection, get the Statement, and release the resource code into the method of this class. You can call the method directly later.

Writing JDBC Tool Class Steps

1. Define a fixed string as a constant
2. Register drivers in static code blocks
3. Provides a method to get the connection static Connection getConneciton();
4. Define the method close(Connection conn, Statement stmt, ResultSet rs) to close resources
5. Overload closing method close(Connection conn, Statement stmt)

Case code

JDBCUtils.java

public class JDBCUtils {
	// 1. Define a fixed string as a constant
	private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql:///day24";
	private static final String USER = "root";
	private static final String PASSWORD = "root";
	
	// 2. Register drivers in static code blocks (once only)
	// When this class is loaded into memory, it goes to the static code block, then triggers the static code block in the Driver class, and actively registers.
	static {
		try {
			Class.forName(DRIVER_CLASS);
		} catch (ClassNotFoundException e) {}
	}
	
	// 3. Provide a method to get the connection static Connection getConneciton();
	// We are programming for JDBC
	public static Connection getConnection() throws SQLException {
		InputStream is = JDBCUtils.class.getResourceAsStream("/jdbc.properties");
		Properties pp = new Properties();
		pp.load(is);

		Connection conn = DriverManager.getConnection(URL, pp);
		return conn;
	}
	
	// 5. Overload closing method close(Connection conn, Statement stmt)
	public static void close(Connection conn, Statement stmt) {
		close(conn, stmt, null);
	}
	
	// 4. Define a method to close resources (Connection conn, Statement stmt, ResultSet rs)
	public static void close(Connection conn, Statement stmt, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {}
		}
		
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {}
		}
		
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {}
		}
	}
}

Summary

What is the purpose of writing JDBC tool classes?

We found that JDBC operates databases, and the code is fixed and repetitive, encapsulated into tool classes, which makes JDBC easier to use.

JDBC implementation login case

target

Simulate user input account and password login website

1. Enter the correct account number and password to show successful login
   
2. Enter the wrong account number, password, display login failure
   

case analysis

1. Use database to save user's account and password
2. Let users enter accounts and passwords
3. Use SQL to query data in database according to user's account number and password
4. If the data is queried, the login is successful
5. If the data is not queried, the login fails

Implementation steps

1. Create a user table to save the user's account and password, and add some data. The SQL statement is as follows:

   CREATE TABLE USER (
     id INT AUTO_INCREMENT PRIMARY KEY,
     NAME VARCHAR(50),
     PASSWORD VARCHAR(50)
   );
   INSERT INTO USER (NAME, PASSWORD) VALUES('admin', '123'), ('test', '123'), ('gm', '123');
   

2. Write code to let users enter accounts and passwords

   public class Demo07 {
      public static void main(String[] args) {
      Scanner sc = new Scanner(System.in);
      System.out.println("Please enter your account number.: ");
      String name = sc.nextLine();
      System.out.println("Please input a password: ");
      String password = sc.nextLine();
   }
   

3. Use SQL to query data in database according to user's account number and password

   public class Demo07 {
      public static void main(String[] args) throws Exception {
      // Let users enter accounts and passwords
      Scanner sc = new Scanner(System.in);
      System.out.println("Please enter your account number.: ");
      String name = sc.nextLine();
      System.out.println("Please input a password: ");
      String password = sc.nextLine();
      
      // Use SQL to query data in database according to user's account number and password
      Connection conn = JDBCUtils.getConnection();
      Statement stmt = conn.createStatement();
      String sql = "SELECT * FROM user WHERE name='" + name + "' AND password='" + password + "';";
      }
   }
   

4. If the data is queried, the login succeeds, and if the data is not queried, the login fails.

   public class Demo07 {
   	public static void main(String[] args) throws Exception {
   	   // Let users enter accounts and passwords
   	   Scanner sc = new Scanner(System.in);
   	   System.out.println("Please enter your account number.: ");
   	   String name = sc.nextLine();
   	   System.out.println("Please input a password: ");
   	   String password = sc.nextLine();
   	   
   	   // Use SQL to query data in database according to user's account number and password
   	   Connection conn = JDBCUtils.getConnection();
   	   Statement stmt = conn.createStatement();
   	   String sql = "SELECT * FROM user WHERE name='" + name + "' AND password='" + password + "';";
   	   
   	   // If the data is queried, the login succeeds, and if the data is not queried, the login fails.
   	   ResultSet rs = stmt.executeQuery(sql);
   	
   	   if (rs.next()) {
   	      //Can come in and query the data.
   	      String name2 = rs.getString("name");
   	      System.out.println("Welcome," + name2);
   	   } else {
   	      //Failure to query data indicates login failure
   	      System.out.println("Error in account or password...");
   	   }
   	
   	   JDBCUtils.close(conn, stmt, rs);
   	   }
   	}
   }
   

Summary

Login case steps

Create user tables and save some user data

Let users enter accounts and passwords

The input account and password are spliced into a query statement and executed.

If the data is queried, the login is successful

If the data is not queried, the login fails

Question of SQL Injection

target

Learn the concept of SQL injection

Question of SQL Injection

In our previous JDBC implementation login case, when we entered the following password, we found that our account and password were not correct and even successfully logged in.

Please enter a user name:
hehe
 Please input a password:
a' or '1'='1

Problem analysis:

// SQL statement in code
"SELECT * FROM user WHERE name='" + name + "' AND password='" + password + "';";
// After splicing the user's input account password
"SELECT * FROM user WHERE name='hehe' AND password='a' or '1'='1';"

We let users input passwords and SQL statements for string splicing. As a part of the syntax of SQL statements, user input changes the real meaning of the original SQL. The above problem is called SQL injection.

To solve the problem of SQL injection, it is impossible to make simple string splicing between user input password and our SQL statements. The PreparedSatement class is needed to solve the problem of SQL injection.

Summary

What is SQL injection?

The data input by users are spliced into SQL statements, which changes the meaning of the SQL statements.

PreparedStatement precompiled object

target

Understanding the execution principle of PreparedSatement

Execution Principle of PreparedStatement

Inheritance structure:
[External Link Picture Transfer Failure (img-1bFr4w7d-1564843431162)(/pstmt01.png)]
The SQL statements we write allow the database to execute, and the database does not execute the SQL statement string directly. Like Java, the database needs to execute compiled SQL statements (similar to Java compiled bytecode files).

1. Satement objects send this SQL statement to the database for compilation and execution for each SQL statement executed.

Statement stmt = conn.createStatement();
stmt.executeUpdate("INSERT INTO users VALUES (1, 'Zhang San', '123456');");
stmt.executeUpdate("INSERT INTO users VALUES (2, 'Li Si', '666666');");

The above two SQL statements show that most of the content is the same, but the data is slightly different. The database is compiled every time it executes. If there are 10,000 similar SQL statements, the database needs to be compiled 10,000 times and executed 10,000 times, which is obviously inefficient.

1. prepareStatement() sends the SQL statement to the database precompiled first. PreparedStatement refers to precompiled results. Different parameters can be passed in multiple times to the PreparedStatement object and executed. It is equivalent to calling methods to pass in different parameters many times.

String sql = "INSERT INTO users VALUES (?, ?, ?);";
// The SQL statement is first sent to the database precompiled. PreparedStatement refers to precompiled results.
PreparedStatement pstmt = conn.prepareStatement(sql);

// Setting parameters
pstmt.setString(1, 1);
pstmt.setInt(2, "Zhang San");
pstmt.setString(3, "123456");
pstmt.executeUpdate();

// Set the parameters again
pstmt.setString(1, 2);
pstmt.setInt(2, "Li Si");
pstmt.setString(3, "66666");
pstmt.executeUpdate();

Pre-compiled a SQL above, passed in different parameters twice and executed. If there are 10,000 similar statements for inserting data. The database only needs to be precompiled once, 10,000 different parameters are passed in and executed. It reduces the compilation times of SQL statements and improves the execution efficiency.

Sketch Map

Benefits of PreparedStatement

1. prepareStatement() sends the SQL statement to the database precompiled first. PreparedStatement refers to precompiled results. Different parameters can be passed in multiple times to the PreparedStatement object and executed. Reduce the number of SQL compilation and improve efficiency.
2. Security is higher and there is no hidden danger of SQL injection.
3. The readability of the program is improved.

Summary

Benefits of PreparedSatement?

1. Increase of efficiency
2. Improving security
3. Improving SQL Readability

Introduction to API of PreparedStatement

target

Learn the API for PreparedSatement

Inheritance structure

Statement
_Inheritance
PreparedStatement

Get PreparedStatement

In java.sql.Connection, there are methods to obtain PreparedSatement objects

PreparedStatement prepareStatement(String sql)
The SQL statement is first sent to the database precompiled. PreparedStatement objects refer to precompiled results.

"SELECT * FROM user WHERE name=? AND password=?;"; ParameterizedSQL

Introduction to API of PreparedStatement

In java.sql.PreparedStatement, there are methods to set parameters of SQL statements and execute parameterized SQL statements.

1. void setDouble(int parameterIndex, double x) sets the specified parameter to a given Java double value.
   

2. void setFloat(int parameterIndex, float x) sets the specified parameter to a given Java REAL value. 
   

3. Void setInt (int parameter index, int x) sets the specified parameter to a given Java int value.
   

4. void setLong(int parameterIndex, long x) sets the specified parameter to a given Java long value. 
   

5. void setObject(int parameterIndex, Object x) sets the value of the specified parameter with a given object.  
   

6. void setString(int parameterIndex, String x) sets the specified parameter to a given Java String value. 
   

7. ResultSet executeQuery() 
   Execute the SQL query in this PreparedStatement object and return the ResultSet object generated by the query. 
   

8. int executeUpdate() 
   Execute the SQL statement in this PreparedStatement object, which must be a SQL data manipulation language DML statement, such as INSERT, UPDATE or DELETE statement, or a SQL statement without return content, such as DDL statement. 
   

PreparedSatement usage steps

1. Get the connection
2. Write SQL statements, use unknown content? Place: "SELECT * FROM user WHERE name=? AND password =?"; Parametric SQL
3. Get the PreparedStatement object
4. Setting actual parameters
5. Execute parameterized SQL statements
6. close resource

Summary

How to set parameters for PreparedSatement

Void setXxx (the number of question marks, the specific value of the question marks);

How PreparedSatement Executes SQL

ResultSet executeQuery() 
int executeUpdate() 

PreparedStatement Implements Addition, Deletation, Checking and Amendment

target

Learning PreparedSatement to Achieve Addition, Deletion and Change

Create a table structure

CREATE TABLE employee (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20),
  age INT,
  address VARCHAR(50)
);

Add data

Add three records to the Employee table

// Add data: Add three records to the Employee table
public static void addEmployee() throws Exception {
	Connection conn = JDBCUtils.getConnection();
	String sql = "INSERT INTO employee VALUES (NULL, ?, ?, ?);";
	// prepareStatement() sends the SQL statement to the database precompiled first.
	PreparedStatement pstmt = conn.prepareStatement(sql);

	// Setting parameters
	pstmt.setString(1, "Lau Andy");
	pstmt.setInt(2, 57);
	pstmt.setString(3, "Hong Kong");
	int i = pstmt.executeUpdate();
	System.out.println("Number of rows affected:" + i);

	// Set the parameters again
	pstmt.setString(1, "Zhang Xueyou");
	pstmt.setInt(2, 55);
	pstmt.setString(3, "Macao");
	i = pstmt.executeUpdate();
	System.out.println("Number of rows affected:" + i);

	// Set the parameters again
	pstmt.setString(1, "Dawn");
	pstmt.setInt(2, 52);
	pstmt.setString(3, "Hong Kong");
	i = pstmt.executeUpdate();
	System.out.println("Number of rows affected:" + i);

	JDBCUtils.close(conn, pstmt);
}

Effect:

PreparedStatement Modifies Data

Change the student address with id 2 to Taiwan

// Modify the data: Change the student address with id 2 to Taiwan
public static void updateEmployee() throws Exception {
	Connection conn = JDBCUtils.getConnection();

	String sql = "UPDATE employee SET address=? WHERE id=?;";
	PreparedStatement pstmt = conn.prepareStatement(sql);
	pstmt.setString(1, "Taiwan");
	pstmt.setInt(2, 2);
	int i = pstmt.executeUpdate();
	System.out.println("Number of rows affected:" + i);

	JDBCUtils.close(conn, pstmt);
}

Effect:

PreparedStatement Deletes Data

Delete employees with id 2

// Delete data: Delete employees with id 2
public static void deleteEmployee() throws Exception {
	Connection conn = JDBCUtils.getConnection();

	String sql = "DELETE FROM employee WHERE id=?;";
	PreparedStatement pstmt = conn.prepareStatement(sql);
	pstmt.setInt(1, 2);
	int i = pstmt.executeUpdate();
	System.out.println("Number of rows affected:" + i);

	JDBCUtils.close(conn, pstmt);
}

Effect:

Summary

PreparedSatement usage steps?

1. Get the connection
2. Write parameterized SQL, with? SQL
3. Get PreparedStatement
4. Setting parameters
5. Execute SQL
6. Releasing resources

Which method does PreparedSatement use to add, delete and modify?

int executeUpdate();

Is it not possible to write anywhere? When the field value is uncertain?

Rewriting login cases using PreparedSatement

target

Rewriting login cases using PreparedSatement

1. Enter the correct account password:
   
2. Enter the wrong password:
   

explain

PreparedSatement usage steps

1. Write SQL statements, use unknown content?
2. Get the PreparedStatement object
3. Setting actual parameters
4. Execute parameterized SQL statements
5. close resource

Case code

public class Demo02 {
	public static void main(String[] args) throws Exception {
        // Let users enter accounts and passwords
        Scanner sc = new Scanner(System.in);
        System.out.println("Please enter your account number.: ");
        String name = sc.nextLine();
        System.out.println("Please input a password: ");
        String password = sc.nextLine();
        
		// Get the connection
		Connection conn = JDBCUtils.getConnection();
		
		// Write SQL statements, use unknown content?
		String sql = "SELECT * FROM user WHERE name=? AND password=?;";
		
		// prepareStatement() sends the SQL statement to the database precompiled first.
		PreparedStatement pstmt = conn.prepareStatement(sql);
		
		// Specify the value of ____________
		// parameterIndex: Number 1
		// x: Specific value
		pstmt.setString(1, name);
		pstmt.setString(2, password); // Correct password
		
		ResultSet rs = pstmt.executeQuery();
		
		if (rs.next()) {
			String name = rs.getString("name");
			System.out.println("name: " + name);
		} else {
			System.out.println("No data found...");
		}
		
		JDBCUtils.close(conn, pstmt, rs);
	}
}

Case effect

1. Enter the correct account password:
   
2. Enter the wrong password:
   

Summary

Using PreparedStatement to Solve the Question of SQL Injection

Don't splice SQL statements, use unknown content? Place first, then set the parameters using PreparedStatement, and execute

PreparedSatement Query Data

target

Using PreparedSatement to Implement Query Data

Query the employee information with id less than 8 and save it in the Employee class

explain

Query the employee information with id less than 8 and save it in the Employee class

Implementation steps

1. Define the Employee class
2. Get the connection object
3. Get the PreparedStatement object
4. Write the SQL statement and execute it, save the ResultSet
5. Create a collection to save all employee objects
6. Each iteration stores a record in an employee object
7. Put employee objects in a collection
8. close resource
9. Traversing collections, circularly exporting employee objects

Code

public class Employee {
	private int id;
	private String name;
	private int age;
	private String address;
	public Employee() {
	}
	
	public Employee(int id, String name, int age, String address) {
		this.id = id;
		this.name = name;
		this.age = age;
		this.address = address;
	}

	public int getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

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

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	@Override
	public String toString() {
		return "Employee2 [id=" + id + ", name=" + name + ", age=" + age + ", address=" + address + "]";
	}
}

// Query data: Query employee information with id less than 8 and save it in the Employee class
public static void queryEmployee() throws Exception {
	Connection conn = JDBCUtils.getConnection();
	String sql = "SELECT * FROM employee WHERE id<?;";
	PreparedStatement pstmt = conn.prepareStatement(sql);
	pstmt.setInt(1, 26);
	ResultSet rs = pstmt.executeQuery();

	// Create a collection to store multiple Employee2 objects
	ArrayList<Employee> list = new ArrayList<>();

	while (rs.next()) {
		// Move to the next row and get the data
		int id = rs.getInt("id");
		String name = rs.getString("name");
		int age = rs.getInt("age");
		String address = rs.getString("address");

		// Create Employee2 objects
		Employee e = new Employee(id, name, age, address);
		// Add the created employees to the collection
		list.add(e);
	}

	// Output object
	for (Employee e : list) {
		System.out.println(e);
	}
	
	JDBCUtils.close(conn, pstmt, rs);
}

Effect:

Summary

Table Correspondence Classes

A record corresponds to an object

The field value corresponds to the value of the member variable of the object

Which method does PreparedSatement use to implement queries?

ResultSet executeQuery();

ParameterMetaData metadata

target

The Concept of Learning Metadata

Learning ParameterMetaData Metadata Use

Basic overview of metadata

What is metadata: Definition information for databases, tables and columns.

Function of ParameterMetaData

ParameterMetaData can be used to obtain the types and attributes of each parameter tag in the PreparedStatement object.

select * from user where name=? and password=?
// ParameterMetaData can be used to retrieve? Number and type

How to Get ParameterMetaData

Get the ParameterMetaData object through the getParameterMetaData() method of PreparedStatement

Introduction to ParameterMetaData API

1. int getParameterCount() Gets the SQL statement parameter of PreparedStatement? Number
   

2. int getParameterType(int param) Gets the SQL type of the specified parameter.  
   

Use steps

1. Get the ParameterMetaData object
2. Calling methods using objects

Matters needing attention

Case code

public class Demo01 {
	public static void main(String[] args) throws Exception {
		Connection conn = DataSourceUtils.getConnection();
		String sql = "INSERT INTO  student (name, age, score) VALUES (?, ?, ?)";
		PreparedStatement stmt = conn.prepareStatement(sql);
		ParameterMetaData md = stmt.getParameterMetaData();
		System.out.println("Number of parameters: " + md.getParameterCount());

		// Parameter metadata not available for the given statement
		// MySQL does not support getting parameter types
		// System.out.println("parameter type:" + md.getParameterType(1)));
	}
}

Summary

1. Role of ParameterMetaData

Metadata representing parameters can be obtained in SQL? Number and type (mysql is not available)

ResultSetMetaData Metadata

target

Learning ResultSetMetaData Metadata

ResultSetMetaData role

ResultSetMetaData can be used to obtain information about the types and attributes of columns in ResultSet objects.

How to get ResultSetMetaData

Get the ResultSetMetaData object through the getMetaData() method of ResultSet

Introduction to ResultSetMetaData API

1. int getColumnCount() returns the number of columns in this ResultSet object
   

2. String getColumnName(int column) Gets the name of the specified column
   

3. String getColumnTypeName(int column) Gets the database-specific type name of the specified column
   

Use steps

1. Get the ResultSetMetaData object
2. Calling methods using objects

Case code

// ResultSetMetaData
public static void test02() throws SQLException {
    Connection conn = DataSourceUtils.getConnection();
    String sql = "SELECT * FROM student WHERE id=1";
    PreparedStatement stmt = conn.prepareStatement(sql);
    ResultSet rs = stmt.executeQuery();
    // Getting result set metadata
    ResultSetMetaData md = rs.getMetaData();
    int num = md.getColumnCount();
    System.out.println("Number of columns:" + num);
    for (int i = 0; i < num; i++) {
        System.out.println("List:" + md.getColumnName(i + 1)); // Get column names
        System.out.println("Column type:" + md.getColumnTypeName(i + 1)); // Get the type of the class
        System.out.println("-----------");
    }
}

Summary

Role of ResultSetMetaData

Represents the metadata of the result set. You can get the number of fields, the name of the field, and the type of the field in the result set.

Hierarchical introduction of projects

Hierarchical Introduction - Hierarchical in the Company

Layering in Software

The role of stratification:

1. Decoupling: Decoupling between layers.
2. Maintainability: Improve the maintainability of the software, modifying and updating the existing functions will not affect the original functions.
3. Scalability: Enhance the scalability of software, adding new functions will not affect the existing functions.

Introduction to Connection Pool

target

The Principle and Benefits of Learning Connection Pool

explain

Current status of no connection pool

1. Previous steps for JDBC to access the database:
   Create database connections Run SQL statements Close connections
   Perform this duplicate action for each database access
   

2. Problems with creating database connections each time

   • Getting a database connection requires a lot of resources, and each operation needs to retrieve a new connection object, and the connection is closed by performing one operation, while creating a database connection usually requires a relatively large amount of resources and takes a long time to create. In this way, the utilization rate of database connection objects is low.
   • Assuming 100,000 visits a day, database servers need to create 100,000 connections, which wastes the resources of the database and easily causes memory overflow of the database server.

   We eat three meals a day in real life. Instead of throwing away the bowl after a meal, we put the bowl in the cupboard after the meal and use it after the next meal. The goal is to reuse the bowl. Our database connection can also be reused, which can reduce the number of database connections created. Improve the usage of database connection objects.

Connection pool concept: Connection pool is a buffer pool technology for creating and managing database connections. Connection pool is a container in which some database connections are saved and can be reused.

Principle of Connection Pool

1. When the program starts, we create a connection pool and create some connections first.
2. When someone needs to use a connection, take some connections directly from the connection pool
3. When the connection is used up, it is returned to the connection pool

Connection pool benefits

Some database connections are stored in the connection pool, which can be reused. Save the resource consumption of database.

Introduction to Common Connection Pools

javax.sql.DataSource represents the database connection pool, which is an interface provided by JDK. There is no specific implementation. Its implementation is implemented by the vendor of the connection pool. We just need to learn how to use this tool.

public interface DataSource {
	Connection getConnection();
	...
}

Commonly used connection pool implementation components include the following

1. Alibaba-Druid Connection Pool: Druid is a project on Alibaba Open Source Platform
2. C3P0 is an open source connection pool, and its open source projects include Hibernate, Spring, etc.
3. DBCP(DataBase Connection Pool) database connection pool is a Java connection pool project on Apache and a connection pool component used by Tomcat.

Summary

The benefits of connection pooling?
Connections are saved in the connection pool, which can be reused to reduce resource consumption of the database.

Principle of connection pool?

1. When the connection pool is started, some connections are initialized by the connection pool.
2. When someone wants to use a connection, take a connection out of the connection pool
 3. When the connection is used up, it is returned to the connection pool.

C3P0 Connection Pool

target

Learning C3P0 Connection Pool

explain

Brief Introduction of C3P0 Connection Pool

C3P0 address: https://sourceforge.net/projects/c3p0/?source=navbar
C3P0 is an open source connection pool. The Hibernate framework, by default, recommends C3P0 as a connection pool.
C3P 0 jar package: c3p0-0.9.1.2.jar

Explanation of Common Configuration Parameters of C3P0

C3P0 configuration file

We see that some parameters need to be set to use the C3P0 connection pool. So how can these parameters be set most conveniently? Use configuration file mode.

Configuration file requirements:

1. File name: c3p0-config.xml
2. Put it in the source code, the src directory

Configuration file c3p0-config.xml

 <c3p0-config>
   <!-- Read connection pool objects with default configuration -->
   <default-config>
     <!--  Connection parameters -->
     <property name="driverClass">com.mysql.jdbc.Driver</property>
     <property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property>
     <property name="user">root</property>
     <property name="password">root</property>

     <!-- Connection pool parameters -->
     <property name="initialPoolSize">5</property>
     <property name="maxPoolSize">10</property>
     <property name="checkoutTimeout">2000</property>
     <property name="maxIdleTime">1000</property>
   </default-config>

   <named-config name="itheimac3p0"> 
     <!--  Connection parameters -->
     <property name="driverClass">com.mysql.jdbc.Driver</property>
     <property name="jdbcUrl">jdbc:mysql://localhost:3306/day25</property>
     <property name="user">root</property>
     <property name="password">root</property>

     <!-- Connection pool parameters -->
     <property name="initialPoolSize">5</property>
     <property name="maxPoolSize">15</property>
     <property name="checkoutTimeout">2000</property>
     <property name="maxIdleTime">1000</property>
   </named-config>
 </c3p0-config>

Introduction to API

The com.mchange.v2.c3p0.ComboPooledDataSource class represents the connection pool object of C3P0. There are two common ways to create the connection pool:

1. No parametric construction, using default configuration

2. Parametric constructs, using named configurations

1. public ComboPooledDataSource()
   The parameter-free construct uses the default configuration (using the corresponding parameters in the default-config tag in xml)
   

2. public ComboPooledDataSource(String configName)
   Referential constructs use named configuration (configName: XML configuration name, using the corresponding parameters in the named-config tag in xml)
   

3. public Connection getConnection() throws SQLException
    Remove a connection from the connection pool
   

Use steps

1. Import jar package c3p0-0.9.1.2.jar
2. Write c3p0-config.xml configuration file and configure corresponding parameters
3. Place the configuration file in the src directory
4. Create the connection pool object ComboPooled DataSource, using the default configuration or named configuration
5. Getting Connection Objects from Connection Pool
6. Operating databases using connection objects
7. Close the resource and return the connection back to the connection pool

Matters needing attention

The C3P0 configuration file name must be c3p0-config.xml
C3P0 naming configurations can have multiple

Case code

1. Preparing data

   CREATE TABLE student (
      id INT PRIMARY KEY AUTO_INCREMENT,
      NAME VARCHAR(20),
      age INT,
      score DOUBLE DEFAULT 0.0
   );
   

2. configuration file

    <c3p0-config>
      <!-- Read connection pool objects with default configuration -->
      <default-config>
        <!--  Connection parameters -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/day25</property>
        <property name="user">root</property>
        <property name="password">root</property>
   
        <!-- Connection pool parameters -->
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">10</property>
        <property name="checkoutTimeout">2000</property>
        <property name="maxIdleTime">1000</property>
      </default-config>
   
      <named-config name="itheimac3p0"> 
        <!--  Connection parameters -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/day25</property>
        <property name="user">root</property>
        <property name="password">root</property>
   
        <!-- Connection pool parameters -->
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">15</property>
        <property name="checkoutTimeout">2000</property>
        <property name="maxIdleTime">1000</property>
      </named-config>
    </c3p0-config>
   

3. java code

    public class Demo01 {
   
        public static void main(String[] args) throws Exception {
            // Mode 1: Use default-config
            // new ComboPooledDataSource();
    //		 ComboPooledDataSource ds = new ComboPooledDataSource();
   
            // Mode 2: Use Named Configuration (named-config: configuration name)
            // New ComboPooled Data Source ("configuration name");
            ComboPooledDataSource ds = new ComboPooledDataSource("otherc3p0");
   
    //		for (int i = 0; i < 10; i++) {
    //			Connection conn = ds.getConnection();
    //			System.out.println(conn);
    //		}
   
            // Remove connections from connection pool
            Connection conn = ds.getConnection();
   
            // Execute SQL statements
            String sql = "INSERT INTO student VALUES (NULL, ?, ?, ?);";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "Zhang San");
            pstmt.setInt(2, 25);
            pstmt.setDouble(3, 99.5);
   
            int i = pstmt.executeUpdate();
            System.out.println("Number of rows affected: " + i);
            pstmt.close();
            conn.close(); // Return the connection back to the connection pool
        }
    }
   

Case effect

1. Normal access to connections in connection pool
   

2. Get connection timeout in connection pool
   

3. Add data to the database using connections in the connection pool
   

Benefits of using configuration files

Simply modify the configuration file individually, without modifying the code
Benefits of multiple configurations:

1. Different databases can be connected: db1,db2
2. Different connection pool parameters can be used: maxPoolSize
3. Connecting databases from different vendors: Oracle or MySQL

Summary

How to use CP30?

1. Import the Jar package: c3p0-0.9.5.2.jar,mchange-commons-java-0.2.12.jar
2. Copy c3p0-config.xml and modify parameters
3. c3p0-config.xml in src
4. Create C3P0 connection pool object, Combopooled DataSource object in code
5. Getting connections from connection pools
6. Execute SQL statements
7. Return the connection back to the connection pool

Common parameters of C3P0?

Druid connection pool

target

Learn how to use Druid connection pool

explain

Druid is a database connection pool developed by Alibaba called monitoring. Druid is the best database connection pool at present. In terms of function, performance and scalability, it surpasses other database connection pools, and adds log monitoring, which can monitor the execution of database connection pools and SQL very well. Druid has deployed more than 600 applications in Alibaba, after more than a year of rigorous deployment of large-scale production environment. Druid address: https://github.com/alibaba/druid 

The jar package used by the DRUID connection pool: druid-1.0.9.jar

Common configuration parameters for Druid

Basic use of Druid connection pool

Introduction to API

The com.alibaba.druid.pool.DruidDataSourceFactory class has methods for creating connection pools

public static DataSource createDataSource(Properties properties)
Create a connection pool whose parameters use the data in properties

We can see that Druid connection pool needs a Properties object to set parameters when it is created, so we use the properties file to save the corresponding parameters.
Druid connection pool configuration file name is casual, easy to load under the src directory
druid.properties file content:

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/day17
username=root
password=root
initialSize=5
maxActive=10
maxWait=3000

Use steps

1. Import the jar package of druid

2. Create a properties file in the src directory and set the corresponding parameters

3. Load the contents of the properties file into the Properties object

4. Create a Druid connection pool using parameters in the configuration file

5. Remove connections from Druid connection pool

6. Execute SQL statements

7. close resource

Case code

1. Create a new Druid configuration file under the src directory named: druid.properties, which reads as follows

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/day25
username=root
password=root
initialSize=5
maxActive=10
maxWait=3000

java code

public class Demo04 {
	public static void main(String[] args) throws Exception {
		// Load configuration parameters in configuration files
		InputStream is = Demo04.class.getResourceAsStream("/druid.properties");
		Properties pp = new Properties();
		pp.load(is);
		
		// Create connection pools using parameters in configuration files
		DataSource ds = DruidDataSourceFactory.createDataSource(pp);
		
//		for (int i = 0; i < 10; i++) {
//			Connection conn = ds.getConnection();
//			System.out.println(conn);
//		}
		
		// Remove connections from connection pool
		Connection conn = ds.getConnection();
		
		// Execute SQL statements
		String sql = "INSERT INTO student VALUES (NULL, ?, ?, ?);";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, "Wang Wu");
		pstmt.setInt(2, 35);
		pstmt.setDouble(3, 88.5);
		
		int i = pstmt.executeUpdate();
		System.out.println("Number of rows affected: " + i);
		
		// Execute queries
		sql = "SELECT * FROM student;";
		ResultSet rs = pstmt.executeQuery(sql);
		
		while (rs.next()) {
			int id = rs.getInt("id");
			String name = rs.getString("name");
			int age = rs.getInt("age");
			double score = rs.getDouble("score");
			System.out.println("id: " + id + " ,name: " + name + " ,age = " + age + " ,score = " + score);
		}
		
		pstmt.close();
		conn.close(); // Return the connection back to the connection pool
	}
}

Case effect

1. Normal access to connections in connection pool
   
2. Get connection timeout in connection pool
   
3. Connection operation database in pool using DRUID
   

Summary

Druid usage steps?

1. Importing Druid's Jar package
2. Copy druid.properties to src and modify the configuration
3. Loading properties into the Properties object in the code
4. Create Druid connection pool
5. Remove connections from Druid connection pool
6. Execute SQL
7. Close

Common configuration parameters for Druid

Druid connection pool is basically used, regardless of the C3P0 connection pool, the configuration can be roughly divided into two types: 1. parameters of connection database, 2. parameters of connection pool. These two configurations have the same function, but the parameters name may be different.

Writing Connection Pool Tool Class

Every time we operate a database, we need to create a connection pool, get connections, close resources, which is duplicate code. We can simplify the code by placing the code that creates and gets the connection pool in a tool class.

target

Write a tool class for connection pool to simplify the use of connection pool

Connection pool tool class steps

1. Declare static connection pool member variables
2. Create connection pool objects
3. Define the method of getting connection pool
4. Define the method of getting connected objects
5. Define ways to close resources

Case code

DataSourceUtils.java

public class DataSourceUtils {
	// 1. Declare static data source member variables
	private static DataSource ds;

	// 2. Create connection pool objects
	static {
		// Load the data in the configuration file
		InputStream is = DataSourceUtils.class.getResourceAsStream("/druid.properties");
		Properties pp = new Properties();
		try {
			pp.load(is);
			// Create connection pools using parameters in configuration files
			ds = DruidDataSourceFactory.createDataSource(pp);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 3. Define the way to get the data source
	public static DataSource getDataSource() {
		return ds;
	}

	// 4. Define the method to connect objects
	public static Connection getConnection() throws SQLException {
		return ds.getConnection();
	}

	// 5. Define ways to close resources
	public static void close(Connection conn, Statement stmt, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {}
		}

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

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

	// 6. Overload Closing Method
	public static void close(Connection conn, Statement stmt) {
		close(conn, stmt, null);
	}
}

Test class code

public class Demo03 {
	public static void main(String[] args) throws Exception {
		// Get the connection
		Connection conn = DataSourceUtils.getConnection();

		// Execute sql statements
		String sql = "INSERT INTO student VALUES (NULL, ?, ?, ?);";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, "Li Si");
		pstmt.setInt(2, 30);
		pstmt.setDouble(3, 50);
		int i = pstmt.executeUpdate();
		System.out.println("Functions of influence: " + i);

		// close resource
		DataSourceUtils.close(conn, pstmt);
	}
}

Using the connection pool tool class can simplify the code, we focus on writing SQL to execute.

Summary

Writing connection pool tool class steps

1. Static connection pool member variables are defined
2. Create connection pool objects in static code blocks
3. Define a method to return to connection pool
4. Define a method to return a connection
5. Define close method to close resources

summary

1. Understanding the concept of JDBC

   Java database connection is the specification of Java operating database

2. Ability to use Connection Connection

   Connection conn = DriverManager.getConnection("jdbc:mysql:///day16", "root", "root");
   
   Statement stmt = conn.createStatement();
   

3. Ability to use Statement to connect

   Objects that execute SQL statements, equivalent to vans
   
   Statement stmt = conn.createStatement();
   int i = stmt.executeUpdate(sql);
   ResultSet rs = stmt.executeQuery(sql);
   

4. Ability to use ResultSet connection

   ResultSet Pick up:
   boolean next(); Move the cursor down one line, if data is returned true,No data return false
   Xxx getXxx(); Get the value of the field
   
   ResultSet rs = stmt.executeQuery("SELECT * FROM category;");
   // 5.ResultSet Processing Results
   while (rs.next()) {
   	int cid = rs.getInt(1);
   	String cname = rs.getString(2);
   	System.out.println(cid + "::" + cname);
   }
   

5. JDBC can be used to add, delete, modify and check data in a single form.

   // 1. Registration Driver
   Class.forName("com.mysql.jdbc.Driver");
   // 2. Getting Connections
   Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day16", "root", "root");
   // 3. Getting a pickup truck
   Statement stmt = conn.createStatement();
   // 4. Executing SQL statements
   // Add data
   // String SQL = INSERT INTO category (cname) VALUES ('sex toys');
   
   // Modify data
   // String SQL = UPDATE category SET cname='family planning supplies'WHERE cid=2;;
   
   // Delete data
   String sql = "DELETE FROM category WHERE cid=3;";
   // int executeUpdate(String sql)
   int i = stmt.executeUpdate(sql);
   System.out.println("Number of rows affected: " + i);
   
   // 5. Closing resources
   stmt.close();
   conn.close();
   

6. Ability to operate transactions using JDBC

   The way to manipulate transactions is Connection in
   void setAutoCommit(false): Open a transaction
   void commit(); Submission transaction
   void rollback(); Roll back transactions
   
   
   public static void main(String[] args) {
   	Connection conn = null;
   	Statement stmt = null;
   	try {
   		// 1. Registration Driver
   		Class.forName("com.mysql.jdbc.Driver");
   		// 2. Getting Connections
   		conn = DriverManager.getConnection("jdbc:mysql:///day16", "root", "root");
   		// 3. Open a transaction
   		// false: Represents closing autocommit and starting a transaction
   		conn.setAutoCommit(false);
   		// 4. Get Statement
   		stmt = conn.createStatement();
   		// 5. Executing SQL with Statement
   		// Zhang San-500, Li Si+500
   		stmt.executeUpdate("UPDATE account SET balance = balance - 500 WHERE id=1;");
   
   		// The simulation is out of order.
   		int a = 10 / 0;
   
   		stmt.executeUpdate("UPDATE account SET balance = balance + 500 WHERE id=2;");
   
   		// Submit the transaction without any problems
   		System.out.println("Submission transaction");
   		conn.commit();
   	} catch (Exception e) {
   		// 6. Submit or roll back transactions
   		System.out.println("Something's wrong,Roll back transactions");
   		try {
   			conn.rollback();
   		} catch (SQLException ex) {
   			System.out.println("Rollback failure");
   		}
   	} finally {
   		// 7. Closing resources
   		if (conn != null) {
   			try {
   				conn.close();
   			} catch (SQLException e) {
   				e.printStackTrace();
   			}
   		}
   
   		if (stmt != null) {
   			try {
   				stmt.close();
   			} catch (SQLException e) {
   				e.printStackTrace();
   			}
   		}
   	}
   }
   

7. Be able to complete JDBC implementation login case

   Core to query the data whether there is user input account and password
   public static void main(String[] args) throws SQLException {
   	// 1. Use database to save user's account and password
   	// 2. Enable users to enter accounts and passwords
   	Scanner sc = new Scanner(System.in);
   	System.out.println("Please enter your account number.:");
   	String name = sc.nextLine();
   	System.out.println("Please input a password:");
   	String password = sc.nextLine();
   
   	// 3. Use SQL to query data in database according to user's account number and password
   	String sql = "SELECT * FROM user WHERE name='" + name+ "' AND password='" + password + "';";
   
   	// SELECT * FROM user WHERE name='nba' AND password='a' or '1'='1';
   	System.out.println(sql);
   
   	Connection conn = JDBCUtils.getConnection();
   	Statement stmt = conn.createStatement();
   	ResultSet rs = stmt.executeQuery(sql);
   	if (rs.next()) {
   		// Find Users
   		// 4. If the data is queried, the login is successful.
   		System.out.println("Congratulations, " + name + "Successful login!");
   	} else {
   		// No user was found
   		// 5. If the data is not queried, the login fails.
   		System.out.println("ERROR Incorrect username or password");
   	}
   }