Использование массового копирования с помощью JDBC Driver

Скачать драйвер JDBC

Microsoft SQL Server включает популярную служебную программу командной строки bcp, позволяющую быстро выполнять массовое копирование больших файлов в таблицы или представления баз данных SQL Server. Класс SQLServerBulkCopy позволяет создавать решения для написания кода на Java, которые предоставляют аналогичные возможности. Существуют другие способы загрузки данных в таблицу SQL Server (например, с помощью инструкции INSERT), но SQLServerBulkCopy делает это значительно быстрее.

Класс SQLServerBulkCopy можно использовать для записи данных только в таблицы SQL Server. Но источником данных может быть не только SQL Server, а любой источник данных, если данные можно считать с помощью реализации ResultSet, RowSet или ISQLServerBulkRecord.

Класс SQLServerBulkCopy предоставляет следующие возможности:

• одну операцию массового копирования;

• Несколько операций массового копирования

• операцию массового копирования с транзакцией.

Пример настройки массового копирования

Класс SQLServerBulkCopy можно использовать для записи данных только в таблицы SQL Server. В примерах кода в этой статье используется пример базы данных SQL Server с именем AdventureWorks. Чтобы не допустить изменения существующих таблиц в примерах кода, запишите данные в таблицы, которые сначала необходимо создать.

Таблицы BulkCopyDemoMatchingColumns и BulkCopyDemoDifferentColumns основаны на таблице AdventureWorks Production.Products. В примерах кода, использующих эти таблицы, данные из таблицы Production.Products добавляются к одной из этих таблиц-образцов. Таблица BulkCopyDemoDifferentColumns используется для демонстрации сопоставления столбцов из источника данных с целевой таблицей. Таблица BulkCopyDemoMatchingColumns используется в большинстве других примеров.

Некоторые примеры кода демонстрируют использование одного класса SQLServerBulkCopy для записи в несколько таблиц. В этих примерах таблицы BulkCopyDemoOrderHeader и BulkCopyDemoOrderDetail используются в качестве целевых. Эти таблицы основаны на таблицах Sales.SalesOrderHeader и Sales.SalesOrderDetail в AdventureWorks.

Настройка таблицы

Чтобы создать таблицы, необходимые для правильной работы примеров кода, выполните следующие инструкции Transact-SQL в базе данных SQL Server.

USE AdventureWorks2022;
GO
  
IF EXISTS (SELECT * FROM dbo.sysobjects
 WHERE id = object_id(N'[dbo].[BulkCopyDemoMatchingColumns]')
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1)  
    DROP TABLE [dbo].[BulkCopyDemoMatchingColumns]  
  
CREATE TABLE [dbo].[BulkCopyDemoMatchingColumns]([ProductID] [int] IDENTITY(1,1) NOT NULL,  
    [Name] [nvarchar](50) NOT NULL,  
    [ProductNumber] [nvarchar](25) NOT NULL,  
 CONSTRAINT [PK_ProductID] PRIMARY KEY CLUSTERED
(  
    [ProductID] ASC  
) ON [PRIMARY]) ON [PRIMARY]  
  
IF EXISTS (SELECT * FROM dbo.sysobjects
 WHERE id = object_id(N'[dbo].[BulkCopyDemoDifferentColumns]')
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1)  
    DROP TABLE [dbo].[BulkCopyDemoDifferentColumns]  
  
CREATE TABLE [dbo].[BulkCopyDemoDifferentColumns]([ProdID] [int] IDENTITY(1,1) NOT NULL,  
    [ProdNum] [nvarchar](25) NOT NULL,  
    [ProdName] [nvarchar](50) NOT NULL,  
 CONSTRAINT [PK_ProdID] PRIMARY KEY CLUSTERED
(  
    [ProdID] ASC  
) ON [PRIMARY]) ON [PRIMARY]  
  
IF EXISTS (SELECT * FROM dbo.sysobjects
 WHERE id = object_id(N'[dbo].[BulkCopyDemoOrderHeader]')
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1)  
    DROP TABLE [dbo].[BulkCopyDemoOrderHeader]  
  
CREATE TABLE [dbo].[BulkCopyDemoOrderHeader]([SalesOrderID] [int] IDENTITY(1,1) NOT NULL,  
    [OrderDate] [datetime] NOT NULL,  
    [AccountNumber] [nvarchar](15) NULL,  
 CONSTRAINT [PK_SalesOrderID] PRIMARY KEY CLUSTERED
(  
    [SalesOrderID] ASC  
) ON [PRIMARY]) ON [PRIMARY]  
  
IF EXISTS (SELECT * FROM dbo.sysobjects
 WHERE id = object_id(N'[dbo].[BulkCopyDemoOrderDetail]')
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1)  
    DROP TABLE [dbo].[BulkCopyDemoOrderDetail]  
  
CREATE TABLE [dbo].[BulkCopyDemoOrderDetail]([SalesOrderID] [int] NOT NULL,  
    [SalesOrderDetailID] [int] NOT NULL,  
    [OrderQty] [smallint] NOT NULL,  
    [ProductID] [int] NOT NULL,  
    [UnitPrice] [money] NOT NULL,  
 CONSTRAINT [PK_LineNumber] PRIMARY KEY CLUSTERED
(  
    [SalesOrderID] ASC,  
    [SalesOrderDetailID] ASC  
) ON [PRIMARY]) ON [PRIMARY]  
  

Одинарные операции массового копирования

Самый простой способ массового копирования SQL Server — выполнение одной операции в базе данных. По умолчанию операция массового копирования выполняется как изолированная, т. е. не как транзакция и без возможности отката.

Для массового копирования выполните следующие действия.

1. Подключитесь к исходному серверу и получите данные для копирования. Данные также могут поступать из других источников, если их можно извлечь из объекта ResultSet или реализации ISQLServerBulkRecord.

2. Подключитесь к целевому серверу (если только не требуется, чтобы объект SQLServerBulkCopy сам установил соединение).

3. Создайте объект SQLServerBulkCopy, задав все необходимые свойства через setBulkCopyOptions.

4. Вызовите метод setDestinationTableName, чтобы указать целевую таблицу для операции массовой вставки.

5. Вызовите один из методов writeToServer.

6. Кроме того, если это необходимо, можно обновить свойства через setBulkCopyOptions и снова вызвать метод writeToServer.

7. Вызовите close или заключите код операций массового копирования в инструкцию try-with-resources.

Пример

Следующее приложение показывает, как загрузить данные с помощью класса SQLServerBulkCopy. В этом примере ResultSet используется для копирования данных из таблицы Production.Product в базе данных SQL Server AdventureWorks в аналогичную таблицу в той же базе данных.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.microsoft.sqlserver.jdbc.SQLServerBulkCopy;

public class BulkCopySingle {
    public static void main(String[] args) {
        String connectionUrl = "jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=AdventureWorks;user=<user>;password=<password>";
        String destinationTable = "dbo.BulkCopyDemoMatchingColumns";
        int countBefore, countAfter;
        ResultSet rsSourceData;

        try (Connection sourceConnection = DriverManager.getConnection(connectionUrl);
                Connection destinationConnection = DriverManager.getConnection(connectionUrl);
                Statement stmt = sourceConnection.createStatement();
                SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(destinationConnection)) {

            // Empty the destination table.
            stmt.executeUpdate("DELETE FROM " + destinationTable);

            // Perform an initial count on the destination table.
            countBefore = getRowCount(stmt, destinationTable);

            // Get data from the source table as a ResultSet.
            rsSourceData = stmt.executeQuery("SELECT ProductID, Name, ProductNumber FROM Production.Product");

            // In real world applications you would
            // not use SQLServerBulkCopy to move data from one table to the other
            // in the same database. This is for demonstration purposes only.

            // Set up the bulk copy object.
            // Note that the column positions in the source
            // table match the column positions in
            // the destination table so there is no need to
            // map columns.
            bulkCopy.setDestinationTableName(destinationTable);

            // Write from the source to the destination.
            bulkCopy.writeToServer(rsSourceData);

            // Perform a final count on the destination
            // table to see how many rows were added.
            countAfter = getRowCount(stmt, destinationTable);
            System.out.println((countAfter - countBefore) + " rows were added.");
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static int getRowCount(Statement stmt,
            String tableName) throws SQLException {
        ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);
        rs.next();
        int count = rs.getInt(1);
        rs.close();
        return count;
    }
}

Выполнение операции массового копирования с помощью Transact-SQL

Следующий пример демонстрирует использование метода executeUpdate для выполнения инструкции BULK INSERT.

try (Connection con = DriverManager.getConnection(connectionUrl);
        Statement stmt = con.createStatement()) {
    // Perform the BULK INSERT
    stmt.executeUpdate(
            "BULK INSERT Northwind.dbo.[Order Details] " + "FROM 'f:\\mydata\\data.tbl' " + "WITH ( FORMATFILE='f:\\mydata\\data.fmt' )");
}

Несколько операций массового копирования

Вы можете выполнить несколько операций массового копирования, используя один экземпляр класса SQLServerBulkCopy. Если между копированиями параметры операции изменяются (например, имя целевой таблицы), необходимо обновить их до последующих вызовов любого из методов writeToServer, как показано в следующем примере. Если значения свойств не изменяются явным образом, все они остаются такими же, как при предыдущей операции массового копирования для данного экземпляра.

При выполнении нескольких операций массового копирования с одним объектом SQLServerBulkCopy не существует ограничений, касающихся совпадения или различий целевой информации в каждой операции. Однако для каждой записи на сервер необходимо убедиться, что связи столбцов настроены правильно.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.microsoft.sqlserver.jdbc.SQLServerBulkCopy;
import com.microsoft.sqlserver.jdbc.SQLServerBulkCopyOptions;

public class BulkCopyMultiple {
    public static void main(String[] args) {
        String connectionUrl = "jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=AdventureWorks;user=<user>;password=<password>";
        String destinationHeaderTable = "dbo.BulkCopyDemoOrderHeader";
        String destinationDetailTable = "dbo.BulkCopyDemoOrderDetail";
        int countHeaderBefore, countDetailBefore, countHeaderAfter, countDetailAfter;
        ResultSet rsHeader, rsDetail;

        try (Connection sourceConnection1 = DriverManager.getConnection(connectionUrl);
                Connection sourceConnection2 = DriverManager.getConnection(connectionUrl);
                Statement stmt = sourceConnection1.createStatement();
                PreparedStatement preparedStmt1 = sourceConnection1.prepareStatement(
                        "SELECT [SalesOrderID], [OrderDate], [AccountNumber] FROM [Sales].[SalesOrderHeader] WHERE [AccountNumber] = ?;");
                PreparedStatement preparedStmt2 = sourceConnection2.prepareStatement(
                        "SELECT [Sales].[SalesOrderDetail].[SalesOrderID], [SalesOrderDetailID], [OrderQty], [ProductID], [UnitPrice] FROM "
                                + "[Sales].[SalesOrderDetail] INNER JOIN [Sales].[SalesOrderHeader] ON "
                                + "[Sales].[SalesOrderDetail].[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID] WHERE [AccountNumber] = ?;");
                SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(connectionUrl);) {

            // Empty the destination tables.
            stmt.executeUpdate("DELETE FROM " + destinationHeaderTable);
            stmt.executeUpdate("DELETE FROM " + destinationDetailTable);

            // Perform an initial count on the destination
            // table with matching columns.
            countHeaderBefore = getRowCount(stmt, destinationHeaderTable);

            // Perform an initial count on the destination
            // table with different column positions.
            countDetailBefore = getRowCount(stmt, destinationDetailTable);

            // Get data from the source table as a ResultSet.
            // The Sales.SalesOrderHeader and Sales.SalesOrderDetail
            // tables are quite large and could easily cause a timeout
            // if all data from the tables is added to the destination.
            // To keep the example simple and quick, a parameter is
            // used to select only orders for a particular account
            // as the source for the bulk insert.
            preparedStmt1.setString(1, "10-4020-000034");
            rsHeader = preparedStmt1.executeQuery();

            // Get the Detail data in a separate connection.
            preparedStmt2.setString(1, "10-4020-000034");
            rsDetail = preparedStmt2.executeQuery();

            // Create the SQLServerBulkCopySQLServerBulkCopy object.
            SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
            copyOptions.setBulkCopyTimeout(100);
            bulkCopy.setBulkCopyOptions(copyOptions);
            bulkCopy.setDestinationTableName(destinationHeaderTable);

            // Guarantee that columns are mapped correctly by
            // defining the column mappings for the order.
            bulkCopy.addColumnMapping("SalesOrderID", "SalesOrderID");
            bulkCopy.addColumnMapping("OrderDate", "OrderDate");
            bulkCopy.addColumnMapping("AccountNumber", "AccountNumber");

            // Write rsHeader to the destination.
            bulkCopy.writeToServer(rsHeader);

            // Set up the order details destination.
            bulkCopy.setDestinationTableName(destinationDetailTable);

            // Clear the existing column mappings
            bulkCopy.clearColumnMappings();

            // Add order detail column mappings.
            bulkCopy.addColumnMapping("SalesOrderID", "SalesOrderID");
            bulkCopy.addColumnMapping("SalesOrderDetailID", "SalesOrderDetailID");
            bulkCopy.addColumnMapping("OrderQty", "OrderQty");
            bulkCopy.addColumnMapping("ProductID", "ProductID");
            bulkCopy.addColumnMapping("UnitPrice", "UnitPrice");

            // Write rsDetail to the destination.
            bulkCopy.writeToServer(rsDetail);

            // Perform a final count on the destination
            // tables to see how many rows were added.
            countHeaderAfter = getRowCount(stmt, destinationHeaderTable);
            countDetailAfter = getRowCount(stmt, destinationDetailTable);

            System.out.println((countHeaderAfter - countHeaderBefore) + " rows were added to the Header table.");
            System.out.println((countDetailAfter - countDetailBefore) + " rows were added to the Detail table.");
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static int getRowCount(Statement stmt,
            String tableName) throws SQLException {
        ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);
        rs.next();
        int count = rs.getInt(1);
        rs.close();
        return count;
    }
}

Транзакции и операции массового копирования

Операции массового копирования могут выполняться как изолированные операции или как часть многошаговой транзакции. Этот последний параметр позволяет выполнять несколько операций массового копирования в одной транзакции и выполнять другие операции базы данных (например, вставки, обновления и удаления) при сохранении или откате всей транзакции.

По умолчанию операция массового копирования выполняется как изолированная. Операция массового копирования выполняется не как транзакция и без возможности отката. Если при возникновении ошибки необходимо частично или полностью отменить массовое копирование, можно использовать управляемую SQLServerBulkCopy транзакцию или выполнить операцию массового копирования в существующей транзакции.

Расширенное массовое копирование для хранилища данных Azure

Версия драйвера 8.4.1 добавляет новое свойство соединения (sendTemporalDataTypesAsStringForBulkCopy). Это логическое свойство равно true по умолчанию.

Это свойство соединения, если задано значение false, отправит типы данных DATE, DATETIME, DATIMETIME2, DATETIMEOFFSET, SMALLDATETIME и TIME в качестве соответствующих типов вместо отправки их в виде строки.

Отправка темпоральных типов данных в качестве соответствующих типов позволяет пользователю отправлять данные в эти столбцы для Azure Synapse Analytics, что было невозможно раньше из-за драйвера, преобразующего данные в String. Отправка строковых данных в темпоральные столбцы работает для SQL Server, так как SQL Server будет выполнять неявное преобразование для нас, но это не так же, как и в Azure Synapse Analytics.

Кроме того, даже без установки для этой строки подключения значения false, начиная с версии 8.4.1 типы данных MONEY и SMALLMONEY будут отправляться как типы данных MONEY / SMALLMONEY, а не DECIMAL, что также позволяет массово копировать типы данных в Azure Synapse Analytics.

Ограничения расширенного массового копирования для хранилища данных Azure

В настоящее время существует два ограничения.

1. Если для этого свойства соединения задано значение false, драйвер принимает только строковый литерал по умолчанию для каждого временного типа данных, например:

   DATE: YYYY-MM-DD

   DATETIME: YYYY-MM-DD hh:mm:ss[.nnn]

   DATETIME2: YYYY-MM-DD hh:mm:ss[.nnnnnnn]

   DATETIMEOFFSET: YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+/-}hh:mm]

   SMALLDATETIME:YYYY-MM-DD hh:mm:ss

   TIME: hh:mm:ss[.nnnnnnn]

2. Если это свойство соединения имеет значение false, тип столбца, указанный для массового копирования, должен учитывать диаграмму сопоставления типов данных здесь. Например, ранее пользователи могли указать java.sql.Types.TIMESTAMP для выполнения операций с массовым копированием данных в столбец DATE, но если эта функция включена, они должны указать java.sql.Types.DATE, чтобы выполнить те же действия.

Выполнение операции массового копирования без транзакции

Следующее приложение показывает, что происходит, когда во время операции массового копирования без транзакции возникает ошибка.

В этом примере исходная и целевая таблицы содержат столбец идентификаторов с именем ProductID. Код сначала подготавливает целевую таблицу, удаляя все строки, а затем вставляя одну строку, которую ProductID известно, что она существует в исходной таблице. По умолчанию в целевой таблице для каждой добавляемой строки создается новое значение столбца идентификаторов. В этом примере при открытии соединения задается параметр, заставляющий процесс массовой загрузки использовать значения идентификаторов из исходной таблицы.

Операция массового копирования выполняется со свойством BatchSize, равным 10. Когда операция встречает недопустимую строку, вызывается исключение. В первом примере операция массового копирования выполняется без транзакции. Фиксируются все пакеты, скопированные до появления ошибки. Пакет, содержащий повторяющийся ключ, откатывается, а операция массового копирования прерывается до обработки любых других пакетов.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.microsoft.sqlserver.jdbc.SQLServerBulkCopy;
import com.microsoft.sqlserver.jdbc.SQLServerBulkCopyOptions;

public class BulkCopyNonTransacted {
    public static void main(String[] args) {
        String connectionUrl = "jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=AdventureWorks;user=<user>;password=<password>";
        String destinationTable = "dbo.BulkCopyDemoMatchingColumns";
        int countBefore, countAfter;
        ResultSet rsSourceData;

        try (Connection sourceConnection = DriverManager.getConnection(connectionUrl);
                Statement stmt = sourceConnection.createStatement();
                SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(connectionUrl)) {

            // Empty the destination table.
            stmt.executeUpdate("DELETE FROM " + destinationTable);

            // Add a single row that will result in duplicate key
            // when all rows from source are bulk copied.
            // Note that this technique will only be successful in
            // illustrating the point if a row with ProductID = 446
            // exists in the AdventureWorks Production.Products table.
            // If you have made changes to the data in this table, change
            // the SQL statement in the code to add a ProductID that
            // does exist in your version of the Production.Products
            // table. Choose any ProductID in the middle of the table
            // (not first or last row) to best illustrate the result.
            stmt.executeUpdate("SET IDENTITY_INSERT " + destinationTable + " ON;" + "INSERT INTO " + destinationTable
                    + "([ProductID], [Name] ,[ProductNumber]) VALUES(446, 'Lock Nut 23','LN-3416'); SET IDENTITY_INSERT " + destinationTable
                    + " OFF");

            // Perform an initial count on the destination table.
            countBefore = getRowCount(stmt, destinationTable);

            // Get data from the source table as a ResultSet.
            rsSourceData = stmt.executeQuery("SELECT ProductID, Name, ProductNumber FROM Production.Product");

            // Set up the bulk copy object using the KeepIdentity option and BatchSize = 10.
            SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
            copyOptions.setKeepIdentity(true);
            copyOptions.setBatchSize(10);

            bulkCopy.setBulkCopyOptions(copyOptions);
            bulkCopy.setDestinationTableName(destinationTable);

            // Write from the source to the destination.
            // This should fail with a duplicate key error
            // after some of the batches have been copied.
            try {
                bulkCopy.writeToServer(rsSourceData);
            }
            catch (SQLException e) {
                e.printStackTrace();
            }

            // Perform a final count on the destination
            // table to see how many rows were added.
            countAfter = getRowCount(stmt, destinationTable);
            System.out.println((countAfter - countBefore) + " rows were added.");
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static int getRowCount(Statement stmt,
            String tableName) throws SQLException {
        ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);
        rs.next();
        int count = rs.getInt(1);
        rs.close();
        return count;
    }
}

Выполнение выделенной операции массового копирования в транзакции

По умолчанию операция массового копирования не создает транзакции. Если вы хотите выполнить выделенную операцию массового копирования, создайте новый экземпляр SQLServerBulkCopy со строкой подключения. В этом сценарии каждый пакет операции массового копирования неявно фиксируется базой данных. Можно установить для параметра UseInternalTransaction значение true в SQLServerBulkCopyOptions, чтобы операция массового копирования создавала транзакции, выполняя фиксацию после каждого пакета операции массового копирования.

SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
copyOptions.setKeepIdentity(true);
copyOptions.setBatchSize(10);
copyOptions.setUseInternalTransaction(true);

Использование существующих транзакций

Вы можете передать объект Connection с включенными транзакциями в качестве параметра конструктора SQLServerBulkCopy. В этом случае операция массового копирования выполняется в существующей транзакции, а состояние транзакции не изменяется (то есть она не фиксируется и не прерывается). Это позволяет приложению включить операцию массового копирования в транзакцию с другими операциями базы данных. Если необходимо выполнить откат всей операции массового копирования из-за ошибки или если массовое копирование должно выполняться как часть большего процесса, который может быть отменен, можно выполнить откат в объекте Connection в любой момент после массового копирования.

Следующее приложение похоже на BulkCopyNonTransacted с одним исключением: в этом примере операция массового копирования включена в большую внешнюю транзакцию. Если возникает ошибка нарушения первичного ключа, производится откат всей транзакции и никакие строки не добавляются в целевую таблицу.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.microsoft.sqlserver.jdbc.SQLServerBulkCopy;
import com.microsoft.sqlserver.jdbc.SQLServerBulkCopyOptions;

public class BulkCopyExistingTransactions {
    public static void main(String[] args) {
        String connectionUrl = "jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=AdventureWorks;user=<user>;password=<password>";
        String destinationTable = "dbo.BulkCopyDemoMatchingColumns";
        int countBefore, countAfter;
        ResultSet rsSourceData;
        SQLServerBulkCopyOptions copyOptions;

        try (Connection sourceConnection = DriverManager.getConnection(connectionUrl);
                Connection destinationConnection = DriverManager.getConnection(connectionUrl);
                Statement stmt = sourceConnection.createStatement();
                SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(destinationConnection);) {

            // Empty the destination table.
            stmt.executeUpdate("DELETE FROM " + destinationTable);

            // Add a single row that will result in duplicate key
            // when all rows from source are bulk copied.
            // Note that this technique will only be successful in
            // illustrating the point if a row with ProductID = 446
            // exists in the AdventureWorks Production.Products table.
            // If you have made changes to the data in this table, change
            // the SQL statement in the code to add a ProductID that
            // does exist in your version of the Production.Products
            // table. Choose any ProductID in the middle of the table
            // (not first or last row) to best illustrate the result.
            stmt.executeUpdate("SET IDENTITY_INSERT " + destinationTable + " ON;" + "INSERT INTO " + destinationTable
                    + "([ProductID], [Name] ,[ProductNumber]) VALUES(446, 'Lock Nut 23','LN-3416'); SET IDENTITY_INSERT " + destinationTable
                    + " OFF");

            // Perform an initial count on the destination table.
            countBefore = getRowCount(stmt, destinationTable);

            // Get data from the source table as a ResultSet.
            rsSourceData = stmt.executeQuery("SELECT ProductID, Name, ProductNumber FROM Production.Product");

            // Set up the bulk copy object inside the transaction.
            destinationConnection.setAutoCommit(false);

            copyOptions = new SQLServerBulkCopyOptions();
            copyOptions.setKeepIdentity(true);
            copyOptions.setBatchSize(10);

            bulkCopy.setBulkCopyOptions(copyOptions);
            bulkCopy.setDestinationTableName(destinationTable);

            // Write from the source to the destination.
            // This should fail with a duplicate key error.
            try {
                bulkCopy.writeToServer(rsSourceData);
                destinationConnection.commit();
            }
            catch (SQLException e) {
                e.printStackTrace();
                destinationConnection.rollback();
            }

            // Perform a final count on the destination
            // table to see how many rows were added.
            countAfter = getRowCount(stmt, destinationTable);
            System.out.println((countAfter - countBefore) + " rows were added.");
        }
        catch (Exception e) {
            // Handle any errors that may have occurred.
            e.printStackTrace();
        }
    }

    private static int getRowCount(Statement stmt,
            String tableName) throws SQLException {
        ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);
        rs.next();
        int count = rs.getInt(1);
        rs.close();
        return count;
    }
}

Массовое копирование из CSV-файла

Следующее приложение показывает, как загрузить данные с помощью класса SQLServerBulkCopy. В этом примере CSV-файл используется для копирования данных, экспортированных из таблицы Production.Product в базе данных AdventureWorks, в аналогичную таблицу в той же базе данных.

1. ОткройтеSQL Server Management Studio и подключитесь к SQL Server с базой данных AdventureWorks.

2. Разверните базы данных, щелкните правой кнопкой мыши базу данных AdventureWorks, выберите Задачи и Экспорт данных.

3. Выберите Источник данных, который позволяет подключиться к SQL Server (например, SQL Server Native Client 11.0), проверьте конфигурацию и нажмите Далее

4. Выберите Назначение "Неструктурированный файл" и введите имя файла с путем назначения, например C:\Test\TestBulkCSVExample.csv. Задайте значения Формат — "С разделителями" и Ограничитель текста — "Нет", установите флажок Имена столбцов в первой строке данных и нажмите Далее

5. Установите флажок Написать запрос, указывающий данные для передачи и вновь нажмите Далее. Введите инструкцию SQL SELECT ProductID, Name, ProductNumber FROM Production.Product и нажмите Далее

6. Проверьте конфигурацию: можно оставить разделитель строк как {CR}{LF} и разделитель столбцов в качестве запятой {,}. Выберите Изменить сопоставления… и проверьте тип данных для каждого столбца (например, целое число для ProductID и строка Юникода для других столбцов).

7. Нажмите Готово и запустите экспорт.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.microsoft.sqlserver.jdbc.SQLServerBulkCSVFileRecord;
import com.microsoft.sqlserver.jdbc.SQLServerBulkCopy;

public class BulkCopyCSV {
    public static void main(String[] args) {
        String connectionUrl = "jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=AdventureWorks;user=<user>;password=<password>";
        String destinationTable = "dbo.BulkCopyDemoMatchingColumns";
        int countBefore, countAfter;

        // Get data from the source file by loading it into a class that implements ISQLServerBulkRecord.
        // Here we are using the SQLServerBulkCSVFileRecord implementation to import the example CSV file.
        try (Connection destinationConnection = DriverManager.getConnection(connectionUrl);
                Statement stmt = destinationConnection.createStatement();
                SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(destinationConnection);
                SQLServerBulkCSVFileRecord fileRecord = new SQLServerBulkCSVFileRecord("C:\\Test\\TestBulkCSVExample.csv", true);) {

            // Set the metadata for each column to be copied.
            fileRecord.addColumnMetadata(1, null, java.sql.Types.INTEGER, 0, 0);
            fileRecord.addColumnMetadata(2, null, java.sql.Types.NVARCHAR, 50, 0);
            fileRecord.addColumnMetadata(3, null, java.sql.Types.NVARCHAR, 25, 0);

            // Empty the destination table.
            stmt.executeUpdate("DELETE FROM " + destinationTable);

            // Perform an initial count on the destination table.
            countBefore = getRowCount(stmt, destinationTable);

            // Set up the bulk copy object.
            // Note that the column positions in the source
            // data reader match the column positions in
            // the destination table so there is no need to
            // map columns.
            bulkCopy.setDestinationTableName(destinationTable);

            // Write from the source to the destination.
            bulkCopy.writeToServer(fileRecord);

            // Perform a final count on the destination
            // table to see how many rows were added.
            countAfter = getRowCount(stmt, destinationTable);
            System.out.println((countAfter - countBefore) + " rows were added.");
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static int getRowCount(Statement stmt,
            String tableName) throws SQLException {
        ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);
        rs.next();
        int count = rs.getInt(1);
        rs.close();
        return count;
    }
}

Использование символа регулярного выражения в качестве разделителя

SQLServerBulkCSVFileRecord fileRecord = new SQLServerBulkCSVFileRecord(CSVFilePath, null, "\\|", true);

Массовое копирование с разделителями как данными в CSV-файле

Версия драйвера 8.4.1 добавляет новый API SQLServerBulkCSVFileRecord.setEscapeColumnDelimitersCSV(boolean). Если задано значение "true", будут применяться следующие правила.

• Каждое поле может быть заключено в двойные кавычки.
• Если поля не заключены в двойные кавычки, в полях могут отсутствовать двойные кавычки.
• Поля, содержащие двойные кавычки и разделители, должны быть заключены в двойные кавычки.
• Если поля заключены в двойные кавычки, то двойные кавычки внутри поля должны быть отмечены с помощью еще одной двойной кавычки перед ними.

Использование массового копирования со столбцами Always Encrypted

Начиная с версии Microsoft JDBC Driver 6.0 для SQL Server массовое копирование поддерживается для столбцов, зашифрованных с помощью Always Encrypted.

В зависимости от параметров массового копирования, а также типа шифрования исходной и конечной таблиц драйвер JDBC может осуществлять прозрачное шифрование данных с последующей расшифровкой либо отправлять зашифрованные данные "как есть". Например, при массовом копировании данных из зашифрованного столбца в незашифрованный драйвер прозрачным образом расшифровывает данные перед их отправкой в SQL Server. Аналогичным образом, при массовом копировании данных из незашифрованного столбца (или из файла CSV) в зашифрованный драйвер прозрачным образом зашифровывает данные перед их отправкой в SQL Server. Если зашифрованы оба источника и назначения, то в зависимости от allowEncryptedValueModifications параметра массового копирования драйвер отправит данные как есть или расшифрует данные и зашифрует его еще раз перед отправкой в SQL Server.

Дополнительные сведения см. в описании параметра массового копирования allowEncryptedValueModifications ниже и в статье Использование функции Always Encrypted с драйвером JDBC.

Массовое копирование API для драйвера JDBC

SQLServerBulkCopy

Позволяет эффективно выполнять массовую загрузку таблицы SQL Server с данными из другого источника.

Microsoft SQL Server включает популярную программу командной строки bcp, используемую для перемещения данных из одной таблицы в другую как на одном сервере, так и между серверами. Класс SQLServerBulkCopy позволяет создавать решения на языке Java, которые предоставляют аналогичные возможности. Существуют другие способы загрузки данных в таблицу SQL Server (например, с помощью инструкции INSERT), но SQLServerBulkCopy делает это значительно быстрее.

Класс SQLServerBulkCopy можно использовать для записи данных только в таблицы SQL Server. Однако источником данных может быть не только SQL Server, а любой источник данных, если данные можно считать с помощью экземпляра ResultSet или реализации ISQLServerBulkRecord.

SQLServerBulkCopyOptions

Коллекция параметров, которые управляют поведением методов writeToServer в экземпляре SQLServerBulkCopy.

Методы get и set существуют для следующих параметров.

Методы получения и методы задания:

ISQLServerBulkRecord

Интерфейс ISQLServerBulkRecord можно использовать для создания классов, которые считывают данные из любого источника (например, файла), чтобы позволить экземпляру SQLServerBulkCopy выполнить массовую загрузку данных таблицы SQL Server.

SQLServerBulkCSVFileRecord

Простая реализация интерфейса ISQLServerBulkRecord, которая может использоваться для чтения базовых типов данных Java из файла с разделителями, где каждая строка представляет строку данных.

Примечания по реализации и ограничения

1. Максимальный объем данных в любой строке ограничивается объемом доступной памяти, поскольку данные считываются по одной строке за раз.

2. Потоковая передача больших типов данных, таких как varchar(max), varbinary(max), nvarchar(max), sqlxml и ntext, не поддерживается.

3. Разделитель, указанный для CSV-файла, не должен отображаться в любом месте данных и должен быть правильно экранирован, если это ограниченный символ в регулярных выражениях Java.

4. В реализации CSV-файла двойные кавычки рассматриваются как часть данных. Например, строка hello,"world","hello,world" будет рассматриваться как строка с четырьмя столбцами со значениями hello, "world", "hello и world", если разделитель — запятая.

5. Символы новой строки используются в качестве признака конца строки и не допускаются в данных.

См. также

Общие сведения о JDBC Driver