KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > hsqldb > test > TestTimestamp


1 /* Copyright (c) 2001-2005, The HSQL Development Group
2  * All rights reserved.
3  *
4  * Redistribution and use in source and binary forms, with or without
5  * modification, are permitted provided that the following conditions are met:
6  *
7  * Redistributions of source code must retain the above copyright notice, this
8  * list of conditions and the following disclaimer.
9  *
10  * Redistributions in binary form must reproduce the above copyright notice,
11  * this list of conditions and the following disclaimer in the documentation
12  * and/or other materials provided with the distribution.
13  *
14  * Neither the name of the HSQL Development Group nor the names of its
15  * contributors may be used to endorse or promote products derived from this
16  * software without specific prior written permission.
17  *
18  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21  * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
22  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29  */

30
31
32 package org.hsqldb.test;
33
34 import java.sql.Connection JavaDoc;
35 import java.sql.DriverManager JavaDoc;
36 import java.sql.PreparedStatement JavaDoc;
37 import java.sql.ResultSet JavaDoc;
38 import java.sql.SQLException JavaDoc;
39 import java.sql.Statement JavaDoc;
40 import java.sql.Timestamp JavaDoc;
41 import java.text.SimpleDateFormat JavaDoc;
42 import java.util.Locale JavaDoc;
43 import java.util.TimeZone JavaDoc;
44
45 import junit.framework.Assert;
46 import junit.framework.TestCase;
47
48 public class TestTimestamp extends TestCase {
49
50     Connection JavaDoc conn = null;
51     TimeZone JavaDoc timeZone = null;
52     long id = 10;
53     String JavaDoc checkTimestamp = "2003-09-04 16:42:58";
54     String JavaDoc checkTimestampOra = "2003-09-04 16:42:58";
55
56     public TestTimestamp(String JavaDoc testName) {
57         super(testName);
58     }
59
60     private void initOracle() throws Exception JavaDoc {
61
62         Class.forName("oracle.jdbc.driver.OracleDriver");
63
64         conn = DriverManager.getConnection(
65             "jdbc:oracle:thin:@oracle:1521:MILL", "aaa", "qqq");
66
67         conn.setAutoCommit(false);
68     }
69
70     private void initHypersonic() throws Exception JavaDoc {
71
72         Class.forName("org.hsqldb.jdbcDriver");
73
74 // conn = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/yourtest", "sa", "");
75
conn = DriverManager.getConnection("jdbc:hsqldb:mem:.", "sa", "");
76
77         conn.setAutoCommit(false);
78     }
79
80 /*
81     public void testOracle() throws Exception {
82
83         nameTable = "AAA_TEST";
84         checkTimestamp = checkTimestampOra;
85
86         setTimeZone();
87         initOracle();
88         dropAllTables();
89         createTestTable("CREATE TABLE " + nameTable + "(T DATE, id DECIMAL)");
90         createTestTable(
91             "create table \"CASH_CURRENCY\" ( \"ID_CURRENCY\" DECIMAL NOT NULL , \"ID_SITE\" DECIMAL )");
92         createTestTable(
93             "create table \"CASH_CURR_VALUE\" ( \"ID_CURRENCY\" DECIMAL NOT NULL , \"DATE_CHANGE\" DATE DEFAULT sysdate, \"CURS\" DECIMAL, \"ID_CURVAL\" DECIMAL NOT NULL )");
94         insertTestData();
95         conn.createStatement().executeUpdate(
96             "INSERT INTO \"CASH_CURR_VALUE\" VALUES(134, to_date('2003-09-04 16:42:58', 'yyyy-mm-dd hh24:mi:ss'),1.01,155)");
97         conn.createStatement().executeUpdate(
98             "INSERT INTO \"CASH_CURR_VALUE\" VALUES(135, to_date('"
99             + checkTimestamp + "', 'yyyy-mm-dd hh24:mi:ss'),34.51,156)");
100         doTest();
101
102 // dropTestTable();
103     }
104 */

105     private void checkExceptionTableExistsOracle(SQLException JavaDoc e) {}
106
107     private void checkExceptionTableExistsHsql(SQLException JavaDoc e) {
108
109         Assert.assertTrue("Error code of SQLException is wrong",
110                           e.getErrorCode()
111                           == -org.hsqldb.Trace.TABLE_ALREADY_EXISTS);
112     }
113
114     public void testHypersonic() throws Exception JavaDoc {
115
116         nameTable = "\"AAA_TEST\"";
117
118         setTimeZone();
119         initHypersonic();
120         dropAllTables();
121         createTestTable("CREATE TABLE " + nameTable
122                         + " (T timestamp, id DECIMAL)");
123
124         try {
125             createTestTable("CREATE TABLE " + nameTable
126                             + " (T timestamp, id DECIMAL)");
127         } catch (SQLException JavaDoc e) {
128             checkExceptionTableExistsHsql(e);
129         }
130
131 // conn.createStatement().execute("create table \"SITE_LIST_SITE\" ( \"ID_SITE\" DECIMAL NOT NULL , \"ID_FIRM\" DECIMAL, \"DEF_LANGUAGE\" VARCHAR NOT NULL , \"DEF_COUNTRY\" VARCHAR NOT NULL , \"DEF_VARIANT\" VARCHAR, \"NAME_SITE\" VARCHAR NOT NULL , \"ADMIN_EMAIL\" VARCHAR, \"IS_CSS_DYNAMIC\" DECIMAL DEFAULT 0 NOT NULL , \"CSS_FILE\" VARCHAR DEFAULT '/front_styles.css', \"IS_REGISTER_ALLOWED\" DECIMAL DEFAULT 1 NOT NULL , \"ORDER_EMAIL\" VARCHAR, \"IS_ACTIVATE_EMAIL_ORDER\" DECIMAL DEFAULT 0 NOT NULL , CONSTRAINT ID_SITE_SLS_PK PRIMARY KEY ( ID_SITE ) )");
132
// conn.createStatement().execute("create table \"SITE_VIRTUAL_HOST\" ( \"ID_SITE_VIRTUAL_HOST\" DECIMAL NOT NULL , \"ID_SITE\" DECIMAL NOT NULL , \"NAME_VIRTUAL_HOST\" VARCHAR NOT NULL , CONSTRAINT ID_VIRT_HST_SVH_PK PRIMARY KEY ( ID_SITE_VIRTUAL_HOST ) )");
133
// conn.createStatement().execute("create table \"SITE_SUPPORT_LANGUAGE\" ( \"ID_SITE_SUPPORT_LANGUAGE\" DECIMAL NOT NULL , \"ID_SITE\" DECIMAL, \"ID_LANGUAGE\" DECIMAL, \"CUSTOM_LANGUAGE\" VARCHAR, \"NAME_CUSTOM_LANGUAGE\" VARCHAR, CONSTRAINT ID_SITE_LNG_SSL_PK PRIMARY KEY ( ID_SITE_SUPPORT_LANGUAGE ) )");
134
// conn.createStatement().execute("create table \"CASH_CURRENCY\" ( \"ID_CURRENCY\" DECIMAL NOT NULL , \"CURRENCY\" VARCHAR, \"IS_USED\" DECIMAL, \"NAME_CURRENCY\" VARCHAR, \"IS_USE_STANDART\" DECIMAL DEFAULT 0, \"ID_STANDART_CURS\" DECIMAL, \"ID_SITE\" DECIMAL, \"PERCENT_VALUE\" DECIMAL, CONSTRAINT PK_CURRENCY PRIMARY KEY ( ID_CURRENCY ) )");
135
// conn.createStatement().execute("create table \"CASH_CURR_VALUE\" ( \"ID_CURRENCY\" DECIMAL NOT NULL , \"DATE_CHANGE\" TIMESTAMP DEFAULT sysdate, \"CURS\" DECIMAL, \"ID_CURVAL\" DECIMAL NOT NULL , CONSTRAINT ID_CURVAL_CCV_PK PRIMARY KEY ( ID_CURVAL ) )");
136
createTestTable(
137             "create table \"CASH_CURRENCY\" ( \"ID_CURRENCY\" bigint NOT NULL , \"ID_SITE\" bigint )");
138         createTestTable(
139             "create table \"CASH_CURR_VALUE\" ( \"ID_CURRENCY\" bigint NOT NULL , \"DATE_CHANGE\" TIMESTAMP DEFAULT sysdate, \"CURS\" bigint, \"ID_CURVAL\" DECIMAL NOT NULL )");
140         insertTestData();
141         conn.createStatement().executeUpdate(
142             "INSERT INTO \"CASH_CURR_VALUE\" VALUES(134,'2003-09-04 16:42:58.729',1.01,155)");
143         conn.createStatement().executeUpdate(
144             "INSERT INTO \"CASH_CURR_VALUE\" VALUES(135,'" + checkTimestamp
145             + "',34.51,156)");
146         doTest();
147         conn.close();
148
149         conn = null;
150
151 // dropTestTable();
152
}
153
154     private void dropAllTables() throws Exception JavaDoc {
155
156         dropTestTable(nameTable);
157         dropTestTable("\"SITE_LIST_SITE\"");
158         dropTestTable("\"SITE_VIRTUAL_HOST\"");
159         dropTestTable("\"SITE_SUPPORT_LANGUAGE\"");
160         dropTestTable("\"CASH_CURRENCY\"");
161         dropTestTable("\"CASH_CURR_VALUE\"");
162     }
163
164     private String JavaDoc nameTable = null;
165
166     private void doTest() throws Exception JavaDoc {
167
168         PreparedStatement JavaDoc ps = conn.prepareStatement("select max(T) T1 from "
169             + nameTable + " where ID=?");
170
171         ps.setLong(1, id);
172
173         ResultSet JavaDoc rs = ps.executeQuery();
174         boolean isRecordFound = rs.next();
175
176         Assert.assertTrue("Record in DB not found", isRecordFound);
177
178         Timestamp JavaDoc ts = rs.getTimestamp("T1");
179
180         ps.close();
181
182         ps = null;
183
184         {
185             Assert.assertTrue("Timestamp not found", ts != null);
186
187             SimpleDateFormat JavaDoc df = new SimpleDateFormat JavaDoc("yyyy-MM-dd HH:mm:ss",
188                 Locale.ENGLISH);
189
190             df.setTimeZone(timeZone);
191
192             String JavaDoc tsString = df.format(ts);
193             String JavaDoc testTsString = df.format(testTS);
194
195             System.out.println("db timestamp " + tsString
196                                + ", test timestamp " + testTsString);
197             Assert.assertTrue("Timestamp is wrong",
198                               tsString.equals(testTsString));
199         }
200
201         {
202             Timestamp JavaDoc cursTs = getCurrentCurs();
203
204             Assert.assertTrue("Timestamp curs not found", cursTs != null);
205
206             SimpleDateFormat JavaDoc df = new SimpleDateFormat JavaDoc("yyyy-MM-dd HH:mm:ss",
207                 Locale.ENGLISH);
208
209             df.setTimeZone(timeZone);
210
211             String JavaDoc tsString = df.format(cursTs);
212
213             Assert.assertTrue("Timestamp curs is wrong",
214                               tsString.equals(checkTimestamp));
215             System.out.println("db timestamp curs " + tsString
216                                + ", test timestamp curs " + checkTimestamp);
217         }
218     }
219
220     private static Timestamp JavaDoc testTS =
221         new Timestamp JavaDoc(System.currentTimeMillis());
222
223     private void insertTestData() throws Exception JavaDoc {
224
225 // conn.createStatement().executeUpdate("INSERT INTO \"SITE_LIST_SITE\" VALUES(23,1,'ru','RU',NULL,'\u041f\u0440\u043e\u0431\u043d\u044b\u0439 \u0441\u0430\u0439\u0442',NULL,0,'''/front_styles.css''',1,NULL,0)");
226
// conn.createStatement().executeUpdate("INSERT INTO \"SITE_VIRTUAL_HOST\" VALUES(36,23,'test-host')");
227
// conn.createStatement().executeUpdate("INSERT INTO \"SITE_SUPPORT_LANGUAGE\" VALUES(115,23,1,'ru_RU','ru_RU')");
228
// conn.createStatement().executeUpdate("INSERT INTO \"CASH_CURRENCY\" VALUES(134,'\u0420\u0443\u0431',1,'\u0420\u0443\u0431',0,3,23,0.0)");
229
// conn.createStatement().executeUpdate("INSERT INTO \"CASH_CURRENCY\" VALUES(135,'EURO',1,'EURO',0,7,23,0.0)");
230
conn.createStatement().executeUpdate(
231             "INSERT INTO \"CASH_CURRENCY\" VALUES(134,23)");
232         conn.createStatement().executeUpdate(
233             "INSERT INTO \"CASH_CURRENCY\" VALUES(135,23)");
234
235         PreparedStatement JavaDoc ps = conn.prepareStatement("insert into "
236             + nameTable + "(T, ID) values (?, ?)");
237
238         ps.setTimestamp(1, testTS);
239         ps.setLong(2, id);
240         ps.executeUpdate();
241         ps.close();
242
243         ps = null;
244
245         conn.commit();
246     }
247
248     private void createTestTable(String JavaDoc sql) throws Exception JavaDoc {
249
250         Statement JavaDoc ps = conn.createStatement();
251
252         ps.execute(sql);
253         ps.close();
254
255         ps = null;
256     }
257
258     private void dropTestTable(String JavaDoc nameTableDrop) throws Exception JavaDoc {
259
260         String JavaDoc sql = "drop table " + nameTableDrop;
261         Statement JavaDoc ps = conn.createStatement();
262
263         try {
264             ps.execute(sql);
265         } catch (SQLException JavaDoc e) {}
266
267         ps.close();
268
269         ps = null;
270     }
271
272     private void setTimeZone() {
273
274         timeZone = TimeZone.getTimeZone("Asia/Irkutsk");
275
276         TimeZone.setDefault(timeZone);
277     }
278
279     private Timestamp JavaDoc getCurrentCurs() throws Exception JavaDoc {
280
281         long idCurrency = 134;
282         long idSite = 23;
283         String JavaDoc sql_ =
284             "select max(f.DATE_CHANGE) LAST_DATE "
285             + "from CASH_CURR_VALUE f, CASH_CURRENCY b "
286             + "where f.ID_CURRENCY=b.ID_CURRENCY and b.ID_SITE=? and f.ID_CURRENCY=? ";
287         PreparedStatement JavaDoc ps = null;
288         ResultSet JavaDoc rs = null;
289         Timestamp JavaDoc stamp = null;
290
291         try {
292             ps = conn.prepareStatement(sql_);
293
294             ps.setLong(1, idSite);
295             ps.setLong(2, idCurrency);
296
297             rs = ps.executeQuery();
298
299             if (rs.next()) {
300                 stamp = rs.getTimestamp("LAST_DATE");
301             } else {
302                 return null;
303             }
304         } finally {
305             rs.close();
306             ps.close();
307
308             rs = null;
309             ps = null;
310         }
311
312         System.out.println("ts in db " + stamp);
313
314         if (stamp == null) {
315             return null;
316         }
317
318         try {
319             SimpleDateFormat JavaDoc df =
320                 new SimpleDateFormat JavaDoc("dd.MM.yyyy HH:mm:ss.SSS",
321                                      Locale.ENGLISH);
322
323             df.setTimeZone(timeZone);
324
325             String JavaDoc st = df.format(stamp);
326
327             System.out.println("String ts in db " + st);
328         } catch (Throwable JavaDoc th) {
329             System.out.println("Error get timestamp " + th.toString());
330         }
331
332         sql_ = "select a.ID_CURRENCY, a.DATE_CHANGE, a.CURS "
333                + "from CASH_CURR_VALUE a, CASH_CURRENCY b "
334                + "where a.ID_CURRENCY=b.ID_CURRENCY and "
335                + "b.ID_SITE=? and " + "a.ID_CURRENCY=? and "
336                + "DATE_CHANGE = ?";
337         ps = null;
338         rs = null;
339
340         double curs;
341         Timestamp JavaDoc tsCurs = null;
342         long idCurrencyCurs;
343
344         try {
345             ps = conn.prepareStatement(sql_);
346
347             ps.setLong(1, idSite);
348             ps.setLong(2, idCurrency);
349             ps.setTimestamp(3, stamp);
350
351             rs = ps.executeQuery();
352
353             if (rs.next()) {
354                 curs = rs.getDouble("CURS");
355                 tsCurs = rs.getTimestamp("DATE_CHANGE");
356             }
357
358             return tsCurs;
359         } finally {
360             rs.close();
361             ps.close();
362
363             rs = null;
364             ps = null;
365         }
366     }
367 }
368
Popular Tags