| title | Spatial Data Types Sample for MSSQL JDBC Driver | Microsoft Docs |
|---|---|
| ms.custom | |
| ms.date | 07/30/2018 |
| ms.prod | sql |
| ms.prod_service | connectivity |
| ms.reviewer | |
| ms.technology | connectivity |
| ms.topic | conceptual |
| author | MightyPen |
| ms.author | genemi |
| manager | craigg |
[!INCLUDEDriver_JDBC_Download]
This [!INCLUDEjdbcNoVersion] sample application demonstrates how to create, insert and retrieve Spatial Data types (Geometry and Geography).
The code file for this sample is named SpatialDataTypes.java, and it can be found in the following location:
\<installation directory>\sqljdbc_<version>\<language>\samples\datatypes To run this sample application, you must set the classpath to include the mssql-jdbc jar file. For more information about how to set the classpath, see Using the JDBC Driver.
Note
The [!INCLUDEjdbcNoVersion] provides mssql-jdbc class library files to be used depending on your preferred Java Runtime Environment (JRE) settings. For more information about which JAR file to choose, see System Requirements for the JDBC Driver.
In the following example, the sample code creates a table called SpatialDataTypesTable_JDBC_Sample that contains 'Geometry' and 'Geography' columns.
The sample first creates 'Geometry' and 'Geography' objects from a Well-Known-Text (WKT) representing a POINT. It uses a SQLServerPreparedStatement with a parameterized query to map the data to each column accordingly.
Finally, the sample inserts the data into the table, and retrieves it. The data is displayed in the form of WKT.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import com.microsoft.sqlserver.jdbc.Geography;
import com.microsoft.sqlserver.jdbc.Geometry;
import com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement;
import com.microsoft.sqlserver.jdbc.SQLServerResultSet;
public class SpatialDataTypes {
private static String tableName = "SpatialDataTypesTable_JDBC_Sample";
public static void main(String[] args) {
// Create a variable for the connection string.
String connectionUrl = "jdbc:sqlserver://<server>:<port>;databaseName=<database>;user=<user>;password=<password>";
// Establish the connection.
try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement();) {
dropAndCreateTable(stmt);
// TODO: Implement Sample code
String geoWKT = "POINT(3 40 5 6)";
Geometry geomWKT = Geometry.STGeomFromText(geoWKT, 0);
Geography geogWKT = Geography.STGeomFromText(geoWKT, 4326);
try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con
.prepareStatement("insert into " + tableName + " values (?, ?)");) {
pstmt.setGeometry(1, geomWKT);
pstmt.setGeography(2, geogWKT);
pstmt.execute();
SQLServerResultSet rs = (SQLServerResultSet) stmt.executeQuery("select * from " + tableName);
rs.next();
System.out.println("Geometry data: " + rs.getGeometry(1));
System.out.println("Geography data: " + rs.getGeography(2));
}
} catch (Exception e) {
e.printStackTrace();
}
}
private static void dropAndCreateTable(Statement stmt) throws SQLException {
stmt.executeUpdate("if object_id('" + tableName + "','U') is not null" + " drop table " + tableName);
stmt.executeUpdate("Create table " + tableName + " (c1 geometry, c2 geography)");
}
}