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;
}
Second example is not actually using prepared statement, no parameter passed. It is a non parameterised SQL created dynamically
Your first example doesn’t work with PostgreSQL. I haven’t tried other databases. The way to fix it, however, is to rewrite:
“t.term_id IN (?)”
as
“t.term_id = ANY(?)”
this is totally not security safe , you are just concatenating the string
MySQL :/
Thanks, nice tips