Thursday, 5 September 2013

Get Tables And Columns Details In Schema Using JDBC

This is a sample program written using java and JDBC API , to print all the tables in a database schema . It also prints columns available in the particular table.

package com.datamigration.main;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.datamigration.db.DataBase;

/**
 * 
 * 
 * @author Abhishek Somani
 * 
 */
public class PrintTable {
 
 public static String SCHEMA_NAME="${YOUR_SCHEMA_NAME}";
 
 public static void main(String[] args) {
  
  //create and setup your database and get db connection
  DataBase db = new DataBase();
  db.init();
  
  try {
   Connection con = db.getConnection();
    DatabaseMetaData metaData = con.getMetaData();
     
    String tableType[] = {"TABLE"};
    
    StringBuilder builder = new StringBuilder();
    
   ResultSet result = metaData.getTables(null,SCHEMA_NAME,null,tableType);
    while(result.next())
    {
     String tableName = result.getString(3);
     
     builder.append(tableName + "( ");
     ResultSet columns = metaData.getColumns(null,null,tableName,null);
     
     while(columns.next())
     {
      String columnName = columns.getString(4);
      builder.append(columnName);
      builder.append(",");
     }
     builder.deleteCharAt(builder.lastIndexOf(","));
     builder.append(" )");
     builder.append("\n");
     builder.append("----------------");
     builder.append("\n");
     
     
    }
    
   System.out.println(builder.toString());
   
   
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  
  
 }

}


This program will print tables along with colmuns like this :
TABLE1( ID,NAME,STATUS,CREATED_AT,UPDATED_AT)
-----------------------------------
TABLE2( ID,NAME,STATUS,CREATED_AT,UPDATED_AT)
Post Comments And Suggestions !!