1 / 16

DataBases and SQL

This course wrap-up provides key dates and summarization for INFSY 547 Spring 2007, focusing on completing servlet projects and database interactions. Key tasks include final project submissions, presentations, and a celebration on May 3. Students will learn to process database operations using Oracle via servlets, handling customer records in Java, and outputting results in HTML format. The course emphasizes practical applications in Eclipse, including creating packages and classes, preparing SQL statements, and managing servlet lifecycle events.

keith
Télécharger la présentation

DataBases and SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DataBases and SQL INFSY 547 Spring 2007

  2. 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

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

  4. In SQL Plus

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

  6. 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.*;

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

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

  9. 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

  10. 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(); }

  11. 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.

  12. @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

  13. 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

  14. 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>

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

  16. .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

More Related