关系对象 package cn.gson.jdbc;import java.util.Set;public class Relation {private String tableName;private String columnName;private Set relations;public Relation() {// TODO Auto-generated constructor stub}public Relation(String tableNa
package cn.gson.jdbc;
import java.util.Set;
public class Relation {
private String tableName;
private String columnName;
private Set
relations;
public Relation() {
// TODO Auto-generated constructor stub
}
public Relation(String tableName) {
super();
this.tableName = tableName;
}
public Relation(String tableName, String columnName) {
super();
this.tableName = tableName;
this.columnName = columnName;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public Set
getRelations() { return relations; } public void setRelations(Set
relations) { this.relations = relations; } public void setColumnName(String columnName) { this.columnName = columnName; } public String getColumnName() { return columnName; } @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + ((tableName == null) ? 0 : tableName.hashCode()); return result; } @Override public boolean equals(Object obj) { if (this == obj) return true; if (obj == null) return false; if (getClass() != obj.getClass()) return false; Relation other = (Relation) obj; if (tableName == null) { if (other.tableName != null) return false; } else if (!tableName.equals(other.tableName)) return false; return true; } }
逻辑获取部分
private JdbcTemplate jt;
DatabaseMetaData dbMetaData;
String catalog;
@Autowired
public void setJdbcTemplate(JdbcTemplate jt) {
this.jt = jt;
try {
Connection conn = jt.getDataSource().getConnection();
dbMetaData = conn.getMetaData();
catalog = conn.getCatalog(); // catalog 其实也就是数据库名
} catch (SQLException e) {
e.printStackTrace();
}
}
public void fetchRelation(Relation relation) throws SQLException {
ResultSet tablesResultSet = dbMetaData.getTables(catalog, null, null, new String[] { "TABLE" });
Set
relations = new HashSet<>(10);
while (tablesResultSet.next()) {
String tableName = tablesResultSet.getString("TABLE_NAME");
if(!tableName.equalsIgnoreCase(relation.getTableName())) {
ResultSet foreignKeyResultSet = dbMetaData.getImportedKeys(catalog, null, tableName);
while (foreignKeyResultSet.next()) {
String pkTablenName = foreignKeyResultSet.getString("PKTABLE_NAME");
String pkColumnName = foreignKeyResultSet.getString("FKCOLUMN_NAME");
if (relation.getTableName().equalsIgnoreCase(pkTablenName)) {
Relation r = new Relation(tableName, pkColumnName);
relations.add(r);
fetchRelation(r);
}
}
}
relation.setRelations(relations);
}
}
获取到的关系树
{
"relations": [
{
"columnName": "merchant_id",
"relations": [
{
"columnName": "notice_id",
"relations": [],
"tableName": "flyemu_notice_status"
}
],
"tableName": "flyemu_notice"
},
{
"columnName": "merchant_id",
"relations": [],
"tableName": "flyemu_leager_card"
},
{
"columnName": "merchant_id",
"relations": [
{
"columnName": "store_id",
"relations": [
{
"columnName": "order_no",
"relations": [],
"tableName": "flyemu_LIZELONG_order_detail"
}
],
"tableName": "flyemu_LIZELONG_order"
},
{
"columnName": "store_id",
"relations": [],
"tableName": "flyemu_notice_status"
},
{
"columnName": "store_id",
"relations": [
{
"columnName": "store_user_id",
"relations": [],
"tableName": "flyemu_store_user_role"
}
],
"tableName": "flyemu_store_user"
},
{
"columnName": "store_id",
"relations": [
{
"columnName": "order_no",
"relations": [],
"tableName": "flyemu_order_detail"
}
],
"tableName": "flyemu_order"
},
{
"columnName": "store_id",
"relations": [
{
"columnName": "order_no",
"relations": [],
"tableName": "flyemu_wrjh_order_detail"
}
],
"tableName": "flyemu_wrjh_order"
},
{
"columnName": "store_id",
"relations": [],
"tableName": "flyemu_enable_product"
},
{
"columnName": "store_id",
"relations": [
{
"columnName": "leager_id",
"relations": [],
"tableName": "flyemu_leager_hold_coupon"
}
],
"tableName": "flyemu_leager_user"
},
{
"columnName": "store_id",
"relations": [],
"tableName": "flyemu_store_coupon"
}
],
"tableName": "flyemu_store"
},
{
"columnName": "merchant_id",
"relations": [],
"tableName": "flyemu_merchant_img"
},
{
"columnName": "merchant_id",
"relations": [],
"tableName": "flyemu_merchant_waste"
},
{
"columnName": "merchant_id",
"relations": [
{
"columnName": "role_id",
"relations": [],
"tableName": "flyemu_role_permissions"
},
{
"columnName": "role_id",
"relations": [],
"tableName": "flyemu_user_roles"
}
],
"tableName": "flyemu_merchant_role"
},
{
"columnName": "merchant_id",
"relations": [],
"tableName": "flyemu_recharge_scheme"
},
{
"columnName": "merchant_id",
"relations": [
{
"columnName": "store_type_id",
"relations": [
{
"columnName": "store_id",
"relations": [
{
"columnName": "order_no",
"relations": [],
"tableName": "flyemu_LIZELONG_order_detail"
}
],
"tableName": "flyemu_LIZELONG_order"
},
{
"columnName": "store_id",
"relations": [],
"tableName": "flyemu_notice_status"
},
{
"columnName": "store_id",
"relations": [
{
"columnName": "store_user_id",
"relations": [],
"tableName": "flyemu_store_user_role"
}
],
"tableName": "flyemu_store_user"
},
{
"columnName": "store_id",
"relations": [
{
"columnName": "order_no",
"relations": [],
"tableName": "flyemu_order_detail"
}
],
"tableName": "flyemu_order"
},
{
"columnName": "store_id",
"relations": [
{
"columnName": "order_no",
"relations": [],
"tableName": "flyemu_wrjh_order_detail"
}
],
"tableName": "flyemu_wrjh_order"
},
{
"columnName": "store_id",
"relations": [],
"tableName": "flyemu_enable_product"
},
{
"columnName": "store_id",
"relations": [
{
"columnName": "leager_id",
"relations": [],
"tableName": "flyemu_leager_hold_coupon"
}
],
"tableName": "flyemu_leager_user"
},
{
"columnName": "store_id",
"relations": [],
"tableName": "flyemu_store_coupon"
}
],
"tableName": "flyemu_store"
}
],
"tableName": "flyemu_store_type"
},
{
"columnName": "merchant_id",
"relations": [
{
"columnName": "business_id",
"relations": [
{
"columnName": "store_id",
"relations": [
{
"columnName": "order_no",
"relations": [],
"tableName": "flyemu_LIZELONG_order_detail"
}
],
"tableName": "flyemu_LIZELONG_order"
},
{
"columnName": "store_id",
"relations": [],
"tableName": "flyemu_notice_status"
},
{
"columnName": "store_id",
"relations": [
{
"columnName": "store_user_id",
"relations": [],
"tableName": "flyemu_store_user_role"
}
],
"tableName": "flyemu_store_user"
},
{
"columnName": "store_id",
"relations": [
{
"columnName": "order_no",
"relations": [],
"tableName": "flyemu_order_detail"
}
],
"tableName": "flyemu_order"
},
{
"columnName": "store_id",
"relations": [
{
"columnName": "order_no",
"relations": [],
"tableName": "flyemu_wrjh_order_detail"
}
],
"tableName": "flyemu_wrjh_order"
},
{
"columnName": "store_id",
"relations": [],
"tableName": "flyemu_enable_product"
},
{
"columnName": "store_id",
"relations": [
{
"columnName": "leager_id",
"relations": [],
"tableName": "flyemu_leager_hold_coupon"
}
],
"tableName": "flyemu_leager_user"
},
{
"columnName": "store_id",
"relations": [],
"tableName": "flyemu_store_coupon"
}
],
"tableName": "flyemu_store"
}
],
"tableName": "flyemu_business"
},
{
"columnName": "merchant_id",
"relations": [
{
"columnName": "user_id",
"relations": [],
"tableName": "flyemu_user_roles"
}
],
"tableName": "flyemu_merchant_user"
},
{
"columnName": "merchant_id",
"relations": [
{
"columnName": "leager_id",
"relations": [],
"tableName": "flyemu_leager_hold_coupon"
}
],
"tableName": "flyemu_leager_user"
},
{
"columnName": "merchant_id",
"relations": [
{
"columnName": "user_id",
"relations": [
{
"columnName": "leager_id",
"relations": [],
"tableName": "flyemu_leager_hold_coupon"
}
],
"tableName": "flyemu_leager_user"
}
],
"tableName": "flyemu_leager_login"
}
],
"tableName": "flyemu_merchant_info"
}