Obtaining database-related metadata through a JDBC connection

It suddenly occurred to me today that a sample of the tool from Astah-professional specialized in the ER diagram was used to get the Java code, a pretty good piece of code that only needs to be connected by JDBC to obtain database information, mainly through Java.sql.DatabaseMetaData this class is implemented. Just record it and share it!

1. db.properties

Driver=org.postgresql.driver url=jdbc:postgresql://12.3.3.3:5432/testusername=1password=3

2. Core Class Jdbcutils

Public class jdbcutils {properties ps;boolean isinitialized;protected string  driver;protected String url;protected String username;protected String  Password;protected connectioninfo conninfo;public static void main (String[] args )  throws exception {jdbcutils j1 = new jdbcutils (); j1.process ();} /** *  */public jdbcutils ()  {try {this.init ();}  catch  (ioexception e)  { e.printstacktrace ();}} Protected void init ()  throws ioexception {if (isinitialized)  {return;} Ps = new properties ();p s.load (JdbcUtils.class.getResourceAsStream ("Db.properties"));d River  = ps.getproperty ("Driver"); Url = ps.getproperty ("url");username =  Ps.getproperty ("username");p assword = ps.getproperty ("password");this.conninfo = new  ConnectionInfo (thIs.url);isinitialized = true; System.out.println ("load configurations."); /** *  *  *  @param  executor *  @throws  exception */ Public void process ()  throws Exception {Connection conn =  Getconnection (); System.out.println ("has connected ..."); resultset rs = null; string tablename =  "Product"; try {databasemetadata meta =  Conn.getmetadata ();  //printing   Introducing all foreign keys for the   table try { system.out.println ("------- Print  all foreigh key from table  '  + tableName +  '  --------'); System.out.println ("Seqtablecolumn (FK)"); Rs = meta.getexportedkeys (Conn.getcatalog (),  null,  tablename); while  (Rs.next ())  {   String fkTableName =  Rs.getstring ("Fktable_name");   string fkcolumnname = Rs.getstring ("Fkcolumn_name");    int fksequence = rs.getint ("KEY_SEQ");    system.out.println (String.Format ("%d%s%s",  fksequence, fktablename, fkcolumnname));  } system.out.println ("");}  finally {closeresultset (RS);}         try {          //Print all tables of the library System.out.println ("------- Print all table name in this  database  ' " + this.connInfo.db + " '------"); string[] types = {"TABLE"};rs = meta.gettables (null, null,  "%",  types) ;  system.out.println (This.gettablesdetail ("Table list",  rs));  } finally { Closeresultset (RS);}      //try { //print   Drop script for all foreign keys of the library System.out.println ("------- print  all table foreign keys drop scripts in this database  ' " + this.connInfo.db + " '------"); string[] types = {"TABLE"};rs = meta.gettables (null, null,  "%",  types) ; &NBSP;COLLECTALLTABLEFK (Rs, meta);  } finally {closeresultset (RS);}   //try {//Print   Introduce all foreign key System.out.println for this   table ("------- print all tables  which has foreign key from table  '  + tableName +  '-- ----"); Rs = meta.getexportedkeys (Conn.getcatalog (),  null, tablename); System.out.println (This.getkeydetail ("Exported keys",  rs));}  finally {closeresultset (RS);} try { //prints all foreign key System.out.println for the table ("------- print all tables which table   '  + tableName +  '  reference to.------");rs =  Meta.getimportedkeys (Conn.getcatalog (),  null, tablename); System.out.println (This.getkeydetail ("IMported keys ",  rs));  } finally {closeresultset (RS);}}  finally {closeresultset (RS); CloseConnection (conn);}  }/** *  @param  rs */private void closeresultset (resultset rs)  {if (rs !=null)  {try {rs.close ();}  catch  (sqlexception e)  {}}}/** *  @param  con */private void  CloseConnection (Connection con)  {if (con !=null)  {try {con.close ();}  catch  (sqlexception e)  {}}}    /**     *   @param  tables     *  @return      * @ throws sqlexception     */    private string  COLLECTALLTABLEFK (Resultset tables, databasemetadata meta)  throws sqlexception {         set<string> s&nbsP;= new hashset<string> ();    stringbuilder sb = new  StringBuilder ();     while (Tables.next ())  {      string  tn = tables.getstring ("table_name");     string scm =  Tables.getstring ("Table_schem");         resultset fks =  meta.getexportedkeys (Meta.getconnection (). GetCatalog (),  null, tn);     STRING[]&NBSP;FKNS&NBSP;=&NBSP;THIS.COLLECTFK (Tn, s, fks, new transformer ()  {      public object transform (Object o)  {//table , fk     return string.format ("alter table %s drop constraint %s;\n",      array.get (o, 0),  array.get (o, 1));    }     });  &Nbsp;  if (fkns.length==0)  {    continue;    }     string ds = string.format ("%s: %s",  tn, stringutils.join (Fkns,   "\ n"));     sb.append (Stringutils.join (fkns,  "")); }          system.out.println ("FK---------------\ n"  + sb.tostring ());     return sb.tostring (); &NBSP;&NBSP;&NBSP;}PRIVATE&NBSP;STRING[]&NBSP;COLLECTFK (String  table, set<string> fks, resultset ekrs, transformer transformer)  throws SQLException {Set<String> s = new HashSet<String> (); while (Ekrs.next ())  {string fktn = ekrs.getstring ("Fktable_name"); String v = ekrs.getstring ("Fk_name"); S.add ((String) transformer.transform (new string[] &NBSP;{FKTN,&NBSP;V}));} Ekrs.close (); Return  s.toaRray (New string[s.size ());}  private string gettablesdetail (STRING&NBSP;HEAD,&NBSP;RESULTSET&NBSP;RS)  throws  Sqlexception {return this.getkeydetail (Head, rs, gettablesrsfields ());} Private string getkeydetail (STRING&NBSP;HEAD,&NBSP;RESULTSET&NBSP;RS)  throws SQLException  {return this.getkeydetail (Head, rs, getkeysrscolumns ());}     private string getkeydetail (string head, resultset rs,  String[] cols)  throws SQLException {StringBuilder sb = new  StringBuilder (); Sb.append ("--------"  + head +  "----------\ n");while  (Rs.next ())   { sb.append ("#\n");for  (String s : cols)  {try {sb.append (s). Append ("="). Append (rs.getstring (s)). Append ("\ n");} catch (Exception e)  {system.out.println ("Miss field:"  + s);}}  sb.append ("--------\ n"); RetuRn sb.tostring ();} Private string[] gettablesrsfields ()  {return new string[] {"TABLE_CAT", "TABLE_ Schem "," table_name "," Table_type "," REMARKS "//" Type_cat ",//" Type_schem ",//" type_name ",//" Self_referencing_col_ NAME ",//" Ref_generation "};} Private string[] getkeysrscolumns ()  {return new string[] {"PKTABLE_CAT", "PKTABLE _schem "," Pktable_name "," Pkcolumn_name "," Fktable_cat "," Fktable_schem "," Fktable_name "," Fkcolumn_name "," KEY_SEQ ", "Update_rule", "Delete_rule", "Fk_name", "Pk_name", "deferrability"}; /** *  @return  *  @throws  exception */protected connection getconnection ()  throws exception {class.forname (driver); Connection conn = drivermanager.getconnection (Url, username, password);return  Conn;} private static class connectioninfo {string port ; string ip ; string protocol ; string db ; string d btype ; /** *  */public connectioninfo (string url )  {Pattern  p = pattern.compile ("^ (. *):(. *)://(. *):(. *)/(. *) $"); Matcher m = p.matcher (URL), if (M.find ())  {protocol = m.group (1);d btype =  m.group (2); Ip = m.group (3);p Ort = m.group (4);d B = m.group (5);}}  } }

Obtaining database-related metadata through a JDBC connection