1 JDBC

1.1 concept

We learned about the database. The database realizes the persistence of data, but we finally have to process data in the program. How can we access the database and read and write data in java code?

This requires a set of database standards set by sun company, which is JDBC (Java Database Connectivity). But it is only a specification, not a concrete implementation. Therefore, the database manufacturer realizes its own Driver according to the JDBC standard. For example, MySQL Driver com.mysql.cj.jdbc.Driver, Oracle Driver oracle.jdbc.OracleDriver. With this solution, java can access the data in the database.

Therefore, JDBC is the abbreviation of java database connectivity. It is a technology specially used to complete the connection between java programs and databases

public interface Connection extends Wrapper, AutoCloseable {}

public interface Statement extends Wrapper, AutoCloseable {}

public interface PreparedStatement extends Statement {}

public interface CallableStatement extends PreparedStatement {}

public interface ResultSet extends Wrapper, AutoCloseable {}

Java advocates interface oriented development, and the most classic interface design is JDBC database interface.

Connection link, Statement statement, PreparedStatement preprocessing Statement, CallableStatement stored procedure, ResultSet result set.

There are three calling methods: Statement statement, PreparedStatement preprocessing Statement and CallableStatement stored procedure. It is recommended to use the second PreparedStatement to prevent SQL injection. It also has high precompiling performance.

1.2 use steps

1. Import the jar package (rich tool classes are provided using JDBC)

2. Provide parameters for connecting to the database (user name, root password, root port number 3306)

3. In java program, initiate SQL statement to operate database

4. If the database has found the result, it will be returned to the java program

1.3 idea creates a project and imports the jar package cg

• Create project: File - New - Project - select java - next - next - enter project name - Finish
• Import jar package: find the file mysql-connector-java-5.1.32.jar, copy it and paste it into Project
• In the IDEA, select the jar package, right-click compile (add as library...), and click ok
• Check whether the compilation is successful: you can see that the jar package in the IDEA can be clicked

Finally, write java code

package cn.tedu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

//Introduction to JDBC:
//JDBC is a standard for java to connect to a database. It is essentially a pile of tool classes
public class Test1 {
    public static void main(String[] args) throws Exception {
        //1. Register driver
        Class.forName("com.mysql.jdbc.Driver");//Full path
        //2. Get the connection to the database (user name / password)
        //jdbc protocol for connecting to mysql database / / native: port number / database name
        String url="jdbc:mysql://localhost:3306/cgb2108" ;
        Connection c = DriverManager.getConnection(
                url,"root","root");
        //3. Obtain the transmitter
        Statement s = c.createStatement();
        //4. Execute SQL and return the result set
        String sql="select * from dept" ;//Query all data of dept table
        ResultSet r = s.executeQuery(sql);//executeQuery executes the SQL of the query, and executeupdate executes the SQL of adding, deleting and modifying the query
        //5. Process the results returned by the database
        while(r.next()){//next() determines whether there is data in the resultset
            //getXxx() gets the data in the resultset
            int a = r.getInt(1);//Gets the integer value of column 1
            String b = r.getString(2);//Gets the string value of column 2
            String c1 = r.getString(3);//Gets the string value of column 3
            System.out.println(a+b+c1);
        }
        //6. Release resources
        r.close();//Release result set
        s.close();//Release the transmitter
        c.close();//Release connector
    }
}

1.4.1 introduction case - connecting to database

package jdbc;

import com.mysql.jdbc.Driver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/*JDBC It is a standard for java to connect to the database. It is essentially a pile of tool classes*/
public class Test1 {
    public static void main(String[] args) throws Exception {
        //1. Register driver
        Class.forName("com.mysql.jdbc.Driver");//Full path of reflection tool class
        //2. Get the connection to the database (user name / password)
        String url="jdbc:mysql://localhost:3306/cgb2108 "; / / fixed writing method: jdbc:mysql://localhost: Port number / database name
        Connection c = DriverManager.getConnection(url,"root","root");
        //3. Get the transmitter
        Statement s = c.createStatement();
        //4. Execute the sql statement and return the results to the result set for saving
        String sql="select * from dept";//Query all data of dept table
        ResultSet r = s.executeQuery(sql);//executeQuery() is used to execute the query statement, executeLargeUpdate() is used to perform addition, deletion and modification, and ResultSet result set is used to save the query results
        //5. Process the results returned by the database
        while(r.next()){//Judge whether there is data in the ResultSet and return true
            int a = r.getInt(1);//Gets the value of integer type in column 1 of ResultSet
            String b = r.getString(2);//Gets the value of integer type in column 2 of ResultSet
            String c1 = r.getString(3);//Gets the value of integer type in column 3 of ResultSet
            System.out.println(a+" "+b+" "+c1);
        }
        //6. Release / close resources
        r.close();//Release result set
        s.close();//Release the transmitter
        c.close();//Release connector
    }
}

1.4.2 querying tb_user table data

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Test2 {
    public static void main(String[] args) throws Exception {
        method();
    }

    //Query tb_user table data
    private static void method() throws Exception {
        //1. Register driver
        Class.forName("com.mysql.jdbc.Driver");
        //2. Get the results returned by the database
        String url="jdbc:mysql://localhost:3306/cgb2108";
        Connection c = DriverManager.getConnection(url,"root","root");
        //3. Get the transmitter
        Statement s = c.createStatement();
        //4. Execute SQL
        String sql = "select * from tb_user";
        ResultSet r = s.executeQuery(sql);
        //5. Parse result set
        while (r.next()){
            int a = r.getInt("id");
            String b = r.getString("name");
            String c1 = r.getString("password");
            System.out.println(a+" "+b+" "+c1);
        }
        //6. Release resources
        r.close();
        s.close();
        c.close();
    }
}

1.4.3 simulate user login

package cn.tedu.jdbc;

import java.sql.*;
import java.util.Scanner;

//Requirement: use JDBC to query tb_user table data
/* 1,Create a table, insert data 2, and query data using JDBC
CREATE TABLE tb_user(
   id int PRIMARY KEY auto_increment,
   name varchar(20) default NULL,
   password varchar(20) default NULL
)
insert into tb_user values(null,'jack','321')
 */
public class Test3 {
    public static void main(String[] args) throws Exception {
//        method(); / / query the data in tb_user table
//        method2(); / / simulate user login
        method3();//Resolve SQL attacks
    }
    //Resolve SQL attacks
    private static void method3() throws Exception {
        //1. Register the driver 2. Obtain the connection
        Connection c = JDBCUtils.getConnection();
        //3. Execute SQL
        String a = new Scanner(System.in).nextLine();//user name
        String b = new Scanner(System.in).nextLine();//password
        //If the string is spliced dynamically, the data is in the middle position "+ a +"
//        String sql="select * from tb_user where name='jack' and password='321'" ;
//        String sql="select * from tb_user where name='"+a+"' and password='"+b+"'" ;
        //SQL skeleton: replace the position of parameters with "? Which is called placeholder. Advantages: simplicity (avoiding SQL splicing parameters)
        String sql="select * from tb_user where name=? and password=?" ;
        //4. Obtain the transmitter
//        Statement s = c.createStatement();
        PreparedStatement s = c.prepareStatement(sql);
        //Set SQL parameters -- setXxx() set different types of parameters
        s.setString(1,a);//The index of? And the value to be set for
        s.setString(2,b);//The index of? And the value to be set for
        //TODO, will SQL attacks still occur when the user name enters jack '#???
        ResultSet r = s.executeQuery();
        //5. Parse result set
        if(r.next()){//Did you find the data? If you find it, you can log in successfully
            System.out.println("Login succeeded~");
        }else{
            System.out.println("User name or password input error,Login failed~");
        }
        //6. Close resources
        r.close();
        s.close();
        c.close();
    }

    //Query tb_user table data
    private static void method() throws Exception{
        //Call the method of the tool class
        Connection c = JDBCUtils.getConnection();
        //3. Obtain the transmitter
        Statement s = c.createStatement();
        //4. Execute SQL
        ResultSet r = s.executeQuery("select * from tb_user");
        //5. Parse result set
        while(r.next()){//Judge whether r has data
            //Get r's data
            int a = r.getInt("id");//Gets the value of the id field in the table
            String b = r.getString("name");//Get the value of the name field in the table
            String c1 = r.getString("password");//Gets the value of the password field in the table
            System.out.println(a+b+c1);
        }
        //6. Release resources
        r.close();//Release result set
        s.close();//Release the transmitter
        c.close();//Release connector
    }
    /* Simulate user login
1,Initiate SQL: select * from tb_user where name ='Jack 'and password ='321'
2,Judge the result. If there is a result, the login succeeds, and if there is no result, the login fails

Problem: SQL attack / SQL injection,
In essence, special symbols (#, some conditions are commented out) appear in SQL statements, which leads to the change of SQL semantics
 Solution: Statement is a low-level transmitter, which is unsafe and inefficient
      Change to PreparedStatement advanced, safe
    */
    private static void method2() throws Exception {
        //1. Register the driver 2. Obtain the connection
        Connection c = JDBCUtils.getConnection();
        //3. Obtain the transmitter
        Statement s = c.createStatement();
        //4. Execute SQL
        String a = new Scanner(System.in).nextLine();//user name
        String b = new Scanner(System.in).nextLine();//password
        //If the string is spliced dynamically, the data is in the middle position "+ a +"
//        String sql="select * from tb_user where name='jack' and password='321'" ;
String sql="select * from tb_user where name='"+a+"' and password='"+b+"'" ;
        ResultSet r = s.executeQuery(sql);
        //5. Parse result set
        if(r.next()){//Did you find the data? If you find it, you can log in successfully
            System.out.println("Login succeeded~");
        }else{
            System.out.println("User name or password input error,Login failed~");
        }
        //6. Close resources
        r.close();
        s.close();
        c.close();
    }
}

1.4.4 simulated user login - version after replacing the transmitter

package jdbc;

import java.sql.*;
import java.util.Scanner;

public class Test4 {
    public static void main(String[] args) throws Exception {
        method();
    }
    //Query tb_user table data
    private static void method() throws Exception {
        //1. Get the connection to the database (register driver + get connection)
        Connection c = JDBCUtils.getConnection();
        //2. Execute SQL
        System.out.println("enter one user name");
        String a = new Scanner(System.in).nextLine();//user name
        System.out.println("Please input a password");
        String b = new Scanner(System.in).nextLine();//password
        //SQL skeleton: replace the position of parameters with?, which is called placeholder; benefits: simplicity (avoiding SQL splicing parameters)
        String sql = "select * from tb_user where name=? and password=? ";
        //3. Get the transmitter
        /* jack'# You can log in without entering a password. SQL attack / SQL injection will occur. The # number changes the SQL semantics and comments out some conditions
         * Solution: Statement is a low-level transmitter, unsafe and inefficient
         * Replace with PreparedStatement advanced, safe
         * */
        //Statement s = c.createStatement();
        PreparedStatement s = c.prepareStatement(sql);
        //4. Set SQL parameter - setxxx() method
        s.setString(1,a);//Two parameters are required in parentheses. The first is the position and the second is the value
        s.setString(2,b);//The index of? And the value to be set for
        ResultSet r = s.executeQuery();
        //5. Parse result set
        if(r.next()) {//Did you find the data? If you find it, you can log in successfully
            System.out.println("Login succeeded");
        }else{
            System.out.println("User name or password input error, login failed");
        }
        //6. Release resources
        r.close();
        s.close();
        c.close();
    }
}

1.5 SQL injection

package jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

//JDBC exercise
public class Test5 {
    public static void main(String[] args) throws Exception {
        method();//Query < 100 data of department table
        metod2();//Insert data into dept table
    }

    private static void metod2() throws Exception {
        Connection c = JDBCUtils.getConnection();
        //When inserting data, you should set several question marks depending on the values of several fields in the table
        String sql = "insert into dept values(?,?,?)";
        PreparedStatement p = c.prepareStatement(sql);
        //Set SQL parameters
        p.setObject(1,666);
        p.setObject(2,"Software testing department");
        p.setObject(3,"Great Shanxi");
        /*setInt Data types are required. Different types cannot be set. You can also use object instead*/
        //Execute SQL
        int i = p.executeUpdate();
        //Will T0D0 return the result set? What did you return?
        System.out.println("The number of rows affected is:"+i);
        //close resource
        p.close();
        c.close();


    }

    //Query all data of department table
    private static void method() throws Exception {
        //Using tool classes to obtain database connections
        Connection c = JDBCUtils.getConnection();
        //Get transmission data
        String sql = "select * from dept where depno < ?";
        PreparedStatement s = c.prepareStatement(sql);
        //Set parameters
        s.setInt(1,100);
        ResultSet r = s.executeQuery();
        //Processing result set
        while (r.next()){//Does next() judge whether there is data
            //get data
            String str = r.getString("dname");
            //Get the value of the dname column and print it
            System.out.println(str);

        }
        //Release resources
        r.close();
        s.close();
        c.close();
    }
}

2 JDBC FAQs

Is 2.1 Class.forName useful?

Class.forName can specify the class classpath to dynamically create object instances, but JDBC does not return objects. What's the use of writing this sentence? Look at the source code of java.sql.Driver.class to find the truth. It turns out that it uses static code blocks to create objects.

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }

I wrote it and created it. What about not writing it? How can it be executed without writing?

Java provides SPI mechanism, and users can configure classes by themselves. This support is introduced into JDBC drivers of higher versions. If the user specifies the driver by using the Class.forName method, if this sentence is not written, Java will automatically find the startup class in the META-INF/services/java.sql.Driver file.

2.2 driver version

Different versions of mysql require different drivers

Mysql5.0x mysql-connector-java-5.1.32.jar

Mysql8.0x mysql-connector-java-8.0.21.jar

• The Driver becomes: com.mysql.cj.jdbc.Driver, with CJ in the middle
• url must be time zone parameter: serverTimezone=Asia/Shanghai

2.3 Chinese garbled code

Add a parameter to url: characterEncoding=utf8 to prevent Chinese garbled code

String url ="jdbc:mysql://localhost:3306/mydb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false";

2.4 SQL injection

String condition = "Chen Qiang";
String condition = "Chen Qiang' or 1=1 or '";
String condition = "Chen Qiang' or true or '";

String sql = "select * from teachers where tname='" + condition+"'";

Using the 'single prime' in sql is the end character of the string, or as long as one condition is true, there is no need to judge the others. However, if the sql query is invalid due to malice, only one piece of data should be displayed, and all the results should be displayed.

SQL generated after injection:

SELECT * FROM teachers WHERE tname='Chen Qiang' OR 1=1 OR ''

Imagine if it was a financial statement, you could only look at your own information. As a result, you looked at everyone's information. As a result, the new employee's salary is higher than yours. You're not angry.

2.5 PreparedStatement statement

SQL injection solution:

Replace the Statement object with a PreparedStatement object

sql = "select * from teachers where tname=?";			#Parameter use question mark
PreparedStatement stat = cn.prepareStatement(sql); 		#Object replacement
stat.setString(1, condition);					#Corresponding parameter type, which question mark
ResultSet rs = stat.executeQuery();			#Remove sql parameters

Results after PS:

SELECT * FROM teachers WHERE tname='Chen Qiang\' or 1=1 or \''

Using escape characters, malicious characters in SQL are shielded. It not only solves the problem of SQL injection and makes the system safe, but also has a great advantage. It is a precompiled statement. The main part of mysql is pre compiled and cached. Next time, this part does not need to be parsed, but only spell in conditions, so the execution efficiency is much higher than that of statement. SQL statements should be compiled every time.

2.6 common errors

2.6.1 java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

Error reason:

• jar is not imported and there is no builder path

• Class.forName(“com.mysql.jdbc.Driver”); String spelling error

2.6.2 Unknown database mydb;

Error reason:

• Misspelled database name

2.6.3 Access denied for user 'root123'@'localhost' (using password: YES)

Error reason:

Database user name or password error

2.6.4 Table 'py-school-db.mydb' doesn't exist

Error reason:

The table does not exist, or the table name may be written incorrectly