ЗАНЯТИЕ №2
Изучение интерфейсов Statement,
PrepareStatement и CallableStatement.
Интерфейсы Statement, PrepareStatement и CallableStatement определяют
методы и свойства, позволяющие выдавать запросы и команды
в базу данных, а также получать результаты выполнения запросов. Они также
предусматривают методы, упрощающие процедуру преобразования типов данных JAVA-SQL.
В качестве примера можно привести тип данных integer. В java данные типа
integer не могут принимать значение NULL. В базе данных напротив пустые значения
любых типов представляются NULL значением.
Первым в иерархии стоит интерфейс Statement. Интерфейс PrepareStatement
расширяет интерфейс Statement. Интерфейс CallableStatement в свою очередь
расширяет интерфейс PrepareStatement. Производители JDBC драйверов применяют
перечисленные выше интерфейсы в классах поставляемых ими драйверов и реализуют
соответствующие методы. Таким образом, без регистрации конкретного драйвера
невозможно создать объект с использованием перечисленных выше интерфейсов.
Statement интерфейс используют в случае выполнения статических запросов
к базе данных. Такие запросы не могут содержать изменяемые параметры.
PrepareStatemet интерфейс используют в случае многократного выполнения
одного и того же запроса с динамически меняющимися параметрами.
CallableStatement интерфейс используется для доступа к хранимым в
базе данных процедурам. Этот интерфейс тоже может использоваться с динамически
изменяемыми параметрами.
Объекты, созданные с использованием данных интерфейсов, имею четыре
основных метода для выполнения запросов к базе данных:
1.
executeQuery() - используется
для выполнения команды SELECT и возвращает ResultSet.
2.
executeUpdate() - используется
при выполнении команд INSERT, UPDATE и DELETE возвращая при этом количество
обработанных ими строк, или DDL SQL предложений возвращая при этом значение
0.
3.
execute() - используется
для выполнения любых DDL или DML команд возвращая при этом один или несколько
объектов типа ResultSet и/или количество обработанных строк (в зависимости
от типа выполняемого запроса). Данный метод является наиболее гибки и в тоже
время наиболее сложным в обработке результатов запроса.
4.
executeBatch() - используется
для объединения нескольких запросов в одно обращение к базе данных. Удобен
для организации обработки транзакций.
Для использования этих методов прежде всего необходимо создать сам
объект типа Statement методом createStatement() объекта типа Connection.
Метод executeQuery(), равно как и шаги, предшествующие его выполнению, подробно
рассмотрены в материалах к занятию №1. Ниже приведен пример использования
метода executeUpdate() для дополнения учебной базы данных двумя таблицами
и заполнения их небольшим количеством демонстрационных данных.
//Занятие 2, пример
2-1
public static void createTables() throws SQLException {
String operatorsql = "CREATE TABLE операторы_связи
"
+"(ид NUMBER(3) CONSTRAINT ПК_ОС PRIMARY KEY,"
+"оператор VARCHAR(20))";
String telefonsql = "CREATE TABLE номера_телефонов
"
+"(ид_л NUMBER(9) CONSTRAINT ВК_Л REFERENCES
н_люди(ид),"
+"номер_телефона VARCHAR(20),"
+"дата_регистрации DATE,"
+"ид_ос NUMBER(3) CONSTRAINT ВК_ОС REFERENCES
операторы_связи(ид))";
try {
st.executeUpdate("DROP TABLE операторы_связи
CASCADE CONSTRAINTS");
} catch (SQLException se) {
//Игнорировать ошибку удаления таблицы
if(se.getErrorCode()==942) {
String msg = se.getMessage();
System.out.println("Ошибка при удалении таблицы:
"+msg);
}
}
//Создание таблицы операторы_связи
if(st.executeUpdate(operatorsql)==0)
System.out.println("Таблица операторы_связи
создана...");
try {
st.executeUpdate("DROP TABLE номера_телефонов");
} catch (SQLException se) {
//Игнорировать ошибку удаления таблицы
if(se.getErrorCode()==942) {
String msg = se.getMessage();
System.out.println("Ошибка при удалении таблицы:
"+msg);
}
}
//Создание таблицы номера_телефонов
if(st.executeUpdate(telefonsql)==0)
System.out.println("Таблица номера_телефонов
создана...");
}
public static void
insertData() throws SQLException {
//Загрузка данных в таблицу операторы_связи
st.executeUpdate("INSERT INTO операторы_связи VALUES(1,'Мегафон')");
st.executeUpdate("INSERT INTO операторы_связи VALUES(2,'МТС')");
st.executeUpdate("INSERT INTO операторы_связи VALUES(3,'Би Лайн')");
st.executeUpdate("INSERT INTO операторы_связи VALUES(4,'SkyLink')");
//Загрузка данных в таблицу номера_телефонов
st.executeUpdate("INSERT INTO номера_телефонов VALUES(125704,'9363636',{d
'2000-9-15'},1)");
st.executeUpdate("INSERT INTO номера_телефонов VALUES(125704,'2313131',{d
'2001-2-25'},2)");
st.executeUpdate("INSERT INTO номера_телефонов VALUES(125704,'1151515',{d
'2002-6-17'},4)");
st.executeUpdate("INSERT INTO номера_телефонов VALUES(120848,'4454545',{d
'2003-7-30'},3)");
st.executeUpdate("INSERT INTO номера_телефонов VALUES(120848,'1161616',{d
'2004-1-16'},4)");
System.out.println("Загрузка данных закончена...");
}
Для изучения примера необходимо создать класс, использующий main метод
для регистрации драйвера, создания соединения с базой данных, создания объекта
типа Statement и обработки соответствующих исключений. Для создания таблиц
и заполнения их данными необходимо использовать отдельные методы (аналогичные
приведенным в примере).
При заполнении таблицы номера_телефонов данные о дате регистрации
телефона указываются с использованием JDBC SQL escape последовательности,
которая имеет следующий формат: {ключевое_слово параметр}. В данном примере
ключевым словом является символ d, который сообщает драйверу о необходимости
преобразования указанной в качестве параметра даты в формат даты ORACLE.
Метод execute() является наиболее универсальным методом выполнения
запросов, так как он позволяет выполнять любые запросы и возвращать множественные
результаты запросов типа ResultSet и количество обновлений типа integer.
Такая универсальность ведет к определенной сложности обработки результатов,
так как заранее может быть неизвестно какое количество объектов типа ResultSet
и какое количество значений типа integer и в какой последовательности будет
получено. В любом случае метод execute() возвращает значение типа boolean.
Значение true означает, что результат выполнения запроса может быть получен
методом getResultSet() и помещен в объект типа ResultSet. После обработки
результата запроса необходимо проверить наличие следующего результата используя
метод getMoreResults(). Значение типа false означает отсутствие результата
запроса типа ResultSet. В этом случае необходимо проверить наличие количественного
значения методом getUpdateCount(). Значение большее или равное 0 означает
наличие количественного значения сделанных обновлений (например: вставленных
или обновленных строк). Если метод getUpdateCount() возвращает значение -1,
это означает, что все результаты исчерпаны и обработку можно завершать. В
общем случае алгоритм обработки результатов выполнения метода execute() можно
представить следующим фрагментом программного кода:
Statement st = conn.createStatement();
st.execute( любой SQL-запрос );
while(true) {
rs = st.getResultSet();
if (rs != null)
//Вывести результат запроса
else
//Вывести количество обработанных строк
if ((st.getMoreResults() == false) && (get.UpdateCount ==
-1))
break;
}//end of while
Для изучения этого метода необходимо создать класс, в методе main
которого выполнить следующий фрагмент кода:
//Занятие 2, пример
2-2
//Определение переменных
Connection conn = null;
Statement st = null;
ResultSet rs = null;
boolean executeResult;
//
//Начало блока обработки исключений (try)
//Регистрация драйвера, и создание соединения
//
st = conn.createStatement();
//
String sql = "INSERT INTO номера_телефонов VALUES(121018,'9999999',{d
'2000-4-20'},1)";
executeResult = st.execute(sql);
processExecute(st, executeResult);
//
sql = "SELECT фамилия, имя, номер_телефона, дата_регистрации, оператор
"
+"FROM н_люди л, номера_телефонов т, операторы_связи
о "
+"WHERE л.ид = т.ид_л AND т.ид_ос = о.ид";
executeResult = st.execute(sql);
processExecute(st, executeResult);
//
}//Блок обработки исключений (catch, finally)
}//завершение блока
System.out.println("Программа завершена.");
Обработку результатов, полученных методом execute() реализовать отдельным
метод:
public static void processExecute(Statement
st,
boolean executeResult) throws SQLException {
//Код обработки результатов
}//end of processExecute
metod
Использование метода executeBatch() тесно связано с использованием
технологии транзакций. Этот метод позволяет выполнить за одно обращение к
базе данных группу заранее подготовленных запросов. В случае неудачи выполнения
одного из запросов можно отменить (откатить) все изменения, сделанные данной
группой запросов с помощью метода rollback(). Ниже приведен фрагмент программного
кода, иллюстрирующий данную возможность:
//Занятие 2, пример
2-3
public static void
batchInsert() throws SQLException {
//Загрузка данных в таблицу операторы_связи
st.addBatch("INSERT INTO операторы_связи VALUES(1,'Мегафон')");
st.addBatch("INSERT INTO операторы_связи VALUES(2,'МТС')");
st.addBatch("INSERT INTO операторы_связи VALUES(3,'Би Лайн')");
st.addBatch("INSERT INTO операторы_связи VALUES(4,'SkyLink')");
//Загрузка данных в таблицу номера_телефонов
st.addBatch("INSERT INTO номера_телефонов VALUES(125704,'9363636',{d
'2000-9-15'},1)");
st.addBatch("INSERT INTO номера_телефонов VALUES(125704,'2313131',{d
'2001-2-25'},2)");
st.addBatch("INSERT INTO номера_телефонов VALUES(125704,'1151515',{d
'2002-6-17'},4)");
st.addBatch("INSERT INTO номера_телефонов VALUES(120848,'4454545',{d
'2003-7-30'},3)");
st.addBatch("INSERT INTO номера_телефонов VALUES(120848,'1161616',{d
'2004-1-16'},4)");
System.out.println("Пакет загрузки данных подготовлен...");
//st.clearBatch();
int[] count = st.executeBatch();
for(int i=0; i<count.length; i++) System.out.print(count[i]+"\t");
st.clearBatch();
}
Формирование пакета запросов осуществляется методом addBatch() с параметром
типа String. При необходимости, для аннулирования уже сформированного пакета
запросов можно использовать метод clearBatch(). В этом случае весь пакет
будет аннулирован и его можно начать формировать заново. Перед выполнением
пакета для использования режима транзакций необходимо отменить автоматическое
подтверждение изменений в результате выполнения каждого запроса, то есть
выполнить метод setAutoCommit(false). После выполнения метода executeBatch()
необходимо выполнить метод commit() для подтверждения сделанных в базе изменений.
В случае неудачного выполнения одного из запросов будет получено исключение
типа SQLException. В блоке обработки исключения можно использовать метод
rollback() для отмены (отката) сделанных в базе данных изменений. Для изучения
перечисленных выше методов необходимо в созданном для примера 2-1 классе заменить
метод insertDate() на метод batchInsert() данного примера и выполнить его.
Для более гибкого управления ходом выполнения транзакций существует
возможность установки логических точек отката изменений, сделанных в базе
в процессе выполнения последовательности транзакций. Для этого используется
метод setSavepoint(String Имя точки отката ). Для аннулирования ранее установленной
точки отката можно использовать метод releaseSavepoint(Savepoint svpt). В
случае возникновения исключения при выполнении транзакции можно либо отменить
все начатые изменения в базе данных с момента последнего выполнения метода
setAutoCommit(false), либо отменить их до установленной логической точки отката
методом rollback(Savepoint svpt). Ниже приведен фрагмент программного кода,
на примере которого необжодимо изучить данную возможность, дополнив его до
полноценного класса.
//Занятие 2, пример
2-4
//
import java.sql.*;
//
public class RollBackMethod
{
public
static void main(String[] args) {
//Определение переменных
//
try {
//Регистрация драйвера, и создание соединения
//
st = conn.createStatement();
conn.setAutoCommit(false);
if(!conn.getAutoCommit()) System.out.println("Auto-Commit отменен...");
//
String sql = "INSERT INTO операторы_связи VALUES(5,'TELE2')";
st.executeUpdate(sql);
//
svpt = conn.setSavepoint("Point1");
//
sql = "INSERT INTO номера_телефонов VALUES(130777,'2223322',{d '2004-2-3'},5)";
st.executeUpdate(sql);
conn.commit();
//
} catch (SQLException se) {
System.out.println("SQLException сообщение: "+se.getMessage());
System.out.println("Начнем откат изменений...");
try {
//conn.rollback();
conn.rollback(svpt);
} catch (SQLException rse) {
rse.printStackTrace();
}
System.out.println("Откат изменений закончен...");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(conn!=null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
System.out.println("Программа завершена.");
}
}
При создании объекта типа PreparedStatement указанное ему в качестве
параметра SQL-предложение передается СУБД, которая разбирает его, компилирует
и создает план выполнения запроса. Запрос сохраняется готовым для выполнения
пока остается открытым данное соединение или пока не закроется данный созданный
объект. В отличии от объекта типа Statement объект типа PreparedStatement
создается методом prepareStatement() объекта типа Connection. Передаваемое
данному методу SQL-предложение в местах подстановки переменных значений содержит
знак ?, который является частью синтаксиса и указывает на местоположение
подставляемого параметра.
Существует три типа параметров: IN, OUT и INOUT. Объекты типа PreparedStatemet
используют только параметры типа IN. Перед выполнением запроса необходимо
установить все параметры методом setXXX(), где XXX заменяется на соответствующее
типу данных параметра ключевое слово, например: setInt(), setString(), setDate(),
setTime(). Если какой либо из параметров не установлен, то будет выдано исключение
типа SQLEception. При установке параметра указывается его номер по порядку,
начиная с 1, как он встречается в SQL-предложении, и его значение. Посла
выполнения подготовленного таким образом запроса установленные параметры не
сбрасываются. Их можно сбросить методом clearParameters() или переустановить
упомянутыми выше методами setXXX().
В случае использования метода executeBatch() после каждого утановки
каждого полного комплекта параметров выполняется метод addBatch() без аргументов.
Для изучения интерфейса PrepareStatemet и применения его методов необходимо
выполнить приведенный ниже пример, дополнив его реализацией необходимых методов:
//Занятие 2, пример
2-5
//
import java.sql.*;
//
public class PrepStatDB
{
static
Connection conn = null;
public
static void main(String[] args) {
try {
//Регистрация драйвера, и создание соединения
//
createTables();
insertData();
//
System.out.println("Создание базы данных закончено...");
} catch (SQLException se) {
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(conn!=null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
System.out.println("Программа завершена.");
}
public
static void createTables() throws SQLException {
Statement st = conn.createStatement();
//Программный код создания таблиц базы данных (см. пример 1-1)
}
public
static void insertData() throws SQLException {
//Загрузка данных в таблицу операторы_связи
int count;
String sql = "INSERT INTO операторы_связи VALUES(?,?)";
PreparedStatement prst = conn.prepareStatement(sql);
//
prst.setInt(1,1); prst.setString(2,"Мегафон"); count = prst.executeUpdate();
if(count==1)
System.out.println("Запись \"Мегафон\" добавлена");
prst.setInt(1,2); prst.setString(2,"МТС"); count = prst.executeUpdate();
if(count==1)
System.out.println("Запись \"МТС\" добавлена");
prst.setInt(1,3); prst.setString(2,"Би Лайн"); count = prst.executeUpdate();
if(count==1)
System.out.println("Запись \"Би Лайн\" добавлена");
prst.setInt(1,4); prst.setString(2,"SkyLink"); count = prst.executeUpdate();
if(count==1)
System.out.println("Запись \"SkyLink\" добавлена");
prst.close();
//
//Загрузка данных в таблицу номера_телефонов
sql = "INSERT INTO номера_телефонов VALUES(?,?,?,?)";
prst = conn.prepareStatement(sql);
conn.setAutoCommit(false);
//
prst.setInt(1,125704); prst.setString(2,"9363636"); prst.setDate(3,Date.valueOf("2000-9-15"));
prst.setInt(4,1); prst.addBatch();
prst.setInt(1,125704); prst.setString(2,"2313131"); prst.setDate(3,Date.valueOf("2001-2-25"));
prst.setInt(4,2); prst.addBatch();
prst.setInt(1,125704); prst.setString(2,"1151515"); prst.setDate(3,Date.valueOf("2002-6-17"));
prst.setInt(4,4); prst.addBatch();
prst.setInt(1,120848); prst.setString(2,"4454545"); prst.setDate(3,Date.valueOf("2003-7-30"));
prst.setInt(4,3); prst.addBatch();
prst.setInt(1,120848); prst.setString(2,"1161616"); prst.setDate(3,Date.valueOf("2004-1-16"));
prst.setInt(4,4); prst.addBatch();
int[] countb = prst.executeBatch();
for(int i=0; i<countb.length; i++) System.out.print(countb[i]+"\t");
prst.clearBatch();
prst.close();
conn.commit();
System.out.println("\n"+"Загрузка данных закончена...");
}
}
При работе с данными типа CLOB и BLOB значения параметров можно задавать
и получать используя потоки ввода и вывода. При этом используются следующие
методы:
setAsciiStream() -
используется для больших ASCII объектов;
setCharacterStream()
- используется для больших UNICODE объектов;
setBinaryStream() -
используется для больших двоичных объектов.
В качестве параметров методам setXXXStream() указывается номер по
порядку, объект типа InputStream и длина объекта. Получение потока данных
производится методами getXXXStream(). Ниже приведен пример записи ASCII файла
в поле предварительно созданной таблицы readme.
//Занятие 2, пример
2-6
//
import java.io.*;
import java.sql.*;
import java.util.*;
//
public class StreamMethod
{
public
static void main(String[] args) {
Connection conn = null;
Statement st = null;
PreparedStatement prst = null;
ResultSet rs = null;
try {
//Регистрация драйвера, и создание соединения
//
st = conn.createStatement();
createReadmeTable(st);
//
File f = new File("/etc/passwd");
long fileLength = f.length();
FileInputStream fis = new FileInputStream(f);
//
String sql = "INSERT INTO readme VALUES(?,?)";
prst = conn.prepareStatement(sql);
prst.setInt(1,1);
prst.setAsciiStream(2,fis,(int)fileLength);
prst.execute();
//
fis.close();
//
sql = "SELECT * FROM readme WHERE id = 1";
rs = st.executeQuery(sql);
if(rs.next()) {
InputStream is = rs.getAsciiStream(2);
int c;
ByteArrayOutputStream baos = new ByteArrayOutputStream();
while ((c = is.read()) != -1) baos.write(c);
System.out.println(baos.toString());
}
} catch (SQLException se) {
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(conn!=null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
System.out.println("Программа завершена.");
}
public
static void createReadmeTable(Statement st) throws Exception {
String sql = "CREATE TABLE readme (id INTEGER, data LONG)";
try {
st.executeUpdate("DROP TABLE readme");
} catch (SQLException se) {
//Игнорировать ошибку удаления таблицы
if(se.getErrorCode()==942) {
String msg = se.getMessage();
System.out.println("Ошибка при удалении таблицы: "+msg);
}
}
//Создание таблицы
if(st.executeUpdate(sql)==0)
System.out.println("Таблица readme создана...");
}
}
Объекты типа CallableStatement создается методом prepareCall() объекта
типа Connection и позволяют выполнять хранимые в базе данных процедуры. При
этом могу использоваться параметры всех трех типов: IN, OUT и INOUT. SQL-предложение
при этом подготавливается в виде JDBC SQL escape последовательности:
{[? =] call proc_name[(?[,?...])]} , где:
call ключевое слово
escape-последовательности;
proc_name имя хранимой
процедуры;
? - не обязательные
параметры.
Ниже приведен пример создания хранимой процедуры, использующей функцию
человек учебной базы данных, которая по номеру сотрудника выдает его фамилию
и инициалы.
//Занятие 2, пример
2-7
//
public static void
createProc() throws SQLException {
Statement st = conn.createStatement();
String sql = "CREATE PROCEDURE get_fio "
+"(id IN NUMBER, fio OUT VARCHAR) AS "
+"BEGIN "
+"SELECT человек(id, 'И', 9) INTO fio FROM
DUAL; "
+"END;";
try {
st.executeUpdate("DROP PROCEDURE get_fio");
} catch (SQLException se) {
//Игнорировать ошибку удаления процедуры
if(se.getErrorCode()==4043) {
String msg = se.getMessage();
System.out.println("Ошибка при удалении процедуры: "+msg);
}
}
//Создание процедуры
if(st.executeUpdate(sql)==0)
System.out.println("Процедура get_fio создана...");
}
public static void
executeProc() throws SQLException {
String sql = "{call get_fio(?,?)}";
CallableStatement cst = conn.prepareCall(sql);
cst.setInt(1,121018);
cst.registerOutParameter(2,Types.VARCHAR);
cst.execute();
System.out.println("Результат запроса: "+cst.getString(2));
}
Для изучения интерфейса CallabeStatement необходимо создать класс
использующий main метод для регистрации драйвера и создания соединения с
базой данных. Для создания таблицы и заполнения ее данными необходимо использовать
отдельные методы (аналогичные приведенным в примере).