JDBC PreparedStatement SQL IN condition

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;