| Contents | Prev | Next | JDBCTMGuide: Getting Started |
To accomplish this, JDBC provides sets of getXXX and setXXX methods, the method registerOutParameter, and the class Types.
This section brings together information about data types affecting various classes and interfaces and puts all the tables showing the mappings between SQL types and Java types in one place for easy reference.
INTEGER is normally mapped to a Java int. This supports a
simple interface for reading and writing SQL values as simple Java types.
The Java types do not need to be exactly isomorphic to the SQL types; they just need to be able to represent them with enough type information to correctly store and retrieve parameters and recover results from SQL statements. For example, a Java String object does not precisely match any of the SQL CHAR types, but it gives enough type information to represent CHAR, VARCHAR, or LONGVARCHAR successfully.
CHAR, VARCHAR, and LONGVARCHAR. Each can be expressed as a Java String, and it is
possible to read and write an SQL statement correctly without knowing the exact
data type that was expected.
CHAR, VARCHAR, and LONGVARCHAR could have been mapped to either String or char[], but String is more appropriate for normal use. Also, the String class makes it easy to convert between String and char[]. There is a method for converting a String object to a char[] and also a constructor for turning a char[] into a String object.
One issue that had to be addressed is how to handle fixed-length SQL strings of type CHAR(n). The answer is that JDBC drivers (or the DBMS) perform appropriate padding with spaces. Thus, when a CHAR(n) field is retrieved from the database, the driver will convert it to a Java String object of length n, which may include some padding spaces at the end. Conversely, when a String object is sent to a CHAR(n) field, the driver and/or the database will add any necessary padding spaces to the end of the string to bring it up to length n.
The method ResultSet.getString, which allocates and returns a new String object, is recommended for retrieving data from CHAR, VARCHAR, and LONGVARCHAR fields. This is suitable for retrieving normal data, but can be unwieldy if the type SQL LONGVARCHAR is being used to store multimegabyte strings. To handle this case, two methods in the ResultSet interface allow programmers to retrieve a LONGVARCHAR value as a Java input stream from which they can subsequently read data in whatever size chunks they prefer. These methods are getAsciiStream and getUnicodeStream, which deliver the data stored in a LONGVARCHAR column as a stream of ASCII or Unicode characters.
DECIMAL and NUMERIC, used to express fixed-point numbers
where absolute precision is required, can be expressed identically in Java. They are
mapped to java.math.BigDecimal, a Java type that also expresses fixed-point numbers with absolute precision. The java.math.BigDecimal type provides math operations to allow BigDecimal types to be added, subtracted, multiplied, and divided
with other BigDecimal types, with integer types, and with floating point types.
The method recommended for retrieving SQL DECIMAL and SQL NUMERIC values is ResultSet.getBigDecimal. JDBC also allows access to these SQL types as simple Strings or arrays of char. Thus, Java programmers can use getString to receive a NUMERIC or DECIMAL result. However, this makes the common case where NUMERIC or DECIMAL are used for currency values rather awkward, since it means that application writers have to perform math on strings. It is also possible to retrieve these SQL types as any of the Java numeric types.
BINARY, VARBINARY,
and LONGVARBINARY. They can all be expressed identically as byte arrays in Java.
Since it is possible to read and write SQL statements correctly without knowing the
exact BINARY data type that was expected, there is no need for Java programmers to
distinguish among them.
The method recommended for retrieving BINARY and VARBINARY values is ResultSet.getBytes. If a column of type SQL LONGVARBINARY stores a byte array that is many megabytes long, however, the method getBinaryStream is recommended. Similar to the situation with LONGVARCHAR, this method allows a Java programmer to retrieve a LONGVARBINARY value as a Java input stream that can be read later in smaller chunks.
BIT is mapped directly to the Java type boolean.
TINYINT, SMALLINT, INTEGER, and BIGINT are mapped as follows:
SQL TINYINT represents 8-bit values and is mapped to Java byte.
SQL SMALLINT represents 16-bit values and is mapped to Java short.
SQL INTEGER represents 32-bit values and is mapped to Java int.
SQL BIGINT represents 64-bit values and is mapped to Java long.
REAL, FLOAT, and DOUBLE; whereas Java
defines two: FLOAT and DOUBLE.
SQL REAL is required to support 7 digits of mantissa precision and is
SQL FLOAT and SQL DOUBLE are required to support 15 digits of mantissa
precision and are mapped to Java double.
DATE consists of day, month, and year.TIME consists of of hours, minutes, and seconds.TIMESTAMP consists of DATE plus TIME plus a nanosecond field.java.util.Date does not match any of these three SQL types exactly (it includes both DATE and TIME information but has no nanoseconds), JDBC defines three subclasses of java.util.Date to correspond to the SQL types. They are:
java.sql.Date for SQL DATE information. The hour, minute, second, and millisecond fields of the java.util.Date base class are set to zero. java.sql.Time for SQL TIME information. The year, month, and day fields of the java.util.Date base class are set to 1970, January, and 1. This is the "zero" date in the Java epoch.java.sql.Timestamp for SQL TIMESTAMP information. This class extends java.util.Date by adding a nanosecond field.java.util.Date, and as such, they can be used where a java.util.Date is expected. For example, internationalization methods take a java.util.Date object as an argument, so they can be passed instances of any of the JDBC time-related classes.
A JDBC Timestamp object has its parent's date and time components and also a separate nanoseconds component. If a java.sql.Timestamp object is used where a java.util.Date object is expected, the nanoseconds component is lost. However, since a java.util.Date object is stored with a precision of one millisecond, it is possible to maintain this degree of precision when converting a java.sql.Timestamp object to a java.util.Date object. This is done by converting the nanoseconds in the nanoseconds component to whole milliseconds (by dividing the number of nanoseconds by 1,000,000) and then adding the result to the the java.util.Date object. Up to 999,999 nanoseconds may be lost in this conversion, but the resulting java.util.Date object will be accurate to within one millisecond.
ResultSet, PreparedStatement, and CallableStatement. This section presents three different scenarios, describing the data mapping and conversion
required in each.
ResultSet object with the results. The value returned by the database and stored in
a ResultSet column will have an SQL data type. A call to a ResultSet.getXXX
method will retrieve that value as a Java data type. For example, if a ResultSet column contains an SQL FLOAT value, the method getDouble will retrieve that value as
a Java double. The table in Section 9.5.6 shows which getXXX methods may be
used to retrieve which SQL types. (A user who does not know the type of a ResultSet column can get that information by calling the method ResultSet.getMetaData
and then invoking the ResultSetMetaData methods getColumnType or getColumnTypeName.) The following code fragment demonstrates getting the column type
names for the columns in a result set:
String query = "select * from Table1";
ResultSet rs = stmt.executeQuery(query);
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
String s = rsmd.getColumnTypeName(i);
System.out.println ("Column " + i + " is type " + s);
}
PreparedStatement.setXXX methods to
assign a value to each input parameter. For example, PreparedStatement.setLong(1, 2345678) will assign the value 2345678 to the first parameter as a Java
long. The driver will convert 2345678 to an SQL BIGINT in order to send it to the
database. Which SQL type the driver sends to the database is determined by the
standard mapping from Java types to SQL types, which is shown in the table in Section 9.5.2.
In this scenario, the first thing to do is to assign values to the INOUT parameters using PreparedStatement.setXXX methods. In addition, since the parameters will also be used for output, the programmer must register each parameter with the SQL type of the value that the database will return to it. This is done with the method CallableStatement.registerOutParameter, which takes one of the SQL types defined in the class Types. A programmer retrieves the results returned to a ResultSet object with ResultSet.getXXX methods and retrieves the values stored in the output parameters with CallableStatement.getXXX methods.
The XXX type used for ResultSet.getXXX methods is fairly flexible in some cases. The table in Section 9.5.6 shows which ResultSet.getXXX methods can be used to retrieve which SQL types.
The XXX type used for CallableStatement.getXXX must map to the SQL type registered for that parameter. For example, if the database is expected to return an output value whose type is SQL REAL, the parameter should have been registered as java.sql.Types.REAL. Then to retrieve the SQL REAL value, the method CallableStatement.getFloat should be called (the mapping from SQL types to Java types is shown in the table in Section 9.5.1). The method getFloat will return the value stored in the output parameter after converting it from an SQL REAL to a Java float. To accommodate various databases and make an application more portable, it is recommended that values be retrieved from ResultSet objects before values are retrieved from output parameters.
The following code demonstrates calling a stored procedure named getTestData, which has two parameters that are both INOUT parameters. First the Connection object con creates the CallableStatement object cstmt. Then the method setByte sets the first parameter to 25 as a Java byte. The driver will convert 25 to an SQL TINYINT and send it to the database. The method setBigDecimal sets the second parameter with an input value of 83.75. The driver will convert this java.math.BigDecimal object to an SQL NUMERIC value. Next the two parameters are registered as OUT parameters, the first parameter as an SQL TINYINT and the second parameter as an SQL DECIMAL with two digits after the decimal point. After cstmt is executed, the values are retrieved from the ResultSet object using ResultSet.getXXX methods. The method getString gets the value in the first column as a Java String object, getInt gets the value in the second column as a Java int, and getInt gets the value in the third column as a Java int.
Then CallableStatement.getXXX methods retrieve the values stored in the output parameters. The method getByte retrieves the SQL TINYINT as a Java byte, and getBigDecimal retrieves the SQL DECIMAL as a java.math.BigDecimal object with two digits after the decimal point. Note that when a parameter is both an input and an output parameter, the setXXX method uses the same Java type as the getXXX method (as in setByte and getByte). The registerOutParameter method registers it to the SQL type that is mapped from the Java type (a Java byte maps to an SQL TINYINT, as shown in the table in Section 9.5.2).
CallableStatement cstmt = con.prepareCall(
"{call getTestData(?, ?)}");
cstmt.setByte(1, 25);
cstmt.setBigDecimal(2, 83.75);
// register the first parameter as an SQL TINYINT and the second
//parameter as an SQL DECIMAL with two digits after the decimal point
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.registerOutParameter(2, java.sql.Types.DECIMAL, 2);
ResultSet rs = cstmt.executeUpdate();
// retrieve and print values in result set
while(rs.next()) {
String name = rs.getString(1);
int score = rs.getInt(2);
int percentile = rs.getInt(3);
System.out.print("name = " + name + ", score = " + score + ", "
System.out.println("percentile = " + percentile);
// retrieve values in output parameters
byte x = cstmt.getByte(1);
java.math.BigDecimal n = cstmt.getBigDecimal(2, 2);
To generalize, the XXX in CallableStatement.getXXX and PreparedStatement.setXXX methods is a Java type. For setXXX methods, the driver converts the Java type to an SQL type before sending it to the database (using the standard mappings shown in the table in Section 9.5.2). For getXXX methods, the driver converts the SQL type returned by the database to a Java type (using the standard mappings shown in the table in Section 9.5.1) before returning it to the getXXX method.
The method registerOutParameter always takes an SQL type as an argument, and the method setObject may take an SQL type as an argument.
Note that if an SQL type is supplied in its optional third argument, the method setObject will cause an explicit conversion of the parameter value from a Java type to the SQL type specified. If no target Sql type is supplied to setObject, the parameter value will be converted to the SQL type that is the standard mapping from the Java type (as shown in Section 9.5.2). The driver will perform the explicit or implicit conversion before sending the parameter to the database.
Section 9.5.1-SQL Types Mapped to Java Types
Section 9.5.2-Java Types Mapped to SQL Types
Section 9.5.3-SQL Types Mapped to Java Object Types
Section 9.5.4-Java Object Types Mapped to SQL Types
Section 9.5.5- Conversions by setObject
Section 9.5.6-SQL Types Retrieved by ResultSet.getXXX methods
The mapping for String will normally be VARCHAR but will turn into LONGVARCHAR if the given value exceeds the driver's limit on VARCHAR values. The same is true for byte[] and VARBINARY and LONGVARBINARY values.
boolean and int are not subtypes of Object, there is a slightly different mapping from SQL types to Java object types for the getObject/setObject methods. This mapping is shown in the following table:
Note that the mapping for String will normaly be VARCHAR but will turn into LONGVARCHAR if the given value exceeds the driver's limit on VARCHAR values. The case is similar for byte[] and VARBINARY and LONGVARBINARY values.
setObject converts Java object types to SQL types.
Conversion from Java object types to SQL types.