Example #1
1
  @SuppressWarnings("unchecked")
  public static final List<Record> build(Config config, ResultSet rs) throws SQLException {
    List<Record> result = new ArrayList<Record>();
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();
    String[] labelNames = new String[columnCount + 1];
    int[] types = new int[columnCount + 1];
    buildLabelNamesAndTypes(rsmd, labelNames, types);
    while (rs.next()) {
      Record record = new Record();
      record.setColumnsMap(config.containerFactory.getColumnsMap());
      Map<String, Object> columns = record.getColumns();
      for (int i = 1; i <= columnCount; i++) {
        Object value;
        if (types[i] < Types.BLOB) value = rs.getObject(i);
        else if (types[i] == Types.CLOB) value = ModelBuilder.handleClob(rs.getClob(i));
        else if (types[i] == Types.NCLOB) value = ModelBuilder.handleClob(rs.getNClob(i));
        else if (types[i] == Types.BLOB) value = ModelBuilder.handleBlob(rs.getBlob(i));
        else value = rs.getObject(i);

        columns.put(labelNames[i], value);
      }
      result.add(record);
    }
    return result;
  }
 @Override
 public NClob getNClob(String columnLabel) throws SQLException {
   try {
     return _res.getNClob(columnLabel);
   } catch (SQLException e) {
     handleException(e);
     return null;
   }
 }
 @Override
 public NClob getNClob(int columnIndex) throws SQLException {
   try {
     return _res.getNClob(columnIndex);
   } catch (SQLException e) {
     handleException(e);
     return null;
   }
 }
  private void testInsertRowWithUpdatableResultSetDefault() throws Exception {
    stat.execute("create table test(id int primary key, " + "data varchar(255) default 'Hello')");
    PreparedStatement prep =
        conn.prepareStatement(
            "select * from test", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = prep.executeQuery();
    rs.moveToInsertRow();
    rs.updateInt(1, 1);
    rs.insertRow();
    rs.close();
    rs = stat.executeQuery("select * from test");
    assertTrue(rs.next());
    assertEquals("Hello", rs.getString(2));
    assertEquals("Hello", rs.getString("data"));
    assertEquals("Hello", rs.getNString(2));
    assertEquals("Hello", rs.getNString("data"));
    assertEquals("Hello", IOUtils.readStringAndClose(rs.getNCharacterStream(2), -1));
    assertEquals("Hello", IOUtils.readStringAndClose(rs.getNCharacterStream("data"), -1));
    assertEquals("Hello", IOUtils.readStringAndClose(rs.getNClob(2).getCharacterStream(), -1));
    assertEquals("Hello", IOUtils.readStringAndClose(rs.getNClob("data").getCharacterStream(), -1));

    rs = prep.executeQuery();

    rs.moveToInsertRow();
    rs.updateInt(1, 2);
    rs.updateNString(2, "Hello");
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt(1, 3);
    rs.updateNString("data", "Hello");
    rs.insertRow();

    Clob c;
    Writer w;

    rs.moveToInsertRow();
    rs.updateInt(1, 4);
    c = conn.createClob();
    w = c.setCharacterStream(1);
    w.write("Hello");
    w.close();
    rs.updateClob(2, c);
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt(1, 5);
    c = conn.createClob();
    w = c.setCharacterStream(1);
    w.write("Hello");
    w.close();
    rs.updateClob("data", c);
    rs.insertRow();

    InputStream in;

    rs.moveToInsertRow();
    rs.updateInt(1, 6);
    in = new ByteArrayInputStream("Hello".getBytes("UTF-8"));
    rs.updateAsciiStream(2, in);
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt(1, 7);
    in = new ByteArrayInputStream("Hello".getBytes("UTF-8"));
    rs.updateAsciiStream("data", in);
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt(1, 8);
    in = new ByteArrayInputStream("Hello-".getBytes("UTF-8"));
    rs.updateAsciiStream(2, in, 5);
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt(1, 9);
    in = new ByteArrayInputStream("Hello-".getBytes("UTF-8"));
    rs.updateAsciiStream("data", in, 5);
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt(1, 10);
    in = new ByteArrayInputStream("Hello-".getBytes("UTF-8"));
    rs.updateAsciiStream(2, in, 5L);
    rs.insertRow();

    rs.moveToInsertRow();
    rs.updateInt(1, 11);
    in = new ByteArrayInputStream("Hello-".getBytes("UTF-8"));
    rs.updateAsciiStream("data", in, 5L);
    rs.insertRow();

    rs = stat.executeQuery("select * from test");
    while (rs.next()) {
      assertEquals("Hello", rs.getString(2));
    }

    stat.execute("drop table test");
  }
 @Override
 public NClob getValue(ResultSet resultSet, int index, Field field, Map<String, Object> options)
     throws SQLException {
   return resultSet.getNClob(index);
 }
Example #6
0
  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;
  }