|
1
25 package testsuite.simple;
26
27 import testsuite.BaseTestCase;
28
29 import java.sql.Connection  ;
30 import java.sql.Date ;
31 import java.sql.PreparedStatement ;
32 import java.sql.SQLException ;
33 import java.sql.Statement ;
34 import java.sql.Timestamp ;
35
36 import java.text.DateFormat ;
37 import java.text.SimpleDateFormat ;
38
39 import java.util.Calendar ;
40 import java.util.Properties ;
41 import java.util.TimeZone ;
42
43 import com.mysql.jdbc.SQLError;
44
45
50 public class DateTest extends BaseTestCase {
51
54
60 public DateTest(String name) {
61 super(name);
62 }
63
64
67
72 public static void main(String [] args) {
73 junit.textui.TestRunner.run(DateTest.class);
74 }
75
76
82 public void setUp() throws Exception {
83 super.setUp();
84 createTestTable();
85 }
86
87
93 public void testTimestamp() throws SQLException {
94 this.pstmt = this.conn
95 .prepareStatement("INSERT INTO DATETEST(tstamp, dt, dtime, tm) VALUES (?, ?, ?, ?)");
96
97 Calendar cal = Calendar.getInstance();
99 cal.set(Calendar.MONTH, 6);
100 cal.set(Calendar.DAY_OF_MONTH, 3);
101 cal.set(Calendar.YEAR, 2002);
102 cal.set(Calendar.HOUR, 7);
103 cal.set(Calendar.MINUTE, 0);
104 cal.set(Calendar.SECOND, 0);
105 cal.set(Calendar.MILLISECOND, 0);
106 cal.set(Calendar.AM_PM, Calendar.AM);
107 cal.getTime();
108 System.out.println(cal);
109
110 DateFormat df = new SimpleDateFormat ("yyyy/MM/dd HH:mm:ss z");
112
113 Calendar calGMT = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
114 Timestamp nowTstamp = new Timestamp (cal.getTime().getTime());
116 java.sql.Date nowDate = new java.sql.Date (cal.getTime().getTime());
117 Timestamp nowDatetime = new Timestamp (cal.getTime().getTime());
118 java.sql.Time nowTime = new java.sql.Time (cal.getTime().getTime());
119 System.out
120 .println("** Times with given calendar (before storing) **\n");
121 System.out.println("TIMESTAMP:\t" + nowTstamp.getTime() + " -> "
122 + df.format(nowTstamp));
123 System.out.println("DATE:\t\t" + nowDate.getTime() + " -> "
124 + df.format(nowDate));
125 System.out.println("DATETIME:\t" + nowDatetime.getTime() + " -> "
126 + df.format(nowDatetime));
127 System.out.println("DATE:\t\t" + nowDate.getTime() + " -> "
128 + df.format(nowDate));
129 System.out.println("TIME:\t\t" + nowTime.getTime() + " -> "
130 + df.format(nowTime));
131 System.out.println("\n");
132 this.pstmt.setTimestamp(1, nowTstamp, calGMT);
133 this.pstmt.setDate(2, nowDate, cal);
136 this.pstmt.setTimestamp(3, nowDatetime, calGMT);
137 this.pstmt.setTime(4, nowTime, cal);
140 this.pstmt.execute();
141
142 this.pstmt.getUpdateCount();
143 this.pstmt.clearParameters();
144 this.rs = this.stmt.executeQuery("SELECT * from DATETEST");
145
146 java.sql.Date thenDate = null;
147
148 while (this.rs.next()) {
149 Timestamp thenTstamp = this.rs.getTimestamp(1, calGMT);
150 thenDate = this.rs.getDate(2, cal);
151
152 java.sql.Timestamp thenDatetime = this.rs.getTimestamp(3, calGMT);
153
154 java.sql.Time thenTime = this.rs.getTime(4, cal);
155 System.out
156 .println("** Times with given calendar (retrieved from database) **\n");
157 System.out.println("TIMESTAMP:\t" + thenTstamp.getTime() + " -> "
158 + df.format(thenTstamp));
159 System.out.println("DATE:\t\t" + thenDate.getTime() + " -> "
160 + df.format(thenDate));
161 System.out.println("DATETIME:\t" + thenDatetime.getTime() + " -> "
162 + df.format(thenDatetime));
163 System.out.println("TIME:\t\t" + thenTime.getTime() + " -> "
164 + df.format(thenTime));
165 System.out.println("\n");
166 }
167
168 this.rs.close();
169 this.rs = null;
170 }
171
172 public void testNanosParsing() throws SQLException {
173 try {
174 this.stmt.executeUpdate("DROP TABLE IF EXISTS testNanosParsing");
175 this.stmt
176 .executeUpdate("CREATE TABLE testNanosParsing (dateIndex int, field1 VARCHAR(32))");
177 this.stmt
178 .executeUpdate("INSERT INTO testNanosParsing VALUES (1, '1969-12-31 18:00:00.0'), "
179 + "(2, '1969-12-31 18:00:00.90'), "
180 + "(3, '1969-12-31 18:00:00.900'), "
181 + "(4, '1969-12-31 18:00:00.9000'), "
182 + "(5, '1969-12-31 18:00:00.90000'), "
183 + "(6, '1969-12-31 18:00:00.900000'), "
184 + "(7, '1969-12-31 18:00:00.')");
185
186 this.rs = this.stmt
187 .executeQuery("SELECT field1 FROM testNanosParsing ORDER BY dateIndex ASC");
188 assertTrue(this.rs.next());
189 assertTrue(this.rs.getTimestamp(1).getNanos() == 0);
190 assertTrue(this.rs.next());
191 assertTrue(this.rs.getTimestamp(1).getNanos() + " != 90", this.rs
192 .getTimestamp(1).getNanos() == 90);
193 assertTrue(this.rs.next());
194 assertTrue(this.rs.getTimestamp(1).getNanos() + " != 900", this.rs
195 .getTimestamp(1).getNanos() == 900);
196 assertTrue(this.rs.next());
197 assertTrue(this.rs.getTimestamp(1).getNanos() + " != 9000", this.rs
198 .getTimestamp(1).getNanos() == 9000);
199 assertTrue(this.rs.next());
200 assertTrue(this.rs.getTimestamp(1).getNanos() + " != 90000",
201 this.rs.getTimestamp(1).getNanos() == 90000);
202 assertTrue(this.rs.next());
203 assertTrue(this.rs.getTimestamp(1).getNanos() + " != 900000",
204 this.rs.getTimestamp(1).getNanos() == 900000);
205 assertTrue(this.rs.next());
206
207 try {
208 this.rs.getTimestamp(1);
209 } catch (SQLException sqlEx) {
210 assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
211 .getSQLState()));
212 }
213 } finally {
214 this.stmt.executeUpdate("DROP TABLE IF EXISTS testNanosParsing");
215 }
216 }
217
218 private void createTestTable() throws SQLException {
219 try {
223 this.stmt.executeUpdate("DROP TABLE DATETEST");
224 } catch (SQLException SQLE) {
225 ;
226 }
227
228 this.stmt
229 .executeUpdate("CREATE TABLE DATETEST (tstamp TIMESTAMP, dt DATE, dtime DATETIME, tm TIME)");
230 }
231
232
239 public void testZeroDateBehavior() throws Exception {
240 try {
241 this.stmt
242 .executeUpdate("DROP TABLE IF EXISTS testZeroDateBehavior");
243 this.stmt
244 .executeUpdate("CREATE TABLE testZeroDateBehavior(fieldAsString VARCHAR(32), fieldAsDateTime DATETIME)");
245 this.stmt
246 .executeUpdate("INSERT INTO testZeroDateBehavior VALUES ('0000-00-00 00:00:00', '0000-00-00 00:00:00')");
247 Properties props = new Properties ();
248 props.setProperty("zeroDateTimeBehavior", "round");
249 Connection roundConn = getConnectionWithProps(props);
250 Statement roundStmt = roundConn.createStatement();
251 this.rs = roundStmt
252 .executeQuery("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
253 this.rs.next();
254
255 assertTrue("0001-01-01".equals(this.rs.getDate(1).toString()));
256 assertTrue("0001-01-01 00:00:00.0".equals(this.rs.getTimestamp(1)
257 .toString()));
258 assertTrue("0001-01-01".equals(this.rs.getDate(2).toString()));
259 assertTrue("0001-01-01 00:00:00.0".equals(this.rs.getTimestamp(2)
260 .toString()));
261
262 PreparedStatement roundPrepStmt = roundConn
263 .prepareStatement("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
264 this.rs = roundPrepStmt.executeQuery();
265 this.rs.next();
266
267 assertTrue("0001-01-01".equals(this.rs.getDate(1).toString()));
268 assertTrue("0001-01-01 00:00:00.0".equals(this.rs.getTimestamp(1)
269 .toString()));
270 assertTrue("0001-01-01".equals(this.rs.getDate(2).toString()));
271 assertTrue("0001-01-01 00:00:00.0".equals(this.rs.getTimestamp(2)
272 .toString()));
273
274 props = new Properties ();
275 props.setProperty("zeroDateTimeBehavior", "convertToNull");
276 Connection nullConn = getConnectionWithProps(props);
277 Statement nullStmt = nullConn.createStatement();
278 this.rs = nullStmt
279 .executeQuery("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
280
281 this.rs.next();
282
283 assertTrue(null == this.rs.getDate(1));
284 assertTrue(null == this.rs.getTimestamp(1));
285 assertTrue(null == this.rs.getDate(2));
286 assertTrue(null == this.rs.getTimestamp(2));
287
288 PreparedStatement nullPrepStmt = nullConn
289 .prepareStatement("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
290 this.rs = nullPrepStmt.executeQuery();
291
292 this.rs.next();
293
294 assertTrue(null == this.rs.getDate(1));
295 assertTrue(null == this.rs.getTimestamp(1));
296 assertTrue(null == this.rs.getDate(2));
297 assertTrue(null == this.rs.getTimestamp(2));
298 assertTrue(null == this.rs.getString(2));
299
300 props = new Properties ();
301 props.setProperty("zeroDateTimeBehavior", "exception");
302 Connection exceptionConn = getConnectionWithProps(props);
303 Statement exceptionStmt = exceptionConn.createStatement();
304 this.rs = exceptionStmt
305 .executeQuery("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
306
307 this.rs.next();
308
309 try {
310 this.rs.getDate(1);
311 fail("Exception should have been thrown when trying to retrieve invalid date");
312 } catch (SQLException sqlEx) {
313 assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
314 .getSQLState()));
315 }
316
317 try {
318 this.rs.getTimestamp(1);
319 fail("Exception should have been thrown when trying to retrieve invalid date");
320 } catch (SQLException sqlEx) {
321 assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
322 .getSQLState()));
323 }
324
325 try {
326 this.rs.getDate(2);
327 fail("Exception should have been thrown when trying to retrieve invalid date");
328 } catch (SQLException sqlEx) {
329 assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
330 .getSQLState()));
331 }
332
333 try {
334 this.rs.getTimestamp(2);
335 fail("Exception should have been thrown when trying to retrieve invalid date");
336 } catch (SQLException sqlEx) {
337 assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
338 .getSQLState()));
339 }
340
341 PreparedStatement exceptionPrepStmt = exceptionConn
342 .prepareStatement("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
343
344 try {
345 this.rs = exceptionPrepStmt.executeQuery();
346 this.rs.next();
347 this.rs.getDate(2);
348 fail("Exception should have been thrown when trying to retrieve invalid date");
349 } catch (SQLException sqlEx) {
350 assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
351 .getSQLState()));
352 }
353
354 } finally {
355 this.stmt
356 .executeUpdate("DROP TABLE IF EXISTS testZeroDateBehavior");
357 }
358 }
359
360 public void testReggieBug() throws Exception {
361 try {
362 this.stmt.executeUpdate("DROP TABLE IF EXISTS testReggieBug");
363 this.stmt.executeUpdate("CREATE TABLE testReggieBug (field1 DATE)");
364
365 PreparedStatement pStmt = this.conn
366 .prepareStatement("INSERT INTO testReggieBug VALUES (?)");
367 pStmt.setDate(1, new Date (2004 - 1900, 07, 28));
368 pStmt.executeUpdate();
369 this.rs = this.stmt.executeQuery("SELECT * FROM testReggieBug");
370 this.rs.next();
371 System.out.println(this.rs.getDate(1));
372 this.rs = this.conn.prepareStatement("SELECT * FROM testReggieBug")
373 .executeQuery();
374 this.rs.next();
375 System.out.println(this.rs.getDate(1));
376
377 } finally {
378 this.stmt.executeUpdate("DROP TABLE IF EXISTS testReggieBug");
379 }
380 }
381 }
382 |
Popular Tags |
Java API By Example, From Geeks To Geeks.