|
1
30
31
32 package org.hsqldb.test;
33
34 import java.sql.Connection  ;
35 import java.sql.DatabaseMetaData ;
36 import java.sql.PreparedStatement ;
37 import java.sql.ResultSet ;
38 import java.sql.ResultSetMetaData ;
39 import java.sql.SQLException ;
40 import java.sql.Statement ;
41 import java.sql.Types ;
42
43 import junit.framework.TestCase;
44 import junit.framework.TestResult;
45
46
50 public class TestSql extends TestBase {
51
52 Statement stmnt;
53 PreparedStatement pstmnt;
54 Connection connection;
55 String getColumnName = "false";
56
57 public TestSql(String name) {
58 super(name);
59 }
60
61 protected void setUp() {
62
63 super.setUp();
64
65 try {
66 connection = super.newConnection();
67 stmnt = connection.createStatement();
68 } catch (Exception e) {}
69 }
70
71 public void testMetaData() {
72
73 String ddl0 =
74 "DROP TABLE ADDRESSBOOK IF EXISTS; DROP TABLE ADDRESSBOOK_CATEGORY IF EXISTS; DROP TABLE USER IF EXISTS;";
75 String ddl1 =
76 "CREATE TABLE USER(USER_ID INTEGER NOT NULL PRIMARY KEY,LOGIN_ID VARCHAR(128) NOT NULL,USER_NAME VARCHAR(254) DEFAULT ' ' NOT NULL,CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UPDATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,LAST_ACCESS_DATE TIMESTAMP,CONSTRAINT IXUQ_LOGIN_ID0 UNIQUE(LOGIN_ID))";
77 String ddl2 =
78 "CREATE TABLE ADDRESSBOOK_CATEGORY(USER_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,CATEGORY_NAME VARCHAR(60) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK_CATEGORY PRIMARY KEY(USER_ID,CATEGORY_ID),CONSTRAINT FK_ADRBKCAT1 FOREIGN KEY(USER_ID) REFERENCES USER(USER_ID) ON DELETE CASCADE)";
79 String ddl3 =
80 "CREATE TABLE ADDRESSBOOK(USER_ID INTEGER NOT NULL,ADDRESSBOOK_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,FIRST VARCHAR(64) DEFAULT '' NOT NULL,LAST VARCHAR(64) DEFAULT '' NOT NULL,NOTE VARCHAR(128) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK PRIMARY KEY(USER_ID,ADDRESSBOOK_ID),CONSTRAINT FK_ADRBOOK1 FOREIGN KEY(USER_ID,CATEGORY_ID) REFERENCES ADDRESSBOOK_CATEGORY(USER_ID,CATEGORY_ID) ON DELETE CASCADE)";
81 String result1 = "1";
82 String result2 = "2";
83 String result3 = "3";
84 String result4 = "4";
85 String result5 = "5";
86
87 try {
88 stmnt.execute(ddl0);
89 stmnt.execute(ddl1);
90 stmnt.execute(ddl2);
91 stmnt.execute(ddl3);
92
93 DatabaseMetaData md = connection.getMetaData();
94
95 {
96
97 System.out.println(md.getDatabaseProductName());
100 System.out.println(md.getDatabaseProductVersion());
101 System.out.println(md.getDefaultTransactionIsolation());
102 System.out.println(md.getDriverMajorVersion());
103 System.out.println(md.getDriverMinorVersion());
104 System.out.println(md.getDriverName());
105 System.out.println(md.getDriverVersion());
106 System.out.println(md.getExtraNameCharacters());
107 System.out.println(md.getIdentifierQuoteString());
108
109 System.out.println(md.getMaxBinaryLiteralLength());
112 System.out.println(md.getMaxCatalogNameLength());
113 System.out.println(md.getMaxColumnsInGroupBy());
114 System.out.println(md.getMaxColumnsInIndex());
115 System.out.println(md.getMaxColumnsInOrderBy());
116 System.out.println(md.getMaxColumnsInSelect());
117 System.out.println(md.getMaxColumnsInTable());
118 System.out.println(md.getMaxConnections());
119 System.out.println(md.getMaxCursorNameLength());
120 System.out.println(md.getMaxIndexLength());
121 System.out.println(md.getMaxProcedureNameLength());
122 System.out.println(md.getMaxRowSize());
123 System.out.println(md.getMaxSchemaNameLength());
124 System.out.println(md.getMaxStatementLength());
125 System.out.println(md.getMaxStatements());
126 System.out.println(md.getMaxTableNameLength());
127 System.out.println(md.getMaxUserNameLength());
128 System.out.println(md.getNumericFunctions());
129 System.out.println(md.getProcedureTerm());
130
131 System.out.println(md.getSchemaTerm());
133 System.out.println(md.getSearchStringEscape());
134 System.out.println(md.getSQLKeywords());
135
136 System.out.println(md.getStringFunctions());
138 System.out.println(md.getSystemFunctions());
139 System.out.println(md.getTimeDateFunctions());
140 System.out.println(md.getURL());
141 System.out.println(md.getUserName());
142 System.out.println(DatabaseMetaData.importedKeyCascade);
143 System.out.println(md.isCatalogAtStart());
144 System.out.println(md.isReadOnly());
145
146 ResultSet rs;
147
148 rs = md.getPrimaryKeys(null, null, "USER");
149
150 ResultSetMetaData rsmd = rs.getMetaData();
151 String result0 = "";
152
153 for (; rs.next(); ) {
154 for (int i = 0; i < rsmd.getColumnCount(); i++) {
155 result0 += rs.getString(i + 1) + ":";
156 }
157
158 result0 += "\n";
159 }
160
161 rs.close();
162 System.out.println(result0);
163 }
164
165 {
166 ResultSet rs;
167
168 rs = md.getBestRowIdentifier(null, null, "USER", 0, true);
169
170 ResultSetMetaData rsmd = rs.getMetaData();
171 String result0 = "";
172
173 for (; rs.next(); ) {
174 for (int i = 0; i < rsmd.getColumnCount(); i++) {
175 result0 += rs.getString(i + 1) + ":";
176 }
177
178 result0 += "\n";
179 }
180
181 rs.close();
182 System.out.println(result0);
183 }
184
185 {
186 ResultSet rs = md.getImportedKeys(null, null, "ADDRESSBOOK");
187 ResultSetMetaData rsmd = rs.getMetaData();
188
189 result1 = "";
190
191 for (; rs.next(); ) {
192 for (int i = 0; i < rsmd.getColumnCount(); i++) {
193 result1 += rs.getString(i + 1) + ":";
194 }
195
196 result1 += "\n";
197 }
198
199 rs.close();
200 System.out.println(result1);
201 }
202
203 {
204 ResultSet rs = md.getCrossReference(null, null,
205 "ADDRESSBOOK_CATEGORY",
206 null, null,
207 "ADDRESSBOOK");
208 ResultSetMetaData rsmd = rs.getMetaData();
209
210 result2 = "";
211
212 for (; rs.next(); ) {
213 for (int i = 0; i < rsmd.getColumnCount(); i++) {
214 result2 += rs.getString(i + 1) + ":";
215 }
216
217 result2 += "\n";
218 }
219
220 rs.close();
221 System.out.println(result2);
222 }
223
224 {
225 ResultSet rs = md.getExportedKeys(null, null, "USER");
226 ResultSetMetaData rsmd = rs.getMetaData();
227
228 result3 = "";
229
230 for (; rs.next(); ) {
231 for (int i = 0; i < rsmd.getColumnCount(); i++) {
232 result3 += rs.getString(i + 1) + ":";
233 }
234
235 result3 += "\n";
236 }
237
238 rs.close();
239 System.out.println(result3);
240 }
241
242 {
243 ResultSet rs = md.getCrossReference(null, null, "USER", null,
244 null,
245 "ADDRESSBOOK_CATEGORY");
246 ResultSetMetaData rsmd = rs.getMetaData();
247
248 result4 = "";
249
250 for (; rs.next(); ) {
251 for (int i = 0; i < rsmd.getColumnCount(); i++) {
252 result4 += rs.getString(i + 1) + ":";
253 }
254
255 result4 += "\n";
256 }
257
258 rs.close();
259 System.out.println(result4);
260 }
261
262 {
263 stmnt.execute("DROP TABLE T IF EXISTS;");
264 stmnt.executeQuery(
265 "CREATE TABLE T (I IDENTITY, A CHAR(20), B CHAR(20));");
266 stmnt.executeQuery(
267 "INSERT INTO T VALUES (NULL, 'get_column_name', '"
268 + getColumnName + "');");
269
270 ResultSet rs = stmnt.executeQuery(
271 "SELECT I, A, B, A \"aliasA\", B \"aliasB\" FROM T;");
272 ResultSetMetaData rsmd = rs.getMetaData();
273
274 result5 = "";
275
276 for (; rs.next(); ) {
277 for (int i = 0; i < rsmd.getColumnCount(); i++) {
278 result5 += rsmd.getColumnName(i + 1) + ":"
279 + rs.getString(i + 1) + ":";
280 }
281
282 result5 += "\n";
283 }
284
285 rs.close();
286
287 rs = stmnt.executeQuery(
288 "SELECT I, A, B, A \"aliasA\", B \"aliasB\" FROM T;");;
289 rsmd = rs.getMetaData();
290
291 for (; rs.next(); ) {
292 for (int i = 0; i < rsmd.getColumnCount(); i++) {
293 result5 += rsmd.getColumnLabel(i + 1) + ":"
294 + rs.getString(i + 1) + ":";
295 }
296
297 result5 += "\n";
298 }
299
300 System.out.println(result5);
301 System.out.println("first column identity: "
302 + rsmd.isAutoIncrement(1));
303 rsmd.isCaseSensitive(1);
304 rsmd.isCurrency(1);
305 rsmd.isDefinitelyWritable(1);
306 rsmd.isNullable(1);
307 rsmd.isReadOnly(1);
308 rsmd.isSearchable(1);
309 rsmd.isSigned(1);
310 rsmd.isWritable(1);
311 rs.close();
312
313 pstmnt = connection.prepareStatement(
315 "INSERT INTO T VALUES (?,?,?)");
316
317 pstmnt.setString(1, null);
318 pstmnt.setString(2, "test");
319 pstmnt.setString(3, "test2");
320 pstmnt.executeUpdate();
321
322 pstmnt = connection.prepareStatement("call identity()");
323
324 ResultSet rsi = pstmnt.executeQuery();
325
326 rsi.next();
327
328 int identity = rsi.getInt(1);
329
330 System.out.println("call identity(): " + identity);
331 rsi.close();
332 }
333 } catch (SQLException e) {
334 fail(e.getMessage());
335 }
336
337 System.out.println("testMetaData complete");
338
339 assertEquals(result1, result2);
341 assertEquals(result3, result4);
342 }
343
344
354 public void testDoubleNaN() {
355
356 double value = 0;
357 boolean wasEqual = false;
358 String message = "DB operation completed";
359 String ddl1 =
360 "DROP TABLE t1 IF EXISTS;"
361 + "CREATE TABLE t1 ( d DECIMAL, f DOUBLE, l BIGINT, i INTEGER, s SMALLINT, t TINYINT, "
362 + "dt DATE DEFAULT CURRENT_DATE, ti TIME DEFAULT CURRENT_TIME, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP );";
363
364 try {
365 stmnt.execute(ddl1);
366
367 PreparedStatement ps = connection.prepareStatement(
368 "INSERT INTO t1 (d,f,l,i,s,t,dt,ti,ts) VALUES (?,?,?,?,?,?,?,?,?)");
369
370 ps.setString(1, "0.2");
371 ps.setDouble(2, 0.2);
372 ps.setLong(3, java.lang.Long.MAX_VALUE);
373 ps.setInt(4, Integer.MAX_VALUE);
374 ps.setInt(5, Short.MAX_VALUE);
375 ps.setInt(6, 0);
376 ps.setDate(7, new java.sql.Date (System.currentTimeMillis()));
377 ps.setTime(8, new java.sql.Time (System.currentTimeMillis()));
378 ps.setTimestamp(
379 9, new java.sql.Timestamp (System.currentTimeMillis()));
380 ps.execute();
381 ps.setInt(1, 0);
382 ps.setDouble(2, java.lang.Double.NaN);
383 ps.setLong(3, java.lang.Long.MIN_VALUE);
384 ps.setInt(4, Integer.MIN_VALUE);
385 ps.setInt(5, Short.MIN_VALUE);
386 ps.setInt(6, 0);
387
388 ps.setTimestamp(
390 7, new java.sql.Timestamp (System.currentTimeMillis() + 1));
391 ps.setTime(8, new java.sql.Time (System.currentTimeMillis() + 1));
392 ps.setDate(9, new java.sql.Date (System.currentTimeMillis() + 1));
393 ps.execute();
394
395 ps.setInt(1, 0);
397 ps.setDouble(2, java.lang.Double.POSITIVE_INFINITY);
398 ps.setInt(4, Integer.MIN_VALUE);
399
400 ps.setObject(5, Boolean.TRUE);
402 ps.setBoolean(5, true);
403 ps.setObject(5, new Short ((short) 2), Types.SMALLINT);
404 ps.setObject(6, new Integer (2), Types.TINYINT);
405
406 ps.setObject(7, new java.sql.Date (System.currentTimeMillis()
408 + 2));
409 ps.setObject(8, new java.sql.Time (System.currentTimeMillis()
410 + 2));
411 ps.setObject(9, new java.sql.Timestamp (System.currentTimeMillis()
412 + 2));
413 ps.execute();
414 ps.setObject(1, new Float (0), Types.INTEGER);
415 ps.setObject(4, new Float (1), Types.INTEGER);
416 ps.setDouble(2, java.lang.Double.NEGATIVE_INFINITY);
417 ps.execute();
418
419 ResultSet rs =
420 stmnt.executeQuery("SELECT d, f, l, i, s*2, t FROM t1");
421 boolean result = rs.next();
422
423 value = rs.getDouble(2);
424
425 int integerValue = rs.getInt(4);
427
428 if (rs.next()) {
429 value = rs.getDouble(2);
430 wasEqual = Double.isNaN(value);
431 integerValue = rs.getInt(4);
432
433 integerValue = rs.getInt(1);
436 }
437
438 if (rs.next()) {
439 value = rs.getDouble(2);
440 wasEqual = wasEqual && value == Double.POSITIVE_INFINITY;
441 }
442
443 if (rs.next()) {
444 value = rs.getDouble(2);
445 wasEqual = wasEqual && value == Double.NEGATIVE_INFINITY;
446 }
447
448 rs = stmnt.executeQuery("SELECT MAX(i) FROM t1");
449
450 if (rs.next()) {
451 int max = rs.getInt(1);
452
453 System.out.println("Max value for i: " + max);
454 }
455
456 {
457 stmnt.execute("drop table CDTYPE if exists");
458
459 rs = stmnt.executeQuery(
461 "CREATE TABLE cdType (ID INTEGER NOT NULL, name VARCHAR(50), PRIMARY KEY(ID))");
462 rs = stmnt.executeQuery("SELECT MAX(ID) FROM cdType");
463
464 if (rs.next()) {
465 int max = rs.getInt(1);
466
467 System.out.println("Max value for ID: " + max);
468 } else {
469 System.out.println("Max value for ID not returned");
470 }
471
472 stmnt.executeUpdate(
473 "INSERT INTO cdType VALUES (10,'Test String');");
474 stmnt.executeQuery("CALL IDENTITY();");
475
476 try {
477 stmnt.executeUpdate(
478 "INSERT INTO cdType VALUES (10,'Test String');");
479 } catch (SQLException e1) {
480 stmnt.execute("ROLLBACK");
481 connection.rollback();
482 }
483 }
484 } catch (SQLException e) {
485 fail(e.getMessage());
486 }
487
488 System.out.println("testDoubleNaN complete");
489
490 assertEquals(true, wasEqual);
492 }
493
494 public void testAny() {
495
496 try {
497 String ddl =
498 "drop table PRICE_RELATE_USER_ORDER_V2 if exists;"
499 + "create table PRICE_RELATE_USER_ORDER_V2 "
500 + "(ID_ORDER_V2 BIGINT, ID_USER NUMERIC, DATE_CREATE TIMESTAMP)";
501 String sql = "insert into PRICE_RELATE_USER_ORDER_V2 "
502 + "(ID_ORDER_V2, ID_USER, DATE_CREATE) " + "values "
503 + "(?, ?, ?)";
504 Statement st = connection.createStatement();
505
506 st.execute(ddl);
507
508 PreparedStatement ps = connection.prepareStatement(sql);
509
510 ps.setLong(1, 1);
511 ps.setNull(2, Types.NUMERIC);
512 ps.setTimestamp(
513 3, new java.sql.Timestamp (System.currentTimeMillis()));
514 ps.execute();
515 } catch (SQLException e) {
516 e.printStackTrace();
517 System.out.println("TestSql.testAny() error: " + e.getMessage());
518 }
519
520 System.out.println("testAny complete");
521 }
522
523
526 public void testBinds() {
527
528 try {
529 PreparedStatement pstmt =
530 connection.prepareStatement("drop table test if exists");
531
532 pstmt.execute();
533
534 pstmt =
535 connection.prepareStatement("create table test (id integer)");
536
537 pstmt.execute();
538
539 pstmt =
540 connection.prepareStatement("insert into test values (10)");
541
542 pstmt.execute();
543
544 pstmt =
545 connection.prepareStatement("insert into test values (20)");
546
547 pstmt.execute();
548
549 pstmt = connection.prepareStatement(
550 "select count(*) from test where ? is null");
551
552 pstmt.setString(1, "hello");
553
554 ResultSet rs = pstmt.executeQuery();
555
556 rs.next();
557
558 int count = rs.getInt(1);
559
560 assertEquals(0, count);
561
562 pstmt =
563 connection.prepareStatement("select limit ? 1 id from test");
564
565 pstmt.setInt(1, 0);
566
567 rs = pstmt.executeQuery();
568
569 rs.next();
570
571 count = rs.getInt(1);
572
573 assertEquals(10, count);
574 pstmt.setInt(1, 1);
575
576 rs = pstmt.executeQuery();
577
578 rs.next();
579
580 count = rs.getInt(1);
581
582 assertEquals(20, count);
583 } catch (SQLException e) {
584 e.printStackTrace();
585 System.out.println("TestSql.testBinds() error: "
586 + e.getMessage());
587 }
588 }
589
590 public void testX1() {
592
593 String tableDDL =
594 "create table lo_attribute ( "
595 + "learningid varchar(15) not null, "
596 + "ordering integer not null,"
597 + "attribute_value_data varchar(85) null,"
598 + "constraint PK_LO_ATTR primary key (learningid, ordering))";
599
600 try {
601 Statement stmt = connection.createStatement();
602
603 stmt.execute("drop table lo_attribute if exists");
604 stmt.execute(tableDDL);
605 stmt.execute(
606 "insert into lo_attribute values('abcd', 10, 'cdef')");
607 stmt.execute(
608 "insert into lo_attribute values('bcde', 20, 'cdef')");
609 } catch (SQLException e) {
610 assertEquals(0, 1);
611 }
612
613 try {
614 String prepared =
615 "update lo_attribute set "
616 + " ordering = (ordering - 1) where ordering > ?";
617 PreparedStatement ps = connection.prepareStatement(prepared);
618
619 ps.setInt(1, 10);
620 ps.execute();
621 } catch (SQLException e) {
622 assertEquals(0, 1);
623 }
624
625 try {
626 connection.setAutoCommit(false);
627
628 java.sql.Savepoint savepoint =
629 connection.setSavepoint("savepoint");
630
631 connection.createStatement().executeQuery("CALL true;");
632 connection.rollback(savepoint);
633 } catch (SQLException e) {
634 assertEquals(0, 1);
635 }
636 }
637
638
644 public void testUnionColumnTypes() {
645
646 try {
647 Connection conn = newConnection();
648 Statement stmt = conn.createStatement();
649
650 stmt.execute("DROP TABLE test1 IF EXISTS");
651 stmt.execute("DROP TABLE test2 IF EXISTS");
652 stmt.execute("CREATE TABLE test1 (id int, b1 boolean)");
653 stmt.execute("CREATE TABLE test2 (id int)");
654 stmt.execute("INSERT INTO test1 VALUES(1,true)");
655 stmt.execute("INSERT INTO test2 VALUES(2)");
656
657 ResultSet rs = stmt.executeQuery(
658 "select id,null as b1 from test2 union select id, b1 from test1");
659 Boolean [] array = new Boolean [2];
660
661 for (int i = 0; rs.next(); i++) {
662 boolean boole = rs.getBoolean(2);
663
664 array[i] = Boolean.valueOf(boole);
665
666 if (rs.wasNull()) {
667 array[i] = null;
668 }
669 }
670
671 boolean result = (array[0] == null && array[1] == Boolean.TRUE)
672 || (array[0] == Boolean.TRUE
673 && array[1] == null);
674
675 assertTrue(result);
676 } catch (SQLException e) {
677 e.printStackTrace();
678 System.out.println("TestSql.testUnionColumnType() error: "
679 + e.getMessage());
680 }
681 }
682
683 protected void tearDown() {
684
685 try {
686 connection.close();
687 } catch (Exception e) {
688 e.printStackTrace();
689 System.out.println("TestSql.tearDown() error: " + e.getMessage());
690 }
691 }
692
693 public static void main(String [] argv) {
694
695 TestResult result = new TestResult();
696 TestCase testA = new TestSql("testMetaData");
697 TestCase testB = new TestSql("testDoubleNaN");
698 TestCase testC = new TestSql("testAny");
699
700 testA.run(result);
701 testB.run(result);
702 testC.run(result);
703 System.out.println("TestSql error count: " + result.failureCount());
704 }
705 }
706 |
Popular Tags |
Java API By Example, From Geeks To Geeks.