import java.sql.*;
/*****************************************************************************************
/ Generic read of data from any Mysql database table
/ Class takes any SQL SELECT as args[0]
/ Example: "select * from course where description like '%java%' "  
/****************************************************************************************/

public class QueryGeneric
{
    private static String DBtype   = "MYSQL";
//  private static String DBtype   = "ORACLE";
    private static String DBaccess = "LOCAL";
//  private static String DBaccess = "REMOTE";
    private static String DBhost   = "storm.cis.fordham.edu";
//  private static String DBhost   = "workshop.sps.nyu.edu";

    public static void main(String[] args) 
    {
        String host        = "";                        //will be populated depending on Mysql vs. Oracle 
        String db          = "";                        //will be populated depending on Mysql vs. Oracle  
        String user        = "demo"; 
        String pswd        = "demo"; 
        Connection connect =  null;
        Statement  stmt    =  null;
        String     sql     =  null;
        ResultSet  rs      =  null;
        String[]   colNames;                            //array for col names
        String[][] data;                                //2dim array for col values

        try 
        {
            if (DBtype == "MYSQL")
            {
                Class.forName("com.mysql.cj.jdbc.Driver");                  //dynamically load the JDBC Driver class
                if (DBaccess == "LOCAL")  
                    host = "jdbc:mysql://localhost/";                       //from localhost
                if (DBaccess == "REMOTE") 
                    host = "jdbc:mysql://"+ DBhost +"/";                    //from remote computer 
                db = "demo";                                                //database name                                                        
            }
            if (DBtype == "ORACLE")
            {
                Class.forName("oracle.jdbc.driver.OracleDriver");           //dynamically load the JDBC Driver class
                if (DBaccess == "LOCAL")  
                    host = "jdbc:oracle:thin:@localhost:1521/";             //from localhost
                if (DBaccess == "REMOTE") 
                    host = "jdbc:oracle:thin:@"+ DBhost + ":1521/";         //from remote computer 
                db = "orcl";                                                //database name                                                        
            }

            String host_db = host + db;                                 //append hostname + database name 
//          System.out.println(host_db);

            connect = DriverManager.getConnection(host_db, user, pswd);

            stmt    = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,   //with the ability to go up & down 
                                              ResultSet.CONCUR_READ_ONLY);         //through the cursor        

            sql = (args.length) > 0                                 //if args[0] is provided 
                ? args[0]                                           //use it
                : "SELECT * FROM student";                          //else provide alternate SQL

            System.out.println(sql);

            rs = stmt.executeQuery(sql);                            //execute the query
            
            ResultSetMetaData metadata = rs.getMetaData();          //get metadata column headers 

            int num_of_cols = metadata.getColumnCount();            //obtain column count                   

            rs.last();                                              //move to last record
            int num_of_rows = rs.getRow();                          //obtain the last row num 
            rs.beforeFirst();                                       //reset to start

            colNames = new String[num_of_cols];                     //create an array for column headers
            data     = new String[num_of_rows][num_of_cols];        //create an 2 dim array for the data
        
            for (int col=0; col < num_of_cols; col++)               //get column names 
            {   
                String colName = metadata.getColumnName(col+1);     //index starts at 1 not 0 
                colNames[col]  = colName;                           //store in array for later use (if any)
                System.out.print("["+colName.toUpperCase()+"]\t");  //print column name
            }
            System.out.println();

            for (int row=0; row < num_of_rows; row++)               //loop thru each row 
            {   
                rs.next();                                          //next record 
                for (int col=0; col < num_of_cols; col++)           //loop thru each column within each row 
                {   
                    String colValue = rs.getString(col+1);          //get value as string 
                    data[row][col]  = colValue;                     //store in array for later use (if any)
                    System.out.print(colValue + "\t");              //print the value
                }
                System.out.println();
            }       
        }      
        catch (Exception e) 
        {
            System.out.println(e);
            e.printStackTrace();
        } 
        finally 
        {
            if (stmt != null) 
            {
                try 
                {
                    stmt.close();
                } 
                catch (SQLException e) {}       // let JVM handle it 
            }
            if (connect != null) 
            {
                try 
                {
                    connect.close();
                } 
                catch (SQLException e) {}       // let JVM handle it 
            }
        }
    }
}