Java параметры sql запроса

Запросы с параметрами

Hibernate позволяет передавать параметры запросам. Таким образом сильно упрощается вся работа с запросами и базой данных.

Очень редко можно встретить неизменяемые запросы. Вначале ведь кажется, что тебе нужно вернуть из базы просто список товаров. А потом выясняется, что нужен актуальный список товаров для определенного пользователя на определенную дату. Отсортированный по нужному полю, и еще не весь список, а определенная страница: например, товары с 21 по 30-й.

И именно эту задачу решают параметризированные запросы. Ты пишешь на HQL запрос, а потом значения, которые можно поменять заменяете на “специальные имена” – параметры. И потом отдельно при выполнении запроса можно передать значения этих параметров.

Давай напишем HQL-запрос, который будет возвращать все задачи для пользователя с определенным именем:

 from EmployeeTask where employee.name = "Иван Иванович" 

А теперь заменим имя на параметр:

 from EmployeeTask where employee.name = :username 

И вот как будет выглядеть наш Java-код для поиска задач:

 String hql = "from EmployeeTask where employee.name = :username"; QueryEmployeeTask> query = session.createQuery( hql, EmployeeTask.class); query.setParameter("username", "Иван Иванович"); ListEmployeeTask> resultLIst = query.list(); 

Также вместо имени параметр, можно использовать просто номер:

 String hql = "from EmployeeTask where employee.name = :1"; QueryEmployeeTask> query = session.createQuery( hql, EmployeeTask.class); query.setParameter(1, "Иван Иванович"); ListEmployeeTask> resultLIst = query.list(); 

Хотя лучше, конечно, использовать имя – читать и поддерживать такой код гораздо легче.

4.2 Метод setParameterList().

Также бывают случаи, когда значение параметра не одно, а представляет список объектов. Например, мы хотим проверить, что профессии сотрудников содержатся в определенном списке.

Как бы это можно было сделать:

 String hql = "from EmployeeTask where occupation IN (:occupation_list)"; QueryEmployeeTask> query = session.createQuery( hql, EmployeeTask.class); query.setParameterList("occupation_list", new String[] ); ListEmployeeTask> resultLIst = query.list(); 

В качестве значения параметра можно передать 4 вида списка:

• массив объектов: Object[]
• коллекция: Collection
• типизированный массив: T[]
• типизированная коллекция: Collection

Если ты решил передавать типизированную коллекцию или массив, то тебе нужно передать тип данных третьим параметром. Пример:

 String hql = "from EmployeeTask where occupation IN (:occupation_list)"; QueryEmployeeTask> query = session.createQuery( hql, EmployeeTask.class); query.setParameterList("occupation_list", new String[] , String.class); ListEmployeeTask> resultLIst = query.list(); 

При работе с параметрами-списками также можно использовать номер вместо имени параметра. Но опять-таки с именем удобнее.

4.3 Защита от SQL Injection

Одно из важнейший назначений параметров – это защита базы от SQL-инъекций. Многие программисты-новички вместо использования параметров просто бы склеили строку из нескольких частей.

Вместо того, чтобы написать так:

 String hql = "from EmployeeTask where employee.name = :username"; QueryEmployeeTask> query = session.createQuery( hql, EmployeeTask.class); query.setParameter("username", "Иван Иванович"); ListEmployeeTask> resultLIst = query.list(); 

 String hql = "from EmployeeTask where employee.name text-user">"Иван Иванович"; QueryEmployeeTask> query = session.createQuery( hql, EmployeeTask.class); ListEmployeeTask> resultLIst = query.list(); 

Никогда так не делайте! Никогда не склеивай SQL/HQL-запрос из нескольких частей. Потому что рано или поздно имя пользователя придет тебе с клиента. И злобный хакер в качестве имени клиента передаст вам строку типа «»Иван»; DROP TABLE user;»

И тогда твой запрос к базе данных примет вид:

 from EmployeeTask where employee.name = "Иван"; DROP TABLE user; 

И это еще хорошо, если твои данные просто удалят. Можно ведь написать и так:

 from EmployeeTask where employee.name = "Иван"; UPDATE user SET password = '1' WHERE user.role = 'admin' 

 from EmployeeTask where employee.name = "Иван"; UPDATE user SET role = 'admin' WHERE user.id = 123; 

Источник

Передать параметр в jdbc

Такая задача: у меня есть массив типа String, содержащий коды клиентов которые я получаю методом getCodeClient. Потом в базе oracle мне нужно получить таблицу с параметрами этих клиентов, таблица строиться на основании полученных выше кодов. я по идее должен преобразовать массив в такой список <'code012','code876','code123', ит.д.>как передать этот список в jdbc чтобы потом использовать в запросе?

Тогда SELECT * FROM «TABLE_NAME» WHERE CODE=»code012″ or CODE=»code876″ . . Получите все строки из БД

2 ответа 2

Вынужден поправить предыдущих ораторов. Все верно кроме одного, формирование почти одинаковых запросов без применения PreparedStatement является антипаттерном.

Поясняю. PreparedStatement просьба о прекомпиляции устойчивого SQL выражения, так что при следующем вызове СУБД не будет заново компилировать SQL запрос, а будет только менять параметры вызова извлекая запрос из своего кэша, что значительно быстрее по времени + бонусом защита от SQL injection

String selectSQL = "SELECT * FROM TABLE_NAME WHERE CODE = ?"; String whereClause="blah-blah"; PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL); 

И вот теперь уже можно формировать строку whereClause методом предложенным @Виктор через StringJoiner или StringBuilder подставляя его в PreparedStatement и вызывая собственно сам запрос (не меняя каждый раз объект PreparedStatement ):

preparedStatement.setString(1, whereClause); ResultSet rs = preparedStatement.executeQuery(selectSQL ); 

Источник

JDBC PreparedStatement примеры

Java JDBC PreparedStatement – это особый вид объекта Java JDBC Statement с некоторыми полезными дополнительными функциями. Помните, что вам нужен оператор для выполнения запроса или обновления. Вы можете использовать Java JDBC PreparedStatement вместо Statement и пользоваться преимуществами PreparedStatement.

Основные функции Java JDBC PreparedStatement:

• Легко вставить параметры в оператор SQL.
• Легко использовать PreparedStatement с новыми значениями параметров.
• Может повысить производительность выполненных заявлений.
• Позволяет упростить пакетные обновления.

Я покажу вам, как вставить параметры в операторы SQL, а также как использовать PreparedStatement. Пакетные обновления описаны в отдельно.

Вот быстрый пример, чтобы дать вам представление о том, как это выглядит в коде:

String sql = "update people set firstname=? , lastname=? where preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, "Gary"); preparedStatement.setString(2, "Larson"); preparedStatement.setLong(3, 123); int rowsAffected = preparedStatement.executeUpdate();

Создание PreparedStatement

Прежде чем вы сможете использовать PreparedStatement, вы должны сначала создать его. Вы делаете это с помощью Connection.prepareStatement(), например так:

String sql = "select * from people where preparedStatement = connection.prepareStatement(sql);

PreparedStatement теперь готов к вставке параметров.

Вставка параметров в PreparedStatement

Везде, где вам нужно вставить параметр в ваш SQL, вы пишете знак вопроса(?). Например:

String sql = "select * from people where >Как только PreparedStatement создан(подготовлен) для вышеприведенного оператора SQL, вы можете вставить параметры в расположение знака вопроса. Это делается с помощью многих методов setXXX(). Вот пример:

preparedStatement.setLong(1, 123);

Первое число(1) – это индекс параметра, для которого нужно вставить значение. Второе число(123) – это значение для вставки в оператор SQL.

Вот тот же пример с более подробной информацией:

String sql = "select * from people where preparedStatement = connection.prepareStatement(sql); preparedStatement.setLong(123);

Вы можете иметь более одного параметра в операторе SQL. Просто вставьте более одного знака вопроса. Вот простой пример:

String sql = "select * from people where firstname=? and lastname=?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, "John"); preparedStatement.setString(2, "Smith");

Выполнение PreparedStatement

Выполнение PreparedStatement выглядит как выполнение обычного оператора. Чтобы выполнить запрос, вызовите метод executeQuery() или executeUpdate. Вот пример executeQuery():

String sql = "select * from people where firstname=? and lastname=?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, "John"); preparedStatement.setString(2, "Smith"); ResultSet result = preparedStatement.executeQuery();

Как видите, метод executeQuery() возвращает ResultSet. Итерация ResultSet описана в тексте запроса к базе данных.

String sql = "update people set firstname=? , lastname=? where preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, "Gary"); preparedStatement.setString(2, "Larson"); preparedStatement.setLong(3, 123); int rowsAffected = preparedStatement.executeUpdate();

Метод executeUpdate() используется при обновлении базы данных. Он возвращает int, который сообщает, сколько записей в базе данных было затронуто обновлением.

Повторное использование PreparedStatement

Когда PreparedStatement подготовлен, его можно использовать повторно после выполнения. Вы повторно используете PreparedStatement, устанавливая новые значения для параметров, а затем выполняете его снова. Вот простой пример:

String sql = "update people set firstname=? , lastname=? where preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, "Gary"); preparedStatement.setString(2, "Larson"); preparedStatement.setLong(3, 123); int rowsAffected = preparedStatement.executeUpdate(); preparedStatement.setString(1, "Stan"); preparedStatement.setString(2, "Lee"); preparedStatement.setLong(3, 456); int rowsAffected = preparedStatement.executeUpdate();

Это работает и для выполнения запросов, используя метод executeQuery(), который возвращает ResultSet.

Производительность PreparedStatement

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

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

Существует два уровня потенциального повторного использования для PreparedStatement.

• Повторное использование PreparedStatement драйвером JDBC.
• Повторное использование PreparedStatement базой данных.

Прежде всего, драйвер JDBC может внутренне кэшировать объекты PreparedStatement и, таким образом, повторно использовать объекты PreparedStatement. Это может сэкономить немного времени создания PreparedStatement.

Во-вторых, кэшированный синтаксический анализ и план запросов могут быть повторно использованы в приложениях Java, например, на серверах приложений в кластере, используя одну и ту же базу данных.

Вот схема, иллюстрирующая кэширование операторов в базе данных:

На диаграмме не показан кэш PreparedStatement драйвера JDBC.

Источник