Xử lý mệnh đề in trong PreparedStatement hiệu quả trong

Nếu bạn đang phát triển một ứng dụng sử dụng JDBC để truy vấn database thì chúng ta đã biết rằng PreparedStatement là một trong những lựa chọn tốt nhất để triển khai truy vấn. PreparedStatement cho phép tạo ra các câu truy vấn động thông qua các tham số đầu vào thay vì Statement chỉ thực thi được các câu truy vấn tĩnh không chứa tham số đầu vào.

Ví dụ như câu truy vấn dưới đây khi sử dụng với PreparedStatement thì 3 dấu chấm hỏi (?) đại diện cho 3 tham số đầu vào.

String sql="insert into EMPLOYEE values(?,?,?)";  

Tuy nhiên đối với mệnh đề IN thì có hơi khác so với trường hợp thông thường. IN nhận vào một danh sách các giá trị để so sánh. Vì vậy số lượng dấu chấm hỏi (?) là không thể biết trước. Do vậy để làm việc với mệnh đề IN hiệu quả chúng ta sẽ xem qua một số cách mà mình sẽ liệt kê sau đây.

Chuẩn bị

Để hiểu tiện cho quá trình thực hành ở các ví dụ bên dưới chúng ta sẽ cần chuẩn bị một table Users đi kèm với dữ liệu được khởi tạo sẵn.

CREATE TABLE `Users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `email` varchar(20) NOT NULL DEFAULT '',
  `country` varchar(20) DEFAULT 'USA',
  `password` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO users (name,email,country,password) VALUES
	 ('deft','[email protected]','vn','123456'),
	 ('lind','[email protected]','en','123456'),
	 ('caller','[email protected]','ú','123456');

Tiếp theo chúng ta cần tải thư viện JDBC về sử dụng hoặc có thể dùng dependency sau trong project maven.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.13</version>
</dependency>

Thực thi từng câu truy vấn

Đây là cách đơn giản nhất để thay thế mệnh đề IN bằng các câu truy vấn đơn. Chúng ta chỉ cần thực thi nhiều lần một với truy vấn với tham số đầu vào lần lượt là các giá trị dùng trong mệnh đề IN.

import java.sql.*;

public class Main {

    private static final String QUERY = "select * from users where id = ?";
    private static final String DB_URL = "jdbc:mysql://localhost:3306/mydb";
    private static final String USER = "root";
    private static final String PASS = "123456";

    public static void showData(int[] ids) {
        try (Connection connection = DriverManager.getConnection(DB_URL, USER, PASS)) {
            PreparedStatement ps = connection.prepareStatement(QUERY);
            for (int id: ids) {
                ps.setInt(1, id);
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    System.out.println("Name: " + rs.getString("name"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static void main(String... args) {
        int[] ids = new int[]{1, 2, 3};
        showData(ids);
    }
}

Output:

Name: deft
Name: lind
Name: caller

Viết hàm xây dựng câu truy vấn

Đối với cách trên thì việc thực thi từng câu truy vấn riêng lẽ sẽ làm giảm hiệu năng đáng kể của ứng dụng. Do vậy việc xậy dựng một câu lệnh truy vấn IN có khả năng tương thích với một danh sách tham số đầu vào là một điều cần thiết.

import java.sql.*;

public class Main {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/mydb";
    private static final String USER = "root";
    private static final String PASS = "123456";

    public static void showData(int[] ids) {
        try (Connection connection = DriverManager.getConnection(DB_URL, USER, PASS)) {
            String query = createQuery(ids.length);
            PreparedStatement ps = connection.prepareStatement(query);
            for (int i = 1; i <= ids.length; i++) {
                ps.setInt(i, ids[i - 1]);
            }
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println("Name: " + rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static String createQuery(int length) {
        String query = "select * from users where id in(";
        StringBuilder queryBuilder = new StringBuilder(query);
        for (int i = 0; i < length; i++) {
            queryBuilder.append(" ?");
            if (i != length - 1) queryBuilder.append(",");
        }
        queryBuilder.append(")");
        return queryBuilder.toString();
    }

    public static void main(String... args) {
        int[] ids = new int[]{1, 2, 3};
        showData(ids);
    }
}

Output:

Name: deft
Name: lind
Name: caller

SetArray trong JDBC

Thay vì phải triển khai một hàm dùng để xây dựng câu truy vấn IN động thì chúng ta có thể sử dụng hàm setArray() cho phép truyền vào một danh sách các phần tử cho điều kiện IN. Tuy nhiên rất tiếc là MySQL không hỗ trợ phương thức này. Đối với các bạn đang sử dụng các database khác thì có thể làm như sau

import java.sql.*;

public class Main {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/mydb";
    private static final String USER = "root";
    private static final String PASS = "123456";

    public static void showData(int[] ids) {
        try (Connection connection = DriverManager.getConnection(DB_URL, USER, PASS)) {
            String query = "select * from users where id in (?)";
            PreparedStatement ps = connection.prepareStatement(query);
            Array array = connection.createArrayOf("integer", new Object[]{ids});
            ps.setArray(1, array );
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println("Name: " + rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String... args) {
        int[] ids = new int[]{1, 2, 3};
        showData(ids);
    }
}

Nguồn

https://mkyong.com/jdbc/jdbc-preparedstatement-sql-in-condition/