Usar cópia em massa com o driver JDBC

Baixar driver JDBC

O Microsoft SQL Server inclui uma utilidade popular de linha de comandos, nomeada bcp para copiar rapidamente em massa ficheiros grandes em tabelas ou vistas em bases de dados SQL Server. A SQLServerBulkCopy classe permite-te escrever soluções de código em Java que oferecem funcionalidades semelhantes. Há outras maneiras de carregar dados em uma tabela do SQL Server (instruções INSERT, por exemplo), mas SQLServerBulkCopy oferece uma vantagem de desempenho significativa sobre elas.

A SQLServerBulkCopy classe pode ser usada para gravar dados somente em tabelas do SQL Server. Mas a fonte de dados não se limita ao SQL Server; Qualquer fonte de dados pode ser utilizada, desde que os dados possam ser lidos com um ResultSet, RowSet, ou ISQLServerBulkRecord implementação.

Usando a SQLServerBulkCopy classe, você pode executar:

• Uma única operação de cópia em massa

• Várias operações de cópia em massa

• Uma operação de cópia em massa com uma transação

Exemplo de configuração de cópia em massa

A SQLServerBulkCopy classe pode ser usada para gravar dados somente em tabelas do SQL Server. Os exemplos de código apresentados neste artigo utilizam a base de dados de exemplos do SQL Server, AdventureWorks. Para evitar alterar as tabelas existentes nos exemplos de código, escreva os dados nas tabelas que cria primeiro.

As tabelas BulkCopyDemoMatchingColumns e BulkCopyDemoDifferentColumns são ambas baseadas na tabela AdventureWorks Production.Products. Em exemplos de código que utilizam estas tabelas, os dados são adicionados da Production.Products tabela a uma destas tabelas de exemplo. A BulkCopyDemoDifferentColumns tabela é usada quando o exemplo ilustra como mapear colunas dos dados de origem para a tabela de destino; BulkCopyDemoMatchingColumns é usada para a maioria das outras amostras.

Alguns dos exemplos de código demonstram como usar uma SQLServerBulkCopy classe para gravar em várias tabelas. Para estas amostras, as BulkCopyDemoOrderHeader tabelas e BulkCopyDemoOrderDetail são usadas como tabelas de destino. Estas tabelas baseiam-se nas tabelas Sales.SalesOrderHeader e nas Sales.SalesOrderDetail do AdventureWorks.

Configuração de tabela

Para criar as tabelas necessárias para que os exemplos de código sejam executados corretamente, você deve executar as seguintes instruções Transact-SQL em um banco de dados do 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]  
  

Operações de cópia em massa única

A abordagem mais simples para executar uma operação de cópia em massa do SQL Server é executar uma única operação em um banco de dados. Por padrão, uma operação de cópia em massa é executada como uma operação isolada: a operação de cópia ocorre de forma não transacionada, sem oportunidade de revertê-la.

Os passos gerais para realizar uma operação de cópia em massa são:

1. Conecte-se ao servidor de origem e obtenha os dados a serem copiados. Os dados também podem vir de outras fontes, desde que possam ser recuperados de um ResultSet objeto ou de uma ISQLServerBulkRecord implementação.

2. Conecte-se ao servidor de destino (a menos que pretenda SQLServerBulkCopy estabelecer uma ligação para si).

3. Crie um objeto SQLServerBulkCopy, definindo quaisquer propriedades necessárias através de setBulkCopyOptions.

4. Chame o setDestinationTableName método para indicar a tabela alvo para a operação de inserção em massa.

5. Ligue para um dos writeToServer métodos.

6. Opcionalmente, atualize propriedades via setBulkCopyOptions e chame writeToServer novamente conforme necessário.

7. Chame close, ou encapsule as operações de cópia em massa dentro de uma instrução try-with-resources.

Example

A aplicação seguinte demonstra como carregar dados usando a SQLServerBulkCopy classe. Neste exemplo, a ResultSet é usado para copiar dados da tabela Production.Product na base de dados SQL Server AdventureWorks para uma tabela semelhante na mesma base de dados.

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;
    }
}

Realizar uma operação de cópia em massa usando Transact-SQL

O exemplo a seguir ilustra como usar o executeUpdate método para executar a instrução 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' )");
}

Várias operações de cópia em massa

Você pode executar várias operações de cópia em massa usando uma única instância de uma classe SQLServerBulkCopy. Se os parâmetros de operação mudarem entre cópias (por exemplo, o nome da tabela de destino), deve atualizá-los antes de quaisquer chamadas subsequentes a qualquer um dos writeToServer métodos, como demonstrado no exemplo seguinte. A menos que explicitamente alterados, todos os valores de propriedade para uma determinada instância permanecem os mesmos que estavam na operação de cópia em massa anterior.

Se você executar várias operações de cópia em massa usando o mesmo objeto SQLServerBulkCopy, não haverá restrições sobre se as informações de origem ou destino são iguais ou diferentes em cada operação. No entanto, deve garantir que as informações de associação de coluna sejam configuradas corretamente cada vez que escrever no servidor.

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;
    }
}

Transações e operações de cópia em massa

As operações de cópia em massa podem ser executadas como operações isoladas ou como parte de uma transação de várias etapas. Esta última opção permite-lhe realizar mais do que uma operação de cópia em massa dentro da mesma transação, e realizar outras operações na base de dados (como inserções, atualizações e eliminações), mantendo ainda assim a possibilidade de comprometer ou reverter toda a transação.

Por padrão, uma operação de cópia em massa é executada como uma operação isolada. A operação de cópia em massa ocorre de forma não transacionada, sem oportunidade de revertê-la. Se precisar de reverter total ou parcialmente a cópia em massa quando ocorrer um erro, pode usar uma SQLServerBulkCopytransação gerida ou realizar a operação de cópia em massa dentro de uma transação existente.

Cópia em Massa Estendida para Azure Data Warehouse

A versão do driver v8.4.1 adiciona uma nova propriedade de ligação, sendTemporalDataTypesAsStringForBulkCopy. Esta propriedade booleana é true por defeito.

Esta propriedade de ligação, quando definida para false, enviará os tipos de dados DATE,DATETIME, DATIMETIME2, DATETIMEOFFSET, SMALLDATETIME e TIME como os respetivos tipos em vez de os enviar como String.

Enviar os tipos de dados temporais como os seus respetivos tipos permite ao utilizador enviar dados para essas colunas para o Azure Synapse Analytics, o que antes não era possível devido ao driver converter os dados em String. Enviar dados de strings para colunas temporais funciona para o SQL Server porque o SQL Server faria conversão implícita para nós, mas não é o mesmo com o Azure Synapse Analytics.

Além disso, mesmo sem definir esta cadeia de ligação para 'false', a partir da v8.4.1 , os tipos de dados MONEY e SMALLMONEY serão enviados como tipos de dados MONEY / SMALLMONEY em vez de DECIMAL, o que também permite que esses tipos sejam copiados em massa para o Azure Synapse Analytics.

Limitações do Extended Bulk Copy para o Azure Data Warehouse

Atualmente, existem duas limitações:

1. Com esta propriedade de ligação definida para false, o driver só aceitará o formato literal de string predefinido de cada tipo de dado temporal, por exemplo:

   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. Com esta propriedade de ligação definida para false, o tipo de coluna especificado para cópia em massa tem de respeitar a tabela de mapeamento de tipos de dados apresentada em Utilização de tipos de dados básicos. Por exemplo, anteriormente os utilizadores podiam especificar java.sql.Types.TIMESTAMP copiar dados em massa para uma DATE coluna, mas com esta funcionalidade ativada, têm de especificar java.sql.Types.DATE para fazer o mesmo.

Realização de uma operação de cópia em massa não transacionada

A aplicação seguinte mostra o que acontece quando uma operação de cópia em massa não transacionada encontra um erro a meio da operação.

No exemplo, a tabela de origem e a tabela de destino incluem cada uma coluna de Identidade chamada ProductID. O código prepara primeiro a tabela de destino eliminando todas as linhas e depois inserindo uma única linha cuja ProductID existência é conhecida na tabela de origem. Por defeito, um novo valor para a coluna Identidade é gerado na tabela de destino para cada linha adicionada. Neste exemplo, uma opção é definida quando a ligação é aberta que força o processo de carregamento em massa a usar os valores de Identidade da tabela de origem em vez disso.

A operação de cópia em massa é executada com a BatchSize propriedade definida como 10. Quando a operação encontra a linha inválida, uma exceção é gerada. Neste primeiro exemplo, a operação de cópia em massa não é transacionada. Todos os lotes copiados até ao ponto do erro são confirmados; o lote que contém a chave duplicada é anulado e a operação de cópia em massa é interrompida antes de processar quaisquer outros lotes.

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;
    }
}

Realização de uma operação dedicada de cópia em massa numa transação

Por defeito, uma operação de cópia em massa não cria as transações por si só. Quando quiseres realizar uma operação dedicada de cópia em massa, cria uma nova instância de SQLServerBulkCopy com uma cadeia de ligação. Neste cenário, cada lote da operação de cópia em massa é implicitamente validado pela base de dados. Podes definir a opção UseInternalTransaction para true em SQLServerBulkCopyOptions para fazer com que a operação de cópia em massa crie transações, realizando um commit após cada lote.

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

Utilização de transações existentes

Pode passar um Connection objeto que tenha transações ativadas como parâmetro num SQLServerBulkCopy construtor. Nesta situação, a operação de cópia em bloco é realizada numa transação existente e não se faz qualquer alteração ao estado da transação (ou seja, não é confirmada ou abortada). Isso permite que um aplicativo inclua a operação de cópia em massa em uma transação com outras operações de banco de dados. Se precisares de reverter toda a operação de cópia em massa porque ocorrer um erro, ou se a cópia em massa for executada como parte de um processo maior que possa ser revertido, podes realizar a reversão do Connection objeto em qualquer momento após a operação de cópia em massa.

A aplicação seguinte é semelhante a BulkCopyNonTransacted, com uma exceção: neste exemplo, a operação de cópia em massa está incluída numa transação externa maior. Quando ocorre o erro de violação de chave primária, toda a transação é revertida e nenhuma linha é adicionada à tabela de destino.

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;
    }
}

Cópia em massa a partir de um ficheiro CSV

A aplicação seguinte demonstra como carregar dados usando a SQLServerBulkCopy classe. Neste exemplo, um ficheiro CSV é usado para copiar dados exportados da tabela Production.Product na base de dados SQL Server AdventureWorks para uma tabela semelhante na base de dados.

1. Abra o SQL Server Management Studio e ligue-se ao SQL Server com a base de dados AdventureWorks.

2. Expandir as bases de dados, clicar com o botão direito na base de dados AdventureWorks, selecionar Tarefas e Exportar Dados...

3. Para a fonte de dados, selecione a fonte de dados que lhe permite ligar ao seu SQL Server (por exemplo, SQL Server Native Client 11.0), verifique a configuração e depois Próximo

4. Para o Destino, selecione o Destino de Ficheiro Plano e insira um Nome de Ficheiro com um destino como C:\Test\TestBulkCSVExample.csv. Verifica se o Formato está Delimitado, o qualificador de Texto é nenhum, ativa os nomes das Colunas na primeira linha de dados e depois seleciona Seguinte

5. Selecione Escrever uma consulta para especificar os dados a transferir e Próximo. Introduza a sua Instrução SQLSELECT ProductID, Name, ProductNumber FROM Production.Product e Próximo

6. Verifique a configuração: pode deixar o delimitador de linhas como {CR}{LF} e o delimitador de colunas como vírgula {,}. Selecione Editar Mapeamentos... e verifique se o Tipo de dado está correto para cada coluna (por exemplo, inteiro para ProductID e cadeia Unicode para as outras).

7. Avança para Terminar e executa a exportação.

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;
    }
}

Usar um carácter regex como delimitador

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

Cópia em massa com delimitadores como dados num ficheiro CSV

A versão 8.4.1 do driver adiciona uma nova API SQLServerBulkCSVFileRecord.setEscapeColumnDelimitersCSV(boolean). Quando definido como verdadeiro, aplicam-se as seguintes regras:

• Cada campo pode ou não estar incluído entre aspas duplas.
• Se os campos não estiverem incluídos com aspas duplas, então as aspas duplas podem não aparecer dentro dos campos.
• Os campos contendo aspas duplas e delimitadores devem estar incluídos entre aspas duplas.
• Se forem utilizadas aspas duplas para demarcar campos, então uma aspas dupla que aparece dentro de um campo deve ser precedida por outra aspas dupla para ser escapada.

Cópia em massa com colunas "Always Encrypted"

A partir do Microsoft JDBC Driver 6.0 para SQL Server, a cópia em massa é suportada com colunas Always Encrypted.

Dependendo das opções de cópia em massa e do tipo de encriptação das tabelas de origem e destino, o driver JDBC pode desencriptar e depois encriptar os dados de forma transparente, ou pode enviar os dados encriptados tal como estão. Por exemplo, ao copiar em massa dados de uma coluna encriptada para uma coluna não encriptada, o driver desencripta os dados de forma transparente antes de enviar para o SQL Server. De forma semelhante, ao copiar em massa dados de uma coluna não encriptada (ou de um ficheiro CSV) para uma coluna encriptada, o driver encripta os dados de forma transparente antes de os enviar para o SQL Server. Se tanto a origem como o destino estiverem encriptados, então, dependendo da allowEncryptedValueModifications opção de cópia em massa, o driver enviaria os dados tal como estão ou desencriptaria os dados e encriptá-los novamente antes de enviarem para o SQL Server.

Para mais informações, consulte a allowEncryptedValueModifications opção de cópia em massa abaixo e Usar Sempre Encriptado com o Driver JDBC.

API de cópia em massa para driver JDBC

SQLServerBulkCopy

Permite-te carregar em massa uma tabela SQL Server de forma eficiente com dados de outra fonte.

O Microsoft SQL Server inclui uma popular utilidade de prompt de comandos chamada bcp para mover dados de uma tabela para outra, seja num único servidor ou entre servidores. A SQLServerBulkCopy classe permite-te escrever soluções de código em Java que oferecem funcionalidades semelhantes. Existem outras formas de carregar dados numa tabela SQL Server (instruções INSERT, por exemplo), mas SQLServerBulkCopy oferecem uma vantagem significativa de desempenho em relação a elas.

A SQLServerBulkCopy classe pode ser usada para gravar dados somente em tabelas do SQL Server. No entanto, a fonte de dados não se limita ao SQL Server; Qualquer fonte de dados pode ser utilizada, desde que os dados possam ser lidos com uma ResultSet instância ou ISQLServerBulkRecord implementação.

SQLServerBulkCopyOptions

Uma coleção de definições que controlam como os writeToServer métodos se comportam numa instância de SQLServerBulkCopy.

Existem getters e setters para as seguintes opções:

Lançadores e levantadores:

ISQLServerBulkRecord

A ISQLServerBulkRecord interface pode ser usada para criar classes que leem dados de qualquer fonte (como um ficheiro) e permitem que uma SQLServerBulkCopy instância carregue em massa uma tabela SQL Server com esses dados.

SQLServerBulkCSVFileRecord

Uma implementação simples da ISQLServerBulkRecord interface que pode ser usada para ler os tipos de dados Java básicos a partir de um ficheiro delimitado onde cada linha representa uma linha de dados.

Notas de Implementação e Limitações:

1. A quantidade máxima de dados permitida numa dada linha é limitada pela memória disponível porque os dados são lidos linha de cada vez.

2. O streaming de grandes tipos de dados como varchar(max), varbinary(max), nvarchar(max), sqlxml, e ntext não é suportado.

3. O delimitador especificado para o ficheiro CSV não deve aparecer em nenhum lugar dos dados e deve ser devidamente escapado se for um caractere restrito nas expressões regulares Java.

4. Na implementação do ficheiro CSV, as aspas duplas são tratadas como parte dos dados. Por exemplo, a linha hello,"world","hello,world" seria tratada como tendo quatro colunas com os valores hello, "world", "hello e world" se o delimitador for uma vírgula.

5. Caracteres de nova linha são usados como terminadores de linha e não são permitidos em nenhuma parte dos dados.

Consulte também

Visão geral do driver JDBC