1. DriverManager

(1) Register the driver

Class.forName("com.mysql.cj.jdbc.Driver");
Copy the code

(2) Get the database link

public static Connection getConnection(String url,String user,String password)
Copy the code

Try to establish a connection to the given database URL. Different databases have different URL statements.

"JDBC :mysql://IP: port/database"    // MySQL database URL
"JDBC: Oracle :thin:@//IP: port/database"  // Oracle database URL
"JDBC: sqlServer ://IP address: port; DatabaseName= database" // SQL Server database URL
Copy the code

If MySQL uses a local database and default port, you can use the shorthand “JDBC: MySQL :/// database”.

2. Connection

(1) Get the object to execute SQL

createStatement() 
prepareStatement(String sql) 
Copy the code

(2) Management affairs

setAutoCommit(boolean autoCommit) // Set the value to false to start transactions
commit() // Commit the transaction
rollback() // Rollback the transaction
Copy the code

3. Statement

(1) int executeUpdate(String sql)

(2) ResultSet executeQuery(String sql)

Run the DQL statement to query the data in the table and return the query result set.

(3) boolean execute(String sql)

4. ResultSet

This is a result set object that encapsulates the query results. All you have to do is pull the data out of the result set.

There’s an abstract thing in there called a cursor. Like subscripts in arrays, cursors start by pointing to a row of the column name of the result set, and do not point to any row of data. Method allows you to move a cursor and point to data to read data.

(1) boolean next()

(2) Get data type (parameter)

For example: int getInt(int columnIndex) String getString(int columnIndex)

Note: There are two kinds of parameters:

1. Int: indicates the column number, starting with 1 from the first leftmost column.

For example, get int (1)

2. String: represents the name of the column, which is more precise and intuitive, and is used more often.

For example: get string (” name “)

Example:

import java.sql.*;

public class JDBCSelect {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/" +
                    "db? characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false"."root"."saqwedcxz");
            String sql = "SELECT * FROM user";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while(rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString("name");
                int age = rs.getInt(3);
                System.out.println(id + "-" + name + "-"+ age); }}catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(rs ! =null) {
                try {
                    rs.close();
                } catch(SQLException e) { e.printStackTrace(); }}if(stmt ! =null) {
                try {
                    stmt.close();
                } catch(SQLException e) { e.printStackTrace(); }}if(conn ! =null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
Copy the code

5. PreparedStatement

(1) problem

Conventional pass-value login, through simple string concatenation, there may be some SQL keywords involved in string concatenation, SQL statement changes, will lead to illegal access, which is also known as “SQL injection “vulnerability.

The classic SQL injection string: a ‘or ‘a’ = ‘a ‘

(2) is solved

(3) use

1. When defining SQL statements, use? Instead. For example, “SELECT * FROM user WHERE id =? ;
2. To obtain an object for executing a SQL statement, use the prepareStatement(String SQL) method, for example, conn.prepareStatement(SQL);
3. For the “?” Pstmt.setint (1,1); pstmt.setint (1,1);
4. When executing the SQL statement, you do not need to upload the SQL because the PreparedStatement has been precompiled. For example: PSTMT executeQuery ();

(4) pay attention to

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
    conn = JDBCUtils.getConnection();
    String sql = "SELECT * FROM user WHERE id = ?";
    pstmt = conn.prepareStatement(sql);
    pstmt.setInt(1.1);
    rs = pstmt.executeQuery();
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    JDBCUtils.close(rs, pstmt, conn);
}
Copy the code