Learning about the usage of PreparedStatement in JDBC

PreparedStatement is an interface in Java used to execute precompiled SQL statements, which inherits from the Statement interface. Compared to Statement, PreparedStatement has the following advantages: 1. Prevent SQL injection attacks: PreparedStatement uses placeholders (?) to replace parameters in the SQL statement, which can avoid SQL injection problems caused by string concatenation. 2. Improve performance: PreparedStatement can precompile SQL statements, allowing the database to cache execution plans and improve efficiency. 3. Increase readability: Using PreparedStatement separates the SQL statement from the parameters, making the code clearer. The steps to use a PreparedStatement are: 1. Create a PreparedStatement object: Use the prepareStatement() method of the Connection object to create a PreparedStatement object.

PreparedStatement pstmt = connection.prepareStatement(sql);

2. Set parameter values: Use the setXXX() method of the PreparedStatement object to set the values of parameters in the SQL statement, where XXX is the parameter type, such as setString(), setInt(), etc.

pstmt.setString(1, "John");

Execute the SQL statement: Use the executeUpdate() method of the PreparedStatement object to execute the SQL statement and return the number of affected rows.

int rows = pstmt.executeUpdate();

4. Release resources: Close the PreparedStatement object and database connection.

pstmt.close();
connection.close();

Here is the complete example code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class PreparedStatementExample {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        
        try {
            // 连接数据库
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/
            mydatabase", "root", "password");
            
            // 创建PreparedStatement对象
            String sql = "INSERT INTO users(name, age) VALUES(?, ?)";
            pstmt = connection.prepareStatement(sql);
            
            // 设置参数值
            pstmt.setString(1, "John");
            pstmt.setInt(2, 25);
            
            // 执行SQL语句
            int rows = pstmt.executeUpdate();
            System.out.println("受影响的行数：" + rows);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

The code above shows how to use PreparedStatement to perform insert operations, using placeholders (?) to replace parameters in the SQL statement. In actual practice, you can use PreparedStatement to perform queries, updates, deletions, and other operations as needed.