Reading and Writing Customers from the Database

I l @ ve RuBoard

Now you're ready to add the code to find, add, and modify customers. This means reading and writing from the database, which means that you're going to have to create SQL statements to read and write the objects. However, embedding the SQL directly into Java source is both messy and unstable because it requires that you recompile the class every time the SQL statement needs to be changed.

Using Resource Bundles

The ResourceBundle class can be used (and will be used later in the book) to provide different messages depending on locale, but it can also be used for the more mundane purpose of reading value out of a property file. In this case, you'll create a property file called com.bfg.customer.SQLQueries to hold the specific queries for the class. It looks like the file shown in Listing 8.2.

Listing 8.2 com.bfg.customer.SQLQueries.properties
 findQuery=SELECT * FROM CUSTOMER WHERE EMAIL_ADDRESS = ? updateQuery=UPDATE CUSTOMER SET EMAIL_ADDRESS=?, PASSWORD=? WHERE CUSTOMER_ID=? createQuery=INSERT INTO CUSTOMER (EMAIL_ADDRESS,PASSWORD) VALUES (?, ?) deleteQuery=DELETE FROM CUSTOMER WHERE CUSTOMER_ID=? 

Executing Reads and Writes to the Database

Now that you have a convenient place to store the SQL statements, you need to write the methods that will use them (see Listing 8.3).

Listing 8.3 Additions to Customer.java
 import org.apache.turbine.services.db.TurbineDB; import org.apache.turbine.util.db.pool.DBConnection; import org.apache.turbine.util.TurbineConfig; import com.bfg.exceptions.CustomerActivityException; import com.bfg.exceptions.DuplicateEmailAddressException; import java.sql.*; import java.util.ResourceBundle; import org.apache.log4j.Category; . .     private static ResourceBundle sql_bundle =       ResourceBundle.getBundle("com.bfg.customer.SQLQueries");     public static Customer findCustomer(String emailAddress)       throws CustomerActivityException {       DBConnection dbConn = null;       Customer cust = null;       try           {               dbConn = TurbineDB.getConnection();               if (dbConn == null) {                   cat.error("Can't get database connection");                   throw new CustomerActivityException();               }               PreparedStatement pstmt =                   dbConn.prepareStatement(sql_bundle.getString("findQuery"));               pstmt.setString(1, emailAddress);               ResultSet rs = pstmt.executeQuery();               if (rs.next()) {                   cust = new Customer();                   cust.setCustomerId(rs.getInt("CUSTOMER_ID"));                   cust.setEmail(rs.getString("EMAIL_ADDRESS"));                   cust.setPassword(rs.getString("PASSWORD"));               }               rs.close();               pstmt.close();         }     catch (Exception e)         {             cat.error("Error during findCustomer", e);             throw new CustomerActivityException();         }     finally         {             try                 {                     TurbineDB.releaseConnection(dbConn);                 }             catch (Exception e)                 {                     cat.error("Error during releaseConnection", e);                 }         }     return cust; } 

A new Exception has been defined ” CustomerActivityException . You'll use this signal to call code that some kind of failure occurred during the processing of the operation. The advantage of throwing an explicit exception rather than returning null, for example, is that it forces the caller to at least be aware of the possibility of failure: Java will require the calling function to either catch or pass the exception explicitly.

The first thing you should do is to try to get a database connection from the pool. It's good coding practice to put in checks on return values like this one and flag an error if you didn't get what you expected. Once you have it, grab the appropriate SQL query string from the resource bundle and prepare a statement using that string. Then set the parameter for the e-mail address to the argument that was passed in, execute the query, and create a new Customer object if a match was found.

Be careful to catch any exceptions, and make sure that you return the connection to the pool no matter what. Otherwise , you can end up leaking connections and eventually exhausting your open database connection limit. You might also try to close open statements in the finally clause, to avoid leaving stray database cursors open. However, you must make sure that the statement has been set before trying to do a close on it; otherwise, you could end up trying to close null.

Now that you can read from the database, you need to write the other three methods. They follow along the same lines, as shown in Listing 8.4.

Listing 8.4 More Additions to Customer.java
 public void createCustomer()   throws CustomerActivityException, DuplicateEmailAddressException {   if (findCustomer(getEmail()) != null) {       throw new DuplicateEmailAddressException();   }   DBConnection dbConn = null;   try       {           dbConn = TurbineDB.getConnection();           if (dbConn == null) {               cat.error("Can't get database connection");               throw new CustomerActivityException();           }           PreparedStatement pstmt =               dbConn.prepareStatement(sql_bundle.getString("createQuery"));           pstmt.setString(1, getEmail());           pstmt.setString(2, getPassword());           pstmt.executeUpdate();           pstmt.close();           pstmt =               dbConn.prepareStatement(sql_bundle.getString("findQuery"));           pstmt.setString(1, getEmail());           ResultSet rs = pstmt.executeQuery();           if (rs.next()) {               setCustomerId(rs.getInt("CUSTOMER_ID"));           } else {                cat.error("Couldn't find record for new Customer");           }           rs.close();           pstmt.close();     } catch (Exception e)     {                cat.error("Error during createCustomer", e);                throw new CustomerActivityException();            }        finally            {                try                    {                        TurbineDB.releaseConnection(dbConn);                    }                catch (Exception e)                    {                        cat.error("Error during release connection", e);                    }            }     }     public void updateCustomer() throws CustomerActivityException {       DBConnection dbConn = null;       try           {               dbConn = TurbineDB.getConnection();               if (dbConn == null) {                   cat.error("Can't get database connection");                   throw new CustomerActivityException();               }               PreparedStatement pstmt =                   dbConn.prepareStatement(sql_bundle.getString("updateQuery"));               pstmt.setString(1, getEmail());               pstmt.setString(2, getPassword());               pstmt.setInt(3, getCustomerId());               pstmt.executeUpdate();               pstmt.close();           }       catch (Exception e)           {               cat.error("Error during updateCustomer", e);               throw new CustomerActivityException();           }       finally           {               try                   {                        TurbineDB.releaseConnection(dbConn);                    }                catch (Exception e)                    {                        cat.error("Error during release connection", e);                    }            }     }     public void deleteCustomer() throws CustomerActivityException {       DBConnection dbConn = null;       try           {               dbConn = TurbineDB.getConnection();               if (dbConn == null) {                   cat.error("Can't get database connection");                   throw new CustomerActivityException();               }               PreparedStatement pstmt =                   dbConn.prepareStatement(sql_bundle.getString("deleteQuery"));               pstmt.setInt(1, getCustomerId());               pstmt.executeUpdate();               pstmt.close();           }       catch (Exception e)           {               cat.error("Error during deleteCustomer", e);               throw new CustomerActivityException();           }       finally           {               try                   {                       TurbineDB.releaseConnection(dbConn);                   }               catch (Exception e)                   {                       cat.error("Error during release connection", e);                   }           }     } 

Things to note: In the create method, you need to look up the customer ID after you create the new customer record. Remember that the ID is an autoincrementing field, so you don't know the ID that you're going to get when you insert the new row. Because each e-mail address is unique, you can use the address as the key to look up the newly inserted row.

However, you do have to consider what will happen if someone tries to create an account using an e-mail address that already exists. The insert statement will fail and throw an exception, which will be caught, causing the customer ID field of the object to remain null. Rather than let this happen, the application should check to see if there's already a customer with that e-mail address. It should throw an explicit exception in this case, which will have to be caught and dealt with by the calling code.

I l @ ve RuBoard


MySQL and JSP Web Applications. Data-Driven Programming Using Tomcat and MySQL
MySQL and JSP Web Applications: Data-Driven Programming Using Tomcat and MySQL
ISBN: 0672323095
EAN: 2147483647
Year: 2002
Pages: 203
Authors: James Turner

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net