JarCasting
JSqlServerBulkInsert
JSqlServerBulkInsert is a library to simplify Bulk Inserts to the SQL Server. It wraps the SQLServerBulkCopy behind a nice API.
Installing
You can obtain JSqlServerBulkInsert from Maven by adding the following:
<dependency>
<groupId>de.bytefish</groupId>
<artifactId>jsqlserverbulkinsert</artifactId>
<version>4.0.1</version>
</dependency>
Supported Types
Please read up the Microsoft Documentation for understanding the mapping between SQL Server Types and JDBC Data Types:
The following JDBC Types are supported by the library:
- Numeric Types
- TINYINT
- SMALLINT
- INTEGER
- BIGINT
- NUMERIC
- REAL
- DOUBLE
- Date/Time Types
- DATE
- TIMESTAMP
- TIMESTAMP with Timezone
- Boolean Type
- BIT
- Character / Text Types
- CHAR
- NCHAR
- CLOB
- VARCHAR
- NVARCHAR
- LONGVARCHAR
- NLONGVARCHAR
- Binary Data Types
- VARBINARY
Notes on the Table Mapping
The SQLServerBulkCopy implementation of Microsoft requires all columns of the destination table to be defined, even if the columns contain auto-generated values.
So imagine you have a table with an auto-incrementing primary key:
CREATE TABLE [dbo].[UnitTest](
PK_ID INT IDENTITY(1,1) PRIMARY KEY,
IntegerValue INT
)
And although our class doesn't contain the Auto-Incrementing Primary Key:
public class MySampleEntity {
private final int val;
public MySampleEntity(int val) {
this.val = val;
}
public Integer getVal() {
return val;
}
}
We still need to map it in the AbstractMapping like this:
public class MySampleEntityMapping extends AbstractMapping<MySampleEntity> {
public MySampleEntityMapping() {
super("dbo", "UnitTest");
mapInteger("PK_ID", x -> null);
mapInteger("IntegerValue", x -> x.getVal());
}
}
Or like this to explicitly define the Column as auto-incrementing:
private class MySampleEntityMapping extends AbstractMapping<MySampleEntity> {
public MySampleEntityMapping() {
super("dbo", "UnitTest");
mapInteger("PK_ID", true);
mapInteger("IntegerValue", x -> x.getVal());
}
}
Order of Columns
The SqlServerBulkCopy implementation of the Microsoft JDBC driver requires, that the destination schema and mapping have same column order. This is done automatically by querying the metadata of the table and sorting your mappings, before inserting the data.
If this cannot be done automatically, because the JDBC driver does not return the metadata, then the mapping and destination schema have to match, and the fields must be mapped in the same order as the destination table.
Getting Started
Imagine 1,000,000 Persons should be inserted into an SQL Server database.
Results
Bulk Inserting 1,000,000entities to a SQL Server 2016 database took 5 Seconds:
[Bulk Insert 1000000 Entities] PT4.559S
Domain Model
The domain model could be the Person class with a First Name, Last Name and a birth date.
package de.bytefish.jsqlserverbulkinsert.test.model;
import java.time.LocalDate;
public class Person {
private String firstName;
private String lastName;
private LocalDate birthDate;
public Person() {
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public LocalDate getBirthDate() {
return birthDate;
}
public void setBirthDate(LocalDate birthDate) {
this.birthDate = birthDate;
}
}
Mapping
To bulk insert the Person data to a SQL Server database it is important to know how to map between the Java Object and the Database Columns:
package de.bytefish.jsqlserverbulkinsert.test.integration;
import de.bytefish.jsqlserverbulkinsert.mapping.AbstractMapping;
import de.bytefish.jsqlserverbulkinsert.test.model.Person;
public class PersonMapping extends AbstractMapping<Person> {
public PersonMapping() {
super("dbo", "UnitTest");
mapNvarchar("FirstName", Person::getFirstName);
mapNvarchar("LastName", Person::getLastName);
mapDate("BirthDate", Person::getBirthDate);
}
}
Construct and use the SqlServerBulkInsert
The AbstractMapping is used to instantiate a SqlServerBulkInsert, which provides a saveAll method to store a given stream of data.
// Instantiate the SqlServerBulkInsert class:
SqlServerBulkInsert<Person> bulkInsert = new SqlServerBulkInsert<>(mapping);
// Now save all entities of a given stream:
bulkInsert.saveAll(connection, persons.stream());
And the full Integration Test:
package de.bytefish.jsqlserverbulkinsert.test.integration;
import de.bytefish.jsqlserverbulkinsert.SqlServerBulkInsert;
import de.bytefish.jsqlserverbulkinsert.test.base.TransactionalTestBase;
import de.bytefish.jsqlserverbulkinsert.test.model.Person;
import de.bytefish.jsqlserverbulkinsert.test.utils.MeasurementUtils;
import org.junit.Assert;
import org.junit.Test;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
public class IntegrationTest extends TransactionalTestBase {
@Override
protected void onSetUpInTransaction() throws Exception {
createTable();
}
@Test
public void bulkInsertPersonDataTest() throws SQLException {
// The Number of Entities to insert:
int numEntities = 1000000;
// Create a large list of Persons:
List<Person> persons = getPersonList(numEntities);
// Create the Mapping:
PersonMapping mapping = new PersonMapping();
// Create the Bulk Inserter:
SqlServerBulkInsert<Person> bulkInsert = new SqlServerBulkInsert<>(mapping);
// Measure the Bulk Insert time:
MeasurementUtils.MeasureElapsedTime("Bulk Insert 1000000 Entities", () -> {
// Now save all entities of a given stream:
bulkInsert.saveAll(connection, persons.stream());
});
// And assert all have been written to the database:
Assert.assertEquals(numEntities, getRowCount());
}
private List<Person> getPersonList(int numPersons) {
List<Person> persons = new ArrayList<>();
for (int pos = 0; pos < numPersons; pos++) {
Person p = new Person();
p.setFirstName("Philipp");
p.setLastName("Wagner");
p.setBirthDate(LocalDate.of(1986, 5, 12));
persons.add(p);
}
return persons;
}
private boolean createTable() throws SQLException {
String sqlStatement = "CREATE TABLE [dbo].[UnitTest]\n" +
" (\n" +
" FirstName NVARCHAR(255),\n" +
" LastName NVARCHAR(255),\n" +
" BirthDate DATE\n" +
" );";
Statement statement = connection.createStatement();
return statement.execute(sqlStatement);
}
private int getRowCount() throws SQLException {
Statement s = connection.createStatement();
ResultSet r = s.executeQuery("SELECT COUNT(*) AS total FROM [dbo].[UnitTest];");
r.next();
int count = r.getInt("total");
r.close();
return count;
}
}