Example usage for java.sql ResultSet getTimestamp

List of usage examples for java.sql ResultSet getTimestamp

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

Introduction

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

Prototype

java.sql.Timestamp getTimestamp(String columnLabel) throws SQLException;

Source Link

Document

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

Usage

From source file:org.ohmage.query.impl.CampaignQueries.java

public Campaign findCampaignConfiguration(final String campaignId) throws DataAccessException {
    try {/* www .j  a v  a  2  s.c  o m*/
        return getJdbcTemplate().queryForObject(SQL_GET_CAMPAIGN_INFORMATION, new Object[] { campaignId },
                new RowMapper<Campaign>() {
                    @Override
                    public Campaign mapRow(ResultSet rs, int rowNum) throws SQLException {

                        try {
                            return new Campaign(null, null, rs.getString("description"),
                                    Campaign.RunningState.getValue(rs.getString("running_state")),
                                    Campaign.PrivacyState.getValue(rs.getString("privacy_state")),
                                    rs.getTimestamp("creation_timestamp"), rs.getString("xml"));
                        } catch (DomainException e) {
                            throw new SQLException("The XML is corrupt.", e);
                        }
                    }
                });
    } catch (IncorrectResultSizeDataAccessException e) {
        if (e.getActualSize() == 0) {
            return null;
        }

        throw new DataAccessException("Multiple campaigns have the same ID: " + campaignId, e);
    } catch (org.springframework.dao.DataAccessException e) {
        throw new DataAccessException("General error executing SQL '" + SQL_GET_CAMPAIGN_INFORMATION
                + "' with parameter: " + campaignId, e);
    }
}

From source file:com.mirth.connect.server.migration.Migrate3_0_0.java

private void migrateChannelTable() {
    PreparedStatement preparedStatement = null;
    ResultSet results = null;

    try {/*from w w  w .  j  av a 2s  . c om*/
        /*
         * MIRTH-1667: Derby fails if autoCommit is set to true and there are a large number of
         * results. The following error occurs: "ERROR 40XD0: Container has been closed"
         */
        Connection connection = getConnection();
        connection.setAutoCommit(false);

        preparedStatement = connection.prepareStatement(
                "SELECT ID, NAME, DESCRIPTION, IS_ENABLED, VERSION, REVISION, LAST_MODIFIED, SOURCE_CONNECTOR, DESTINATION_CONNECTORS, PROPERTIES, PREPROCESSING_SCRIPT, POSTPROCESSING_SCRIPT, DEPLOY_SCRIPT, SHUTDOWN_SCRIPT FROM OLD_CHANNEL");
        results = preparedStatement.executeQuery();

        while (results.next()) {
            String channelId = "";

            try {
                channelId = results.getString(1);
                String name = results.getString(2);
                String description = results.getString(3);
                Boolean isEnabled = results.getBoolean(4);
                String version = results.getString(5);
                Integer revision = results.getInt(6);

                Calendar lastModified = Calendar.getInstance();
                lastModified.setTimeInMillis(results.getTimestamp(7).getTime());

                String sourceConnector = results.getString(8);
                String destinationConnectors = results.getString(9);
                String properties = results.getString(10);
                String preprocessingScript = results.getString(11);
                String postprocessingScript = results.getString(12);
                String deployScript = results.getString(13);
                String shutdownScript = results.getString(14);

                Document document = DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument();
                Element element = document.createElement("channel");
                document.appendChild(element);
                DonkeyElement channel = new DonkeyElement(element);

                channel.addChildElement("id", channelId);
                channel.addChildElement("name", name);
                channel.addChildElement("description", description);
                channel.addChildElement("enabled", Boolean.toString(isEnabled));
                channel.addChildElement("version", version);

                DonkeyElement lastModifiedElement = channel.addChildElement("lastModified");
                lastModifiedElement.addChildElement("time", String.valueOf(lastModified.getTimeInMillis()));
                lastModifiedElement.addChildElement("timezone", lastModified.getTimeZone().getDisplayName());

                channel.addChildElement("revision", String.valueOf(revision));

                channel.addChildElementFromXml(sourceConnector).setNodeName("sourceConnector");
                channel.addChildElementFromXml(destinationConnectors).setNodeName("destinationConnectors");
                channel.addChildElementFromXml(properties);

                channel.addChildElement("preprocessingScript", preprocessingScript);
                channel.addChildElement("postprocessingScript", postprocessingScript);
                channel.addChildElement("deployScript", deployScript);
                channel.addChildElement("shutdownScript", shutdownScript);

                String serializedChannel = channel.toXml();

                PreparedStatement updateStatement = null;

                try {
                    updateStatement = connection.prepareStatement(
                            "INSERT INTO CHANNEL (ID, NAME, REVISION, CHANNEL) VALUES (?, ?, ?, ?)");
                    updateStatement.setString(1, channelId);
                    updateStatement.setString(2, name);
                    updateStatement.setInt(3, revision);
                    updateStatement.setString(4, serializedChannel);
                    updateStatement.executeUpdate();
                    updateStatement.close();
                } finally {
                    DbUtils.closeQuietly(updateStatement);
                }
            } catch (Exception e) {
                logger.error("Error migrating channel " + channelId + ".", e);
            }
        }

        connection.commit();
    } catch (SQLException e) {
        logger.error("Error migrating channels.", e);
    } finally {
        DbUtils.closeQuietly(results);
        DbUtils.closeQuietly(preparedStatement);
    }
}

From source file:beproject.MainGUI.java

void getTimeLineData(TimeSeriesCollection t) throws SQLException {
    Statement stmt = Initializer.inConn2.createStatement();
    ResultSet rs1 = stmt.executeQuery("select max(ts) from tweets");
    rs1.first();/*from   w  w w . ja  v a  2 s  . c o  m*/
    Timestamp ts1 = rs1.getTimestamp(1);
    for (String tmp : ScheduledMoviesList.getMovieNames()) {
        TimeSeries t1 = new TimeSeries(tmp, Hour.class);
        Timestamp ts = (Timestamp) ts1.clone();
        for (int i = 0; i < 6; i++) {
            Date d1 = new java.util.Date(ts.getTime());
            Date d2 = new java.util.Date(ts.getTime() + 3600000);
            ResultSet rs = stmt
                    .executeQuery("select count(*) from tweets where moviename='" + tmp + "' and ts between '"
                            + Regression.sdf.format(d1) + "' and '" + Regression.sdf.format(d2) + "'");
            rs.first();
            //if(!rs.first())
            //  t1.addOrUpdate(new Hour(d1), 0);
            //else
            t1.addOrUpdate(new Hour(d1), rs.getInt(1));
            ts.setTime(ts.getTime() - 3600000);
        }
        t.addSeries(t1);
    }

}

From source file:PVGraph.java

public java.util.List<YearsData> getYearsData() {
    Statement stmt = null;//from   w ww .j ava  2s.c  o  m
    String query = "select * from DayData where CurrentPower != 0 order by DateTime";
    Map<String, YearsData> result = new HashMap<String, YearsData>();
    GregorianCalendar gc = new GregorianCalendar();
    try {
        getDatabaseConnection();
        stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            String serial = rs.getString("serial");
            YearsData yd = result.get(serial);
            if (yd == null) {
                yd = new YearsData();
                yd.serial = serial;
                yd.inverter = rs.getString("inverter");
                yd.startTotalPower = rs.getDouble("ETotalToday");
                result.put(serial, yd);
            }
            gc.setTime(rs.getTimestamp("DateTime"));
            int year = gc.get(Calendar.YEAR);
            double totalPower = rs.getDouble("ETotalToday");
            yd.powers.put(year, totalPower);
            yd.endTotalPower = totalPower;
        }
    } catch (SQLException e) {
        System.err.println("Query failed: " + e.getMessage());
    } finally {
        try {
            stmt.close();
        } catch (SQLException e) {
            // relax
        }
    }
    return new java.util.ArrayList<YearsData>(result.values());
}

From source file:com.cnd.greencube.server.dao.jdbc.JdbcDAO.java

@SuppressWarnings("rawtypes")
private Object getColumnValue(ResultSet rs, ResultSetMetaData meta, int index, Class clazz) throws Exception {
    Object value = null;//from   w  w  w.ja va2  s.c om

    int type = meta.getColumnType(index);
    if (clazz == String.class) {
        value = rs.getString(index);
    } else if (clazz == Integer.class) {
        value = rs.getInt(index);
    } else if (clazz == Boolean.class) {
        value = rs.getBoolean(index);
    } else if (clazz == byte[].class) {
        if (type == Types.BLOB)
            value = rs.getBlob(index);
        else
            value = rs.getBytes(index);
    } else if (clazz == Long.class) {
        value = rs.getLong(index);
    } else if (clazz == BigInteger.class) {
        value = rs.getBigDecimal(index);
    } else if (clazz == Float.class) {
        value = rs.getFloat(index);
    } else if (clazz == Double.class) {
        value = rs.getDouble(index);
    } else if (clazz == java.util.Date.class) {
        Timestamp time = rs.getTimestamp(index);
        if (time == null)
            value = null;
        else {
            value = new java.util.Date(time.getTime());
        }
    } else if (clazz == java.sql.Date.class) {
        value = rs.getDate(index);
    } else if (clazz == java.sql.Time.class) {
        value = rs.getTime(index);
    } else if (clazz == java.sql.Timestamp.class) {
        value = rs.getTimestamp(index);
    } else {
        throw new Exception("Cannote determin this column type:" + meta.getColumnName(index));
    }
    return value;
}

From source file:PVGraph.java

public java.util.List<PeriodData> getYearData(int year, boolean detailed) {
    Statement stmt = null;//from  w  w w .  j  av  a 2  s.com
    String query = "select * from DayData where year(DateTime) = " + year
            + " and CurrentPower != 0 order by DateTime";
    Map<String, PeriodData> result = new HashMap<String, PeriodData>();
    GregorianCalendar gc = new GregorianCalendar();
    try {
        getDatabaseConnection();
        stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            String serial = rs.getString("serial");
            PeriodData pd = result.get(serial);
            if (pd == null) {
                pd = new PeriodData();
                pd.serial = serial;
                pd.inverter = rs.getString("inverter");
                pd.startTotalPower = rs.getDouble("ETotalToday");
                result.put(serial, pd);
            }
            gc.setTime(rs.getTimestamp("DateTime"));
            if (detailed)
                pd.numPowers = gc.get(Calendar.DAY_OF_YEAR);
            else
                pd.numPowers = gc.get(Calendar.MONTH) + 1;
            double power = rs.getDouble("ETotalToday");
            pd.powers[pd.numPowers - 1] = power;
            pd.endTotalPower = power;
        }
    } catch (SQLException e) {
        System.err.println("Query failed: " + e.getMessage());
    } finally {
        try {
            stmt.close();
        } catch (SQLException e) {
            // relax
        }
    }
    return new java.util.ArrayList<PeriodData>(result.values());
}

From source file:org.dcache.chimera.FsSqlDriver.java

private Stat toStatLevel(ResultSet rs) throws SQLException {
    Stat stat = new Stat();
    stat.setIno(rs.getLong("inumber"));
    stat.setCrTime(rs.getTimestamp("imtime").getTime());
    stat.setGeneration(0);/*from  w w  w .  j a  v a  2s.co  m*/
    stat.setSize(rs.getLong("isize"));
    stat.setATime(rs.getTimestamp("iatime").getTime());
    stat.setCTime(rs.getTimestamp("ictime").getTime());
    stat.setMTime(rs.getTimestamp("imtime").getTime());
    stat.setUid(rs.getInt("iuid"));
    stat.setGid(rs.getInt("igid"));
    stat.setMode(rs.getInt("imode") | UnixPermission.S_IFREG);
    stat.setNlink(rs.getInt("inlink"));
    stat.setDev(17);
    stat.setRdev(13);
    return stat;
}

From source file:com.gdcn.modules.db.jdbc.processor.CamelBeanProcessor.java

/**
 * Convert a <code>ResultSet</code> column into an object.  Simple
 * implementations could just call <code>rs.getObject(index)</code> while
 * more complex implementations could perform type manipulation to match
 * the column's type to the bean property type.
 *
 * <p>/* w  w w. j av a  2s .c  o m*/
 * This implementation calls the appropriate <code>ResultSet</code> getter
 * method for the given property type to perform the type conversion.  If
 * the property type doesn't match one of the supported
 * <code>ResultSet</code> types, <code>getObject</code> is called.
 * </p>
 *
 * @param rs The <code>ResultSet</code> currently being processed.  It is
 * positioned on a valid row before being passed into this method.
 *
 * @param index The current column index being processed.
 *
 * @param propType The bean property type that this column needs to be
 * converted into.
 *
 * @throws SQLException if a database access error occurs
 *
 * @return The object from the <code>ResultSet</code> at the given column
 * index after optional type processing or <code>null</code> if the column
 * value was SQL NULL.
 */
protected Object processColumn(ResultSet rs, int index, Class<?> propType) throws SQLException {

    if (!propType.isPrimitive() && rs.getObject(index) == null) {
        return null;
    }

    if (propType.equals(String.class)) {
        return rs.getString(index);

    } else if (propType.equals(Integer.TYPE) || propType.equals(Integer.class)) {
        return Integer.valueOf(rs.getInt(index));

    } else if (propType.equals(Boolean.TYPE) || propType.equals(Boolean.class)) {
        return Boolean.valueOf(rs.getBoolean(index));

    } else if (propType.equals(Long.TYPE) || propType.equals(Long.class)) {
        return Long.valueOf(rs.getLong(index));

    } else if (propType.equals(Double.TYPE) || propType.equals(Double.class)) {
        return Double.valueOf(rs.getDouble(index));

    } else if (propType.equals(Float.TYPE) || propType.equals(Float.class)) {
        return Float.valueOf(rs.getFloat(index));

    } else if (propType.equals(Short.TYPE) || propType.equals(Short.class)) {
        return Short.valueOf(rs.getShort(index));

    } else if (propType.equals(Byte.TYPE) || propType.equals(Byte.class)) {
        return Byte.valueOf(rs.getByte(index));

    } else if (propType.equals(Timestamp.class)) {
        return rs.getTimestamp(index);

    } else if (propType.equals(SQLXML.class)) {
        return rs.getSQLXML(index);

    } else {
        return rs.getObject(index);
    }

}

From source file:org.ohmage.query.impl.CampaignQueries.java

public List<Campaign> getCampaigns(final Collection<String> campaignIds, final Collection<String> surveyIds,
        final Collection<String> promptIds, final Collection<String> classIds, final DateTime startDate,
        final DateTime endDate, final Campaign.PrivacyState privacyState,
        final Campaign.RunningState runningState, final long numToSkip, final long numToReturn)
        throws DataAccessException {

    // Build the base SQL that will select the necessary columns to 
    // construct the campaigns.
    StringBuilder sqlBuilder = new StringBuilder(
            "SELECT " + "ca.urn, ca.name, ca.description, " + "ca.icon_url, ca.authored_by, "
                    + "crs.running_state, cps.privacy_state, " + "ca.creation_timestamp, " + "ca.xml " + "FROM "
                    + "campaign ca, " + "campaign_running_state crs, " + "campaign_privacy_state cps "
                    + "WHERE ca.running_state_id = crs.id " + "AND ca.privacy_state_id = cps.id");

    // Add the initial set of parameters.
    List<Object> parameters = new LinkedList<Object>();

    // Add the campaign IDs, if any.
    if ((campaignIds != null) && (campaignIds.size() > 0)) {
        sqlBuilder.append(" AND ca.urn IN " + StringUtils.generateStatementPList(campaignIds.size()));
        parameters.addAll(campaignIds);//from   w w  w.  j a  v  a  2  s  . c o m
    }

    // Add the survey IDs, if any.
    if ((surveyIds != null) && (surveyIds.size() > 0)) {
        sqlBuilder.append(" AND ca.id IN (" + "SELECT csl.campaign_id " + "FROM campaign_survey_lookup csl "
                + "WHERE csl.survey_id IN " + StringUtils.generateStatementPList(surveyIds.size()) + ")");
        parameters.addAll(surveyIds);
    }

    // Add the prompt IDs, if any.
    if ((promptIds != null) && (promptIds.size() > 0)) {
        sqlBuilder.append(" AND ca.id IN (" + "SELECT cpl.campaign_id " + "FROM campaign_prompt_lookup cpl "
                + "WHERE cpl.prompt_id IN " + StringUtils.generateStatementPList(promptIds.size()) + ")");
        parameters.addAll(promptIds);
    }

    // And the classes, if any.
    if ((classIds != null) && (classIds.size() != 0)) {
        sqlBuilder.append(" AND ca.id IN (" + "SELECT cc.campaign_id " + "FROM campaign_class cc "
                + "WHERE cc.class_id IN (" + "SELECT cl.id " + "FROM class cl " + "WHERE cl.urn IN "
                + StringUtils.generateStatementPList(classIds.size()) + ")" + ")");
        parameters.addAll(classIds);
    }

    // Add the start date if one was given.
    if (startDate != null) {
        sqlBuilder.append(" AND creation_timestamp >= ?");
        parameters.add(DateTimeUtils.getIso8601DateString(startDate, true));
    }

    // Add the end date if one was given.
    if (endDate != null) {
        sqlBuilder.append(" AND creation_timestamp <= ?");
        parameters.add(DateTimeUtils.getIso8601DateString(endDate, true));
    }

    // Add the privacy state if one was given.
    if (privacyState != null) {
        sqlBuilder.append(" AND cps.privacy_state = ?");
        parameters.add(privacyState.toString());
    }

    // Add the running state if one was given.
    if (runningState != null) {
        sqlBuilder.append(" AND crs.running_state = ?");
        parameters.add(runningState.toString());
    }

    // Limit the number of results.
    sqlBuilder.append(" LIMIT ?, ?");
    parameters.add(numToSkip);
    parameters.add(numToReturn);

    try {
        return getJdbcTemplate().query(sqlBuilder.toString(), parameters.toArray(), new RowMapper<Campaign>() {
            /**
             * Counts the total number of results and converts each
             * of the actual results into a Campaign object.
             */
            @Override
            public Campaign mapRow(ResultSet rs, int rowNum) throws SQLException {

                try {
                    return new Campaign(null, null, rs.getString("description"),
                            Campaign.RunningState.getValue(rs.getString("running_state")),
                            Campaign.PrivacyState.getValue(rs.getString("privacy_state")),
                            new Date(rs.getTimestamp("creation_timestamp").getTime()), rs.getString("xml"));
                } catch (DomainException e) {
                    throw new SQLException(e);
                }
            }
        });
    } catch (org.springframework.dao.DataAccessException e) {
        throw new DataAccessException(
                "Error executing SQL '" + sqlBuilder.toString() + "' with parameters: " + parameters.toArray(),
                e);
    }
}

From source file:com.intuit.it.billing.data.BillingDAOImpl.java

/**
 * getAllocationsList//from  ww w  . j  a v  a 2s  .c  o m
 * 
 * To be used in a caching method where we are pulling all of the allocations at once.  The way we can do this
 * is to merge a date range based set of billing history records with a date range set of allocations.
 * <p/>
 * <p/>
 * <b>DATABASE PROCEDURE:</b>
 *  
 * @code
 *     FUNCTION fn_get_allocations(
 *           customer IN VARCHAR2,
 *           start_date IN DATE,
 *           end_date   IN DATE )
 *        RETURN ref_cursor;
 * @endcode
 * <p/>
 * <b>DATABASE RESULT SET:</b>
 * <ul>
 *    <li>ALLOCATION_DATE,</li>
 *    <li>ALLOCATION_T, </li>
 *    <li>ALLOCATION_AMT,</li>
 *    <li>AR_ITEM_NO, </li>
 *    <li>BILL_ITEM_NO, </li>
 *    <li>ITEM_DESCRIPTION, </li>
 *    <li>ITEM_CODE,</li> 
 *    <li>AR_ITEM_DATE, </li>
 *    <li>BILL_ITEM_DATE </li>
 *    <li>LICENSE</li>
 *  </ul>
 *  
 * @param customer  :  The Customer.accountNo account number of the customer who's allocations we need
 * @param startDate : The starting date of the allocation - to be merged with a billing history record set
 * @param endDate  :  The ending date of the allocation - to be merged with a billing history record set
 * 
 * @return A list of Allocation objects. 
 * 
 */
@Override
public List<Allocation> getAllocationsList(String customer, Date startDate, Date endDate) throws JSONException {

    List<Allocation> allocs = new ArrayList<Allocation>();

    java.sql.Date sqlStartDate = new java.sql.Date(startDate.getTime());
    java.sql.Date sqlEndDate = new java.sql.Date(endDate.getTime());

    String query = "begin ? := billing_inquiry.fn_get_allocations( ?, ?, ? ); end;";

    Connection conn = null;
    ResultSet rs = null;

    // DB Connection
    try {
        conn = this.getConnection();
    } catch (SQLException e) {
        throw JSONException.sqlError(e);
    } catch (NamingException e) {
        throw JSONException.namingError(e.toString());
    }

    try {
        CallableStatement stmt = conn.prepareCall(query);
        stmt.registerOutParameter(1, OracleTypes.CURSOR);
        stmt.setString(2, customer);
        stmt.setDate(3, sqlStartDate);
        stmt.setDate(4, sqlEndDate);

        stmt.execute();
        rs = (ResultSet) stmt.getObject(1);

        while (rs.next()) {

            Allocation a = new Allocation();
            a.setAllocatedFromItem(rs.getString("AR_ITEM_NO"));
            a.setAllocatedToItem(rs.getString("BILL_ITEM_NO"));
            a.setAllocationAmount(rs.getBigDecimal("ALLOCATION_AMT"));
            a.setAllocationDate(rs.getTimestamp("ALLOCATION_DATE"));
            a.setItemCode(rs.getString("ITEM_CODE"));
            a.setItemDescription(rs.getString("ITEM_DESCRIPTION"));
            a.setLicense(rs.getString("LICENSE"));
            allocs.add(a);
        }
        conn.close();
        rs.close();

    } catch (SQLException e) {
        throw JSONException.sqlError(e);
    }

    if (allocs == null || allocs.isEmpty()) {
        throw JSONException.noDataFound("Null set returned - no data found");
    }

    return allocs;
}