Using Advanced Data Types

The JDBC advanced data types were introduced in the JDBC 2.0 core API. The Microsoft SQL Server 2005 JDBC Driver uses the JDBC advanced data types to convert the SQL Server data types to a format that can be understood by the Java programming language.

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

The following sections provide examples of how you can use the JDBC driver and the advanced data types.

BLOB and CLOB Data Types

The JDBC driver implements all the JDBC 3.0 methods of the java.sql.Blob and java.sql.Clob interfaces. By using these methods, you can retrieve and update the columns of type text, ntext, xml, and image.

Large Value Data Types

In earlier versions of SQL Server, working with large-value data types required special handling. Large-value data types are those that exceed the maximum row size of 8 KB. SQL Server 2005 introduces a max specifier for varchar, nvarchar, and varbinary data types to allow storage of values as large as 2^31 bytes. Table columns and Transact-SQL variables can specify varchar(max), nvarchar(max), or varbinary(max) data types.

The primary scenarios for working with large-value types involve retrieving them from a database, or adding them to a database. The following sections describe different approaches to accomplish these tasks.

Retrieving Large-Value Types from a Database

When you retrieve a non-binary large-value data type—such as the varchar(max) data type—from a database, one approach is to read that data as a character stream. In the following example, the executeQuery method of the SQLServerStatement class is used to retrieve data from the database and return it as a result set. Then the getCharacterStream method of the SQLServerResultSet class is used to read the large-value data from the result set.

ResultSet rs = stmt.executeQuery("SELECT TOP 1 * FROM Test1");
rs.next();
Reader reader = rs.getCharacterStream(2);

When you retrieve a binary large-value data type—such as the varbinary(max) data type—from a database, there are several approaches that you can take. The most efficient approach is to read the data as a binary stream, as in the following:

ResultSet rs = stmt.executeQuery("SELECT photo FROM mypics");
Rs.next();
InputStream is = rs.getBinaryStream(2);

You can also use the getBytes method to read the data as a byte array, as in the following:

ResultSet rs = stmt.executeQuery("SELECT photo FROM mypics");
Rs.next();
byte [] b = rs.getBytes(2);

Adding Large-Value Types to a Database

Uploading large data with the JDBC driver works well for the memory-sized cases, and in the larger-than-memory cases, streaming is the primary option. However, the most efficient way to upload large data is through the stream interfaces.

Using a String or bytes is also an option, as in the following:

PreparedStatement pstmt = con.prepareStatement("INSERT INTO test1 (c1_id, c2_vcmax) VALUES (?, ?)");
pstmt.setInt(1, 1);
pstmt.setString(2, htmlStr);
pstmt.executeUpdate();

If you have an image library on the server and must upload entire binary image files to a varbinary(max) column, the most efficient method with the JDBC driver is to use streams directly, as in the following:

Statement stmt = con.createStatement();
PreparedStatement pstmt = con.prepareStatement("INSERT INTO test1 
VALUES( ?, ? )");
FileReader in = new FileReader(new File("CLOBFile20mb.txt");
long len = fileIn.length();
int id = 1;
pstmt.setInt(1,id);
pstmt.setCharacterStream(2, in, (int)len);
pstmt.executeUpdate();
in.close();

Modifying Large-Value Types in a Database

In most cases, the recommended method for updating or modifying large values on the database is to pass parameters through the SQLServerPreparedStatement and SQLServerCallableStatement classes by using Transact-SQL commands like UPDATE, WRITE, and SUBSTRING.

If you have to replace the instance of a word in a large text file, such as an archived HTML file, you can use a Clob object, as in the following:

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM test1");
rs.next();
Clob clob = rs.getClob(2);
long pos = clob.position("dog", 1);
clob.setString(1, "cat", len, 3);
rs.updateClob(2, clob);
rs.updateRow();

Additionally, you could do all the work on the server and just pass parameters to a prepared UPDATE statement.

For more information about large-value types, see "Using Large-Value Types" in SQL Server Books Online.

XML Data Type

SQL Server 2005 provides an xml data type that lets you store XML documents and fragments in a SQL Server database. The xml data type is a built-in data type in SQL Server, and is in some ways similar to other built-in types, such as int and varchar. As with other built-in types, you can use the xml data type as a column type when you create a table; as a variable type, a parameter type, or a function-return type; or in Transact-SQL CAST and CONVERT functions.

In the JDBC driver, the xml data type can be mapped as a String, byte array, stream, CLOB, or BLOB object. String is the default representation. The implementation of the xml data type in the JDBC driver provides support for the following:

• Access to the XML as a standard Java UTF-16 string for most common programming scenarios

• Input of UTF-8 and other 8-bit encoded XML

• Access to the XML as a byte array with a leading BOM when encoded in UTF-16 for interchange with other XML processors and disk files

SQL Server requires a leading BOM for UTF-16-encoded XML. The application must provide this when XML parameter values are supplied as byte arrays. SQL Server always outputs XML values as UTF-16 strings with no BOM or embedded encoding declaration. When XML values are retrieved as byte[], BinaryStream or Blob, a UTF-16 BOM is pre-pended to the value.

For more information about the xml data type, see "xml Data Type" in SQL Server Books Online.

User-Defined Data Type

The introduction of user-defined types (UDTs) in SQL Server 2005 extends the SQL type system by letting you store objects and custom data structures in a SQL Server database. UDTs can contain multiple data types and can have behaviors, differentiating them from the traditional alias data types that consist of a single SQL Server system data type. UDTs are defined by using any of the languages supported by the Microsoft .NET common language runtime (CLR) that produce verifiable code. This includes Microsoft Visual C# and Visual Basic .NET. The data is exposed as fields and properties of a .NET Framework-based class or structure, and behaviors are defined by methods of the class or structure.

In SQL Server 2005, a UDT can be used as the column definition of a table, as a variable in a Transact-SQL batch, or as an argument of a Transact-SQL function or stored procedure.

For more information about user-defined data types, see "Using and Modifying Instances of User-defined Types" in SQL Server Books Online.

See Also

Other Resources

Understanding the JDBC Driver Data Types