Example usage for java.sql ResultSet getClob

List of usage examples for java.sql ResultSet 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:Main.java

public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "yourName", "mypwd");

    Statement stmt = conn.createStatement();

    createBlobClobTables(stmt);//from   w ww .jav a  2 s  .  c  o  m

    PreparedStatement pstmt = conn.prepareStatement("INSERT INTO BlobClob VALUES(40,?,?)");

    File file = new File("blob.txt");
    FileInputStream fis = new FileInputStream(file);
    pstmt.setBinaryStream(1, fis, (int) file.length());

    file = new File("clob.txt");
    fis = new FileInputStream(file);
    pstmt.setAsciiStream(2, fis, (int) file.length());
    fis.close();

    pstmt.execute();

    ResultSet rs = stmt.executeQuery("SELECT * FROM BlobClob WHERE id = 40");
    rs.next();

    java.sql.Blob blob = rs.getBlob(2);
    java.sql.Clob clob = rs.getClob(3);

    byte blobVal[] = new byte[(int) blob.length()];
    InputStream blobIs = blob.getBinaryStream();
    blobIs.read(blobVal);
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    bos.write(blobVal);
    blobIs.close();

    char clobVal[] = new char[(int) clob.length()];
    Reader r = clob.getCharacterStream();
    r.read(clobVal);
    StringWriter sw = new StringWriter();
    sw.write(clobVal);

    r.close();
    conn.close();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "yourName", "mypwd");

    Statement stmt = conn.createStatement();

    createBlobClobTables(stmt);//from ww w  .  ja v  a 2  s  .  co m

    PreparedStatement pstmt = conn.prepareStatement("INSERT INTO BlobClob VALUES(40,?,?)");

    File file = new File("blob.txt");
    FileInputStream fis = new FileInputStream(file);
    pstmt.setBinaryStream(1, fis, (int) file.length());

    file = new File("clob.txt");
    fis = new FileInputStream(file);
    pstmt.setAsciiStream(2, fis, (int) file.length());
    fis.close();

    pstmt.execute();

    ResultSet rs = stmt.executeQuery("SELECT * FROM BlobClob WHERE id = 40");
    rs.next();

    java.sql.Blob blob = rs.getBlob(2);
    java.sql.Clob clob = rs.getClob("myClobColumn");

    byte blobVal[] = new byte[(int) blob.length()];
    InputStream blobIs = blob.getBinaryStream();
    blobIs.read(blobVal);
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    bos.write(blobVal);
    blobIs.close();

    char clobVal[] = new char[(int) clob.length()];
    Reader r = clob.getCharacterStream();
    r.read(clobVal);
    StringWriter sw = new StringWriter();
    sw.write(clobVal);

    r.close();
    conn.close();
}

From source file:org.etudes.jforum.dao.oracle.OracleUtils.java

/**
 * Writes clob to database // w  w w . j a  v  a  2 s.  c o  m
 * @param query
 * @param idForQuery
 * @param value
 * @throws IOException
 * @throws SQLException
 */
public static void writeClobUTF16BinaryStream(String query, int idForQuery, String value)
        throws IOException, SQLException {
    PreparedStatement p = JForum.getConnection().prepareStatement(query);
    p.setInt(1, idForQuery);

    ResultSet rs = p.executeQuery();
    rs.next();
    Clob clobval = rs.getClob(1);

    if (logger.isDebugEnabled())
        logger.debug("clobval is " + clobval.getClass().getName());

    Writer clobWriter = clobval.setCharacterStream(0L);

    char[] cbuffer = new char[value.length()];
    int srcBegin = 0, dstBegin = 0;
    value.getChars(srcBegin, value.length(), cbuffer, dstBegin);
    clobWriter.write(cbuffer);

    clobWriter.close();

    rs.close();
    p.close();
}

From source file:org.etudes.jforum.dao.oracle.OracleUtils.java

/**
 * read clob into a string/*from www  . ja va2s  . com*/
 * @param rs
 * @param fieldName
 * @return
 * @throws IOException
 * @throws SQLException
 */
public static String readClobUTF16BinaryStream(ResultSet rs, String fieldName)
        throws IOException, SQLException {
    Clob clob = rs.getClob(fieldName);

    Reader clobStream = clob.getCharacterStream();
    StringBuffer clobData = new StringBuffer();

    // Read from the Clob stream and write to the stringbuffer
    int nchars = 0;
    char[] buffer = new char[4096];
    while ((nchars = clobStream.read(buffer)) != -1)
        clobData.append(buffer, 0, nchars);

    clobStream.close();

    return clobData.toString();
}

From source file:Main.java

public static String getCLOB(int id) throws Exception {
    Connection conn = null;//from  ww  w.j ava  2  s .c  o m
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    String query = "SELECT clobData FROM tableName WHERE id = ?";

    try {
        conn = getConnection();
        pstmt = conn.prepareStatement(query);
        pstmt.setInt(1, id);
        rs = pstmt.executeQuery();
        rs.next();
        Clob clob = rs.getClob(1);
        // materialize CLOB onto client
        String wholeClob = clob.getSubString(1, (int) clob.length());
        return wholeClob;
    } finally {
        rs.close();
        pstmt.close();
        conn.close();
    }
}

From source file:org.jumpmind.symmetric.db.derby.DerbyFunctions.java

public static String clobToString(String columnName, String tableName, String whereClause) throws SQLException {
    String str = null;//w  w  w. jav a 2 s.  c  o m
    if (StringUtils.isNotBlank(whereClause)) {
        Connection conn = DriverManager.getConnection(CURRENT_CONNECTION_URL);
        String sql = "select " + columnName + " from " + tableName + " where " + whereClause;
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            Clob clob = rs.getClob(1);
            if (clob != null) {
                str = clob.getSubString(1, MAX_STRING_LENGTH);
            }
        }
        ps.close();
        conn.close();
    }
    return str == null ? "" : escape(str);
}

From source file:com.afforess.nsdump.Test.java

public static void testNationDump() throws IOException, SQLException {
    NationsDump dump = new NationsDump();
    dump.parse();/*from  w ww  .  jav  a2  s  .  com*/

    Connection conn = dump.getDatabaseConnection();
    PreparedStatement statement = conn.prepareStatement("SELECT (name) FROM nations");
    ResultSet result = statement.executeQuery();
    int total = 0;
    while (result.next()) {
        total++;
    }
    result.close();

    System.out.println("Total nations: " + total);

    statement = conn.prepareStatement("SELECT * FROM nations WHERE name = 'sakhovelo'");
    result = statement.executeQuery();
    result.next();
    for (int i = 1; i <= 10; i++) {
        if (i == 4) {
            Clob clob = result.getClob(i);
            String motto = clob.getSubString(1, (int) clob.length());
            String mottoEscaped = StringEscapeUtils.unescapeHtml(motto);
            System.out.println("Raw: " + motto + " Escaped: " + mottoEscaped);
        } else {
            System.out.println(result.getString(i));
        }
    }

    File db = new File("./ns-db.h2.db");
    db.delete();
}

From source file:org.apache.cocoon.util.JDBCTypeConversions.java

/**
 * Get the Statement column so that the results are mapped correctly.
 * (this has been copied from AbstractDatabaseAction and modified slightly)
 *//*w w  w  .j  a  v  a  2 s. c  o m*/
public static Object getColumn(ResultSet set, Configuration column) throws Exception {

    Integer type = (Integer) JDBCTypeConversions.typeConstants.get(column.getAttribute("type"));
    String dbcol = column.getAttribute("name");
    Object value = null;

    switch (type.intValue()) {
    case Types.CLOB:
    case Types.CHAR:
        Clob dbClob = set.getClob(dbcol);
        int length = (int) dbClob.length();
        InputStream asciiStream = new BufferedInputStream(dbClob.getAsciiStream());
        byte[] buffer = new byte[length];
        asciiStream.read(buffer);
        String str = new String(buffer);
        asciiStream.close();
        value = str;
        break;
    case Types.BIGINT:
        value = set.getBigDecimal(dbcol);
        break;
    case Types.TINYINT:
        value = new Byte(set.getByte(dbcol));
        break;
    case Types.VARCHAR:
        value = set.getString(dbcol);
        break;
    case Types.DATE:
        value = set.getDate(dbcol);
        break;
    case Types.DOUBLE:
        value = new Double(set.getDouble(dbcol));
        break;
    case Types.FLOAT:
        value = new Float(set.getFloat(dbcol));
        break;
    case Types.INTEGER:
        value = new Integer(set.getInt(dbcol));
        break;
    case Types.NUMERIC:
        value = new Long(set.getLong(dbcol));
        break;
    case Types.SMALLINT:
        value = new Short(set.getShort(dbcol));
        break;
    case Types.TIME:
        value = set.getTime(dbcol);
        break;
    case Types.TIMESTAMP:
        value = set.getTimestamp(dbcol);
        break;
    case Types.ARRAY:
        value = set.getArray(dbcol); // new Integer(set.getInt(dbcol));
        break;
    case Types.BIT:
        value = BooleanUtils.toBooleanObject(set.getBoolean(dbcol));
        break;
    case Types.STRUCT:
        value = (Struct) set.getObject(dbcol);
        break;
    case Types.OTHER:
        value = set.getObject(dbcol);
        break;

    default:
        // The blob types have to be requested separately, via a Reader.
        value = "";
        break;
    }

    return value;
}

From source file:uk.ac.ebi.orchem.search.SimilaritySearch.java

/**
 * Performs a similarity search between a query molecule and the orchem fingerprint table.
 *
 * @param queryFp fingerprint of the query molecule
 * @param _cutOff tanimoto score below which to stop searching
 * @param _topN top N results after which to stop searching
 * @param debugYN Y or N to debug output back
 * @param idsOnlyYN Y or N to indicate to just return IDs of results (faster)
 * @param extraWhereClause option to include an extra SQL where clause refering to the base compound table
 * @return array of {@link uk.ac.ebi.orchem.bean.OrChemCompound compounds}
 * @throws Exception//  w w  w. j a v  a2s  . c  om
 */
private static oracle.sql.ARRAY search(BitSet queryFp, Float _cutOff, Integer _topN, String debugYN,
        String idsOnlyYN, String extraWhereClause) throws Exception {

    /*
     * 
    The comment block below describes the search algorithm.
    From:
     "Bounds and Algorithms for Fast Exact Searches of Chemical Fingerprints in Linear and Sub-Linear Time"
      S.Joshua Swamidass and Pierre Baldi
      http://dx.doi.org/10.1021/ci600358f
            
     Top K Hits
     ----------
     We can search for the top K hits by starting from the maximum (where A=B), and exploring discrete possible
     values of B right and left of the maximum.
            
     More precisely, for binary fingerprints, we first
     index the molecules in the database by their fingerprint "bit count"
     to enable efficient referencing
     of a particular bit count bin.
            
     Next, with respect to a particular query, we calculate the bound
     on the similarity for every bit count in the database.
            
     Then we sort these bit counts by their associated bound and iterate over the
     molecules in the database, in order of decreasing bound.
            
     As we iterate, we calculate the similarity between the query and the database molecule and use
     a heap to efficiently track the top hits. The algorithm terminates when
     "the lowest similarity value in the heap is greater than the bound associated with the current database bin"
            
     Algorithm 1 Top K Search
     Require: database of fingerprints binned by bit count Bs
     Ensure: hits contains top K hits which satisfy SIMILARITY( ) > T
            
     1:  hits <- MINHEAP()
     2:  bounds <- LIST()
     3:  for all B in database do //iterate over bins
     4:    tuple <- TUPLE(BOUND(A,B),B)
     5:    LISTAPPEND(bounds, tuple)
     6:  end for
     7:  QUICKSORT(bounds) //NOTE: the length of bounds is constant
     8:  for all bound, B in bounds do //iterate in order of decreasing bound
     9:    if bound < T then
     10:      break //threshold stopping condition
     11:   end if
     12:   if K  HEAPSIZE(hits) and bound < MINSIMILARITY(hits) then
     13:     break //top-K stopping condition
     14:   end if
     15:   for all in database[B] do
     16:     S=SIMILARITY( )
     17:     tuple <- TUPLE(S, )
     18:     if S  T then
     19:        continue //ignore this and continue to next
     20:     else if LENGTH(hits)< K then
     21:        HEAPPUSH(hits, tuple)
     22:     else if S > MINSIMILARITY(hits) then
     23:       HEAPPOPMIN(hits)
     24:       HEAPPUSH(hits,tuple)
     25:     end if
     26:   end for
     27: end for
     28: return hits
     */

    boolean debugging = false;
    if (debugYN.toLowerCase().equals("y"))
        debugging = true;

    debug("started", debugging);

    /**********************************************************************
     * Similarity search algorithm section                                *
     *                                                                    *
     **********************************************************************/
    Comparator heapComparator = new SimHeapElementTanimComparator();
    PriorityBuffer heap = null;
    OracleConnection conn = null;
    PreparedStatement pstmtFp = null;
    PreparedStatement pstmLookup = null;

    String query = " select bit_count, id, fp from orchem_fingprint_simsearch s where  bit_count = ? ";

    float cutOff = _cutOff.floatValue();
    int topN = -1;
    if (_topN == null) {
        debug("No topN breakout specified.. searching until lower bound reached", debugging);
    } else {
        topN = _topN.intValue();
        debug("topN is " + topN + ", result set size limited.", debugging);
    }

    try {
        conn = (OracleConnection) new OracleDriver().defaultConnection();

        String compoundTableName = OrChemParameters.getParameterValue(OrChemParameters.COMPOUND_TABLE, conn);
        String compoundTablePkColumn = OrChemParameters.getParameterValue(OrChemParameters.COMPOUND_PK, conn);
        String compoundTableMolfileColumn = OrChemParameters.getParameterValue(OrChemParameters.COMPOUND_MOL,
                conn);

        if (extraWhereClause != null) {
            query = " select s.bit_count, s.id, s.fp from " + " orchem_fingprint_simsearch s , "
                    + compoundTableName + " c " + " where  s.bit_count = ? " + " and s.id = c."
                    + compoundTablePkColumn + " " + " and " + extraWhereClause;
            debug("QUERY is " + query, debugging);
        }

        float queryBitCount = queryFp.cardinality();
        byte[] queryBytes = Utils.toByteArray(queryFp, extFpSize);
        int queryByteArrLen = queryBytes.length;

        float lowBucketNum = queryBitCount - 1;
        float highBucketNum = queryBitCount + 1;
        float currBucketNum = queryBitCount;

        pstmtFp = conn.prepareStatement(query);
        pstmtFp.setFetchSize(250);

        ResultSet resFp = null;
        boolean done = false;
        byte[] dbByteArray = null;
        float tanimotoCoeff = 0f;
        heap = new PriorityBuffer(true, heapComparator);
        int bucksSearched = 0;
        int loopCount = 0;

        while (!done) {
            debug("bucket is " + currBucketNum, debugging);
            loopCount++;
            pstmtFp.setFloat(1, currBucketNum);
            bucksSearched++;
            resFp = pstmtFp.executeQuery();

            float bound = 0f;
            if (currBucketNum < queryBitCount)
                bound = currBucketNum / queryBitCount;
            else
                bound = queryBitCount / currBucketNum;

            /* Algorithm step 9..11
               Here we can break out because the tanimoto score is becoming to low */
            if (bound < cutOff) {
                debug("bound < cutOff, done", debugging);
                done = true;
            }

            if (!done) {
                //Algorithm 15-26
                while (resFp.next()) {
                    dbByteArray = resFp.getBytes("fp");
                    tanimotoCoeff = calcTanimoto(queryBytes, queryByteArrLen, dbByteArray, queryBitCount,
                            currBucketNum);

                    if (tanimotoCoeff >= cutOff) {
                        SimHeapElement elm = new SimHeapElement();
                        elm.setID(resFp.getString("id"));
                        elm.setTanimotoCoeff(new Float(tanimotoCoeff));

                        if (heap.size() < topN || topN == -1) {
                            heap.add(elm);
                            debug("add elem " + elm.getID(), debugging);

                        } else if (tanimotoCoeff > ((SimHeapElement) (heap.get())).getTanimotoCoeff()
                                .floatValue()) {
                            heap.remove();
                            heap.add(elm);
                            debug("remove + add elem " + elm.getID(), debugging);
                        }
                    }
                }
                resFp.close();
                /* Algorithm 12-14:
                 * When top N hits is reached, and the lowest score of the
                 * hits is greater than the current bucket bound, stop.
                 * If not, the next bucket may contain a better score, so go on.
                 */

                if (topN != -1 && heap.size() >= topN
                        && ((SimHeapElement) (heap.get())).getTanimotoCoeff().floatValue() > bound) {
                    done = true;
                    debug("topN reached, done", debugging);

                } else {
                    // calculate new currBucket
                    float up = queryBitCount / highBucketNum;
                    float down = lowBucketNum / queryBitCount;

                    if (up > down) {
                        currBucketNum = highBucketNum;
                        highBucketNum++;
                    } else {
                        currBucketNum = lowBucketNum;
                        lowBucketNum--;
                    }

                    if (lowBucketNum < 1 && highBucketNum > extFpSize)
                        done = true;
                }
            }
        }
        debug("searched bit_count buckets: " + loopCount, debugging);

        /********************************************************************
         * Search completed.                                                *
         *                                                                  *
         * Next section is just looking up the compounds by ID and          *
         * returning the results, sorted by Tanimoto coefficient            *
         *                                                                  *
         *******************************************************************/
        String lookupCompoundQuery = " select " + compoundTableMolfileColumn + " from " + " "
                + compoundTableName + " where " + " " + compoundTablePkColumn + " =?";

        pstmLookup = conn.prepareStatement(lookupCompoundQuery);
        List compounds = new ArrayList();

        while (heap.size() != 0) {
            SimHeapElement bElm = (SimHeapElement) heap.remove();

            if (idsOnlyYN.equals("N")) {
                // return structure to user
                pstmLookup.setString(1, bElm.getID());
                ResultSet resLookup = pstmLookup.executeQuery();
                if (resLookup.next()) {
                    OrChemCompound c = new OrChemCompound();
                    c.setId(bElm.getID());
                    c.setScore(bElm.getTanimotoCoeff().floatValue());
                    c.setMolFileClob(resLookup.getClob(compoundTableMolfileColumn));
                    compounds.add(c);
                }
                resLookup.close();
            } else {
                // only return ID and score to user
                OrChemCompound c = new OrChemCompound();
                c.setId(bElm.getID());
                c.setScore(bElm.getTanimotoCoeff().floatValue());
                compounds.add(c);
            }
        }
        pstmLookup.close();
        long befSort = System.currentTimeMillis();
        Collections.sort(compounds, new OrChemCompoundTanimComparator());
        debug("sorting time (ms) " + (System.currentTimeMillis() - befSort), debugging);

        OrChemCompound[] output = new OrChemCompound[compounds.size()];
        for (int i = 0; i < compounds.size(); i++) {
            output[i] = (OrChemCompound) (compounds.get(i));
        }
        ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("ORCHEM_COMPOUND_LIST", conn);
        debug("#compounds in result list : " + compounds.size(), debugging);
        debug("ended", debugging);
        return new ARRAY(arrayDescriptor, conn, output);
    } catch (Exception ex) {
        ex.printStackTrace();
        throw (ex);
    } finally {
        if (pstmLookup != null)
            pstmLookup.close();
        if (pstmtFp != null)
            pstmtFp.close();
        if (conn != null)
            conn.close();
    }
}

From source file:org.onesun.atomator.test.dao.AbderaEntryDAOImpl.java

private Entry newAbderaEntry(ResultSet resultSet) throws SQLException {
    Clob clob = resultSet.getClob("entry");
    if (clob != null) {
        String entryText = SQLUtils.clobToString(clob);

        try {//from   w w w .  j ava 2s  . co m
            return AbderaUtils.toAbderaEntry(entryText);
        } catch (ParseException e) {
            logger.error("ParseException while creating entry from text " + e.getMessage());
        }
    }

    return null;
}