|
1
25 package testsuite.simple;
26
27 import testsuite.BaseTestCase;
28
29 import java.sql.Connection  ;
30 import java.sql.DatabaseMetaData ;
31 import java.sql.ResultSet ;
32 import java.sql.ResultSetMetaData ;
33 import java.sql.SQLException ;
34 import java.sql.Types ;
35 import java.util.BitSet ;
36 import java.util.Properties ;
37
38
44 public class MetadataTest extends BaseTestCase {
45
48
54 public MetadataTest(String name) {
55 super(name);
56 }
57
58
61
66 public static void main(String [] args) {
67 junit.textui.TestRunner.run(MetadataTest.class);
68 }
69
70
76 public void setUp() throws Exception {
77 super.setUp();
78 createTestTable();
79 }
80
81
87 public void testForeignKeys() throws SQLException {
88 DatabaseMetaData dbmd = this.conn.getMetaData();
89 this.rs = dbmd.getImportedKeys(null, null, "child");
90
91 while (this.rs.next()) {
92 String pkColumnName = this.rs.getString("PKCOLUMN_NAME");
93 String fkColumnName = this.rs.getString("FKCOLUMN_NAME");
94 assertTrue("Primary Key not returned correctly ('" + pkColumnName
95 + "' != 'parent_id')", pkColumnName
96 .equalsIgnoreCase("parent_id"));
97 assertTrue("Foreign Key not returned correctly ('" + fkColumnName
98 + "' != 'parent_id_fk')", fkColumnName
99 .equalsIgnoreCase("parent_id_fk"));
100 }
101
102 this.rs.close();
103 this.rs = dbmd.getExportedKeys(null, null, "parent");
104
105 while (this.rs.next()) {
106 String pkColumnName = this.rs.getString("PKCOLUMN_NAME");
107 String fkColumnName = this.rs.getString("FKCOLUMN_NAME");
108 String fkTableName = this.rs.getString("FKTABLE_NAME");
109 assertTrue("Primary Key not returned correctly ('" + pkColumnName
110 + "' != 'parent_id')", pkColumnName
111 .equalsIgnoreCase("parent_id"));
112 assertTrue(
113 "Foreign Key table not returned correctly for getExportedKeys ('"
114 + fkTableName + "' != 'child')", fkTableName
115 .equalsIgnoreCase("child"));
116 assertTrue(
117 "Foreign Key not returned correctly for getExportedKeys ('"
118 + fkColumnName + "' != 'parent_id_fk')",
119 fkColumnName.equalsIgnoreCase("parent_id_fk"));
120 }
121
122 this.rs.close();
123
124 this.rs = dbmd.getCrossReference(null, null, "cpd_foreign_3", null,
125 null, "cpd_foreign_4");
126
127 while (this.rs.next()) {
128 String pkColumnName = this.rs.getString("PKCOLUMN_NAME");
129 String pkTableName = this.rs.getString("PKTABLE_NAME");
130 String fkColumnName = this.rs.getString("FKCOLUMN_NAME");
131 String fkTableName = this.rs.getString("FKTABLE_NAME");
132 String deleteAction = cascadeOptionToString(this.rs
133 .getInt("DELETE_RULE"));
134 String updateAction = cascadeOptionToString(this.rs
135 .getInt("UPDATE_RULE"));
136
137 System.out.println("[D] " + deleteAction);
138 System.out.println("[U] " + updateAction);
139
140 System.out.println(pkTableName + "(" + pkColumnName + ") -> "
141 + fkTableName + "(" + fkColumnName + ")");
142 }
143
144 this.rs.close();
145
146 this.rs = dbmd.getImportedKeys(null, null, "fktable2");
147 }
148
149
155 public void testGetPrimaryKeys() throws SQLException {
156 try {
157 DatabaseMetaData dbmd = this.conn.getMetaData();
158 this.rs = dbmd.getPrimaryKeys(this.conn.getCatalog(), "",
159 "multikey");
160
161 short[] keySeqs = new short[4];
162 String [] columnNames = new String [4];
163 int i = 0;
164
165 while (this.rs.next()) {
166 this.rs.getString("TABLE_NAME");
167 columnNames[i] = this.rs.getString("COLUMN_NAME");
168
169 this.rs.getString("PK_NAME");
170 keySeqs[i] = this.rs.getShort("KEY_SEQ");
171 i++;
172 }
173
174 if ((keySeqs[0] != 3) && (keySeqs[1] != 2) && (keySeqs[2] != 4)
175 && (keySeqs[4] != 1)) {
176 fail("Keys returned in wrong order");
177 }
178 } finally {
179 if (this.rs != null) {
180 try {
181 this.rs.close();
182 } catch (SQLException sqlEx) {
183
184 }
185 }
186 }
187 }
188
189 private static String cascadeOptionToString(int option) {
190 switch (option) {
191 case DatabaseMetaData.importedKeyCascade:
192 return "CASCADE";
193
194 case DatabaseMetaData.importedKeySetNull:
195 return "SET NULL";
196
197 case DatabaseMetaData.importedKeyRestrict:
198 return "RESTRICT";
199
200 case DatabaseMetaData.importedKeyNoAction:
201 return "NO ACTION";
202 }
203
204 return "SET DEFAULT";
205 }
206
207 private void createTestTable() throws SQLException {
208 this.stmt.executeUpdate("DROP TABLE IF EXISTS child");
209 this.stmt.executeUpdate("DROP TABLE IF EXISTS parent");
210 this.stmt.executeUpdate("DROP TABLE IF EXISTS multikey");
211 this.stmt.executeUpdate("DROP TABLE IF EXISTS cpd_foreign_4");
212 this.stmt.executeUpdate("DROP TABLE IF EXISTS cpd_foreign_3");
213 this.stmt.executeUpdate("DROP TABLE IF EXISTS cpd_foreign_2");
214 this.stmt.executeUpdate("DROP TABLE IF EXISTS cpd_foreign_1");
215 this.stmt.executeUpdate("DROP TABLE IF EXISTS fktable2");
216 this.stmt.executeUpdate("DROP TABLE IF EXISTS fktable1");
217
218 this.stmt
219 .executeUpdate("CREATE TABLE parent(parent_id INT NOT NULL, PRIMARY KEY (parent_id)) TYPE=INNODB");
220 this.stmt
221 .executeUpdate("CREATE TABLE child(child_id INT, parent_id_fk INT, INDEX par_ind (parent_id_fk), "
222 + "FOREIGN KEY (parent_id_fk) REFERENCES parent(parent_id)) TYPE=INNODB");
223 this.stmt
224 .executeUpdate("CREATE TABLE multikey(d INT NOT NULL, b INT NOT NULL, a INT NOT NULL, c INT NOT NULL, PRIMARY KEY (d, b, a, c))");
225
226 this.stmt.executeUpdate("create table cpd_foreign_1("
228 + "id int(8) not null auto_increment primary key,"
229 + "name varchar(255) not null unique," + "key (id)"
230 + ") type=InnoDB");
231 this.stmt.executeUpdate("create table cpd_foreign_2("
232 + "id int(8) not null auto_increment primary key,"
233 + "key (id)," + "name varchar(255)" + ") type=InnoDB");
234 this.stmt
235 .executeUpdate("create table cpd_foreign_3("
236 + "cpd_foreign_1_id int(8) not null,"
237 + "cpd_foreign_2_id int(8) not null,"
238 + "key(cpd_foreign_1_id),"
239 + "key(cpd_foreign_2_id),"
240 + "primary key (cpd_foreign_1_id, cpd_foreign_2_id),"
241 + "foreign key (cpd_foreign_1_id) references cpd_foreign_1(id),"
242 + "foreign key (cpd_foreign_2_id) references cpd_foreign_2(id)"
243 + ") type=InnoDB");
244 this.stmt
245 .executeUpdate("create table cpd_foreign_4("
246 + "cpd_foreign_1_id int(8) not null,"
247 + "cpd_foreign_2_id int(8) not null,"
248 + "key(cpd_foreign_1_id),"
249 + "key(cpd_foreign_2_id),"
250 + "primary key (cpd_foreign_1_id, cpd_foreign_2_id),"
251 + "foreign key (cpd_foreign_1_id, cpd_foreign_2_id) "
252 + "references cpd_foreign_3(cpd_foreign_1_id, cpd_foreign_2_id) "
253 + "ON DELETE RESTRICT ON UPDATE CASCADE"
254 + ") type=InnoDB");
255
256 this.stmt
257 .executeUpdate("create table fktable1 (TYPE_ID int not null, TYPE_DESC varchar(32), primary key(TYPE_ID)) TYPE=InnoDB");
258 this.stmt
259 .executeUpdate("create table fktable2 (KEY_ID int not null, COF_NAME varchar(32), PRICE float, TYPE_ID int, primary key(KEY_ID), "
260 + "index(TYPE_ID), foreign key(TYPE_ID) references fktable1(TYPE_ID)) TYPE=InnoDB");
261 }
262
263
272 public void testViewMetaData() throws SQLException {
273 try {
274 this.rs = this.conn.getMetaData().getTableTypes();
275
276 while (this.rs.next()) {
277 if ("VIEW".equalsIgnoreCase(this.rs.getString(1))) {
278
279 this.stmt
280 .executeUpdate("DROP VIEW IF EXISTS vTestViewMetaData");
281 this.stmt
282 .executeUpdate("DROP TABLE IF EXISTS testViewMetaData");
283 this.stmt
284 .executeUpdate("CREATE TABLE testViewMetaData (field1 INT)");
285 this.stmt
286 .executeUpdate("CREATE VIEW vTestViewMetaData AS SELECT field1 FROM testViewMetaData");
287
288 ResultSet tablesRs = null;
289
290 try {
291 tablesRs = this.conn.getMetaData().getTables(
292 this.conn.getCatalog(), null, "%ViewMetaData",
293 new String [] { "TABLE", "VIEW" });
294 assertTrue(tablesRs.next());
295 assertTrue("testViewMetaData".equalsIgnoreCase(tablesRs
296 .getString(3)));
297 assertTrue(tablesRs.next());
298 assertTrue("vTestViewMetaData"
299 .equalsIgnoreCase(tablesRs.getString(3)));
300
301 } finally {
302 if (tablesRs != null) {
303 tablesRs.close();
304 }
305 }
306
307 try {
308 tablesRs = this.conn.getMetaData().getTables(
309 this.conn.getCatalog(), null, "%ViewMetaData",
310 new String [] { "TABLE" });
311 assertTrue(tablesRs.next());
312 assertTrue("testViewMetaData".equalsIgnoreCase(tablesRs
313 .getString(3)));
314 assertTrue(!tablesRs.next());
315 } finally {
316 if (tablesRs != null) {
317 tablesRs.close();
318 }
319 }
320 break;
321 }
322 }
323
324 } finally {
325 if (this.rs != null) {
326 this.rs.close();
327 }
328 }
329 }
330
331
337 public void testRSMDIsReadOnly() throws Exception {
338 try {
339 this.rs = this.stmt.executeQuery("SELECT 1");
340
341 ResultSetMetaData rsmd = this.rs.getMetaData();
342
343 if (versionMeetsMinimum(4, 1)) {
344 assertTrue(rsmd.isReadOnly(1));
345
346 try {
347 this.stmt
348 .executeUpdate("DROP TABLE IF EXISTS testRSMDIsReadOnly");
349 this.stmt
350 .executeUpdate("CREATE TABLE testRSMDIsReadOnly (field1 INT)");
351 this.stmt
352 .executeUpdate("INSERT INTO testRSMDIsReadOnly VALUES (1)");
353
354 this.rs = this.stmt
355 .executeQuery("SELECT 1, field1 + 1, field1 FROM testRSMDIsReadOnly");
356 rsmd = this.rs.getMetaData();
357
358 assertTrue(rsmd.isReadOnly(1));
359 assertTrue(rsmd.isReadOnly(2));
360 assertTrue(!rsmd.isReadOnly(3));
361 } finally {
362 this.stmt
363 .executeUpdate("DROP TABLE IF EXISTS testRSMDIsReadOnly");
364 }
365 } else {
366 assertTrue(rsmd.isReadOnly(1) == false);
367 }
368 } finally {
369 if (this.rs != null) {
370 this.rs.close();
371 }
372 }
373 }
374
375 public void testBitType() throws Exception {
376 if (versionMeetsMinimum(5, 0, 3)) {
377 try {
378 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBitType");
379 this.stmt
380 .executeUpdate("CREATE TABLE testBitType (field1 BIT, field2 BIT, field3 BIT)");
381 this.stmt
382 .executeUpdate("INSERT INTO testBitType VALUES (1, 0, NULL)");
383 this.rs = this.stmt
384 .executeQuery("SELECT field1, field2, field3 FROM testBitType");
385 this.rs.next();
386
387 assertTrue(((Boolean ) this.rs.getObject(1)).booleanValue());
388 assertTrue(!((Boolean ) this.rs.getObject(2)).booleanValue());
389 assertEquals(this.rs.getObject(3), null);
390
391 System.out.println(this.rs.getObject(1) + ", "
392 + this.rs.getObject(2) + ", " + this.rs.getObject(3));
393
394 this.rs = this.conn.prepareStatement(
395 "SELECT field1, field2, field3 FROM testBitType")
396 .executeQuery();
397 this.rs.next();
398
399 assertTrue(((Boolean ) this.rs.getObject(1)).booleanValue());
400 assertTrue(!((Boolean ) this.rs.getObject(2)).booleanValue());
401
402 assertEquals(this.rs.getObject(3), null);
403 byte[] asBytesTrue = this.rs.getBytes(1);
404 byte[] asBytesFalse = this.rs.getBytes(2);
405 byte[] asBytesNull = this.rs.getBytes(3);
406
407 assertEquals(asBytesTrue[0], 1);
408 assertEquals(asBytesFalse[0], 0);
409 assertEquals(asBytesNull, null);
410
411 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBitField");
412 this.stmt
413 .executeUpdate("CREATE TABLE testBitField(field1 BIT(9))");
414 this.rs = this.stmt
415 .executeQuery("SELECT field1 FROM testBitField");
416 System.out.println(this.rs.getMetaData().getColumnClassName(1));
417 } finally {
418 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBitType");
419 }
420 }
421 }
422
423 public void testSupportsSelectForUpdate() throws Exception {
424 boolean supportsForUpdate = this.conn.getMetaData()
425 .supportsSelectForUpdate();
426
427 if (this.versionMeetsMinimum(4, 0)) {
428 assertTrue(supportsForUpdate);
429 } else {
430 assertTrue(!supportsForUpdate);
431 }
432 }
433
434 public void testTinyint1IsBit() throws Exception {
435 String tableName = "testTinyint1IsBit";
436 createTable(tableName, "(field1 TINYINT(1))");
438 this.stmt.executeUpdate("INSERT INTO " + tableName + " VALUES (1)");
439
440 Properties props = new Properties ();
441 props.setProperty("tinyint1IsBit", "true");
442 props.setProperty("transformedBitIsBoolean", "true");
443 Connection boolConn = getConnectionWithProps(props);
444
445 this.rs = boolConn.createStatement().executeQuery(
446 "SELECT field1 FROM " + tableName);
447 checkBitOrBooleanType(false);
448
449 this.rs = boolConn.prepareStatement("SELECT field1 FROM " + tableName)
450 .executeQuery();
451 checkBitOrBooleanType(false);
452
453 this.rs = boolConn.getMetaData().getColumns(boolConn.getCatalog(),
454 null, tableName, "field1");
455 assertTrue(this.rs.next());
456
457 if (versionMeetsMinimum(4, 1)) {
458 assertEquals(Types.BOOLEAN, this.rs.getInt("DATA_TYPE"));
459 } else {
460 assertEquals(Types.BIT, this.rs.getInt("DATA_TYPE"));
461 }
462
463 if (versionMeetsMinimum(4, 1)) {
464 assertEquals("BOOLEAN", this.rs.getString("TYPE_NAME"));
465 } else {
466 assertEquals("BIT", this.rs.getString("TYPE_NAME"));
467 }
468
469 props.clear();
470 props.setProperty("transformedBitIsBoolean", "false");
471 props.setProperty("tinyint1IsBit", "true");
472
473 Connection bitConn = getConnectionWithProps(props);
474
475 this.rs = bitConn.createStatement().executeQuery(
476 "SELECT field1 FROM " + tableName);
477 checkBitOrBooleanType(true);
478
479 this.rs = bitConn.prepareStatement("SELECT field1 FROM " + tableName)
480 .executeQuery();
481 checkBitOrBooleanType(true);
482
483 this.rs = bitConn.getMetaData().getColumns(boolConn.getCatalog(), null,
484 tableName, "field1");
485 assertTrue(this.rs.next());
486
487 assertEquals(Types.BIT, this.rs.getInt("DATA_TYPE"));
488
489 assertEquals("BIT", this.rs.getString("TYPE_NAME"));
490 }
491
492 private void checkBitOrBooleanType(boolean usingBit) throws SQLException {
493
494 assertTrue(this.rs.next());
495 assertEquals("java.lang.Boolean", this.rs.getObject(1).getClass()
496 .getName());
497 if (!usingBit) {
498 if (versionMeetsMinimum(4, 1)) {
499 assertEquals(Types.BOOLEAN, this.rs.getMetaData()
500 .getColumnType(1));
501 } else {
502 assertEquals(Types.BIT, this.rs.getMetaData().getColumnType(1));
503 }
504 } else {
505 assertEquals(Types.BIT, this.rs.getMetaData().getColumnType(1));
506 }
507
508 assertEquals("java.lang.Boolean", this.rs.getMetaData()
509 .getColumnClassName(1));
510 }
511 }
512 |
Popular Tags |
Java API By Example, From Geeks To Geeks.