JDBC

1. JDBC Работа СУБД Oracle

2. JDBC JDBC – прикладной программный интерфейс (API) для выполнения SQL-запросов. Состоит из множества классов и интерфейсов, написанных на JAVA. Преимущества: • Легкость отсылки запросов на сервер БД • Использование JDBC API освобождает от написания приложения для каждой БД • Поддержка всехрасширений (типов, соединений, классов…) СУБД. Соответствие SQL (но, как всегда, есть исключения) • …….

3. Что может JDBC • Устанавливать соединения с БД, используя различные типы подключений • Отсылать SQL-запросы • Обрабатывать результаты

4. JDBC драйверы, поставляемые Oracle • Thin Driver – драйвер для создания клиентских приложений, не требующий установки клиента Oracle. • OCI Drivers - драйвер для создания клиентских приложений, требующий установки клиента Oracle (OCI 7, 8). • Server-side Thin Driver – драйвер, функциональность которого как у Thin Driver, но применяется для выполнения кода внутри СУБД. Код может подключаться к удаленной СУБД или реализовывать 3-х звенные приложения. • Server-side Internal Driver – драйвер, применяющийся при создании приложений внутри СУБД. Исполняет хранимые Java-процедуры и подключается к ядру СУБД, на которой работает.

5. Структура

6. Thin Driver • Драйвер на 100% написан на Java. Предназначен для апплетов, но годится и для клиентских приложений. • Драйвер платформонезависимый, не требует клиента СУБД. • Закачивается браузером и начинает работу вместе с апплетом. • Драйвер обеспечивает прямое соединение с СУБД через стек TCP/IP путем эмуляции работы библиотеки OCI8 и TTC. • Со стороны СУБД обязательно должен быть Listener. • Для работы с этим типом драйвера в браузере д.б. разрешена поддержка Java-сокетов.

7. OCI Driver • Драйвер написан на Java и Си. Предназначен для создания клиентских приложений. • Требует установки клиента Oracle и является платформозависимым. • Драйвер переводит JDBC-вызовы в вызовы OCI.Использует библиотеки OCI8, Net8, Core … • Предоставляет широкую совместимость с различными версиями СУБД (7,8i,9,10), а также более широкие возможности по работе с СУБД – named pipe …

8. Выбор соответствующего драйвера • При написании апплетов используется только Thin Driver (OCI не работает из-за вызовов Си) • Для обеспечения максимальной производительности в клиентском приложении используется OCI Driver. • При написании 3-х звенных приложений используется server-side Thin Driver. • Если код создаваемого приложения будет работать внутри СУБД, то используется server-side internal Driver.

9. Последовательность действий Для того чтобы подключиться к СУБД и выполнить запрос,необходимо написать код для выполнения следующих действий: • Import Packages • Register the JDBC Drivers • Open a Connection to a Database • Create a Statement Object • Execute a Query and Return a Result Set Object • Process the Result Set • Close the Result Set and Statement Objects • Make Changes to the Database • Commit Changes • Close the Connection

10. Шаг 1. Import Packages • import java.sql.*;// стандартный пакет JDBC • import oracle.jdbc.driver.*; // расширение JDBC для Oracle • import oracle.sql.*; // особенности языка SQL для Oracle • Необходимо переменной classpath указать положение драйвера JDBC ([ORACLE_HOME\jdbs\lib\zip-классы]). Сделать это можно 2 способами: • через переменную среды окружения • при компиляции и запуске указать ключ javac -classpath ".; ORACLE_HOME\jdbs\lib\classes12.zip; ORACLE_HOME\jdbs\lib\nls_charset12.zip"

11. Шаг 2. Register the JDBC Drivers • Данная операция осуществляется вызовом статического метода: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

12. Шаг 3.Open a Connection to a Database(1) • Для открытие соединения вызывается метод getConnection() класса DriverManager, возвращающий объект типа Connection. getConnection(String URL, String user, String password); Строка URL выглядит следующим образом: jdbc:oracle:<drivertype>:@<database>. Например, DriverManager.getConnection ("jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger"); или DriverManager.getConnection("jdbc:oracle:oci8:@(description=(address=(host=myhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))","scott", "tiger"); Connection conn=DriverManager.getConnection("jdbc:oracle:oci8:@mics","stud01","stud01");

13. Шаг 3.Open a Connection to a Database(2) Следующий вариант метода представлен ниже, где строка URL включает в себя имя пользователя и пароль getConnection(String URL); URL есть: jdbc:oracle:<drivertype>:<user>/<password>@<database> Например, DriverManager.getConnection ("jdbc:oracle:oci8:scott/tiger@myhost); или DriverManager.getConnection ("jdbc:oracle:thin:scott/tiger@myhost:1521:orcl);

14. Шаг 3.Open a Connection to a Database(3) Для передачи дополнительных параметров в строку соединения используется объект Properties. getConnection(String URL, Properties info); где URL: jdbc:oracle:<drivertype>:@<database> Например, java.util.Properties info = new java.util.Properties(); info.put ("user", "scott"); info.put ("password","tiger"); info.put ("defaultRowPrefetch","15"); info.put ("internal_logon","sysdba"); getConnection ("jdbc:oracle:oci8:@",info);

15. Шаг 4. Create a Statement Object Создание объекта Statement для описания запроса используется метод createStatement класса соединения Statement stmt = conn.createStatement();

16. Шаг 5. Execute a Query and Return a Result Set Object Для выполнения запроса к БД используется метод executeQuery класса Statement. Полученный резульат возвращается в переменную класса ResultSet, которую в дальнейшем необходимо обработать. ResultSet rset = stmt.executeQuery ("SELECT ename FROM emp");

17. Шаг 6. Process the Result Set • После получения данных в переменную ResultSet необходимо вызывать метод next()для построчного доступа к данным до тех пор, пока не будет достигнут конец данных. • Для извлечения данных используется метод getXXX()класса ResultSet, где XXX – предопределенный тип Java. while (rset.next()) System.out.println (rset.getString(1));

18. Шаг 7. Close the Result Set and Statement Objects • Необходимо после использования явно закрывать экземпляры типов Statement и ResultSet вызовом метода close(). • Драйвер не содержит метод finalizer(), поэтому очистка памяти происходит при вызове close(). • Если переменная rset имеет тип ResultSet, а stmt – Statement, то rset.close(); stmt.close();

19. Шаг 8. Make Changes to the Database • Для записи данных в базу через операции Insert или Update используется класс PreparedStatement. Объект данного класса позволяет выполнить выражение с переменным числом входных параметров. • Для подстановки значений в выражение PreparedStatement используется метод setXXX() класса PreparedStatement. Например, PreparedStatement pstmt = conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (?, ?)"); // Add LESLIE as employee number 1500 pstmt.setInt (1, 1500); // The first ? is for EMPNO pstmt.setString (2, "LESLIE"); // The second ? is for ENAME // Do the insertion pstmt.execute ();

20. Шаг 9. Commit Changes • По умолчанию, операции DML (Insert, Update, Delete) фиксируются автоматически после их выполнения. Для отключения такого режима используется команда conn.setAutoCommit(false); • Если автоматический режим отключен, то необходимо вручную выполнять операции commit и rollback: conn.commit()или conn.rollback() • Неявный commit всегда срабатывает при разрыве соединения или выполнении функций DDL.

21. Шаг 10. Close the Connection • После завершения работы необходимо закрыть соединение conn.close()

22. Пример import java.sql.*; import java.io.*; import java.awt.*; class JdbcTest { public static void main (String args []) throws SQLException { // Load Oracle driver DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@myhost:1521:ORCL","scott", "tiger"); // Query the employee names Statement stmt = conn.createStatement (); ResultSet rset = stmt.executeQuery ("SELECT ename FROM emp"); while (rset.next ()) System.out.println (rset.getString (1)); //close the result set, statement, and the connection rset.close(); stmt.close(); conn.close(); } }

23. Выражение Statement Бывает 3 видов: • Statement – для выполнения запросов Select • PreparedStatement – для операций DDL и DML,добавляет методы управления входными (IN) параметрами. • CallableStatement – для вызова хранимых процедур, добавляет методы для манипуляции выходными (OUT) параметрами. Методы выполнения выражений: • executeQuery() • executeUpdate() • execute()

24. Выражение Statement • Объект Statement используется для выполнения SQL-запросов к БД. • Предоставляет базовые методы для выполнения запросов и извлечения результатов в Result Set. Connection con = DriverManager.getConnection(url, "sunny", ""); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table");

25. Prepared Statement • Интерфейс PreparedStatement наследует от Statement и отличается от последнего следующим: • Экземпляры PreparedStatement "помнят" скомпилированные SQL-выражения. • SQL-выражения в PreparedStatement могут иметь один или более входной (IN) параметр. • Входной параметр - это параметр, чье значение не указывается при создании SQL-выражения. Вместо него в выражении на месте каждого входного параметра ставится знак ("?"). Значение каждого вопросительного знака устанавливается методами setXXX перед выполнением запроса.

26. Создание Prepared Statement • Поскольку объекты PreparedStatement прекомпилированны, исполнение этих запросов может происходить несколько быстрее, чем в объектах Statement. • Создание объекта PreparedStatementвыполняется вызовом метода prepareStatement, который сразу отправляет запрос на СУБД и подготавливает его для выполнения PreparedStatement pstmt = con.prepareStatement( "UPDATE table4 SET m = ? WHERE x = ?");

27. Передача входных параметров • Перед выполнением объекта PreparedStatement надо установить значения всех его параметров. Это делается с помощью методов setXXX, где XXX - это тип параметра. Например, pstmt.setLong(1, 123456789); pstmt.setLong(2, 100000000); • После установки параметра его можно использовать при многократном выполнении выражения до тех пор, пока он не очистится методом clearParameters. Один и тот же объект PreparedStatement может выполняться много раз, если нижестоящий драйвер или СУБД будут сохранять выражение (statement) в открытом состоянии даже после того как произойдет commit. pstmt.setString(1, "Hi"); for (int i = 0; i < 10; i++) { pstmt.setInt(2, i); int rowCount = pstmt.executeUpdate(); }

28. import java.sql.*; class InsertExample { public static void main (String args []) throws SQLException { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); String url = "jdbc:oracle:oci8:@mics"; Connection conn = DriverManager.getConnection (url, "stud01", “stud01"); Statement stmt = conn.createStatement (); try {stmt.execute ("delete from EMP where EMPNO = 1500"); } catch (SQLException e) {… } stmt.close(); PreparedStatement pstmt = conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (?, ?)"); // Add LESLIE as employee number 1500 pstmt.setInt (1, 1500); // The first ? is for EMPNO pstmt.setString (2, "LESLIE"); // The second ? is for ENAME // Do the insertion pstmt.execute (); pstmt.close(); conn.close(); } } Метод setNull позволяет отсылать значения NULL в БД как входные параметры.

29. Callable Statement (вызываемый оператор) • Объект CallableStatement предоставляет унифицированный способ вызова хранимых процедур в любой СУБД. Синтаксис вызова: CallableStatement cs1 = conn.prepareCall ( "{call proc (?,?)}" ) ; // вызов хранимой процедуры CallableStatement cs2 = conn.prepareCall ( "{? = call func (?,?)}" ) ; // вызов функции Для PL/SQL вид следующий: CallableStatement cs3 = conn.prepareCall ( "begin proc (?,?); end;" ) CallableStatement cs4 = conn.prepareCall ( "begin ? := func(?,?);end;") • Знаки ? могут быть как входными, так и выходными параметрами. Для передачи входного параметра используется метод setXXX, унаследованный от PreparedStatement.

30. Callable Statement. OUT и IN-OUT параметры • Регистрация выходных параметров осуществляется следующим образом CallableStatement cs = conn.prepareCall ("begin ? := foo(?); end;"); cs.registerOutParameter(1,Types.CHAR); cs.setString(2, "aa"); cs.executeUpdate(); String result = cs.getString(1); Для параметра IN-OUT необходимо определить его сначала методом setXXX, а затем registerOutParameter. CallableStatement cstmt = con.prepareCall( "{call reviseTotal(?)}"); cstmt.setByte(1, 25); cstmt.registerOutParameter(1, java.sql.Types.TINYINT); cstmt.executeUpdate(); byte x = cstmt.getByte(1);

31. Result Set – набор данных • ResultSet содержит все строки, удовлетворяющие условиям в SQL-выражении и предоставляет доступ к данным в этих строках посредством набора get-методов, которые организуют доступ к колонкам текущей строки. • Метод ResultSet.next используется для перемещения к следующей строке ResultSet, делая ее текущей. • Набор данных результата является таблицей с заголовками колонок и соответствующих значений, возвращенных запросом.

32. Result Set – набор данных Выполнение SQL-запроса, который возвращает коллекцию строк, в которой колонка 1 - это int, колонка 2 - String и колонка 3 - Float java.sql.Statement stmt = conn.createStatement(); ResultSet r = stmt.executeQuery("SELECT a, b, c FROM Table1"); while (r.next()) { // Напечатать значения в текущей строке. int i = r.getInt("a"); String s = r.getString("b"); float f = r.getFloat("c"); System.out.println("ROW = " + i + " " + s + " " + f); }

33. Result Set. Доступ к колонкам • ResultSet содержит т.н. курсор, который указывает на текущую строку данных. Каждый раз, когда выполняется метод next, курсор перемещается на одну строку вниз. Изначально курсор спозиционирован перед первой строкой, и первый вызов next премещает его на первую строку. • Методы getXXX предоставляют доступ к значениям в колонках в текущей строке. В пределах одной строки значения могут быть считаны в любом порядке, но ради обеспечения большей совместимости рекомендуется считывать их подряд слева направо и делать это только один раз. • Для указания колонки можно использовать либо ее имя, либо ее номер. Например, если вторая колонка объекта ResultSetrs называется "title" и хранит строковое значение, то извлечь его можно одним из двух способов: • String s = rs.getString("title"); • String s = rs.getString(2);

34. Result Set. Доступ к колонкам • Вариант с использование имен колонок существует для того, чтобы пользователь задавал методам getXXX те же имена колонок, что он использует в запросе. • Если выражение select не указывает имена колонок (например "select * from table1" или в случаях, когда колонка вычисляется) должны использоваться номера колонок. • В некторых случаях имена двух колонок могут совпадать. Тогда при использовании имен колонок в методах getXXX возвращается значение первой подходящей колонки. Таким образом, чтобы считать значение других колонок с таким же именем, надо использовать индексы колонок. Кроме того, использование индексов немного эффективнее.

35. Result Set. Значение NULL в результатах • Метод wasNull() проверяет, равно ли Null последнее считанное из колонки значение. • Если значение равно Null, то метод getXXX()возвращает: • null для тех из методов getXXX, которые возвращают объекты (getString, getBytes, getDate…) • нулевое значение для getByte, getShort, getInt, getLong, getFloat, and getDouble. • false в случае getBoolean. while(results.next()) {        id = results.getInt(1);        lastname = results.getString(2);        email = results.getString(3);        if(results.wasNull()) { //почему wasNull от results?          email = "no email";}

36. Обработка исключений SQL • Для перехвата исключений SQL существует класс (с производными от него) java.sql.SQLException. • Исключения могут возникать как в самой СУБД, так и в JDBC. • Стандартная обработка исключения включает в себя: • получение текста ошибки (getMessage()) • получение кода ошибки(getErrorCode()) • получение состояния SQL (getSQLState()) • распечатку стека вызова(printStackTrace()). • Метод getMessage()возвращает сообщение ошибки. Если сообщение содержит префикс ORA, то исключение произошло в СУБД, иначе в JDBC.

37. Обработка исключений SQL • getErrorCode() – возвращает пятизначный код ошибки как для ошибок СУБД, так и JDBC • getSQLState() - возвращает пятизначный код ошибки, отображающий состояние SQL. Если ошибка возникла в JDBC, то код не содержит никакой полезной информации catch(SQLException e); {System.out.println("exception: " + e.getMessage());} • printStackTrace() – выводит стек вызовов функций, приводящих к ошибке. try { <some code> } catch(SQLException e) { e.printStackTrace (); }

38. import java.sql.*; • import oracle.jdbc.driver.*; • class JDBCVersion • { • public static void main (String args[]) • throws SQLException • { • // Load the Oracle JDBC driver • DriverManager.registerDriver • (new oracle.jdbc.driver.OracleDriver()); • Connection conn = DriverManager.getConnection • ("jdbc:oracle:thin:@host:port:sid","scott","tiger"); • // Create Oracle DatabaseMetaData object • DatabaseMetaData meta = conn.getMetaData(); • // gets driver info: • System.out.println("JDBC driver version is " + meta.getDriverVersion()); • } • }

39. Благодарю за внимание! • Вопросы?