Example usage for java.sql ResultSet getDate

List of usage examples for java.sql ResultSet getDate

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

Introduction

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

Prototype

java.sql.Date getDate(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.Date object in the Java programming language.

Usage

From source file:com.globalsight.ling.tm3.core.TuStorage.java

protected TM3EventLog loadEvents(ResultSet rs) throws SQLException {
    List<TM3Event> events = new ArrayList<TM3Event>();
    while (rs.next()) {
        TM3Event event = new TM3Event();
        event.setId(rs.getLong(1));//  w  w w.  j a v  a2 s  . c  o m
        event.setTimestamp(rs.getDate(2));
        event.setUsername(rs.getString(3));
        event.setType(rs.getInt(4));
        event.setArgument(rs.getString(5));
        events.add(event);
    }
    return new TM3EventLog(events);
}

From source file:com.wso2telco.dep.reportingservice.dao.TaxDAO.java

/**
 * Gets the taxes for tax list.// w w  w. j  a v  a 2 s . co  m
 *
 * @param taxList the tax list
 * @return the taxes for tax list
 * @throws Exception the exception
 */
public List<Tax> getTaxesForTaxList(List<String> taxList) throws Exception {
    Connection connection = null;
    Statement st = null;
    ResultSet results = null;

    List<Tax> taxes = new ArrayList<Tax>();

    if (taxList == null || taxList.isEmpty()) {
        return taxes;
    }

    // CSV format surrounded by single quote
    String taxListStr = taxList.toString().replace("[", "'").replace("]", "'").replace(", ", "','");

    String sql = "SELECT type,effective_from,effective_to,value FROM " + ReportingTable.TAX + " WHERE type IN ("
            + taxListStr + ")";

    try {
        connection = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB);
        st = connection.createStatement();
        log.debug("In getTaxesForTaxList");
        log.debug("SQL (PS) ---> " + st.toString());
        results = st.executeQuery(sql);
        while (results.next()) {
            Tax tax = new Tax();
            tax.setType(results.getString("type"));
            tax.setEffective_from(results.getDate("effective_from"));
            tax.setEffective_to(results.getDate("effective_to"));
            tax.setValue(results.getBigDecimal("value"));
            taxes.add(tax);
        }
        st.close();
    } catch (SQLException e) {
        log.error("SQL Error in getTaxesForTaxList");
        log.error(e.getStackTrace());
        handleException("Error occurred while getting Taxes for Tax List", e);
    } finally {
        DbUtils.closeAllConnections(null, connection, results);
    }
    return taxes;
}

From source file:cz.incad.vdkcommon.solr.Indexer.java

public void removeOffer(int id) throws Exception {
    Connection conn = DbUtils.getConnection();
    String sql = "SELECT ZaznamOffer.uniqueCode, ZaznamOffer.zaznam, ZaznamOffer.exemplar, "
            + "ZaznamOffer.fields, offer.datum " + "FROM zaznamOffer, Offer "
            + "where ZaznamOffer.offer=Offer.offer_id AND ZaznamOffer.offer=?";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setInt(1, id);/*w ww. j a va 2 s. c  o m*/

    ResultSet rs = ps.executeQuery();

    StringBuilder sb = new StringBuilder();
    sb.append("<add>");
    while (rs.next()) {
        String docCode = rs.getString("uniqueCode");
        String datum = sdf.format(rs.getDate("datum"));
        sb.append("<doc>");
        sb.append("<field name=\"code\">").append(docCode).append("</field>");
        sb.append("<field name=\"md5\">").append(docCode).append("</field>");
        sb.append("<field name=\"nabidka\" update=\"remove\">").append(id).append("</field>");
        sb.append("<field name=\"nabidka_datum\" update=\"remove\">").append(datum).append("</field>");
        JSONObject nabidka_ext = new JSONObject();
        JSONObject nabidka_ext_n = new JSONObject();
        nabidka_ext_n.put("zaznam", rs.getString("zaznam"));
        nabidka_ext_n.put("ex", rs.getString("exemplar"));
        nabidka_ext_n.put("fields", rs.getString("fields"));
        nabidka_ext.put("" + id, nabidka_ext_n);
        sb.append("<field name=\"nabidka_ext\" update=\"remove\">").append(nabidka_ext).append("</field>");

        sb.append("</doc>");
    }
    rs.close();
    sb.append("</add>");
    SolrIndexerCommiter.postData(sb.toString());
    SolrIndexerCommiter.postData("<commit/>");
}

From source file:cn.clickvalue.cv2.model.rowmapper.BeanPropertyRowMapper.java

/**
 * Retrieve a JDBC column value from a ResultSet, using the specified value type.
 * <p>Uses the specifically typed ResultSet accessor methods, falling back to
 * {@link #getResultSetValue(java.sql.ResultSet, int)} for unknown types.
 * <p>Note that the returned value may not be assignable to the specified
 * required type, in case of an unknown type. Calling code needs to deal
 * with this case appropriately, e.g. throwing a corresponding exception.
 * @param rs is the ResultSet holding the data
 * @param index is the column index//w w w  .  ja  v  a  2 s  .c o m
 * @param requiredType the required value type (may be <code>null</code>)
 * @return the value object
 * @throws SQLException if thrown by the JDBC API
 */
public static Object getResultSetValue(ResultSet rs, int index, Class requiredType) throws SQLException {
    if (requiredType == null) {
        return getResultSetValue(rs, index);
    }

    Object value = null;
    boolean wasNullCheck = false;

    // Explicitly extract typed value, as far as possible.
    if (String.class.equals(requiredType)) {
        value = rs.getString(index);
    } else if (boolean.class.equals(requiredType) || Boolean.class.equals(requiredType)) {
        value = Boolean.valueOf(rs.getBoolean(index));
        wasNullCheck = true;
    } else if (byte.class.equals(requiredType) || Byte.class.equals(requiredType)) {
        value = Byte.valueOf(rs.getByte(index));
        wasNullCheck = true;
    } else if (short.class.equals(requiredType) || Short.class.equals(requiredType)) {
        value = Short.valueOf(rs.getShort(index));
        wasNullCheck = true;
    } else if (int.class.equals(requiredType) || Integer.class.equals(requiredType)) {
        value = Integer.valueOf(rs.getInt(index));
        wasNullCheck = true;
    } else if (long.class.equals(requiredType) || Long.class.equals(requiredType)) {
        value = Long.valueOf(rs.getLong(index));
        wasNullCheck = true;
    } else if (float.class.equals(requiredType) || Float.class.equals(requiredType)) {
        value = Float.valueOf(rs.getFloat(index));
        wasNullCheck = true;
    } else if (double.class.equals(requiredType) || Double.class.equals(requiredType)
            || Number.class.equals(requiredType)) {
        value = Double.valueOf(rs.getDouble(index));
        wasNullCheck = true;
    } else if (byte[].class.equals(requiredType)) {
        value = rs.getBytes(index);
    } else if (java.sql.Date.class.equals(requiredType)) {
        value = rs.getDate(index);
    } else if (java.sql.Time.class.equals(requiredType)) {
        value = rs.getTime(index);
    } else if (java.sql.Timestamp.class.equals(requiredType) || java.util.Date.class.equals(requiredType)) {
        value = rs.getTimestamp(index);
    } else if (BigDecimal.class.equals(requiredType)) {
        value = rs.getBigDecimal(index);
    } else if (Blob.class.equals(requiredType)) {
        value = rs.getBlob(index);
    } else if (Clob.class.equals(requiredType)) {
        value = rs.getClob(index);
    } else {
        // Some unknown type desired -> rely on getObject.
        value = getResultSetValue(rs, index);
    }

    // Perform was-null check if demanded (for results that the
    // JDBC driver returns as primitives).
    if (wasNullCheck && value != null && rs.wasNull()) {
        value = null;
    }
    return value;
}

From source file:com.wso2telco.dep.reportingservice.dao.TaxDAO.java

/**
 * Gets the taxes for subscription./*from  w  ww . ja  v  a 2s  .  c  o m*/
 *
 * @param applicationId the application id
 * @param apiId the api id
 * @return the taxes for subscription
 * @throws Exception the exception
 */
public List<Tax> getTaxesForSubscription(int applicationId, int apiId) throws Exception {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet results = null;
    String sql = "SELECT type,effective_from,effective_to,value FROM " + ReportingTable.TAX + ", "
            + ReportingTable.SUBSCRIPTION_TAX
            + "WHERE subscription_tax.application_id=? AND subscription_tax.api_id=? AND tax.type=subscription_tax.tax_type ";

    List<Tax> taxes = new ArrayList<Tax>();
    try {
        connection = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB);
        ps = connection.prepareStatement(sql);
        log.debug("getTaxesForSubscription for applicationId---> " + applicationId + " apiId--> " + apiId);
        ps.setInt(1, applicationId);
        ps.setInt(2, apiId);
        log.debug("SQL (PS) ---> " + ps.toString());
        results = ps.executeQuery();
        while (results.next()) {
            Tax tax = new Tax();
            tax.setType(results.getString("type"));
            tax.setEffective_from(results.getDate("effective_from"));
            tax.setEffective_to(results.getDate("effective_to"));
            tax.setValue(results.getBigDecimal("value"));
            taxes.add(tax);
        }
    } catch (SQLException e) {
        log.error("SQL Error in getTaxesForSubscription");
        log.error(e.getStackTrace());
        handleException("Error occurred while getting Taxes for Subscription", e);
    } finally {
        DbUtils.closeAllConnections(ps, connection, results);
    }
    return taxes;
}

From source file:com.oic.event.GetProfile.java

@Override
public void ActionEvent(JSONObject json, WebSocketListener webSocket) {
    JSONObject responseJSON = new JSONObject();
    if (!validation(json)) {
        responseJSON.put("status", "1");
        webSocket.sendJson(responseJSON);
        return;/*from w w w .ja  va  2 s .c  o  m*/
    }
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        String sql = "SELECT user.userid, name, avatarid, grade, sex, birth, comment, privategrade, privatesex, privatebirth FROM user JOIN setting ON (user.userid = setting.userid) WHERE user.userid = ?;";
        con = DatabaseConnection.getConnection();
        ps = con.prepareStatement(sql);
        ps.setLong(1, Long.parseLong(json.get("userid").toString()));
        rs = ps.executeQuery();

        if (rs.next()) {
            responseJSON.put("userid", rs.getLong("userid"));
            responseJSON.put("username", rs.getString("name"));
            responseJSON.put("avatarid", rs.getInt("avatarid"));
            if (rs.getString("privategrade").equals("public")) {
                responseJSON.put("grade", rs.getInt("grade"));
            }
            if (rs.getString("privatesex").equals("public")) {
                responseJSON.put("gender", OicGender.getGender(rs.getString("sex")).toString());
            }
            if (rs.getString("privatebirth").equals("public")) {
                responseJSON.put("birthday", Tools.convertData(rs.getDate("birth")));
            }
            responseJSON.put("comment", rs.getString("comment"));
            responseJSON.put("status", 0);
        } else {
            responseJSON.put("status", 1);
        }
    } catch (SQLException se) {
        responseJSON.put("status", 1);
        se.printStackTrace();
    } finally {
        try {
            rs.close();
        } catch (Exception e1) {
        }
        try {
            ps.close();
        } catch (Exception e1) {
        }
    }
    webSocket.sendJson(responseJSON);
}

From source file:eionet.meta.dao.mysql.VocabularyConceptDAOImpl.java

/**
 * {@inheritDoc}/*from w  ww . j a  v  a 2s  .  co  m*/
 */
@Override
public List<VocabularyConcept> getVocabularyConcepts(int vocabularyFolderId) {
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("vocabularyFolderId", vocabularyFolderId);

    StringBuilder sql = new StringBuilder();
    sql.append(
            "select VOCABULARY_CONCEPT_ID, IDENTIFIER, LABEL, DEFINITION, NOTATION, STATUS, ACCEPTED_DATE, ");
    sql.append("NOT_ACCEPTED_DATE, STATUS_MODIFIED ");
    sql.append("from VOCABULARY_CONCEPT where VOCABULARY_ID=:vocabularyFolderId order by IDENTIFIER + 0");

    List<VocabularyConcept> resultList = getNamedParameterJdbcTemplate().query(sql.toString(), params,
            new RowMapper<VocabularyConcept>() {
                @Override
                public VocabularyConcept mapRow(ResultSet rs, int rowNum) throws SQLException {
                    VocabularyConcept vc = new VocabularyConcept();
                    vc.setId(rs.getInt("VOCABULARY_CONCEPT_ID"));
                    vc.setIdentifier(rs.getString("IDENTIFIER"));
                    vc.setLabel(rs.getString("LABEL"));
                    vc.setDefinition(rs.getString("DEFINITION"));
                    vc.setNotation(rs.getString("NOTATION"));
                    vc.setStatus(rs.getInt("STATUS"));
                    vc.setAcceptedDate(rs.getDate("ACCEPTED_DATE"));
                    vc.setNotAcceptedDate(rs.getDate("NOT_ACCEPTED_DATE"));
                    vc.setStatusModified(rs.getDate("STATUS_MODIFIED"));
                    return vc;
                }
            });

    return resultList;
}

From source file:com.sfs.whichdoctor.dao.ProjectDAOImpl.java

/**
 * Load the project bean from the result set.
 *
 * @param rs the rs//from ww w. java2 s .  c  o  m
 * @return the project bean
 * @throws SQLException the sQL exception
 */
private ProjectBean loadProject(final ResultSet rs) throws SQLException {

    ProjectBean project = new ProjectBean();

    project.setId(rs.getInt("ProjectId"));
    project.setGUID(rs.getInt("GUID"));
    project.setReferenceGUID(rs.getInt("ReferenceGUID"));
    project.setAssessmentType(rs.getString("AssessmentType"));
    project.setAssessmentTypeAbbr(rs.getString("AssessmentTypeAbbr"));
    project.setProjectType(rs.getString("ProjectType"));
    project.setProjectTypeAbbr(rs.getString("ProjectTypeAbbr"));
    project.setStatus(rs.getString("Status"));
    project.setYear(rs.getInt("Year"));
    project.setAllOfProgram(rs.getBoolean("AllOfProgram"));
    project.setTitle(rs.getString("Title"));
    project.setMemo(rs.getString("Memo"));
    try {
        project.setSubmitted(rs.getDate("Submitted"));
    } catch (SQLException sqe) {
        dataLogger.debug("Error reading Submitted: " + sqe.getMessage());
    }
    try {
        project.setResubmitted(rs.getDate("Resubmitted"));
    } catch (SQLException sqe) {
        dataLogger.debug("Error reading Resubmitted: " + sqe.getMessage());
    }
    int assessorId1 = rs.getInt("AssessorId1");
    if (assessorId1 > 0) {
        try {
            /* Load person related to assessorId 1 */
            PersonBean person = this.personDAO.loadGUID(assessorId1);
            project.setAssessor1(person);
        } catch (Exception e) {
            dataLogger.error("Could not load PersonBean for Project");
        }
    }
    int assessorId2 = rs.getInt("AssessorId2");
    if (assessorId2 > 0) {
        try {
            /* Load person related to assessorId 2 */
            PersonBean person = this.personDAO.loadGUID(assessorId2);
            project.setAssessor2(person);
        } catch (Exception e) {
            dataLogger.error("Could not load PersonBean for Project");
        }
    }
    int assessorId3 = rs.getInt("AssessorId3");
    if (assessorId3 > 0) {
        try {
            /* Load person related to assessorId 3 */
            PersonBean person = this.personDAO.loadGUID(assessorId3);
            project.setAssessor3(person);
        } catch (Exception e) {
            dataLogger.error("Could not load PersonBean for Project");
        }
    }

    project.setTrainingOrganisation(rs.getString("TrainingOrganisation"));
    project.setTrainingProgram(rs.getString("TrainingProgram"));
    project.setTrainingProgramISBMapping(rs.getString("TrainingProgramISBMapping"));

    project.setActive(rs.getBoolean("Active"));
    try {
        project.setCreatedDate(rs.getTimestamp("CreatedDate"));
    } catch (SQLException sqe) {
        dataLogger.debug("Error reading CreatedDate: " + sqe.getMessage());
    }
    project.setCreatedBy(rs.getString("CreatedBy"));
    try {
        project.setModifiedDate(rs.getTimestamp("ModifiedDate"));
    } catch (SQLException sqe) {
        dataLogger.debug("Error reading ModifiedDate: " + sqe.getMessage());
    }
    project.setModifiedBy(rs.getString("ModifiedBy"));
    try {
        project.setExportedDate(rs.getTimestamp("ExportedDate"));
    } catch (SQLException sqe) {
        dataLogger.debug("Error reading ExportedDate: " + sqe.getMessage());
    }
    project.setExportedBy(rs.getString("ExportedBy"));

    // Load user details from DB
    UserBean user = new UserBean();
    user.setDN(rs.getString("CreatedBy"));
    user.setPreferredName(rs.getString("CreatedFirstName"));
    user.setLastName(rs.getString("CreatedLastName"));
    project.setCreatedUser(user);

    UserBean modified = new UserBean();
    modified.setDN(rs.getString("ModifiedBy"));
    modified.setPreferredName(rs.getString("ModifiedFirstName"));
    modified.setLastName(rs.getString("ModifiedLastName"));
    project.setModifiedUser(modified);

    UserBean export = new UserBean();
    export.setDN(rs.getString("ExportedBy"));
    export.setPreferredName(rs.getString("ExportedFirstName"));
    export.setLastName(rs.getString("ExportedLastName"));
    project.setExportedUser(export);

    return project;
}

From source file:at.alladin.rmbt.statisticServer.UsageJSONResource.java

/**
 * Returns the statistics for used platforms for a specific timespan [begin, end)
 * @param begin select all tests with time >= begin
 * @param end select all tests with time < end
 * @return the structurized JSON object/*from   w  ww  .j a  v a  2 s  . co  m*/
 * @throws SQLException
 * @throws JSONException
 */
private JSONObject getPlatforms(Timestamp begin, Timestamp end) throws SQLException, JSONException {
    JSONObject returnObj = new JSONObject();
    JSONArray sums = new JSONArray();
    JSONArray values = new JSONArray();
    returnObj.put("sums", sums);
    returnObj.put("values", values);

    HashMap<String, Long> fieldSums = new HashMap<>();

    PreparedStatement ps;
    ResultSet rs;

    final String sql = "SELECT date_trunc('day', time) _day, COALESCE(plattform,'null') platform, count(plattform) count_platform"
            + " FROM test" + " WHERE status='FINISHED' AND deleted=false AND time >= ? AND time < ? "
            + " GROUP BY _day, plattform" + " HAVING count(plattform) > 0" + "ORDER BY _day ASC";

    ps = conn.prepareStatement(sql);
    ps.setTimestamp(1, begin);
    ps.setTimestamp(2, end);
    rs = ps.executeQuery();

    //one array-item for each day
    long currentTime = -1;
    JSONObject currentEntry = null;
    JSONArray currentEntryValues = null;
    while (rs.next()) {

        //new item, of a new day is reached
        long newTime = rs.getDate("_day").getTime();
        if (currentTime != newTime) {
            currentTime = newTime;
            currentEntry = new JSONObject();
            currentEntryValues = new JSONArray();
            currentEntry.put("day", rs.getDate("_day").getTime());
            currentEntry.put("values", currentEntryValues);
            values.put(currentEntry);
        }

        //disable null-values
        String platform = rs.getString("platform");
        long count = rs.getLong("count_platform");
        if (platform.isEmpty()) {
            platform = "empty";
        }

        //add value to sum
        if (!fieldSums.containsKey(platform)) {
            fieldSums.put(platform, new Long(0));
        }
        fieldSums.put(platform, fieldSums.get(platform) + count);

        JSONObject current = new JSONObject();
        current.put("field", platform);
        current.put("value", count);
        currentEntryValues.put(current);
    }

    rs.close();
    ps.close();

    //add field sums
    for (String field : fieldSums.keySet()) {
        JSONObject obj = new JSONObject();
        obj.put("field", field);
        obj.put("sum", fieldSums.get(field));
        sums.put(obj);
    }

    return returnObj;
}

From source file:org.apache.phoenix.end2end.CSVCommonsLoaderTest.java

@Test
public void testAllDatatypes() throws Exception {
    CSVParser parser = null;/*from   w ww  . j  a va  2 s.  c o  m*/
    PhoenixConnection conn = null;
    try {
        // Create table
        String statements = "CREATE TABLE IF NOT EXISTS " + DATATYPE_TABLE
                + " (CKEY VARCHAR NOT NULL PRIMARY KEY,"
                + "  CVARCHAR VARCHAR, CINTEGER INTEGER, CDECIMAL DECIMAL(31,10), CUNSIGNED_INT UNSIGNED_INT, CBOOLEAN BOOLEAN, CBIGINT BIGINT, CUNSIGNED_LONG UNSIGNED_LONG, CTIME TIME, CDATE DATE);";
        conn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class);
        PhoenixRuntime.executeStatements(conn, new StringReader(statements), null);

        // Upsert CSV file
        CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, DATATYPE_TABLE, Collections.<String>emptyList(),
                true);
        csvUtil.upsert(new StringReader(DATATYPES_CSV_VALUES));

        // Compare Phoenix ResultSet with CSV file content
        PreparedStatement statement = conn.prepareStatement(
                "SELECT CKEY, CVARCHAR, CINTEGER, CDECIMAL, CUNSIGNED_INT, CBOOLEAN, CBIGINT, CUNSIGNED_LONG, CTIME, CDATE FROM "
                        + DATATYPE_TABLE);
        ResultSet phoenixResultSet = statement.executeQuery();
        parser = new CSVParser(new StringReader(DATATYPES_CSV_VALUES), csvUtil.getFormat());

        for (CSVRecord record : parser) {
            assertTrue(phoenixResultSet.next());
            int i = 0;
            int size = record.size();
            for (String value : record) {
                assertEquals(value, phoenixResultSet.getObject(i + 1).toString().toUpperCase());
                if (i < size - 2)
                    break;
                i++;
            }
            // special case for matching date, time values
            assertEquals(DateUtil.parseTime(record.get(8)), phoenixResultSet.getTime("CTIME"));
            assertEquals(DateUtil.parseDate(record.get(9)), phoenixResultSet.getDate("CDATE"));
        }

        assertFalse(phoenixResultSet.next());
    } finally {
        if (parser != null)
            parser.close();
        if (conn != null)
            conn.close();
    }
}