Utilisation de la copie en bloc avec le pilote JDBC

Télécharger le pilote JDBC

Microsoft SQL Server inclut un utilitaire en ligne de commande connu nommé bcp, pour rapidement copier en bloc des fichiers volumineux dans des tables ou des vues de bases de données SQL Server. La classe SQLServerBulkCopy vous permet d’écrire des solutions de code dans Java qui fournissent des fonctionnalités similaires. Il existe d’autres façons de charger des données dans une table SQL Server (instructions INSERT, par exemple) mais SQLServerBulkCopy offre de meilleures performances.

La classe SQLServerBulkCopy peut être utilisée pour écrire des données uniquement sur les tables SQL Server. Toutefois, les sources de données ne sont pas limitées à SQL Server : toutes sont utilisables, tant que les données sont lisibles avec une implémentation ResultSet, RowSet ou ISQLServerBulkRecord.

À l'aide de la classe SQLServerBulkCopy, vous pouvez :

• Une opération unique de copie en bloc

• Plusieurs opérations de copie en bloc

• Une opération de copie en bloc avec une transaction

Configuration de l'exemple de copie en bloc

La classe SQLServerBulkCopy peut être utilisée pour écrire des données uniquement sur les tables SQL Server. Les exemples de code présentés dans cet article utilisent l'exemple de base de données AdventureWorks de SQL Server. Pour éviter de modifier les tables existantes dans les exemples de code, écrivez les données dans des tables que vous aurez préalablement créées.

Les tables BulkCopyDemoMatchingColumns et BulkCopyDemoDifferentColumns sont toutes deux basées sur la table Production.Products AdventureWorks. Dans les exemples de code qui utilisent ces tables, les données sont ajoutées à partir de la table Production.Products à l’un de ces exemples de tables. La table BulkCopyDemoMatchingColumns est utilisée quand l’exemple illustre comment mapper des colonnes de données sources à la table de destination. BulkCopyDemoDifferentColumns est utilisée pour la plupart des autres exemples.

Certains exemples de code montrent comment utiliser une classe SQLServerBulkCopy pour écrire dans plusieurs tables. Pour ces exemples, les tables BulkCopyDemoOrderHeader et BulkCopyDemoOrderDetail sont utilisées comme tables de destination. Ces tables sont basées sur les tables Sales.SalesOrderHeader et Sales.SalesOrderDetail dans AdventureWorks.

Configuration des tables

Pour créer les tables nécessaires à la bonne exécution des exemples de code, vous devez exécuter les instructions Transact-SQL suivantes dans une base de données 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]  
  

Opérations uniques de copie en bloc

L'approche la plus simple pour effectuer une opération de copie en bloc SQL Server est d'effectuer une opération unique sur une base de données. Par défaut, une opération de copie en bloc est effectuée comme une opération isolée : l'opération de copie se produit de façon non transactionnelle, sans possibilité de restauration.

Les étapes générales pour effectuer une opération de copie en bloc sont les suivantes :

1. Connectez-vous au serveur source et obtenez les données à copier. Les données peuvent également provenir d’autres sources, si elles peuvent être récupérées à partir d’un objet ResultSet ou d’une implémentation ISQLServerBulkRecord.

2. Connectez-vous au serveur de destination (à moins que vous souhaitiez que SQLServerBulkCopy établisse la connexion pour vous).

3. Créez un objet SQLServerBulkCopy, en définissant les propriétés nécessaires via setBulkCopyOptions.

4. Appelez la méthode setDestinationTableName pour indiquer la table cible pour l’opération d’insertion en bloc.

5. Appelez une des méthodes writeToServer.

6. À titre facultatif, mettez à jour les propriétés via setBulkCopyOptions et rappelez writeToServer le cas échéant.

7. Appelez close ou insérez les opérations de copie en bloc dans une instruction try-with-resources.

Exemple

L'application suivante montre comment charger des données à l'aide de la classe SQLServerBulkCopy. Dans cet exemple, ResultSet est utilisé pour copier des données de la table Production.Product dans la base de données AdventureWorks SQL Server vers une table semblable dans la même base de données.

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

Exécution d'une opération de copie en bloc à l'aide de Transact-SQL

L’exemple suivant illustre comment utiliser la méthode executeUpdate pour exécuter l’instruction 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' )");
}

Plusieurs opérations de copie en bloc

Vous pouvez effectuer plusieurs opérations de copie en bloc à l’aide d’une seule instance d’une classe SQLServerBulkCopy. Si les paramètres des opérations changent entre les copies (par exemple le nom de la table de destination), vous devez les mettre à jour avant d’appeler l’une des méthodes writeToServer, comme illustré dans l’exemple suivant. Sans modification explicite, toutes les valeurs de propriété restent identiques à celles utilisées lors de la précédente opération de copie pour une instance donnée.

Si vous effectuez plusieurs opérations de copie en bloc à l’aide du même objet SQLServerBulkCopy, aucune restriction ne s’applique selon que les informations sources ou cibles sont égales ou différentes dans chaque opération. Toutefois, vous devez vous assurer que les informations d'association de colonnes sont définies correctement chaque fois que vous écrivez sur le serveur.

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

Transaction et opérations de copie en bloc

Les opérations de copie en bloc peuvent être effectuées comme des opérations isolées ou dans le cadre d'une transaction à plusieurs étapes. Cette dernière option permet d'effectuer plusieurs opérations de copie en bloc dans la même transaction ainsi que d'autres opérations de base de données (telles que des insertions, mises à jour et suppressions), tout en étant en mesure de valider ou restaurer toute la transaction.

Par défaut, une opération de copie en bloc est effectuée comme une opération isolée. L'opération de copie en bloc se produit de façon non transactionnelle, sans possibilité de restauration. Si vous devez restaurer tout ou partie de la copie en bloc en cas d’erreur, vous pouvez utiliser une transaction managée par SQLServerBulkCopy ou effectuer l’opération de copie en bloc dans une transaction existante.

Copie en bloc étendue pour Azure Data Warehouse

La version du pilote v8.4.1 ajoute une nouvelle propriété de connexion, sendTemporalDataTypesAsStringForBulkCopy. Par défaut, cette propriété booléenne est true par défaut.

Cette propriété de connexion, lorsqu’elle est définie sur false envoie les types de données DATE, DATETIME, DATIMETIME2, DATETIMEOFFSET, SMALLDATETIME et TIME en tant que types respectifs au lieu de les envoyer en tant que Chaîne.

L’envoi des types de données temporels en tant que types respectifs permet à l’utilisateur d’envoyer des données dans ces colonnes pour Azure Synapse Analytics, ce qui n’était pas possible auparavant en raison du pilote convertissant les données en Chaîne. L’envoi de données de chaîne dans des colonnes temporelles fonctionne pour SQL Server, car SQL Server effectuerait une conversion implicite pour nous, mais ce n’est pas pareil avec Azure Synapse Analytics.

En outre, même si vous ne définissez pas cette chaîne de connexion sur « false », à partir de la v8.4.1 et les versions ultérieures, les types de données MONEY et SMALLMONEY seront envoyés en tant que type de données MONEY / SMALLMONEY au lieu de DECIMAL, ce qui permet également la copie en bloc de ces types de données dans Azure Synapse Analytics.

Limitations des copies en bloc étendues pour Azure Data Warehouse

Il existe actuellement deux limitations :

1. Avec cette propriété de connexion définie sur false, le pilote n’accepte que le format de littéral de chaîne par défaut de chaque type de données temporel, par exemple :

   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. Lorsque cette propriété de connexion est définie sur false, le type de colonne spécifié pour la copie en bloc doit respecter le graphique de mappage des types de données à partir d’ici. Par exemple, les utilisateurs précédents pouvaient spécifier java.sql.Types.TIMESTAMP pour copier en bloc des données dans une colonne DATE, mais avec cette fonctionnalité activée, ils doivent spécifier java.sql.Types.DATE pour effectuer la même opération.

Exécution d'une opération de copie en bloc non transactionnelle

L'application suivante montre ce qui se passe quand une opération de copie en bloc non transactionnelle rencontre une erreur au cours de l'opération.

Dans l’exemple, la table source et la table de destination incluent chacune une colonne d’identité nommée ProductID. Le code commence par préparer la table de destination en supprimant toutes les lignes, puis en insérant une ligne dont la colonne ProductID existe dans la table source. Par défaut, une nouvelle valeur pour la colonne d'identité est générée dans la table de destination pour chaque ligne ajoutée. Dans cet exemple, quand la connexion est ouverte, une option est définie qui oblige le processus de chargement en bloc à utiliser à la place les valeurs d'identité de la table source.

L’opération de copie en bloc est exécutée avec la propriété BatchSize définie avec la valeur 10. Quand l'opération rencontre la ligne non valide, une exception est levée. Dans ce premier exemple, l'opération de copie en bloc est non transactionnel. Tous les lots copiés avant l'erreur sont validés. Le lot contenant la clé dupliquée est restauré et l'opération de copie en bloc est interrompue avant le traitement des autres lots.

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

Exécution d’une opération de copie en bloc dédiée dans une transaction

Par défaut, une opération de copie en bloc ne crée pas de transactions elle-même. Si vous voulez effectuer une opération de copie en bloc dédiée, créez une instance de SQLServerBulkCopy avec une chaîne de connexion. Dans ce scénario, chaque lot de l’opération de copie en bloc est implicitement validé par la base de données. Vous pouvez définir l’option UseInternalTransaction sur true dans SQLServerBulkCopyOptions pour que l’opération de copie en bloc crée des transactions, en effectuant une validation après chaque lot de l’opération de copie en bloc.

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

Utilisation de transactions existantes

Vous pouvez passer un objet Connection dont les transactions sont activées en tant que paramètre dans un constructeur SQLServerBulkCopy. Dans ce cas, l'opération de copie en bloc est effectuée dans une transaction existante et aucune modification n'est apportée à l'état de la transaction (autrement dit, elle n'est ni validée ni abandonnée). Cela permet à une application d'inclure l'opération de copie en bloc dans une transaction avec d'autres opérations de base de données. Si vous devez restaurer toute l'opération de copie en bloc en raison d'une erreur ou si la copie en bloc doit s'exécuter dans le cadre d'un processus plus vaste pouvant être restauré, vous pouvez effectuer la restauration de l'objet Connection à tout moment après l'opération de copie en bloc.

L'application suivante est similaire à BulkCopyNonTransacted, à une exception près : dans cet exemple, l'opération de copie en bloc est incluse dans une transaction externe plus vaste. Quand l'erreur de violation de clé primaire se produit, toute la transaction est restaurée et aucune ligne n'est ajoutée à la table de destination.

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

Copie en bloc à partir d'un fichier CSV

L'application suivante montre comment charger des données à l'aide de la classe SQLServerBulkCopy. Dans cet exemple, un fichier CSV est utilisé pour copier des données exportées de la table Production.Product de la base de données SQL Server AdventureWorks dans une table semblable de la base de données.

1. Ouvrez SQL Server Management Studio, puis connectez-vous à SQL Server avec la base de données AdventureWorks.

2. Développez les bases de données, cliquez avec le bouton droit sur la base de données AdventureWorks, sélectionnez Tâches et Exporter les données…

3. Sélectionnez la Source de données qui permet de vous connecter à SQL Server (par exemple, SQL Server Native Client 11.0), vérifiez la configuration, puis cliquez sur Suivant

4. Sélectionnez la Destination de fichier plat et entrez un Nom de fichier avec une destination telle que C:\Test\TestBulkCSVExample.csv. Vérifiez que le Format est délimité, que l’Identificateur de texte a la valeur aucun, activez Noms de colonne dans la première ligne de données, puis sélectionnez Suivant.

5. Sélectionnez Écrire une requête pour spécifier les données à transférer, puis Suivant. Entrez votre Instruction SQLSELECT ProductID, Name, ProductNumber FROM Production.Product et cliquez sur Suivant

6. Vérifiez la configuration : vous pouvez laisser la valeur {CR}{LF} comme séparateur de lignes et la virgule {,} comme séparateur de colonnes. Sélectionnez Modifier les mappages... et vérifiez que le Type de données est correct pour chaque colonne (par exemple, un entier pour ProductID et une chaîne Unicode pour les autres).

7. Sélectionnez Terminer et exécutez l’exportation.

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

Utilisation d’un caractère regex comme délimiteur

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

Copie en bloc avec des délimiteurs comme données dans un fichier CSV

La version du pilote 8.4.1 ajoute un nouvel API SQLServerBulkCSVFileRecord.setEscapeColumnDelimitersCSV(boolean). Quand la valeur est définie sur true, les règles suivantes s’appliquent :

• Chaque champ peut ou ne peut pas être placé entre guillemets doubles.
• Si les champs ne sont pas entourés de guillemets doubles, les guillemets doubles peuvent ne pas apparaître à l’intérieur des champs.
• Les champs contenant des guillemets doubles et les délimiteurs doivent être placés entre guillemets doubles.
• Si vous utilisez des guillemets doubles pour encadrer des champs, un guillemet double s’affichant à l’intérieur d’un champ doit être placé dans une séquence d’échappement en le faisant précéder d’un autre guillemet.

Copie en bloc avec des colonnes Always Encrypted

À partir de Microsoft JDBC Driver 6.0 pour SQL Server, la copie en bloc est prise en charge avec les colonnes Always Encrypted.

Selon les options de copie en bloc et le type de chiffrement des tables source et de destination, le pilote JDBC peut de manière transparente déchiffrer les données, puis les chiffrer, ou il peut envoyer les données chiffrées telles quelles. Par exemple, quand il effectue une copie en bloc de données à partir d’une colonne chiffrée vers une colonne non chiffrée, le pilote déchiffre les données de manière transparente avant de les envoyer à SQL Server. De même, quand il effectue une copie en bloc de données à partir d’une colonne non chiffrée (ou d’un fichier CSV) vers une colonne chiffrée, le pilote chiffre les données de manière transparente avant de les envoyer à SQL Server. Si la source et la destination sont toutes deux chiffrées, selon l’option de copie en bloc allowEncryptedValueModifications, le pilote envoie les données telles quelles ou les déchiffre, puis les rechiffre avant de les envoyer à SQL Server.

Pour plus d’informations, consultez l’option de copie en bloc allowEncryptedValueModifications ci-dessous et Utilisation d’Always Encrypted avec JDBC Driver.

API de copie en bloc pour le pilote JDBC

SQLServerBulkCopy

Vous permet de charger efficacement en bloc une table SQL Server avec des données d'une autre source.

Microsoft SQL Server inclut un utilitaire de ligne de commande connu nommé bcp pour transférer les données d'une table vers une autre, sur un même serveur ou entre plusieurs serveurs. La classe SQLServerBulkCopy vous permet d'écrire des solutions de code en Java, qui fournissent des fonctionnalités similaires. Il existe d’autres façons de charger des données dans une table SQL Server (instructions INSERT, par exemple) mais SQLServerBulkCopy offre de bien meilleures performances.

La classe SQLServerBulkCopy peut être utilisée pour écrire des données uniquement sur les tables SQL Server. Toutefois, les sources de données ne sont pas limitées à SQL Server : toutes sont utilisables, tant que les données sont lisibles avec une instance ResultSet ou une implémentation ISQLServerBulkRecord.

SQLServerBulkCopyOptions

Collection de paramètres qui contrôlent le comportement des méthodes writeToServer dans une instance de SQLServerBulkCopy.

Des accesseurs Get et Set existent pour les options suivantes :

Getters et setters :

ISQLServerBulkRecord

L'interface ISQLServerBulkRecord peut être utilisée pour créer des classes qui lisent les données de n'importe quelle source (par exemple un fichier) et autorisent une instance SQLServerBulkCopy à charger en bloc une table SQL Server avec ces données.

SQLServerBulkCSVFileRecord

Implémentation simple de l'interface ISQLServerBulkRecord qui peut être utilisée pour lire les types de données Java de base à partir d'un fichier délimité, où chaque ligne représente une ligne de données.

Notes et limitations de l'implémentation :

1. la quantité maximale de données autorisées dans une ligne donnée est limitée par la mémoire disponible, car les données sont lues une ligne à la fois.

2. La diffusion en continu de types de données volumineux tels que varchar(max), varbinary(max), nvarchar(max), sqlxml et ntext n’est pas prise en charge.

3. Le séparateur spécifié pour le fichier CSV ne doit apparaître nulle part dans les données ; il doit être placé dans une séquence d'échappement adaptée s'il s'agit d'un caractère restreint dans les expressions régulières Java.

4. Dans l'implémentation de fichier CSV, les guillemets doubles sont traités comme faisant partie des données. Par exemple, la ligne hello,"world","hello,world" est traitée comme ayant quatre colonnes avec les valeurs hello, "world", "hello et world" si le séparateur est une virgule.

5. Les caractères de nouvelle ligne sont utilisés comme indicateurs de fin de ligne ; ils ne sont autorisés nulle part dans les données.

Voir aussi

Présentation du pilote JDBC