Using basic data types

Download JDBC driver

The Microsoft JDBC Driver for SQL Server uses the JDBC basic data types to convert the SQL Server data types to a format understood by the Java programming language, and vice versa. The JDBC driver provides support for the JDBC 4.0 API, which includes the SQLXML data type, and National (Unicode) data types, such as NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB.

Data type mappings

The following table lists the default mappings between the basic SQL Server, JDBC, and Java programming language data types:

SQL Server Types	JDBC Types (java.sql.Types)	Java Language Types
bigint	BIGINT	long
binary	BINARY	byte[]
bit	BIT	boolean
char	CHAR	String
date	DATE	java.sql.Date
datetime3	TIMESTAMP	java.sql.Timestamp
datetime2	TIMESTAMP	java.sql.Timestamp
datetimeoffset2	microsoft.sql.Types.DATETIMEOFFSET	microsoft.sql.DateTimeOffset
decimal	DECIMAL	java.math.BigDecimal
float	DOUBLE	double
image	LONGVARBINARY	byte[]
int	INTEGER	int
money	DECIMAL	java.math.BigDecimal
nchar	CHAR NCHAR (Java SE 6.0)	String
ntext	LONGVARCHAR LONGNVARCHAR (Java SE 6.0)	String
numeric	NUMERIC	java.math.BigDecimal
nvarchar	VARCHAR NVARCHAR (Java SE 6.0)	String
nvarchar(max)	VARCHAR NVARCHAR (Java SE 6.0)	String
real	REAL	float
smalldatetime	TIMESTAMP	java.sql.Timestamp
smallint	SMALLINT	short
smallmoney	DECIMAL	java.math.BigDecimal
text	LONGVARCHAR	String
time	TIME1	java.sql.Time1
timestamp	BINARY	byte[]
tinyint	TINYINT	short
udt	VARBINARY	byte[]
uniqueidentifier	CHAR	String
varbinary	VARBINARY	byte[]
varbinary(max)	VARBINARY	byte[]
varchar	VARCHAR	String
varchar(max)	VARCHAR	String
xml	LONGVARCHAR LONGNVARCHAR (Java SE 6.0)	String SQLXML
sqlvariant	microsoft.sql.Types.SQL_VARIANT	Object
geometry	VARBINARY	byte[]
geography	VARBINARY	byte[]

¹ To use java.sql.Time with the time SQL Server type, you must set the sendTimeAsDatetime connection property to false.

² You can programmatically access values of datetimeoffset with DateTimeOffset Class.

³ java.sql.Timestamp values can no longer be used to compare values from a datetime column starting from SQL Server 2016. This limitation is due to a server-side change that converts datetime to datetime2 differently, resulting in non equal values. The workaround to this issue is to either change datetime columns to datetime2(3), use String instead of java.sql.Timestamp, or change database compatibility level to 120 or lower.

The following sections provide examples of how you can use the JDBC Driver and the basic data types. For a more detailed example of how to use the basic data types in a Java application, see Basic Data Types Sample.

Retrieving data as a string

If you have to retrieve data from a data source that maps to any of the JDBC basic data types for viewing as a string, or if strongly-typed data isn't required, use the getString method of the SQLServerResultSet class. The following example shows this usage:

try(Statement stmt = con.createStatement();) {
    ResultSet rs = stmt.executeQuery("SELECT lname, job_id FROM employee WHERE (lname = 'Brown')");
    rs.next();
    short empJobID = rs.getString("job_id");
}

Retrieving data by data type

If you have to retrieve data from a data source, and you know the type of data that is being retrieved, use one of the get<Type> methods of the SQLServerResultSet class, also known as the getter methods. You can use either a column name or a column index with the get<Type> methods, as in the following example:

try(Statement stmt = con.createStatement();) {
    ResultSet rs = stmt.executeQuery("SELECT lname, job_id FROM employee WHERE (lname = 'Brown')");
    rs.next();
    short empJobID = rs.getShort("job_id");
}

Note

The getUnicodeStream and getBigDecimal with scale methods are deprecated and are not supported by the JDBC driver.

Updating data by data type

If you have to update the value of a field in a data source, use one of the update<Type> methods of the SQLServerResultSet class. In the following example, the updateInt method is used with the updateRow method to update the data in the data source:

try (Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);) {
    ResultSet rs = stmt.executeQuery("SELECT lname, job_id FROM employee WHERE (lname = 'Brown')");
    rs.next();
    int empJobID = rs.getInt(2);
    empJobID++;
    rs.first();
    rs.updateInt(2, empJobID);
    rs.updateRow();
}

Note

The JDBC driver cannot update a SQL Server column with a column name that is more than 127 characters long. If an update to a column whose name is more than 127 characters is attempted, an exception is thrown.

Updating data by parameterized query

If you update data in a data source by using a parameterized query, you can set the data type of the parameters by using one of the set<Type> methods of the SQLServerPreparedStatement class. These methods are also known as setter methods. In the following example, the prepareStatement method is used to precompile the parameterized query and then the setString method is used to set the string value of the parameter before the executeUpdate method is called.

try(PreparedStatement pstmt = con.prepareStatement("UPDATE employee SET fname = ? WHERE (lname = 'Brown')");) {
    String name = "Bob";
    pstmt.setString(1, name);
    int rowCount = pstmt.executeUpdate();
}

For more information about parameterized queries, see Using a SQL statement with parameters.

Passing parameters to a stored procedure

If you have to pass typed parameters into a stored procedure, you can set the parameters by index or name by using one of the set<Type> methods of the SQLServerCallableStatement class. In the following example, the prepareCall method is used to set up the call to the stored procedure, and then the setString method is used to set the parameter for the call before the executeQuery method is called.

try(CallableStatement cstmt = con.prepareCall("{call employee_jobid(?)}");) {
    String lname = "Brown";
    cstmt.setString(1, lname);
    ResultSet rs = cstmt.executeQuery();
}

Note

In this example, a result set is returned with the results of running the stored procedure.

BigDecimal

When using BigDecimal parameter values, the precision and scale may be passed along with the value through setBigDecimal. Using setBigDecimal this way avoids potential value truncation. Alternatively, if the connection string option calcBigDecimalPrecision is set to true, the driver will calculate precision for the BigDecimal input on the user's behalf, at the cost of performance. If the value is passed in alone, without calcBigDecimalPrecision set, or the option set to false, the driver assumes the maximum allowed value (38) for precision for that BigDecimal value.

For more information about using the JDBC driver with stored procedures and input parameters, see Using a stored procedure with input parameters.

Retrieving parameters from a stored procedure

If you have to retrieve parameters back from a stored procedure, you must first register an out parameter by name or index by using the registerOutParameter method of the SQLServerCallableStatement class. Then assign the returned out parameter to an appropriate variable after you run the call to the stored procedure. In the following example, the prepareCall method is used to set up the call to the stored procedure, the registerOutParameter method is used to set up the out parameter, and then the setString method is used to set the parameter for the call before executeQuery method is called. The value of the out parameter of the stored procedure is retrieved by using the getShort method.

try(CallableStatement cstmt = con.prepareCall("{call employee_jobid (?, ?)}");) {
    cstmt.registerOutParameter(2, java.sql.Types.SMALLINT);
    String lname = "Brown";
    cstmt.setString(1, lname);
    ResultSet rs = cstmt.executeQuery();
    short empJobID = cstmt.getShort(2);
}

Note

In addition to the returned out parameter, a result set might also be returned with the results of running the stored procedure.

For more information about how to use the JDBC driver with stored procedures and output parameters, see Using a stored procedure with output parameters.

See also

Understanding the JDBC driver data types