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;
}
参考文献
翻译自: https://mkyong.com/jdbc/jdbc-preparedstatement-sql-in-condition/