Хранимая процедура – это набор команд языка SQL, который хранится на сервере и является самостоятельным объектом базы данных (далее – БД).
Создание экземпляра CallableStatement
Допустим, нам необходимо создать следующую хранимую процедуру:
DELIMITER $
DROP PROCEDURE IF EXISTS PROSELYTE_TUTORIALS.`getDeveloperName` $
CREATE PROCEDURE PROSELYTE_TUTORIALS.`getDeveloperName`
(IN DEVELOPER_ID INT, OUT DEVELOPER_NAME VARCHAR(50))
BEGIN
SELECT name INTO DEVELOPER_NAME
FROM developers
WHERE id = DEVELOPER_ID;
END $
DELIMITER ;
Существует три типа параметров: IN, OUT, INOUT. PreparedStatement использует только IN, а CallableStatement, в свою очередь, использует все три.
Рассмотрим, что же это за параметры:
IN
Параметр, значение которого известно в момент, когда создаётся запрос. Мы назначаем параметр IN с помощью метода типа setXXX().
OUT
Параметр, значение которого возвращается SQL – запросом. Мы получаем значения из OUT с помощью методов типа getXXX().
INOUT
Параметр, который использует входные и выходные значения. Мы назначаем параметр с помощью метода типа setXXX(), а получаем значения, с помощью метода типа getXXX().
Для создания экземпляра CallabelStatement используется примерно, следующий код:
try {
String SQL = "{call getDeveloperName (?, ?)}";
} catch(SQLException e){
e.printStackTrace();
} finally {
/*do some job*/
}
Строка SQL представляет собой хранимую процедуру, с параметрами.
Схожим с PreparedStatement способом, мы, используя экземпляр CallableStatement, должны установить значения параметров.
Когда мы используем параметры типа OUT и INOUT, нам необходимо задействовать дополнительный метод registerOutParameter(). Этот метод устанавливает тип данных JDBC в тип данных процедуры.
После того как мы вызвали процедуру, мы получаем значение из параметра OUT с помощью соответствующего метода getXXX(). Этот метод преобразует полученное значение из типа данных SQL в тип данных Java.
Закрытие экземпляра CallableStatement
Когда мы закрываем наше соединение (Connection) для сохранения результатов в БД мы таким же образом закрываем и экземпляр Statement.
Для этого мы используем метод close().
Рассмотрим, как это выглядит в нашем коде:
try {
String SQL = "{call getDeveloperName (?, ?)}";
callableStatement = connection.prepareCall(SQL);
}finally {
if(callableStatement!=null){
callableStatement.close();
}
}
Для понимания того, как это работает на практике, рассмотрим пример простого приложения
Пример:
Создаём процедуру в нашей БД:
DELIMITER $
DROP PROCEDURE IF EXISTS PROSELYTE_TUTORIALS.`getDeveloperName` $
CREATE PROCEDURE PROSELYTE_TUTORIALS.`getDeveloperName`
(IN DEVELOPER_ID INT, OUT DEVELOPER_NAME VARCHAR(50))
BEGIN
SELECT name INTO DEVELOPER_NAME
FROM developers
WHERE id = DEVELOPER_ID;
END $
DELIMITER ;
Класс CallableStatementDemo
import java.sql.*;
public class CallableStatementDemo {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DATABASE_URL = "jdbc:mysql://localhost/PROSELYTE_TUTORIALS";
static final String USER = "ВАШЕ_ИМЯ_ПОЛЬЗОВАТЕЛЯ";
static final String PASSWORD = "ВАШ_ПАРОЛЬ";
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection connection = null;
CallableStatement callableStatement = null;
System.out.println("Registering JDBC driver...");
Class.forName(JDBC_DRIVER);
System.out.println("Creating connection...");
connection = DriverManager.getConnection(DATABASE_URL, USER, PASSWORD);
System.out.println("Creating callable statement...");
try {
String SQL = "{call getDeveloperName (?, ?)}";
callableStatement = connection.prepareCall(SQL);
int developerID = 1;
callableStatement.setInt(1, developerID);
callableStatement.registerOutParameter(2, Types.VARCHAR);
System.out.println("Executing procedure...");
callableStatement.execute();
String developerName = callableStatement.getString(2);
System.out.println("Developer INFO");
System.out.println("id: " + developerID);
System.out.println("Name: " + developerName);
} finally {
if (callableStatement != null) {
callableStatement.close();
}
if (connection != null) {
connection.close();
}
}
System.out.println("Thank You.");
}
}
В результате работы программы мы получим следующий результат:
/*Some System Messages*/
Registering JDBC driver...
Creating connection...
Creating callable statement...
Executing procedure...
Developer INFO
id: 1
Name: Proselyte
Thank You.
Escape – синтаксис JDBC SQL
Этот синтаксис даёт нам большую гибкость использования таких свойств БД, которые недоступны при использовании стандартных методов и свойств JDBC.
Общий формат escape-конструкции выглядит так:
{ключевое_слово, 'параметры'}
Ключевое слово escape
Это ключевое слово используется вместе с параметром LIKE, когда мы используем запись вида %***%, которая означает 0 или более символов.
Пример:
String SQL = "SELECT * FROM developers WHERE specialty LIKE '%' {escape '\'}";
statement.execute(SQL);
В этом примере мы используем символ ‘\’, как escape-символ.
Ключевое слово call
Это ключевое слово используется для вызова хранимой процедуры. Например:
{call getDeveloperName (?, ?)};
Если хранимая процедура требует параметр OUT, мы должны использовать следующий синтаксис:
{? = call some_procedure (?)};
Ключевое слово oj
Это ключевое слово используется для обозначения внешнего join’a.
Например, мы имеем outer-join, который равен:
outer-join = table {LEFT | RIGHT | FULL } OUTERJOIN {table | outer-join}
Тогда,
String SQL = "SELECT developers FROM {oj companies RIGHT OUTER JOIN projects on id = 5}";
statement.execute(SQL);
Ключевое слово fn
Это ключевое слово представляет собой скалярную функцию, которая используется СУБД. Например, мы можем использовать функцию length языка SQL, для получения длины строки:
{fn length ('This is simple string.')}
В результате мы получим 22 – длину строки This is simple string.
В этом уроке мы изучили хранимые процедуры и escape-последовательности языка SQL. Мы также рассмотрели пример простого приложения и примеры применения escape-конструкций.
В следующем уроке мы изучим работу с потоками.