// 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