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

pom.xml

	<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

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

avatar