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

/**
 *
 * @author Pete Cappello
 */
public class RangeQuery 
{
    static final String DATABASE_URL = "jdbc:derby://localhost:1527/Mandelbrot";
    static final String USERNAME = "nbuser";
    static final String PASSWORD = "nbuser";
    
    private Connection connection;
    private PreparedStatement selectModelsInRange;
    private PreparedStatement insertNewModel;
    
    RangeQuery()
    {
        try 
        {
           connection = DriverManager.getConnection( DATABASE_URL, USERNAME, PASSWORD );

           // create query that selects all entries in the AddressBook
           selectModelsInRange = connection.prepareStatement( 
                     "SELECT REALCOORDINATE, IMAGCOORDINATE, EDGELENGTH, ITERATIONLIMIT "
                   + "FROM APP.MODELS "
                   + "WHERE REALCOORDINATE > ? AND REALCOORDINATE < ? " 
                   + "AND   IMAGCOORDINATE > ? AND IMAGCOORDINATE < ? ");

           // create insert that adds a new entry into the database
           insertNewModel = connection.prepareStatement( 
                "INSERT INTO APP.MODELS " 
              + "( ID, REALCOORDINATE, IMAGCOORDINATE, EDGELENGTH, ITERATIONLIMIT ) " 
              + "VALUES ( ?, ?, ?, ?, ? )" 
                   );
        } 
        catch ( SQLException sqlException )
        {
           sqlException.printStackTrace();
           System.exit( 1 );
        } 
    }
    
    void insert( int id, double realCoordinate, double imagCoordinate, double edgeLength, int iterationLimit )
    {
        try
        {
            insertNewModel.setInt( 1, id );
            insertNewModel.setDouble( 2, realCoordinate );
            insertNewModel.setDouble( 3, imagCoordinate );
            insertNewModel.setDouble( 4, edgeLength );
            insertNewModel.setDouble( 5, iterationLimit );
            insertNewModel.executeUpdate();
        }
        catch ( SQLException sQLException )
        {
            sQLException.printStackTrace();
        }
    }
    
    void displayModelsInRange( double rLow, double iLow, double rHigh, double iHigh )
    {
        ResultSet resultSet = null;
        try 
        {
            System.out.println("\nQuery Results\n");
            // print headings
            System.out.printf( "%-8s\t", "REALCOORDINATE" );
            System.out.printf( "%-8s\t", "IMAGCOORDINATE" );
            System.out.printf( "%-8s\t", "EDGELENGTH" );
            System.out.printf( "%-8s\t", "ITERATIONLIMIT" );
            System.out.println();
            
            selectModelsInRange.setDouble( 1, rLow );
            selectModelsInRange.setDouble( 2, rHigh );
            selectModelsInRange.setDouble( 3, iLow );
            selectModelsInRange.setDouble( 4, iHigh );
            resultSet = selectModelsInRange.executeQuery();
            while ( resultSet.next() )
            {
                // print row (Model)
                System.out.printf( "%-8s\t", resultSet.getDouble( "REALCOORDINATE" ) );
                System.out.printf( "%-8s\t", resultSet.getDouble( "IMAGCOORDINATE" ) );
                System.out.printf( "%-8s\t", resultSet.getDouble( "EDGELENGTH" ) );
                System.out.printf( "%-8s\t", resultSet.getInt( "ITERATIONLIMIT" ) );
                System.out.println();
            }
        }
        catch ( SQLException sQLException )
        {
            sQLException.printStackTrace();
        }
        finally
        {
            try
            {
                resultSet.close();
            }
            catch ( SQLException sQLException )
            {
                sQLException.printStackTrace();
            }
        }    
    }
    
    public static void main( String args[] )
   {
      RangeQuery rangeQuery = new RangeQuery();
      rangeQuery.displayModelsInRange( -1, -1, 1, 1 );
      rangeQuery.insert( 22, -0.3, -0.3, 0.4, 512 );
      rangeQuery.displayModelsInRange( -1, -1, 1, 1 );
   }
}