public Item(int id, String name, int price, int quantity, Clob description) throws SQLException { this.id = id; this.name = name; this.price = price; this.quantity = quantity; this.description = description; this.stringDescr = description.getSubString(1L, (int) description.length()); }
/** * Inserts a Clob with the specified length, using a stream source, then fetches it from the * database and checks the length. * * @param length number of characters in the Clob * @throws IOException if reading from the source fails * @throws SQLException if something goes wrong */ private void insertAndFetchTest(long length) throws IOException, SQLException { PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)"); int id = BlobClobTestSetup.getID(); ps.setInt(1, id); ps.setCharacterStream(2, new LoopingAlphabetReader(length), length); long tsStart = System.currentTimeMillis(); ps.execute(); println( "Inserted " + length + " chars (length specified) in " + (System.currentTimeMillis() - tsStart) + " ms"); Statement stmt = createStatement(); tsStart = System.currentTimeMillis(); ResultSet rs = stmt.executeQuery("select CLOBDATA from BLOBCLOB where id = " + id); assertTrue("Clob not inserted", rs.next()); Clob aClob = rs.getClob(1); assertEquals("Invalid length", length, aClob.length()); println("Fetched length (" + length + ") in " + (System.currentTimeMillis() - tsStart) + " ms"); rs.close(); // Insert same Clob again, using the lengthless override. id = BlobClobTestSetup.getID(); ps.setInt(1, id); ps.setCharacterStream(2, new LoopingAlphabetReader(length)); tsStart = System.currentTimeMillis(); ps.executeUpdate(); println( "Inserted " + length + " chars (length unspecified) in " + (System.currentTimeMillis() - tsStart) + " ms"); rs = stmt.executeQuery("select CLOBDATA from BLOBCLOB where id = " + id); assertTrue("Clob not inserted", rs.next()); aClob = rs.getClob(1); assertEquals("Invalid length", length, aClob.length()); println("Fetched length (" + length + ") in " + (System.currentTimeMillis() - tsStart) + " ms"); rs.close(); rollback(); }
private static String read(Clob c) throws SQLException, IOException { StringBuilder sb = new StringBuilder((int) c.length()); Reader r = c.getCharacterStream(); char[] cbuf = new char[CLOBBUFFERSIZE]; int n; while ((n = r.read(cbuf, 0, cbuf.length)) != -1) { sb.append(cbuf, 0, n); } return sb.toString(); }
private static String read(Clob c) throws SQLException, IOException { StringBuffer sb = new StringBuffer((int) c.length()); Reader r = c.getCharacterStream(); char[] cbuf = new char[2048]; int n = 0; while ((n = r.read(cbuf, 0, cbuf.length)) != -1) { if (n > 0) { sb.append(cbuf, 0, n); } } return sb.toString(); }
/** * Converts a LOB to the equivalent Java type, i.e. <code>Clob</code> to <code>String</code> and * <code>Blob</code> to <code>byte[]</code>. If the value passed is not a LOB object, it is left * unchanged and no exception is thrown; the idea is to transparently convert only LOBs. * * @param value an object that may be a LOB * @return if the value was a LOB, the equivalent Java object, otherwise the original value * @throws SQLException if an error occurs while reading the LOB contents */ public static Object convertLOB(Object value) throws SQLException { if (value instanceof Clob) { Clob c = (Clob) value; return c.getSubString(1, (int) c.length()); } if (value instanceof Blob) { Blob b = (Blob) value; return b.getBytes(1, (int) b.length()); } return value; }
/** * Tests the exceptions thrown by the getCharacterStream (long pos, long length) for the following * conditions a) pos <= 0 b) pos > (length of LOB) c) length < 0 d) pos + length > (length of * LOB). * * @throws SQLException */ public void testGetCharacterStreamLongExceptionConditions() throws SQLException { String str1 = "This is a test String. This is a test String"; Reader r1 = new java.io.StringReader(str1); PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)"); int id = BlobClobTestSetup.getID(); ps.setInt(1, id); ps.setCharacterStream(2, r1); ps.execute(); ps.close(); Statement st = createStatement(); ResultSet rs = st.executeQuery("select CLOBDATA from " + "BLOBCLOB where ID=" + id); rs.next(); Clob clob = rs.getClob(1); // check the case where pos <= 0 try { // set pos as negative clob.getCharacterStream(-2L, 5L); // Should not come here. The exception has to be thrown. fail("FAIL: Expected SQLException for pos being negative " + "not thrown"); } catch (SQLException sqle) { // The SQLState for the exception thrown when pos <= 0 is XJ070 assertSQLState("XJ070", sqle); } // check for the case pos > length of clob try { // set the pos to any value greater than the Clob length clob.getCharacterStream(clob.length() + 1, 5L); // Should not come here. The exception has to be thrown. fail( "FAIL: Expected SQLException for position being greater than " + "length of LOB not thrown"); } catch (SQLException sqle) { // The SQLState for the exception thrown when pos > length of Clob // is XJ076 assertSQLState("XJ087", sqle); } // check for the case when length < 0 try { // set length as negative clob.getCharacterStream(2L, -5L); // Should not come here. The exception has to be thrown. fail("Fail: expected exception for the length being negative " + "not thrown"); } catch (SQLException sqle) { // The SQLState for the exception thrown when length < 0 of Clob // is XJ071 assertSQLState("XJ071", sqle); } // check for the case when pos + length > length of Clob try { // set pos + length > length of Clob clob.getCharacterStream((clob.length() - 4), 10L); // Should not come here. The exception has to be thrown. fail( "Fail: expected exception for the sum of position and length" + " being greater than the LOB size not thrown"); } catch (SQLException sqle) { // The SQLState for the exception thrown when length < 0 of Clob // is XJ087 assertSQLState("XJ087", sqle); } }
public void setStringDescr(Clob description) throws SQLException { this.stringDescr = description.getSubString(1L, (int) description.length()); }
/** * Embed the data object as a string literal in the buffer supplied. * * @param buf The buffer in which the data will be embedded. * @param value The data object. * @param isUnicode Set to <code>true</code> if Unicode strings should be used, else <code>false * </code>. * @param connection The {@link ConnectionJDBC2} object. */ static void embedData( StringBuffer buf, Object value, boolean isUnicode, ConnectionJDBC2 connection) throws SQLException { buf.append(' '); if (value == null) { buf.append("NULL "); return; } if (value instanceof Blob) { Blob blob = (Blob) value; value = blob.getBytes(1, (int) blob.length()); } else if (value instanceof Clob) { Clob clob = (Clob) value; value = clob.getSubString(1, (int) clob.length()); } if (value instanceof DateTime) { buf.append('\''); buf.append(value); buf.append('\''); } else if (value instanceof byte[]) { byte[] bytes = (byte[]) value; int len = bytes.length; if (len >= 0) { buf.append('0').append('x'); if (len == 0 && connection.getTdsVersion() < Driver.TDS70) { // Zero length binary values are not allowed buf.append('0').append('0'); } else { for (int i = 0; i < len; i++) { int b1 = bytes[i] & 0xFF; buf.append(hex[b1 >> 4]); buf.append(hex[b1 & 0x0F]); } } } } else if (value instanceof String) { String tmp = (String) value; int len = tmp.length(); if (isUnicode) { buf.append('N'); } buf.append('\''); for (int i = 0; i < len; i++) { char c = tmp.charAt(i); if (c == '\'') { buf.append('\''); } buf.append(c); } buf.append('\''); } else if (value instanceof java.sql.Date) { DateTime dt = new DateTime((java.sql.Date) value); buf.append('\''); buf.append(dt); buf.append('\''); } else if (value instanceof java.sql.Time) { DateTime dt = new DateTime((java.sql.Time) value); buf.append('\''); buf.append(dt); buf.append('\''); } else if (value instanceof java.sql.Timestamp) { DateTime dt = new DateTime((java.sql.Timestamp) value); buf.append('\''); buf.append(dt); buf.append('\''); } else if (value instanceof Boolean) { buf.append(((Boolean) value).booleanValue() ? '1' : '0'); } else if (value instanceof BigDecimal) { // // Ensure large decimal number does not overflow the // maximum precision of the server. // Main problem is with small numbers e.g. BigDecimal(1.0).toString() = // 0.1000000000000000055511151231.... // String tmp = value.toString(); int maxlen = connection.getMaxPrecision(); if (tmp.charAt(0) == '-') { maxlen++; } if (tmp.indexOf('.') >= 0) { maxlen++; } if (tmp.length() > maxlen) { buf.append(tmp.substring(0, maxlen)); } else { buf.append(tmp); } } else { buf.append(value.toString()); } buf.append(' '); }
/** * Convert an existing data object to the specified JDBC type. * * @param callerReference an object reference to the caller of this method; must be a <code> * Connection</code>, <code>Statement</code> or <code>ResultSet</code> * @param x the data object to convert * @param jdbcType the required type constant from <code>java.sql.Types</code> * @return the converted data object * @throws SQLException if the conversion is not supported or fails */ static Object convert(Object callerReference, Object x, int jdbcType, String charSet) throws SQLException { // handle null value if (x == null) { switch (jdbcType) { case java.sql.Types.BIT: case JtdsStatement.BOOLEAN: return Boolean.FALSE; case java.sql.Types.TINYINT: case java.sql.Types.SMALLINT: case java.sql.Types.INTEGER: return INTEGER_ZERO; case java.sql.Types.BIGINT: return LONG_ZERO; case java.sql.Types.REAL: return FLOAT_ZERO; case java.sql.Types.FLOAT: case java.sql.Types.DOUBLE: return DOUBLE_ZERO; default: return null; } } try { switch (jdbcType) { case java.sql.Types.TINYINT: if (x instanceof Boolean) { return ((Boolean) x).booleanValue() ? INTEGER_ONE : INTEGER_ZERO; } else if (x instanceof Byte) { return new Integer(((Byte) x).byteValue() & 0xFF); } else { long val; if (x instanceof Number) { val = ((Number) x).longValue(); } else if (x instanceof String) { val = new Long(((String) x).trim()).longValue(); } else { break; } if (val < Byte.MIN_VALUE || val > Byte.MAX_VALUE) { throw new SQLException( Messages.get("error.convert.numericoverflow", x, getJdbcTypeName(jdbcType)), "22003"); } else { return new Integer(new Long(val).intValue()); } } case java.sql.Types.SMALLINT: if (x instanceof Boolean) { return ((Boolean) x).booleanValue() ? INTEGER_ONE : INTEGER_ZERO; } else if (x instanceof Short) { return new Integer(((Short) x).shortValue()); } else if (x instanceof Byte) { return new Integer(((Byte) x).byteValue() & 0xFF); } else { long val; if (x instanceof Number) { val = ((Number) x).longValue(); } else if (x instanceof String) { val = new Long(((String) x).trim()).longValue(); } else { break; } if (val < Short.MIN_VALUE || val > Short.MAX_VALUE) { throw new SQLException( Messages.get("error.convert.numericoverflow", x, getJdbcTypeName(jdbcType)), "22003"); } else { return new Integer(new Long(val).intValue()); } } case java.sql.Types.INTEGER: if (x instanceof Integer) { return x; } else if (x instanceof Boolean) { return ((Boolean) x).booleanValue() ? INTEGER_ONE : INTEGER_ZERO; } else if (x instanceof Short) { return new Integer(((Short) x).shortValue()); } else if (x instanceof Byte) { return new Integer(((Byte) x).byteValue() & 0xFF); } else { long val; if (x instanceof Number) { val = ((Number) x).longValue(); } else if (x instanceof String) { val = new Long(((String) x).trim()).longValue(); } else { break; } if (val < Integer.MIN_VALUE || val > Integer.MAX_VALUE) { throw new SQLException( Messages.get("error.convert.numericoverflow", x, getJdbcTypeName(jdbcType)), "22003"); } else { return new Integer(new Long(val).intValue()); } } case java.sql.Types.BIGINT: if (x instanceof BigDecimal) { BigDecimal val = (BigDecimal) x; if (val.compareTo(MIN_VALUE_LONG_BD) < 0 || val.compareTo(MAX_VALUE_LONG_BD) > 0) { throw new SQLException( Messages.get("error.convert.numericoverflow", x, getJdbcTypeName(jdbcType)), "22003"); } else { return new Long(val.longValue()); } } else if (x instanceof Long) { return x; } else if (x instanceof Boolean) { return ((Boolean) x).booleanValue() ? LONG_ONE : LONG_ZERO; } else if (x instanceof Byte) { return new Long(((Byte) x).byteValue() & 0xFF); } else if (x instanceof BigInteger) { BigInteger val = (BigInteger) x; if (val.compareTo(MIN_VALUE_LONG_BI) < 0 || val.compareTo(MAX_VALUE_LONG_BI) > 0) { throw new SQLException( Messages.get("error.convert.numericoverflow", x, getJdbcTypeName(jdbcType)), "22003"); } else { return new Long(val.longValue()); } } else if (x instanceof Number) { return new Long(((Number) x).longValue()); } else if (x instanceof String) { return new Long(((String) x).trim()); } else { break; } case java.sql.Types.REAL: if (x instanceof Float) { return x; } else if (x instanceof Byte) { return new Float(((Byte) x).byteValue() & 0xFF); } else if (x instanceof Number) { return new Float(((Number) x).floatValue()); } else if (x instanceof String) { return new Float(((String) x).trim()); } else if (x instanceof Boolean) { return ((Boolean) x).booleanValue() ? FLOAT_ONE : FLOAT_ZERO; } break; case java.sql.Types.FLOAT: case java.sql.Types.DOUBLE: if (x instanceof Double) { return x; } else if (x instanceof Byte) { return new Double(((Byte) x).byteValue() & 0xFF); } else if (x instanceof Number) { return new Double(((Number) x).doubleValue()); } else if (x instanceof String) { return new Double(((String) x).trim()); } else if (x instanceof Boolean) { return ((Boolean) x).booleanValue() ? DOUBLE_ONE : DOUBLE_ZERO; } break; case java.sql.Types.NUMERIC: case java.sql.Types.DECIMAL: if (x instanceof BigDecimal) { return x; } else if (x instanceof Number) { return new BigDecimal(x.toString()); } else if (x instanceof String) { return new BigDecimal((String) x); } else if (x instanceof Boolean) { return ((Boolean) x).booleanValue() ? BIG_DECIMAL_ONE : BIG_DECIMAL_ZERO; } break; case java.sql.Types.VARCHAR: case java.sql.Types.CHAR: if (x instanceof String) { return x; } else if (x instanceof Number) { return x.toString(); } else if (x instanceof Boolean) { return ((Boolean) x).booleanValue() ? "1" : "0"; } else if (x instanceof Clob) { Clob clob = (Clob) x; long length = clob.length(); if (length > Integer.MAX_VALUE) { throw new SQLException(Messages.get("error.normalize.lobtoobig"), "22000"); } return clob.getSubString(1, (int) length); } else if (x instanceof Blob) { Blob blob = (Blob) x; long length = blob.length(); if (length > Integer.MAX_VALUE) { throw new SQLException(Messages.get("error.normalize.lobtoobig"), "22000"); } x = blob.getBytes(1, (int) length); } if (x instanceof byte[]) { return toHex((byte[]) x); } return x.toString(); // Last hope! case java.sql.Types.BIT: case JtdsStatement.BOOLEAN: if (x instanceof Boolean) { return x; } else if (x instanceof Number) { return (((Number) x).intValue() == 0) ? Boolean.FALSE : Boolean.TRUE; } else if (x instanceof String) { String tmp = ((String) x).trim(); return ("1".equals(tmp) || "true".equalsIgnoreCase(tmp)) ? Boolean.TRUE : Boolean.FALSE; } break; case java.sql.Types.VARBINARY: case java.sql.Types.BINARY: if (x instanceof byte[]) { return x; } else if (x instanceof Blob) { Blob blob = (Blob) x; return blob.getBytes(1, (int) blob.length()); } else if (x instanceof Clob) { Clob clob = (Clob) x; long length = clob.length(); if (length > Integer.MAX_VALUE) { throw new SQLException(Messages.get("error.normalize.lobtoobig"), "22000"); } x = clob.getSubString(1, (int) length); } if (x instanceof String) { // // Strictly speaking this conversion is not required by // the JDBC standard but jTDS has always supported it. // if (charSet == null) { charSet = "ISO-8859-1"; } try { return ((String) x).getBytes(charSet); } catch (UnsupportedEncodingException e) { return ((String) x).getBytes(); } } else if (x instanceof UniqueIdentifier) { return ((UniqueIdentifier) x).getBytes(); } break; case java.sql.Types.TIMESTAMP: if (x instanceof DateTime) { return ((DateTime) x).toTimestamp(); } else if (x instanceof java.sql.Timestamp) { return x; } else if (x instanceof java.sql.Date) { return new java.sql.Timestamp(((java.sql.Date) x).getTime()); } else if (x instanceof java.sql.Time) { return new java.sql.Timestamp(((java.sql.Time) x).getTime()); } else if (x instanceof java.lang.String) { return java.sql.Timestamp.valueOf(((String) x).trim()); } break; case java.sql.Types.DATE: if (x instanceof DateTime) { return ((DateTime) x).toDate(); } else if (x instanceof java.sql.Date) { return x; } else if (x instanceof java.sql.Time) { return DATE_ZERO; } else if (x instanceof java.sql.Timestamp) { GregorianCalendar cal = (GregorianCalendar) calendar.get(); cal.setTime((java.util.Date) x); cal.set(Calendar.HOUR_OF_DAY, 0); cal.set(Calendar.MINUTE, 0); cal.set(Calendar.SECOND, 0); cal.set(Calendar.MILLISECOND, 0); // VM1.4+ only return new java.sql.Date(cal.getTimeInMillis()); return new java.sql.Date(cal.getTime().getTime()); } else if (x instanceof java.lang.String) { return java.sql.Date.valueOf(((String) x).trim()); } break; case java.sql.Types.TIME: if (x instanceof DateTime) { return ((DateTime) x).toTime(); } else if (x instanceof java.sql.Time) { return x; } else if (x instanceof java.sql.Date) { return TIME_ZERO; } else if (x instanceof java.sql.Timestamp) { GregorianCalendar cal = (GregorianCalendar) calendar.get(); // VM 1.4+ only cal.setTimeInMillis(((java.sql.Timestamp)x).getTime()); cal.setTime((java.util.Date) x); cal.set(Calendar.YEAR, 1970); cal.set(Calendar.MONTH, 0); cal.set(Calendar.DAY_OF_MONTH, 1); // VM 1.4+ only return new java.sql.Time(cal.getTimeInMillis());*/ return new java.sql.Time(cal.getTime().getTime()); } else if (x instanceof java.lang.String) { return java.sql.Time.valueOf(((String) x).trim()); } break; case java.sql.Types.OTHER: return x; case java.sql.Types.JAVA_OBJECT: throw new SQLException( Messages.get( "error.convert.badtypes", x.getClass().getName(), getJdbcTypeName(jdbcType)), "22005"); case java.sql.Types.LONGVARBINARY: case java.sql.Types.BLOB: if (x instanceof Blob) { return x; } else if (x instanceof byte[]) { return new BlobImpl(getConnection(callerReference), (byte[]) x); } else if (x instanceof Clob) { // // Convert CLOB to BLOB. Not required by the standard but we will // do it anyway. // Clob clob = (Clob) x; try { if (charSet == null) { charSet = "ISO-8859-1"; } Reader rdr = clob.getCharacterStream(); BlobImpl blob = new BlobImpl(getConnection(callerReference)); BufferedWriter out = new BufferedWriter(new OutputStreamWriter(blob.setBinaryStream(1), charSet)); // TODO Use a buffer to improve performance int c; while ((c = rdr.read()) >= 0) { out.write(c); } out.close(); rdr.close(); return blob; } catch (UnsupportedEncodingException e) { // Unlikely to happen but fall back on in memory copy x = clob.getSubString(1, (int) clob.length()); } catch (IOException e) { throw new SQLException( Messages.get("error.generic.ioerror", e.getMessage()), "HY000"); } } if (x instanceof String) { // // Strictly speaking this conversion is also not required by // the JDBC standard but jTDS has always supported it. // BlobImpl blob = new BlobImpl(getConnection(callerReference)); String data = (String) x; if (charSet == null) { charSet = "ISO-8859-1"; } try { blob.setBytes(1, data.getBytes(charSet)); } catch (UnsupportedEncodingException e) { blob.setBytes(1, data.getBytes()); } return blob; } break; case java.sql.Types.LONGVARCHAR: case java.sql.Types.CLOB: if (x instanceof Clob) { return x; } else if (x instanceof Blob) { // // Convert BLOB to CLOB // Blob blob = (Blob) x; try { InputStream is = blob.getBinaryStream(); ClobImpl clob = new ClobImpl(getConnection(callerReference)); Writer out = clob.setCharacterStream(1); // TODO Use a buffer to improve performance int b; // These reads/writes are buffered by the underlying blob buffers while ((b = is.read()) >= 0) { out.write(hex[b >> 4]); out.write(hex[b & 0x0F]); } out.close(); is.close(); return clob; } catch (IOException e) { throw new SQLException( Messages.get("error.generic.ioerror", e.getMessage()), "HY000"); } } else if (x instanceof Boolean) { x = ((Boolean) x).booleanValue() ? "1" : "0"; } else if (!(x instanceof byte[])) { x = x.toString(); } if (x instanceof byte[]) { ClobImpl clob = new ClobImpl(getConnection(callerReference)); clob.setString(1, toHex((byte[]) x)); return clob; } else if (x instanceof String) { return new ClobImpl(getConnection(callerReference), (String) x); } break; default: throw new SQLException( Messages.get("error.convert.badtypeconst", getJdbcTypeName(jdbcType)), "HY004"); } throw new SQLException( Messages.get("error.convert.badtypes", x.getClass().getName(), getJdbcTypeName(jdbcType)), "22005"); } catch (NumberFormatException nfe) { throw new SQLException( Messages.get("error.convert.badnumber", getJdbcTypeName(jdbcType)), "22000"); } }
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; }