1. Обзор
API подключения к базе данных Java (JDBC) обеспечивает доступ к базе данных из приложения Java. Мы можем использовать JDBC для подключения к любой базе данных, если доступен поддерживаемый драйвер JDBC.
ResultSet — это таблица данных, созданная путем выполнения запросов к базе данных. В этом руководстве мы более подробно рассмотрим API ResultSet .
2. Создание набора результатов
Во-первых, мы получаем ResultSet , вызывая executeQuery() для любого объекта, реализующего интерфейс Statement . И PreparedStatement , и CallableStatement являются подинтерфейсами оператора :
PreparedStatement pstmt = dbConnection.prepareStatement("select * from employees");
ResultSet rs = pstmt.executeQuery();
Объект ResultSet поддерживает курсор, указывающий на текущую строку результирующего набора. Мы будем использовать next() в нашем ResultSet для перебора записей.
Далее мы будем использовать методы getX() при переборе результатов для извлечения значений из столбцов базы данных , где X — тип данных столбца. Фактически, мы предоставим имена столбцов базы данных методам getX() :
while(rs.next()) {
String name = rs.getString("name");
Integer empId = rs.getInt("emp_id");
Double salary = rs.getDouble("salary");
String position = rs.getString("position");
}
Точно так же порядковый номер столбца можно использовать с методами getX() вместо имени столбца. Номер индекса — это последовательность столбцов в операторе выбора SQL.
Если оператор select не перечисляет имена столбцов, номер индекса представляет собой последовательность столбцов в таблице. Нумерация индексов столбцов начинается с единицы:
Integer empId = rs.getInt(1);
String name = rs.getString(2);
String position = rs.getString(3);
Double salary = rs.getDouble(4);
3. Получение метаданных из набора результатов
В этом разделе мы увидим, как получить информацию о свойствах и типах столбцов в ResultSet .
Во-первых, давайте воспользуемся методом getMetaData() для нашего ResultSet , чтобы получить ResultSetMetaData :
ResultSetMetaData metaData = rs.getMetaData();
Далее, давайте получим количество столбцов, которые есть в нашем ResultSet :
Integer columnCount = metaData.getColumnCount();
Кроме того, мы можем использовать любой из следующих методов в нашем объекте метаданных для получения свойств каждого столбца:
getColumnName(int columnNumber)–чтобы получить имя столбцаgetColumnLabel(int columnNumber)—для доступа к метке столбца, которая указана послеASв SQL-запросеgetTableName(int columnNumber)–чтобы получить имя таблицы, которой принадлежит этот столбецgetColumnClassName(int columnNumber)—для получения типа данных Java столбцаgetColumnTypeName(int columnNumber)—чтобы получить тип данных столбца в базе данныхgetColumnType(int columnNumber)–чтобы получить тип данных SQL столбцаisAutoIncrement (int columnNumber)—указывает, является ли столбец автоинкрементнымisCaseSensitive(int columnNumber)–указывает, имеет ли значение регистр в столбце.isSearchable(int columnNumber)—предлагает, можем ли мы использовать столбец в предложенииwhereSQL-запроса.isCurrency(int columnNumber)–сигнализирует, содержит ли столбец денежное значениеisNullable(int columnNumber)—возвращаетноль, если столбец не может быть нулевым,один, если столбец может содержать нулевое значение, идва, если допустимость нулевого значения столбца неизвестна.isSigned(int columnNumber)—возвращаетtrue, если значения в столбце подписаны, иначе возвращаетfalse
Давайте пройдемся по столбцам, чтобы получить их свойства:
for (int columnNumber = 1; columnNumber <= columnCount; columnNumber++) {
String catalogName = metaData.getCatalogName(columnNumber);
String className = metaData.getColumnClassName(columnNumber);
String label = metaData.getColumnLabel(columnNumber);
String name = metaData.getColumnName(columnNumber);
String typeName = metaData.getColumnTypeName(columnNumber);
int type = metaData.getColumnType(columnNumber);
String tableName = metaData.getTableName(columnNumber);
String schemaName = metaData.getSchemaName(columnNumber);
boolean isAutoIncrement = metaData.isAutoIncrement(columnNumber);
boolean isCaseSensitive = metaData.isCaseSensitive(columnNumber);
boolean isCurrency = metaData.isCurrency(columnNumber);
boolean isDefiniteWritable = metaData.isDefinitelyWritable(columnNumber);
boolean isReadOnly = metaData.isReadOnly(columnNumber);
boolean isSearchable = metaData.isSearchable(columnNumber);
boolean isReadable = metaData.isReadOnly(columnNumber);
boolean isSigned = metaData.isSigned(columnNumber);
boolean isWritable = metaData.isWritable(columnNumber);
int nullable = metaData.isNullable(columnNumber);
}
4. Навигация по набору результатов
Когда мы получаем ResultSet , курсор находится перед первой строкой. Более того, по умолчанию ResultSet перемещается только в прямом направлении. Но мы можем использовать прокручиваемый набор результатов для других вариантов навигации.
В этом разделе мы обсудим различные варианты навигации.
4.1. Типы результирующих наборов
Тип ResultSet указывает, как мы будем управлять набором данных:
TYPE_FORWARD_ONLY —опция по умолчанию, при которой курсор перемещается от начала до концаTYPE_SCROLL_INSENSITIVE —наш курсор может перемещаться по набору данных как в прямом, так и в обратном направлении; если есть изменения в базовых данных при перемещении по набору данных, они игнорируются; набор данных содержит данные с момента, когда запрос к базе данных возвращает результатTYPE_SCROLL_SENSITIVE —аналогично типу, нечувствительному к прокрутке, однако для этого типа набор данных немедленно отражает любые изменения базовых данных.
Не все базы данных поддерживают все типы ResultSet . Итак, давайте проверим, поддерживается ли тип, используя supportsResultSetType в нашем объекте DatabaseMetaData :
DatabaseMetaData dbmd = dbConnection.getMetaData();
boolean isSupported = dbmd.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
4.2. Прокручиваемый набор результатов
Чтобы получить прокручиваемый набор результатов , нам нужно передать некоторые дополнительные параметры при подготовке оператора .
Например, мы могли бы получить прокручиваемый ResultSet , используя либо TYPE_SCROLL_INSENSITIVE , либо TYPE_SCROLL_SENSITIVE в качестве типа ResultSet :
PreparedStatement pstmt = dbConnection.prepareStatement(
"select * from employees",
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = pstmt.executeQuery();
4.3. Параметры навигации
Мы можем использовать любую из следующих опций для прокручиваемого ResultSet :
next()— переходит к следующей строке с текущей позициипредыдущая ()— переходит к предыдущей строкеfirst () —переходит к первой строкеResultSetlast() —переходит к последней строкеbeforeFirst() –перемещается в начало; вызовnext()в нашемResultSetпосле вызова этого метода возвращает первую строку из нашегоResultSetafterLast() –переходит в конец; вызовпредыдущего() в нашем ResultSetпосле выполнения этого метода возвращает последнюю строку из нашегоResultSetотносительный(int numOfRows) –перейти вперед или назад от текущей позиции наnumOfRowsabsolute(int rowNumber) –переходит к указанному номерустроки
Давайте посмотрим несколько примеров:
PreparedStatement pstmt = dbConnection.prepareStatement(
"select * from employees",
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
// iterate through the results from first to last
}
rs.beforeFirst(); // jumps back to the starting point, before the first row
rs.afterLast(); // jumps to the end of resultset
rs.first(); // navigates to the first row
rs.last(); // goes to the last row
rs.absolute(2); //jumps to 2nd row
rs.relative(-1); // jumps to the previous row
rs.relative(2); // jumps forward two rows
while (rs.previous()) {
// iterates from current row to the first row in backward direction
}
4.4. Количество строк набора результатов
Давайте используем getRow() для получения текущего номера строки нашего ResultSet .
Сначала мы перейдем к последней строке ResultSet , а затем используем getRow() для получения количества записей:
rs.last();
int rowCount = rs.getRow();
5. Обновление данных в наборе результатов
По умолчанию ResultSet доступен только для чтения. Однако мы можем использовать обновляемый набор результатов для вставки, обновления и удаления строк.
5.1. Параллелизм набора результатов
Режим параллелизма указывает, может ли наш ResultSet обновлять данные.
Параметр CONCUR_READ_ONLY используется по умолчанию и должен использоваться, если нам не нужно обновлять данные с помощью нашего ResultSet .
Однако, если нам нужно обновить данные в нашем ResultSet , следует использовать опцию CONCUR_UPDATABLE .
Не все базы данных поддерживают все режимы параллелизма для всех типов ResultSet . Поэтому нам нужно проверить, поддерживаются ли желаемый тип и режим параллелизма, используя метод supportsResultSetConcurrency() :
DatabaseMetaData dbmd = dbConnection.getMetaData();
boolean isSupported = dbmd.supportsResultSetConcurrency(
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
5.2. Получение обновляемого набора результатов
Чтобы получить обновляемый набор результатов , нам нужно передать дополнительный параметр при подготовке оператора . Для этого воспользуемся CONCUR_UPDATABLE в качестве третьего параметра при создании инструкции:
PreparedStatement pstmt = dbConnection.prepareStatement(
"select * from employees",
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = pstmt.executeQuery();
5.3. Обновление строки
В этом разделе мы обновим строку, используя обновляемый набор результатов, созданный в предыдущем разделе.
Мы можем обновлять данные в строке, вызывая методы updateX() , передавая имена столбцов и значения для обновления. Мы можем использовать любой поддерживаемый тип данных вместо X в методе updateX() .
Обновим столбец «зарплата» типа double :
rs.updateDouble("salary", 1100.0);
Обратите внимание, что это просто обновляет данные в ResultSet , но изменения еще не сохраняются обратно в базу данных.
Наконец, давайте вызовем updateRow() для сохранения обновлений в базе данных :
rs.updateRow();
Вместо имен столбцов мы можем передать индекс столбца в методы updateX() . Это похоже на использование индекса столбца для получения значений с помощью методов getX() . Передача имени столбца или индекса в методы updateX() дает тот же результат:
rs.updateDouble(4, 1100.0);
rs.updateRow();
5.4. Вставка строки
Теперь давайте вставим новую строку, используя наш обновляемый ResultSet .
Во- первых, мы будем использовать moveToInsertRow() для перемещения курсора для вставки новой строки:
rs.moveToInsertRow();
Затем мы должны вызвать методы updateX() , чтобы добавить информацию в строку. Нам нужно предоставить данные для всех столбцов в таблице базы данных. Если мы не предоставляем данные для каждого столбца, используется значение столбца по умолчанию:
rs.updateString("name", "Venkat");
rs.updateString("position", "DBA");
rs.updateDouble("salary", 925.0);
Затем вызовем метод insertRow() , чтобы вставить новую строку в базу данных:
rs.insertRow();
Наконец, воспользуемся функцией moveToCurrentRow(). Это вернет позицию курсора к строке, в которой мы были до того, как начали вставлять новую строку с помощью метода moveToInsertRow() :
rs.moveToCurrentRow();
5.5. Удаление строки
В этом разделе мы удалим строку, используя наш обновляемый ResultSet .
Сначала мы перейдем к строке, которую хотим удалить. Затем мы вызовем метод deleteRow() для удаления текущей строки:
rs.absolute(2);
rs.deleteRow();
6. Удерживаемость
Возможность удержания определяет, будет ли наш ResultSet открытым или закрытым в конце транзакции базы данных.
6.1. Типы удержания
Используйте CLOSE_CURSORS_AT_COMMIT , если ResultSet не требуется после фиксации транзакции.
Используйте HOLD_CURSORS_OVER_COMMIT , чтобы создать удерживаемый ResultSet . Удерживаемый ResultSet не закрывается даже после фиксации транзакции базы данных.
Не все базы данных поддерживают все типы удержания.
Итак, давайте проверим, поддерживается ли тип удержания, используя supportsResultSetHoldability() для нашего объекта DatabaseMetaData . Затем мы получим удерживаемость базы данных по умолчанию с помощью getResultSetHoldability() :
boolean isCloseCursorSupported
= dbmd.supportsResultSetHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
boolean isOpenCursorSupported
= dbmd.supportsResultSetHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
boolean defaultHoldability
= dbmd.getResultSetHoldability();
6.2. Удерживаемый набор результатов
Чтобы создать удерживаемый ResultSet , нам нужно указать тип удерживаемого как последний параметр при создании Statement . Этот параметр указывается после режима параллелизма.
Обратите внимание, что если мы используем Microsoft SQL Server (MSSQL), мы должны установить удержание для соединения с базой данных, а не для ResultSet :
dbConnection.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
Давайте посмотрим на это в действии. Во-первых, давайте создадим Statement , установив удерживаемость на HOLD_CURSORS_OVER_COMMIT :
Statement pstmt = dbConnection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE,
ResultSet.HOLD_CURSORS_OVER_COMMIT)
Теперь давайте обновим строку при получении данных. Это похоже на пример обновления, который мы обсуждали ранее, за исключением того, что мы продолжим итерацию по ResultSet после фиксации транзакции обновления в базе данных. Это отлично работает как с базами данных MySQL, так и с MSSQL:
dbConnection.setAutoCommit(false);
ResultSet rs = pstmt.executeQuery("select * from employees");
while (rs.next()) {
if(rs.getString("name").equalsIgnoreCase("john")) {
rs.updateString("name", "John Doe");
rs.updateRow();
dbConnection.commit();
}
}
rs.last();
Стоит отметить, что MySQL поддерживает только HOLD_CURSORS_OVER_COMMIT . Таким образом, даже если мы используем CLOSE_CURSORS_AT_COMMIT , он будет проигнорирован.
База данных MSSQL поддерживает CLOSE_CURSORS_AT_COMMIT . Это означает, что ResultSet будет закрыт, когда мы зафиксируем транзакцию. В результате попытка доступа к ResultSet после фиксации транзакции приводит к ошибке «Курсор не открыт». Поэтому мы не можем получить дополнительные записи из ResultSet .
7. Размер выборки
Обычно при загрузке данных в ResultSet драйверы базы данных определяют количество строк, которые необходимо извлечь из базы данных. Например, в базе данных MySQL ResultSet обычно загружает все записи в память сразу.
Однако иногда нам может понадобиться иметь дело с большим количеством записей, которые не помещаются в нашу память JVM. В этом случае мы можем использовать свойство размера выборки либо в нашем объекте Statement , либо в объектах ResultSet , чтобы ограничить количество первоначально возвращаемых записей.
Всякий раз, когда требуются дополнительные результаты, ResultSet извлекает из базы данных еще один пакет записей. Используя свойство размера выборки, мы можем предоставить драйверу базы данных предложение о количестве строк для выборки за одно обращение к базе данных . Указанный нами размер выборки будет применяться к последующим обращениям к базе данных.
Если мы не указываем размер выборки для нашего ResultSet , используется размер выборки оператора . Если мы не указываем размер выборки ни для Statement , ни для ResultSet , то используется база данных по умолчанию.
7.1. Использование размера выборки в заявлении
Теперь давайте посмотрим на размер выборки в Statement в действии. Мы установим размер выборки оператора равным 10 записям. Если наш запрос вернет 100 записей, то будет 10 циклов обращения к базе данных, каждый раз загружая 10 записей:
PreparedStatement pstmt = dbConnection.prepareStatement(
"select * from employees",
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(10);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
// iterate through the resultset
}
7.2. Использование размера выборки в ResultSet
Теперь давайте изменим размер выборки в нашем предыдущем примере, используя ResultSet .
Во-первых, мы будем использовать размер выборки в нашем Заявлении . Это позволяет нашему ResultSet изначально загружать 10 записей после выполнения запроса.
Затем мы изменим размер выборки в ResultSet . Это переопределит размер выборки, который мы ранее указали в нашем Заявлении . Таким образом, все последующие поездки будут загружать 20 записей, пока не будут загружены все записи.
В итоге для загрузки всех записей будет всего 6 обращений к БД:
PreparedStatement pstmt = dbConnection.prepareStatement(
"select * from employees",
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(10);
ResultSet rs = pstmt.executeQuery();
rs.setFetchSize(20);
while (rs.next()) {
// iterate through the resultset
}
Наконец, мы увидим, как изменить размер выборки ResultSet при повторении результатов.
Как и в предыдущем примере, мы сначала установим размер выборки равным 10 в нашем операторе . Таким образом, наши первые 3 обращения к базе данных будут загружать по 10 записей за каждое обращение.
Затем мы изменим размер выборки в нашем наборе результатов до 20 при чтении 30-й записи. Таким образом, следующие 4 поездки будут загружать по 20 записей за каждую поездку.
Следовательно, нам потребуется 7 обращений к базе данных, чтобы загрузить все 100 записей:
PreparedStatement pstmt = dbConnection.prepareStatement(
"select * from employees",
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(10);
ResultSet rs = pstmt.executeQuery();
int rowCount = 0;
while (rs.next()) {
// iterate through the resultset
if (rowCount == 30) {
rs.setFetchSize(20);
}
rowCount++;
}
8. Заключение
В этой статье мы увидели, как использовать API ResultSet для извлечения и обновления данных из базы данных. Некоторые из расширенных функций, которые мы обсуждали, зависят от используемой базы данных. Таким образом, нам нужно проверить поддержку этих функций, прежде чем использовать их.
Как всегда, код доступен на GitHub .