2024.4.12 Friday
Following 【WEEK7】 【DAY4】JDBC—Statement Object【English Version】

10.3.PreparedStatement Object

10.3.1.PreparedStatement can prevent SQL injection, more efficient than statement

10.3.2. Insertion

package lesson.three;

import lesson.two.utils.JdbcUtils;

import java.sql.*;

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();
            //Difference from a regular statement: uses question marks as placeholders
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (?,?,?,?,?)";

            st = conn.prepareStatement(sql);   //Pre-compile SQL: generate SQL but do not execute

            //Manually assign values to parameters
            //The syntax of set... corresponds to each position of the input function parameters and the parameters you wish to set
            st.setInt(1,4); //id
            st.setString(2,"lqf");
            st.setString(3,"987654");
            st.setString(4,"27046873@qq.com");
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));
            //new Date().getTime() means: the time calculated by the computer needs to be converted to MySQL time
            //sql.Date is database time, util.Date is Java's
            //Because the source code of setDate has the time parameter as the database type: void setDate(int parameterIndex, java.sql.Date x)
            //So, we need to use new Date().getTime() to get the timestamp (in this version, using “new java.util.Date().getTime()” does not cause an error)

            //Execute
            int i = st.executeUpdate();
            if(i > 0){
                System.out.println("Insertion successful");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

• Result
  
  

10.3.3. Deletion

package lesson.three;

import lesson.two.utils.JdbcUtils;

import java.sql.*;

public class TestDelete {
    public static void main (String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();
            //Difference from a regular statement: uses question marks as placeholders
            String sql = "DELETE FROM users WHERE id = ?";

            st = conn.prepareStatement(sql);   //Pre-compile SQL: generate SQL but do not execute

            //Manually assign values to parameters
            st.setInt(1,4); //id
            //Execute
            int i = st.executeUpdate();
            if(i > 0){
                System.out.println("Deletion successful");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

• Result
  
  
  *After completing the code, if there’s no option to run: generally means the system didn’t detect the main function -> The most basic error is a spelling mistake in the main function, check this first before searching online for other possibilities.

10.3.4. Update

package lesson.three;

import lesson.two.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestUpdate {
    public static to main(String[] args){

        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();
            //Difference from a regular statement: uses question marks as placeholders
            String sql = "UPDATE users SET `NAME` = ? WHERE id = ?";

            st = conn.prepareStatement(sql);   //Pre-compile SQL: generate SQL but do not execute

            //Manually assign values to parameters
            st.setString(1,"阿布巴卡");
            st.setInt(2,1); //id

            //Execute
            int i = st.executeUpdate();
            if(i > 0){
                System.out.println("Update successful");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

• Result
  
  
  Here’s the translation:

10.3.5. Query

package lesson.three;

import lesson.two.utils.JdbcUtils;

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

public class TestSelect {
    public static void main(String[] args){

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            //Write SQL
            String sql = "SELECT * FROM users WHERE id = ?";

            st = conn.prepareStatement(sql);   //Pre-compile SQL: generate SQL but do not execute

            //Pass parameters
            st.setInt(1,1); //id

            //Execute
            rs = st.executeQuery();
            if(rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

• Result
  

10.3.6. Preventing SQL Injection

10.3.6.1. Under normal circumstances

package lesson.three;

import lesson.two.utils.JdbcUtils;

import java.sql.*;

public class prevent_SQL_injection {
    public static void main(String[] args){
        login("Abubakar","123456"); //Under normal circumstances
//        login("''or '1=1 ","'or '1=1 "); //sql injection
    }

    //Login service
    public static void login(String username, String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();

            //SQL
            String sql = "SELECT * FROM users WHERE `NAME`=? AND `PASSWORD`=?";

            st = conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,password);

            //The result set returned after the query is saved in rs
            rs = st.executeQuery(); //The sql in the brackets should be deleted, otherwise it throws an error, but the reason is unknown
            //Print
            while (rs.next()) {
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("email"));
                System.out.println(rs.getString("password"));
                System.out.println("===========================");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.3.6.2. Result

10.3.6.3. SQL Injection Failed

Only modify the login statement

login("''or '1=1 ","'or '1=1 "); //sql injection

10.3.6.4. Result

(No results found)

10.4. Using IDEA to Connect to the Database

10.4.1. As shown below

In theory, it’s fine as long as it’s successful, but in practice, it’s almost never needed.
Create a table under the p37jdbc database and insert data through IDEA:

-- P44
-- Create user table
CREATE TABLE account(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(40),
    money FLOAT
);
-- Insert test data
INSERT INTO account(`NAME`, money) VALUES ('A', 1000);
INSERT INTO account(`NAME`, money) VALUES ('B', 1000);
INSERT INTO account(`NAME`, money) VALUES ('C', 1000);