List of usage examples for java.sql ResultSet getTimestamp
java.sql.Timestamp getTimestamp(String columnLabel, Calendar cal) throws SQLException;
ResultSet object as a java.sql.Timestamp object in the Java programming language. From source file:ch.digitalfondue.npjt.mapper.ZonedDateTimeMapper.java
public Object getObject(ResultSet rs) throws SQLException { Timestamp timestamp = rs.getTimestamp(name, Calendar.getInstance(UTC_TZ)); return toZonedDateTime(timestamp); }
From source file:com.act.lcms.db.model.CuratedStandardMetlinIon.java
@Override protected List<CuratedStandardMetlinIon> fromResultSet(ResultSet resultSet) throws SQLException, IOException, ClassNotFoundException { List<CuratedStandardMetlinIon> results = new ArrayList<>(); while (resultSet.next()) { Integer id = resultSet.getInt(DB_FIELD.ID.getOffset()); String note = resultSet.getString(DB_FIELD.NOTE.getOffset()); LocalDateTime createdAtDate = new LocalDateTime( resultSet.getTimestamp(DB_FIELD.CREATED_AT.getOffset(), utcCalendar).getTime(), utcDateTimeZone);//from ww w . j a v a2 s . c o m String bestMetlinIon = resultSet.getString(DB_FIELD.BEST_METLIN_ION.getOffset()); Integer standardIonResultId = resultSet.getInt(DB_FIELD.STANDARD_ION_RESULT_ID.getOffset()); String author = resultSet.getString(DB_FIELD.AUTHOR.getOffset()); results.add(new CuratedStandardMetlinIon(id, note, createdAtDate, bestMetlinIon, standardIonResultId, author)); } return results; }
From source file:at.rocworks.oa4j.logger.dbs.NoSQLJDBC.java
@Override public boolean dpGetPeriod(Date t1, Date t2, Dp dp, Set<String> configs, DpGetPeriodResult result) { JDebug.out.log(Level.INFO, "dpGetPeriod: {0}-{1} {2} {3}", new Object[] { t1, t2, dp.toString(), configs.toString() }); try {/*from w w w . j a v a 2 s . c o m*/ Connection conn = dataSourceQuery.getConnection(); if (conn != null) { // select columns ArrayList<Dp> dps = createDpConfigAttrList(dp, configs); if (dps.isEmpty()) { JDebug.out.warning("dpGetPeriod without any valid config."); return false; } StringBuilder columns = new StringBuilder(); dps.forEach((Dp x) -> { String c = attrMap.get(x.getAttribute()); if (c != null) columns.append(c).append(","); }); // add the timestamp columns.append("ts AS TS"); // datapoint element_id Object tag = this.getTagOfDp(dp); if (tag == null) { JDebug.out.log(Level.SEVERE, "dpGetPeriod with invalid datapoint {0}", new Object[] { dp.toString() }); return false; } // build sql statement String sql = String.format(this.sqlSelectStmt, columns); // query data int records = 0; JDebug.out.log(Level.FINE, "dpGetPeriod SQL={0}", sql); try (PreparedStatement stmt = conn.prepareStatement(sql)) { // tag if (tag instanceof Long) stmt.setLong(1, (Long) tag); else if (tag instanceof String) { stmt.setString(1, (String) tag); } // timerange stmt.setTimestamp(2, new java.sql.Timestamp(t1.getTime()), cal); stmt.setTimestamp(3, new java.sql.Timestamp(t2.getTime()), cal); // execute //stmt.setFetchSize(1000); ResultSet rs = stmt.executeQuery(); //ResultSetMetaData md = rs.getMetaData(); Date ts; Object value; while (rs.next()) { records++; int c = 0; ts = rs.getTimestamp("TS", cal); for (int i = 0; i < dps.size(); i++) { switch (dps.get(i).getAttribute()) { case Value: // value_number value = rs.getObject(++c); if (value != null) result.addValue(dps.get(i), ts, value); // value_string value = rs.getObject(++c); if (value != null) result.addValue(dps.get(i), ts, value); // value_timestamp value = rs.getObject(++c); if (value != null) result.addValue(dps.get(i), ts, value); break; case Status: value = rs.getObject(++c); result.addVariable(dps.get(i), ts, new Bit32Var(value)); break; case Status64: value = rs.getObject(++c); result.addVariable(dps.get(i), ts, new Bit64Var(value)); break; case Manager: case User: value = rs.getObject(++c); result.addValue(dps.get(i), ts, value); break; case Stime: value = ts; result.addValue(dps.get(i), ts, value); break; default: c++; JDebug.out.log(Level.SEVERE, "unhandeled config {0}", dps.get(i).getAttribute()); } } } } JDebug.out.log(Level.FINE, "dpGetPeriod: {0} records", records); conn.close(); return true; } else { JDebug.StackTrace(Level.SEVERE, "no connection!"); } } catch (Exception ex) { JDebug.StackTrace(Level.SEVERE, ex); } return false; }
From source file:net.solarnetwork.node.dao.jdbc.test.PreparedStatementCsvReaderTests.java
@Test public void importTable() throws Exception { final String tableName = "SOLARNODE.TEST_CSV_IO"; executeSqlScript("net/solarnetwork/node/dao/jdbc/test/csv-data-01.sql", false); importData(tableName);//from w w w .j a v a 2s .co m final MutableInt row = new MutableInt(0); final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"); sdf.setTimeZone(TimeZone.getTimeZone("UTC")); final Calendar utcCalendar = Calendar.getInstance(TimeZone.getTimeZone("UTC")); jdbcTemplate.query(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { // TODO Auto-generated method stub return con.prepareStatement("select PK,STR,INUM,DNUM,TS from solarnode.test_csv_io order by pk"); } }, new RowCallbackHandler() { @Override public void processRow(ResultSet rs) throws SQLException { row.increment(); final int i = row.intValue(); assertEquals("PK " + i, i, rs.getLong(1)); if (i == 2) { assertNull("STR " + i, rs.getString(2)); } else { assertEquals("STR " + i, "s0" + i, rs.getString(2)); } if (i == 3) { assertNull("INUM " + i, rs.getObject(3)); } else { assertEquals("INUM " + i, i, rs.getInt(3)); } if (i == 4) { assertNull("DNUM " + i, rs.getObject(4)); } else { assertEquals("DNUM " + i, i, rs.getDouble(4), 0.01); } if (i == 5) { assertNull("TS " + i, rs.getObject(5)); } else { Timestamp ts = rs.getTimestamp(5, utcCalendar); try { assertEquals("TS " + i, sdf.parse("2016-10-0" + i + "T12:01:02.345Z"), ts); } catch (ParseException e) { // should not get here } } } }); assertEquals("Imported count", 5, row.intValue()); }
From source file:ca.nrc.cadc.vos.server.NodeMapper.java
/** * Map the row to the appropriate type of node object. * @param rs/* w ww .j a v a 2s. co m*/ * @param row * @return a Node * @throws SQLException */ public Object mapRow(ResultSet rs, int row) throws SQLException { long nodeID = rs.getLong("nodeID"); String name = rs.getString("name"); String type = rs.getString("type"); String busyString = rs.getString("busyState"); String groupRead = rs.getString("groupRead"); String groupWrite = rs.getString("groupWrite"); boolean isPublic = rs.getBoolean("isPublic"); boolean isLocked = rs.getBoolean("isLocked"); Object ownerObject = rs.getObject("ownerID"); String contentType = rs.getString("contentType"); String contentEncoding = rs.getString("contentEncoding"); String link = null; Long contentLength = null; Object o = rs.getObject("contentLength"); if (o != null) { Number n = (Number) o; contentLength = new Long(n.longValue()); } log.debug("readNode: contentLength = " + contentLength); Object contentMD5 = rs.getObject("contentMD5"); Date lastModified = rs.getTimestamp("lastModified", cal); String path = basePath + "/" + name; VOSURI vos; try { vos = new VOSURI(new URI("vos", authority, path, null, null)); } catch (URISyntaxException bug) { throw new RuntimeException("BUG - failed to create vos URI", bug); } Node node; if (NodeDAO.NODE_TYPE_CONTAINER.equals(type)) { node = new ContainerNode(vos); } else if (NodeDAO.NODE_TYPE_DATA.equals(type)) { node = new DataNode(vos); ((DataNode) node).setBusy(NodeBusyState.getStateFromValue(busyString)); } else if (NodeDAO.NODE_TYPE_LINK.equals(type)) { link = rs.getString("link"); try { node = new LinkNode(vos, new URI(link)); } catch (URISyntaxException bug) { throw new RuntimeException("BUG - failed to create link URI", bug); } } else { throw new IllegalStateException("Unknown node database type: " + type); } NodeID nid = new NodeID(); nid.id = nodeID; nid.ownerObject = ownerObject; node.appData = nid; if (contentType != null && contentType.trim().length() > 0) { node.getProperties().add(new NodeProperty(VOS.PROPERTY_URI_TYPE, contentType)); } if (contentEncoding != null && contentEncoding.trim().length() > 0) { node.getProperties().add(new NodeProperty(VOS.PROPERTY_URI_CONTENTENCODING, contentEncoding)); } if (contentLength != null) node.getProperties().add(new NodeProperty(VOS.PROPERTY_URI_CONTENTLENGTH, contentLength.toString())); else node.getProperties().add(new NodeProperty(VOS.PROPERTY_URI_CONTENTLENGTH, "0")); if (contentMD5 != null && contentMD5 instanceof byte[]) { byte[] md5 = (byte[]) contentMD5; if (md5.length < 16) { byte[] tmp = md5; md5 = new byte[16]; System.arraycopy(tmp, 0, md5, 0, tmp.length); // extra space is init with 0 } String contentMD5String = HexUtil.toHex(md5); node.getProperties().add(new NodeProperty(VOS.PROPERTY_URI_CONTENTMD5, contentMD5String)); } if (lastModified != null) { node.getProperties().add(new NodeProperty(VOS.PROPERTY_URI_DATE, dateFormat.format(lastModified))); } if (groupRead != null && groupRead.trim().length() > 0) { node.getProperties().add(new NodeProperty(VOS.PROPERTY_URI_GROUPREAD, groupRead)); } if (groupWrite != null && groupWrite.trim().length() > 0) { node.getProperties().add(new NodeProperty(VOS.PROPERTY_URI_GROUPWRITE, groupWrite)); } node.getProperties().add(new NodeProperty(VOS.PROPERTY_URI_ISPUBLIC, isPublic ? "true" : "false")); if (isLocked) node.getProperties().add(new NodeProperty(VOS.PROPERTY_URI_ISLOCKED, isLocked ? "true" : "false")); // set the read-only flag on the properties for (String propertyURI : VOS.READ_ONLY_PROPERTIES) { int propertyIndex = node.getProperties().indexOf(new NodeProperty(propertyURI, "")); if (propertyIndex != -1) { node.getProperties().get(propertyIndex).setReadOnly(true); } } log.debug("read: " + node.getUri() + "," + node.appData); return node; }
From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java
public void testDateTimeTimestampWithCalendar() throws SQLException { Statement stat = conn.createStatement(); stat.execute("create table ts(x timestamp primary key)"); stat.execute("create table t(x time primary key)"); stat.execute("create table d(x date)"); Calendar utcCalendar = new GregorianCalendar(new SimpleTimeZone(0, "Z")); TimeZone old = TimeZone.getDefault(); DateTimeUtils.resetCalendar();// w w w .j av a 2 s . c om TimeZone.setDefault(TimeZone.getTimeZone("PST")); try { Timestamp ts1 = Timestamp.valueOf("2010-03-13 18:15:00"); Time t1 = new Time(ts1.getTime()); Date d1 = new Date(ts1.getTime()); // when converted to UTC, this is 03:15, which doesn't actually exist // because of summer time change at that day Timestamp ts2 = Timestamp.valueOf("2010-03-13 19:15:00"); Time t2 = new Time(ts2.getTime()); Date d2 = new Date(ts2.getTime()); PreparedStatement prep; ResultSet rs; prep = conn.prepareStatement("insert into ts values(?)"); prep.setTimestamp(1, ts1, utcCalendar); prep.execute(); prep.setTimestamp(1, ts2, utcCalendar); prep.execute(); prep = conn.prepareStatement("insert into t values(?)"); prep.setTime(1, t1, utcCalendar); prep.execute(); prep.setTime(1, t2, utcCalendar); prep.execute(); prep = conn.prepareStatement("insert into d values(?)"); prep.setDate(1, d1, utcCalendar); prep.execute(); prep.setDate(1, d2, utcCalendar); prep.execute(); rs = stat.executeQuery("select * from ts order by x"); rs.next(); assertEquals("2010-03-14 02:15:00.0", rs.getString(1)); assertEquals("2010-03-13 18:15:00.0", rs.getTimestamp(1, utcCalendar).toString()); assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp(1).toString()); assertEquals("2010-03-14 02:15:00.0", rs.getString("x")); assertEquals("2010-03-13 18:15:00.0", rs.getTimestamp("x", utcCalendar).toString()); assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp("x").toString()); rs.next(); assertEquals("2010-03-14 03:15:00.0", rs.getString(1)); assertEquals("2010-03-13 19:15:00.0", rs.getTimestamp(1, utcCalendar).toString()); assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp(1).toString()); assertEquals("2010-03-14 03:15:00.0", rs.getString("x")); assertEquals("2010-03-13 19:15:00.0", rs.getTimestamp("x", utcCalendar).toString()); assertEquals("2010-03-14 03:15:00.0", rs.getTimestamp("x").toString()); rs = stat.executeQuery("select * from t order by x"); rs.next(); assertEquals("02:15:00", rs.getString(1)); assertEquals("18:15:00", rs.getTime(1, utcCalendar).toString()); assertEquals("02:15:00", rs.getTime(1).toString()); assertEquals("02:15:00", rs.getString("x")); assertEquals("18:15:00", rs.getTime("x", utcCalendar).toString()); assertEquals("02:15:00", rs.getTime("x").toString()); rs.next(); assertEquals("03:15:00", rs.getString(1)); assertEquals("19:15:00", rs.getTime(1, utcCalendar).toString()); assertEquals("03:15:00", rs.getTime(1).toString()); assertEquals("03:15:00", rs.getString("x")); assertEquals("19:15:00", rs.getTime("x", utcCalendar).toString()); assertEquals("03:15:00", rs.getTime("x").toString()); rs = stat.executeQuery("select * from d order by x"); rs.next(); assertEquals("2010-03-14", rs.getString(1)); assertEquals("2010-03-13", rs.getDate(1, utcCalendar).toString()); assertEquals("2010-03-14", rs.getDate(1).toString()); assertEquals("2010-03-14", rs.getString("x")); assertEquals("2010-03-13", rs.getDate("x", utcCalendar).toString()); assertEquals("2010-03-14", rs.getDate("x").toString()); rs.next(); assertEquals("2010-03-14", rs.getString(1)); assertEquals("2010-03-13", rs.getDate(1, utcCalendar).toString()); assertEquals("2010-03-14", rs.getDate(1).toString()); assertEquals("2010-03-14", rs.getString("x")); assertEquals("2010-03-13", rs.getDate("x", utcCalendar).toString()); assertEquals("2010-03-14", rs.getDate("x").toString()); } finally { TimeZone.setDefault(old); DateTimeUtils.resetCalendar(); } stat.execute("drop table ts"); stat.execute("drop table t"); stat.execute("drop table d"); }