Java Program to List Tables and Their Relationships in Database

Here is sample Java Program to List all Tables and their relationships in database


/**
*
*/
package com.research.sql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
* @author Ashwin
*
*/
public class ObjectGrapher {

/**
* @param args
*/
public static void main(String[] args) {
try {
Connection con = DBUtils.getConnection();
DatabaseMetaData metaData = con.getMetaData();
ResultSet rs = metaData.getTables(null, null, "%", null);
List tableNamesList = new ArrayList();
Map tableMap = new HashMap();

while (rs.next()) {
String tableName = rs.getString(3);
tableNamesList.add(tableName);
}
if (rs != null) {
rs.close();
}

// Iterate Throught the List
for (int i = 0; i < tableNamesList.size(); i++) {

for (int j = 0; j < tableNamesList.size(); j++) {

// If both are same tablename we dnt need to find any
// relation
if (i == j) {
continue;
}

String primaryTable = (String) tableNamesList.get(i);
String foreignTable = (String) tableNamesList.get(j);

rs = metaData.getCrossReference(null, null, primaryTable,
null, null, foreignTable);

while (rs.next()) {
DBTable primaryDBTable = null;
DBTable foreignDBTable = null;

String primaryTableName = rs.getString("PKTABLE_NAME");
String foreignTableName = rs.getString("FKTABLE_NAME");

if (tableMap.get(primaryTableName) != null) {
primaryDBTable = (DBTable) tableMap
.get(primaryTableName);
} else {
primaryDBTable = new DBTable();
primaryDBTable.setTableName(primaryTableName);
}

if (tableMap.get(foreignTableName) != null) {
foreignDBTable = (DBTable) tableMap
.get(foreignTableName);
} else {
foreignDBTable = new DBTable();
foreignDBTable.setTableName(foreignTableName);
}

foreignDBTable.addRelations(primaryDBTable);

tableMap.put(foreignTableName, foreignDBTable);
tableMap.put(primaryTableName, primaryDBTable);

System.out.println("Primary Key Table Name :"
+ rs.getString("PKTABLE_NAME")
+ " Primary Key Column :"
+ rs.getString("PKCOLUMN_NAME"));
System.out.println("Foreign Key Table Name :"
+ rs.getString("FKTABLE_NAME")
+ " Foreign Key Column :"
+ rs.getString("FKCOLUMN_NAME"));
System.out
.println("--------------------------------------------------------------------");
}

if (rs != null) {
rs.close();
}
}
}

} catch (Exception e) {
e.printStackTrace();
}
}

}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s