DataBases and SQL

DataBases and SQL INFSY 547 Spring 2007

Course Wrap Up April 12: Complete Work on Servlets Review of Team Projects Close of Portfolio Work April 19: Review Form ideas, Team Meetings and presentation guidelines April 26: Project Due Date Develop presentations May 3: Final Presentation and Party

Servlet Processing • Process a database (similar to an .xml file) • Output an .html file • Interact with Oracle via a servlet

In SQL Plus

Lab 10: Start with Eclipse • Create a project in Eclipse – (Infsy547OracleCustomerLab.java) • External .jar files • classes12.jar • servlet-api.jar

Lab 10: Start with Eclipse • Create a package – infsy547oraclecustomerlab • Create a class – by the same name as the project • Add import statements • import java.io.*; • import java.sql.*; • import javax.servlet.*; • import javax.servlet.http.*;

Servlet Assuming Names Table public class Infsy547OracleCustomer extends HttpServlet { private Connection connection; private PreparedStatement insertRecord; private int acctNumber; <add string name, address, city, state> private PrintWriter out; Names acctNumber* name address city state

public void init( ServletConfig config ) throws ServletException { <call getConnected method> }

private void getConnected () { try { Class.forName( "oracle.jdbc.driver.OracleDriver" ); connection = DriverManager.getConnection ("jdbc:oracle:thin: @146.186.84.66:1521:CLDB", “<userid>",“<psword>"); // PreparedStatement to add a customer record to names table insertRecord = connection.prepareStatement ( "INSERT INTO names (name, address, city, state, accountnumber) " + "VALUES(?, ?, ?, ?, ?)"); } catch(ClassNotFoundException cnf) { System.err.println(cnf.getMessage()); cnf.printStackTrace(); } catch (SQLException sqlex) { System.err.println(sqlex.getMessage()); sqlex.printStackTrace(); } } Loads the driver for Oracle

protected void doPost( HttpServletRequest request, HttpServletResponse response ) throws ServletException, IOException { // set up response to client response.setContentType( "text/html" ); out = response.getWriter(); // start XHTML document out.println( "<?xml version = \"1.0\"?>" ); out.println( "<!DOCTYPE html PUBLIC \"-//W3C//DTD " + "XHTML 1.0 Strict//EN\" \"http://www.w3.org" + "/TR/xhtml1/DTD/xhtml1-strict.dtd\">" ); out.println("<html xmlns = \"http://www.w3.org/1999/xhtml\">" ); // head section of document out.println( "<head>" ); acctNumber = Integer.parseInt(request.getParameter("accountNumber")); name = request.getParameter("Name"); <write code to request the other necessary parameters> <call a method, insertRecord> out.println( "<title>Thank you!</title>" ); out.println( "</head>" ); out.println( "<body>" ); out.println( "<p>Thank you for adding your record." ); out.println( "</pre></body></html>" ); out.close(); }

public void insertRecord () { try { // update total for current survey response insertRecord.setString( 1, name ); insertRecord.setString( 2, address ); <insert the city and state> insertRecord.setInt( 5, acctNumber); insertRecord.executeUpdate(); } catch ( SQLException sqlException ) { sqlException.printStackTrace(); out.println( "<title>Error</title>" ); out.println( "</head>" ); out.println( "<body><p>Database error occurred. " ); out.println( "Try again later.</p></body></html>" ); out.close(); } } Note: Will not complete the html on previous page if this occurs.

@Override public void destroy() { // attempt to close statements and database connection try { insertRecord.close(); connection.close(); } catch( SQLException sqlException ) { sqlException.printStackTrace(); } } // end of destroy method

Exception Terminology • Exceptions represent error conditions • Exceptions isolate the code that deals with error situations • Throwing: the act of detecting an abnormal condition • Searches for a handler • catchesthe exception • Try Block: Encloses one or more java statements

HTML <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>Insert Customer</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body> <?xml version="1.0" encoding="iso-8859-1"?> <form method = "post" action = "servlet/Infsy547OracleCustomerLab"> <label>Account Number</label> <input name="accountNumber" type="text"><br><br> <do the same for the remainder of the variables> <br/><br/> <input name="Save" type="submit" value="Save"> </form> </body> </html>

Complete • Place files appropriately in TomCat structure • Add to web.xml • Start TomCat • Start you servlet

.java file from this lab • uploaded to the DataBase Connectivity Drop Box • In ANGEL under the Lessons Tab • .html file • Upload into HTML Drop Box

DataBases and SQL