Export (0) Print
Expand All

Using Basic Data Types

The Microsoft JDBC Driver for SQL Server uses the JDBC basic data types to convert the SQL Server data types to a format that can be 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.

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

datetime

TIMESTAMP

java.sql.Timestamp

datetime2

TIMESTAMP

java.sql.Timestamp

datetimeoffset (2)

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

TIME (1)

java.sql.Time (1)

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

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

(2) You can programmatically access values of datetimeoffset with DateTimeOffset Class.

The SQL Server sqlvariant data type is not currently supported by the JDBC driver. If a query is used to retrieve data from a table that contains a column of the sqlvariant data type, an exception will occur.

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.

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 is not required, you can use the getString method of the SQLServerResultSet class, as in the following:

String SQL = "SELECT TOP 10 * FROM Person.Contact";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(SQL);

while (rs.next()) {
   System.out.println(rs.getString(4) + " " + rs.getString(6));
}
rs.close();
stmt.close();


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:

ResultSet rs = stmt.executeQuery("SELECT lname, job_id FROM employee
   WHERE (lname = 'Brown')");
rs.next();
short empJobID = rs.getShort("job_id");
rs.close();
stmt.close();


Note Note

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

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 in conjunction with the updateRow method to update the data in the data source:

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();
short empJobID = rs.getInt(2);
empJobID++;
rs.first();
rs.updateInt(2, empJobID);
rs.updateRow();
rs.close();
stmt.close();


Note 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.

If you have to 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, also known as the setter methods. In the following example, the prepareStatement method is used to pre-compile the parameterized query, and then the setString method is used to set the string value of the parameter before the executeUpdate method is called.

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


For more information about parameterized queries, see Using an SQL Statement with Parameters.

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.

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


Note Note

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

For more information about using the JDBC driver with stored procedures and input parameters, see Using a Stored Procedure with Input Parameters.

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, and 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 that is returned by the out parameter of the stored procedure is retrieved by using the getShort method.

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);
rs.close();
cstmt.close();


Note 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.

Show:
© 2014 Microsoft