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




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;
       }
}