Oshan's Blog
Sunday, October 27, 2013
Sunday, May 13, 2012
Getting Primary key and Foreign Key Relations from a Database Java
This code is used to Retrieve primary key and foreign key relations between all tables in a Database, code tested with SQL Server, output will be stored in MetaData.dat
Format of the O/P file will be
PrimaryKeyTable.ColumnName~ForignKeyTable.ColumnName~~ForignKeyTable.ColumnName
Format of the O/P file will be
PrimaryKeyTable.ColumnName~ForignKeyTable.ColumnName~~ForignKeyTable.ColumnName
import
java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import
java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import
java.sql.SQLException;
public class clsKeys {
public static void main(String args[])
{
subGetKeys();
}
public static void subGetKeys() {
String
strRow;
try {
jdbc
objJDBC = new jdbc();
Connection
con = objJDBC.getConnection();
/* jdbc is a class that i have created
for my database connections
u
can alternately use
String url = "jdbc:mysql://localhost:3306/";
String dbName = "jdbctutorial";
String driver =
"com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url+dbName,userName,password);*/
ResultSet
rsKeys;
DatabaseMetaData
meta = con.getMetaData();
String[]
TABLE_TYPES = { "TABLE" };
ResultSet
tables = meta.getTables(null, null, null, TABLE_TYPES);
FileWriter
fstream = new FileWriter("MetaData.dat");
BufferedWriter
out = new
BufferedWriter(fstream);
while (tables.next()) {
rsKeys
= meta.getExportedKeys(con.getCatalog(), null, tables
.getString("TABLE_NAME"));
strRow
= subGenerateRow(rsKeys);
System.out.println(strRow);
if(strRow.length()>0)
{
out.write(strRow);
out.newLine();
}
}
out.close();
}
catch (SQLException e) {
e.printStackTrace();
}
catch (IOException e) {
e.printStackTrace();
}
}
public static String
subGenerateRow(ResultSet rs) throws SQLException {
String
strRow = "";
int intCounter = 0;
while (rs.next()) {
if (intCounter == 0) {
strRow
+= rs.getString("PKTABLE_NAME");
strRow
+= ".";
strRow
+= rs.getString("PKCOLUMN_NAME");
strRow
+= "~";
}
strRow
+= rs.getString("FKTABLE_NAME");
strRow
+= ".";
strRow
+= rs.getString("FKCOLUMN_NAME");
strRow
+= "~";
intCounter++;
}
if (strRow.length()
> 1) {
strRow=strRow.substring(0,
strRow.length() - 1);
}
return strRow;
}
}
Subscribe to:
Comments (Atom)