使用jdbc参数化in子句的最佳方法是什么?[副本]

在jdbc中确实没有简单的方法可以做到这一点。 一些 JDBC驱动程序似乎支持 PreparedStatement#setArray() 上 IN 条款。我只是不确定是哪一个。

你可以使用helper方法 String#join() 和 Collections#nCopies() 为生成占位符 在 子句和另一个helper方法来设置循环中的所有值 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;
}

注意,有些数据库在 在 条款。例如,Oracle对1000个项目有此限制。

因为没有人回答这个案子 大in子句(大于100) 我将给出这个问题的解决方案,它对jdbc非常有效。总之,我把 IN 用一个 INNER JOIN 在tmp桌上。

我所做的是创建一个我称之为批处理id表的表,并且根据rdbms的不同,我可以创建一个tmp表或内存表。

这个表有两列。一列具有in子句中的id,另一列具有我动态生成的批处理id。

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

在您选择之前,请将您的id与给定的批处理id一起放入表中。 然后,只需将原来的查询in子句替换为id表上匹配的内部联接,其中batch_id等于当前批。完成后,删除批处理的条目。

标准的方法是(如果您使用的是spring jdbc)使用 org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate 上课。

使用此类,可以将列表定义为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;
}

我通过构造sql字符串来解决这个问题 ? 就像我要寻找的价值一样。

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

首先,我搜索了一个可以传递到语句中的数组类型,但所有jdbc数组类型都是特定于供应商的。所以我和警察呆在一起 是吗? 是的。

我得到了答案 docs.spring(19.7.3)

sql标准允许基于包含值的变量列表的表达式选择行。一个典型的例子是select*from t_actor,其中id在(1,2,3)中。JDBC标准对准备好的语句不直接支持此变量列表;不能声明占位符的变量数。您需要准备好所需数量的占位符的许多变体,或者在知道需要多少占位符后动态生成sql字符串。named parameter jdbctemplate和jdbctemplate中提供的命名参数支持采用后一种方法。将值作为基本对象的java.util.List传入。此列表将用于插入所需的占位符并在语句执行期间传入值。

希望这能帮助你。

afaik,jdbc中没有将集合作为参数处理的标准支持。如果你能给我一份名单,那就太好了。

spring的jdbc访问支持将集合作为参数传递。您可以看看如何做到这一点,以获得安全编码的灵感。

见 Auto-expanding collections as JDBC parameters

(本文首先讨论hibernate,然后讨论jdbc。)

看看我的试验和它的成功,据说列表的大小有潜在的限制。 list l=arrays.aslist(新整数[]{12496124971249812499}); 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);

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

1. 执行单个查询-速度慢,不推荐
2. 使用存储过程-特定于数据库
3. 动态创建preparedstatement查询-性能良好,但缓存的好处不明显,需要重新编译
4. 在preparedstatement查询中使用null——我认为这是一种性能最佳的好方法。

查看有关这些的详细信息 here 是的。

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());
}

我可以想到的一种方法是使用java.sql.preparedstatement和一些陪审团操纵

preparedStatement preparedStmt=conn.prepareStatement(“从myTable中选择*,其中mycol在(?)”)(二)

…然后。。。

preparedstmt.setstring(1,[字符串参数]);

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