|
1
30
31
32 package org.hsqldb.test;
33
34 import java.sql.Connection  ;
35 import java.sql.DriverManager ;
36 import java.sql.PreparedStatement ;
37 import java.sql.ResultSet ;
38 import java.sql.SQLException ;
39 import java.sql.Statement ;
40 import java.sql.Timestamp ;
41 import java.text.SimpleDateFormat ;
42 import java.util.Locale ;
43 import java.util.TimeZone ;
44
45 import junit.framework.Assert;
46 import junit.framework.TestCase;
47
48 public class TestTimestamp extends TestCase {
49
50 Connection conn = null;
51 TimeZone timeZone = null;
52 long id = 10;
53 String checkTimestamp = "2003-09-04 16:42:58";
54 String checkTimestampOra = "2003-09-04 16:42:58";
55
56 public TestTimestamp(String testName) {
57 super(testName);
58 }
59
60 private void initOracle() throws Exception {
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 {
71
72 Class.forName("org.hsqldb.jdbcDriver");
73
74 conn = DriverManager.getConnection("jdbc:hsqldb:mem:.", "sa", "");
76
77 conn.setAutoCommit(false);
78 }
79
80
105 private void checkExceptionTableExistsOracle(SQLException e) {}
106
107 private void checkExceptionTableExistsHsql(SQLException 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 {
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 e) {
128 checkExceptionTableExistsHsql(e);
129 }
130
131 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 }
153
154 private void dropAllTables() throws Exception {
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 nameTable = null;
165
166 private void doTest() throws Exception {
167
168 PreparedStatement ps = conn.prepareStatement("select max(T) T1 from "
169 + nameTable + " where ID=?");
170
171 ps.setLong(1, id);
172
173 ResultSet rs = ps.executeQuery();
174 boolean isRecordFound = rs.next();
175
176 Assert.assertTrue("Record in DB not found", isRecordFound);
177
178 Timestamp 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 df = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss",
188 Locale.ENGLISH);
189
190 df.setTimeZone(timeZone);
191
192 String tsString = df.format(ts);
193 String 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 cursTs = getCurrentCurs();
203
204 Assert.assertTrue("Timestamp curs not found", cursTs != null);
205
206 SimpleDateFormat df = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss",
207 Locale.ENGLISH);
208
209 df.setTimeZone(timeZone);
210
211 String 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 testTS =
221 new Timestamp (System.currentTimeMillis());
222
223 private void insertTestData() throws Exception {
224
225 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 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 sql) throws Exception {
249
250 Statement ps = conn.createStatement();
251
252 ps.execute(sql);
253 ps.close();
254
255 ps = null;
256 }
257
258 private void dropTestTable(String nameTableDrop) throws Exception {
259
260 String sql = "drop table " + nameTableDrop;
261 Statement ps = conn.createStatement();
262
263 try {
264 ps.execute(sql);
265 } catch (SQLException 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 getCurrentCurs() throws Exception {
280
281 long idCurrency = 134;
282 long idSite = 23;
283 String 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 ps = null;
288 ResultSet rs = null;
289 Timestamp 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 df =
320 new SimpleDateFormat ("dd.MM.yyyy HH:mm:ss.SSS",
321 Locale.ENGLISH);
322
323 df.setTimeZone(timeZone);
324
325 String st = df.format(stamp);
326
327 System.out.println("String ts in db " + st);
328 } catch (Throwable 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 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 |
Java API By Example, From Geeks To Geeks.