PreparedStatement

เหตุใดจึงควรใช้ PreparedStatement

ปัญหาการฉีดคำสั่งเอสคิวแอล

public class Main {
    public static void main(String[] args) {
        String userInput = "'; DROP TABLE users; --";
        // Dangerous: never do this
        String unsafe = "SELECT * FROM users WHERE name = '" + userInput + "'";
        System.out.println("Injected SQL becomes:");
        System.out.println(unsafe);
    }
}

ตัวแทนค่า

คำค้นที่ปลอดภัย

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

public class Main {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/shop";
        String sql = "SELECT id FROM users WHERE name = ?";
        try (Connection conn = DriverManager.getConnection(url, "app", "secret");
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, "Ada");
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) System.out.println(rs.getInt("id"));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

การกำหนดค่าหลายพารามิเตอร์

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

public class Main {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/shop";
        String sql = "INSERT INTO users(name, age, active) VALUES (?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(url, "app", "secret");
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, "Grace");
            ps.setInt(2, 42);
            ps.setBoolean(3, true);
            int rows = ps.executeUpdate();
            System.out.println("Inserted " + rows + " row");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

การจัดการ NULL

การรับคีย์ที่สร้างขึ้นอัตโนมัติ

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

public class Main {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/shop";
        String sql = "INSERT INTO users(name) VALUES (?)";
        try (Connection conn = DriverManager.getConnection(url, "app", "secret");
             PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, "Linus");
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                if (keys.next()) System.out.println("New id: " + keys.getLong(1));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

การแทรกข้อมูลเป็นชุด

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

public class Main {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/shop";
        String sql = "INSERT INTO tags(label) VALUES (?)";
        String[] labels = {"java", "jdbc", "sql"};
        try (Connection conn = DriverManager.getConnection(url, "app", "secret");
             PreparedStatement ps = conn.prepareStatement(sql)) {
            for (String label : labels) {
                ps.setString(1, label);
                ps.addBatch();
            }
            int[] counts = ps.executeBatch();
            System.out.println("Batched " + counts.length + " inserts");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

การนำคำสั่งกลับมาใช้ซ้ำ

ห้ามต่อข้อความ

ขอบเขตการปิดและการใช้ซ้ำ

ตรวจสอบอย่างรวดเร็ว

สรุปทบทวน