1 | /* |
---|
2 | Copyright (c) 2002, 2010, Oracle and/or its affiliates. All rights reserved. |
---|
3 | |
---|
4 | |
---|
5 | The MySQL Connector/J is licensed under the terms of the GPLv2 |
---|
6 | <http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>, like most MySQL Connectors. |
---|
7 | There are special exceptions to the terms and conditions of the GPLv2 as it is applied to |
---|
8 | this software, see the FLOSS License Exception |
---|
9 | <http://www.mysql.com/about/legal/licensing/foss-exception.html>. |
---|
10 | |
---|
11 | This program is free software; you can redistribute it and/or modify it under the terms |
---|
12 | of the GNU General Public License as published by the Free Software Foundation; version 2 |
---|
13 | of the License. |
---|
14 | |
---|
15 | This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; |
---|
16 | without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
---|
17 | See the GNU General Public License for more details. |
---|
18 | |
---|
19 | You should have received a copy of the GNU General Public License along with this |
---|
20 | program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth |
---|
21 | Floor, Boston, MA 02110-1301 USA |
---|
22 | |
---|
23 | |
---|
24 | |
---|
25 | */ |
---|
26 | package testsuite.simple; |
---|
27 | |
---|
28 | import java.sql.Connection; |
---|
29 | import java.sql.Date; |
---|
30 | import java.sql.PreparedStatement; |
---|
31 | import java.sql.SQLException; |
---|
32 | import java.sql.Statement; |
---|
33 | import java.sql.Time; |
---|
34 | import java.sql.Timestamp; |
---|
35 | import java.text.DateFormat; |
---|
36 | import java.text.SimpleDateFormat; |
---|
37 | import java.util.Calendar; |
---|
38 | import java.util.Locale; |
---|
39 | import java.util.Properties; |
---|
40 | import java.util.TimeZone; |
---|
41 | |
---|
42 | import testsuite.BaseTestCase; |
---|
43 | |
---|
44 | import com.mysql.jdbc.SQLError; |
---|
45 | |
---|
46 | /** |
---|
47 | * |
---|
48 | * @author Mark Matthews |
---|
49 | * @version $Id$ |
---|
50 | */ |
---|
51 | public class DateTest extends BaseTestCase { |
---|
52 | // ~ Constructors |
---|
53 | // ----------------------------------------------------------- |
---|
54 | |
---|
55 | /** |
---|
56 | * Creates a new DateTest object. |
---|
57 | * |
---|
58 | * @param name |
---|
59 | * DOCUMENT ME! |
---|
60 | */ |
---|
61 | public DateTest(String name) { |
---|
62 | super(name); |
---|
63 | } |
---|
64 | |
---|
65 | // ~ Methods |
---|
66 | // ---------------------------------------------------------------- |
---|
67 | |
---|
68 | /** |
---|
69 | * Runs all test cases in this test suite |
---|
70 | * |
---|
71 | * @param args |
---|
72 | */ |
---|
73 | public static void main(String[] args) { |
---|
74 | junit.textui.TestRunner.run(DateTest.class); |
---|
75 | } |
---|
76 | |
---|
77 | /** |
---|
78 | * DOCUMENT ME! |
---|
79 | * |
---|
80 | * @throws Exception |
---|
81 | * DOCUMENT ME! |
---|
82 | */ |
---|
83 | public void setUp() throws Exception { |
---|
84 | super.setUp(); |
---|
85 | } |
---|
86 | |
---|
87 | /** |
---|
88 | * DOCUMENT ME! |
---|
89 | * |
---|
90 | * @throws SQLException |
---|
91 | * DOCUMENT ME! |
---|
92 | */ |
---|
93 | public void testTimestamp() throws SQLException { |
---|
94 | createTable("DATETEST", "(tstamp TIMESTAMP, dt DATE, dtime DATETIME, tm TIME)"); |
---|
95 | |
---|
96 | this.pstmt = this.conn |
---|
97 | .prepareStatement("INSERT INTO DATETEST(tstamp, dt, dtime, tm) VALUES (?, ?, ?, ?)"); |
---|
98 | |
---|
99 | // TimeZone.setDefault(TimeZone.getTimeZone("GMT")); |
---|
100 | Calendar cal = Calendar.getInstance(); |
---|
101 | cal.set(Calendar.MONTH, 6); |
---|
102 | cal.set(Calendar.DAY_OF_MONTH, 3); |
---|
103 | cal.set(Calendar.YEAR, 2002); |
---|
104 | cal.set(Calendar.HOUR, 7); |
---|
105 | cal.set(Calendar.MINUTE, 0); |
---|
106 | cal.set(Calendar.SECOND, 0); |
---|
107 | cal.set(Calendar.MILLISECOND, 0); |
---|
108 | cal.set(Calendar.AM_PM, Calendar.AM); |
---|
109 | cal.getTime(); |
---|
110 | System.out.println(cal); |
---|
111 | |
---|
112 | // DateFormat df = SimpleDateFormat.getInstance(); |
---|
113 | DateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss z"); |
---|
114 | |
---|
115 | Calendar calGMT = Calendar.getInstance(TimeZone.getTimeZone("GMT")); |
---|
116 | // df.setTimeZone(TimeZone.getTimeZone("GMT")); |
---|
117 | Timestamp nowTstamp = new Timestamp(cal.getTime().getTime()); |
---|
118 | java.sql.Date nowDate = new java.sql.Date(cal.getTime().getTime()); |
---|
119 | Timestamp nowDatetime = new Timestamp(cal.getTime().getTime()); |
---|
120 | java.sql.Time nowTime = new java.sql.Time(cal.getTime().getTime()); |
---|
121 | System.out |
---|
122 | .println("** Times with given calendar (before storing) **\n"); |
---|
123 | System.out.println("TIMESTAMP:\t" + nowTstamp.getTime() + " -> " |
---|
124 | + df.format(nowTstamp)); |
---|
125 | System.out.println("DATE:\t\t" + nowDate.getTime() + " -> " |
---|
126 | + df.format(nowDate)); |
---|
127 | System.out.println("DATETIME:\t" + nowDatetime.getTime() + " -> " |
---|
128 | + df.format(nowDatetime)); |
---|
129 | System.out.println("DATE:\t\t" + nowDate.getTime() + " -> " |
---|
130 | + df.format(nowDate)); |
---|
131 | System.out.println("TIME:\t\t" + nowTime.getTime() + " -> " |
---|
132 | + df.format(nowTime)); |
---|
133 | System.out.println("\n"); |
---|
134 | this.pstmt.setTimestamp(1, nowTstamp, calGMT); |
---|
135 | // have to use the same TimeZone as used to create or there will be |
---|
136 | // shift |
---|
137 | this.pstmt.setDate(2, nowDate, cal); |
---|
138 | this.pstmt.setTimestamp(3, nowDatetime, calGMT); |
---|
139 | // have to use the same TimeZone as used to create or there will be |
---|
140 | // shift |
---|
141 | this.pstmt.setTime(4, nowTime, cal); |
---|
142 | this.pstmt.execute(); |
---|
143 | |
---|
144 | this.pstmt.getUpdateCount(); |
---|
145 | this.pstmt.clearParameters(); |
---|
146 | this.rs = this.stmt.executeQuery("SELECT * from DATETEST"); |
---|
147 | |
---|
148 | java.sql.Date thenDate = null; |
---|
149 | |
---|
150 | while (this.rs.next()) { |
---|
151 | Timestamp thenTstamp = this.rs.getTimestamp(1, calGMT); |
---|
152 | thenDate = this.rs.getDate(2, cal); |
---|
153 | |
---|
154 | java.sql.Timestamp thenDatetime = this.rs.getTimestamp(3, calGMT); |
---|
155 | |
---|
156 | java.sql.Time thenTime = this.rs.getTime(4, cal); |
---|
157 | System.out |
---|
158 | .println("** Times with given calendar (retrieved from database) **\n"); |
---|
159 | System.out.println("TIMESTAMP:\t" + thenTstamp.getTime() + " -> " |
---|
160 | + df.format(thenTstamp)); |
---|
161 | System.out.println("DATE:\t\t" + thenDate.getTime() + " -> " |
---|
162 | + df.format(thenDate)); |
---|
163 | System.out.println("DATETIME:\t" + thenDatetime.getTime() + " -> " |
---|
164 | + df.format(thenDatetime)); |
---|
165 | System.out.println("TIME:\t\t" + thenTime.getTime() + " -> " |
---|
166 | + df.format(thenTime)); |
---|
167 | System.out.println("\n"); |
---|
168 | } |
---|
169 | |
---|
170 | this.rs.close(); |
---|
171 | this.rs = null; |
---|
172 | } |
---|
173 | |
---|
174 | public void testNanosParsing() throws SQLException { |
---|
175 | try { |
---|
176 | this.stmt.executeUpdate("DROP TABLE IF EXISTS testNanosParsing"); |
---|
177 | this.stmt |
---|
178 | .executeUpdate("CREATE TABLE testNanosParsing (dateIndex int, field1 VARCHAR(32))"); |
---|
179 | this.stmt |
---|
180 | .executeUpdate("INSERT INTO testNanosParsing VALUES (1, '1969-12-31 18:00:00.0'), " |
---|
181 | + "(2, '1969-12-31 18:00:00.000000090'), " |
---|
182 | + "(3, '1969-12-31 18:00:00.000000900'), " |
---|
183 | + "(4, '1969-12-31 18:00:00.000009000'), " |
---|
184 | + "(5, '1969-12-31 18:00:00.000090000'), " |
---|
185 | + "(6, '1969-12-31 18:00:00.000900000'), " |
---|
186 | + "(7, '1969-12-31 18:00:00.')"); |
---|
187 | |
---|
188 | this.rs = this.stmt |
---|
189 | .executeQuery("SELECT field1 FROM testNanosParsing ORDER BY dateIndex ASC"); |
---|
190 | assertTrue(this.rs.next()); |
---|
191 | assertTrue(this.rs.getTimestamp(1).getNanos() == 0); |
---|
192 | assertTrue(this.rs.next()); |
---|
193 | assertTrue(this.rs.getTimestamp(1).getNanos() + " != 90", this.rs |
---|
194 | .getTimestamp(1).getNanos() == 90); |
---|
195 | assertTrue(this.rs.next()); |
---|
196 | assertTrue(this.rs.getTimestamp(1).getNanos() + " != 900", this.rs |
---|
197 | .getTimestamp(1).getNanos() == 900); |
---|
198 | assertTrue(this.rs.next()); |
---|
199 | assertTrue(this.rs.getTimestamp(1).getNanos() + " != 9000", this.rs |
---|
200 | .getTimestamp(1).getNanos() == 9000); |
---|
201 | assertTrue(this.rs.next()); |
---|
202 | assertTrue(this.rs.getTimestamp(1).getNanos() + " != 90000", |
---|
203 | this.rs.getTimestamp(1).getNanos() == 90000); |
---|
204 | assertTrue(this.rs.next()); |
---|
205 | assertTrue(this.rs.getTimestamp(1).getNanos() + " != 900000", |
---|
206 | this.rs.getTimestamp(1).getNanos() == 900000); |
---|
207 | assertTrue(this.rs.next()); |
---|
208 | |
---|
209 | try { |
---|
210 | this.rs.getTimestamp(1); |
---|
211 | } catch (SQLException sqlEx) { |
---|
212 | assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx |
---|
213 | .getSQLState())); |
---|
214 | } |
---|
215 | } finally { |
---|
216 | this.stmt.executeUpdate("DROP TABLE IF EXISTS testNanosParsing"); |
---|
217 | } |
---|
218 | } |
---|
219 | |
---|
220 | /** |
---|
221 | * Tests the configurability of all-zero date/datetime/timestamp handling in |
---|
222 | * the driver. |
---|
223 | * |
---|
224 | * @throws Exception |
---|
225 | * if the test fails. |
---|
226 | */ |
---|
227 | public void testZeroDateBehavior() throws Exception { |
---|
228 | try { |
---|
229 | this.stmt |
---|
230 | .executeUpdate("DROP TABLE IF EXISTS testZeroDateBehavior"); |
---|
231 | this.stmt |
---|
232 | .executeUpdate("CREATE TABLE testZeroDateBehavior(fieldAsString VARCHAR(32), fieldAsDateTime DATETIME)"); |
---|
233 | this.stmt |
---|
234 | .executeUpdate("INSERT INTO testZeroDateBehavior VALUES ('0000-00-00 00:00:00', '0000-00-00 00:00:00')"); |
---|
235 | Properties props = new Properties(); |
---|
236 | props.setProperty("zeroDateTimeBehavior", "round"); |
---|
237 | Connection roundConn = getConnectionWithProps(props); |
---|
238 | Statement roundStmt = roundConn.createStatement(); |
---|
239 | this.rs = roundStmt |
---|
240 | .executeQuery("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior"); |
---|
241 | this.rs.next(); |
---|
242 | |
---|
243 | assertEquals("0001-01-01", this.rs.getDate(1).toString()); |
---|
244 | assertEquals("0001-01-01 00:00:00.0", |
---|
245 | new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.0", Locale.US).format(this.rs.getTimestamp(1))); |
---|
246 | assertEquals("0001-01-01", this.rs.getDate(2).toString()); |
---|
247 | assertEquals("0001-01-01 00:00:00.0", |
---|
248 | new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.0", Locale.US).format(this.rs.getTimestamp(2))); |
---|
249 | |
---|
250 | PreparedStatement roundPrepStmt = roundConn |
---|
251 | .prepareStatement("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior"); |
---|
252 | this.rs = roundPrepStmt.executeQuery(); |
---|
253 | this.rs.next(); |
---|
254 | |
---|
255 | assertEquals("0001-01-01", this.rs.getDate(1).toString()); |
---|
256 | assertEquals("0001-01-01 00:00:00.0", |
---|
257 | new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.0", Locale.US).format(this.rs.getTimestamp(1))); |
---|
258 | assertEquals("0001-01-01", this.rs.getDate(2).toString()); |
---|
259 | assertEquals("0001-01-01 00:00:00.0", |
---|
260 | new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.0", Locale.US).format(this.rs.getTimestamp(2))); |
---|
261 | |
---|
262 | props = new Properties(); |
---|
263 | props.setProperty("zeroDateTimeBehavior", "convertToNull"); |
---|
264 | Connection nullConn = getConnectionWithProps(props); |
---|
265 | Statement nullStmt = nullConn.createStatement(); |
---|
266 | this.rs = nullStmt |
---|
267 | .executeQuery("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior"); |
---|
268 | |
---|
269 | this.rs.next(); |
---|
270 | |
---|
271 | assertTrue(null == this.rs.getDate(1)); |
---|
272 | assertTrue(null == this.rs.getTimestamp(1)); |
---|
273 | assertTrue(null == this.rs.getDate(2)); |
---|
274 | assertTrue(null == this.rs.getTimestamp(2)); |
---|
275 | |
---|
276 | PreparedStatement nullPrepStmt = nullConn |
---|
277 | .prepareStatement("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior"); |
---|
278 | this.rs = nullPrepStmt.executeQuery(); |
---|
279 | |
---|
280 | this.rs.next(); |
---|
281 | |
---|
282 | assertTrue(null == this.rs.getDate(1)); |
---|
283 | assertTrue(null == this.rs.getTimestamp(1)); |
---|
284 | assertTrue(null == this.rs.getDate(2)); |
---|
285 | assertTrue(null == this.rs.getTimestamp(2)); |
---|
286 | assertTrue(null == this.rs.getString(2)); |
---|
287 | |
---|
288 | props = new Properties(); |
---|
289 | props.setProperty("zeroDateTimeBehavior", "exception"); |
---|
290 | Connection exceptionConn = getConnectionWithProps(props); |
---|
291 | Statement exceptionStmt = exceptionConn.createStatement(); |
---|
292 | this.rs = exceptionStmt |
---|
293 | .executeQuery("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior"); |
---|
294 | |
---|
295 | this.rs.next(); |
---|
296 | |
---|
297 | try { |
---|
298 | this.rs.getDate(1); |
---|
299 | fail("Exception should have been thrown when trying to retrieve invalid date"); |
---|
300 | } catch (SQLException sqlEx) { |
---|
301 | assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx |
---|
302 | .getSQLState())); |
---|
303 | } |
---|
304 | |
---|
305 | try { |
---|
306 | this.rs.getTimestamp(1); |
---|
307 | fail("Exception should have been thrown when trying to retrieve invalid date"); |
---|
308 | } catch (SQLException sqlEx) { |
---|
309 | assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx |
---|
310 | .getSQLState())); |
---|
311 | } |
---|
312 | |
---|
313 | try { |
---|
314 | this.rs.getDate(2); |
---|
315 | fail("Exception should have been thrown when trying to retrieve invalid date"); |
---|
316 | } catch (SQLException sqlEx) { |
---|
317 | assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx |
---|
318 | .getSQLState())); |
---|
319 | } |
---|
320 | |
---|
321 | try { |
---|
322 | this.rs.getTimestamp(2); |
---|
323 | fail("Exception should have been thrown when trying to retrieve invalid date"); |
---|
324 | } catch (SQLException sqlEx) { |
---|
325 | assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx |
---|
326 | .getSQLState())); |
---|
327 | } |
---|
328 | |
---|
329 | PreparedStatement exceptionPrepStmt = exceptionConn |
---|
330 | .prepareStatement("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior"); |
---|
331 | |
---|
332 | try { |
---|
333 | this.rs = exceptionPrepStmt.executeQuery(); |
---|
334 | this.rs.next(); |
---|
335 | this.rs.getDate(2); |
---|
336 | fail("Exception should have been thrown when trying to retrieve invalid date"); |
---|
337 | } catch (SQLException sqlEx) { |
---|
338 | assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx |
---|
339 | .getSQLState())); |
---|
340 | } |
---|
341 | |
---|
342 | } finally { |
---|
343 | this.stmt |
---|
344 | .executeUpdate("DROP TABLE IF EXISTS testZeroDateBehavior"); |
---|
345 | } |
---|
346 | } |
---|
347 | |
---|
348 | public void testReggieBug() throws Exception { |
---|
349 | try { |
---|
350 | this.stmt.executeUpdate("DROP TABLE IF EXISTS testReggieBug"); |
---|
351 | this.stmt.executeUpdate("CREATE TABLE testReggieBug (field1 DATE)"); |
---|
352 | |
---|
353 | PreparedStatement pStmt = this.conn |
---|
354 | .prepareStatement("INSERT INTO testReggieBug VALUES (?)"); |
---|
355 | pStmt.setDate(1, new Date(2004 - 1900, 07, 28)); |
---|
356 | pStmt.executeUpdate(); |
---|
357 | this.rs = this.stmt.executeQuery("SELECT * FROM testReggieBug"); |
---|
358 | this.rs.next(); |
---|
359 | System.out.println(this.rs.getDate(1)); |
---|
360 | this.rs = this.conn.prepareStatement("SELECT * FROM testReggieBug") |
---|
361 | .executeQuery(); |
---|
362 | this.rs.next(); |
---|
363 | System.out.println(this.rs.getDate(1)); |
---|
364 | |
---|
365 | } finally { |
---|
366 | this.stmt.executeUpdate("DROP TABLE IF EXISTS testReggieBug"); |
---|
367 | } |
---|
368 | } |
---|
369 | |
---|
370 | public void testNativeConversions() throws Exception { |
---|
371 | Timestamp ts = new Timestamp(System.currentTimeMillis()); |
---|
372 | Date dt = new Date(ts.getTime()); |
---|
373 | Time tm = new Time(ts.getTime()); |
---|
374 | |
---|
375 | createTable("testNativeConversions", "(time_field TIME, date_field DATE, datetime_field DATETIME, timestamp_field TIMESTAMP)"); |
---|
376 | this.pstmt = this.conn.prepareStatement("INSERT INTO testNativeConversions VALUES (?,?,?,?)"); |
---|
377 | this.pstmt.setTime(1, tm); |
---|
378 | this.pstmt.setDate(2, dt); |
---|
379 | this.pstmt.setTimestamp(3, ts); |
---|
380 | this.pstmt.setTimestamp(4, ts); |
---|
381 | this.pstmt.execute(); |
---|
382 | this.pstmt.close(); |
---|
383 | |
---|
384 | this.pstmt = this.conn.prepareStatement("SELECT time_field, date_field, datetime_field, timestamp_field FROM testNativeConversions"); |
---|
385 | this.rs = this.pstmt.executeQuery(); |
---|
386 | assertTrue(this.rs.next()); |
---|
387 | System.out.println(this.rs.getTime(1)); |
---|
388 | System.out.println(this.rs.getTime(2)); |
---|
389 | System.out.println(this.rs.getTime(3)); |
---|
390 | System.out.println(this.rs.getTime(4)); |
---|
391 | System.out.println(); |
---|
392 | System.out.println(this.rs.getDate(1)); |
---|
393 | System.out.println(this.rs.getDate(2)); |
---|
394 | System.out.println(this.rs.getDate(3)); |
---|
395 | System.out.println(this.rs.getDate(4)); |
---|
396 | System.out.println(); |
---|
397 | System.out.println(this.rs.getTimestamp(1)); |
---|
398 | System.out.println(this.rs.getTimestamp(2)); |
---|
399 | System.out.println(this.rs.getTimestamp(3)); |
---|
400 | System.out.println(this.rs.getTimestamp(4)); |
---|
401 | } |
---|
402 | |
---|
403 | } |
---|