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:DisplayClobServlet.java

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

    Clob fileAsCLOB = null;/*from   w ww .  j  a v  a 2  s .co m*/
    Connection conn = null;
    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:org.castor.jdo.engine.SQLTypeInfos.java

/**
 * Get value from given ResultSet at given index with given SQL type.
 * //from w w  w .j av  a  2  s.c o m
 * @param rs The ResultSet to get the value from.
 * @param index The index of the value in the ResultSet.
 * @param sqlType The SQL type of the value.
 * @return The value.
 * @throws SQLException If a database access error occurs.
 */
public static Object getValue(final ResultSet rs, final int index, final int sqlType) throws SQLException {
    switch (sqlType) {
    case Types.CHAR:
    case Types.VARCHAR:
    case Types.LONGVARCHAR:
        return rs.getString(index);
    case Types.DECIMAL:
    case Types.NUMERIC:
        return rs.getBigDecimal(index);
    case Types.INTEGER:
        int intVal = rs.getInt(index);
        return (rs.wasNull() ? null : new Integer(intVal));
    case Types.TIME:
        return rs.getTime(index, getCalendar());
    case Types.DATE:
        return rs.getDate(index);
    case Types.TIMESTAMP:
        return rs.getTimestamp(index, getCalendar());
    case Types.FLOAT:
    case Types.DOUBLE:
        double doubleVal = rs.getDouble(index);
        return (rs.wasNull() ? null : new Double(doubleVal));
    case Types.REAL:
        float floatVal = rs.getFloat(index);
        return (rs.wasNull() ? null : new Float(floatVal));
    case Types.SMALLINT:
        short shortVal = rs.getShort(index);
        return (rs.wasNull() ? null : new Short(shortVal));
    case Types.TINYINT:
        byte byteVal = rs.getByte(index);
        return (rs.wasNull() ? null : new Byte(byteVal));
    case Types.LONGVARBINARY:
    case Types.VARBINARY:
    case Types.BINARY:
        return rs.getBytes(index);
    case Types.BLOB:
        Blob blob = rs.getBlob(index);
        return (blob == null ? null : blob.getBinaryStream());
    case Types.CLOB:
        return rs.getClob(index);
    case Types.BIGINT:
        long longVal = rs.getLong(index);
        return (rs.wasNull() ? null : new Long(longVal));
    case Types.BIT:
        boolean boolVal = rs.getBoolean(index);
        return (rs.wasNull() ? null : new Boolean(boolVal));
    default:
        Object value = rs.getObject(index);
        return (rs.wasNull() ? null : value);
    }
}

From source file:com.headstrong.fusion.services.config.ConfigurationServiceImpl.java

/**
 * Reloads the schema definition from STRIDEHub Database.
 * /*  w w  w.j  a v a 2 s.c o m*/
 * @precondition
 * @postcondition
 * @throws SQLException
 * @throws ServiceConfigurationParseException
 * @throws ServiceUnavailableException
 * @throws ServiceNotFoundException
 */

public synchronized void reloadConfiguration()
        throws SQLException, ServiceConfigurationParseException, ServiceUnavailableException {
    ServiceConfigurationCache cache = ServiceConfigurationCache.getInstance();
    Connection connection = null;
    try {
        connection = DatabaseManager.getInstance(FusionConstants.FUSIONDB).getConnection();
    } catch (SQLException e1) {
        logger.error("Error getting the Fusion database connection", e1);
        throw e1;
    }

    if (connection != null) {

        /**
         * Schema table statement and result set
         */
        Statement serviceConfigStmt = null;
        ResultSet serviceConfigRs = null;

        try {

            serviceConfigStmt = connection.createStatement();
            serviceConfigRs = serviceConfigStmt.executeQuery(serviceConfigurationQuery);

            while (serviceConfigRs.next()) {
                ServiceConfiguration serviceConfiguration = new ServiceConfiguration();
                serviceConfiguration.setProcessId(serviceConfigRs.getString("prcs_id"));
                serviceConfiguration.setServiceId(serviceConfigRs.getString("service_id"));
                serviceConfiguration.setPropertyId(serviceConfigRs.getString("property_id"));

                Clob clob = serviceConfigRs.getClob("property_config");
                InputStream inputStream = clob.getAsciiStream();
                StringWriter writer = new StringWriter();
                IOUtils.copy(inputStream, writer, "UTF-8");
                Object propertyConfig = null;

                // TODO - Data type of property_config changed to CLOB to 
                // handle issue with data type size.
                /*Object propertyConfig = serviceConfigRs
                      .getObject("property_config");*/
                String serviceType = serviceConfigRs.getString("service_type");
                // If config type is present search for the corresponding
                // parser.
                if (serviceType != null) {
                    try {
                        String parserType = serviceConfigurationParserRegistry.getServiceConfigurationParser(
                                serviceType, serviceConfiguration.getPropertyId());
                        ServiceConfigParser configurationParser = (ServiceConfigParser) ServiceAliasManager
                                .getInstance().getServiceByAlias(parserType, 0);

                        if (configurationParser != null) {
                            propertyConfig = configurationParser.parseConfiguration(writer.toString());
                        }

                    } catch (ServiceConfigurationParserNotFoundException e) {
                        // Currently being ignored. Need relook.
                        logger.error("No configuration parser found for service type " + serviceType
                                + " and property type " + serviceConfiguration.getPropertyId(), e);
                    }

                }
                cache.addPropertyConfiguration(serviceConfiguration, propertyConfig);
            }
        } catch (SQLException e) {
            logger.error("Error reading the service configuration.", e);
            throw e;
        } catch (IOException e) {
            logger.error("Error parsing the service configuration.", e);
            throw new ServiceConfigurationParseException("Error parsing the service configuration.", e);
        } catch (ServiceConfigurationParseException e) {
            logger.error("Error parsing the service configuration.", e);
            throw e;
        } catch (ServiceUnavailableException e) {
            logger.error("Error parsing the service configuration. "
                    + "No configuration parser service found for the config type.", e);
            throw e;
        } finally {
            DatabaseManager.releaseResources(connection, serviceConfigStmt, serviceConfigRs);
        }
    }
}

From source file:org.blocks4j.reconf.client.setup.DatabaseManager.java

public String get(String fullProperty, Method method) {
    Connection conn = null;/*  ww  w .j  a v a  2 s.  c o  m*/
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = getConnection();
        stmt = conn.prepareStatement("SELECT VALUE                        "
                + "FROM   PUBLIC.CLS_METHOD_PROP_VALUE " + "WHERE  FULL_PROP = ?                "
                + "AND    NAM_CLASS = ?                " + "AND    NAM_METHOD = ?               ");

        stmt.setString(1, StringUtils.upperCase(fullProperty));
        stmt.setString(2, method.getDeclaringClass().getName());
        stmt.setString(3, method.getName());
        rs = stmt.executeQuery();

        if (rs.next()) {
            return rs.getClob(1) == null ? null
                    : rs.getClob(1).getCharacterStream() == null ? null
                            : IOUtils.toString(rs.getClob(1).getCharacterStream());
        }

        return null;

    } catch (Exception e) {
        LoggerHolder.getLog().warn(msg.format("error.db", "get"), e);
        throw new RuntimeException(e);

    } finally {
        close(rs);
        close(stmt);
        close(conn);
    }
}

From source file:reconf.client.setup.DatabaseManager.java

public String get(String fullProperty, Method method) {
    Connection conn = null;//from  www.  ja  v  a2s.c o  m
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
        conn = getConnection();
        stmt = conn.prepareStatement("SELECT VALUE                           "
                + "FROM   PUBLIC.CLS_METHOD_PROP_VALUE_V2 " + "WHERE  FULL_PROP = ?                   "
                + "AND    NAM_CLASS = ?                   " + "AND    NAM_METHOD = ?                  ");

        stmt.setString(1, StringUtils.upperCase(fullProperty));
        stmt.setString(2, method.getDeclaringClass().getName());
        stmt.setString(3, method.getName());
        rs = stmt.executeQuery();

        if (rs.next()) {
            return rs.getClob(1) == null ? null
                    : rs.getClob(1).getCharacterStream() == null ? null
                            : IOUtils.toString(rs.getClob(1).getCharacterStream());
        }

        return null;

    } catch (Exception e) {
        LoggerHolder.getLog().warn(msg.format("error.db", "get"), e);
        throw new RuntimeException(e);

    } finally {
        close(rs);
        close(stmt);
        close(conn);
    }
}

From source file:com.headstrong.fusion.services.config.ConfigurationServiceImpl.java

/**
 * Reloads the schema definition from STRIDEHub Database.
 * //from  w ww .j  a  v  a  2s  . c  om
 * @precondition
 * @postcondition
 * @throws SQLException
 * @throws ServiceConfigurationParseException
 * @throws ServiceUnavailableException
 * @throws ServiceNotFoundException
 */

public synchronized void reloadConfiguration(String processId)
        throws SQLException, ServiceConfigurationParseException, ServiceUnavailableException {
    ServiceConfigurationCache cache = ServiceConfigurationCache.getInstance();
    Connection connection = null;
    try {
        connection = DatabaseManager.getInstance(FusionConstants.FUSIONDB).getConnection();
    } catch (SQLException e1) {
        logger.error("Error getting the Fusion database connection", e1);
        throw e1;
    }

    if (connection != null) {

        /**
         * Schema table statement and result set
         */
        PreparedStatement serviceConfigStmt = null;
        ResultSet serviceConfigRs = null;

        try {

            serviceConfigStmt = connection.prepareStatement(serviceConfigurationQueryProcessIdCheck);
            serviceConfigStmt.setInt(1, Integer.parseInt(processId));
            serviceConfigRs = serviceConfigStmt.executeQuery();

            while (serviceConfigRs.next()) {
                ServiceConfiguration serviceConfiguration = new ServiceConfiguration();
                serviceConfiguration.setProcessId(serviceConfigRs.getString("prcs_id"));
                serviceConfiguration.setServiceId(serviceConfigRs.getString("service_id"));
                serviceConfiguration.setPropertyId(serviceConfigRs.getString("property_id"));

                Clob clob = serviceConfigRs.getClob("property_config");
                InputStream inputStream = clob.getAsciiStream();
                StringWriter writer = new StringWriter();
                IOUtils.copy(inputStream, writer, "UTF-8");

                Object propertyConfig = null;

                /*Object propertyConfig = serviceConfigRs
                      .getObject("property_config");*/
                String serviceType = serviceConfigRs.getString("service_type");
                // If config type is present search for the corresponding
                // parser.
                if (serviceType != null) {
                    try {
                        String parserType = serviceConfigurationParserRegistry.getServiceConfigurationParser(
                                serviceType, serviceConfiguration.getPropertyId());
                        ServiceConfigParser configurationParser = (ServiceConfigParser) ServiceAliasManager
                                .getInstance().getServiceByAlias(parserType, 0);
                        if (configurationParser != null) {
                            propertyConfig = configurationParser.parseConfiguration(writer.toString());
                        }
                    } catch (ServiceConfigurationParserNotFoundException e) {
                        // Currently being ignored. Need relook.
                        logger.error("No configuration parser found for service type " + serviceType
                                + " and property type " + serviceConfiguration.getPropertyId(), e);
                    }

                }
                cache.addPropertyConfiguration(serviceConfiguration, propertyConfig);
            }
        } catch (SQLException e) {
            logger.error("Error reading the service configuration.", e);
            throw e;
        } catch (ServiceConfigurationParseException e) {
            logger.error("Error parsing the service configuration.", e);
            throw e;
        } catch (IOException e) {
            logger.error("Error parsing the service configuration.", e);
            throw new ServiceConfigurationParseException("Error parsing the service configuration.", e);
        } catch (ServiceUnavailableException e) {
            logger.error("Error parsing the service configuration. "
                    + "No configuration parser service found for the config type.", e);
            throw e;
        } finally {
            DatabaseManager.releaseResources(connection, serviceConfigStmt, serviceConfigRs);
        }
    }
}

From source file:com.opencsv.ResultSetHelperService.java

private String getColumnValue(ResultSet rs, int colType, int colIndex, boolean trim, String dateFormatString,
        String timestampFormatString) throws SQLException, IOException {

    String value = "";

    switch (colType) {
    case Types.BIT:
    case Types.JAVA_OBJECT:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getObject(colIndex), "");
        value = ObjectUtils.toString(rs.getObject(colIndex), "");
        break;//from w  w w .  ja v  a2s .  c om
    case Types.BOOLEAN:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getBoolean(colIndex));
        value = ObjectUtils.toString(rs.getBoolean(colIndex));
        break;
    case Types.NCLOB: // todo : use rs.getNClob
    case Types.CLOB:
        Clob c = rs.getClob(colIndex);
        if (c != null) {
            StrBuilder sb = new StrBuilder();
            sb.readFrom(c.getCharacterStream());
            value = sb.toString();
        }
        break;
    case Types.BIGINT:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getLong(colIndex));
        value = ObjectUtils.toString(rs.getLong(colIndex));
        break;
    case Types.DECIMAL:
    case Types.REAL:
    case Types.NUMERIC:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getBigDecimal(colIndex), "");
        value = ObjectUtils.toString(rs.getBigDecimal(colIndex), "");
        break;
    case Types.DOUBLE:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getDouble(colIndex));
        value = ObjectUtils.toString(rs.getDouble(colIndex));
        break;
    case Types.FLOAT:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getFloat(colIndex));
        value = ObjectUtils.toString(rs.getFloat(colIndex));
        break;
    case Types.INTEGER:
    case Types.TINYINT:
    case Types.SMALLINT:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getInt(colIndex));
        value = ObjectUtils.toString(rs.getInt(colIndex));
        break;
    case Types.DATE:
        java.sql.Date date = rs.getDate(colIndex);
        if (date != null) {
            SimpleDateFormat df = new SimpleDateFormat(dateFormatString);
            value = df.format(date);
        }
        break;
    case Types.TIME:
        // Once Java 7 is the minimum supported version.
        //            value = Objects.toString(rs.getTime(colIndex), "");
        value = ObjectUtils.toString(rs.getTime(colIndex), "");
        break;
    case Types.TIMESTAMP:
        value = handleTimestamp(rs.getTimestamp(colIndex), timestampFormatString);
        break;
    case Types.NVARCHAR: // todo : use rs.getNString
    case Types.NCHAR: // todo : use rs.getNString
    case Types.LONGNVARCHAR: // todo : use rs.getNString
    case Types.LONGVARCHAR:
    case Types.VARCHAR:
    case Types.CHAR:
        String columnValue = rs.getString(colIndex);
        if (trim && columnValue != null) {
            value = columnValue.trim();
        } else {
            value = columnValue;
        }
        break;
    default:
        value = "";
    }

    if (rs.wasNull() || value == null) {
        value = "";
    }

    return value;
}

From source file:org.sakaiproject.webservices.SakaiReport.java

private String getColumnValue(ResultSet rs, int colType, int colIndex) throws SQLException, IOException {
    String value = "";
    switch (colType) {
    case Types.BIT:
    case Types.JAVA_OBJECT:
        value = handleObject(rs.getObject(colIndex));
        break;/*from ww  w .j a  v  a  2  s  .  c  o  m*/
    case Types.BOOLEAN:
        boolean b = rs.getBoolean(colIndex);
        value = Boolean.valueOf(b).toString();
        break;
    case NCLOB: // todo : use rs.getNClob
    case Types.CLOB:
        Clob c = rs.getClob(colIndex);
        if (c != null) {
            value = read(c);
        }
        break;
    case Types.BIGINT:
        value = handleLong(rs, colIndex);
        break;
    case Types.DECIMAL:
    case Types.DOUBLE:
    case Types.FLOAT:
    case Types.REAL:
    case Types.NUMERIC:
        value = handleBigDecimal(rs.getBigDecimal(colIndex));
        break;
    case Types.INTEGER:
    case Types.TINYINT:
    case Types.SMALLINT:
        value = handleInteger(rs, colIndex);
        break;
    case Types.DATE:
        value = handleDate(rs, colIndex);
        break;
    case Types.TIME:
        value = handleTime(rs.getTime(colIndex));
        break;
    case Types.TIMESTAMP:
        value = handleTimestamp(rs.getTimestamp(colIndex));
        break;
    case NVARCHAR: // todo : use rs.getNString
    case NCHAR: // todo : use rs.getNString
    case LONGNVARCHAR: // todo : use rs.getNString
    case Types.LONGVARCHAR:
    case Types.VARCHAR:
    case Types.CHAR:
        value = rs.getString(colIndex);
        break;
    case Types.VARBINARY:
    case Types.BINARY:
        value = handleRaw(rs.getBytes(colIndex));
        break;
    default:
        value = "";
    }

    if (value == null) {
        value = "";
    }

    return value;

}

From source file:com.liferay.portal.upgrade.util.Table.java

public Object getValue(ResultSet rs, String name, Integer type) throws Exception {

    Object value = null;//from  www.j a  v a 2s  . com

    int t = type.intValue();

    if (t == Types.BIGINT) {
        try {
            value = GetterUtil.getLong(rs.getLong(name));
        } catch (SQLException e) {
            value = GetterUtil.getLong(rs.getString(name));
        }
    } else if (t == Types.BOOLEAN) {
        value = GetterUtil.getBoolean(rs.getBoolean(name));
    } else if (t == Types.CLOB) {
        try {
            Clob clob = rs.getClob(name);

            if (clob == null) {
                value = StringPool.BLANK;
            } else {
                UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(clob.getCharacterStream());

                StringBundler sb = new StringBundler();

                String line = null;

                while ((line = unsyncBufferedReader.readLine()) != null) {
                    if (sb.length() != 0) {
                        sb.append(SAFE_NEWLINE_CHARACTER);
                    }

                    sb.append(line);
                }

                value = sb.toString();
            }
        } catch (Exception e) {

            // If the database doesn't allow CLOB types for the column
            // value, then try retrieving it as a String

            value = GetterUtil.getString(rs.getString(name));
        }
    } else if (t == Types.DOUBLE) {
        value = GetterUtil.getDouble(rs.getDouble(name));
    } else if (t == Types.FLOAT) {
        value = GetterUtil.getFloat(rs.getFloat(name));
    } else if (t == Types.INTEGER) {
        value = GetterUtil.getInteger(rs.getInt(name));
    } else if (t == Types.SMALLINT) {
        value = GetterUtil.getShort(rs.getShort(name));
    } else if (t == Types.TIMESTAMP) {
        try {
            value = rs.getTimestamp(name);
        } catch (Exception e) {
        }

        if (value == null) {
            value = StringPool.NULL;
        }
    } else if (t == Types.VARCHAR) {
        value = GetterUtil.getString(rs.getString(name));
    } else {
        throw new UpgradeException("Upgrade code using unsupported class type " + type);
    }

    return value;
}

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  av  a  2s  . c o 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();
    }
}