Example usage for java.sql ResultSet getClob

List of usage examples for java.sql ResultSet getClob

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. getClob

Introduction

In this page you can find the example usage for java.sql ResultSet getClob.

Prototype

Clob getClob(String columnLabel) throws SQLException;

Source Link

Document

Retrieves the value of the designated column in the current row of this ResultSet object as a Clob object in the Java programming language.

Usage

From source file:data.DefaultExchanger.java

protected void putClob(JsonGenerator generator, String fieldName, ResultSet rs, short index)
        throws SQLException, IOException {
    generator.writeFieldName(fieldName);
    String clobString = clobString(rs.getClob(index));
    if (clobString == null) {
        generator.writeNull();/*from   ww w.  j  av a 2s .  c om*/
    } else {
        generator.writeString(clobString);
    }
}

From source file:org.sakaiproject.mailarchive.impl.conversion.ExtractXMLToColumns.java

public Object getSource(String id, ResultSet rs) throws SQLException {
    ResultSetMetaData metadata = rs.getMetaData();
    String rv = null;// ww  w .j ava2s  .  co m
    switch (metadata.getColumnType(1)) {
    case Types.BLOB:
        Blob blob = rs.getBlob(1);
        if (blob != null) {
            rv = new String(blob.getBytes(1L, (int) blob.length()));
        }
        break;
    case Types.CLOB:
        Clob clob = rs.getClob(1);
        if (clob != null) {
            rv = clob.getSubString(1L, (int) clob.length());
        }
        break;
    case Types.CHAR:
    case Types.LONGVARCHAR:
    case Types.VARCHAR:
    case Types.BINARY:
    case Types.VARBINARY:
    case Types.LONGVARBINARY:
        byte[] bytes = rs.getBytes(1);
        if (bytes != null) {
            rv = new String(bytes);
        }
        break;
    }
    // System.out.println("getSource(" + id + ") \n" + rv + "\n");
    return rv;
}

From source file:org.sakaiproject.content.impl.serialize.impl.conversion.Type1BlobCollectionConversionHandler.java

public Object getSource(String id, ResultSet rs) throws SQLException {
    ResultSetMetaData metadata = rs.getMetaData();
    String rv = null;/*from w  w  w  .  ja  v  a  2  s . c  o m*/
    switch (metadata.getColumnType(1)) {
    case Types.BLOB:
        Blob blob = rs.getBlob(1);
        if (blob != null) {
            rv = new String(blob.getBytes(1L, (int) blob.length()));
        }
        break;
    case Types.CLOB:
        Clob clob = rs.getClob(1);
        if (clob != null) {
            rv = clob.getSubString(1L, (int) clob.length());
        }
        break;
    case Types.CHAR:
    case Types.LONGVARCHAR:
    case Types.VARCHAR:
    case Types.BINARY:
    case Types.VARBINARY:
    case Types.LONGVARBINARY:
        byte[] bytes = rs.getBytes(1);
        if (bytes != null) {
            rv = new String(bytes);
        }
        break;
    }
    //System.out.println("getSource(" + id + ") \n" + rv + "\n");
    return rv;
}

From source file:au.com.ish.derbydump.derbydump.metadata.Column.java

/**
 * Get a string value for the value in this column in the datarow
 * /*from  w  ww.java2  s .  c  om*/
 * @param dataRow The row which we are exporting
 * @return an SQL statement compliant string version of the value
 */
public String toString(ResultSet dataRow) throws SQLException {

    switch (getColumnDataType()) {
    case Types.BINARY:
    case Types.VARBINARY:
    case Types.BLOB: {
        Blob obj = dataRow.getBlob(columnName);
        return (obj == null) ? "NULL" : processBinaryData(obj);
    }

    case Types.CLOB: {
        Clob obj = dataRow.getClob(columnName);
        return (obj == null) ? "NULL" : processClobData(obj);
    }

    case Types.CHAR:
    case Types.LONGNVARCHAR:
    case Types.VARCHAR: {
        String obj = dataRow.getString(columnName);
        return (obj == null) ? "NULL" : processStringData(obj);
    }

    case Types.TIME: {
        Time obj = dataRow.getTime(columnName);
        return (obj == null) ? "NULL" : processStringData(obj.toString());
    }

    case Types.DATE: {
        Date obj = dataRow.getDate(columnName);
        return (obj == null) ? "NULL" : processStringData(obj.toString());
    }

    case Types.TIMESTAMP: {
        Timestamp obj = dataRow.getTimestamp(columnName);
        return (obj == null) ? "NULL" : processStringData(obj.toString());
    }

    case Types.SMALLINT: {
        Object obj = dataRow.getObject(columnName);
        return (obj == null) ? "NULL" : obj.toString();
    }

    case Types.BIGINT: {
        Object obj = dataRow.getObject(columnName);
        return (obj == null) ? "NULL" : obj.toString();
    }

    case Types.INTEGER: {
        Object obj = dataRow.getObject(columnName);
        return (obj == null) ? "NULL" : obj.toString();
    }

    case Types.NUMERIC:
    case Types.DECIMAL: {
        BigDecimal obj = dataRow.getBigDecimal(columnName);
        return (obj == null) ? "NULL" : String.valueOf(obj);
    }

    case Types.REAL:
    case Types.FLOAT: {
        Float obj = dataRow.getFloat(columnName);
        // dataRow.getFloat() always returns a value. only way to check the null is wasNull() method
        return (dataRow.wasNull()) ? "NULL" : String.valueOf(obj);
    }

    case Types.DOUBLE: {
        Double obj = dataRow.getDouble(columnName);
        return (dataRow.wasNull()) ? "NULL" : String.valueOf(obj);
    }

    default: {
        Object obj = dataRow.getObject(columnName);
        return (obj == null) ? "NULL" : obj.toString();
    }
    }
}

From source file:org.kawanfw.test.api.client.InsertAndUpdateClobTest.java

/**
 * Test that the blob was were correctly inserted
 * /*www .j  a v  a2s  .  c  o m*/
 * @param connection
 */
public void selectClobTestAlternateSyntax(Connection connection, String originalFileName, String shaHexa)
        throws Exception {

    int item_id;
    Clob clob;

    String sql = "select * from documentation where item_id >= ? ";

    PreparedStatement prepStatement = connection.prepareStatement(sql);

    int i = 1;
    prepStatement.setInt(i++, 1);

    ResultSet rs = prepStatement.executeQuery();

    MessageDisplayer.display("");

    Reader reader = null;
    Writer writer = null;

    while (rs.next()) {

        item_id = rs.getInt("item_id");

        i = 1;
        item_id = rs.getInt(i++);
        clob = rs.getClob(i++);

        File originalFile = SqlTestParms.getFileFromUserHome(originalFileName);
        // String extension = "."
        // + StringUtils.substringAfterLast(originalFile.toString(),
        // ".");

        File file = InsertAndUpdateBlobTest.createTempFile(originalFile.toString());

        try {
            reader = clob.getCharacterStream();

            if (reader != null) {
                writer = new BufferedWriter(new FileWriter(file));
                IOUtils.copy(reader, writer);

            } else {
                MessageDisplayer.display("item_doc column is null!");
            }
        } finally {
            IOUtils.closeQuietly(reader);
            IOUtils.closeQuietly(writer);

            try {
                clob.free();
            } catch (Throwable e) {
                MessageDisplayer.display("clob.free() not done: " + e.toString());
            }
        }

        MessageDisplayer.display("");
        MessageDisplayer.display("item_id     : " + item_id);

        // Compute the hash of the file
        Sha1Util sha1 = new Sha1Util();
        String shaHexaNew = sha1.getHexFileHash(file);

        Assert.assertEquals(shaHexa, shaHexaNew);

        file.delete();

        MessageDisplayer.display("");
        MessageDisplayer.display("Ok, SHA-1 value of read file " + file + " is same as inserted file "
                + SqlTestParms.getFileFromUserHome(originalFileName));

    }

    prepStatement.close();
    rs.close();

    MessageDisplayer.display("Select done!");

}

From source file:org.kawanfw.test.api.client.InsertAndUpdateClobTestAsciiStream.java

/**
 * Test that the blob was were correctly inserted
 * //w  w  w. j av a 2 s  . c o  m
 * @param connection
 */
public void selectClobTestAlternateSyntax(Connection connection, String originalFileName, String shaHexa)
        throws Exception {

    int item_id;
    Clob clob;

    String sql = "select * from documentation where item_id >= ? ";

    PreparedStatement prepStatement = connection.prepareStatement(sql);

    int i = 1;
    prepStatement.setInt(i++, 1);

    ResultSet rs = prepStatement.executeQuery();

    MessageDisplayer.display("");

    InputStream in = null;
    Writer writer = null;

    while (rs.next()) {

        item_id = rs.getInt("item_id");

        i = 1;
        item_id = rs.getInt(i++);
        clob = rs.getClob(i++);

        File originalFile = SqlTestParms.getFileFromUserHome(originalFileName);
        // String extension = "."
        // + StringUtils.substringAfterLast(originalFile.toString(),
        // ".");

        File file = InsertAndUpdateBlobTest.createTempFile(originalFile.toString());

        try {
            in = clob.getAsciiStream();

            if (in != null) {
                Reader reader = new InputStreamReader(in);
                writer = new BufferedWriter(new FileWriter(file));
                IOUtils.copy(reader, writer);
            } else {
                MessageDisplayer.display("item_doc column is null!");
            }

        } finally {
            IOUtils.closeQuietly(in);
            IOUtils.closeQuietly(writer);
            try {
                clob.free();
            } catch (Throwable e) {
                MessageDisplayer.display("clob.free() not done: " + e.toString());
            }
        }

        MessageDisplayer.display("");
        MessageDisplayer.display("item_id     : " + item_id);

        // Compute the hash of the file
        Sha1Util sha1 = new Sha1Util();
        String shaHexaNew = sha1.getHexFileHash(file);

        Assert.assertEquals(shaHexa, shaHexaNew);

        file.delete();

        MessageDisplayer.display("");
        MessageDisplayer.display("Ok, SHA-1 value of read file " + file + " is same as inserted file "
                + SqlTestParms.getFileFromUserHome(originalFileName));

    }

    prepStatement.close();
    rs.close();

    MessageDisplayer.display("Select done!");

}

From source file:org.sakaiproject.mailarchive.impl.conversion.ExtractXMLToColumns.java

public Object getValidateSource(String id, ResultSet rs) throws SQLException {
    ResultSetMetaData metadata = rs.getMetaData();
    byte[] rv = null;
    switch (metadata.getColumnType(1)) {
    case Types.BLOB:
        Blob blob = rs.getBlob(1);
        if (blob != null) {
            rv = blob.getBytes(1L, (int) blob.length());
        } else {/*from   w  w w  .  j  ava  2  s. co m*/
            System.out.println("getValidateSource(" + id + ") blob ==  null");
        }
        break;
    case Types.CLOB:
        Clob clob = rs.getClob(1);
        if (clob != null) {
            rv = clob.getSubString(1L, (int) clob.length()).getBytes();
        }
        break;
    case Types.CHAR:
    case Types.LONGVARCHAR:
    case Types.VARCHAR:
        rv = rs.getString(1).getBytes();
        break;
    case Types.BINARY:
    case Types.VARBINARY:
    case Types.LONGVARBINARY:
        rv = rs.getBytes(1);
        break;
    }
    // System.out.println("getValidateSource(" + id + ") \n" + rv + "\n");
    return rv;
}

From source file:org.springframework.jdbc.support.lob.OracleLobHandler.java

@Override
public String getClobAsString(ResultSet rs, int columnIndex) throws SQLException {
    logger.debug("Returning Oracle CLOB as string");
    Clob clob = rs.getClob(columnIndex);
    initializeResourcesBeforeRead(rs.getStatement().getConnection(), clob);
    String retVal = (clob != null ? clob.getSubString(1, (int) clob.length()) : null);
    releaseResourcesAfterRead(rs.getStatement().getConnection(), clob);
    return retVal;
}

From source file:org.springframework.jdbc.support.lob.OracleLobHandler.java

@Override
public InputStream getClobAsAsciiStream(ResultSet rs, int columnIndex) throws SQLException {
    logger.debug("Returning Oracle CLOB as ASCII stream");
    Clob clob = rs.getClob(columnIndex);
    initializeResourcesBeforeRead(rs.getStatement().getConnection(), clob);
    InputStream retVal = (clob != null ? clob.getAsciiStream() : null);
    releaseResourcesAfterRead(rs.getStatement().getConnection(), clob);
    return retVal;
}

From source file:org.apache.nifi.processors.standard.util.JdbcCommon.java

public static long convertToAvroStream(final ResultSet rs, final OutputStream outStream, String recordName,
        ResultSetRowCallback callback, final int maxRows, boolean convertNames)
        throws SQLException, IOException {
    final Schema schema = createSchema(rs, recordName, convertNames);
    final GenericRecord rec = new GenericData.Record(schema);

    final DatumWriter<GenericRecord> datumWriter = new GenericDatumWriter<>(schema);
    try (final DataFileWriter<GenericRecord> dataFileWriter = new DataFileWriter<>(datumWriter)) {
        dataFileWriter.create(schema, outStream);

        final ResultSetMetaData meta = rs.getMetaData();
        final int nrOfColumns = meta.getColumnCount();
        long nrOfRows = 0;
        while (rs.next()) {
            if (callback != null) {
                callback.processRow(rs);
            }//w ww  .j  a v  a 2 s . co m
            for (int i = 1; i <= nrOfColumns; i++) {
                final int javaSqlType = meta.getColumnType(i);

                // Need to handle CLOB and BLOB before getObject() is called, due to ResultSet's maximum portability statement
                if (javaSqlType == CLOB) {
                    Clob clob = rs.getClob(i);
                    if (clob != null) {
                        long numChars = clob.length();
                        char[] buffer = new char[(int) numChars];
                        InputStream is = clob.getAsciiStream();
                        int index = 0;
                        int c = is.read();
                        while (c > 0) {
                            buffer[index++] = (char) c;
                            c = is.read();
                        }
                        rec.put(i - 1, new String(buffer));
                        clob.free();
                    } else {
                        rec.put(i - 1, null);
                    }
                    continue;
                }

                if (javaSqlType == BLOB) {
                    Blob blob = rs.getBlob(i);
                    if (blob != null) {
                        long numChars = blob.length();
                        byte[] buffer = new byte[(int) numChars];
                        InputStream is = blob.getBinaryStream();
                        int index = 0;
                        int c = is.read();
                        while (c > 0) {
                            buffer[index++] = (byte) c;
                            c = is.read();
                        }
                        ByteBuffer bb = ByteBuffer.wrap(buffer);
                        rec.put(i - 1, bb);
                        blob.free();
                    } else {
                        rec.put(i - 1, null);
                    }
                    continue;
                }

                final Object value = rs.getObject(i);

                if (value == null) {
                    rec.put(i - 1, null);

                } else if (javaSqlType == BINARY || javaSqlType == VARBINARY || javaSqlType == LONGVARBINARY
                        || javaSqlType == ARRAY) {
                    // bytes requires little bit different handling
                    byte[] bytes = rs.getBytes(i);
                    ByteBuffer bb = ByteBuffer.wrap(bytes);
                    rec.put(i - 1, bb);

                } else if (value instanceof Byte) {
                    // tinyint(1) type is returned by JDBC driver as java.sql.Types.TINYINT
                    // But value is returned by JDBC as java.lang.Byte
                    // (at least H2 JDBC works this way)
                    // direct put to avro record results:
                    // org.apache.avro.AvroRuntimeException: Unknown datum type java.lang.Byte
                    rec.put(i - 1, ((Byte) value).intValue());
                } else if (value instanceof Short) {
                    //MS SQL returns TINYINT as a Java Short, which Avro doesn't understand.
                    rec.put(i - 1, ((Short) value).intValue());
                } else if (value instanceof BigDecimal) {
                    // Avro can't handle BigDecimal as a number - it will throw an AvroRuntimeException such as: "Unknown datum type: java.math.BigDecimal: 38"
                    rec.put(i - 1, value.toString());

                } else if (value instanceof BigInteger) {
                    // Check the precision of the BIGINT. Some databases allow arbitrary precision (> 19), but Avro won't handle that.
                    // It the SQL type is BIGINT and the precision is between 0 and 19 (inclusive); if so, the BigInteger is likely a
                    // long (and the schema says it will be), so try to get its value as a long.
                    // Otherwise, Avro can't handle BigInteger as a number - it will throw an AvroRuntimeException
                    // such as: "Unknown datum type: java.math.BigInteger: 38". In this case the schema is expecting a string.
                    if (javaSqlType == BIGINT) {
                        int precision = meta.getPrecision(i);
                        if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) {
                            rec.put(i - 1, value.toString());
                        } else {
                            try {
                                rec.put(i - 1, ((BigInteger) value).longValueExact());
                            } catch (ArithmeticException ae) {
                                // Since the value won't fit in a long, convert it to a string
                                rec.put(i - 1, value.toString());
                            }
                        }
                    } else {
                        rec.put(i - 1, value.toString());
                    }

                } else if (value instanceof Number || value instanceof Boolean) {
                    if (javaSqlType == BIGINT) {
                        int precision = meta.getPrecision(i);
                        if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) {
                            rec.put(i - 1, value.toString());
                        } else {
                            rec.put(i - 1, value);
                        }
                    } else {
                        rec.put(i - 1, value);
                    }

                } else {
                    // The different types that we support are numbers (int, long, double, float),
                    // as well as boolean values and Strings. Since Avro doesn't provide
                    // timestamp types, we want to convert those to Strings. So we will cast anything other
                    // than numbers or booleans to strings by using the toString() method.
                    rec.put(i - 1, value.toString());
                }
            }
            dataFileWriter.append(rec);
            nrOfRows += 1;

            if (maxRows > 0 && nrOfRows == maxRows)
                break;
        }

        return nrOfRows;
    }
}