/**
 * An address book Model Database Access - MVC pattern
 *    
 * Query the database for matching lastnames
 * Loop through resulting output and place in AddrBookEntry array
 */
import java.sql.*;

public class AddrBookModelDB implements AddrBookModel
{
    private AddrBookControl control;               //A reference to the control object
    private AddrBookEntry[] found;                 //An array of address book entry objects matching search

    private String     host    = "jdbc:oracle:thin:@localhost:1521/";       
    private String     db      = "orcl"; 
    private String     user    = "demo"; 
    private String     pswd    = "demo"; 
    private Connection connect =  null;
    private Statement  stmt    =  null;
    private String     sql     =  null;
    private ResultSet  rs      =  null;


    AddrBookModelDB(AddrBookControl c)            //constructor
    {
        this.control = c;
    }

    public AddrBookEntry[] search(String searchLastname)        //search method                           
    {                                                           //returns array of matching entries        
        try 
        {
            found = new AddrBookEntry[100];
            int i = 0;

            Class.forName("oracle.jdbc.driver.OracleDriver");   //dynamically load
                                                                //the JDBC Drive class
            String host_db = host + db;
            connect = DriverManager.getConnection(host_db, user, pswd);

            stmt = connect.createStatement();                   //create a statement object

            searchLastname = searchLastname.toLowerCase();

            try
            {
                sql  = "SELECT * FROM addrbook   "   
                     + "WHERE LOWER(lname) LIKE '" + searchLastname + "%' "
                     + "ORDER BY 1               ";

                rs = stmt.executeQuery(sql);
            }
            catch (Exception e)
            {
                System.out.println(e);
            }
            while(rs.next())
            {
                String lname  = rs.getString("lname");       //retrieve the data
                String fname  = rs.getString("fname");
                String street = rs.getString("street");
                String city   = rs.getString("city");
                String state  = rs.getString("state");
                String zip    = rs.getString("zip");
                String phone  = rs.getString("phone");

                AddrBookEntry entry = new AddrBookEntry(lname,fname,street,city,state,zip,phone);  //create an address book entry object

                found[i++] = entry;                             //save object in the array
            }  
        } 
        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
                }
            }
        }
        return(found);
    }
}