Использование параметров, возвращающих табличные значения
Параметры, возвращающие табличное значение, упрощают маршалинг нескольких строк данных из клиентского приложения в SQL Server, устраняя потребность в нескольких круговых путях или специальной серверной логике для обработки данных. Параметры, возвращающие табличное значение, можно использовать для инкапсуляции строк данных в клиентском приложении и их отправки на сервер единой параметризованной командой. Входящие строки данных хранятся в переменной таблицы, которой затем можно управлять с помощью Transact-SQL.
Доступ к значениям столбца в возвращающих табличное значение параметрах обеспечивается с помощью стандартных инструкций Transact-SQL SELECT. Возвращающие табличное значение параметры строго типизированы, и проверка их структуры происходит автоматически. Размер возвращающих табличное значение параметров ограничен только объемом памяти сервера.
Поддержка возвращающих табличное значение параметров доступна, начиная с Microsoft JDBC Driver 6.0 для SQL Server.
В параметре, возвращающем табличное значение, невозможно вернуть данные. Возвращающие табличное значение параметры являются только входными. Ключевое слово OUTPUT не поддерживается.
Дополнительные сведения о возвращающих табличное значение параметрах см. в следующих ресурсах.
| Ресурс | Описание |
|---|---|
| Возвращающие табличное значение параметры (ядро СУБД) в электронной документации на SQL Server | Здесь описывается создание и использование возвращающих табличное значение параметров. |
| Определяемые пользователем табличные типы в электронной документации на SQL Server | Здесь описывается использование определяемых пользователем табличных типов для объявления возвращающих табличное значение параметров. |
Передача нескольких строк в предыдущих версиях SQL Server
До появления в SQL Server 2008 параметров, возвращающих табличное значение, были ограниченны возможности передачи нескольких строк данных в хранимую процедуру или параметризованную команду SQL. Разработчик может выбрать один из следующих вариантов передачи нескольких строк на сервер.
- Использовать ряд отдельных параметров, чтобы представить значения в нескольких столбцах и строках данных. Объем данных, которые можно передать с помощью этого метода, ограничен количеством допустимых параметров. В процедурах SQL Server можно использовать не более 2100 параметров. Для сборки этих отдельных значений в табличную переменную или временную таблицу для обработки требуется логика на стороне сервера.
- Объединить несколько значений данных в строки с разделителями или документы XML, а затем передать эти текстовые значения в процедуру или инструкцию. Для этого требуется, чтобы процедура или инструкция содержали логику, необходимую для проверки структур данных и разъединения значений.
- Создать ряд отдельных инструкций SQL для изменений данных, затрагивающих несколько строк. Изменения можно отправлять на сервер по отдельности или объединять в группы. Тем не менее даже при отправке в пакетах, содержащих несколько инструкций, каждая из них выполняется на сервере отдельно.
- Использовать программу bcp или SQLServerBulkCopy, чтобы загрузить в таблицу множество строк данных. Хотя этот метод эффективен, он не поддерживает обработку на стороне сервера, если данные не загружены во временную таблицу или табличную переменную.
Создание типов параметров, возвращающих табличное значение
Возвращающие табличное значение параметры основаны на строго типизированных табличных структурах, заданных с помощью инструкций Transact-SQL CREATE TYPE . Перед использованием в клиентских приложениях возвращающих табличное значение параметров в SQL Server необходимо создать табличный тип и определить структуру. Дополнительные сведения о создании типов таблиц см. в разделе Определяемые пользователем табличные типы электронной документации на SQL Server.
CREATE TYPE dbo.CategoryTableType AS TABLE ( CategoryID int, CategoryName nvarchar(50) ) После создания табличного типа можно объявить возвращающие табличное значение параметры на основе этого типа. В приведенном ниже фрагменте кода Transact-SQL демонстрируется объявление возвращающего табличное значение параметра в определении хранимой процедуры. Для объявления возвращающего табличное значение параметра требуется ключевое слово READONLY .
CREATE PROCEDURE usp_UpdateCategories (@tvpNewCategories dbo.CategoryTableType READONLY) Изменение данных с помощью возвращающих табличное значение параметров (Transact-SQL)
Возвращающие табличное значение параметры можно использовать во влияющих на несколько строк изменениях данных на основе наборов, выполняя одну инструкцию. Например, можно выбрать в возвращающем табличное значение параметре все строки и вставить их в таблицу базы данных. Кроме того, можно создать инструкцию UPDATE, присоединив возвращающий табличное значение параметр к таблице, которую необходимо обновить.
В приведенной ниже инструкции Transact-SQL UPDATE демонстрируется соединение возвращающего табличное значение параметра с таблицей Categories. При использовании возвращающего табличное значение параметра со значением JOIN в предложении FROM необходимо также присвоить ему псевдоним, как показано здесь, где параметр с табличным значением имеет псевдоним «ec»:
UPDATE dbo.Categories SET Categories.CategoryName = ec.CategoryName FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec ON dbo.Categories.CategoryID = ec.CategoryID; В этом примере кода Transact-SQL демонстрируется выбор строк из возвращающего табличное значение параметра для выполнения инструкции INSERT в одной операции на основе набора данных.
INSERT INTO dbo.Categories (CategoryID, CategoryName) SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc; Ограничения возвращающих табличное значение параметров
Для возвращающих табличное значение параметров существует несколько ограничений.
- Возвращающие табличное значение параметры нельзя передавать определяемым пользователем функциям.
- Возвращающие табличное значение параметры могут индексироваться только для поддержки ограничений UNIQUE или PRIMARY KEY. SQL Server не ведет статистику возвращающих табличные значения параметров.
- В коде Transact-SQL возвращающие табличное значение параметры предназначены только для чтения. Нельзя обновлять значения столбцов в строках возвращающего табличное значение параметра, а также вставлять или удалять строки. Чтобы изменить данные, передаваемые в хранимую процедуру или параметризованную инструкцию в возвращающем табличное значение параметре, необходимо вставить данные во временную таблицу или в табличную переменную.
- Нельзя использовать инструкции ALTER TABLE для изменения структуры возвращающих табличное значение параметров.
- В возвращающем табличное значение параметре можно передавать большие объекты.
Настройка параметра, возвращающего табличные значения
Начиная с Microsoft JDBC Driver 6.0 для SQL Server, возвращающие табличное значение параметры поддерживаются с параметризованной инструкцией или параметризованной хранимой процедурой. Возвращающие табличное значение параметры можно заполнить из таблицы SQLServerDataTable, из результирующего набора или из реализации интерфейса ISQLServerDataRecord, предоставленного пользователем. При указании возвращающего табличное значение параметра для подготовленного запроса необходимо указать имя типа, которое должно совпадать с именем совместимого типа, ранее созданного на сервере.
В следующих двух фрагментах кода показано, как настроить возвращающий табличное значение параметр с инструкциями SQLServerPreparedStatement и SQLServerCallableStatement для вставки данных. Здесь объект sourceTVPObject может быть объектом SQLServerDataTable, результирующим набором или объектом ISQLServerDataRecord. В примерах предполагается, что подключение является активным объектом Connection.
// Using table-valued parameter with a SQLServerPreparedStatement. SQLServerPreparedStatement pStmt = (SQLServerPreparedStatement) connection.prepareStatement("INSERT INTO dbo.Categories SELECT * FROM ?"); pStmt.setStructured(1, "dbo.CategoryTableType", sourceTVPObject); pStmt.execute(); // Using table-valued parameter with a SQLServerCallableStatement. SQLServerCallableStatement pStmt = (SQLServerCallableStatement) connection.prepareCall("exec usp_InsertCategories ?"); pStmt.setStructured(1, "dbo.CategoryTableType", sourceTVPObject);; pStmt.execute(); Полный список интерфейсов API, доступных для настройки возвращающего табличное значение параметра, см. в разделе API-интерфейс возвращающего табличное значение параметра для драйвера JDBC ниже.
Передача возвращающего табличное значение параметра в виде объекта SQLServerDataTable
Начиная с Microsoft JDBC Driver 6.0 для SQL Server, класс SQLServerDataTable представляет таблицу реляционных данных в памяти. В этом примере показано, как создать возвращающий табличное значение параметр из данных в памяти с помощью объекта SQLServerDataTable. Сначала код создает объект SQLServerDataTable, определяет его схему и заполняет таблицу данными. Затем код настраивает SQLServerPreparedStatement, который передает эту таблицу данных в качестве возвращающего табличное значение параметра для SQL Server.
/* Assumes connection is an active Connection object. */ // Create an in-memory data table. SQLServerDataTable sourceDataTable = new SQLServerDataTable(); // Define metadata for the data table. sourceDataTable.addColumnMetadata("CategoryID" ,java.sql.Types.INTEGER); sourceDataTable.addColumnMetadata("CategoryName" ,java.sql.Types.NVARCHAR); // Populate the data table. sourceDataTable.addRow(1, "CategoryNameValue1"); sourceDataTable.addRow(2, "CategoryNameValue2"); // Pass the data table as a table-valued parameter using a prepared statement. SQLServerPreparedStatement pStmt = (SQLServerPreparedStatement) connection.prepareStatement( "INSERT INTO dbo.Categories SELECT * FROM ?;"); pStmt.setStructured(1, "dbo.CategoryTableType", sourceDataTable); pStmt.execute(); Этот пример аналогичен предыдущему. Единственное отличие состоит в том, что он задает имя TVP для SQLServerDataTable вместо того, чтобы полагаться на приведение PreparedStatement к SQLServerPreparedStatement для использования метода setStructured .
/* Assumes connection is an active Connection object. */ // Create an in-memory data table. SQLServerDataTable sourceDataTable = new SQLServerDataTable(); sourceDataTable.setTvpName("dbo.CategoryTableType"); // Define metadata for the data table. sourceDataTable.addColumnMetadata("CategoryID" ,java.sql.Types.INTEGER); sourceDataTable.addColumnMetadata("CategoryName" ,java.sql.Types.NVARCHAR); // Populate the data table. sourceDataTable.addRow(1, "CategoryNameValue1"); sourceDataTable.addRow(2, "CategoryNameValue2"); // Pass the data table as a table-valued parameter using a prepared statement. PreparedStatement pStmt = connection.prepareStatement( "INSERT INTO dbo.Categories SELECT * FROM ?;"); pStmt.setObject(1, sourceDataTable); pStmt.execute(); Полный список интерфейсов API, доступных для настройки возвращающего табличное значение параметра, см. в разделе API-интерфейс возвращающего табличное значение параметра для драйвера JDBC ниже.
Передача возвращающего табличное значение параметра в виде объекта ResultSet
В этом примере демонстрируется потоковая передача строк данных из результирующего набора в возвращающий табличное значение параметр. Сначала код извлекает данные из исходной таблицы, создает объект SQLServerDataTable, определяет его схему и заполняет таблицу данными. Затем код настраивает SQLServerPreparedStatement, который передает эту таблицу данных в качестве возвращающего табличное значение параметра для SQL Server.
/* Assumes connection is an active Connection object. */ // Create the source ResultSet object. Here SourceCategories is a table defined with the same schema as Categories table. ResultSet sourceResultSet = connection.createStatement().executeQuery("SELECT * FROM SourceCategories"); // Pass the source result set as a table-valued parameter using a prepared statement. SQLServerPreparedStatement pStmt = (SQLServerPreparedStatement) connection.prepareStatement( "INSERT INTO dbo.Categories SELECT * FROM ?;"); pStmt.setStructured(1, "dbo.CategoryTableType", sourceResultSet); pStmt.execute(); Полный список интерфейсов API, доступных для настройки возвращающего табличное значение параметра, см. в разделе API-интерфейс возвращающего табличное значение параметра для драйвера JDBC ниже.
Передача возвращающего табличное значение параметра в виде объекта ISQLServerDataRecord
Начиная с Microsoft JDBC Driver 6.0 для SQL Server, доступен новый интерфейс ISQLServerDataRecord для потоковой передачи данных (в зависимости от того, как пользователь предоставляет реализацию) с помощью возвращающего табличное значение параметра. В следующем примере показано, как реализовать интерфейс ISQLServerDataRecord и передать его в качестве возвращающего табличное значение параметра. Для простоты в следующем примере в возвращающий табличное значение параметр передается только одна строка с жестко заданными значениями. В идеале пользователь будет реализовывать этот интерфейс для потоковой передачи строк из любого источника, например из текстовых файлов.
class MyRecords implements ISQLServerDataRecord < int currentRow = 0; Object[] row = new Object[2]; MyRecords()< // Constructor. This implementation has just one row. row[0] = new Integer(1); row[1] = "categoryName1"; >public int getColumnCount() < // Return the total number of columns, for this example it is 2. return 2; >public SQLServerMetaData getColumnMetaData(int columnIndex) < // Return the column metadata. if (1 == columnIndex) return new SQLServerMetaData("CategoryID", java.sql.Types.INTEGER); else return new SQLServerMetaData("CategoryName", java.sql.Types.NVARCHAR); >public Object[] getRowData() < // Return the columns in the current row as an array of objects. This implementation has just one row. return row; >public boolean next() < // Move to the next row. This implementation has just one row, after processing the first row, return false. currentRow++; if (1 == currentRow) return true; else return false; >> // Following code demonstrates how to pass MyRecords object as a table-valued parameter. MyRecords sourceRecords = new MyRecords(); SQLServerPreparedStatement pStmt = (SQLServerPreparedStatement) connection.prepareStatement( "INSERT INTO dbo.Categories SELECT * FROM ?;"); pStmt.setStructured(1, "dbo.CategoryTableType", sourceRecords); pStmt.execute(); Полный список интерфейсов API, доступных для настройки возвращающего табличное значение параметра, см. в разделе API-интерфейс возвращающего табличное значение параметра для драйвера JDBC ниже.
API-интерфейс возвращающего табличное значение параметра для драйвера JDBC
SQLServerMetaData
Этот класс представляет метаданные для столбца. Он используется в интерфейсе ISQLServerDataRecord для передачи метаданных столбца в возвращающий табличное значение параметр. В этом классе используются следующие методы.
SQLServerSortOrder
Перечисление, определяющее порядок сортировки. Допустимые значения: Ascending, Descending и Unspecified.
SQLServerDataTable
Этот класс представляет таблицу данных в памяти для использования с возвращающими табличное значение параметрами. В этом классе используются следующие методы.
| Имя | Описание |
|---|---|
| Public SQLServerDataTable() | Инициализирует новый экземпляр SQLServerDataTable. |
| public Iterator> getIterator() | Извлекает итератор в строках таблицы данных. |
| public void addColumnMetadata(String columnName, int sqlType) | Добавляет метаданные для указанного столбца. |
| public void addColumnMetadata(SQLServerDataColumn column) | Добавляет метаданные для указанного столбца. |
| public void addRow(Object. values) | Добавляет одну строку данных в таблицу данных. |
| public Map getColumnMetadata() | Извлекает метаданные столбца этой таблицы данных. |
| public void clear() | Очищает эту таблицу данных. |
SQLServerDataColumn
Этот класс представляет столбец таблицы данных в памяти, представленный SQLServerDataTable. В этом классе используются следующие методы.
| Имя | Описание |
|---|---|
| public SQLServerDataColumn(String columnName, int sqlType) | Инициализирует новый экземпляр SQLServerDataColumn с именем и типом столбца. |
| public String getColumnName() | Извлекает имя столбца. |
| public int getColumnType() | Извлекает тип столбца. |
ISQLServerDataRecord
Этот класс представляет интерфейс, который пользователи могут реализовать для потоковой передачи данных в возвращающий табличное значение параметр. В этом интерфейсе используются следующие методы.
| Имя | Описание |
|---|---|
| public SQLServerMetaData getColumnMetaData(int column); | Извлекает метаданные столбца заданного индекса столбца. |
| public int getColumnCount(); | Извлекает общее число столбцов. |
| public Object[] getRowData(); | Получает данные для текущей строки как массив объектов. |
| public boolean next(); | Переходит к следующей строке. Возвращает значение true, если перемещение выполнено успешно и имеется наведенная дальше строка. В противном случае возвращает значение false. |
SQLServerPreparedStatement
Для поддержки передачи возвращающих табличное значение параметров в этот класс были добавлены следующие методы.
| Имя | Описание |
|---|---|
| public final void setStructured(int parameterIndex, String tvpName, SQLServerDataTable tvpDataTable) | Заполняет возвращающий табличное значение параметр таблицей данных. parameterIndex — индекс параметра, tvpName — имя возвращающего табличное значение параметра, а tvpDataTable — объект исходной таблицы данных. |
| public final void setStructured(int parameterIndex, String tvpName, ResultSet tvpResultSet) | Заполняет возвращающий табличное значение параметр объектом ResultSet, полученным из другой таблицы. parameterIndex — индекс параметра, tvpName — имя возвращающего табличное значение параметра, а tvpResultSet — объект исходного результирующего набора. |
| public final void setStructured(int parameterIndex, String tvpName, ISQLServerDataRecord tvpDataRecord) | Заполняет возвращающий табличное значение параметр объектом ISQLServerDataRecord. ISQLServerDataRecord используется для потоковой передачи данных, и пользователь решает, как его использовать. parameterIndex — номер параметра, tvpName — имя табличного параметра, а tvpDataRecord — объект ISQLServerDataRecord. |
SQLServerCallableStatement
Для поддержки передачи возвращающих табличное значение параметров в этот класс были добавлены следующие методы.
| Имя | Описание |
|---|---|
| public final void setStructured(String paratemeterName, String tvpName, SQLServerDataTable tvpDataTable) | Заполняет возвращающий табличное значение параметр, передаваемый хранимой процедуре, таблицей данных. paratemeterName — имя параметра, tvpName — имя типа TVP, а tvpDataTable — объект таблицы данных. |
| public final void setStructured(String paratemeterName, String tvpName, ResultSet tvpResultSet) | Заполняет возвращающий табличное значение параметр, передаваемый хранимой процедуре, результирующим набором, полученным из другой таблицы. paratemeterName — имя параметра, tvpName — имя типа TVP, а tvpResultSet — исходный объект результирующего набора. |
| public final void setStructured(String paratemeterName, String tvpName, ISQLServerDataRecord tvpDataRecord) | Заполняет возвращающий табличное значение параметр, передаваемый хранимой процедуре, объектом ISQLServerDataRecord. ISQLServerDataRecord используется для потоковой передачи данных, и пользователь решает, как его использовать. paratemeterName — имя параметра, tvpName — имя табличного параметра, а tvpDataRecord — объект ISQLServerDataRecord. |
Какой метод класса statement надо использовать для посылки sql команды select from table
Для взаимодействия с базой данных приложение отправляет серверу MySQL команды на языке SQL. Чтобы выполнить команду, вначале необходимо создаеть объект Statement .
Для его создания у объекта Connection вызывается метод createStatement() :
Statement statement = conn.createStatement();
Для выполнения команд SQL в классе Statement определено три метода:
- executeUpdate : выполняет такие команды, как INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE. В качестве результата возвращает количество строк, затронутых операцией (например, количество добавленных, измененных или удаленных строк), или 0, если ни одна строка не затронута операцией или если команда не изменяет содержимое таблицы (например, команда создания новой таблицы)
- executeQuery : выполняет команду SELECT. Возвращает объект ResultSet, который содержит результаты запроса.
- execute() : выполняет любые команды и возвращает значение boolean: true — если команда возвращает набор строк (SELECT), иначе возвращается false.
Рассмотрим метод executeUpdate() . В качестве параметра в него передается собственно команда SQL:
int executeUpdate("Команда_SQL")
Ранее была создана база данных store, но она пустая, в ней нет таблиц и соответственно данных. Создадим таблицу и добавим в нее начальные данные:
import java.sql.*; public class Program < public static void main(String[] args) < try< String url = "jdbc:mysql://localhost/store?serverTimezone=Europe/Moscow&useSSL=false"; String username = "root"; String password = "password"; Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance(); // команда создания таблицы String sqlCommand = "CREATE TABLE products (Id INT PRIMARY KEY AUTO_INCREMENT, ProductName VARCHAR(20), Price INT)"; try (Connection conn = DriverManager.getConnection(url, username, password))< Statement statement = conn.createStatement(); // создание таблицы statement.executeUpdate(sqlCommand); System.out.println("Database has been created!"); >> catch(Exception ex) < System.out.println("Connection failed. "); System.out.println(ex); >> >
То есть в данном случае мы выполняем команду CREATE TABLE products (Id INT PRIMARY KEY AUTO_INCREMENT, ProductName VARCHAR(20), Price INT) , которая создает таблицу Products с тремя столбцами: Id — индентификатор стоки, ProductName — строковое название товара и Price — числовая цена товара.
При этом если необходимо выполнить сразу несколько команд, то необязательно создавать новый объект Statement:
Statement statement = conn.createStatement(); statement.executeUpdate("Команда_SQL1"); statement.executeUpdate("Команда_SQL2"); statement.executeUpdate("Команда_SQL3");
Какой метод класса statement надо использовать для посылки sql команды select from table
Кроме класса Statement в java.sql мы можем использовать для выполнения запросов еще один класс — PreparedStatement . Кроме собственно выполнения запроса этот класс позволяет подготовить запрос, отформатировать его должным образом.
Например, в прошлых темах была создана таблица, которая имеет три столбца:
CREATE TABLE products ( Id INT PRIMARY KEY AUTO_INCREMENT, ProductName VARCHAR(20), Price INT )
С помощью PreparedStatement добавим в нее один объект:
import java.sql.*; import java.util.Scanner; public class Program < public static void main(String[] args) < try< String url = "jdbc:mysql://localhost/store?serverTimezone=Europe/Moscow&useSSL=false"; String username = "root"; String password = "password"; Scanner scanner = new Scanner(System.in); Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance(); System.out.print("Input product name: "); String name = scanner.nextLine(); System.out.print("Input product price: "); int price = scanner.nextInt(); try (Connection conn = DriverManager.getConnection(url, username, password))< String sql = "INSERT INTO Products (ProductName, Price) Values (?, ?)"; PreparedStatement preparedStatement = conn.prepareStatement(sql); preparedStatement.setString(1, name); preparedStatement.setInt(2, price); int rows = preparedStatement.executeUpdate(); System.out.printf("%d rows added", rows); >> catch(Exception ex) < System.out.println("Connection failed. "); System.out.println(ex); >> >
В данном случае данные вводятся с консоли и затем добавляются в базу данных. Для создания объекта PreparedStatement применяется метод prepareStatement() класса Connection. В этот метод передается выражение sql INSERT INTO Products (ProductName, Price) Values (?, ?) . Это выражение может содержать знаки вопроса ? — знаки подстановки, вместо которых будут вставляться реальные значения.
Чтобы связать отдельные знаки подстановки с конкретными значениями у класса PreparedStatement определен ряд методов для различных типов данных. Все методы, которые поставляют значения вместо знаков подстановки, в качестве первого параметра принимают порядковый номер знака подстановки (нумерация начинается с 1), а в качестве второго параметра — собственно значение, которое вставляется вместо знака подстановки.
Например, первый знак подстановки ? в выражении sql представляет значение для столбца ProductName, который хранит строку. Поэтому для связи значения с первым знаком подстановки применяется метод preparedStatement.setString(1, name) .
Второй знак подстановки должен передавать значение для столбца Price, который хранит целые числа. Поэтому для вставик значения используется метод preparedStatement.setInt(2, price)
Кроме setString и setInt PreparedStatement имеет еще ряд подобных методов, которые работают подобным образом. Некоторые из них:
- setBigDecimal
- setBoolean
- setDate
- setDouble
- setFloat
- setLong
- setNull
- setTime
Для выполнения запроса PreparedStatement имеет три метода:
- boolean execute() : выполняет любую SQL-команду
- ResultSet executeQuery() : выполняет команду SELECT, которая возвращает данные в виде ResultSet
- int executeUpdate() : выполняет такие SQL-команды, как INSERT, UPDATE, DELETE, CREATE и возвращает количество измененных строк
При этом в отличие от методов Statement эти методы не принимают SQL-выражение.
Пример выполнения программы:
C:\Java>javac Program.java C:\Java>java -classpath c:\Java\mysql-connector-java-8.0.11.jar;c:\Java Program Inpit product name: Xiaomi Mi 8 Input product price: 35000 1 rows added C:\Java>
Подобным образом мы можем выполнять и другие выражения. Например, получим товары, у которых цена меньше 50000:
int maxPrice = 50000; PreparedStatement preparedStatement = conn.prepareStatement(«SELECT * FROM Products WHERE Price
Java for web JDBC
Что такое JDBC
JDBC (Java DataBase Connectivity) — это прикладной
программный интерфейс (далее API) для выполнения SQLзапросов. Он состоит из множества классов и интерфейсов,
написанных на языке Java.
JDBC предоставляет стандартный API для разработчиков,
использующих базы данных (далее БД). С помощью JDBC
можно писать приложения на языке Java, использующие БД.
С помощью JDBC легко отсылать SQL-запросы почти ко
всем реляционным БД. Другими словами, использование
JDBC API избавляет от необходимости для каждой СУБД
писать свое приложение.
Достаточно написать одну единственную программу,
использующую JDBC API, и эта программа сможет отсылать
SQL-запросы к требуемой БД. Кроме того, это проложение
будет переносимо на различные платформы.
3.
Структура JDBC
С точки зрения разработчика можно
считать, что JDBC состоит из двух
основных частей:
JDBC API, который содержит набор
классов и интерфейсов, определяющих
Java – ориентированный доступ к базам
данных. Эти классы и методы
объявлены в двух пакетах (package)
java.sql и javax.sql
JDBC-драйвера, специфического для
каждой базы данных (или других
источников данных) JDBC превращает
(тем или иным способом) вызовы уровня
JDBC API в «родные» команды того или
иного сервера баз данных.
Другими словами.
В задачу менеджера драйверов также
входит присоединение Java-приложений
к требуемому драйверу JDBC
Драйвер поддерживает обмен данными
между приложением и
базой данных
Приложение на Java
Менеджер драйверов
Драйвер
Драйвер
…
База
данных
База
данных
4.
Основные классы и интерфейсы JDBC
java.sql.DriverManager — позволяет загрузить и зарегистрировать
необходимый JDBC-драйвер, а затем получить соединение с базой
данных.
java.sql.Connection — обеспечивает формирование запросов к
источнику данных и управление транзакциями. Предусмотрены
также интерфейсы javax.sql.PooledConnection (логическое
соединение с БД из пула соединений) и javax.sql.XAConnection
(логическое соединение с БД из пула, сопоставленное с внешней
транзакцией).
java.sql.Statement , java.sql.PreparedStatement и
java.sql.CallableStatenient — эти интерфейсы позволяют отправить
запрос к источнику данных. Различные виды интерфейсов
применяются в зависимости от того, используются в запросе
параметры или нет и является ли запрос обращением к хранимой
процедуре реляционной базы данных.
java.sql.ResultSet — объявляет методы, которые позволяют перемещаться по набору данных, возвращаемых оператором SELECT, и
считывать значения отдельных полей в текущей записи
5.
Основные типы данных JDBC
Так как SQL-типы и Java-типы данных не идентичны, то необходим какой-либо
механизм передачи данных между Java-приложением и СУБД.
К счастью, программистам не обязательно отягощать себя именами типов SQL,
используемых в таблицах БД.
JDBC поддерживает взаимное отображение между типами данных, характерных для
использования SQL, и их Java-аналогами.
При программировании с
использованием JDBC API
программисты могут
использовать эти JDBC-типы
для обращения к базовым типам
SQL незаботясь о том, какие
имена типов данных
использовались при создании
БД.
В таблице приведены
соответствия между основными
типами.
JDBC Type
Java Type
CHAR
String
VARCHAR
String
LONGVARCHAR
String
BOOLEAN
Boolean
INTEGER
Int
BIGINT
Long
…
…
6.
Соединение
Регистрация драйверов осуществляется классом DriverManager. Он содержит
информацию о всех зарегистрированных драйверах.
Метод getConnection на основании параметра URL находит java.sql.Driver
соответствующей базы данных и вызывает у него метод connect.
Задача класса DriverManager — обеспечить поиск нужного JDBC-драйвера среди
всех доступных при поступлении запроса клиента, который содержит URL нужной базы
данных.
Во всех примерах подключения к базе данных вы обязательно встретите эти
строки:
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password) ;
Вызов Class.forName загружает класс , инициализирует, и осуществляет регистрацию
драйвера в DriverManager.
Пример для MySQL:
Class.forName(«com.mysql.jdbc.Driver»);
Connection conn =
DriverManager.getConnection(«jdbc:mysql://localhost:3306/stunet3?user=ИМЯ&password=
GПАРОЛЬ&characterEncoding=UTF-8″);
7.
Выполнение SQL-команд
В JDBC есть три класса для посылки SQL-запросов в БД и три метода в интерфейсе
Connection создают экземпляры этих классов.
Statement — создается методом createStatement. Объект Statement используется при
простых SQL-запросах.
PreparedStatement — создается методом prepareStatement. Объект
PreparedStatement используется в SQL-запросах с одним или более входными
параметрами (IN parameters). PreparedStatement содержит группу методов,
устанавливающих значения входных параметров, которые отсылаются в БД при
выполнении запроса. Экземпляры класса PreparedStatement расширяют (наследуются
от) Statement и, таким образом, включают методы Statement. Объект PreparedStatement
потенциально может быть более эффективным, чем Statement, так как он
прекомпилируется и сохраняется для будущего использования.
CallableStatement — создается методом prepareCall. Объекты CallableStatement
используются для выполнения т.н. хранимых процедур — именованных групп SQLзапросов, наподобие вызова подпрограммы. Объект CallableStatement наследует
методы обработки входных (IN) параметров из PreparedStatement, а также добавляет
методы для обработки выходных (OUT) и входных-выходных (INOUT) параметров.
Другими словами: Интерфейс Statement предоставляет базовые методы для
выполнения запросов и извлечения результатов. Интерфейс PreparedStatement
добавляет методы управления входными (IN) параметрами; CallableStatement
добавляет методы для манипуляции выходними (OUT) параметрами.
8.
Создание объектов Statement
Как только соединение с определенной БД установлено, оно может использоваться для
выполнения SQL-запросов. Объект Statement создается методом
Connection.createStatement, как показано ниже:
try Class.forName(«com.mysql.jdbc.Driver»);
conn = DriverManager.getConnection(«url»);
loadAllStudents = conn.createStatement();
List studentsArray = new LinkedList();
rs = loadAllStudents.executeQuery(«SELECT * FROM students»);
while (rs.next()) Student newStudent = new Student();
newStudent.setIdStudent(rs.getInt(«id»));
newStudent.setNameStudent(rs.getString(«name»));
newStudent.setSurnameStudent(rs.getString(«surname»));
studentsArray.add(newStudent);
>
> catch (Exception e) e.printStackTrace();
>
9.
Методы выполнения SQL-команд
Интерфейс Statement предоставляет три различных метода выполнения SQLвыражений: executeQuery, executeUpdate и execute, в зависимости от SQLзапроса.
executeQuery() — для запросов, результатом которых является один
единственный набор значений, таких как запросов SELECT. Метод
возвращает набор данных, полученный из базы
executeUpdate() — для выполнения операторов INSERT, UPDATE или
DELETE, а также для операторов DDL (Data Definition Language). Метод
возвращает целое число, показывающее, сколько строк данных было
модифицировано
execute() – исполняет SQL-команды, которые могут возвращать различные
результаты. Например, может использоваться для операции CREATE TABLE,
и т.д.
Все методы выполнения SQL-запросов закрывают предыдущий набор
результатов (result set) у данного объекта Statement. Это означает, что перед
тем как выполнять следующий запрос над тем же объектом Statement, надо
завершить обработку результатов предыдущего (ResultSet).
10.
Наборы данных
Метод executeQuery() возвращает объект с
интерфейсом ResultSet, который хранит в
себе результат запроса к базе данных.
В наборе данных есть курсор, который может
указывать на одну из строк таблицы, эта
строка называется текущей.
1
id
next()
Курсор перемещается по строкам при
помощи метода next().
Сразу после получения набора данных его
курсор находится перед первой строкой.
Чтобы сделать первую строку текущей надо
вызвать метод next()
Поля текущей записи (колонки таблицы)
доступны программе при помощи методов
интерфейса ResultSet: getInt() getFloat(),
getString(), getDate() и им подобных.
getXXX()
2
3
name
salary
4
age
11.
Интерфейс PreparedStatement
Экземпляры PreparedStatement сохраняют скомпилированные SQLвыражения.
Особенностью SQL-выражений в PreparedStatement является то, что они могут
иметь параметры
Параметризованное выражение содержит знаки вопроса в своем тексте.
Например: “SELECT * FROM students WHERE > Перед выполнением запроса значение каждого вопросительного знака
явно устанавливается методами setXxx()
Например: ps.setInt(1, 30);
Использование PreparedStatement приводит к более быстрому выполнению
запросов при их многократном вызове с различными параметрами.
12.
Пример использования PreparedStatement
public Student getAllStudentId(int id) rs = null;
Student student = new Student();
try getStudById.setInt(1, id);
rs = getStudById.executeQuery();
if (rs.next()) student.setIdStudent(rs.getInt(«id»));
student.setNameStudent(rs.getString(«name»));
student.setSurnameStudent(rs.getString(«surname»));
student.setDate(rs.getDate(«date»));
student.setGroupe(rs.getString(«groupe»));
>
> catch (SQLException e) e.printStackTrace();
>
return student;
>
13.
Интерфейс CallableStatement
Интерфейс CallableStatement используется, когда необходимо обратиться к хранимой
процедуре.
Работа с интерфейсом CallableStatement усложняется несколькими обстоятельствами:
Хранимые процедуры сильно отличаются (с точки зрения синтаксиса их вызова)
для различных серверов баз данных
Хранимые процедуры отличаются друг от друга тем, какие результаты они могут
возвращать и как именно выполняется возврат.
Например, для некоторых серверов (точнее, иx JDBC-дpaйвepoв) хранимые
процедуры могут возвращать только out- (и inout-) параметры,
Для других серверов процедура возвращает out- и inout-параметры как поля в
наборе записей (типа ResultSet).
Третьи поддерживают и тот, и другой подход
При работе с интерфейсом CallableStatement (который является производным от
интерфейса PreparedStatement) широко используется escape-синтаксис, и программист
задает команду обращения к процедуре либо с помощью вызова метода
executeQuery(), либо с помощью executeUpdate().
В любом случае при работе с конкретным JDBC-драйвером нужно изучить, каким
образом этот драйвер обеспечивает взаимодействие с хранимыми процедурами
14.
Пример вызова хранимой процедуры.
Эта хранимая процедура имеет единственный входной аргумент типа
integer, а в качестве выходного результата тоже возвращает целое число
Синтаксис вызова хранимой процедуры и способ трактовки outаргументов являются специфическими для используемого JDBCдрайвера
СаllableStatement pstmt = connection.prepareCall («»);
// Настройка параметров и обращение к процедуре
pstmt.setInt (1, 1);
ResultSet rs =pstmt.executeQuery();
15.
Работа с пулом соединений
При создании нового коннекта к БД может потребоваться значительное
время, особенно если БД удаленная. Чтобы на каждое обращение к базе не
открывать новое соединение используется пул соединений
Применение пулов соединений позволяет повысить производительность
приложения за счет переиспользования объектов-соединений с БД.
В настоящее время большинство драйверов для работы с БД поддерживают
работу с пулами соединений.
Существует несколько реализаций пулов подключений к БД. Можно, сделать
и свою.