package test;

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

public class TestJDBCForMySQL {
	String driverName = "com.mysql.jdbc.Driver"; 
	String url = "jdbc:mysql://cin0389:3307/TESTDB?useCursorFetch=true"; 
	String username = "root";
    String password = "c0rdys";
    String dbName = "TESTDB";
    String tableName="TESTCHILD";
    Connection 	connection = null;
    void initialiseConnection() throws ClassNotFoundException,SQLException{
    	try {
	        Class.forName(driverName);
	        
	        connection = DriverManager.getConnection(url, username, password);
        } catch(ClassNotFoundException cne){
        	throw cne;
        }
    }
    public void testColumnDetails() throws SQLException{
		
        ResultSet rsResultSet = null;
        
        try {
	        connection.setReadOnly(true);
	        connection.setAutoCommit(true);
	        connection.setCatalog(dbName);
	        
			DatabaseMetaData jMetadata =  connection.getMetaData();
			rsResultSet = jMetadata.getColumns(dbName, null, tableName, null);
			System.out.println("*********Column Details***********");
			while(rsResultSet.next()){
				String tableName_rs = rsResultSet.getString("TABLE_NAME");
				String columnName = rsResultSet.getString("COLUMN_NAME");
				
				System.out.println("Table:"+tableName_rs+" Column name : "+columnName);
				
        	}
			
        } catch(SQLException sqle){
        	throw sqle;
        } finally{
        	
        	if(rsResultSet != null){
        		rsResultSet.close();
        	}
        }
		
		
	}
	public void testPrimaryKeys() throws SQLException{
		
        ResultSet rsResultSet = null;
       
        try {
	        connection.setReadOnly(true);
	        connection.setAutoCommit(true);
	        connection.setCatalog(dbName);
	        
			DatabaseMetaData jMetadata =  connection.getMetaData();
			rsResultSet = jMetadata.getPrimaryKeys(dbName, null, tableName);
			System.out.println("*********Primary Key Details***********");
			while(rsResultSet.next()){
				String tableName_rs = rsResultSet.getString("TABLE_NAME");
				String columnName = rsResultSet.getString("COLUMN_NAME");
				
				System.out.println("Table:"+tableName_rs+" Column name : "+columnName);
				
        	}
			
        } catch(SQLException sqle){
        	throw sqle;
        } finally{
        	
        	if(rsResultSet != null){
        		rsResultSet.close();
        	}
        }
		
		
	}
	public void testIndices() throws SQLException{
		
        
        ResultSet rsResultSet = null;
       
        try {
	        connection.setReadOnly(true);
	        connection.setAutoCommit(true);
	        connection.setCatalog(dbName);
	        
			DatabaseMetaData jMetadata =  connection.getMetaData();
			rsResultSet = jMetadata.getIndexInfo(dbName, null, tableName, true, false);
			System.out.println("*********Index Details***********");
			while(rsResultSet.next()){
				String tableName_rs = rsResultSet.getString("TABLE_NAME");
				String columnName = rsResultSet.getString("COLUMN_NAME");
				String indexName = rsResultSet.getString("INDEX_NAME");
				
				System.out.println("Table:"+tableName_rs+" Column name : "+columnName + " index Name:"+indexName);
				
        	}
			
        } catch(SQLException sqle){
        	throw sqle;
        } finally{
        	
        	if(rsResultSet != null){
        		rsResultSet.close();
        	}
        }
		
		
	}
	public void testImportedKeys() throws SQLException{
		
       
        ResultSet rsResultSet = null;
       
        try {
	        connection.setReadOnly(true);
	        connection.setAutoCommit(true);
	        connection.setCatalog(dbName);
	        
			DatabaseMetaData jMetadata =  connection.getMetaData();
			rsResultSet = jMetadata.getImportedKeys(dbName, null, tableName);
			System.out.println("*********Imported Keys***********");
			while(rsResultSet.next()){
				String pktableName = rsResultSet.getString("PKTABLE_NAME");
				String pkColumnName = rsResultSet.getString("PKCOLUMN_NAME");
				String fktableName = rsResultSet.getString("FKTABLE_NAME");
				String fkColumnName = rsResultSet.getString("FKCOLUMN_NAME");
				String fkName = rsResultSet.getString("FK_NAME");
				String pkName = rsResultSet.getString("PK_NAME");
				
				System.out.println("Primary Key Table:"+pktableName+" Column name : "+pkColumnName);
				System.out.println("Foreign Key Table:"+fktableName+" Column name : "+fkColumnName);
        	}
			
        } catch(SQLException sqle){
        	throw sqle;
        } finally{
        	
        	if(rsResultSet != null){
        		rsResultSet.close();
        	}
        }
		
		
	}
	public void testExportedKeys() throws ClassNotFoundException,SQLException{
		
        
        ResultSet rsResultSet = null;
       
        try {
	        connection.setReadOnly(true);
	        connection.setAutoCommit(true);
	        connection.setCatalog(dbName);
	        
			DatabaseMetaData jMetadata =  connection.getMetaData();
			rsResultSet = jMetadata.getExportedKeys(dbName, null, tableName);
			System.out.println("*********Exported Keys***********");
			while(rsResultSet.next()){
				String pktableName = rsResultSet.getString("PKTABLE_NAME");
				String pkColumnName = rsResultSet.getString("PKCOLUMN_NAME");
				String fktableName = rsResultSet.getString("FKTABLE_NAME");
				String fkColumnName = rsResultSet.getString("FKCOLUMN_NAME");
				String fkName = rsResultSet.getString("FK_NAME");
				String pkName = rsResultSet.getString("PK_NAME");
				
				System.out.println("Primary Key Table:"+pktableName+" Column name : "+pkColumnName);
				System.out.println("Foreign Key Table:"+fktableName+" Column name : "+fkColumnName);
			}
        } catch(SQLException sqle){
        	throw sqle;
        } finally{
        	
        	if(rsResultSet != null){
        		rsResultSet.close();
        	}
        }
		
		
	}
	void closeAll() throws SQLException{
		if(connection != null){
			connection.close();
		}
	}
	public static void main(String[] args){
		TestJDBCForMySQL testObj = new TestJDBCForMySQL();
		try 
		{
			testObj.initialiseConnection();
			testObj.testColumnDetails();
			testObj.testPrimaryKeys();
			testObj.testIndices();
			testObj.testExportedKeys();
			testObj.testImportedKeys();
			testObj.closeAll();
		} catch(Exception e){
			e.printStackTrace();
		}
		
	}
}