Подготовленный оператор — использование функции как части предложения where

Я работаю с подготовленным оператором Java, который получает данные из базы данных Oracle. Из-за некоторых проблем с производительностью запрос использует «виртуальный столбец» в качестве индекса.

Запрос выглядит так:

String status = "processed";
String customerId = 123;
String query = "SELECT DISTINCT trans_id FROM trans WHERE status = " + status + " AND FN_GET_CUST_ID(trans.trans_id) = " + customerId;

Connection conn = getConnection();
PreparedStatement ps = null;
ResultSet rs = null;

try {
  ps = conn.prepareStatement(query);
  ps.execute();
  ...
} catch (...)

Это не работает. Наличие функции как части предложения where вызывает SQLException. Я знаю о CallableStatement и знаю, что могу сначала использовать его, а затем объединить результаты. Однако эта таблица использует FN_GET_CUST_ID(trans_id) как часть своего индекса. Есть ли способ использовать подготовленный оператор с функцией базы данных в качестве параметра запроса?


java sql oracle prepared-statement
person emulcahy    schedule 05.10.2009    source источник
comment
Статус обработан должен быть указан в кавычках. Может быть, это то, что вызывает ваше исключение, нет?   -  person quosoo    schedule 05.10.2009
comment
Наличие функции в предложении WHERE должно работать, ваша проблема, вероятно, заключается в другом. Не могли бы вы опубликовать код ошибки и сообщение (SQLException.getErrorCode)? Действительно ли запрос работает в SQL*Plus?   -  person Vincent Malgrat    schedule 05.10.2009
comment
Извините, пример, который я разместил, не был скопирован дословно. Я попытался обрезать его, чтобы удалить много лишних несущественных вещей. Отсутствующие кавычки не являются проблемой.   -  person emulcahy    schedule 05.10.2009

Ответы (3)


arrow_upward
6
arrow_downward

  1. Никогда не объединяйте аргументы для SQL в строку. Всегда используйте заполнители (?) и setXxx(column, value);.

  2. Вы получите ту же ошибку, если запустите SQL в своем любимом инструменте БД. Проблема в том, что Oracle по какой-то причине не может использовать эту функцию. Какой код ошибки вы получаете?

person Aaron Digulla    schedule 05.10.2009
comment
Проблема заключалась в том, что функция не имела публичных привилегий на выполнение. # 2 в комментарии Аарона Проблема в том, что Oracle по какой-то причине не может использовать эту функцию. это то, что в конечном итоге привело меня к тому, что я нашел проблему, поэтому отдаю должное ему. - person emulcahy; 05.10.2009

arrow_upward
1
arrow_downward

Если идентификатор клиента является числовым, сохраните его в int, а не в String. Затем попробуйте сделать следующее:

String query = "SELECT DISTINCT trans_id FROM trans WHERE status = ? AND FN_GET_CUST_ID(trans.trans_id) = ?"; 

ps = conn.prepareStatement(query); 
ps.setString(1, status);
ps.setInt(2, customerId);
ps.execute();

Помимо других преимуществ подготовленного оператора, вам не придется помнить о строковых кавычках (это, скорее всего, и является причиной вашей ошибки) и экранировании специальных символов.

person quosoo    schedule 05.10.2009

arrow_upward
0
arrow_downward

На первый взгляд запрос кажется некорректным. Вам не хватает апострофа до и после использования переменной status (при условии, что статус является столбцом varchar).

String query = "SELECT DISTINCT trans_id FROM trans 
WHERE status = '" + status + "' AND FN_GET_CUST_ID(trans.trans_id) = " + customerId;

РЕДАКТИРОВАТЬ: я не из фона Java. Однако, как сказал @Aron, лучше использовать заполнители, а затем использовать какой-либо метод для установки значений параметров, чтобы избежать SQL-инъекция.

person shahkalpeshp    schedule 05.10.2009
comment
Это правильно, как я указал в своем первом комментарии к вопросу. Однако, вероятно, даже лучше использовать заполнители в запросе, как упомянул Аарон, иначе нет никакой пользы от использования PreparedStatement. - person quosoo; 05.10.2009