How to enable Bulk Copy API for batch insert operations

Using Bulk Copy API for batch insert operations is supported starting from JDBC driver preview release 6.5.4. This feature allows the users to utilize Bulk Copy API underneath when executing batch insert operations against Azure Data Warehouses, and improves the performance significantly. This page shows various use cases with batch insert using Bulk Copy API, as well as its limitations.

Creating a Geometry / Geography object

There are two main ways to create a Geometry / Geography object - either convert from a Well-Known Text (WKT) or a Well-Known Binary (WKB).

1. Creating from WKT:

String geoWKT = "LINESTRING(1 0, 0 1, -1 0)";
Geometry geomWKT = Geometry.STGeomFromText(geoWKT, 0);
Geography geogWKT = Geography.STGeomFromText(geoWKT, 4326);

This will create a LINESTRING Geometry object with Spatial Reference System Identifier (SRID) 0, and a Geography object with SRID 4326.

2. Creating from WKB:

byte[] geomWKB = Hex.decodeHex("00000000010403000000000000000000F03F00000000000000000000000000000000000000000000F03F000000000000F0BF000000000000000001000000010000000001000000FFFFFFFF0000000002".toCharArray());
byte[] geogWKB = Hex.decodeHex("E61000000104030000000000000000000000000000000000F03F000000000000F03F00000000000000000000000000000000000000000000F0BF01000000010000000001000000FFFFFFFF0000000002".toCharArray());

Geometry geomWKT = Geometry.deserialize(geomWKB);
Geography geogWKT = Geography.deserialize(geogWKB);

This will create a Geometry and Geography object that is equivalent to the ones created from the WKT previously.

Working with a Geometry / Geography object

Assuming you have a table on SQL Server like this:

CREATE TABLE sampleTable (c1 geometry)  

A sample script to insert a Geometry value:

String geoWKT = "LINESTRING(1 0, 0 1, -1 0)";
Geometry geomWKT = Geometry.STGeomFromText(geoWKT, 0);
PreparedStatement pstmt = con.prepareStatement("insert into sampleTable values (?)");   
pstmt.setGeometry(1, geomWKT);  
pstmt.execute();

The same can be done for Geography counterpart, using a Geography column and setGeography method.

To read Geometry / Geography column:

SQLServerResultSet rs = (SQLServerResultSet) stmt.executeQuery("select * from sampleTable ");   
rs.next();          
rs.getGeometry(1);

The same can be done for Geography counterpart, using a Geography column and getGeography method.

Limitations of Spatial Datatypes

1- The spatial sub-datatypes CircularString, CompoundCurve, CurvePolygon, and FullGlobe are only supported starting from SQL Server 2012 and above.

2- Always Encrypted cannot be used with spatial datatypes.

3- Stored procedures, TVP and BulkCopy are currently not supported with spatial datatypes.