Вставка нескольких строк с помощью JdbcTemplate

Как я могу выполнить следующий SQL масштабируемым способом, используя JdbcTemplate, работающий на mySQL. В этом случае масштабируемость означает:

  1. На сервере выполняется только один оператор SQL
  2. он работает для любого количества строк.

Вот заявление:

INSERT INTO myTable (foo, bar) VALUES ("asdf", "asdf"), ("qwer", "qwer")

Предположим, что у меня есть список POJO с полями foo и bar. Я понимаю, что могу просто перебрать список и выполнить:

jdbcTemplate.update("INSERT INTO myTable(foo, bar) VALUES (?, ?)", paramMap)

но это не соответствует первому критерию.

Я считаю, что я мог бы также выполнить:

jdbcTemplate.batchUpdate("INSERT INTO myTable(foo, bar) VALUES (?, ?)", paramMapArray)

но из того, что я могу сказать, это просто скомпилирует SQL один раз и выполнит его несколько раз, снова не выполнив первый критерий.

Последняя возможность, которая, кажется, удовлетворяет обоим критериям, состоит в том, чтобы просто создать SQL самостоятельно с помощью StringBuffer, но я бы хотел этого избежать.


java spring sql jdbc jdbctemplate
person Edward Dale    schedule 02.07.2010    source источник
comment
Можем ли мы сделать то же самое, используя только JDBC??   -  person Teja Kantamneni    schedule 02.07.2010
comment
Это не имеет ничего общего с JdbcTemplate или даже с JDBC. Вы не можете сделать это в SQL (во всяком случае, в стандартном SQL), поэтому вы, конечно, не можете сделать это в JdbcTemplate.   -  person skaffman    schedule 02.07.2010
comment
@skaffman: я обновил свой вопрос, чтобы сказать, что я использую mySQL. Возможно, это функция только для mySQL, но она описана на dev.mysql. com/doc/refman/5.1/en/insert.html примерно на четверть пути вниз: операторы INSERT, использующие синтаксис VALUES, могут вставлять несколько строк. Для этого включите несколько списков значений столбцов, каждый из которых заключен в круглые скобки и разделен запятыми. Пример:   -  person Edward Dale    schedule 02.07.2010
comment
@Teja: Да, это можно было бы сделать в чистом JDBC, но вопрос не в этом. Я обновил вопрос с третьей возможностью, которая заключалась бы в том, чтобы вручную построить SQL.   -  person Edward Dale    schedule 02.07.2010
comment
Если вы используете InnoDB, то пакетное обновление должно обновлять индексную таблицу только после последней вставки. Единственный выигрыш в эффективности, который вы получите от использования одного оператора, заключается в том, что вам придется отправлять меньше данных на сервер MySQL. Я сомневаюсь, что вы сможете выполнять несколько вставок со стандартным JdbcTemplate, но вы всегда можете расширить JdbcTemplate и свернуть свой собственный метод пакетной вставки, который создает строку вставки вручную.   -  person Pace    schedule 02.07.2010
comment
@Pace: Кажется, это хорошее объяснение, и никто больше не публикует ответ. Если вы перепишете это как ответ, я приму это.   -  person Edward Dale    schedule 07.07.2010

Ответы (5)


arrow_upward
5
arrow_downward

Многострочные вставки (с использованием «конструкторов значений строк») фактически являются частью стандарта SQL-92. См. http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts< /а>.

Некоторые базы данных не поддерживают этот синтаксис, но многие поддерживают. По моему опыту, Derby/Cloudscape, DB2, Postgresql и более новые выпуски Hypersonic 2.*+ поддерживают это.

Ваша забота о том, чтобы это работало как PreparedStatement, понятно, но я видел похожие случаи, когда Spring JDBC автоматически обрабатывает коллекцию элементов для определенных запросов (например, где в (?)), но я не могу поручиться за этот случай.

Я нашел некоторую, возможно, полезную информацию (не могу добавить вторую ссылку в этот пост), которая может быть полезной.

Я могу сказать вам, что, вероятно, невозможно, чтобы ваше второе требование (работает для любого количества аргументов) было выполнено в самом строгом смысле: каждая база данных, которую я использовал, накладывает ограничения на длину запроса, которые могут вступить в игру.

person Will    schedule 15.10.2010
comment
этот URL-адрес, который SO не позволил мне опубликовать выше, был: fusesource.com /docs/router/2.2/транзакции/ - person Will; 15.10.2010

arrow_upward
40
arrow_downward

Вы можете использовать BatchPreparedStatementSetter, как показано ниже.

public void insertListOfPojos(final List<MyPojo> myPojoList) {

    String sql = "INSERT INTO "
        + "MY_TABLE "
        + "(FIELD_1,FIELD_2,FIELD_3) "
        + "VALUES " + "(?,?,?)";

    getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

        @Override
        public void setValues(PreparedStatement ps, int i)
            throws SQLException {

            MyPojo myPojo = myPojoList.get(i);
            ps.setString(1, myPojo.getField1());
            ps.setString(2, myPojo.getField2());
            ps.setString(3, myPojo.getField3());

        }

        @Override
        public int getBatchSize() {
            return myPojoList.size();
        }
    });

}
person Desorder    schedule 15.01.2012
comment
У меня индекс параметра выходит за пределы допустимого диапазона (1 > количество параметров, равное 0) при использовании :NamedParameters вместо ?. Некоторое обновление для использования с NamedParameterJdbcTemplate? - person luso; 21.10.2016

arrow_upward
3
arrow_downward

Мне кажется, что в этом случае может быть полезен метод batchUpdate() JdbcTemplate (скопировано отсюда http://www.mkyong.com/spring/spring-jdbctemplate-batchupdate-example/):

//insert batch example
public void insertBatch(final List<Customer> customers){

  String sql = "INSERT INTO CUSTOMER " +
    "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";

  getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
    Customer customer = customers.get(i);
    ps.setLong(1, customer.getCustId());
    ps.setString(2, customer.getName());
    ps.setInt(3, customer.getAge() );
}

@Override
public int getBatchSize() {
    return customers.size();
}

  });
 }
person Anatolii Stepaniuk    schedule 27.07.2015
comment
Большое спасибо, это именно то, что я искал!! - person Harish Kumar Saini; 18.06.2020

arrow_upward
-1
arrow_downward

вы также можете попробовать jdbcInsert.executeBatch(sqlParamSourceArray)

   // define parameters
jdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
jdbcInsert.withTableName("TABlE_NAME");
SqlParameterSource[] sqlParamSourceArray = new SqlParameterSource[apiConsumer
        .getApiRoleIds().size()];
for (int i = 0; i < myCollection.size(); i++) 
    {
  sqlParamSourceArray[i] = new MapSqlParameterSource().addValue("COL1");
      ......................
}
// execute insert
int[] keys = jdbcInsert.executeBatch(sqlParamSourceArray);
person Sheetal Mohan Sharma    schedule 07.02.2014
comment
Этот ответ неверный! jdbcInsert.executeBatch() НЕ возвращает ключи. Он возвращает the array of number of rows affected as returned by the JDBC driver. См. javadocs. docs.spring.io/spring-framework/docs/current/javadoc-api/org/ - person Stewart; 27.01.2018

arrow_upward
-4
arrow_downward

Вы не можете сделать это в JDBC, и точка. В MySQL это просто синтаксический сахар, но эффект оператора будет таким же, как и при выполнении нескольких операторов INSERT. Таким образом, вы можете использовать пакетное обновление, и это будет иметь тот же эффект.

person Chochos    schedule 14.10.2010
comment
Неправильно. Расширенная вставка MySQL (как представлено в Вопросе) быстрее, чем пакетная вставка (где вы готовитесь заранее, но вставляете по одной строке за раз). Это НЕ синтаксический сахар в MySQL. - person Shlomi Noach; 04.07.2013