JDBC — хранимая процедура

Мы узнали, как использовать хранимые процедуры в JDBC, обсуждая главу JDBC — Утверждения . Эта глава аналогична этому разделу, но она даст вам дополнительную информацию о escape-синтаксисе JDBC SQL.

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

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

Предположим, вам нужно выполнить следующую хранимую процедуру Oracle:

CREATE OR REPLACE PROCEDURE getEmpName 
   (EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS
BEGIN
   SELECT first INTO EMP_FIRST
   FROM Employees
   WHERE ID = EMP_ID;
END;

ПРИМЕЧАНИЕ. Вышеописанная хранимая процедура была написана для Oracle, но мы работаем с базой данных MySQL, поэтому давайте создадим такую ​​же хранимую процедуру для MySQL, как описано ниже, для ее создания в базе данных EMP:

DELIMITER $$

DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$
CREATE PROCEDURE `EMP`.`getEmpName` 
   (IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
   SELECT first INTO EMP_FIRST
   FROM Employees
   WHERE ID = EMP_ID;
END $$

DELIMITER ;

Существуют три типа параметров: IN, OUT и INOUT. Объект PreparedStatement использует только параметр IN. Объект CallableStatement может использовать все три.

Вот определения каждого —

В следующем фрагменте кода показано, как использовать метод Connection.prepareCall () для создания экземпляра объекта CallableStatement на основе предыдущей хранимой процедуры.

CallableStatement cstmt = null;
try {
   String SQL = "{call getEmpName (?, ?)}";
   cstmt = conn.prepareCall (SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   . . .
}

Строковая переменная SQL представляет хранимую процедуру с параметрами-заполнителями.

Использование объектов CallableStatement очень похоже на использование объектов PreparedStatement. Вы должны привязать значения ко всем параметрам перед выполнением оператора, иначе вы получите исключение SQLException.

Если у вас есть параметры IN, просто следуйте тем же правилам и методам, которые применяются к объекту PreparedStatement; используйте метод setXXX (), который соответствует типу данных Java, который вы привязываете.

Когда вы используете параметры OUT и INOUT, вы должны использовать дополнительный метод CallableStatement, registerOutParameter (). Метод registerOutParameter () связывает тип данных JDBC с типом данных, который ожидается от хранимой процедуры.

После вызова хранимой процедуры вы извлекаете значение из параметра OUT с помощью соответствующего метода getXXX (). Этот метод преобразует извлеченное значение типа SQL в тип данных Java.

Закрытие объекта CallableStatement

Так же, как вы закрываете другой объект Statement, по той же причине вы должны также закрыть объект CallableStatement.

Простой вызов метода close () сделает эту работу. Если вы сначала закроете объект Connection, он также закроет объект CallableStatement. Однако вы всегда должны явно закрывать объект CallableStatement, чтобы обеспечить правильную очистку.

CallableStatement cstmt = null;
try {
   String SQL = "{call getEmpName (?, ?)}";
   cstmt = conn.prepareCall (SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   cstmt.close();
}

Мы изучили больше деталей в коде Callable — Пример .

Синтаксис JDBC SQL Escape

Синтаксис escape дает вам гибкость в использовании специфических функций базы данных, недоступных вам при использовании стандартных методов и свойств JDBC.

Общий формат escape-синтаксиса SQL выглядит следующим образом:

{keyword 'parameters'}

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

Ключевые слова d, t, ts

Они помогают идентифицировать литералы даты, времени и метки времени. Как вы знаете, никакие две СУБД не представляют время и дату одинаково. Этот escape-синтаксис указывает драйверу на рендеринг даты или времени в формате целевой базы данных. Например —

{d 'yyyy-mm-dd'}

Где гггг = год, мм = месяц; дд = дата. Использование этого синтаксиса {d ‘2009-09-03’} — 9 марта 2009 г.

Вот простой пример, показывающий, как вставить дату в таблицу —

//Create a Statement object
stmt = conn.createStatement();
//Insert data ==> ID, First Name, Last Name, DOB
String sql="INSERT INTO STUDENTS VALUES" +
             "(100,'Zara','Ali', {d '2001-12-16'})";

stmt.executeUpdate(sql);

Точно так же вы можете использовать один из следующих двух синтаксисов, t или ts —

{t 'hh:mm:ss'}

Где чч = час; мм = минута; сс = второй. Использование этого синтаксиса {t ’13: 30: 29 ‘} — 13:30:29.

{ts 'yyyy-mm-dd hh:mm:ss'}

Это объединенный синтаксис вышеупомянутых двух синтаксисов для ‘d’ и ‘t’ для представления метки времени.

бежать ключевое слово

Это ключевое слово идентифицирует escape-символ, используемый в предложениях LIKE. Полезно при использовании подстановочного знака SQL%, который соответствует нулю или более символов. Например —

String sql = "SELECT symbol FROM MathSymbols
              WHERE symbol LIKE '\%' {escape '\'}";
stmt.execute(sql);

Если вы используете символ обратной косой черты (\) в качестве escape-символа, вы также должны использовать два символа обратной косой черты в литерале Java String, поскольку обратная косая черта также является escape-символом Java.

Ключевое слово

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

{fn length('Hello World')}

Это возвращает 11, длину строки символов «Hello World».

назвать ключевое слово

Это ключевое слово используется для вызова хранимых процедур. Например, для хранимой процедуры, требующей параметра IN, используйте следующий синтаксис:

{call my_procedure(?)};

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

{? = call my_procedure(?)};

oj Ключевое слово

Это ключевое слово используется для обозначения внешних объединений. Синтаксис выглядит следующим образом —

{oj outer-join}

Где external-join = таблица {LEFT | RIGHT | FULL} OUTERJOIN {таблица | external-join} в условиях поиска. Например —