List of usage examples for java.sql ResultSet getDate
java.sql.Date getDate(String columnLabel, Calendar cal) throws SQLException;
ResultSet object as a java.sql.Date object in the Java programming language. From source file:Main.java
public static void main(String[] args) throws Exception { Connection conn = getConnection(); Statement st = conn.createStatement(); st.executeUpdate("create table survey (id int,myDate DATE );"); String INSERT_RECORD = "insert into survey(id, myDate) values(?, ?)"; PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD); pstmt.setString(1, "1"); java.sql.Date sqlDate = new java.sql.Date(new java.util.Date().getTime()); pstmt.setDate(2, sqlDate);/*from w ww. j a v a 2 s . co m*/ pstmt.executeUpdate(); ResultSet rs = st.executeQuery("SELECT * FROM survey"); Calendar cal = Calendar.getInstance(); // get the TimeZone for "America/Los_Angeles" TimeZone tz = TimeZone.getTimeZone("America/Los_Angeles"); cal.setTimeZone(tz); while (rs.next()) { // the JDBC driver will use the time zone information in // Calendar to calculate the date, with the result that // the variable dateCreated contains a java.sql.Date object // that is accurate for "America/Los_Angeles". java.sql.Date dateCreated = rs.getDate(2, cal); System.out.println(dateCreated); } rs.close(); st.close(); conn.close(); }
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();/*from www . ja v a 2s. c o m*/ 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"); }
From source file:com.alibaba.wasp.jdbc.TestJdbcResultSet.java
public void testDatetimeWithCalendar() throws SQLException { trace("test DATETIME with Calendar"); ResultSet rs; stat = conn.createStatement();/*from www . j av a2s . com*/ stat.execute("CREATE TABLE test(ID INT PRIMARY KEY, D DATE, T TIME, TS TIMESTAMP)"); PreparedStatement prep = conn.prepareStatement("INSERT INTO test VALUES(?, ?, ?, ?)"); Calendar regular = Calendar.getInstance(); Calendar other = null; // search a locale that has a _different_ raw offset long testTime = java.sql.Date.valueOf("2001-02-03").getTime(); for (String s : TimeZone.getAvailableIDs()) { TimeZone zone = TimeZone.getTimeZone(s); long rawOffsetDiff = regular.getTimeZone().getRawOffset() - zone.getRawOffset(); // must not be the same timezone (not 0 h and not 24 h difference // as for Pacific/Auckland and Etc/GMT+12) if (rawOffsetDiff != 0 && rawOffsetDiff != 1000 * 60 * 60 * 24) { if (regular.getTimeZone().getOffset(testTime) != zone.getOffset(testTime)) { other = Calendar.getInstance(zone); break; } } } trace("regular offset = " + regular.getTimeZone().getRawOffset() + " other = " + other.getTimeZone().getRawOffset()); prep.setInt(1, 0); prep.setDate(2, null, regular); prep.setTime(3, null, regular); prep.setTimestamp(4, null, regular); prep.execute(); prep.setInt(1, 1); prep.setDate(2, null, other); prep.setTime(3, null, other); prep.setTimestamp(4, null, other); prep.execute(); prep.setInt(1, 2); prep.setDate(2, java.sql.Date.valueOf("2001-02-03"), regular); prep.setTime(3, java.sql.Time.valueOf("04:05:06"), regular); prep.setTimestamp(4, Timestamp.valueOf("2007-08-09 10:11:12.131415"), regular); prep.execute(); prep.setInt(1, 3); prep.setDate(2, java.sql.Date.valueOf("2101-02-03"), other); prep.setTime(3, java.sql.Time.valueOf("14:05:06"), other); prep.setTimestamp(4, Timestamp.valueOf("2107-08-09 10:11:12.131415"), other); prep.execute(); prep.setInt(1, 4); prep.setDate(2, java.sql.Date.valueOf("2101-02-03")); prep.setTime(3, java.sql.Time.valueOf("14:05:06")); prep.setTimestamp(4, Timestamp.valueOf("2107-08-09 10:11:12.131415")); prep.execute(); rs = stat.executeQuery("SELECT * FROM test ORDER BY ID"); assertResultSetMeta(rs, 4, new String[] { "ID", "D", "T", "TS" }, new int[] { Types.INTEGER, Types.DATE, Types.TIME, Types.TIMESTAMP }, new int[] { 10, 8, 6, 23 }, new int[] { 0, 0, 0, 10 }); rs.next(); assertEquals(0, rs.getInt(1)); assertTrue(rs.getDate(2, regular) == null && rs.wasNull()); assertTrue(rs.getTime(3, regular) == null && rs.wasNull()); assertTrue(rs.getTimestamp(3, regular) == null && rs.wasNull()); rs.next(); assertEquals(1, rs.getInt(1)); assertTrue(rs.getDate(2, other) == null && rs.wasNull()); assertTrue(rs.getTime(3, other) == null && rs.wasNull()); assertTrue(rs.getTimestamp(3, other) == null && rs.wasNull()); rs.next(); assertEquals(2, rs.getInt(1)); assertEquals("2001-02-03", rs.getDate(2, regular).toString()); assertEquals("04:05:06", rs.getTime(3, regular).toString()); assertFalse(rs.getTime(3, other).toString().equals("04:05:06")); assertEquals("2007-08-09 10:11:12.131415", rs.getTimestamp(4, regular).toString()); assertFalse(rs.getTimestamp(4, other).toString().equals("2007-08-09 10:11:12.131415")); rs.next(); assertEquals(3, rs.getInt("ID")); assertFalse(rs.getTimestamp("TS", regular).toString().equals("2107-08-09 10:11:12.131415")); assertEquals("2107-08-09 10:11:12.131415", rs.getTimestamp("TS", other).toString()); assertFalse(rs.getTime("T", regular).toString().equals("14:05:06")); assertEquals("14:05:06", rs.getTime("T", other).toString()); // checkFalse(rs.getDate(2, regular).toString(), "2101-02-03"); // check(rs.getDate("D", other).toString(), "2101-02-03"); rs.next(); assertEquals(4, rs.getInt("ID")); assertEquals("2107-08-09 10:11:12.131415", rs.getTimestamp("TS").toString()); assertEquals("14:05:06", rs.getTime("T").toString()); assertEquals("2101-02-03", rs.getDate("D").toString()); assertFalse(rs.next()); stat.execute("DROP TABLE test"); }