通過JDBC連接取得數據庫相關的元數據
只需要通過JDBC連接就可以得到數據庫信息，主要是通過 java.sql.DatabaseMetaData 這個類實現。
1. db.properties
driver=org.postgresql.Driver url=jdbc:postgresql://127.0.0.1:5432/test username=u password=p
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);
}
}
}
}
本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！