java jdbc如何使用in_jdbc的使用拼接个参数（in）的使用方法
骑士李四记录：
在有连接池的情况下，对有多个参数的sql拼接和添加入参的方法如下。
代码一：
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.append("SELECT a, b, c" +
"FROM `table` " +
"a in ( ");
//拼接参数 ?
for ( int i = 0; i < aids.size(); i++) {
queryBuilder.append( " ?");
if (i != ids.size() - 1) {
queryBuilder.append( ",");
}
}
queryBuilder.append(") and b in (" );
for ( int i = 0; i < bids.size(); i++) {
queryBuilder.append( " ?");
if (i != bids.size() - 1) {
queryBuilder.append( ",");
}
}
queryBuilder.append(") group by a" );
try {
Connection connection = pool.getConnection();
PreparedStatement statement = connection.prepareStatement(queryBuilder.toString());
for ( int i = 1; i <= allIds.size(); i++) {
statement.setString(i, allIds.get(i - 1));
}
ResultSet rs = statement.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
JSONObject jsonObject = new JSONObject();
for (int i = 1; i <= columnCount; i++) {
if (rs.getObject(i) != null) {
jsonObject.put(metaData.getColumnLabel(i), rs.getObject(i)); //查询结果
}
}
AiEdVO resultVO = jsonObject.toJavaObject(AiEdVO.class);
logger.info("ccc resultVO: {}", resultVO);
results.add(resultVO);
}
} catch (Exception e) {
logger.error("query nc_ip list failed：{}", e.getMessage());
throw e;
}
代码二：
用 这句话替代了上面的循环拼参数
String sql = MessageFormat.format(query,ids,ids);
MessageFormat.format是根据顺序和占位符来对应插入的
String query =
"SELECT a, b, c" +
"FROM `table` " +
"a in ( {0}) and b in({1}) "
"group by a, b";
//对传入的参数用，分开
String ids = list.stream().map(s -> "\'" + s + "\'").collect(Collectors.joining(", "));
//把参数配置进去
String sql = MessageFormat.format(query,ids,ids); //in({0})和in({1})传入一样的参数
try {
Connection connection = pool.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet rs = statement.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
JSONObject jsonObject = new JSONObject();
for (int i = 1; i <= columnCount; i++) {
if (rs.getObject(i) != null) {
jsonObject.put(metaData.getColumnLabel(i), rs.getObject(i)); //查询结果
}
}
AiEdVO resultVO = jsonObject.toJavaObject(AiEdVO.class);
logger.info("ccc resultVO: {}", resultVO);
results.add(resultVO);
}
} catch (Exception e) {
logger.error("query nc_ip list failed：{}", e.getMessage());
throw e;
}