The database realizes data persistence, 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 specific implementation. Therefore, the database manufacturer realizes its own driver according to the JDBC standard. For example: MySQL driver com mysql. cj. jdbc. Driver, Oracle jdbc. OracleDriver. With this solution, java can access the data in the database.

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.

If you have questions, you can watch the video:

https://www.bilibili.com/vide...

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. The second PreparedStatement is recommended to prevent SQL injection. It also has high precompiling performance.

Use steps

• Import jar package (rich tool classes)
• Get the connection with the database (user name, password)
• Execute SQL by program
• Process results by program

idea creates the project and imports the jar package

• Create stage2 Java project
• Create the lib directory and copy the objbc6-11.1.0.7.0 driver to the lib directory
• The project references this external jar package

Introductory case

package cn.tedu.jdbc;
 
import java.sql.*;
 
//Test jdbc
//Requirement: query all data in the students table in cgb2104 library
public class Test1 {
    public static void main(String[] args) throws Exception {
        //1. Register driver
        Class.forName("com.mysql.jdbc.Driver");
        //2. Obtain the connection with the database
//String url= "jdbc:mysql://localhost:3306/cgb2104?characterEncoding=utf8 "; / / specify which database to connect to
String url= "jdbc:mysql:///cgb2104?characterEncoding=utf8 "; / / specify which database to connect to
        String user= "root" ; //User name used
        String pwd= "root" ; //Password used
        Connection conn = DriverManager.getConnection(url, user, pwd);
        //3. Get the transmitter and execute SQL
        Statement st = conn.createStatement();
        //4. Execute SQL
        ResultSet rs = st.executeQuery("select * from students");
        //5. Parsing result set
        while( rs.next() ){//next() determines whether there is data in the result set
            for (int i = 1; i <= 5 ; i++) {
                //Get the value of each column and print it
                System.out.println( rs.getString(i) );
            }
        }
        //6. Release resources
        rs.close(); //Close result set
        st.close();//Turn off the transmitter
        conn.close();//Close connection
    }
}

SQL injection

/*Prepare the user2 table (id/name/password) and prepare the data
    CREATE TABLE `user2` (
          `id` int(11)  PRIMARY KEY  auto_increment,
          `name` varchar(10) default NULL,
          `password` varchar(10) default NULL
    ) ;
 */
//Requirements: use jdbc to query the user table in cgb2104 library according to user name and password
//SQL injection attack problem
private static void login() {
    try{
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql:///cgb2104?characterEncoding=utf8";
        Connection conn = DriverManager.getConnection(url, "root", "root");
        Statement st = conn.createStatement();
// String sql ="select * from user2 where name='jack' and password='123456'";// It's dead
 
        String user = new Scanner(System.in).nextLine();//User enters' jack '#
        String pwd = new Scanner(System.in).nextLine();
        //SQL injection attack: it is essentially because special symbols #, which change the SQL semantics, appear in the SQL statement
String sql ="select * from user2 where name='"+user+"' and password='"+pwd+"'";
        ResultSet rs = st.executeQuery(sql);//Execute the SQL of the query and return the result set
        if(rs.next()){
            System.out.println("Login successful~");
        }else{
            System.out.println("Login failed~");
        }
        st.close();
        conn.close();
    }catch(Exception e){
        e.printStackTrace();//Print abnormal information directly
        //System.out.println("execution failed...)// go online
    }
 
}

Solutions for SQL injection

//Solutions to SQL injection attacks
private static void login2() {
    try{
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql:///cgb2104?characterEncoding=utf8";
        Connection conn = DriverManager.getConnection(url, "root", "root");
//            Statement st = conn.createStatement(); No, it's not safe. It will be attacked by SQL
 
        String user = new Scanner(System.in).nextLine();//User enters' jack '#
        String pwd = new Scanner(System.in).nextLine();
        //? It's called placeholder, the skeleton of SQL
String sql ="select * from user2 where name=? and password=?";
        //First send the SQL skeleton to the database for execution
        PreparedStatement ps = conn.prepareStatement(sql);
        //To SQL? Set parameters
        ps.setString(1,user);//For the first one? The setting value is user
        ps.setString(2,pwd);//For the second one? The setting value is pwd
        
        ResultSet rs = ps.executeQuery();//Execute the spliced SQL and return the result set
 
        if(rs.next()){
            System.out.println("Login successful~");
        }else{
            System.out.println("Login failed~");
        }
        ps.close();
        conn.close();
    }catch(Exception e){
        e.printStackTrace();//If there is an exception, print the exception information directly
        //System.out.println("execution failed...)// go online
    }
}

JDBC FAQs
Class. Is 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 Java sql. Driver. The source code of class finds 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 higher version drivers. If the user uses class The forname method specifies its own driver. If this sentence is not written, Java will automatically go to meta-inf / services / Java sql. Boot class not found in driver file.

Driver version
Different versions of mysql need different drivers

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

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

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

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

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 'single prime' in sql is the terminator 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 formed after injection:

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

Let's imagine that if it was a financial statement, you could only see your own information. As a result, you saw everyone's information. As a result, the new employee's salary is higher than yours. You're not angry.

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, the 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.

Common errors

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
Error reason:

1) jar is not imported and there is no builder path

2)Class.forName("com.mysql.jdbc.Driver"); String spelling error

Unknown database mydb;
Error reason:

Misspelled database name

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

Database user name or password error

Table 'py-school-db.mydb' doesn't exist
Error reason:

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