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.splicemachine.derby.impl.load.HdfsImportIT.java
@Test public void testAlternateDateAndTimeImport() throws Exception { methodWatcher.executeUpdate("delete from " + spliceSchemaWatcher.schemaName + "." + TABLE_12); PreparedStatement ps = methodWatcher.prepareStatement(format("call SYSCS_UTIL.IMPORT_DATA(" + "'%s'," + // schema name "'%s'," + // table name "null," + // insert column list "'%s'," + // file path "','," + // column delimiter "null," + // character delimiter "null," + // timestamp format "'MM/dd/yyyy'," + // date format "'HH.mm.ss'," + // time format "%d," + // max bad records "'%s'," + // bad record dir "null," + // has one line records "null)", // char set spliceSchemaWatcher.schemaName, TABLE_12, getResourceDirectory() + "dateAndTime.in", 0, BADDIR.getCanonicalPath())); ps.execute();/* w w w . ja v a 2 s.com*/ ResultSet rs = methodWatcher .executeQuery(format("select * from %s.%s", spliceSchemaWatcher.schemaName, TABLE_12)); List<String> results = Lists.newArrayList(); while (rs.next()) { Date d = rs.getDate(1); Time t = rs.getTime(2); assertNotNull("Date is null!", d); assertNotNull("Time is null!", t); results.add(String.format("Date:%s,Time:%s", d, t)); } Assert.assertTrue("Incorrect number of rows imported", results.size() == 2); }
From source file:com.mimp.hibernate.HiberNna.java
public InformeNna InformeExpNna(Long idInforme) { Session session = sessionFactory.getCurrentSession(); session.beginTransaction();//w w w . j ava 2 s . c om final Long id = idInforme; final InformeNna temp = new InformeNna(); Work work = new Work() { @Override public void execute(Connection connection) throws SQLException { String hql = "{call HN_GET_INF_EVAL(?,?)}"; CallableStatement statement = connection.prepareCall(hql); statement.setLong(1, id); statement.registerOutParameter(2, OracleTypes.CURSOR); statement.execute(); ResultSet rs = (ResultSet) statement.getObject(2); if (rs.next()) { temp.setIdinformeNna(rs.getLong("IDINFORME_NNA")); temp.setNumero(rs.getString("NUMERO")); temp.setFecha(rs.getDate("FECHA")); temp.setResultado(rs.getString("RESULTADO")); temp.setObservaciones(rs.getString("OBSERVACIONES")); } rs.close(); statement.close(); } }; session.doWork(work); return temp; }
From source file:com.splicemachine.derby.impl.load.HdfsImportIT.java
@Test public void testNullDatesWithMixedCaseAccuracy() throws Exception { // TODO: JC - was expecting CSV empty column default to CURRENT_TIMESTAMP (old empty column import behavior). Not sure how useful this test is anymore. PreparedStatement ps = methodWatcher.prepareStatement(format("call SYSCS_UTIL.IMPORT_DATA(" + "'%s'," + // schema name "'%s'," + // table name "null," + // insert column list "'%s'," + // file path "','," + // column delimiter "null," + // character delimiter "'yyyy-MM-dd HH:mm:ss.SSSSSS'," + // timestamp format "null," + // date format "null," + // time format "%d," + // max bad records "'%s'," + // bad record dir "null," + // has one line records "null)", // char set spliceSchemaWatcher.schemaName, TABLE_15, getResourceDirectory() + "datebug.tbl", 0, BADDIR.getCanonicalPath())); ps.execute();/*from w w w . jav a 2s. co m*/ ResultSet rs = methodWatcher.executeQuery( format("select DW_SRCC_EXTRC_DTTM from %s.%s", spliceSchemaWatcher.schemaName, TABLE_15)); int i = 0; while (rs.next()) { i++; Assert.assertTrue("Date is still null", rs.getDate(1) != null); } Assert.assertEquals("10 Records not imported", 10, i); }
From source file:org.adempiere.apps.graph.ChartBuilder.java
private void addData(MChartDatasource ds) { String value = ds.getValueColumn(); String category;//from w ww. ja va2s. c om String unit = "D"; if (!chartModel.isTimeSeries()) category = ds.getCategoryColumn(); else { if (chartModel.getTimeUnit().equals(MChart.TIMEUNIT_Week)) { unit = "W"; } else if (chartModel.getTimeUnit().equals(MChart.TIMEUNIT_Month)) { unit = "MM"; } else if (chartModel.getTimeUnit().equals(MChart.TIMEUNIT_Quarter)) { unit = "Q"; } else if (chartModel.getTimeUnit().equals(MChart.TIMEUNIT_Year)) { unit = "Y"; } category = " TRUNC(" + ds.getDateColumn() + ", '" + unit + "') "; } String series = DB.TO_STRING(ds.getName()); boolean hasSeries = false; if (ds.getSeriesColumn() != null) { series = ds.getSeriesColumn(); hasSeries = true; } String where = ds.getWhereClause(); if (!Util.isEmpty(where)) { where = Env.parseContext(Env.getCtx(), chartModel.getWindowNo(), where, true); } boolean hasWhere = false; String sql = "SELECT " + value + ", " + category + ", " + series + " FROM " + ds.getFromClause(); if (!Util.isEmpty(where)) { sql += " WHERE " + where; hasWhere = true; } Date currentDate = Env.getContextAsDate(Env.getCtx(), "#Date"); Date startDate = null; Date endDate = null; int scope = chartModel.getTimeScope(); int offset = ds.getTimeOffset(); if (chartModel.isTimeSeries() && scope != 0) { offset += -scope; startDate = increment(currentDate, chartModel.getTimeUnit(), offset); endDate = increment(startDate, chartModel.getTimeUnit(), scope); } if (startDate != null && endDate != null) { sql += hasWhere ? " AND " : " WHERE "; sql += category + ">=TRUNC(" + DB.TO_DATE(new Timestamp(startDate.getTime())) + ", '" + unit + "') AND "; sql += category + "<=TRUNC(" + DB.TO_DATE(new Timestamp(endDate.getTime())) + ", '" + unit + "') "; } if (sql.indexOf('@') >= 0) { sql = Env.parseContext(Env.getCtx(), 0, sql, false, true); } MRole role = MRole.getDefault(Env.getCtx(), false); sql = role.addAccessSQL(sql, null, true, false); if (hasSeries) sql += " GROUP BY " + series + ", " + category + " ORDER BY " + series + ", " + category; else sql += " GROUP BY " + category + " ORDER BY " + category; log.log(Level.FINE, sql); PreparedStatement pstmt = null; ResultSet rs = null; TimeSeries tseries = null; Dataset dataset = getDataset(); try { pstmt = DB.prepareStatement(sql, null); rs = pstmt.executeQuery(); while (rs.next()) { String key = rs.getString(2); String seriesName = rs.getString(3); if (seriesName == null) seriesName = ds.getName(); String queryWhere = ""; if (hasWhere) queryWhere += where + " AND "; queryWhere += series + " = " + DB.TO_STRING(seriesName) + " AND " + category + " = "; if (chartModel.isTimeSeries() && dataset instanceof TimeSeriesCollection) { if (tseries == null || !tseries.getKey().equals(seriesName)) { if (tseries != null) ((TimeSeriesCollection) dataset).addSeries(tseries); tseries = new TimeSeries(seriesName); } Date date = rs.getDate(2); RegularTimePeriod period = null; if (chartModel.getTimeUnit().equals(MChart.TIMEUNIT_Day)) period = new Day(date); else if (chartModel.getTimeUnit().equals(MChart.TIMEUNIT_Week)) period = new Week(date); else if (chartModel.getTimeUnit().equals(MChart.TIMEUNIT_Month)) period = new Month(date); else if (chartModel.getTimeUnit().equals(MChart.TIMEUNIT_Quarter)) period = new Quarter(date); else if (chartModel.getTimeUnit().equals(MChart.TIMEUNIT_Year)) period = new Year(date); tseries.add(period, rs.getBigDecimal(1)); key = period.toString(); queryWhere += DB.TO_DATE(new Timestamp(date.getTime())); } else { queryWhere += DB.TO_STRING(key); } MQuery query = new MQuery(ds.getAD_Table_ID()); String keyCol = MTable.get(Env.getCtx(), ds.getAD_Table_ID()).getKeyColumns()[0]; String whereClause = keyCol + " IN (SELECT " + ds.getKeyColumn() + " FROM " + ds.getFromClause() + " WHERE " + queryWhere + " )"; query.addRestriction(whereClause.toString()); query.setRecordCount(1); HashMap<String, MQuery> map = getQueries(); if (dataset instanceof DefaultPieDataset) { ((DefaultPieDataset) dataset).setValue(key, rs.getBigDecimal(1)); map.put(key, query); } else if (dataset instanceof DefaultCategoryDataset) { ((DefaultCategoryDataset) dataset).addValue(rs.getBigDecimal(1), seriesName, key); map.put(seriesName + "__" + key, query); } else if (dataset instanceof TimeSeriesCollection) { map.put(seriesName + "__" + key, query); } } } catch (SQLException e) { throw new DBException(e, sql); } finally { DB.close(rs, pstmt); rs = null; pstmt = null; } if (tseries != null) ((TimeSeriesCollection) dataset).addSeries(tseries); }
From source file:HW3.java
private void displayReviewTable(ResultSet result) throws SQLException { TableColumnModel tableModel = jTable1.getColumnModel(); tableModel.getColumn(2).setPreferredWidth(300); tableModel.getColumn(3).setPreferredWidth(200); DefaultTableModel model = (DefaultTableModel) jTable1.getModel(); while (result.next()) { String date = result.getDate(7) + ""; String stars = result.getInt(5) + ""; String text = result.getString(6); String user = result.getString(4); String useful = result.getInt(2) + ""; String cool = result.getInt(3) + ""; String funny = result.getInt(1) + ""; model.addRow(new Object[] { date, stars, text, user, useful, cool, funny }); //System.out.println(date + " "+ stars + " "+ text + " "+ user + " "+ useful + " " + cool + " "+ funny); }//w w w. j a v a 2s . c o m topPanel.setVisible(false); //reviewPanel.repaint(); jScrollPane3.setVisible(true); jTable1.setVisible(true); reviewPanel.setVisible(true); if (jTable1.getRowCount() == 0) { JOptionPane.showMessageDialog(reviewPanel, "NO Reviews tO DISPLAY", "Message", JOptionPane.INFORMATION_MESSAGE); } }
From source file:com.sfs.whichdoctor.dao.MembershipDAOImpl.java
/** * Load membership.//from w w w . j a va 2 s . c o m * * @param rs the rs * * @return the membership bean * * @throws SQLException the SQL exception */ private MembershipBean loadMembership(final ResultSet rs) throws SQLException { MembershipBean membership = this.getInstance(rs.getString("MembershipClass"), rs.getString("MembershipType")); membership.setId(rs.getInt("Id")); membership.setGUID(rs.getInt("GUID")); membership.setReferenceGUID(rs.getInt("ReferenceGUID")); membership.setMemo(rs.getString("Memo")); try { membership.setJoinedDate(rs.getDate("JoinedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading JoinedDate: " + sqe.getMessage()); } try { membership.setLeftDate(rs.getDate("LeftDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading LeftDate: " + sqe.getMessage()); } membership.setPrimary(rs.getBoolean("PrimaryMembership")); for (int i = 1; i <= MAX_INTEGERS; i++) { String field = "intField" + i; membership.setIntField(i, rs.getInt(field)); } for (int i = 1; i <= MAX_OBJECTTYPES; i++) { String field = "objecttypeField" + i; if (rs.getInt(field) > 0) { // Value exists, load objecttype bean from supplied dataset ObjectTypeBean objectType = loadObjectType(String.valueOf(i), rs); membership.setObjectTypeField(i, objectType); } } for (int i = 1; i <= MAX_CHARS; i++) { String field = "charField" + i; membership.setCharField(i, rs.getString(field)); } for (int i = 1; i <= MAX_DATES; i++) { String field = "dateField" + i; try { membership.setDateField(i, rs.getDate(field)); } catch (SQLException sqe) { dataLogger.debug("Error reading dateField (" + i + "): " + sqe.getMessage()); } } membership.setActive(rs.getBoolean("Active")); try { membership.setCreatedDate(rs.getTimestamp("CreatedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading CreatedDate: " + sqe.getMessage()); } membership.setCreatedBy(rs.getString("CreatedBy")); try { membership.setModifiedDate(rs.getTimestamp("ModifiedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading ModifiedDate: " + sqe.getMessage()); } membership.setModifiedBy(rs.getString("ModifiedBy")); try { membership.setExportedDate(rs.getTimestamp("ExportedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading ExportedDate: " + sqe.getMessage()); } membership.setExportedBy(rs.getString("ExportedBy")); return membership; }
From source file:migration.RepositoryUpgrader.java
/** /*w w w .j av a 2 s . c o m*/ * This should be ordered by uri, always. the folder always comes first * @param parentId String of the parent folder structure id * @return List of class Folder * @throws SQLException */ protected List getChildren(ReFolder parentFolder) //throws SQLException { Connection connection = null; boolean wasCommit = false; try { connection = SqlService.borrowConnection(); wasCommit = connection.getAutoCommit(); connection.setAutoCommit(false); String sql = "select row_id, parent, osp_tree_node.id, osp_tree_node.name, uri, " + "creation, last_modified, owner_id, worksiteId, typeId " + "from osp_tree_node join osp_node_metadata on osp_tree_node.id=osp_node_metadata.id " + "where parent "; Object[] fields = null; if (parentFolder != null) { sql += "=? "; fields = new Object[1]; fields[0] = parentFolder.getFolderStructureId(); } else sql += "is null "; sql += "order by uri"; List children = SqlService.dbRead(connection, sql, fields, new SqlReader() { public Object readSqlResultRecord(ResultSet result) { try { RepositoryEntity ent = null; String type = result.getString(10); if (type.equals("folder")) { ent = new ReFolder(); } else if (type.equals("fileArtifact")) { ent = new ReFile(); } else { ent = new ReForm(type); } ent.setFolderStructureId(result.getString(1)); ent.setParentFolderId(result.getString(2)); ent.setArtifactId(result.getString(3)); ent.setTitle(result.getString(4)); ent.setUri(result.getString(5)); ent.setCreationDate(result.getDate(6)); ent.setLastModifiedDate(result.getDate(7)); ent.setOwnerId(result.getString(8)); ent.setWorksiteId(result.getString(9)); return ent; } catch (SQLException ignore) { return null; } } }); for (Iterator i = children.iterator(); i.hasNext();) { RepositoryEntity ent = (RepositoryEntity) i.next(); ent.setParentFolder(parentFolder); } return children; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { connection.commit(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { connection.setAutoCommit(wasCommit); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } SqlService.returnConnection(connection); } return null; }
From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.DBExcelUtility.java
private ArrayList getRowData(ResultSet rs, String colnames[], String coltype[]) throws Exception { ArrayList data = new ArrayList(); int numberOfColumns = coltype.length; short col = 0; boolean nodata = true; for (int i = 0; i < numberOfColumns; i++) { String dat = ""; if (coltype[i].equalsIgnoreCase(DataType.TYPE_STRING)) { String cdata = rs.getString(colnames[i]); data.add(cdata);/*w w w . j ava 2s . c o m*/ } else if (coltype[i].equalsIgnoreCase(DataType.TYPE_INTEGER)) { Integer idata = new Integer(rs.getInt(colnames[i])); data.add(idata); } else if (coltype[i].equalsIgnoreCase(DataType.TYPE_DOUBLE)) { Double ddata = new Double(rs.getDouble(colnames[i])); data.add(ddata); } else if (coltype[i].equalsIgnoreCase(DataType.TYPE_BOOLEAN)) { Boolean bdata = new Boolean(rs.getBoolean(colnames[i])); data.add(bdata); } else if (coltype[i].equalsIgnoreCase(DataType.TYPE_DATE)) { Date dtval = rs.getDate(colnames[i]); data.add(dtval); } } return data; }
From source file:com.wso2telco.dep.reportingservice.dao.ApiManagerDAO.java
/** * Gets the API list for api traffic histogram. * * @param fromDate the from date// w w w. j a v a2 s . co m * @param toDate the to date * @param api the api * @return the API list for api traffic histogram * @throws APIMgtUsageQueryServiceClientException the API mgt usage query service client exception * @throws SQLException the SQL exception */ public List<String[]> getAPIListForAPITrafficHistogram(String fromDate, String toDate, String api) throws APIMgtUsageQueryServiceClientException, SQLException { Connection conn = null; PreparedStatement ps = null; ResultSet results = null; String sql = "select ap.API_NAME,al.Date,0 as hits from\n" + "( select STR_TO_DATE(?,'%Y-%m-%d') -INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date\n" + " from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a \n" + " cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b\n" + " cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c \n" + " ) al \n" + " cross join \n" + " (select api_name from " + ReportingTable.AM_API + " where api_name like ?) ap \n" + " where al.Date between STR_TO_DATE(?,'%Y-%m-%d') and STR_TO_DATE(?,'%Y-%m-%d')"; List<String[]> api_list = new ArrayList<String[]>(); try { conn = DbUtils.getDbConnection(DataSourceNames.WSO2AM_DB); ps = conn.prepareStatement(sql); ps.setString(1, toDate); ps.setString(2, api); ps.setString(3, fromDate); ps.setString(4, toDate); log.debug("getAPIListForAPITrafficHistogram"); results = ps.executeQuery(); while (results.next()) { String[] temp = { results.getString(1), results.getDate(2).toString(), results.getString(3) }; api_list.add(temp); } } catch (Exception e) { log.error("Error occured while getting API list from the database" + e); } finally { DbUtils.closeAllConnections(ps, conn, results); } return api_list; }
From source file:com.mimp.hibernate.HiberNna.java
public ArrayList<InformeNna> listaInformesExpNna(Long idExpNna) { Session session = sessionFactory.getCurrentSession(); session.beginTransaction();/* w w w . j a v a 2s .com*/ final Long expNna = idExpNna; final ArrayList<InformeNna> lista = new ArrayList(); Work work = new Work() { @Override public void execute(Connection connection) throws SQLException { String hql = "{call HN_LIST_INF_EVAL(?,?)}"; CallableStatement statement = connection.prepareCall(hql); statement.setLong(1, expNna); statement.registerOutParameter(2, OracleTypes.CURSOR); statement.execute(); ResultSet rs = (ResultSet) statement.getObject(2); while (rs.next()) { InformeNna tempInf = new InformeNna(); tempInf.setIdinformeNna(rs.getLong("IDINFORME_NNA")); tempInf.setNumero(rs.getString("NUMERO")); tempInf.setFecha(rs.getDate("FECHA")); lista.add(tempInf); } rs.close(); statement.close(); } }; session.doWork(work); return lista; }