1 12 package mondrian.gui; 13 14 import java.util.*; 15 import java.sql.*; 16 17 21 public class JDBCMetaData { 22 String jdbcDriverClassName = null; String jdbcConnectionUrl = null; 25 Connection conn = null; 26 DatabaseMetaData md = null; 27 28 41 private Map allFactTableDimensions = new HashMap(); private Map allTablesPKs = new HashMap(); 43 private Map allTablesCols = new HashMap(); 44 private Map allSchemasMap = new HashMap(); 45 46 private Vector allSchemas = new Vector(); 48 private String errMsg = null; 49 private Database db = new Database(); 50 51 public JDBCMetaData(String jdbcDriverClassName, String jdbcConnectionUrl) { 52 this.jdbcConnectionUrl = jdbcConnectionUrl; 53 this.jdbcDriverClassName = jdbcDriverClassName; 54 55 if (initConnection() == null) { 56 setAllSchemas(); 57 closeConnection(); 58 } 59 } 60 61 62 public String initConnection(){ 63 try{ 64 if (jdbcDriverClassName==null || jdbcConnectionUrl==null) { 65 throw new Exception ("Driver="+jdbcDriverClassName+"\nConn Url="+jdbcConnectionUrl+"\n(Hint: Use Prefrences to set Database Connection parameters first and then open a Schema.)"); 66 } 67 68 Class.forName(jdbcDriverClassName); 69 conn = DriverManager.getConnection(jdbcConnectionUrl); 70 71 System.out.println("JDBC connection OPEN"); 72 md = conn.getMetaData(); 73 74 db.productName = md.getDatabaseProductName(); 75 db.productVersion = md.getDatabaseProductVersion(); 76 db.catalogName = conn.getCatalog(); 77 78 System.out.println("Catalog name = "+db.catalogName); 79 89 System.out.println("Database Product Name: " + db.productName); 90 System.out.println("Database Product Version: " + db.productVersion); 91 92 96 return null; 97 } catch (Exception e) { 98 errMsg = e.getMessage(); 99 System.out.println("Database connection exception : "+errMsg); 100 return errMsg; 101 } 103 } 104 105 public void closeConnection() { 106 try { 107 conn.close(); 108 System.out.println("JDBC connection CLOSE"); 109 }catch (Exception e) {e.printStackTrace();} 110 } 111 112 113 private void setAllSchemas(){ 114 ResultSet rs = null; 115 try{ 116 rs = md.getSchemas(); 117 121 while(rs.next()) { 122 DbSchema dbs = new DbSchema(); 123 dbs.name = rs.getString("TABLE_SCHEM"); 124 setAllTables(dbs); 125 db.addDbSchema(dbs); 126 } 127 rs.close(); 128 } catch (Exception e) { 129 System.out.println("Exception : Database does not support schemas."+e.getMessage()); 130 DbSchema dbs = new DbSchema(); 131 dbs.name = null; setAllTables(dbs); 133 db.addDbSchema(dbs); 134 } 135 } 136 137 138 private void setAllTables(DbSchema dbs){ 139 ResultSet rs = null; 140 try{ 141 rs = md.getTables(null, dbs.name, null, new String []{"TABLE"}); 142 while(rs.next()) { 143 String tbname = rs.getString("TABLE_NAME"); 144 DbTable dbt; 145 146 149 ResultSet rs_fks = md.getImportedKeys(null, dbs.name, tbname); 150 if (rs_fks.next()) { 151 dbt = new FactTable(); 152 do { 153 ((FactTable) dbt).addFks(rs_fks.getString("FKCOLUMN_NAME"),rs_fks.getString("pktable_name")); 154 } while(rs_fks.next()); 155 156 } else { 157 dbt = new DbTable(); 158 } 159 rs_fks.close(); 160 161 dbt.schemaName = dbs.name; 162 dbt.name = tbname; 163 setPKey(dbt); 164 setColumns(dbt); 165 dbs.addDbTable(dbt); 166 db.addDbTable(dbt); 167 } 168 rs.close(); 169 } catch (Exception e) {e.printStackTrace();} 170 } 171 172 175 private void setPKey(DbTable dbt){ 176 ResultSet rs = null; 177 try{ 178 rs = md.getPrimaryKeys(null, dbt.schemaName, dbt.name); 179 184 if (rs.next()) { 185 dbt.pk = rs.getString("column_name"); } 188 rs.close(); 189 } catch (Exception e) { 190 e.printStackTrace(); 191 } 192 } 193 194 195 private void setColumns(DbTable dbt){ 196 ResultSet rs = null; 197 try{ 198 rs = md.getColumns(null, dbt.schemaName, dbt.name, null); 199 while(rs.next()) { 200 dbt.addColsDataType(rs.getString("COLUMN_NAME"), rs.getString("DATA_TYPE")); 201 } 202 rs.close(); 203 } catch (Exception e) { 204 e.printStackTrace(); 205 } 206 } 207 208 211 212 public Vector getAllSchemas() { 213 return db.getAllSchemas(); 214 } 215 216 217 218 public Vector getAllTables(String schemaName) { 219 return db.getAllTables(schemaName); 220 } 221 222 223 public Vector getAllTables(String schemaName, String minusTable) { 224 225 if (minusTable == null) { 226 return getAllTables(schemaName); 227 } else { 228 Vector allTablesMinusOne = new Vector(); 229 Iterator i = getAllTables(schemaName).iterator(); 230 while (i.hasNext()) { 231 String s = (String ) i.next(); 232 if (s.endsWith(minusTable)) { if ((schemaName == null) || s.startsWith(schemaName) ) { 234 continue; 235 } 236 } 237 allTablesMinusOne.add(s); 238 } 239 return allTablesMinusOne; 240 } 241 } 242 243 244 public Vector getFactTables(String schemaName) { 245 return db.getFactTables(schemaName); 246 } 247 248 249 public Vector getDimensionTables(String schemaName, String factTable) { 250 Vector dimeTables = new Vector(); 251 252 if (factTable == null) { 253 return dimeTables; 254 } else { 255 return db.getDimensionTables(schemaName, factTable); 256 } 257 } 258 259 public boolean isTableExists(String schemaName, String tableName) { 260 if (tableName == null) { 261 return true; 262 } else { 263 return db.tableExists(schemaName, tableName); 264 } 265 } 266 267 public boolean isColExists(String schemaName, String tableName, String colName) { 268 if (tableName == null || colName == null) { 269 return true; 270 } else { 271 return db.colExists(schemaName, tableName, colName); 272 } 273 } 274 275 276 public Vector getFactTableFKs(String schemaName, String factTable) { 277 Vector fks = new Vector(); 278 279 if (factTable == null) { 280 return fks; 281 } else { 282 return db.getFactTableFKs(schemaName, factTable); 283 } 284 } 285 286 public String getTablePK(String schemaName, String tableName) { 287 288 if (tableName == null) { 289 return null; 290 } else { 291 return db.getTablePK(schemaName, tableName); 292 } 293 } 294 295 296 public Vector getAllColumns(String schemaName, String tableName) { 297 Vector allcols = new Vector(); 298 299 if (tableName == null) { 300 return allcols; 301 } else { 302 return db.getAllColumns(schemaName, tableName); 303 } 304 } 305 306 public int getColumnDataType(String schemaName, String tableName, String colName) { 308 if (tableName == null || colName==null) { 309 return -1; 310 } else { 311 return db.getColumnDataType(schemaName, tableName, colName); 312 } 313 314 } 315 public String getDbCatalogName() { 316 return db.catalogName; 317 } 318 319 public String getDatabaseProductName() { 320 return db.productName; 321 } 322 323 public String getErrMsg() { 324 return errMsg; 325 } 326 327 public static void main(String [] args) { 328 338 String s = "somita->namita"; 339 String [] p = s.split("->"); 340 if (p.length >=2) 341 System.out.println("p0="+p[0]+", p1="+p[1]); 342 } 343 344 347 class Database { 348 String catalogName = ""; String productName = "Unknown"; 350 String productVersion = ""; 351 352 List schemas = new ArrayList(); List tables = new ArrayList(); Map tablesCount = new TreeMap(); 357 Vector allSchemas ; 358 359 private void addDbSchema(DbSchema dbs) { 360 schemas.add(dbs); 361 } 362 363 private void addDbTable(DbTable dbs) { 364 tables.add(dbs); 365 Integer count = (Integer ) tablesCount.get(dbs.name); 366 if (count == null) { 367 count = new Integer (1); 368 } else { 369 count = new Integer (count.intValue()+1); 370 } 371 tablesCount.put(dbs.name, count); 372 } 373 374 private Vector getAllSchemas() { 375 if (allSchemas == null) { 376 allSchemas = new Vector(); 377 if (schemas.size() > 0) { 378 Iterator i = schemas.iterator(); 379 while (i.hasNext()) { 380 allSchemas.add( ((DbSchema) i.next()).name ); 381 } 382 } 383 } 384 return allSchemas; 385 } 386 387 private boolean tableExists(String sname, String tableName) { 388 if (sname == null || sname.equals("")) { 389 return tablesCount.containsKey(tableName); 390 } else { 391 Iterator i = schemas.iterator(); 392 while (i.hasNext()) { 393 DbSchema s = (DbSchema) i.next(); 394 if ( s.name.equals(sname) ) { 395 Iterator ti = s.tables.iterator(); 396 while (ti.hasNext()) { 397 DbTable d = (DbTable) ti.next(); 398 if (d.name.equals(tableName)) { 399 return true; 400 } 401 } 402 break; 403 } 404 } 405 } 406 return false; 407 } 408 409 private boolean colExists(String sname, String tableName, String colName) { 410 if (sname == null || sname.equals("")) { 411 Iterator ti = tables.iterator(); 412 while (ti.hasNext()) { 413 DbTable t = (DbTable) ti.next(); 414 if (t.name.equals(tableName)){ 415 return t.colsDataType.containsKey(colName); 416 } 417 } 418 } else { 419 Iterator i = schemas.iterator(); 421 while (i.hasNext()) { 422 DbSchema s = (DbSchema) i.next(); 423 if ( s.name.equals(sname) ) { 424 Iterator ti = s.tables.iterator(); 425 while (ti.hasNext()) { 426 DbTable t = (DbTable) ti.next(); 427 if (t.name.equals(tableName)){ 428 return t.colsDataType.containsKey(colName); 429 } 430 } 431 break; 432 } 433 } 434 } 435 436 return false; 437 } 438 439 private Vector getAllTables(String sname) { 440 Vector v = new Vector(); 441 442 if (sname == null || sname.equals("")) { 443 Iterator i = tables.iterator(); 445 while (i.hasNext()) { 446 DbTable d = (DbTable) i.next(); 447 if ( d.schemaName == null ) { 448 v.add(d.name); 449 } else { 450 v.add(d.schemaName + "->"+ d.name); 451 } 452 } 453 454 } else { 455 Iterator i = schemas.iterator(); 457 while (i.hasNext()) { 458 DbSchema s = (DbSchema) i.next(); 459 if ( s.name.equals(sname) ) { 460 Iterator ti = s.tables.iterator(); 461 while (ti.hasNext()) { 462 DbTable d = (DbTable) ti.next(); 463 v.add(d.name); 464 } 465 break; 466 } 467 } 468 } 469 return v; 470 } 471 472 private Vector getFactTables(String sname) { 473 Vector f = new Vector(); 474 475 if (sname == null || sname.equals("")) { 476 Iterator ti = tables.iterator(); 478 while (ti.hasNext()) { 479 DbTable t = (DbTable) ti.next(); 480 if (t instanceof FactTable){ 481 if ( t.schemaName == null ) { 482 f.add(t.name); 483 } else { 484 f.add(t.schemaName + "->"+ t.name); 485 } 486 } 487 } 488 } else { 489 Iterator i = schemas.iterator(); 491 while (i.hasNext()) { 492 DbSchema s = (DbSchema) i.next(); 493 if ( s.name.equals(sname) ) { 494 Iterator ti = s.tables.iterator(); 495 while (ti.hasNext()) { 496 Object t = ti.next(); 497 if (t instanceof FactTable){ 498 f.add(((FactTable)t).name); 499 } 500 } 501 break; 502 } 503 } 504 } 505 506 return f; 507 } 508 509 510 private Vector getFactTableFKs(String sname, String factTable) { 511 Vector f = new Vector(); 512 513 if (sname == null || sname.equals("")) { 514 boolean duplicate = (tablesCount.containsKey(factTable)) && (((Integer ) tablesCount.get(factTable)).intValue() > 1); 516 517 Iterator ti = tables.iterator(); 518 while (ti.hasNext()) { 519 DbTable t = (DbTable) ti.next(); 520 if (t instanceof FactTable && t.name.equals(factTable)){ 521 if (duplicate) { 522 Iterator fki = ((FactTable)t).fks.keySet().iterator(); 523 while (fki.hasNext()) { 524 String fk = (String ) fki.next(); 525 if ( t.schemaName == null ) { 526 f.add(t.name + "->" + fk); 527 } else { 528 f.add(t.schemaName + "->"+ t.name + "->" + fk); 529 } 530 } 531 } else { 532 f.addAll( ((FactTable)t).fks.keySet()); 533 } 534 } 535 } 536 } else { 537 Iterator i = schemas.iterator(); 539 while (i.hasNext()) { 540 DbSchema s = (DbSchema) i.next(); 541 if ( s.name.equals(sname) ) { 542 Iterator ti = s.tables.iterator(); 543 while (ti.hasNext()) { 544 DbTable t = (DbTable) ti.next(); 545 if (t instanceof FactTable && t.name.equals(factTable)){ 546 f.addAll(((FactTable)t).fks.keySet()); 547 break; 548 } 549 } 550 break; 551 } 552 } 553 } 554 return f; 555 } 556 557 private Vector getDimensionTables(String sname, String factTable) { 558 Vector f = new Vector(); 559 560 if (sname == null || sname.equals("")) { 561 boolean duplicate = (tablesCount.containsKey(factTable)) && (((Integer ) tablesCount.get(factTable)).intValue() > 1); 563 564 Iterator ti = tables.iterator(); 565 while (ti.hasNext()) { 566 DbTable t = (DbTable) ti.next(); 567 if (t instanceof FactTable && t.name.equals(factTable)){ 568 if (duplicate) { 569 Iterator fki = ((FactTable)t).fks.values().iterator(); 570 while (fki.hasNext()) { 571 String fkt = (String ) fki.next(); 572 if ( t.schemaName == null ) { 573 f.add(t.name + "->" + fkt); 574 } else { 575 f.add(t.schemaName + "->"+ t.name + "->" + fkt); 576 } 577 } 578 } else { 579 f.addAll(((FactTable)t).fks.values()); 580 break; 581 } 582 } 583 } 584 } else { 585 Iterator i = schemas.iterator(); 587 while (i.hasNext()) { 588 DbSchema s = (DbSchema) i.next(); 589 if ( s.name.equals(sname) ) { 590 Iterator ti = s.tables.iterator(); 591 while (ti.hasNext()) { 592 DbTable t = (DbTable) ti.next(); 593 if (t instanceof FactTable && t.name.equals(factTable)){ 594 f.addAll(((FactTable)t).fks.values()); 595 break; 596 } 597 } 598 break; 599 } 600 } 601 } 602 return f; 603 } 604 605 private String getTablePK(String sname, String tableName) { 606 607 if (sname == null || sname.equals("")) { 608 Iterator ti = tables.iterator(); 610 while (ti.hasNext()) { 611 DbTable t = (DbTable) ti.next(); 612 if (t.name.equals(tableName)){ 613 return t.pk; 614 } 615 } 616 } else { 617 Iterator i = schemas.iterator(); 619 while (i.hasNext()) { 620 DbSchema s = (DbSchema) i.next(); 621 if ( s.name.equals(sname) ) { 622 Iterator ti = s.tables.iterator(); 623 while (ti.hasNext()) { 624 DbTable t = (DbTable) ti.next(); 625 if (t.name.equals(tableName)){ 626 return t.pk; 627 } 628 } 629 break; 630 } 631 } 632 } 633 return null; 634 } 635 636 private Vector getAllColumns(String sname, String tableName) { 637 Vector f = new Vector(); 638 639 if (sname == null || sname.equals("")) { 640 boolean duplicate = (tablesCount.containsKey(tableName)) && (((Integer ) tablesCount.get(tableName)).intValue() > 1); 642 643 Iterator ti = tables.iterator(); 644 while (ti.hasNext()) { 645 DbTable t = (DbTable) ti.next(); 646 if (t.name.equals(tableName)){ 647 if (duplicate) { 648 Iterator ci = t.colsDataType.keySet().iterator(); 649 while (ci.hasNext()) { 650 String c = (String ) ci.next(); 651 if ( t.schemaName == null ) { 652 f.add(t.name + "->" + c); 653 } else { 654 f.add(t.schemaName + "->"+ t.name + "->" + c); 655 } 656 } 657 } else { 658 f.addAll(t.colsDataType.keySet()); break; 660 } 661 } 662 } 663 } else { 664 Iterator i = schemas.iterator(); 666 while (i.hasNext()) { 667 DbSchema s = (DbSchema) i.next(); 668 if ( s.name.equals(sname) ) { 669 Iterator ti = s.tables.iterator(); 670 while (ti.hasNext()) { 671 DbTable t = (DbTable) ti.next(); 672 if (t.name.equals(tableName)){ 673 f.addAll(t.colsDataType.keySet()); 674 break; 675 } 676 } 677 break; 678 } 679 } 680 } 681 return f; 682 } 683 684 private int getColumnDataType(String sname, String tableName, String colName) { 685 686 if (sname == null || sname.equals("")) { 687 Iterator ti = tables.iterator(); 688 while (ti.hasNext()) { 689 DbTable t = (DbTable) ti.next(); 690 if (t.name.equals(tableName)){ 691 int dataType = Integer.parseInt((String ) t.colsDataType.get(colName)); 692 return dataType; 693 } 694 } 695 } else { 696 Iterator i = schemas.iterator(); 698 while (i.hasNext()) { 699 DbSchema s = (DbSchema) i.next(); 700 if ( s.name.equals(sname) ) { 701 Iterator ti = s.tables.iterator(); 702 while (ti.hasNext()) { 703 DbTable t = (DbTable) ti.next(); 704 if (t.name.equals(tableName)){ 705 int dataType = Integer.parseInt((String ) t.colsDataType.get(colName)); 706 return dataType; 707 } 708 } 709 break; 710 } 711 } 712 } 713 714 return -1; 715 } 716 717 } 718 719 class DbSchema { 720 String name; 721 List tables = new ArrayList(); 723 private void addDbTable(DbTable dbt){ 724 tables.add(dbt); 725 } 726 } 727 728 class DbTable { 729 String schemaName; 730 String name; 731 String pk; 732 Map colsDataType = new TreeMap(); 734 private void addColsDataType(String col, String dataType) { 735 colsDataType.put(col, dataType); 736 } 737 738 } 739 740 class FactTable extends DbTable { 741 Map fks = new TreeMap(); 743 private void addFks(String fk, String pkt) { 744 fks.put(fk, pkt); 745 } 746 } 747 } 748 749 750 | Popular Tags |