通过JDBC连接取得数据库相关的元数据
今天突然想起之前从astah-professional专门画ER图的工具 的一个Sample中取得Java代码,相当棒的一段代码,只需要通过JDBC连接就可以得到数据库信息,主要是通过 java.sql.DatabaseMetaData 这个类实现。先记录下来分享一下!
1. db.properties
driver=org.postgresql.Driver url=jdbc:postgresql://12.3.3.3:5432/test username=1 password=3
2. 核心类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(); ps.load(JdbcUtils.class.getResourceAsStream("db.properties")); driver = ps.getProperty("driver"); url = ps.getProperty("url"); username = ps.getProperty("username"); password = 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(); //打印 引入该 表的所有外键 try { System.out.println("------- Print all foreigh key from table ‘" + tableName + "‘ --------"); System.out.println("Seq Table Column(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 { //打印该库的所有表 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 { //打印 出该库所有外键的Drop脚本 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); collectAllTableFK(rs, meta); } finally { closeResultSet(rs); } // try { //打印 引入该 表的所有外键 System.out.println("------- 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 { //打印该表的所有外键 System.out.println("------- 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 = 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[] fkns = 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)); } }); 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(); } private String[] 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[] {fktn, v})); } ekRS.close(); return s.toArray(new String[s.size()]); } private String getTablesDetail(String head, ResultSet rs) throws SQLException { return this.getKeyDetail(head, rs, getTablesRsFields()); } private String getKeyDetail(String head, ResultSet 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 dbtype ; /** * */ public ConnectionInfo(String url ) { Pattern p = Pattern.compile("^(.*):(.*)://(.*):(.*)/(.*)$"); Matcher m = p.matcher(url); if(m.find()) { protocol = m.group(1); dbtype = m.group(2); ip = m.group(3); port = m.group(4); db = m.group(5); } } } }
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。