List of usage examples for java.sql ResultSet getDate
java.sql.Date getDate(String columnLabel) throws SQLException;
ResultSet object as a java.sql.Date object in the Java programming language. From source file:com.erbjuder.logger.server.rest.util.ResultSetConverter.java
private List<LogMessage> toLogMessageInternal(ResultSet rs, List<LogMessage> logMessages) { try {/*from ww w. j a v a 2 s . c o m*/ // we will need the column names. java.sql.ResultSetMetaData rsmd = rs.getMetaData(); //loop through the ResultSet while (rs.next()) { //figure out how many columns there are int numColumns = rsmd.getColumnCount(); //each row in the ResultSet will be converted to a Object LogMessage obj = new LogMessage(); // loop through all the columns for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (column_name.equals("ID")) { obj.setId(rs.getBigDecimal(column_name).longValueExact()); } if (column_name.equals("APPLICATIONNAME")) { obj.setApplicationName(rs.getNString(column_name)); } if (column_name.equals("EXPIREDDATE")) { obj.setExpiredDate(rs.getDate(column_name)); } if (column_name.equals("FLOWNAME")) { obj.setFlowName(rs.getNString(column_name)); } if (column_name.equals("FLOWPOINTNAME")) { obj.setFlowPointName(rs.getNString(column_name)); } if (column_name.equals("ISERROR")) { obj.setIsError(rs.getBoolean(column_name)); } if (column_name.equals("TRANSACTIONREFERENCEID")) { obj.setTransactionReferenceID(rs.getNString(column_name)); } if (column_name.equals("UTCLOCALTIMESTAMP")) { obj.setUtcLocalTimeStamp(rs.getTimestamp(column_name)); } if (column_name.equals("UTCSERVERTIMESTAMP")) { obj.setUtcServerTimeStamp(rs.getTimestamp(column_name)); } } //end foreach logMessages.add(obj); } //end while } catch (Exception e) { e.printStackTrace(); } return logMessages; }
From source file:org.apache.sqoop.tool.ImportTool.java
/** * Return the max value in the incremental-import test column. This * value must be numeric./*from www .ja v a 2 s. c o m*/ */ private Object getMaxColumnId(SqoopOptions options) throws SQLException { StringBuilder sb = new StringBuilder(); String query; sb.append("SELECT MAX("); sb.append(options.getIncrementalTestColumn()); sb.append(") FROM "); if (options.getTableName() != null) { // Table import sb.append(options.getTableName()); String where = options.getWhereClause(); if (null != where) { sb.append(" WHERE "); sb.append(where); } query = sb.toString(); } else { // Free form table based import sb.append("("); sb.append(options.getSqlQuery()); sb.append(") sqoop_import_query_alias"); query = sb.toString().replaceAll("\\$CONDITIONS", "(1 = 1)"); } Connection conn = manager.getConnection(); Statement s = null; ResultSet rs = null; try { LOG.info("Maximal id query for free form incremental import: " + query); s = conn.createStatement(); rs = s.executeQuery(query); if (!rs.next()) { // This probably means the table is empty. LOG.warn("Unexpected: empty results for max value query?"); return null; } ResultSetMetaData rsmd = rs.getMetaData(); checkColumnType = rsmd.getColumnType(1); if (checkColumnType == Types.TIMESTAMP) { return rs.getTimestamp(1); } else if (checkColumnType == Types.DATE) { return rs.getDate(1); } else if (checkColumnType == Types.TIME) { return rs.getTime(1); } else { return rs.getObject(1); } } finally { try { if (null != rs) { rs.close(); } } catch (SQLException sqlE) { LOG.warn("SQL Exception closing resultset: " + sqlE); } try { if (null != s) { s.close(); } } catch (SQLException sqlE) { LOG.warn("SQL Exception closing statement: " + sqlE); } } }
From source file:com.sfs.whichdoctor.dao.AccreditationDAOImpl.java
/** * Load accreditation.//from w ww .jav a2s . c o m * * @param rs the rs * * @return the accreditation bean * * @throws SQLException the SQL exception */ private AccreditationBean loadAccreditation(final ResultSet rs) throws SQLException { AccreditationBean accreditation = new AccreditationBean(); accreditation.setId(rs.getInt("AccreditationId")); accreditation.setGUID(rs.getInt("GUID")); accreditation.setReferenceGUID(rs.getInt("ReferenceGUID")); accreditation.setCore(rs.getBoolean("Core")); accreditation.setAccreditationClass(rs.getString("AccreditationClass")); accreditation.setAccreditationType(rs.getString("AccreditationType")); accreditation.setAbbreviation(rs.getString("AccreditationTypeAbbreviation")); accreditation.setSpecialtyType(rs.getString("SpecialtyTypeClass")); accreditation.setSpecialtyTypeAbbreviation(rs.getString("SpecialtyTypeAbbreviation")); accreditation.setSpecialtySubType(rs.getString("SpecialtyTypeName")); accreditation.setWeeksApproved(rs.getInt("WeeksApproved")); accreditation.setWeeksCertified(rs.getInt("WeeksCertified")); accreditation.setNote(rs.getString("Note")); try { accreditation.setAccreditationDate(rs.getDate("AccreditationDate")); } catch (Exception e) { dataLogger.debug("Error parsing AccreditationDate: " + e.getMessage()); } accreditation.setActive(rs.getBoolean("Active")); try { accreditation.setCreatedDate(rs.getTimestamp("CreatedDate")); } catch (SQLException e) { dataLogger.debug("Error parsing CreatedDate: " + e.getMessage()); } accreditation.setCreatedBy(rs.getString("CreatedBy")); try { accreditation.setModifiedDate(rs.getTimestamp("ModifiedDate")); } catch (SQLException e) { dataLogger.debug("Error parsing ModifiedDate: " + e.getMessage()); } accreditation.setModifiedBy(rs.getString("ModifiedBy")); try { accreditation.setExportedDate(rs.getTimestamp("ExportedDate")); } catch (SQLException e) { dataLogger.debug("Error parsing ExportedDate: " + e.getMessage()); } accreditation.setExportedBy(rs.getString("ExportedBy")); return accreditation; }
From source file:com.sfs.whichdoctor.dao.SavedSearchDAOImpl.java
/** * Load the saved search./*from w w w.j ava2s.c o m*/ * * @param rs the rs * @return the search bean * @throws SQLException the sQL exception */ private SearchBean loadSearch(final ResultSet rs) throws SQLException { final SearchBean search = new SearchBean(); dataLogger.debug("Loading search id: " + rs.getInt("SearchId")); search.setId(rs.getInt("SearchId")); search.setName(rs.getString("Name")); search.setDisplayOrder(rs.getInt("DisplayOrder")); search.setType(rs.getString("Type")); String strVariables = rs.getString("Variables"); Collection<Object> parameters = new ArrayList<Object>(); if (strVariables == null) { strVariables = ""; } StringTokenizer tokenizer = new StringTokenizer(strVariables, VARIABLE_DIVIDER); while (tokenizer.hasMoreTokens()) { final String variable = tokenizer.nextToken(); dataLogger.debug("Adding token: '" + variable + "'"); parameters.add(variable); } search.setSQLWhereComponents(rs.getString("SQLString"), "", parameters, search.getSearchDescription()); search.setOrderColumn(rs.getString("Order1")); search.setOrderColumn2(rs.getString("Order2")); search.setOrderColumn3(rs.getString("Order3")); try { search.setCreatedDate(rs.getDate("CreatedDate")); } catch (SQLException sqe) { dataLogger.debug("Error parsing CreatedDate: " + sqe.getMessage()); } search.setPublicSearch(rs.getBoolean("PublicSearch")); search.setFavourite(rs.getBoolean("Favourite")); search.setDescription(rs.getString("Description")); search.setDN(rs.getString("UserDn")); search.setUserName(rs.getString("UserName")); search.setSavedSearch(true); search.setOrderAscending(true); search.setRequestedPage(1); search.setLimit(DEFAULT_LIMIT); return search; }
From source file:org.apache.wiki.auth.user.JDBCUserDatabase.java
/** * Private method that returns the first {@link UserProfile} matching a * named column's value. This method will also set the UID if it has not yet been set. * @param sql the SQL statement that should be prepared; it must have one parameter * to set (either a String or a Long)/*from w w w . ja v a2s. co m*/ * @param index the value to match * @return the resolved UserProfile * @throws SQLException */ private UserProfile findByPreparedStatement(String sql, Object index) throws NoSuchPrincipalException { UserProfile profile = null; boolean found = false; boolean unique = true; Connection conn = null; try { // Open the database connection conn = m_ds.getConnection(); if (m_supportsCommits) { conn.setAutoCommit(false); } PreparedStatement ps = conn.prepareStatement(sql); // Set the parameter to search by if (index instanceof String) { ps.setString(1, (String) index); } else if (index instanceof Long) { ps.setLong(1, ((Long) index).longValue()); } else { throw new IllegalArgumentException("Index type not recognized!"); } // Go and get the record! ResultSet rs = ps.executeQuery(); while (rs.next()) { if (profile != null) { unique = false; break; } profile = newProfile(); // Fetch the basic user attributes profile.setUid(rs.getString(m_uid)); if (profile.getUid() == null) { profile.setUid(generateUid(this)); } profile.setCreated(rs.getTimestamp(m_created)); profile.setEmail(rs.getString(m_email)); profile.setFullname(rs.getString(m_fullName)); profile.setLastModified(rs.getTimestamp(m_modified)); Date lockExpiry = rs.getDate(m_lockExpiry); profile.setLockExpiry(rs.wasNull() ? null : lockExpiry); profile.setLoginName(rs.getString(m_loginName)); profile.setPassword(rs.getString(m_password)); // Fetch the user attributes String rawAttributes = rs.getString(m_attributes); if (rawAttributes != null) { try { Map<String, ? extends Serializable> attributes = Serializer .deserializeFromBase64(rawAttributes); profile.getAttributes().putAll(attributes); } catch (IOException e) { log.error("Could not parse user profile attributes!", e); } } found = true; } ps.close(); } catch (SQLException e) { throw new NoSuchPrincipalException(e.getMessage()); } finally { try { if (conn != null) conn.close(); } catch (Exception e) { } } if (!found) { throw new NoSuchPrincipalException("Could not find profile in database!"); } if (!unique) { throw new NoSuchPrincipalException("More than one profile in database!"); } return profile; }
From source file:org.jrecruiter.service.migration.impl.MigrationServiceImpl.java
@Override @Transactional/*from ww w .j av a2s.co m*/ public void migrateJobData() { jobService.updateJobCountPerDays(); LOGGER.info("Migrating jobs..."); userDao.getAll(); String sql = "SELECT job_id, business_name, business_location, job_title, salary, " + "description, web_site, business_address1, business_address2, " + "business_city, business_state, business_zip, business_phone, " + "business_email, industry, job_restrictions, register_date, expire_date, " + "update_date, status, user_name " + "FROM jobs order by register_date asc;"; ParameterizedRowMapper<Job> mapper = new ParameterizedRowMapper<Job>() { // notice the return type with respect to Java 5 covariant return types public Job mapRow(ResultSet rs, int rowNum) throws SQLException { Job job = new Job(); job.setId(rs.getLong("job_id")); job.setBusinessName(rs.getString("business_name")); job.setBusinessCity(rs.getString("business_location")); job.setJobTitle(rs.getString("job_title")); job.setSalary(rs.getString("salary")); job.setDescription(rs.getString("description")); job.setWebsite(rs.getString("web_site")); job.setBusinessAddress1(rs.getString("business_address1")); job.setBusinessAddress2(rs.getString("business_address2")); job.setBusinessCity(rs.getString("business_city")); job.setBusinessState(rs.getString("business_state")); job.setBusinessZip(rs.getString("business_zip")); job.setBusinessPhone(rs.getString("business_phone")); job.setBusinessEmail(rs.getString("business_email")); job.setIndustryOther(rs.getString("industry")); job.setJobRestrictions(rs.getString("job_restrictions")); job.setRegistrationDate(rs.getDate("register_date")); job.setUpdateDate(rs.getDate("update_date")); job.setStatus(JobStatus.ACTIVE); job.setUsesMap(Boolean.FALSE); //Let's populated the universal id job.setUniversalId(rs.getString("job_id")); final User user = userDao.getUser(rs.getString("user_name")); LOGGER.info("Fetching user: " + rs.getString("user_name") + "."); if (user == null) { throw new IllegalStateException("No user found for user name: " + rs.getString("user_name")); } job.setUser(user); return job; } }; final List<Job> jobs = this.jdbcTemplateV1.query(sql, mapper); //Populating statistics int counterForStatistics = 0; for (Job job : jobs) { Statistic statistic = this.getStatistic(job.getId()); LOGGER.info("[" + ++counterForStatistics + "/" + jobs.size() + "] Old Id: " + job.getId() + "...assigning statistics: " + statistic); if (statistic == null) { throw new IllegalStateException("Statistic should not be null."); } statistic.setJob(job); job.setStatistic(statistic); } int counter = 0; Industry otherIndustry = industryDao.get(CommongKeyIds.OTHER.getId()); Region otherRegion = regionDao.get(CommongKeyIds.OTHER.getId()); for (Job job : jobs) { LOGGER.info("[" + ++counter + "/" + jobs.size() + "] Job: " + job + "."); final Job newJob = new Job(); newJob.setBusinessAddress1(job.getBusinessAddress1()); newJob.setBusinessAddress2(job.getBusinessAddress2()); newJob.setBusinessCity(job.getBusinessCity()); newJob.setBusinessEmail(job.getBusinessEmail()); newJob.setBusinessName(job.getBusinessName()); newJob.setBusinessPhone(job.getBusinessPhone()); newJob.setBusinessState(job.getBusinessState()); newJob.setBusinessZip(job.getBusinessZip()); newJob.setDescription(job.getDescription()); newJob.setIndustry(otherIndustry); newJob.setRegion(otherRegion); newJob.setIndustryOther(job.getIndustryOther()); newJob.setJobRestrictions(job.getJobRestrictions()); newJob.setJobTitle(job.getJobTitle()); newJob.setSalary(job.getSalary()); newJob.setUniversalId(job.getUniversalId()); newJob.setStatus(JobStatus.ACTIVE); if (job.getRegistrationDate() != null) { newJob.setRegistrationDate(job.getRegistrationDate()); } else if (job.getUpdateDate() != null) { newJob.setRegistrationDate(job.getUpdateDate()); } else { throw new IllegalStateException("Both Registration Date and Update Date are null for Job: " + job); } newJob.setUpdateDate(job.getUpdateDate()); newJob.setUser(job.getUser()); newJob.setUsesMap(Boolean.FALSE); newJob.setWebsite(job.getWebsite()); Statistic newStatistic = new Statistic(); newStatistic.setJob(newJob); newStatistic.setCounter(job.getStatistic().getCounter()); newStatistic.setLastAccess(job.getStatistic().getLastAccess()); newJob.setStatistic(newStatistic); jobService.addJob(newJob); } entityManager.flush(); LOGGER.info("Total number of jobs migrated: " + jobs.size()); }
From source file:com.sfs.whichdoctor.analysis.FinancialSummaryAnalysisDAOImpl.java
/** * Load transaction./* ww w . ja v a 2 s . c o m*/ * * @param rs the rs * @param loadDetails the load details * * @return the transaction summary bean * * @throws SQLException the SQL exception */ private TransactionSummaryBean loadTransaction(final ResultSet rs, final BuilderBean loadDetails) throws SQLException { TransactionSummaryBean transaction = new TransactionSummaryBean(); transaction.setSummaryId(rs.getInt("SummaryId")); transaction.setId(rs.getInt("Id")); transaction.setType(rs.getString("Name")); transaction.setFinancialType(rs.getString("Class")); transaction.setSecurity(rs.getString("Security")); transaction.setCategory(rs.getString("Object")); transaction.setAbbreviation(rs.getString("Abbreviation")); transaction.setNumber(rs.getString("Number")); transaction.setDescription(rs.getString("Description")); transaction.setValue(rs.getDouble("Value")); transaction.setNetValue(rs.getDouble("NetValue")); transaction.setCancelled(rs.getBoolean("Cancelled")); transaction.setPersonId(rs.getInt("PersonId")); transaction.setOrganisationId(rs.getInt("OrganisationId")); try { transaction.setIssued(rs.getDate("Issued")); } catch (SQLException sqe) { dataLogger.debug("Error parsing Issued: " + sqe.getMessage()); } if (loadDetails.getBoolean("LOAD_OBJECTS")) { if (transaction.getPersonId() > 0) { try { transaction.setPerson(this.personDAO.loadGUID(transaction.getPersonId())); } catch (Exception e) { dataLogger.error("Error loading person: " + e.getMessage()); } } if (transaction.getOrganisationId() > 0) { try { transaction.setOrganisation(this.organisationDAO.loadGUID(transaction.getOrganisationId())); } catch (Exception e) { dataLogger.error("Error loading organisation: " + e.getMessage()); } } } return transaction; }
From source file:com.wso2telco.dep.reportingservice.dao.TaxDAO.java
/** * Gets the API request times for subscription. * * @param year the year//from w w w. j a va 2 s. c o m * @param month the month * @param apiName the api name * @param apiVersion the api version * @param consumerKey the consumer key * @param operatorId the operator id * @param operation the operation * @param category the category * @param subcategory the subcategory * @return the API request times for subscription * @throws Exception the exception */ public Set<APIRequestDTO> getAPIRequestTimesForSubscription(short year, short month, String apiName, String apiVersion, String consumerKey, String operatorId, int operation, String category, String subcategory) throws Exception { Connection connection = null; PreparedStatement ps = null; ResultSet results = null; String sql = "SELECT api_version,response_count AS count,STR_TO_DATE(time,'%Y-%m-%d') as date FROM " + HostObjectConstants.SB_RESPONSE_SUMMARY_TABLE + " WHERE consumerKey=? and api=? and api_version=? and operatorId=? and responseCode like '20_' and month=? and year=? and operationType=? and category =? and subcategory=? "; Set<APIRequestDTO> apiRequests = new HashSet<APIRequestDTO>(); try { connection = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB); ps = connection.prepareStatement(sql); ps.setString(1, consumerKey); ps.setString(2, apiName); ps.setString(3, apiVersion); ps.setString(4, operatorId); ps.setShort(5, month); ps.setShort(6, year); ps.setInt(7, operation); ps.setString(8, category); ps.setString(9, subcategory); log.debug("SQL (PS) st ---> " + ps.toString()); results = ps.executeQuery(); log.debug("SQL (PS) ed ---> "); while (results.next()) { APIRequestDTO req = new APIRequestDTO(); req.setApiVersion(results.getString("api_version")); req.setRequestCount(results.getInt("count")); req.setDate(results.getDate("date")); apiRequests.add(req); } } catch (SQLException e) { handleException("Error occurred while getting Request Times for Subscription", e); } finally { DbUtils.closeAllConnections(ps, connection, results); } log.debug("done getAPIRequestTimesForSubscription"); return apiRequests; }
From source file:org.apache.phoenix.end2end.CSVCommonsLoaderIT.java
@Test public void testAllDatatypes() throws Exception { CSVParser parser = null;//from ww w.j a va 2s .c om PhoenixConnection conn = null; try { // Create table String statements = "CREATE TABLE IF NOT EXISTS " + DATATYPE_TABLE + " (CKEY VARCHAR NOT NULL PRIMARY KEY," + " CVARCHAR VARCHAR, CCHAR CHAR(10), 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, CCHAR, 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 String timeFieldValue = record.get(9); assertEquals(timeFieldValue.isEmpty() ? null : DateUtil.parseTime(record.get(9)), phoenixResultSet.getTime("CTIME")); String dateField = record.get(10); assertEquals(dateField.isEmpty() ? null : DateUtil.parseDate(record.get(10)), phoenixResultSet.getDate("CDATE")); } assertFalse(phoenixResultSet.next()); } finally { if (parser != null) parser.close(); if (conn != null) conn.close(); } }
From source file:com.webbfontaine.valuewebb.action.rimm.RefSelect.java
public List<SelectItem> autocompleteIdf(Object idf) { List idfNumbersList = new ArrayList<>(); int pos = 2;/*w w w. ja v a2s . c om*/ if (idf == null) { idf = ""; // otherwise concat will not work } else { pos = idf.toString().length(); } Connection connection = Utils.getSQLConnection(); ResultSet rs = null; PreparedStatement preparedStatement = null; String sql = "select idf_number, idf_date from idf, idf_details where LOADED_DETAILS = 1 and substr(idf_number,1,?) = ? and (used is null or used = 0) and idf.idf_details_id = idf_details.id"; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, pos); preparedStatement.setString(2, idf.toString()); rs = preparedStatement.executeQuery(); SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); while (rs.next()) { String idfDate = sdf.format(rs.getDate("IDF_DATE")); idfNumbersList.add(new SelectItem(rs.getString("IDF_NUMBER"), idfDate)); } } catch (SQLException e) { LOGGER.error(e, ""); } finally { DBUtils.closeResources(rs, preparedStatement, connection); } return idfNumbersList; }