Example usage for java.sql Clob getSubString

List of usage examples for java.sql Clob getSubString

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Clob
  6. getSubString

Introduction

In this page you can find the example usage for java.sql Clob getSubString.

Prototype

String getSubString(long pos, int length) throws SQLException;

Source Link

Document

Retrieves a copy of the specified substring in the CLOB value designated by this Clob object.

Usage

From source file:DisplayClobServlet.java

public void doGet(HttpServletRequest request, HttpServletResponse response)
        throws IOException, ServletException {

    Clob fileAsCLOB = null;
    Connection conn = null;/* w  w w. j  a v a  2s  .com*/
    Statement stmt = null;
    ResultSet rs = null;

    String id = "001";
    String query = "select fileBody from DataFiles where id = " + id;
    ServletOutputStream out = response.getOutputStream();

    // all responses will be in text/html format
    response.setContentType("text/html");

    try {
        conn = getHSQLConnection();
    } catch (Exception e) {
        out.println("<html><head><title>CLOB Example</title></head>");
        out.println("<body><h4>Database Connection Problem.</h4>");
        out.println("<h5>" + e.getMessage() + "</h5></body></html>");
        return;
    }

    try {
        stmt = conn.createStatement();
        rs = stmt.executeQuery(query);
        if (rs.next()) {
            fileAsCLOB = rs.getClob(1);
        } else {
            out.println("<html><head><title>CLOB Example</title></head>");
            out.println("<body><h4>No file found for id=" + id + "</h4></body></html>");
            return;
        }

        // Materialize the CLOB as a String object (get the whole clob).
        long length = fileAsCLOB.length();
        // note that the first character is at position 1
        String fileAsString = fileAsCLOB.getSubString(1, (int) length);

        // write it for display
        out.println(fileAsString);
        System.out.println("CLOB writing done.");
    } catch (SQLException e) {
        out.println("<html><head><title>Error: CLOB Example</title></head>");
        out.println("<body><h4>Error=" + e.getMessage() + "</h4></body></html>");
        return;
    } finally {
        try {
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

From source file:dk.netarkivet.harvester.datamodel.JobDBDAO.java

/** Read a single job from the job database.
 *
 * @param jobID ID of the job./*from   www. j av  a2s . co m*/
 * @param connection an open connection to the harvestDatabase
 * @return A Job object
 * @throws UnknownID if the job id does not exist.
 * @throws IOFailure if there was some problem talking to the database.
 */
private synchronized Job read(Connection connection, Long jobID) {
    if (!exists(connection, jobID)) {
        throw new UnknownID("Job id " + jobID + " is not known in persistent storage");
    }
    PreparedStatement statement = null;
    try {
        statement = connection.prepareStatement("SELECT " + "harvest_id, status, channel, "
                + "forcemaxcount, forcemaxbytes, " + "forcemaxrunningtime, orderxml, "
                + "orderxmldoc, seedlist, harvest_num," + "harvest_errors, harvest_error_details, "
                + "upload_errors, upload_error_details, " + "startdate, enddate, submitteddate, creationdate, "
                + "edition, resubmitted_as_job, continuationof, harvestname_prefix, snapshot "
                + "FROM jobs WHERE job_id = ?");
        statement.setLong(1, jobID);
        ResultSet result = statement.executeQuery();
        result.next();
        long harvestID = result.getLong(1);
        JobStatus status = JobStatus.fromOrdinal(result.getInt(2));
        String channel = result.getString(3);
        long forceMaxCount = result.getLong(4);
        long forceMaxBytes = result.getLong(5);
        long forceMaxRunningTime = result.getLong(6);
        String orderxml = result.getString(7);

        Document orderXMLdoc = null;

        boolean useClobs = DBSpecifics.getInstance().supportsClob();
        if (useClobs) {
            Clob clob = result.getClob(8);
            orderXMLdoc = getOrderXMLdocFromClob(clob);
        } else {
            orderXMLdoc = XmlUtils.documentFromString(result.getString(8));
        }
        String seedlist = "";
        if (useClobs) {
            Clob clob = result.getClob(9);
            seedlist = clob.getSubString(1, (int) clob.length());
        } else {
            seedlist = result.getString(9);
        }

        int harvestNum = result.getInt(10);
        String harvestErrors = result.getString(11);
        String harvestErrorDetails = result.getString(12);
        String uploadErrors = result.getString(13);
        String uploadErrorDetails = result.getString(14);
        Date startdate = DBUtils.getDateMaybeNull(result, 15);
        Date stopdate = DBUtils.getDateMaybeNull(result, 16);
        Date submittedDate = DBUtils.getDateMaybeNull(result, 17);
        Date creationDate = DBUtils.getDateMaybeNull(result, 18);
        Long edition = result.getLong(19);
        Long resubmittedAsJob = DBUtils.getLongMaybeNull(result, 20);
        Long continuationOfJob = DBUtils.getLongMaybeNull(result, 21);
        String harvestnamePrefix = result.getString(22);
        boolean snapshot = result.getBoolean(23);
        statement.close();
        // IDs should match up in a natural join
        // The following if-block is an attempt to fix Bug 1856, an
        // unexplained derby deadlock, by making this statement a dirty
        // read.
        String domainStatement = "SELECT domains.name, configurations.name "
                + "FROM domains, configurations, job_configs " + "WHERE job_configs.job_id = ?"
                + "  AND job_configs.config_id = configurations.config_id"
                + "  AND domains.domain_id = configurations.domain_id";
        if (Settings.get(CommonSettings.DB_SPECIFICS_CLASS).contains(CommonSettings.DB_IS_DERBY_IF_CONTAINS)) {
            statement = connection.prepareStatement(domainStatement + " WITH UR");
        } else {
            statement = connection.prepareStatement(domainStatement);
        }
        statement.setLong(1, jobID);
        result = statement.executeQuery();
        Map<String, String> configurationMap = new HashMap<String, String>();
        while (result.next()) {
            String domainName = result.getString(1);
            String configName = result.getString(2);
            configurationMap.put(domainName, configName);
        }
        final Job job = new Job(harvestID, configurationMap, channel, snapshot, forceMaxCount, forceMaxBytes,
                forceMaxRunningTime, status, orderxml, orderXMLdoc, seedlist, harvestNum, continuationOfJob);
        job.appendHarvestErrors(harvestErrors);
        job.appendHarvestErrorDetails(harvestErrorDetails);
        job.appendUploadErrors(uploadErrors);
        job.appendUploadErrorDetails(uploadErrorDetails);
        if (startdate != null) {
            job.setActualStart(startdate);
        }
        if (stopdate != null) {
            job.setActualStop(stopdate);
        }

        if (submittedDate != null) {
            job.setSubmittedDate(submittedDate);
        }

        if (creationDate != null) {
            job.setCreationDate(creationDate);
        }

        job.configsChanged = false;
        job.setJobID(jobID);
        job.setEdition(edition);

        if (resubmittedAsJob != null) {
            job.setResubmittedAsJob(resubmittedAsJob);
        }
        if (harvestnamePrefix == null) {
            job.setDefaultHarvestNamePrefix();
        } else {
            job.setHarvestFilenamePrefix(harvestnamePrefix);
        }

        return job;
    } catch (SQLException e) {
        String message = "SQL error reading job " + jobID + " in database" + "\n"
                + ExceptionUtils.getSQLExceptionCause(e);
        log.warn(message, e);
        throw new IOFailure(message, e);
    } catch (DocumentException e) {
        String message = "XML error reading job " + jobID + " in database";
        log.warn(message, e);
        throw new IOFailure(message, e);
    }
}

From source file:dk.netarkivet.harvester.datamodel.DomainDBDAO.java

/**
 * Make SeedList based on entry from seedlists 
 * (id, name, comments, seeds).//w ww.  ja va  2s . c  o  m
 * @param res a Resultset
 * @return a SeedList based on ResultSet entry.
 * @throws SQLException if unable to get data from database
 */
private SeedList getSeedListFromResultset(ResultSet res) throws SQLException {
    final long seedlistId = res.getLong(1);
    final String seedlistName = res.getString(2);
    String seedlistComments = res.getString(3);

    String seedlistContents = "";
    if (DBSpecifics.getInstance().supportsClob()) {
        Clob clob = res.getClob(4);
        seedlistContents = clob.getSubString(1, (int) clob.length());
    } else {
        seedlistContents = res.getString(4);
    }
    final SeedList seedlist = new SeedList(seedlistName, seedlistContents);
    seedlist.setComments(seedlistComments);
    seedlist.setID(seedlistId);
    return seedlist;
}

From source file:nl.b3p.viewer.util.ApplicationDetailsValueTransformer.java

/**
 * create a CaseInsensitiveMap with key/values.
 *
 * Tuples are the elements making up each "row" of the query result. The
 * contract here is to transform these elements into the final row.
 *
 * @param tuple The result elements//from www.  j  ava  2  s .co  m
 * @param aliases The result aliases ("parallel" array to tuple)
 * @return The transformed row.
 */
@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
    Map<String, Object> map = new CaseInsensitiveMap();
    for (int i = 0; i < aliases.length; i++) {
        Object t = tuple[i];
        if (t != null && t instanceof Clob) {
            Clob c = (Clob) tuple[i];
            try {
                t = c.getSubString(1, (int) c.length());
            } catch (SQLException e) {
                LOG.error("Error transforming data tuple of " + aliases[i], e);
            }
        }
        map.put(aliases[i], t);
    }
    return map;
}

From source file:org.agnitas.backend.DBase.java

public String asClob(Object o) {
    if (o == null) {
        return null;
    } else if (o.getClass() == String.class) {
        return (String) o;
    } else {/* w  w w  .  j  a v a2 s. c om*/
        Clob clob = (Clob) o;

        try {
            return clob == null ? null : clob.getSubString(1, (int) clob.length());
        } catch (SQLException e) {
            failure("clob parse", e);
        }
        return null;
    }
}

From source file:org.agnitas.backend.StringOps.java

/**
 * Converts a DB clob into a string/*ww w  .  j  av  a  2  s. c  o  m*/
 * 
 * @param clob
 *            the source
 * @return the extracted string
 */
public static String clob2string(Clob clob) throws SQLException {
    return clob == null ? "" : clob.getSubString(1, (int) clob.length());
}

From source file:org.apache.ddlutils.platform.PlatformImplBase.java

/**
 * This is the core method to retrieve a value for a column from a result set. Its  primary
 * purpose is to call the appropriate method on the result set, and to provide an extension
 * point where database-specific implementations can change this behavior.
 * /*from w  ww.  ja  va 2 s. com*/
 * @param resultSet  The result set to extract the value from
 * @param columnName The name of the column; can be <code>null</code> in which case the
  *                   <code>columnIdx</code> will be used instead
  * @param columnIdx  The index of the column's value in the result set; is only used if
  *                   <code>columnName</code> is <code>null</code>
 * @param jdbcType   The jdbc type to extract
 * @return The value
 * @throws SQLException If an error occurred while accessing the result set
 */
protected Object extractColumnValue(ResultSet resultSet, String columnName, int columnIdx, int jdbcType)
        throws SQLException {
    boolean useIdx = (columnName == null);
    Object value;

    switch (jdbcType) {
    case Types.CHAR:
    case Types.VARCHAR:
    case Types.LONGVARCHAR:
        value = useIdx ? resultSet.getString(columnIdx) : resultSet.getString(columnName);
        break;
    case Types.NUMERIC:
    case Types.DECIMAL:
        value = useIdx ? resultSet.getBigDecimal(columnIdx) : resultSet.getBigDecimal(columnName);
        break;
    case Types.BIT:
    case Types.BOOLEAN:
        value = new Boolean(useIdx ? resultSet.getBoolean(columnIdx) : resultSet.getBoolean(columnName));
        break;
    case Types.TINYINT:
    case Types.SMALLINT:
    case Types.INTEGER:
        value = new Integer(useIdx ? resultSet.getInt(columnIdx) : resultSet.getInt(columnName));
        break;
    case Types.BIGINT:
        value = new Long(useIdx ? resultSet.getLong(columnIdx) : resultSet.getLong(columnName));
        break;
    case Types.REAL:
        value = new Float(useIdx ? resultSet.getFloat(columnIdx) : resultSet.getFloat(columnName));
        break;
    case Types.FLOAT:
    case Types.DOUBLE:
        value = new Double(useIdx ? resultSet.getDouble(columnIdx) : resultSet.getDouble(columnName));
        break;
    case Types.BINARY:
    case Types.VARBINARY:
    case Types.LONGVARBINARY:
        value = useIdx ? resultSet.getBytes(columnIdx) : resultSet.getBytes(columnName);
        break;
    case Types.DATE:
        value = useIdx ? resultSet.getDate(columnIdx) : resultSet.getDate(columnName);
        break;
    case Types.TIME:
        value = useIdx ? resultSet.getTime(columnIdx) : resultSet.getTime(columnName);
        break;
    case Types.TIMESTAMP:
        value = useIdx ? resultSet.getTimestamp(columnIdx) : resultSet.getTimestamp(columnName);
        break;
    case Types.CLOB:
        Clob clob = useIdx ? resultSet.getClob(columnIdx) : resultSet.getClob(columnName);

        if (clob == null) {
            value = null;
        } else {
            long length = clob.length();

            if (length > Integer.MAX_VALUE) {
                value = clob;
            } else if (length == 0) {
                // the javadoc is not clear about whether Clob.getSubString
                // can be used with a substring length of 0
                // thus we do the safe thing and handle it ourselves
                value = "";
            } else {
                value = clob.getSubString(1l, (int) length);
            }
        }
        break;
    case Types.BLOB:
        Blob blob = useIdx ? resultSet.getBlob(columnIdx) : resultSet.getBlob(columnName);

        if (blob == null) {
            value = null;
        } else {
            long length = blob.length();

            if (length > Integer.MAX_VALUE) {
                value = blob;
            } else if (length == 0) {
                // the javadoc is not clear about whether Blob.getBytes
                // can be used with for 0 bytes to be copied
                // thus we do the safe thing and handle it ourselves
                value = new byte[0];
            } else {
                value = blob.getBytes(1l, (int) length);
            }
        }
        break;
    case Types.ARRAY:
        value = useIdx ? resultSet.getArray(columnIdx) : resultSet.getArray(columnName);
        break;
    case Types.REF:
        value = useIdx ? resultSet.getRef(columnIdx) : resultSet.getRef(columnName);
        break;
    default:
        value = useIdx ? resultSet.getObject(columnIdx) : resultSet.getObject(columnName);
        break;
    }
    return resultSet.wasNull() ? null : value;
}

From source file:org.apache.gobblin.source.jdbc.JdbcExtractor.java

private String readClobAsString(Clob logClob) throws SQLException {
    if (logClob == null) {
        return StringUtils.EMPTY;
    }/* w ww.j  a va  2  s  .c  om*/
    long length = logClob.length();
    return logClob.getSubString(1, (int) length);
}

From source file:org.apache.oozie.tools.OozieDBCLI.java

private void replaceForDerby(String oldStr, String newStr) throws Exception {
    Connection connRead = createConnection();
    try {/*from  w  w w  .  j  a va 2 s .co  m*/
        connRead.setAutoCommit(false);
        Statement st = connRead.createStatement();
        // set fetch size to limit number of rows into memory for large table
        st.setFetchSize(100);
        ResultSet rs = st.executeQuery(COORD_ACTION_ID_DEPS);
        while (rs.next()) {
            String id = rs.getString(1);
            Clob clob = rs.getClob(2);
            String clobStr = clob.getSubString(1, (int) clob.length());
            clob.setString(1, clobStr.replace(oldStr, newStr));
            PreparedStatement prepStmt = connRead
                    .prepareStatement("UPDATE COORD_ACTIONS SET MISSING_DEPENDENCIES=? WHERE ID=?");
            prepStmt.setString(1, clob.getSubString(1, (int) clob.length()));
            prepStmt.setString(2, id);
            prepStmt.execute();
            prepStmt.close();
        }
    } finally {
        connRead.commit();
        connRead.close();
    }
}

From source file:org.apache.openjpa.jdbc.sql.DBDictionary.java

/**
 * Convert the specified column of the SQL ResultSet to the proper
 * java type.//from  ww  w  .  jav  a 2  s .c om
 */
public String getClobString(ResultSet rs, int column) throws SQLException {
    if (useGetStringForClobs)
        return rs.getString(column);

    Clob clob = getClob(rs, column);
    if (clob == null)
        return null;
    if (clob.length() == 0)
        return "";

    // unlikely that we'll have strings over Integer.MAX_VALUE chars
    return clob.getSubString(1, (int) clob.length());
}