@Override
public void releaseResources() {
log.trace("Releasing JDBC resources");
for (Map.Entry<Statement, Set<ResultSet>> entry : xref.entrySet()) {
if (entry.getValue() != null) {
closeAll(entry.getValue());
}
close(entry.getKey());
}
xref.clear();
closeAll(unassociatedResultSets);
if (blobs != null) {
for (Blob blob : blobs) {
try {
blob.free();
} catch (SQLException e) {
log.debugf("Unable to free JDBC Blob reference [%s]", e.getMessage());
}
}
blobs.clear();
}
if (clobs != null) {
for (Clob clob : clobs) {
try {
clob.free();
} catch (SQLException e) {
log.debugf("Unable to free JDBC Clob reference [%s]", e.getMessage());
}
}
clobs.clear();
}
if (nclobs != null) {
for (NClob nclob : nclobs) {
try {
nclob.free();
} catch (SQLException e) {
log.debugf("Unable to free JDBC NClob reference [%s]", e.getMessage());
}
}
nclobs.clear();
}
}
private void testClob(int length) throws Exception {
Random r = new Random(length);
char[] data = new char[length];
// Unicode problem:
// The UCS code values 0xd800-0xdfff (UTF-16 surrogates)
// as well as 0xfffe and 0xffff (UCS non-characters)
// should not appear in conforming UTF-8 streams.
// (String.getBytes("UTF-8") only returns 1 byte for 0xd800-0xdfff)
for (int i = 0; i < length; i++) {
char c;
do {
c = (char) r.nextInt();
} while (c >= 0xd800 && c <= 0xdfff);
data[i] = c;
}
Clob c = conn.createClob();
Writer out = c.setCharacterStream(1);
out.write(data, 0, data.length);
out.close();
stat.execute("delete from test");
PreparedStatement prep = conn.prepareStatement("insert into test values(?, ?)");
prep.setInt(1, 1);
prep.setClob(2, c);
prep.execute();
c = conn.createClob();
c.setString(1, new String(data));
prep.setInt(1, 2);
prep.setClob(2, c);
prep.execute();
prep.setInt(1, 3);
prep.setCharacterStream(2, new StringReader(new String(data)));
prep.execute();
prep.setInt(1, 4);
prep.setCharacterStream(2, new StringReader(new String(data)), -1);
prep.execute();
NClob nc;
nc = conn.createNClob();
nc.setString(1, new String(data));
prep.setInt(1, 5);
prep.setNClob(2, nc);
prep.execute();
prep.setInt(1, 5);
prep.setNClob(2, new StringReader(new String(data)));
prep.execute();
prep.setInt(1, 6);
prep.setNClob(2, new StringReader(new String(data)), -1);
prep.execute();
prep.setInt(1, 7);
prep.setNString(2, new String(data));
prep.execute();
ResultSet rs;
rs = stat.executeQuery("select * from test");
rs.next();
Clob c2 = rs.getClob(2);
assertEquals(length, c2.length());
String s = c.getSubString(1, length);
String s2 = c2.getSubString(1, length);
while (rs.next()) {
c2 = rs.getClob(2);
assertEquals(length, c2.length());
s2 = c2.getSubString(1, length);
assertEquals(s, s2);
}
}
public static Object parseType(ResultSet result, Integer i, int type)
throws SQLException, IOException, ParseException {
logger.trace("i={} type={}", i, type);
switch (type) {
/**
* The JDBC types CHAR, VARCHAR, and LONGVARCHAR are closely related. CHAR represents a
* small, fixed-length character string, VARCHAR represents a small, variable-length
* character string, and LONGVARCHAR represents a large, variable-length character string.
*/
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
{
return result.getString(i);
}
case Types.NCHAR:
case Types.NVARCHAR:
case Types.LONGNVARCHAR:
{
return result.getNString(i);
}
/**
* The JDBC types BINARY, VARBINARY, and LONGVARBINARY are closely related. BINARY
* represents a small, fixed-length binary value, VARBINARY represents a small,
* variable-length binary value, and LONGVARBINARY represents a large, variable-length
* binary value
*/
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
{
byte[] b = result.getBytes(i);
return b;
}
/**
* The JDBC type ARRAY represents the SQL3 type ARRAY.
*
* <p>An ARRAY value is mapped to an instance of the Array interface in the Java programming
* language. If a driver follows the standard implementation, an Array object logically
* points to an ARRAY value on the server rather than containing the elements of the ARRAY
* object, which can greatly increase efficiency. The Array interface contains methods for
* materializing the elements of the ARRAY object on the client in the form of either an
* array or a ResultSet object.
*/
case Types.ARRAY:
{
Array arr = result.getArray(i);
return arr == null ? null : arr.getArray();
}
/**
* The JDBC type BIGINT represents a 64-bit signed integer value between
* -9223372036854775808 and 9223372036854775807.
*
* <p>The corresponding SQL type BIGINT is a nonstandard extension to SQL. In practice the
* SQL BIGINT type is not yet currently implemented by any of the major databases, and we
* recommend that its use be avoided in code that is intended to be portable.
*
* <p>The recommended Java mapping for the BIGINT type is as a Java long.
*/
case Types.BIGINT:
{
Object o = result.getLong(i);
return result.wasNull() ? null : o;
}
/**
* The JDBC type BIT represents a single bit value that can be zero or one.
*
* <p>SQL-92 defines an SQL BIT type. However, unlike the JDBC BIT type, this SQL-92 BIT
* type can be used as a parameterized type to define a fixed-length binary string.
* Fortunately, SQL-92 also permits the use of the simple non-parameterized BIT type to
* represent a single binary digit, and this usage corresponds to the JDBC BIT type.
* Unfortunately, the SQL-92 BIT type is only required in "full" SQL-92 and is currently
* supported by only a subset of the major databases. Portable code may therefore prefer to
* use the JDBC SMALLINT type, which is widely supported.
*/
case Types.BIT:
{
try {
Object o = result.getInt(i);
return result.wasNull() ? null : o;
} catch (Exception e) {
String exceptionClassName = e.getClass().getName();
// postgresql can not handle boolean, it will throw PSQLException, something like "Bad
// value for type int : t"
if ("org.postgresql.util.PSQLException".equals(exceptionClassName)) {
return "t".equals(result.getString(i));
}
throw new IOException(e);
}
}
/**
* The JDBC type BOOLEAN, which is new in the JDBC 3.0 API, maps to a boolean in the Java
* programming language. It provides a representation of true and false, and therefore is a
* better match than the JDBC type BIT, which is either 1 or 0.
*/
case Types.BOOLEAN:
{
return result.getBoolean(i);
}
/**
* The JDBC type BLOB represents an SQL3 BLOB (Binary Large Object).
*
* <p>A JDBC BLOB value is mapped to an instance of the Blob interface in the Java
* programming language. If a driver follows the standard implementation, a Blob object
* logically points to the BLOB value on the server rather than containing its binary data,
* greatly improving efficiency. The Blob interface provides methods for materializing the
* BLOB data on the client when that is desired.
*/
case Types.BLOB:
{
Blob blob = result.getBlob(i);
if (blob != null) {
long n = blob.length();
if (n > Integer.MAX_VALUE) {
throw new IOException("can't process blob larger than Integer.MAX_VALUE");
}
byte[] tab = blob.getBytes(1, (int) n);
blob.free();
return tab;
}
break;
}
/**
* The JDBC type CLOB represents the SQL3 type CLOB (Character Large Object).
*
* <p>A JDBC CLOB value is mapped to an instance of the Clob interface in the Java
* programming language. If a driver follows the standard implementation, a Clob object
* logically points to the CLOB value on the server rather than containing its character
* data, greatly improving efficiency. Two of the methods on the Clob interface materialize
* the data of a CLOB object on the client.
*/
case Types.CLOB:
{
Clob clob = result.getClob(i);
if (clob != null) {
long n = clob.length();
if (n > Integer.MAX_VALUE) {
throw new IOException("can't process clob larger than Integer.MAX_VALUE");
}
String str = clob.getSubString(1, (int) n);
clob.free();
return str;
}
break;
}
case Types.NCLOB:
{
NClob nclob = result.getNClob(i);
if (nclob != null) {
long n = nclob.length();
if (n > Integer.MAX_VALUE) {
throw new IOException("can't process nclob larger than Integer.MAX_VALUE");
}
String str = nclob.getSubString(1, (int) n);
nclob.free();
return str;
}
break;
}
/**
* The JDBC type DATALINK, new in the JDBC 3.0 API, is a column value that references a file
* that is outside of a data source but is managed by the data source. It maps to the Java
* type java.net.URL and provides a way to manage external files. For instance, if the data
* source is a DBMS, the concurrency controls it enforces on its own data can be applied to
* the external file as well.
*
* <p>A DATALINK value is retrieved from a ResultSet object with the ResultSet methods
* getURL or getObject. If the Java platform does not support the type of URL returned by
* getURL or getObject, a DATALINK value can be retrieved as a String object with the method
* getString.
*
* <p>java.net.URL values are stored in a database using the method setURL. If the Java
* platform does not support the type of URL being set, the method setString can be used
* instead.
*/
case Types.DATALINK:
{
return result.getURL(i);
}
/**
* The JDBC DATE type represents a date consisting of day, month, and year. The
* corresponding SQL DATE type is defined in SQL-92, but it is implemented by only a subset
* of the major databases. Some databases offer alternative SQL types that support similar
* semantics.
*/
case Types.DATE:
{
try {
Date d = result.getDate(i, calendar);
return d != null ? formatDate(d.getTime()) : null;
} catch (SQLException e) {
return null;
}
}
case Types.TIME:
{
try {
Time t = result.getTime(i, calendar);
return t != null ? formatDate(t.getTime()) : null;
} catch (SQLException e) {
return null;
}
}
case Types.TIMESTAMP:
{
try {
Timestamp t = result.getTimestamp(i, calendar);
return t != null ? formatDate(t.getTime()) : null;
} catch (SQLException e) {
// java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column ... to
// TIMESTAMP.
return null;
}
}
/**
* The JDBC types DECIMAL and NUMERIC are very similar. They both represent fixed-precision
* decimal values.
*
* <p>The corresponding SQL types DECIMAL and NUMERIC are defined in SQL-92 and are very
* widely implemented. These SQL types take precision and scale parameters. The precision is
* the total number of decimal digits supported, and the scale is the number of decimal
* digits after the decimal point. For most DBMSs, the scale is less than or equal to the
* precision. So for example, the value "12.345" has a precision of 5 and a scale of 3, and
* the value ".11" has a precision of 2 and a scale of 2. JDBC requires that all DECIMAL and
* NUMERIC types support both a precision and a scale of at least 15.
*
* <p>The sole distinction between DECIMAL and NUMERIC is that the SQL-92 specification
* requires that NUMERIC types be represented with exactly the specified precision, whereas
* for DECIMAL types, it allows an implementation to add additional precision beyond that
* specified when the type was created. Thus a column created with type NUMERIC(12,4) will
* always be represented with exactly 12 digits, whereas a column created with type
* DECIMAL(12,4) might be represented by some larger number of digits.
*
* <p>The recommended Java mapping for the DECIMAL and NUMERIC types is
* java.math.BigDecimal. 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.
*
* <p>The method recommended for retrieving DECIMAL and 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 DECIMAL or NUMERIC
* result. However, this makes the common case where DECIMAL or NUMERIC 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.
*/
case Types.DECIMAL:
case Types.NUMERIC:
{
BigDecimal bd = null;
try {
// getBigDecimal() should get obsolete. Most seem to use getString/getObject anyway...
bd = result.getBigDecimal(i);
} catch (NullPointerException e) {
// But is it true? JDBC NPE exists since 13 years?
// http://forums.codeguru.com/archive/index.php/t-32443.html
// Null values are driving us nuts in JDBC:
// http://stackoverflow.com/questions/2777214/when-accessing-resultsets-in-jdbc-is-there-an-elegant-way-to-distinguish-betwee
}
if (bd == null || result.wasNull()) {
return null;
}
int scale = 2;
if (scale >= 0) {
bd = bd.setScale(scale, BigDecimal.ROUND_UP);
try {
long l = bd.longValueExact();
if (Long.toString(l).equals(result.getString(i))) {
// convert to long if possible
return l;
} else {
// convert to double (with precision loss)
return bd.doubleValue();
}
} catch (ArithmeticException e) {
return bd.doubleValue();
}
} else {
return bd.toPlainString();
}
}
/**
* The JDBC type DOUBLE represents a "double precision" floating point number that supports
* 15 digits of mantissa.
*
* <p>The corresponding SQL type is DOUBLE PRECISION, which is defined in SQL-92 and is
* widely supported by the major databases. The SQL-92 standard leaves the precision of
* DOUBLE PRECISION up to the implementation, but in practice all the major databases
* supporting DOUBLE PRECISION support a mantissa precision of at least 15 digits.
*
* <p>The recommended Java mapping for the DOUBLE type is as a Java double.
*/
case Types.DOUBLE:
{
String s = result.getString(i);
if (result.wasNull() || s == null) {
return null;
}
NumberFormat format = NumberFormat.getInstance(locale);
Number number = format.parse(s);
return number.doubleValue();
}
/**
* The JDBC type FLOAT is basically equivalent to the JDBC type DOUBLE. We provided both
* FLOAT and DOUBLE in a possibly misguided attempt at consistency with previous database
* APIs. FLOAT represents a "double precision" floating point number that supports 15 digits
* of mantissa.
*
* <p>The corresponding SQL type FLOAT is defined in SQL-92. The SQL-92 standard leaves the
* precision of FLOAT up to the implementation, but in practice all the major databases
* supporting FLOAT support a mantissa precision of at least 15 digits.
*
* <p>The recommended Java mapping for the FLOAT type is as a Java double. However, because
* of the potential confusion between the double precision SQL FLOAT and the single
* precision Java float, we recommend that JDBC programmers should normally use the JDBC
* DOUBLE type in preference to FLOAT.
*/
case Types.FLOAT:
{
String s = result.getString(i);
if (result.wasNull() || s == null) {
return null;
}
NumberFormat format = NumberFormat.getInstance(locale);
Number number = format.parse(s);
return number.doubleValue();
}
/**
* The JDBC type JAVA_OBJECT, added in the JDBC 2.0 core API, makes it easier to use objects
* in the Java programming language as values in a database. JAVA_OBJECT is simply a type
* code for an instance of a class defined in the Java programming language that is stored
* as a database object. The type JAVA_OBJECT is used by a database whose type system has
* been extended so that it can store Java objects directly. The JAVA_OBJECT value may be
* stored as a serialized Java object, or it may be stored in some vendor-specific format.
*
* <p>The type JAVA_OBJECT is one of the possible values for the column DATA_TYPE in the
* ResultSet objects returned by various DatabaseMetaData methods, including getTypeInfo,
* getColumns, and getUDTs. The method getUDTs, part of the new JDBC 2.0 core API, will
* return information about the Java objects contained in a particular schema when it is
* given the appropriate parameters. Having this information available facilitates using a
* Java class as a database type.
*/
case Types.OTHER:
case Types.JAVA_OBJECT:
{
return result.getObject(i);
}
/**
* The JDBC type REAL represents a "single precision" floating point number that supports
* seven digits of mantissa.
*
* <p>The corresponding SQL type REAL is defined in SQL-92 and is widely, though not
* universally, supported by the major databases. The SQL-92 standard leaves the precision
* of REAL up to the implementation, but in practice all the major databases supporting REAL
* support a mantissa precision of at least seven digits.
*
* <p>The recommended Java mapping for the REAL type is as a Java float.
*/
case Types.REAL:
{
String s = result.getString(i);
if (result.wasNull() || s == null) {
return null;
}
NumberFormat format = NumberFormat.getInstance(locale);
Number number = format.parse(s);
return number.doubleValue();
}
/**
* The JDBC type TINYINT represents an 8-bit integer value between 0 and 255 that may be
* signed or unsigned.
*
* <p>The corresponding SQL type, TINYINT, is currently supported by only a subset of the
* major databases. Portable code may therefore prefer to use the JDBC SMALLINT type, which
* is widely supported.
*
* <p>The recommended Java mapping for the JDBC TINYINT type is as either a Java byte or a
* Java short. The 8-bit Java byte type represents a signed value from -128 to 127, so it
* may not always be appropriate for larger TINYINT values, whereas the 16-bit Java short
* will always be able to hold all TINYINT values.
*/
/**
* The JDBC type SMALLINT represents a 16-bit signed integer value between -32768 and 32767.
*
* <p>The corresponding SQL type, SMALLINT, is defined in SQL-92 and is supported by all the
* major databases. The SQL-92 standard leaves the precision of SMALLINT up to the
* implementation, but in practice, all the major databases support at least 16 bits.
*
* <p>The recommended Java mapping for the JDBC SMALLINT type is as a Java short.
*/
/**
* The JDBC type INTEGER represents a 32-bit signed integer value ranging between
* -2147483648 and 2147483647.
*
* <p>The corresponding SQL type, INTEGER, is defined in SQL-92 and is widely supported by
* all the major databases. The SQL-92 standard leaves the precision of INTEGER up to the
* implementation, but in practice all the major databases support at least 32 bits.
*
* <p>The recommended Java mapping for the INTEGER type is as a Java int.
*/
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
{
try {
Integer integer = result.getInt(i);
return result.wasNull() ? null : integer;
} catch (SQLDataException e) {
Long l = result.getLong(i);
return result.wasNull() ? null : l;
}
}
case Types.SQLXML:
{
SQLXML xml = result.getSQLXML(i);
return xml != null ? xml.getString() : null;
}
case Types.NULL:
{
return null;
}
/**
* The JDBC type DISTINCT field (Types class)>DISTINCT represents the SQL3 type DISTINCT.
*
* <p>The standard mapping for a DISTINCT type is to the Java type to which the base type of
* a DISTINCT object would be mapped. For example, a DISTINCT type based on a CHAR would be
* mapped to a String object, and a DISTINCT type based on an SQL INTEGER would be mapped to
* an int.
*
* <p>The DISTINCT type may optionally have a custom mapping to a class in the Java
* programming language. A custom mapping consists of a class that implements the interface
* SQLData and an entry in a java.util.Map object.
*/
case Types.DISTINCT:
{
logger.warn("JDBC type not implemented: {}", type);
return null;
}
/**
* The JDBC type STRUCT represents the SQL99 structured type. An SQL structured type, which
* is defined by a user with a CREATE TYPE statement, consists of one or more attributes.
* These attributes may be any SQL data type, built-in or user-defined.
*
* <p>The standard mapping for the SQL type STRUCT is to a Struct object in the Java
* programming language. A Struct object contains a value for each attribute of the STRUCT
* value it represents.
*
* <p>A STRUCT value may optionally be custom mapped to a class in the Java programming
* language, and each attribute in the STRUCT may be mapped to a field in the class. A
* custom mapping consists of a class that implements the interface SQLData and an entry in
* a java.util.Map object.
*/
case Types.STRUCT:
{
logger.warn("JDBC type not implemented: {}", type);
return null;
}
case Types.REF:
{
logger.warn("JDBC type not implemented: {}", type);
return null;
}
case Types.ROWID:
{
logger.warn("JDBC type not implemented: {}", type);
return null;
}
default:
{
logger.warn("unknown JDBC type ignored: {}", type);
return null;
}
}
return null;
}