什么是使用JDBC来参数化IN子句的最佳方法？

问题描述：

SELECT * FROM MYTABLE WHERE MYCOL in (?)

的查询和我想的参数参数为IN。

有没有一种简单的方法在Java中使用JDBC来做到这一点，在某种程度上，可以在不修改SQL本身的情况下处理多个数据库？

最近question I've found had to do with C#，我不知道是否有适合的Java/JDBC不同。

答

我用尽可能多的?构建SQL字符串，因为我有值，以寻找解决此。

SELECT * FROM MYTABLE WHERE MYCOL in (?,?,?,?)

首先我搜索了我可以传入语句的数组类型，但所有JDBC数组类型都是供应商特定的。所以我留在多个?。我能想到的

这就是我们现在正在做的，但我希望有一个统一的方式来做到这一点没有自定义SQL ... – Uri 2010-05-18 21:30:14

另外，如果它是像甲骨文一样，它将不得不重新解析大部分每个语句。 – orbfish 2010-07-15 14:46:41

答

一种方法是使用java.sql.PreparedStatement中和位评委的索具

的PreparedStatement preparedStmt = conn.prepareStatement（ “SELECT * FROM MYTABLE WHERE MYCOL在（）？”）;

...然后...

preparedStmt.setString（1，[您stringged PARAMS]）;

http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

这不会起作用，因为它可能会创建一个查询，比如'... WHERE MYCOL IN（'2,3,5,6'）'这不是您正在尝试执行的操作。 – Progman 2010-05-18 21:50:53

答

据我所知，有在JDBC处理集合作为参数没有标准的支持。如果你能通过一个List并且将被扩展，那将是非常棒的。

Spring的JDBC访问支持将集合作为参数传递。你可以看看这是如何安全地进行编码的灵感。

见Auto-expanding collections as JDBC parameters

（文章首先讨论了休眠，然后继续讨论JDBC）

答

有确实JDBC做到这一点没有直接的方法。部分 JDBC驱动程序似乎支持IN子句中的PreparedStatement#setArray()。我只是不确定哪些是。

你可以只使用一个辅助方法与String#join()和Collections#nCopies()来生成IN条款和其它辅助方法来与PreparedStatement#setObject()设置在循环中的所有值的占位符。

public static String preparePlaceHolders(int length) { 
    return String.join(",", Collections.nCopies(length, "?")); 
} 

public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException { 
    for (int i = 0; i < values.length; i++) { 
     preparedStatement.setObject(i + 1, values[i]); 
    } 
}

这里是你如何使用它：

private static final String SQL_FIND = "SELECT id, name, value FROM entity WHERE id IN (%s)"; 

public List<Entity> find(Set<Long> ids) throws SQLException { 
    List<Entity> entities = new ArrayList<Entity>(); 
    String sql = String.format(SQL_FIND, preparePlaceHolders(ids.size())); 

    try (
     Connection connection = dataSource.getConnection(); 
     PreparedStatement statement = connection.prepareStatement(sql); 
    ) { 
     setValues(statement, ids.toArray()); 

     try (ResultSet resultSet = statement.executeQuery()) { 
      while (resultSet.next()) { 
       entities.add(map(resultSet)); 
      } 
     } 
    } 

    return entities; 
} 

private static Entity map(ResultSet resultSet) throws SQLException { 
    Enitity entity = new Entity(); 
    entity.setId(resultSet.getLong("id")); 
    entity.setName(resultSet.getString("name")); 
    entity.setValue(resultSet.getInt("value")); 
    return entity; 
}

注意，某些数据库有IN子句中值的允许量的限制。例如，Oracle对1000个项目有这个限制。

这种方法会导致SQL注入吗？ – 2011-09-22 07:04:47

@Kaylan：没有单一代码行将用户控制的输入raw添加到SQL查询字符串中。所以绝对没有SQL注入风险的手段。 – BalusC 2011-09-22 12:51:56

好。感谢您的澄清 – 2011-10-23 14:50:34

答

看我的试用和它的成功，据说列表大小有潜在的限制。列表l =数组。asList（new Integer [] {12496,12497,12498,12499}）; Map param = Collections.singletonMap（“goodsid”，l）;

NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource()); 
    String sql = "SELECT bg.goodsid FROM beiker_goods bg WHERE bg.goodsid in(:goodsid)"; 
    List<Long> list = namedParameterJdbcTemplate.queryForList(sql, param2, Long.class);

答

sormula使得这个简单的（见Example 4）：

ArrayList<Integer> partNumbers = new ArrayList<Integer>(); 
partNumbers.add(999); 
partNumbers.add(777); 
partNumbers.add(1234); 

// set up 
Database database = new Database(getConnection()); 
Table<Inventory> inventoryTable = database.getTable(Inventory.class); 

// select operation for list "...WHERE PARTNUMBER IN (?, ?, ?)..." 
for (Inventory inventory: inventoryTable. 
    selectAllWhere("partNumberIn", partNumbers))  
{ 
    System.out.println(inventory.getPartNumber()); 
}

答

由于无人接听的大IN子句的情况下（超过100）我会扔我解决了这个问题，其很适合用于JDBC。简而言之，我将IN替换为tmp表格上的INNER JOIN。

我所做的是制作我称之为批处理ID表，并根据RDBMS我可以做一个tmp表或内存表。

该表有两列。一列带有来自IN子句的ID，另一列带有我在飞行中生成的批次ID。

SELECT * FROM MYTABLE M INNER JOIN IDTABLE T ON T.MYCOL = M.MYCOL WHERE T.BATCH = ?

之前您选择您的推IDS到表中给定的批次ID。然后，您只需将您的原始查询IN子句替换为您的ID表上的INNER JOIN匹配WHERE batch_id等于您当前的批次。完成之后，为您批量删除条目。

+1这对于大型数据集非常有效，并且不会破坏数据库 – jasonk 2012-07-08 11:31:43

嗯，是不是半连接（'IN'或'EXISTS'谓词）可能会胜过内部连接？ – 2017-03-30 20:51:06

@LukasEder YMMV与我的SQL伪代码。一如既往的测试/基准。这是一个有趣的想法。说到这一点，我应该去看看我们的实际SQL是什么。 – 2017-03-31 12:50:35

答

执行此操作的标准方法是（如果您使用Spring JDBC）是使用org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate类。

使用此类，可以将List定义为SQL参数，并使用NamedParameterJdbcTemplate来替换命名参数。例如：

public List<MyObject> getDatabaseObjects(List<String> params) { 
    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource); 
    String sql = "select * from my_table where my_col in (:params)"; 
    List<MyObject> result = jdbcTemplate.query(sql, Collections.singletonMap("params", params), myRowMapper); 
    return result; 
}

答

我们可以使用不同的替代方法。

执行单查询 - 慢，不推荐
使用存储过程 - 具体
创建的PreparedStatement查询动态数据库 - 良好的性能，但缓存的松散的利益和需要重新编译
使用PreparedStatement的查询NULL - 我认为这是一个优化性能的好方法。

查看有关这些here的更多详情。

答

得到的答复从docs.spring(19.7.3)

SQL标准允许用于选择基于包括值的变量列表中的表达式的行。一个典型的例子是select * from T_ACTOR，其中id在（1，2，3）中。这个变量列表并不直接支持JDBC标准的预处理语句;你不能声明可变数目的占位符。您需要准备好所需数量的占位符时需要多种变体，或者一旦知道需要多少占位符，就需要动态生成SQL字符串。 NamedParameterJdbcTemplate和JdbcTemplate中提供的named参数支持采用后一种方法。将这些值作为基本对象的java.util.List传递。此列表将用于插入所需的占位符并在语句执行过程中传递值。

希望这可以帮助你。

什么是使用JDBC来参数化IN子句的最佳方法？

相关推荐