У меня есть Java-программа, которая вставляет список номеров магазина с уникальным идентификатором, называемым pilotID
в базу данных Oracle со следующим синтаксисом:
pilotDAO.insertPilotStores(pilotID, storeList);
storeList
- это List<String>
хранящий номера магазинов, а pilotID
- целое число, например 101.
Однако, когда storelist
более 999 магазинов, я получаю исключение DB в Oracle:
Caused by:
java.sql.SQLException: ORA-24335: cannot support more than 1000 columns
Запрос вставки, который используется
INSERT ALL
INTO eportal.pilot_store (pilot_id, store_nbr, last_updt_dt_tme) VALUES (96, 100, SYSDATE)
INTO eportal.pilot_store (pilot_id, store_nbr, last_updt_dt_tme) VALUES (96, 101, SYSDATE)
INTO eportal.pilot_store (pilot_id, store_nbr, last_updt_dt_tme) VALUES (96, 102, SYSDATE)
SELECT * FROM dual;
Я действительно застрял здесь. Любые предложения приветствуются.
заранее спасибо
пуджа
Проблема с INSERT ALL. Эта функция предназначена для нескольких табличных вставок (MTI), она не предназначена для вставки нескольких строк в одну таблицу. Вы можете повторно написать свой запрос, чтобы использовать отдельные инструкции вставки. Или вы можете написать так:
INSERT INTO eportal.pilot_store (pilot_id, store_nbr, last_updt_dt_tme)
select 96, 100, SYSDATE from dual UNION ALL
select 96, 101, SYSDATE from dual UNION ALL
select 96, 102, SYSDATE from dual UNION ALL
....;
Как вы заметили, INSERT ALL не работает хорошо для большого количества строк. Вместо этого вы можете использовать UNION ALL
INSERT INTO
eportal.pilot_store (pilot_id, store_nbr, last_updt_dt_tme)
select 96, 100, SYSDATE from dual
union all
select 96, 101, SYSDATE from dual
union all
select 96, 102, SYSDATE from dual
Вы можете переписать запрос с помощью StringBuilder следующим образом:
StringBuilder sb = new StringBuilder("INSERT INTO eportal.pilot_store (pilot_id, store_nbr, last_updt_dt_tme) ");
String unionAll = " union all ";
for(int i = 100; i < 103; i++) {
sb.append("select 96,").append(i).append(", SYSDATE from dual").append(unionAll);
}
sb.delete(sb.length() - unionAll.length(), sb.length());
sb.append(";");
Вы получаете максимальное количество столбцов в базе данных. вам, скорее всего, придется изменить это в подготовленный оператор и называть его несколько раз. каждый "в...." считается одним столбцом оракула, я бы рискнул, если у вас есть
INSERT ALL
INTO eportal.pilot_store (....)
999 more rows
SELECT * FROM dual;
то да, действительно, это не сработает.
вместо этого попробуйте это (адаптированный из этого вопроса)
public void insertStores(int pilotId, List<Store> stores) throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = database.getConnection();
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement("INSERT INTO eportal.pilot_store (pilot_id, store_nbr, last_updt_dt_time) values (?, ?, SYSDATE)");
// ofcourse adapt to use your own list of store id etc.
for (Store store : stores) {
preparedStatement.setInt(1, pilotId);
preparedStatement.setInt(2, store.getNumber());
preparedStatement.addBatch();
}
preparedStatement.executeBatch();
connection.commit();
} catch (SQLException e) {
connection.rollback();
throw e;
} finally {
close(preparedStatement);
close(connection);
}
}
Вы можете использовать объемную вставку, а не вставлять все
В Java это будет что-то вроде этой пакетной обработки Oracle JDBC
Преимущества - простой запрос вставки, отличная производительность.
Использование литералов в запросе не является правильным выбором для Oracle (особенно для запросов, которые выполняются часто), потому что oracle будет анализировать запрос каждый раз и будет беспорядок в общем пуле.