Каков наилучший подход с использованием JDBC для параметризации предложения IN?

Скажи, что у меня есть запрос в форме

SELECT * FROM MYTABLE WHERE MYCOL in (?)

И я хочу параметризировать аргументы в.

Есть ли прямой способ сделать это в Java с помощью JDBC таким образом, чтобы он мог работать с несколькими базами данных без изменения самого SQL?

Самый близкий вопрос, который я нашел, касался C#, мне интересно, есть ли что-то другое для Java/JDBC.

Источник

10 ответов

Там действительно нет простого способа сделать это в JDBC. Некоторые драйверы JDBC, кажется, поддерживают PreparedStatement#setArray() на IN пункт. Я только не уверен, какие из них.

Вы можете просто использовать вспомогательный метод с 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 единиц.

Источник

Поскольку никто не отвечает на случай большого предложения IN (более 100), я приведу свое решение этой проблемы, которое хорошо работает для JDBC. Короче я заменяю IN с INNER JOIN на столе ТМП.

Я делаю то, что я называю таблицей пакетных идентификаторов, и в зависимости от СУБД я могу сделать это таблицей tmp или таблицей памяти.

Таблица состоит из двух столбцов. Один столбец с идентификатором из предложения IN и другой столбец с идентификатором пакета, который я генерирую на лету.

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

Прежде чем выбрать, вы помещаете свои идентификаторы в таблицу с заданным идентификатором партии. Затем вы просто заменяете исходное предложение IN запроса на INNER JOIN, совпадающее с вашей таблицей идентификаторов, ГДЕ batch_id равно вашему текущему пакету. После того, как вы сделали, удалите записи для вашей партии.

Источник

Стандартный способ сделать это (если вы используете Spring JDBC) - это использовать класс http://static.springsource.org/spring/docs/3.0.x/javadoc-api/index.html?org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.html.

Используя этот класс, можно определить 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;
}
Источник

Я решил это, построив строку SQL с таким количеством ? поскольку у меня есть ценности, чтобы искать.

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

Сначала я искал тип массива, который можно передать в оператор, но все типы массивов JDBC зависят от поставщика. Так что я остался с несколькими ?,

Источник

Я получил ответ от docs.spring(19.7.3)

Стандарт SQL позволяет выбирать строки на основе выражения, включающего переменный список значений. Типичным примером будет выбор * из T_ACTOR, где id в (1, 2, 3). Этот список переменных напрямую не поддерживается для подготовленных операторов стандартом JDBC; Вы не можете объявить переменное число заполнителей. Вам нужно несколько вариантов с желаемым количеством подготовленных заполнителей, или вам нужно генерировать строку SQL динамически, когда вы знаете, сколько заполнителей требуется. Поддержка именованных параметров, предоставляемая в NamedParameterJdbcTemplate и JdbcTemplate, использует последний подход. Передайте значения в виде java.util.List примитивных объектов. Этот список будет использоваться для вставки необходимых заполнителей и передачи значений во время выполнения оператора.

Надеюсь, это поможет вам.

Источник

AFAIK, в JDBC нет стандартной поддержки для обработки коллекций как параметров. Было бы здорово, если бы вы могли просто передать список, и это было бы расширено.

Доступ Spring JDBC поддерживает передачу коллекций в качестве параметров. Вы могли бы посмотреть, как это делается, чтобы вдохновить на надежное кодирование.

См. Авторасширяющиеся коллекции как параметры JDBC

(В статье сначала обсуждается Hibernate, а затем продолжается обсуждение JDBC.)

Источник

Смотрите мое испытание и его успех. Говорят, что размер списка потенциально ограничен. List l = Arrays.asList(new Integer[]{12496,12497,12498,12499}); Параметр карты = 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);
Источник

Сормула делает это простым (см. Пример 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());
}
Источник

Существуют разные альтернативные подходы, которые мы можем использовать.

  1. Выполнять одиночные запросы - медленно и не рекомендуется
  2. Использование хранимой процедуры - для конкретной базы данных
  3. Динамическое создание PreparedStatement Query - хорошая производительность, но лишенные преимущества кэширования и требуют перекомпиляции
  4. Использование NULL в PreparedStatement Query - я думаю, что это хороший подход с оптимальной производительностью.

Проверьте более подробную информацию об этом здесь.

Источник

Один из способов, который я могу придумать, - это использовать java.sql.PreparedStatement и немного фальсификации присяжных.

PreparedStatement prepareStmt = conn.prepareStatement("ВЫБРАТЬ * ИЗ MYTABLE ГДЕ МИКОЛ в (?)");

... а потом...

prepareStmt.setString(1, [ваши строковые параметры]);

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

Источник
Другие вопросы по тегам