Summary of JDBC

What is a JDBC

  • Java Data Base Connectivity (JDBC) is a standard specification for Java to access databases. Is a Java API for executing SQL statements that provides uniform access to multiple relational databases and consists of a set of classes and interfaces written in the Java language.

JDBC theory

  • JDBC is the interface, the driver is the implementation of the interface, without the driver will not be able to complete the database connection, thus can not operate the database! Each database vendor needs to provide its own driver to connect to its own database, which means that drivers are generally provided by the database generator vendor.

  • Summary: JDBC is defined by Sun company a set of operation of all relational database rules (interface), and database manufacturers need to achieve this interface, provide a database driver JAR package, we can use this interface programming, the real implementation of the code is the corresponding driver package implementation class.

JDBC development

API usage: 1. Register the driver

  • The JDBC specification defines the Driver interface: java.sql.Driver
  • The MySql Driver package provides the implementation class com.mysql.jdbc.driver
How to load the registered driver describe
Class.forname (database driven implementation Class) Load and register the database Driver provided by MySql “com.mysql.jdbc.driver”.
  • Code sample
public class JDBCDemo01 { 
    public static void main(String[] args) throws ClassNotFoundException { 
        //1. Register the driver
        // The forName method initializes the class
        Class.forName("com.mysql.jdbc.Driver"); }}Copy the code
  • Note: starting with JDBC3, the version that is currently in common use. Can be used directly without registering the driver. The sentence class.forname can be omitted.

API usage: 2. Get a connection

  • The Connection interface, representing a Connection object, is implemented by the vendor of the database
  • Using static methods of the DriverManager class, getConnection gets the connection to the database
Static method to get a connection instructions
Connection getConnection(String url, String user, String password) Retrieve the database connection object by connection string and username and password
  • 1) Description of three connection parameters of getConnection method
The connection parameters instructions
user Login User name
password The login password
url MySql URL format

jdbc:mysql://localhost:3306/db4
  • 2) A detailed description of urls

jdbc:mysql://localhost:3306/db4? characterEncoding=UTF-8

  • JDBC specifies the URL format byThree partsEach part is separated by a colon.
    • The first part is the JDBC protocol, which is fixed;
    • The second part is the subprotocol, which is the name of the database, connecting to the mysql database, and the second part is of course mysql;
    • The third part is specified by the database vendor. We need to understand the requirements of each database vendor. The third part of mysql consists of the IP address of the database server (localhost), port number (3306), and database name to be used.

3. Obtain the statement execution platform

  • The SQL statement execution object is obtained through the createStatement method of Connection
Methods in the Connection interface instructions
Statement createStatement() Create SQL statement execution objects
  • Statement: Represents a Statement object that sends SQL statements to the server, executes static SQL statements, and returns the results it generates.
Common methods of the Statement class instructions
int executeUpdate(String sql); Execute the INSERT UPDATE delete statement. Returns an int representing the number of rows affected
ResultSet executeQuery(String sql); Execute the SELECT statement to return a ResultSet object

API usage: 4. Process result sets

  • The result set is processed only when a query operation is performed

The ResultSet interface

  • Function: encapsulate the result set of database query, traverse the result set, take out each record.
ResultSet Interface method instructions
boolean next() 1) Cursor down one line

2) Return Boolean, true if there is a next record, false otherwise
xxx getXxx( String or int) 1) By column name, the argument is of type String. Returns different types

2) Pass the column number, the argument is an integer, starting at 1. Returns different types

5. Release resources

1) Objects to be released are as follows: ResultSet, Statement, and Connection 2) Release principles: Open objects first and close them later. ResultSet ==> Statement ==> Connection 3) Which code block to place in: finally block

  • As with IO streams, everything needs to be turned off after use! The sequence of closing is first on and then off, the first obtained and then closed, and the first obtained and then closed

Step to summarize

  1. Get driver (can be omitted)
  2. Get connected
  3. Obtaining the Statement object
  4. Processing result sets (only at query time)
  5. Release resources

JDBC implementation add, delete, change and check

JDBC tools

  • When do you create your own utility classes?
    • If a feature is used frequently, we recommend making it a utility class that can be reused in different places.
    • The “get database connection” operation, which will be present in all future add, delete, change, and search functions, can encapsulate the utility class JDBCUtils. Provides a way to get connection objects to achieve code reuse.
  • What a utility class contains
      1. Several strings can be defined as constants: username, password, URL, driver class
      1. Get the database connection: getConnection()
      1. Close all open resources:

DML operations

Insert records

  • Solve the problem of inserting Chinese garbled characters
// jdbc:mysql://localhost:3306/db4? characterEncoding=UTF-8
// characterEncoding= utf-8 specifies the characterEncoding and decoding format.
Copy the code
  • Code sample
/** * Insert data *@throws SQLException 
*/ 
@Test 
public void testInsert(a) throws SQLException { 

    //1. Use the tool class to obtain the connection
    Connection connection = JDBCUtils.getConnection(); 
    
    / / 2. Get the Statement
    Statement statement = connection.createStatement(); 
    
    / / 2.1 to write Sql
    String sql = Insert into Jdbc_user values(null,' jdbc_user ','123','2020/1/1')"; 
    
    / / 2.2 Sql execution
    int i = statement.executeUpdate(sql); 
    System.out.println(i); 
    
    / / 3. Close the flow
    JDBCUtils.close(connection,statement); 
}
Copy the code

Update record

  • Change the user name according to the ID
/** * Change the name of user 1 to guangkun */ 
@Test 
public void testUpdate(a) throws SQLException { 

    Connection connection = JDBCUtils.getConnection(); 

    Statement statement = connection.createStatement(); 

    String sql = "Update jdbc_user set username = ' 'where id = 1"; 
    statement.executeUpdate(sql); 

    JDBCUtils.close(connection,statement); 
}
Copy the code

Delete records

  • Delete records 3 and 4
/** * Delete records with ids 3 and 4 *@throws SQLException 
*/ 
@Test 
public void testDelete(a) throws SQLException { 

    Connection connection = JDBCUtils.getConnection(); 

    Statement statement = connection.createStatement(); 
    statement.executeUpdate("delete from jdbc_user where id in(3,4)"); 

    JDBCUtils.close(connection,statement); 
}
Copy the code

DQL operation

Query a record whose name is Zhang Baiwan

public class TestJDBC02 { 
    public static void main(String[] args) throws SQLException { 

        //1. Obtain the connection object
        Connection connection = JDBCUtils.getConnection(); 
    
        2. Obtain the Statement object
        Statement statement = connection.createStatement(); 
    
        String sql = "SELECT * FROM jdbc_user WHERE username = 'jdbc_user ';; 
        ResultSet resultSet = statement.executeQuery(sql); 
    
        //3. Process the result set
        while(resultSet.next()){ 
            // Get field information by column name
            int id = resultSet.getInt("id"); 
            String username = resultSet.getString("username"); 
            String password = resultSet.getString("password"); 
            String birthday = resultSet.getString("birthday"); 
        
            System.out.println(id+""+username+"" + password +"" + birthday); 
         }
     
         //4. Release resourcesJDBCUtils.close(connection,statement,resultSet); }}Copy the code

SQL Injection Problems

Sql Injection Demo

1) Insert two data into jdbc_user

Insert 2 pieces of data
INSERT INTO jdbc_user VALUES(NULL.'jack'.'123456'.'2020/2/24'); 
INSERT INTO jdbc_user VALUES(NULL.'tom'.'123456'.'2020/2/24');
Copy the code

2) SQL injection demonstration

# SQL injection demo
-- Enter an incorrect password
SELECT * FROM jdbc_user WHERE username = 'tom' AND PASSWORD = '123' OR '1' = '1';
Copy the code

If this is a login operation, then the user has logged in successfully. Obviously this is not what we want to see

Problem analysis

What is SQL injection?

  • We concatenate the password entered by the user with the SQL statement. The user’s input becomes part of the SQL statement syntax and changes the true meaning of the original SQL. This problem is called SQL injection.

How is injection implemented

  • Concatenate string at user input
select * from jdbc_user where username = 'abc' and password = 'abc' or '1'='1' 

-- name=' ABC 'and password=' ABC

Select * from user where true=true; All records were queried
Copy the code

How to solve

  • To solve SQL injection, you cannot simply concatenate the password entered by the user with our SQL statement.

Preprocessing object

PreparedStatement Describes the interface

  • PreparedStatement is a subinterface of the Statement interface that inherits all methods from the parent interface. It is a pre-compiled SQL statement object.
  • Precompiled: Indicates that SQL statements are precompiled and stored in a PreparedStatement object. You can then use this object to efficiently execute the statement multiple times.

PreparedStatement characteristics

  • Because of the pre-compiled function, improve the efficiency of SQL execution.
  • Can effectively prevent SQL injection problems, higher security

Get the PreparedStatement object

  • Create a PreparedStatement object using Connection
Methods in the Connection interface instructions
PreparedStatement prepareStatement(String sql) Specifies a precompiled SQL statement that uses placeholders? Create a statement object

PreparedStatement Common method of an interface

Commonly used method instructions
int executeUpdate(); Execute the INSERT UPDATE delete statement
ResultSet executeQuery(); Execute the SELECT statement. Returns the result set object Resulet

Use the steps in PreparedStatement

1) Write SQL statement, unknown content use? Sites:

"SELECT * FROM jdbc_user WHERE username=? AND password=?" ;

2) Get the PreparedStatement object 3) set the actual parameters: setXxx(placeholder position, real value) 4) execute the parameterized SQL statement 5) close the resource

SetXxx overloaded method instructions
void setDouble(int parameterIndex, double x) Sets the specified parameter to the given Java double value.
void setInt(int parameterIndex, int x) Sets the specified parameter to the given Java int value.
void setString(int parameterIndex, String x) Sets the specified parameter to the given Java String value.
void setObject(int parameterIndex, Object x) Sets the value of the specified argument using the given object.

What is the difference between a Statement and a PreparedStatement?

  • Statement is used to execute static SQL statements. To execute static SQL statements, you must specify a prepared SQL Statement.
  • A PrepareStatement is a pre-compiled SQL statement object that can contain dynamic parameters such as? , can be “? “. Dynamically set parameter values.
  • Preparestatements can reduce compilation times and improve database performance.

JDBC Control transactions

  • Previously we used MySQL commands to manipulate transactions. Next we use JDBC to handle transactions for bank transfers.

Transaction dependent API

  • We use the methods in Connection for transaction management
methods instructions
void setAutoCommit(boolean autoCommit) The parameter is true or false. If this parameter is set to false, automatic commit is disabled and transactions are enabled
void commit() Commit the transaction
void rollback() Roll back the transaction

Development steps

  1. Get connected
  2. Open the transaction
  3. When the PreparedStatement is obtained, perform two update operations
  4. Normally the transaction is committed
  5. An exception occurred to rollback the transaction
  6. Finally close the resource