Monday, May 18, 2009

jdbc-data-types-tutorial

Data Types and Conversions :
For the getXXX methods, the JDBC driver attempts to convert the underlying data to the specified Java type and then returns a suitable Java value. For example, if the getXXX method is getString, and the data type of the data in the underlying database is VARCHAR, the JDBC driver will convert VARCHAR to Java String. The return value of getString will be a Java String object.

The following table shows which JDBC types a getXXX method is allowed to retrieve and which JDBC types (generic SQL types) are recommended for it to retrieve. A small x indicates a legal getXXX method for a particular data type; a large X indicates the recommended getXXX method for a data type. For example, any getXXX method except getBytes or getBinaryStream can be used to retrieve the value of a LONGVARCHAR, but getAsciiStream or getUnicodeStream are recommended, depending on which data type is being returned.

The method getObject will return any data type as a Java Object and is useful when the underlying data type is a database-specific abstract type or when a generic application needs to be able to accept any data type.


Use of ResultSet.getXXX methods to retrieve common JDBC data types.
An "x" indicates that the getXXX method may legally be used to retrieve the given JDBC type.

An "X" indicates that the getXXX method is recommended for retrieving the given JDBC type

Using Streams for Very Large Row Values :
ResultSet makes it possible to retrieve arbitrarily large LONGVARBINARY or LONGVARCHAR data. The methods getBytes and getString return data as one large chunk (up to the limits imposed by the return value of Statement.getMaxFieldSize). However, it may be more convenient to retrieve very large data in smaller, fixed-size chunks. This is done by having the ResultSet class return java.io.Input streams from which data can be read in chunks. Note that these streams must be accessed immediately because they will be closed automatically on the next getXXX call on ResultSet. (This behavior is imposed by underlying implementation constraints on large blob access.) 


The JDBC API has three separate methods for getting streams, each with a different return value: 

  • getBinaryStream returns a stream which simply provides the raw bytes from the database without any conversion.
  •  getAsciiStream returns a stream which provides one-byte ASCII characters.
  •  getUnicodeStream returns a stream which provides two-byte Unicode characters. 

    Note that this differs from Java streams, which return untyped bytes and can (for  example) be used for both ASCII and Unicode characters. 

The following code gives an example of using getAsciiStream: 

  java.sql.Statement stmt = con.createStatement();
  ResultSet r = stmt.executeQuery("SELECT x FROM Table2");
  // Now retrieve the column 1 results in 4 K chunks:
  byte buff = new byte[4096];
  while (r.next()) { 
  Java.io.InputStream fin = r.getAsciiStream(1);
  for (;;) {
  int size = fin.read(buff);
  if (size == -1) { // at end of stream
  break;
  }
  // Send the newly-filled buffer to some ASCII output stream:
  output.write(buff, 0, size);
  }
  }