SQLTable

// 2012-04-15 johnpfeiffer requires SQLColumn

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;

public class SQLTable
{
    private String databaseName;
    private String tableName;
    private int rowCount;
    private int columnCount;
    private ArrayList <SQLColumn> columnHeaders;

    SQLTable( String database , String name , Connection c )
    {
        if( database != null && !database.isEmpty() && name != null && !name.isEmpty() && c != null)
        {
            this.databaseName = database;
            this.tableName = name;
            try{
                calculateRowCount( c );
                calculateColumnCount( c );
                columnHeaders = calculateColumnHeaders( c );

            }catch( SQLException sqle )
            {       System.err.println( sqle.getMessage() );
            }
        }else
        {       throw new IllegalArgumentException( "database, tablename, or connection is null or empty" );
        }
    }

    protected String getDatabaseName()
    {       return databaseName;
    }
  protected String getTableName()
    {       return tableName;
    }
    protected int getRowCount()
    {       return rowCount;
    }
  protected int getColumnCount()
    {       return columnCount;
    }
  protected ArrayList <SQLColumn> getColumnHeaders()
  {     return columnHeaders;
  }

  private void calculateRowCount( Connection c ) throws SQLException
    {
        if( c != null )
        {
            String query = "select count(*) from " + databaseName + "." + tableName + ";";
            PreparedStatement pstmt = c.prepareStatement( query );
            ResultSet rs = pstmt.executeQuery();
            while( rs.next() )
            {       this.rowCount = rs.getInt( 1 ) ;        //select count(*) only returns one integer
            }
        }else
        {       throw new IllegalArgumentException( "Connection cannot be null" );
        }
    }

  private void calculateColumnCount( Connection c  ) throws SQLException
    {
        if( c != null )
        {
            String query = "desc " + databaseName + "." + tableName + ";";
            PreparedStatement pstmt = c.prepareStatement( query );
            ResultSet rs = pstmt.executeQuery();
            ResultSetMetaData rsmeta = rs.getMetaData();
            columnCount = rsmeta.getColumnCount();
        }
    }

    private ArrayList <SQLColumn> calculateColumnHeaders( Connection c ) throws SQLException
    {
        String query = "desc " + databaseName + "." + tableName + ";";
        PreparedStatement pstmt = c.prepareStatement( query );
        ResultSet rs = pstmt.executeQuery();
        ResultSetMetaData rsmeta = rs.getMetaData();
        ArrayList <SQLColumn> headers = new ArrayList <SQLColumn>();

        while( rs.next() )
        {
            String field = null;
            String type = null;
            String nullable = null;
            String key = null;
            String defaultValue = null;
            String extra = null;

            for ( int i = 1 ; i <= columnCount ; i++ )  //column counting starts at 1
            {
                if( rsmeta.getColumnLabel( i ).equals( "Field" ) )
                {
                    if( rs.getObject( i ) != null )
                    {       field = rs.getObject( i ).toString();
                    }else
                    {       field = "null";
                    }
                }
                if( rsmeta.getColumnLabel( i ).equals( "Type" ) )
                {
                    if( rs.getObject( i ) != null )
                    {       type = rs.getObject( i ).toString();
                    }else
                    {       type = "null";
                    }
                }
                if( rsmeta.getColumnLabel( i ).equals( "Null" ) )
                {
                    if( rs.getObject( i ) != null )
                    {       nullable = rs.getObject( i ).toString();
                    }else
                    {       nullable = "null";
                    }
                }
                if( rsmeta.getColumnLabel( i ).equals( "Key" ) )
                {
                    if( rs.getObject( i ) != null )
                    {       key = rs.getObject( i ).toString();
                    }else
                    {       key = "null";
                    }
                }
                if( rsmeta.getColumnLabel( i ).equals( "Default" ) )
                {
                    if( rs.getObject( i ) != null )
                    {       defaultValue = rs.getObject( i ).toString();
                    }else
                    {       defaultValue = "null";
                    }
                }
                if( rsmeta.getColumnLabel( i ).equals( "Extra" ) )
                {
                    if( rs.getObject( i ) != null )
                    {       extra = rs.getObject( i ).toString();
                    }else
                    {       extra = "null";
                    }
                }

                if( i % columnCount == 0 )
                {
                    SQLColumn column = new SQLColumn( field , type , nullable , key , defaultValue , extra );
                    headers.add( column );
//                  System.out.println( "DEBUG: " + field + "," + type + "," + nullable + "," + key + "," + defaultValue + "," + extra );
                    field = null;
                    type = null;
                    nullable = null;
                    key = null;
                    defaultValue = null;
                    extra = null;
                }
            } //end for
        } //end while
        return headers;
    } //end calculateColumnHeaders()

} // end class