JDBC PreparedStatement SQL IN条件

Java JDBC PreparedStatement示例创建一个SQL IN条件。

1. PreparedStatement +数组

在JDBC中，我们可以使用createArrayOf来创建PreparedStatement IN查询。

@Override
    public List<Integer> getPostIdByTagId(List<Integer> tagIds) {

        List<Integer> result = new ArrayList<>();

        String sql = "SELECT tr.object_id as post_id FROM wp_term_relationships tr " +
                " JOIN wp_term_taxonomy tt JOIN wp_terms t " +
                " ON tr.term_taxonomy_id = tt.term_taxonomy_id " +
                " AND tt.term_id = t.term_id " +
                " WHERE t.term_id IN (?) AND tt.taxonomy= 'post_tag'";

        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {

            Array tagIdsInArray = connection.createArrayOf("integer", tagIds.toArray());
            ps.setArray(1, tagIdsInArray);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    result.add(rs.getInt("post_id"));
                }
            }

        } catch (SQLException e) {
            logger.error("Unknown error : {}", e);
        }

        return result;

    }

但是，此array类型不是标准的JDBC选项。 如果我们使用MYSQL运行它，它将提示以下错误消息：

java.sql.SQLFeatureNotSupportedException

MySQL不支持array类型，经过测试

pom.xml

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

2 PreparedStatement +加入

该版本可在MySQL中使用，并且任何支持SQL IN条件的数据库都可以使用，只需手动连接并替换(?) ，就可以了(?)

@Override
    public List<Integer> getPostIdByTagId(List<Integer> tagIds) {

        List<Integer> result = new ArrayList<>();

        String sql = "SELECT tr.object_id as post_id FROM wp_term_relationships tr " +
                " JOIN wp_term_taxonomy tt JOIN wp_terms t " +
                " ON tr.term_taxonomy_id = tt.term_taxonomy_id " +
                " AND tt.term_id = t.term_id " +
                " WHERE t.term_id IN (?) AND tt.taxonomy= 'post_tag'";

        String sqlIN = tagIds.stream()
			.map(x -> String.valueOf(x))
			.collect(Collectors.joining(",", "(", ")"));
			
        sql = sql.replace("(?)", sqlIN);

        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    result.add(rs.getInt("post_id"));
                }
            }

        } catch (SQLException e) {
            logger.error("Unknown error : {}", e);
        }

        return result;

    }

参考文献

• PreparedStatement Java文档
• Java 8 – StringJoiner示例– Mkyong.com

标签： java jdbc mysql preparestatement

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