Результаты запроса ResultSet
Класс ResultSet представляет результирующий набор данных и обеспечивает приложению построчный доступ к результатам запросов. При обработке запроса ResultSet поддерживает указатель на текущую обрабатываемую строку.
Доступ к данным ResultSet обеспечивает посредством набора get-методов, которые организуют доступ к колонкам текущей строки. Метод ResultSet.next используется для перемещения к следующей строке ResultSet, делая ее текущей.
Методы ResultSet
Список наиболее часто используемых методов класса ResultSet представлен в таблице :
| Метод | Описание |
|---|---|
| public boolean absolute(int row) throws SQLException | Метод перемещает курсор на заданное число строк от начала, если число положительно, и от конца — если отрицательно |
| public void afterLast() throws SQLException | Этот метод перемещает курсор в конец результирующего набора за последнюю строку |
| public void beforeFirst() throws SQLException | Этот метод перемещает курсор в начало результирующего набора перед первой строкой |
| public void deleteRow() throws SQLException | Удаляет текущую строку из результирующего набора и базы данных |
| public ResultSetMetaData getMetaData() throws SQLException | Предоставляет объект метаданных для данного ResultSet. Класс ResultSetMetaData содержит информацию о результирующие таблице, такую как количество столбцов, их заголовок и т.д. |
| public int getRow() throws SQLException | Возвращает номер текущей строки |
| public Statement getStatement() throws SQLException | Возвращает экземпляр Statement, который произвел данный результирующий набор |
| public boolean next() throws SQLException public boolean previous() throws SQLException |
Эти методы позволяют переместиться в результирующем наборе на одну строку вперед или назад. Во вновь созданном результирующем наборе курсор устанавливается перед первой строкой, поэтому первое обращение к методу next() влечет позиционирование на первую строку. Эти методы возвращают true, если остается строка для дальнейшего перемещения. Если строк для обработки больше нет, возвращается false. Если открыт поток InputStream для предыдущей строки, он закрывается. Также очищается цепочка предупреждений SQLWarning |
| public void close() throws SQLException | Осуществляет немедленное закрытие ResultSet вручную. Обычно этого не требуется, так как закрытие Statement, связанного с ResultSet, автоматически закрывает ResultSet. К сожалению, не все разработчики JDBC-драйверов придерживаются этих концепций, например, драйвер Oracle самостоятельно не закрывает ResultSet’ы, так что настоятельно рекомендуется закрывать вручную |
Пример использования ResultSet
package mssql; import java.sql.*; public class Main < private static Connection con = null; private static String username = "name"; private static String password = "pass"; private static String URL = "jdbc:jtds:sqlserver://localhost:1433"; public static void main(String[] args) throws SQLException < // Загрузка драйвера DriverManager.registerDriver(new net.sourceforge.jtds.jdbc.Driver()); // Подключение к БД con = DriverManager.getConnection(URL, username, password); if(con != null) System.out.println("Connection Successful !\n"); else System.exit(0); // Создание Statement для отправки запроса базе данных Statement st = con.createStatement(); // Результирующий запрос ResultSet rs = st.executeQuery("select * from users"); // Количество колонок в результирующем запросе int columns = rs.getMetaData().getColumnCount(); // Перебор строк с данными while(rs.next())< for (int i = 1; i System.out.println(); > System.out.println(); if(rs != null) rs.close(); if(st != null) st.close(); if(con != null) con.close(); > >
Результирующий набор данных ResultSet можно не закрывать. Это делается автоматически родительским объектом Statement, когда он закрывается, начинает выполняться повторно или используется для извлечения следующего результата в последовательности нескольких результатов. Но лучше все же закрывать и не надеяться, что это сделает разработчик драйвера JDBC.
Значение NULL в ResultSet
Чтобы определить, равно ли значение определенной колонки NULL или нет, необходимо сначала прочитать значение колонки, а затем использовать метод wasNull класса ResultSet для выяснения данного факта. Если wasNull возвращает значение true, то это означает, что считанное значение равно NULL.
В случае, если возвращаемое значение NULL, то методы ResultSet.getXXX, равны:
- значение будет null для тех методов getXXX, которые возвращают объекты (getString, getBigDecimal, getBytes, getDate, getTime, getTimestamp, getAsciiStream, getUnicodeStream, getBinaryStream, getObject)
- нулевое значение для методов, возвращающих целочисленное или вещественное значения (getByte, getShort, getInt, getLong, getFloat, and getDouble)
- false для метода getBoolean
Строки, колонки и курсоры ResultSet
ResultSet содержит так называемый курсор, который позиционируется на текущей строке данных. При вызове метода next, курсор перемещается на следующую строку.
При открытии набора данных ResultSet курсор расположен перед первой строкой, и первый вызов next передвигает его на первую строку.
ResultSet хранит курсор до самого закрытия или пока не закроется родительский объект Statement.
Курсор для результирующей таблицы имеет имя. Если БД поддерживает позиционированные обновления или позиционированные удаления, то командам обновления или удаления можно передать в качестве параметра имя курсора, которое можно получить с помощью вызова метода getCursorName()
Statement stmt = connection.createStatement(); ResultSet rset = stmt.executeQuery ("select * from users"); String cursorName = rs.getCursorName();
Но не все СУБД могут поддерживать позиционированные обновления или удаления. Чтобы узнать, поддерживает ли данное соединение Connection эти операции или нет, можно вызвать методы DatabaseMetaData.supportsPositionedDelete и supportsPositionedUpdate.
Методы ResultSet.getXXX предоставляют доступ к значениям в колонках в текущей строке. В пределах одной строки значения могут быть считаны в любом порядке. Для обеспечения бо́льшей совместимости рекомендуется считывать их подряд слева направо и делать это только один раз. Для указания колонки можно использовать либо ее имя, либо ее номер. Например, если вторая колонка объекта ResultSet rs называется «title» и хранит строковое значение, то извлечь его можно одним из двух способов:
String s = rs.getString("title"); String s = rs.getString(2);
При обращении к колонке по номеру следует помнить, что колонки нумеруются слева направо, начиная с 1, а имена колонок в вызове методов getXXX нечувствительны к регистру букв.
Наименования колонок совпадает с соответствующими наименованиями колонок в запросе. Если же в выражении select не указываются имена колонок (например «select * from users»), то необходимо либо использовать номера колонок, либо «подключать» метаданные. Информацию о колонках в ResultSet можно получить с помощью вызова ResultSet.getMetaData. Возвращаемый объект ResultSetMetaData содержит информацию о количестве, типах и свойствах колонок объекта ResultSet.
В некоторых случаях имена двух колонок могут совпадать. Тогда при использовании имен колонок в методах getXXX возвращается значение первой подходящей колонки. Таким образом, чтобы считать значение других колонок с таким же именем, надо использовать индексы колонок. Кроме того, использование индексов немного эффективнее.
Если имя колонки известно, а индекс нет, то для поиска номера колонки можно использовать метод findColumn().
Типы данных и их преобразование
Различные методы чтения записей типа getXXX конвертируют низкоуровневые данные в типы данных Java. Например, если в таблице БД тип данных VARCHAR, то при использовании метода getString, драйвер JDBC конвертирует VARCHAR в объект String. Т.е. возвращаемым из метода getString значением будет объект String.
Следующая таблица показывает, какие типы данных различные методы getXXX могут считывать и какие JDBC-типы (SQL-типы) рекомендуются для этих методов.
- «x» означает, что метод getXXX может быть использован,
- «X» означает, что данный метод рекомендуется использовать для этого типа данных.
Например, для типа данных LONGVARCHAR значение можно извлечь любым из методов getXXX кроме getBytes и getBinaryStream, но рекомендуется использовать методы getAsciiStream и getUnicodeStream.
Метод getObject возвращает значение как Object и может быть использован в тех случаях, когда соответствующий низкоуровневый тип данных является специфичным для данной СУБД, или когда приложению необходимо принять любой тип данных.
Таблица соответствия методов ResultSet.getXXX при чтении значений различных типам данных SQL.
| T I N Y I N T |
S M A L L I N T |
I N T E G E R |
B I G I N T |
R E A L |
F L O A T |
D O U B L E |
D E C I M A L |
N U M E R I C |
B I T |
C H A R |
V A R C H A R |
L O N G V A R C H A R |
B I N A R Y |
V A R B I N A R Y |
L O N G V A R B I N A R Y |
D A T E |
T I M E |
T I M E S T A M P |
|
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| getByte | X | x | x | x | x | x | x | x | x | x | x | x | x | ||||||
| getShort | x | X | x | x | x | x | x | x | x | x | x | x | x | ||||||
| getInt | x | x | X | x | x | x | x | x | x | x | x | x | x | ||||||
| getLong | x | x | x | X | x | x | x | x | x | x | x | x | x | ||||||
| getFloat | x | x | x | x | X | x | x | x | x | x | x | x | x | ||||||
| getDouble | x | x | x | x | x | X | X | x | x | x | x | x | x | ||||||
| getBigDecimal | x | x | x | x | x | x | x | X | X | x | x | x | x | ||||||
| getBoolean | x | x | x | x | x | x | x | x | x | X | x | x | x | ||||||
| getString | x | x | x | x | x | x | x | x | x | x | X | X | x | x | x | x | x | x | x |
| getBytes | X | X | x | ||||||||||||||||
| getDate | x | x | x | X | x | ||||||||||||||
| getTime | x | x | x | X | x | ||||||||||||||
| getTimestamp | x | x | x | x | X | ||||||||||||||
| getAsciiStream | x | x | X | x | x | x | |||||||||||||
| getUnicodeStream | x | x | X | x | x | x | |||||||||||||
| getBinaryStream | x | x | X | ||||||||||||||||
| getObject | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x |
Чтение больших LOB объектов
Для чтения больших объектов LOB (Large Object Bynary) также используется ResultSet. Методы getBytes и getString возвращают эти данные в виде одного большого массива (байт, символов). Можно узнать размер объекта с помощью метода Statement.getMaxFieldSize.
byte[] binaryBuffer = null; try < Statement stmt = connection.createStatement(); ResultSet rset = stmt.executeQuery(sql); rset.next(); binaryBuffer = rset.getBytes(1); rset.close(); stmt.close(); >catch (SQLException e)
Можно большие объекты LOB читать с помощью потоков (java.io.InputStream), которые возвращаются некоторыми методами ResultSet. Следует обратить внимание на то, что к этим потокам надо обращаться сразу, так как они будут закрыты при следующем вызове getXXX объекта ResultSet. Такое поведение диктуется низкоуровневой реализацией доступа к большим двоичным объектам.
JDBC API включает три отдельных метода для чтения данных в поток :
- getBinaryStream возвращает поток байтов «как есть», без какого-либо предварительного преобразования
- getAsciiStream возвращает поток, состоящий из однобайтовых ASCII-символов
- getUnicodeStream возвращает поток двухбайтных символов Unicode
Эти потоки отличаются от обычных потоков Java, которые возвращают нетипизированные байты.
Следующий пример демонстрирует использование getAsciiStream :
String sql = "select book from lib where name = 'Золушка'"; Statement stmt = connection.createStatement(); ResultSet rs = st.executeQuery(sql); rs.next(); Clob clob = rs.getClob(1); InputStream is = clob.getAsciiStream();
Пример записи файлов в бинарные (BLOB) и символьные (CLOB/TEXT) поля баз данных Oracle и MySQL можно увидеть здесь.
Множественные наборы : getResultSet, getUpdateCount, getMoreResults
Обычно при выполнении SQL-запросов используют либо метод executeQuery, возвращающий единственный ResultSet, либо executeUpdate, который может быть использован для изменения значения в таблице БД и который возвращают количество измененных строк. Тем не менее, в отдельных случаях приложению заранее может быть неизвестно, возвратит ли данный запрос результат или нет. Кроме этого, некоторые хранимые процедры могут возвратить несколько наборов данных и/или счетчиков обновления.
Для этого случая в JDBC есть механизм, когда приложение может обрабатывать произвольную коллекцию наборов результатов или счетчиков обновления. Данный механизм основан на вызове метода execute и последующем вызове трех других методов getResultSet, getUpdateCount и getMoreResults.
Методы getResultSet, getUpdateCount и getMoreResults позволяют приложению получать результаты запроса по-очереди и для каждого результата определять, является ли он набором данных или счетчиком обновлений.
Что такое resultset как с ним работать
Изучение интерфейсов ResultSet, ResultSetMetaData и DatabaseMetaData.
Интерфейс ResultSet определяет методы, которые позволяют работать с данными, полученными в результате выполнения запроса в базу данных. Результаты запроса помещаются в объект типа ResultSet и являются логическим представлением строк и столбцов данных, хранящихся в базе.
Существует три варианта объектов типа ResultSet: Standard, Scrollable и Updateable. При использовании стандартного варианта обновление полученного результата запроса невозможно, а перемещение по строкам результата запроса возможно только в одном направлении: от первой к последней записи. Вариант Scrollable позволяет перемещаться по строкам запроса как в прямом, так и в обратном направлении, а также позиционировать курсор как на абсолютную, так и на относительную текущей позицию. Вариант Updateable позволяет изменять данные результата запроса, вставлять и удалять строки. Оба последних варианта будут выполняться более медленно, чем стандартный вариант использования объекта типа ResultSet. По этому их лучше использовать только в необходимых для этого случаях.
При работе со стандартным вариантом использования объектов типа ResultSet используются следующие методы:
next() — перемещает курсор на следующую строку результата запроса;
isBeforeFirst() — Возвращает true если курсор находится на позиции “до первой записи” (BFR);
isFirst() — Возвращает true если курсор указывает на первую запись;
isAfterLast() — Возвращает true если курсор находится на позиции “после последней записи” (ALR);
isLast() — Возвращает true если курсор указывает на последнюю запись;
getRow() — Возвращает integer значение номера строки в результате запроса. 1 – первая строка; 2 – вторая строка и т.д. Метод возвращает 0 если строк не существует либо курсор находится в позиции BFR либо ALR.
Для изучения методов стандартного варианта использования объектов типа ResultSet необходимо создать класс с методом main, который регистрирует драйвер JDBC, устанавливает соединение, подготавливает и выполняет запрос в базу данных и демонстрирует использование перечисленных выше методов аналогично следующему примеру:
//Пример №3-1 // ResultSet rs = . ; System.out.println(«Row number «+rs.getRow()+»; BFR is «+rs.isBeforeFirst()); while (rs.next()) System.out.print(«Row number «+rs.getRow()+»; First is «+rs.isFirst()); System.out.print(«:\t»+rs.getInt(1)); System.out.println(«\t»+rs.getString(2)); System.out.println(«Row number «+rs.getRow()+»; Last is «+rs.isLast()); > System.out.println(«Row number «+rs.getRow()+»; ALR is «+rs.isAfterLast());
Два других варианта использования объектов типа ResultSet требуют указания дополнительных параметров методам createStatement(), prepareStatement() и prepareCall():
createStatement(int resultSetType, int resultSetConcurrency);
prepareStatement(String sql, int resultSetType, int resultSetConcurrency);
prepareCall(String sql, int resultSetType, int resultSetConcurrency).
Первый параметр resultSetType используется для создания Scrollable ResultSet. Он задает возможность перемещения курсора в разных направления по строкам результата запроса и определяет чувствительность к изменениям данных, которые были изменены в базе после выполнения запроса. Параметр resultSetType может принимать следующие интуитивно понятные значения:
TYPE_SCROLL_INSENSITIVE – предусматривает перемещение курсора в любых направлениях, допускает абсолютное и относительное позиционирование курсора. Не отражает изменений данных, сделанных в базе после выполнения запроса.
TYPE_SCROLL_SENSITIVE — предусматривает перемещение курсора в любых направлениях, допускает абсолютное и относительное позиционирование курсора. Отражает изменения данных, сделанных в базе после выполнения запроса.
TYPE_FORWARD_ONLY – параметр по умолчанию, стандартный ResultSet.
Второй параметр resultSetConcurrency отвечает за возможность изменения данных результата запроса, вставки и удаления строк из базы данных. Он может принимать одно из двух значений:
CONCUR_UPDATABLE – позволяет вносить изменения в данные;
CONCUR_READ_ONLY – параметр по умолчанию, стандартный ResultSet.
При работе со Scrollable и Updateable вариантами объектов типа ResultSet используются следующие методы:
next() — перемещает курсор на следующую строку результата запроса;
previous() — перемещает курсор на предыдущую строку результата запроса;
beforeFirst() — устанавливает курсор на позицию “до первой строки”; если сразу после выполнения этого метода выполнить метод getXXX() будет выдано исключение типа SQLException;
afterLast() — устанавливает курсор на позицию “после последней строки”; если сразу после выполнения этого метода выполнить метод getXXX() будет выдано исключение типа SQLException;
first() — устанавливает курсор на первую строку результата запроса;
last() — устанавливает курсор на последнюю строку результата запроса;
absolute() — устанавливает курсор на указанную строку относительно первой строки результата запроса;
relative() — устанавливает курсор на указанную строку относительно текущей строки;
moveToCurrentRow() — устанавливает курсор на строку, номер которой был запомнен в результате выполнения метода moveToInsertRow();
moveToInsertRow() — устанавливает курсор в специальную свободную позицию для заполнения пустой строки значениями с помощью методов updateXXX() и последующей вставки этой строки в базу данных с помощью метода insertRow();
deleteRow() — удаляет строку как из результата запроса, так и из базы данных.
Для изучения перечисленных выше методов необходимо создать класс с методом main, который регистрирует драйвер JDBC, устанавливает соединение, подготавливает и выполняет запрос в базу данных и демонстрирует использование перечисленных выше методов аналогично следующим примерам:
//Пример №3-2 // ResultSet rs = . ; rs.afterLast(); System.out.println(«Row number «+rs.getRow()+»; BFR is «+rs.isBeforeFirst()); rs.last(); while (rs.previous()) System.out.print(«Row number «+rs.getRow()+»; First is «+rs.isFirst()); System.out.print(«:\t»+rs.getInt(1)); System.out.println(«\t»+rs.getString(2)); System.out.println(«Row number «+rs.getRow()+»; Last is «+rs.isLast()); > System.out.println(«Row number «+rs.getRow()+»; ALR is «+rs.isAfterLast()); //Пример №3-3 // Statement st = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = st.executeQuery(«SELECT * FROM операторы_связи»); while (rs.next()) System.out.println(rs.getInt(1)+»\t»+rs.getString(2)); if(rs.getString(2).equals(«МТС»)) rs.updateString(2, «TELE2»); rs.updateRow(); > > //rs = st.executeQuery(«SELECT * FROM операторы_связи»); rs.beforeFirst(); while (rs.next()) System.out.println(rs.getInt(1)+»\t»+rs.getString(2)); > //Пример 3-4 // Statement st = . ; ResultSet rs = . ; while (rs.next()) System.out.println(rs.getInt(1)+»\t»+rs.getString(2)); if(rs.getString(2).equals(«SkyLink2»)) rs.deleteRow(); > > rs.moveToInsertRow(); rs.updateInt(1, 5); rs.updateString(2, «TELE2»); rs.insertRow(); //rs = st.executeQuery(«SELECT * FROM операторы_связи»); rs.beforeFirst(); while (rs.next()) System.out.println(rs.getInt(1)+»\t»+rs.getString(2)); >
Интерфейсы ResultSetMetaData и DatabaseMetaData предусматривают методы для работы с метаданными базы данных. Первый интерфейс предназначен для получения информации о данных результата запроса, таких как имена столбцов, типы данных, максимальная длина. Второй интерфейс необходим для получения данных о структуре базы данных, таких как имена таблиц, первичные и внешние ключи, типы данных. Объекты такого типа используются для написания универсальных методов обработки результатов запросов либо для создания системных приложений администрирования баз данных.
Объект типа ResultSetMetaData создается методом getMetaData() объекта типа ResultSet. Ниже перечислены некоторые наиболее часто используемые методы объекта типа ResultSetMetaData:
getTableName() — Возвращает имя таблицы, по которой выполнялся запрос; тип возвращаемого значения String.
getColumnCount() — Возвращает количество столбцов результата запроса; тип возвращаемого значения int.
getColumnName(int n) – Возвращает имя столбца номер n в результате запроса; тип возвращаемого значения String.
getColumnType(int n) – Возвращает JDBC тип данных столбца номер n результата запроса (java.sql.Types); тип возвращаемого значения int.
GetColumnTypeName(int n) – Возвращает имя типа данных столбца номер n результата запроса в соответствии с типом данных его в базе данных (SQL data types); тип возвращаемого значения String.
Номер столбца результата запроса, так же как и в базе данных нумеруется начиная с 1, а не с 0, как при работе с массивами. Попытка указать нулевой номер столбца приведет к появлению исключения типа SQLException.
Объект типа DatabaseMetaData создается методом getMetaData() объекта типа Connection. Метода полученного таким образом объекта можно разделить условно на две категории: характеристики и струкрура базы данных.
К первой категории относятся методы для получения такой информации, как список ключевых слов SQL, перечень SQL типов данных, поддерживаемых данной СУБД. Типами возвращаемого значения таких методов могут быть одиночные строки или строки, разделенные символом “точка”. Методы, предназначенные для получения количественных характеристик базы данных возвращают значение типа int, например: максимально допустимое количество активных соединений. Методы, предназначенные для получения информации типа поддерживается ли базой данных пакетное обновление, возвращают значение типа boolean.
Ко второй категории относятся методы, запрашивающие информацию о таблицах, хранимых процедурах и т.д. Такие методы возвращают объекты типа ResultSet. Параметры таких методов могут содержать метасимволы _ (любой одиночный символ) и % (любая последовательность символов).
Изучение интерфейсов ResultSetMetaData и DatabaseMetaData выносится на самостоятельную проработку. Для проверки изученного материала будет выдано индивидуальное контрольное задание, заключающееся в написании соответствующего программного кода на java, которое необходимо выполнить и защитить на контрольном занятии.
JDBC или с чего всё начинается
В современном мире без хранения данных никак. И история работы с базами данных началась уже очень давно, с появления JDBC. Предлагаю вспомнить то, без чего не обходися ни один современный фрэймворк, построенный поверх JDBC. Кроме того, даже работая с ними временами может понадобится возможность «вернуться к корням». Надеюсь, обзор поможет как вступительное слово или поможет что-то освежить в памяти.
Вступление
Одна из основных целей языка программирования — хранение и обработка информации. Чтобы лучше понять работу хранения данных стоит немного времени выделить на теорию и архитектуру приложений. Например, можно ознакомиться с литературой, а именно с книгой «Software Architect’s Handbook: Become a successful software architect by implementing effective arch. » авторства Joseph Ingeno. Как сказано, есть некий Data Tier или «Слой данных». Он включает в себя место хранения данных (например, SQL базу данных) и средства для работы с хранилищем данных (например, JDBC, о котором и пойдёт речь). Так же на сайте Microsoft есть статья: «Проектирование уровня сохраняемости инфраструктуры» в которой описывается архитектурное решение выделения из Data Tier дополнительного слоя — Persistence Layer. В таком случае Data Tier — это уровень хранения самих данных, в то время как Persistence Layer — это некоторый уровень абстракции для работы с данными из хранилища с уровня Data Tier. К уровню Persistence Layer можно отнести шаблон «DAO» или различные ORM. Но ORM — это тема отдельного разговора. Как Вы могли уже понять, вначале появился Data Tier. Ещё с времён JDK 1.1 в Java мире появился JDBC (Java DataBase Connectivity — соединение с базами данных на Java). Это стандарт взаимодействия Java-приложений с различными СУБД, реализованный в виде пакетов java.sql и javax.sql, входящих в состав Java SE:
Данный стандарт описан специфкицией «JSR 221 JDBC 4.1 API». Данная спецификация рассказывает нам о том, что JDBC API предоставляет программный доступ к реляционным базам данных из программ, написанных на Java. Так же рассказывает о том, что JDBC API является частью платформы Java и входит поэтому в Java SE и Java EE. JDBC API представлен двумя пакетами: java.sql and javax.sql. Давайте тогда с ними и познакомимся.
Начало работы
Чтобы понять что такое вообще JDBC API нам понадобится Java приложение. Удобнее всего воспользоваться одной из систем сборки проектов. Например, воспользуемся Gradle. Более подробно про Gradle можно прочитать в небольшом обзоре: «Краткое знакомство с Gradle». Для начала инициализируем новый Gradle проект. Так как функциональность Gradle реализуется через плагины, то для инициализации нам нужно воспользоваться «Gradle Build Init Plugin»:
gradle init --type java-application
Откроем после этого билд скрипт — файл build.gradle, где описывается наш проект и то, как с ним нужно работать. Нас интересует блок «dependencies«, где описываются зависимости — то есть те библиотеки/фрэймворки/api, без которых мы не можем работать и от которых мы зависим. По умолчанию мы увидим что-то вроде:
dependencies < // This dependency is found on compile classpath of this component and consumers. implementation 'com.google.guava:guava:26.0-jre' // Use JUnit test framework testImplementation 'junit:junit:4.12' >
Почему мы тут это видим? Это зависимости нашего проекта, которые нам сгенерировал автоматически Gradle при создании проекта. А так же потому что guava — это отдельная библиотека, не входящая в комплект с Java SE. JUnit так же не входит в комплект с Java SE. Но JDBC у нас есть «из коробки», то есть входит в состав Java SE. Получается JDBC у нас есть. Отлично. Что же нам ещё надо? Есть такая замечательная схема:
Как мы видим, и это логично, база данных является внешним компонентом, которого нет изначально в Java SE. Это объясняется просто — существует огромное количество баз данных и работать вы можете захотеть с любой. Например, есть PostgreSQL, Oracle, MySQL, H2. Каждая из этих баз данных поставляется отдельной компанией, которые называются поставщиками баз данных или database vendors. Каждая база данных написана на каком-то своём языке программирования (не обязательно Java). Чтобы с базой данных можно было работать из Java приложения поставщик базы данных пишет особый драйвер, который является своего образа адаптером. Такие JDBC совместимые (то есть у которых есть JDBC драйвер) ещё называют «JDBC-Compliant Database». Тут можно провести аналогию с компьютерными устройствами. Например, в блокноте есть кнопка «Печать». Каждый раз когда вы её нажимаете программа сообщает операционной системе, что приложение блокнот хочет напечатать. И у Вас есть принтер. Чтобы научить разговаривать единообразно вашу операционную систему с принтером Canon или HP понадобятся разные драйверы. Но для Вас, как пользователя, ничего не изменится. Вы по прежнему будете нажимать одну и ту же кнопку. Так и с JDBC. Вы выполняете один и тот же код, просто «под капотом» могут работать разные базы данных. Думаю, тут очень понятный подход. Каждый такой JDBC драйвер — это некоторый артефакт, библиотека, jar файл. Он то и является зависимостью для нашего проекта. Например, мы можем выбрать базу данных «H2 Database» и тогда нам надо добавить зависимость следующим образом:
dependencies < implementation 'com.h2database:h2:1.4.197'
То, как найти зависимость и как её описать указано на официальных сайтах поставщика БД или на "Maven Central". JDBC драйвер не является базой данных, как Вы поняли. А лишь является проводником к ней. Но есть такое понятие, как "In memory databases". Это такие базы данных, которые существуют в памяти на время жизни вашего приложения. Обычно, это часто используют для тестирования или для учебных целей. Это позволяет не ставить отдельный сервер баз данных на машине. Что нам очень даже подойдёт для знакомств с JDBC. Вот и готова наша песочница и мы приступаем.
Connection
- Через DriverManager
- Через DataSource
Connection con = DriverManager.getConnection(url, user, passwd);
Параметры можно взять с сайта выбранной нами базы данных. В нашем случае это H2 — "H2 Cheat Sheet". Перейдём в подготовленный Gradle'ом класс AppTest. Он содержит JUnit тесты. JUnit тест — это метод, который помечен аннотацией @Test . Юнит тесты не являются темой данного обзора, поэтому просто ограничимся пониманием того, что это описанные определённым образом методы, цель которых что-то протестировать. Согласно специфкиации JDBC и сайту H2 проверим, что мы получили подключение к БД. Напишем метод получения подключения:
private Connection getNewConnection() throws SQLException
Теперь напишем тест для этого метода, который проверит, что подключение действительно устанавливается:
@Test public void shouldGetJdbcConnection() throws SQLException < try(Connection connection = getNewConnection()) < assertTrue(connection.isValid(1)); assertFalse(connection.isClosed()); >>
Данный тест при выполнении проверит, что полученное подключение валидное (корректно созданное) и что оно не закрыто. Благодаря использованию конструкции try-with-resources мы освободим ресурсы после того, как они нам больше не нужны. Это убережёт нас от "провисших" соединений и утечек памяти. Так как любые действия с БД требуют подключения, то давайте для остальных тестовых методов, помеченных @Test, обеспечим в начале теста Connection, который мы освободим после теста. Для этого нам понадобится две аннотации: @Before и @After Добавим в класс AppTest новое поле, которое будет хранить JDBC подключение для тестов:
private static Connection connection;
И добавим новые методы:
@Before public void init() throws SQLException < connection = getNewConnection(); >@After public void close() throws SQLException
Теперь, любому тестовому методу гарантируется наличие JDBC connection и он не должен каждый раз сам его создавать.
Statements
- Statement: SQL выражение, которое не содержит параметров
- PreparedStatement : Подготовленное SQL выражение, содержащее входные параметры
- CallableStatement : SQL выражение с возможностью получить возвращаемое значение из хранимых процедур (SQL Stored Procedures).
private int executeUpdate(String query) throws SQLException < Statement statement = connection.createStatement(); // Для Insert, Update, Delete int result = statement.executeUpdate(query); return result; >
Добавим метод создания тестовой таблицы с использованием прошлого метода:
private void createCustomerTable() throws SQLException
Теперь протестируем это:
@Test public void shouldCreateCustomerTable() throws SQLException
Теперь давайте выполним запрос, да ещё и с параметром:
@Test public void shouldSelectData() throws SQLException
JDBC не поддерживает именованные параметры для PreparedStatement, поэтому сами параметры указываются вопросами, а указывая значение мы указываем индекс вопроса (начиная с 1, а не с нуля). В последнем тесте мы получили true как признак того, есть ли результат. Но как представлен результат запроса в JDBC API? А представлен он как ResultSet.
ResultSet
Понятие ResultSet описано в спецификации JDBC API в главе "CHAPTER 15 Result Sets". Прежде всего, там сказано, что ResultSet предоставляет методы для получения и манипуляции результатами выполненных запросов. То есть если метод execute вернул нам true, значит мы можем получить и ResultSet. Давайте вынесем вызов метода createCustomerTable() в метод init, который отмечен как @Before. Теперь доработаем наш тест shouldSelectData:
@Test public void shouldSelectData() throws SQLException < String query = "SELECT * FROM customers WHERE name = ?"; PreparedStatement statement = connection.prepareStatement(query); statement.setString(1, "Brian"); boolean hasResult = statement.execute(); assertTrue(hasResult); // Обработаем результат ResultSet resultSet = statement.getResultSet(); resultSet.next(); int age = resultSet.getInt("age"); assertEquals(33, age); >
Тут стоит отметить, что next — это метод, который двигает так называемый "курсор". Курсор в ResultSet указывает на некоторую строку. Таким образом, чтобы считать строку, на неё нужно этот самый курсор установить. Когда курсор перемещается, то метод перемещения курсора возвращает true, если курсор валидный (правильный, корректный), то есть указывает на данные. Если возвращает false, значит данных нет, то есть курсор не указывает на данные. Если попытаться получить данные с невалидным курсором, то мы получим ошибку: No data is available Ещё интересно, что через ResultSet можно обновлять или даже вставлять строки:
@Test public void shouldInsertInResultSet() throws SQLException
RowSet
JDBC помимо ResultSet вводит такое понятие, как RowSet. Подробнее можно прочитать здесь: "JDBC Basics: Using RowSet Objects". Существуют различные вариации использования. Например, самый простой случай может выглядеть так:
@Test public void shouldUseRowSet() throws SQLException
Как видно, RowSet похож на симбиоз statement (мы указали через него command) и выполнили command. Через него же мы управляем курсором (вызывая метод next) и из него же получаем данные. Интересен не только такой подход, но и возможные реализации. Например, CachedRowSet. Он является "отключённым" (то есть не использует постоянное подключение к БД) и требует явного выполнения синхронизации с БД:
CachedRowSet jdbcRsCached = new CachedRowSetImpl(); jdbcRsCached.acceptChanges(connection);
Подробнее можно прочитать в tutorial на сайте Oracle: "Using CachedRowSetObjects".
Metadata
Кроме запросов, подключение к БД (т.е. экземпляр класса Connection) предоставляет доступ к метаданным - данным о том, как настроена и как устроена наша база данных. Но для начала озвучим несколько ключевых моментов: URL подключения к нашей БД: "jdbc:h2:mem:test". test - это название нашей базы данных. Для JDBC API это каталог. И название будет в верхнем регистре, то есть TEST. Схема по умолчанию (Default schema) для H2 - PUBLIC. Теперь, напишем тест, который показывает все пользовательские таблицы. Почему пользовательские? Потому что в базах данных есть не только пользовательские (те, которые мы сами создали при помощи create table выражений), но и системные таблицы. Они необходимы, чтобы хранить системную информацию о структуре БД. У каждой БД такие системные таблицы могут храниться по-разному. Например, в H2 они хранятся в схеме "INFORMATION_SCHEMA". Интересно, что INFORMATION SCHEMA является общим подходом, но Oracle пошли иным путём. Подробнее можно прочитать здесь: "INFORMATION_SCHEMA и Oracle". Напишем тест, получающий метаданные по пользовательским таблицам:
@Test public void shoudGetMetadata() throws SQLException < // У нас URL = "jdbc:h2:mem:test", где test - название БД // Название БД = catalog DatabaseMetaData metaData = connection.getMetaData(); ResultSet result = metaData.getTables("TEST", "PUBLIC", "%", null); Listtables = new ArrayList<>(); while(result.next()) < tables.add(result.getString(2) + "." + result.getString(3)); >assertTrue(tables.contains("PUBLIC.CUSTOMERS")); >
Пул подключений
Пулу подключений в спецификации JDBC отведен раздел "Chapter 11 Connection Pooling". В нём же и даётся главное обоснование необходимости пула подключений. Каждый Coonection - это физическое подключение к БД. Его создание и закрытие - довольно "дорогая" работа. JDBC предоставляет лишь API для пула соединений. Поэтому, выбор реализации остаётся за нами. Например, к таким реализациям относится HikariCP. Соответственно, нам понадобится добавить пул к нам в зависимости проекта:
dependencies
Теперь надо как-то пул этот задействовать. Для этого нужно выполнить инициализацию источника данных, он же Datasource:
private DataSource getDatasource()
И напишем тест на получение подключения из пула:
@Test public void shouldGetConnectionFromDataSource() throws SQLException < DataSource datasource = getDatasource(); try (Connection con = datasource.getConnection()) < assertTrue(con.isValid(1)); >>
Транзакции
- Data Manipulation Language, он же DML (Insert, Update, Delete)
Транзакция завершается как только завершилось выполнение действия Select Statements
Транзакция завершается тогда, когда ResultSet будет закрыт (ResultSet#close) - CallableStatement и выражения, возвращающие несколько результатов
Когда все ассоциированные ResultSets будут закрыты и все выходные данные получены (включая кол-во апдейтов)
@Test public void shouldCommitTransaction() throws SQLException < connection.setAutoCommit(false); String query = "INSERT INTO customers VALUES (1, 'Max', 20)"; connection.createStatement().executeUpdate(query); connection.commit(); Statement statement = connection.createStatement(); statement.execute("SELECT * FROM customers"); ResultSet resultSet = statement.getResultSet(); int count = 0; while(resultSet.next()) < count++; >assertEquals(2, count); >
Всё просто. Но это так, пока у нас всего одна транзакция. А что делать, когда их несколько? Нужно их изолировать друг от друга. Поэтому, поговорим об уровнях изоляции транзакции и как с ними справляется JDBC.
Уровни изоляции
- Atomicity(Атомарность):
Никакая транзакция не будет зафиксирована в системе частично. Будут либо выполнены все её подоперации, либо не выполнено ни одной. - Consistency(Согласованность):
Каждая успешная транзакция по определению фиксирует только допустимые результаты. - Isolation(Изолированность):
Во время выполнения транзакции параллельные транзакции не должны оказывать влияния на её результат. - Durability(Долговечность):
Если транзакция успешно завершенеа, сделанные в ней изменения не будут отменены из-за какого-либо сбоя.
Далее, используем это в версиях:
dependencies < implementation "com.h2database:h2:$" implementation "com.zaxxer:HikariCP:$" testImplementation "junit:junit:$" >
Вы могли заметить, что версия h2 стала ниже. Позже мы увидим, зачем. Итак, как же применять уровни изолированности? Давайте посмотрим сразу небольшой практический пример:
@Test public void shouldGetReadUncommited() throws SQLException < Connection first = getNewConnection(); assertTrue(first.getMetaData().supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED)); first.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED); first.setAutoCommit(false); // Транзакиця на подключение. Поэтому первая транзакция с ReadUncommited вносит изменения String insertQuery = "INSERT INTO customers VALUES (5, 'Max', 15)"; first.createStatement().executeUpdate(insertQuery); // Вторая транзакция пытается их увидеть int rowCount = 0; JdbcRowSet jdbcRs = new JdbcRowSetImpl(getNewConnection()); jdbcRs.setCommand("SELECT * FROM customers"); jdbcRs.execute(); while (jdbcRs.next()) < rowCount++; >assertEquals(2, rowCount); >
Интересно, что данный тест может упасть на вендоре, который не поддерживает TRANSACTION_READ_UNCOMMITTED (например, sqlite или HSQL). А ещё уровень транзакции может просто не сработать. Помните мы указывали версию драйвера H2 Database? Если мы поднимем её до h2Version = '1.4.177' и выше, то READ UNCOMMITTED перестанет работать, хотя код мы не меняли. Это ещё раз доказывает, что выбор вендора и версии драйвера - это не просто буквы, от этого будет в реальности зависеть то, как будут выполняться ваши запросы. Про то, как исправить это поведение в версии 1.4.177 и как это не работает в версиях выше можно прочитать здесь: "Support READ UNCOMMITTED isolation level in MVStore mode".
Итог
- Огненный доклад: "Transactions: myths, surprises and opportunities" от Martin Kleppmann
- Юрий Ткач: "JPA. Транзакции"
- Юрик Ткач: "JDBC - Java для тестировщиков"
- Бесплатный курс на Udemy: "JDBC and MySQL"
- "Обработка объектов CallableStatement"
- IBM Developer: "Java Database Connectivity"
- IBM Knowledge Center: "Getting started with JDBC"
Продвинутая работа с ResultSet
Современный JDBC API позволяет очень сильно кастомизировать объекты Statement и ResultSet . Например, с помощью ResultSet можно менять строки в базе данных.
При создании объекта statement в него можно передать кучу наших пожеланий. Эти пожелания можно разделить на три группы:
- Тип связи с базой
- Управление одновременным доступом
- Сохраняемость и транзакции
Эти параметры можно передавать при создании объекта Statement или PreparedStatement . Пример:
Statement statement = connection.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_OVER_COMMIT ); PreparedStatement statement = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_OVER_COMMIT);
Глубоко эти вещи мы изучать не будем, но я хочу, чтобы ты знал, что такое возможно, если встретишь что-то похожее в чужом коде.
Типы ResultSet
ResultSet может быть определенного типа. Тип определяет некоторые характеристики и возможности ResultSet.
Не все типы поддерживаются всеми базами данных и драйверами JDBC. Тебе придется проверить свою базу данных и драйвер JDBC, чтобы увидеть, поддерживает ли он тип, который ты хочешь использовать. Метод DatabaseMetaData.supportsResultSetType(int type) возвращает true или false в зависимости от того, поддерживается данный тип или нет.
На момент написания статьи существует три типа ResultSet:
- ResultSet.TYPE_FORWARD_ONLY
- ResultSet.TYPE_SCROLL_INSENSITIVE
- ResultSet.TYPE_SCROLL_SENSITIVE
Тип по умолчанию — TYPE_FORWARD_ONLY.
TYPE_FORWARD_ONLY означает, что ResultSet можно перемещать только вперед. То есть ты можешь перемещаться только из строки 1, строки 2, строки 3 и т. д. В ResultSet ты не можешь двигаться назад: нельзя считать данные из 9-й строки после чтения десятой.
TYPE_SCROLL_INSENSITIVE означает, что ResultSet можно перемещать (прокручивать) как вперед, так и назад. Ты также можешь перейти к позиции относительно текущей позиции или перейти к абсолютной позиции.
ResultSet этого типа нечувствителен к изменениям в базовом источнике данных, пока ResultSet открыт. То есть если запись в ResultSet изменяется в базе данных другим потоком или процессом, она не будет отражена в уже открытых ResultSet этого типа.
TYPE_SCROLL_SENSITIVE означает, что ResultSet можно перемещать (прокручивать) как вперед, так и назад. Ты также можешь перейти к позиции относительно текущей позиции или перейти к абсолютной позиции.
ResultSet этого типа чувствителен к изменениям в базовом источнике данных, пока ResultSet открыт. То есть если запись в ResultSet изменяется в базе данных другим потоком или процессом, она будет отражена в уже открытых ResultSet этого типа.
Concurrency
Параллельность ResultSet определяет, может ли ResultSet обновляться, или только считываться.
Некоторые базы данных и драйверы JDBC поддерживают обновление ResultSet, но не все. Метод DatabaseMetaData.supportsResultSetConcurrency(int concurrency) возвращает значение true или false в зависимости от того, поддерживается данный режим параллелизма или нет.
ResultSet может иметь один из двух уровней параллелизма:
CONCUR_READ_ONLY означает, что ResultSet может быть только прочитан.
CONCUR_UPDATABLE означает, что ResultSet может быть прочитан и изменен.
Пример изменения данных в базе
С помощью этих параметров ты можешь управлять создаваемым Statement и его ResultSet.
Например, можно создать обновляемый ResultSet и с его помощью менять базу данных. При создании Statement важно соблюсти следующие условия:
- указывается только одна таблица
- не содержит предложений join или group by
- столбцы запроса должны содержать первичный ключ
При выполнении вышеуказанных условий обновляемый ResultSet может быть использован для модификации таблицы в базе данных. При создании объекта Statement нужно указать такие параметры:
Statement st = createStatement(Result.TYPE_SCROLL_INSENSITIVE, Result.CONCUR_UPDATABLE)
Результатом выполнения такого оператора является обновляемый набор результатов. Метод обновления заключается в перемещении курсора ResultSet в строку, которую ты хочешь обновить, а затем в вызове метода updateXXX() .
Метод updateXXX работает аналогично методу getXXX() . Метод updateXXX() имеет два параметра. Первый — это номер обновляемого столбца, который может быть именем столбца или серийным номером. Второй — это данные, которые необходимо обновить, и этот тип данных должен быть тот же, что и XXX.
Чтобы строка реально обновилась в базе, нужно вызвать метод updateRow() до того, как курсор ResultSet покинет измененную строку, в противном случае изменения так и не попадут в базу.
Также можно добавлять новые строки в таблицу:
Сначала нужно переместить курсор на пустую строку. Для этого нужно вызвать метод moveToInsertRow() .
Затем нужно заполнить эту строку данными с помощью метода updateXXX() .
Затем нужно вызвать метод inserRow() , чтобы строка добавилась в базу.
Ну и наконец нужно вернуть курсор обратно, вызвав метод moveToCurrentRow() .
Важно! Не все СУБД поддерживают данные параметры для расширенного оператора Statement. В случае наличия проблем смотри официальную документацию конкретной СУБД.