Java sql statement query

Класс Statement

Statement используется для выполнения SQL-запросов. Существует три типа класса Statement, которые являются как бы контейнерами для выполнения SQL-выражений через установленное соединение:

• Statement, базовый;
• PreparedStatement, наследующий от Statement;
• CallableStatement, наследующий от PreparedStatement.

Все классы специализируются для выполнения различных типов запросов:

• Statement предназначен для выполнения простых SQL-запросов без параметров; содержит базовые методы для выполнения запросов и извлечения результатов.
• PreparedStatement используется для выполнения SQL-запросов с или без входных параметров; добавляет методы управления входными параметрами.
• CallableStatement используется для вызовов хранимых процедур; добавляет методы для манипуляции выходными параметрами.

Создание объектов Statement

После установления соединения Connection с базой данных, оно может использоваться для выполнения SQL-запросов. Объект Statement создается методом Connection.createStatement.

Пример :

Connection conn = DriverManager.getConnection(url, username, password); Statement stmt = conn.createStatement();

Для отправки серверу БД SQL-выражения для выполнения необходимо вызвать метод executeQuery объекта Statement и в качестве аргумента передать скрипт запроса :

ResultSet rs = stmt.executeQuery("SELECT * FROM USERS");

Методы Statement : executeQuery, executeUpdate, execute

Класс Statement содержит три различных метода выполнения SQL-выражений : executeQuery, executeUpdate и execute, которые вызываются в зависимости от текста SQL-запроса.

executeQuery

Метод executeQuery используется в запросах, результатом которых является один единственный набор значений, таких как запросов типа SELECT.

executeUpdate

Метод executeUpdate следует использовать, как для выполнения операторов управления данными типа INSERT, UPDATE или DELETE (DML — Data Manipulation Language), так и для операторов определения структуры базы данных CREATE TABLE, DROP TABLE (DDL — Data Definition Language).

Результатом выполнения операторов INSERT, UPDATE, или DELETE является изменения одной или более строк таблицы.

Результатом выполнения метода executeUpdate является целочисленное значение, определяющее, сколько строк было модифицировано. Для выражений DML, которые не оперируют со строками, возвращаемое методом executeUpdate значение всегда равно нулю.

execute

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

Объекты Statement сами по себе не «помнят» SQL-выражение. Оно передается в качестве аргумента методов Statement.executeXXX.

Необходимо отметить, что PreparedStatement, наследующий все методы Statement, имеет свои реализации методов executeQuery, executeUpdate и execute. Объекты PreparedStatement не принимают SQL-выражения в виде аргументов этих методов, так как они уже содержат прекомпилированные SQL-выражения.

CallableStatement наследуют методы от PreparedStatement без параметров. Использование аргументов в методах executeXXX объектов PreparedStatement и CallableStatement приведет к генерации ошибки SQLException.

Объекты Statement закрываются автоматически сборщиком мусора виртуальной машины Java. Тем не менее рекомендуется закрывать их явно после того, как работа с ними завершена. Закрытие объектов Statement после их использования освобождает ресурсы СУБД и позволяет избежать проблем с памятью.

Завершение выполнения запросов

Объект Statement считается завершенным (complete), если он выполнился и все его результаты были возвращены.

Для метода executeQuery, возвращающий набор данных, оператор считается завершенным, если считаны все строки соответствующего объекта ResultSet. В случае использования метода executeUpdate объект завершен сразу же после выполнения метода.

В случае вызова метода execute оператор остается не завершенным до тех пор, пока все наборы данных не будут считаны.

Использование PreparedStatement

PreparedStatement предварительно компилирует запросы, которые могут содержать входные параметры обозначенные символом ‘?’

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

PreparedStatement pstmt = null; //~~~ Чтение таблицы БД ~~~ pstmt = connection.prepareStatement( "SELECT * FROM GOODS where id > ? and id < ?"); // Определяем значения параметров pstmt.setInt(1, 2); pstmt.setInt(2, 10); // Выполнение запроса ResultSet rs = preparedStatement.executeQuery(); // Вывод результата запроса while (rs.next()) < System.out.println("" + rs.getRow() + ". " + "id = " + result2.getInt("id") + ", name = '" + rs.getString("name")); >//~~~ Запись в таблицу БД ~~~ pstmt = connection.prepareStatement( "INSERT INTO GOODS(name) values(?)"); pstmt.setString(1, "Кофе"); pstmt.executeUpdate();

Использование CallableStatement

Объект CallableStatement предоставляет унифицированный способ вызова хранимых процедур в СУБД. Вызов процедуры осуществляется с результирующим параметром и без него. Результирующий параметр — это один из типов выходных (OUT) параметров, являющийся возвращаемым значением хранимой процедуры.

Синтаксис вызова хранимой процедуры в JDBC показан ниже. Квадратные скобки означают, что то, что находится между ними, необязательно, и сами по себе не являются частью синтаксиса.

// Синтаксис для функции, возвращающей результат: // Синтаксис хранимой процедуры без параметров:

Первые две формы могут иметь переменное число аргументов на входе (параметры IN), выходе (параметры OUT) или входных и выходных параметров одновременно (INOUT-параметры). Вопросительный знак означает местоположение параметра.

Метод supportsStoredProcedures() класса DatabaseMetaData позволяет узнать, поддерживает СУБД хранимые процедуры или нет.

Создание объекта CallableStatement

Объекты CallableStatement создаются методом prepareCall объекта Connection. Пример, который создает экземпляр CallableStatement, содержащий вызов хранимой процедуры setGoodsData с двумя аргументами и без возвращаемого параметра:

CallableStatement cstmt = con.prepareCall("");

Какими именно параметрами (IN, OUT или INOUT) являются знаки вопроса — зависит от самой хранимой процедуры setGoodsData.

Входные и выходные IN- и OUT-параметры

Передача значений входных парметров объекта CallableStatement осуществляется с помощью методов setXXX, унаследованных от PreparedStatement. Типы передаваемых значений определяются тем, какой из методов setXXX используется (setString для передачи значений типа String, setInt для передачи значений типа int и т.п.).

JDBC-типы всех OUT-параметров хранимых процедур должны быть зарегистрирваны перед их вызовом. Регистрация типов данных выходного параметра производится методом registerOutParameter. Только в этом случае после вызова хранимой процедуры CallableStatement.executeQuery() можно получить результаты выполнения с помощью методов getXXX. Необходимо использовать подходящий по типу данных Java метод getXXX в соответствии с зарегистрированным JDBC-типом параметра. Другими словами, registerOutParameter использует JDBC-тип, который подходит к JDBC-типу возвращаемого из значения, а getXXX преобразует его в тип Java.

Пример регистрации выходных параметров хранимой процедуры и чтение выходных значений. В примере метод getByte извлекает байт из первого выходного параметра, а getBigDecimal возвращает объект BigDecimal (с двумя цифрами после десятичной точки) из второго выходного параметра :

CallableStatement cstmt = con.prepareCall (""); cstmt.registerOutParameter(1, java.sql.Types.TINYINT); cstmt.registerOutParameter(2, java.sql.Types.DECIMAL, 2); // Вызов хранимой процедуры cstmt.executeQuery(); // Чтение выходных данных byte x = cstmt.getByte(1); java.math.BigDecimal n = cstmt.getBigDecimal(2, 2);

Чтение выходных параметров

В связи с ограничениями некоторых СУБД для большей совместимости рекомендуется сначала считывать результаты, сгенерированные вызовом CallableStatement, а затем выходные (OUT) параметры.

Если объект CallableStatement возвращает несколько объектов ResultSet (с использованием метода execute), то ВСЕ результаты должны быть прочитаны перед первым обращением к выходным параметрам. В этом случае для того, чтобы прочитать все результаты, надо последовательно вызывать методы Statement getResultSet, getUpdateCount и getMoreResults до тех пор, пока не останется больше результатов. После этого значения выходных параметров могут быть извлечены спомощью методов CallableStatement.getXXX.

Нулевое значение в выходных параметрах, wasNull

Возращаемое значение в выходном параметре может быть NULL. При этом методы getXXX возвращают null, 0 или false, в зависимости от типа данных.

Как и в случае с ResultSet, единственным способом узнать, вернула ли процедура 0, false или NULL, является вызов метода wasNull, который возвращает true, если последнее значение, считанное одним из методов getXXX был NULL, и false иначе.

Входные/выходные параметры INOUT

Если параметр является одновременно и входным, и выходным (INOUT), то необходимо вызывать как метод setXXX, так и метод registerOutParameter. Метод setXXX устанавливает входное значение параметра, а registerOutParameter регистрирует тип выходного значения.

Типы входного и выходного значений, зарегистрированных методом registerOutParameter, должны быть одинаковыми. Для чтения выходного значения используется соответствующий метод getXXX. Например, для параметра типа byte нужно использовать метод установки значения setByte, передавать JDBC-тип данных TINYINT методу registerOutParameter и использовать getByte для чтения выходного значения.

В следующий пример демонстрирует вызов хранимой процедуры rebuildTotal с одним INOUT-параметром. Метод setByte устанавливает значение параметра в 25, которое будет передано хранимой процедуре базе данных как TINYINT. Далее метод registerOutParameter регистрирует 1-ый параметр как TINYINT. После выполнения хранимой процедуры возвращается значение типа TINYINT, которое будет считано методом getByte в виде типа byte языка Java.

CallableStatement cstmt = con.prepareCall(""); // Определение значение параметра cstmt.setByte(1, 25); // Регистрация выходного параметра cstmt.registerOutParameter(1, java.sql.Types.TINYINT); cstmt.executeUpdate(); // Чтение параметра byte x = cstmt.getByte(1);

Если хранимая процедура оформлена функцией, т.е возвращает значение не через параметры, а через оператор «RETURNS», то для вызова используйте «execute» вместо «executeUpdate».

В отличие от ResultSet, CallableStatement не может считывать большие значения последовательно (в потоке)..

Escape-последовательности в запросах Statement

SQL-выражения Statement‘ов могут включать escape-последовательности, которые сигнализирует о том, что код выражения должен обрабатываться особо. Escape-последовательности заменяются кодом, специфичным для данной СУБД. Escape-синтаксис независит от типа СУБД

Cинтаксис escape-последовательности

Escape-конструкция заключается в фигурные скобки, где определяется ключевое слово и параметры :

Ключевое слово индицирует вид Escape-конструкции.

1. escape-последовательность с символом

Операция SQL типа LIKE использует шаблонные символы «%» и «_», которые используются для извлечения информации с определенными символами. Чтобы эти символы интерпретировались в SQL-выражении без изменений, необходимо перед ними установить обратный символ слэша «\». Этот специальный символ и называется escape-символом.

Можно явно определить, какой именно из символов использовать в качестве escape-символа, если в конце запроса ввести следующую конструкцию:

В следующем коде осуществляется чтение строки, начинающейся со знака подчеркивания:

stmt.executeQuery("SELECT name FROM GOODS WHERE NAME LIKE `\_%' ;

2. escape-последовательность с функциией fn

Практически во всех СУБД есть функции для манипуляции с числами, строками, временем, датой. Эти функции могут использоваться в escape-конструкции с ключевым словом fn, именем функции и ее аргументами. Следующий пример вызывает функцию конкатенации concat с двумя аргументами:

Имя текущего пользователя БД может быть извлечено с помощью следующего вызова:

Скалярные функции могут поддерживаться различными СУБД с немного отличающимся синтаксисом, а некоторыми СУБД могут и не поддерживаться вовсе.

Для получения списка поддерживаемых СУБД функций можно использовать класс чтения метаданных DatabaseMetaData, у которого метод getNumericFunctions возвращает список имен числовых функций, разделенных запятой, а метод getStringFunctions возвращает строковые функции, и т.д.

3. escape-последовательность для работы с датой и временам

Различные СУБД отличаются форматом записи и чтения даты, времени и временного штампа, включающего дату и время (timestamp).

Формат использование escape-последовательности даты следующий:

где yyyy — это год, mm — месяц, и dd — день. Например, следующая последовательность будет заменена строкой ’28- FEB-15′, если СУБД воспринимает именно такой формат даты.

Аналогичным образом обрабатываются escape-конструкции для типов данных TIME и TIMESTAMP:

Милисекунда (.ms) в TIMESTAMP может быть опущена.

4. escape-последовательность с хранимыми процедурами

Для обращения к хранимой процедуры из JDBC используется либо call, либо ? = call.

Если СУБД поддерживает хранимые процедуры, то они могут вызываться из JDBC с помощью следующего синтаксиса escape-последовательности:

// Процедура не возвращает значение // Процедура возвращает значение

Источник