JDBC PreparedStatement SQL IN condition

By mkyong | Updated: March 20, 2019

Viewed: 12,417 (+150 pv/w)

Tags: java jdbc mysql preparedstatement

Java JDBC PreparedStatement example to create a SQL IN condition.

1. PreparedStatement + Array

In JDBC, we can use createArrayOf to create a PreparedStatement IN query.

	@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;

    }

But, this array type is not a standard JDBC options. If we run this with MYSQL, it will prompt the following error message :

java.sql.SQLFeatureNotSupportedException

MySQL doesn’t support array type, tested with

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

2 PreparedStatement + Join

This version works in MySQL, and any database supporting SQL IN condition, no magic, just a manual join and replace the (?)

@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;

    }

References

• PreparedStatement Java docs
• Java 8 – StringJoiner example – Mkyong.com