1 / 89

Servlets Chapter 9

Servlets Chapter 9. database connectivity. Servlets and db . Messaging, storefronts and search engines all require databases. Such sites may be complicated to build and have performance issues. We will use SQL and JDBC. The JDBC and servlet API are a good solution to db issues. lifecycle.

rosalyn
Download Presentation

Servlets Chapter 9

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. Servlets Chapter 9 database connectivity

  2. Servlets and db • Messaging, storefronts and search engines all require databases. • Such sites may be complicated to build and have performance issues. • We will use SQL and JDBC. • The JDBC and servlet API are a good solution to db issues.

  3. lifecycle • The servlet lifecycle allows servlets to maintain pools of connections to a database. • Additionally, as per Chapter 3, servlets run in the jvm and have low server load. Once loaded the server thread may remain in the server until it is shutdown.

  4. Platform independence • Servlets written for oracle can easily be modified for sybase, mysql or odbc. • Text does many connection types. I only do mysql.

  5. Connectors • Connecting to mysql from java requires a connector. • Applications and servlets can connect to the db. • MYSQL listens on port 3306 • You’ll have to go to the mysql site to download mysql-connector-java .zip • Unzip, and put the jar file in your classpath.

  6. Getting connections • Imports:import java.sql.*; • The first step in using a JDBC driver to get a db connection in your application involves loading the specific driver class into the application’s jvm. • One way to do it is to use the Class.forName() method: Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); • Once loaded, the driver registers itself with the java.sql.DriverManager class as an available db driver. • Next step is to ask the driver manager to open a connection to a given db specified in a URL. The method used is DriverManager.getConnection(): Connection con= DriverManager.getConnection(“jdbc etc”,”user”,”pw”);

  7. MYSQL admin

  8. administration • Some slides show the mysqlcc (control center) but since we already have apache/php it is easier to continue to use PHPMyAdmin. • You’ll need apache running to administer mysql using phpmyadmin. • If Apache and Tomcat run on the same port you’ll have a problem. • By default, apache is at 80 and tomcat is at 8080 but if you’ve changed those settings you might have trouble.

  9. MYSQL admin and MYSQLcontrol center • Download and install mysql. • Run MYSQL from the admintool (icon): • A little traffic light icon with a red light will appear lower right monitor screen. • Rt-click this and select NT. (Selecting showme will open the mysql admin GUI) • First, shutdown the service, then start the service standalone. • The traffic light should be green indicating that mysql is running. • MySQLMyAdmin is a good GUI for managing your db

  10. MySQLCC

  11. Some remarks • Looking at user admin in the control center you can add users or set pws. (rt click user admin selection) • Security is less tight for the “test” db, so that is where my examples are.

  12. Add user

  13. New user bob

  14. A new table: rt click tables selection in mysql control center

  15. Saving table/viewing table fields

  16. Open table/query/insert record • Under query type insert record to put some data in

  17. The phonelookup servlet import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; public class DBPhoneLookup extends HttpServlet { public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { Connection con = null; Statement stmt = null; ResultSet rs = null; res.setContentType("text/html"); PrintWriter out = res.getWriter(); try { // Load (and therefore register) the Oracle Driver Class.forName("org.gjt.mm.mysql.Driver"); // Get a Connection to the database con = DriverManager.getConnection( "jdbc:mysql://localhost/test", "bob", "justabob");//or user= “root”, pw=”” // Create a Statement object stmt = con.createStatement(); // Execute an SQL query, get a ResultSet rs = stmt.executeQuery("SELECT NAME, EMAIL FROM guestlist");//added cmt and id to this // Display the result set as a list out.println("<HTML><HEAD><TITLE>Phonebook</TITLE></HEAD>"); out.println("<BODY>"); out.println("<UL>"); while(rs.next()) { out.println("<LI>" + rs.getString("name") + " " + rs.getString("email")); }//actually added more to get all columns out.println("</UL>"); out.println("</BODY></HTML>"); } catch(ClassNotFoundException e) { out.println("Couldn't load database driver: " + e.getMessage()); } catch(SQLException e) { out.println("SQLException caught: " + e.getMessage()); } finally { // Always close the database connection. try { if (con != null) con.close(); } catch (SQLException ignored) { } } }}

  18. Phone lookup (using guestbook table)

  19. phonebook • This is about as simple as it could be. • It does not establish a pool of connections – it just opens one. • It does not get db driver and user/pw from servlet context or init params. These are hardcoded.

  20. HtmlSQL result class presents query result as an html table public class HtmlSQLResult { private String sql; private Connection con; public HtmlSQLResult(String sql, Connection con) { this.sql = sql; this.con = con; } public String toString() { // can be called at most once StringBuffer out = new StringBuffer(); // Uncomment the following line to display the SQL command at start of table // out.append("Results of SQL Statement: " + sql + "<P>\n"); try { Statement stmt = con.createStatement(); if (stmt.execute(sql)) { // There's a ResultSet to be had ResultSet rs = stmt.getResultSet(); out.append("<TABLE>\n"); ResultSetMetaData rsmd = rs.getMetaData(); int numcols = rsmd.getColumnCount();

  21. continued // Title the table with the result set's column labels out.append("<TR>"); for (int i = 1; i <= numcols; i++) out.append("<TH>" + rsmd.getColumnLabel(i)); out.append("</TR>\n"); while(rs.next()) { out.append("<TR>"); // start a new row for(int i = 1; i <= numcols; i++) { out.append("<TD>"); // start a new data element Object obj = rs.getObject(i); if (obj != null) out.append(obj.toString()); else out.append("&nbsp;"); } out.append("</TR>\n"); } // End the table out.append("</TABLE>\n"); } else { // There's a count to be had out.append("<B>Records Affected:</B> " + stmt.getUpdateCount()); } } catch (SQLException e) { out.append("</TABLE><H1>ERROR:</H1> " + e.getMessage()); } return out.toString(); } }

  22. Reuse example • can reuse connection created in advance in init method

  23. Here are just the parts that differ from previous phonebook example public void init() throws ServletException { try { // Load (and therefore register) the Oracle Driver Class.forName("org.gjt.mm.mysql.Driver"); // Get a Connection to the database con = DriverManager.getConnection( "jdbc:mysql://localhost/test", "bob", "justabob"); } catch (ClassNotFoundException e) { throw new UnavailableException("Couldn't load database driver"); } catch (SQLException e) { throw new UnavailableException("Couldn't get db connection"); } } public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { res.setContentType("text/html"); PrintWriter out = res.getWriter(); out.println("<HTML><HEAD><TITLE>Phonebook</TITLE></HEAD>"); out.println("<BODY>"); HtmlSQLResult result = new HtmlSQLResult("SELECT NAME, EMAIL, CMT, ID FROM guestlist", con);

  24. Adding a guest to our guestlist: the get methods calls post… this mimicks text example “OrderHandler” • I didn’t change the message text servlet printed out • uses connection pool class

  25. Phone lookup checks the table to verify guest added

  26. add a guest servlet public class AddAGuestPool extends HttpServlet { private ConnectionPool pool; public void init() throws ServletException { try { pool = new ConnectionPool("org.gjt.mm.mysql.Driver","jdbc:mysql://localhost/test", "bob", "justabob",5); }//get connections catch (Exception e) { throw new UnavailableException("Couldn't create connection pool"); } } public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {doPost(req,res);} public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { Connection con = null; res.setContentType("text/plain"); PrintWriter out = res.getWriter(); try { con = pool.getConnection(); // Turn on transactions con.setAutoCommit(false); Statement stmt = con.createStatement(); stmt.executeUpdate("INSERT INTO guestlist(NAME,ID,EMAIL,CMT)values ('Xavier Poindexter III','81234','Xavier@oneonta.edu','astounding salad bar')");//this would be form data con.commit(); out.println("Order successful! Thanks for your business!"); } catch (Exception e) { // Any error is grounds for rollback try {con.rollback(); } catch (Exception ignored) { } out.println("Order failed. Please contact technical support."); } finally { if (con != null) pool.returnConnection(con); } }}

  27. Connectionpool servlet in slide notes. • Blackscreen output (server screen) provides some information

  28. Guestbook servlet revisited: form posts data to db…entire servlet in slide notes

  29. Guestbook servlet revisited after pressing button (code in notes)

  30. Guestbook servlet: some notes • Init gets a pool of connections: public void init() throws ServletException { try { ServletContext context = getServletContext(); synchronized (context) { // A pool may already be saved as a context attribute pool = (ConnectionPool) context.getAttribute("pool"); if (pool == null) { // Construct a pool using our context init parameters // connection.driver, connection.url, user, password, etc pool = new ConnectionPool(new ContextProperties(context), 3); context.setAttribute("pool", pool); } } } catch (Exception e) { throw new UnavailableException( "Failed to fetch a connection pool from the context: " + e.getMessage()); } }

  31. Guestbook servlet: some notes • doGet and doPost are a series of method calls: public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { res.setContentType("text/html"); PrintWriter out = res.getWriter(); printHeader(out); printForm(out); printMessages(out); printFooter(out); } // Add a new entry, then dispatch back to doGet() public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { handleForm(req, res); doGet(req, res); }

  32. Guestbook servlet: some notes • Printing a form: private void printForm(PrintWriter out) { out.println("<FORM METHOD=POST>"); // posts to itself out.println("<B>Please submit your feedback:</B><BR>"); out.println("Your name: <INPUT TYPE=TEXT NAME=name><BR>"); out.println("Your email: <INPUT TYPE=TEXT NAME=email><BR>"); out.println("Comment: <INPUT TYPE=TEXT SIZE=50 NAME=comment><BR>"); out.println("<INPUT TYPE=SUBMIT VALUE=\"Send Feedback\"><BR>"); out.println("</FORM>"); out.println("<HR>"); }

  33. HandleForm is insert record function private void handleForm(HttpServletRequest req, HttpServletResponse res) throws ServletException { String name = req.getParameter("name"); String email = req.getParameter("email"); String comment = req.getParameter("comment"); Connection con = null; PreparedStatement pstmt = null; try { con = pool.getConnection(); // Use a prepared statement for automatic string escaping pstmt = con.prepareStatement(INSERT); long time = System.currentTimeMillis(); pstmt.setString(1, Long.toString(time)); pstmt.setString(2, name); pstmt.setString(3, email); pstmt.setString(4, comment); pstmt.executeUpdate(); } catch (SQLException e) { throw new ServletException(e); } finally { try { if (pstmt != null) pstmt.close(); } catch (SQLException ignored) { } pool.returnConnection(con); } // Make note we have a new last modified time lastModified = System.currentTimeMillis(); }

  34. printMessages method provides Read functionality private void printMessages(PrintWriter out) throws ServletException { String name, email, comment; Connection con = null; Statement stmt = null; ResultSet rs = null; try { con = pool.getConnection(); stmt = con.createStatement(); rs = stmt.executeQuery(SELECT_ALL); while (rs.next()) { name = rs.getString(1); if (rs.wasNull() || name.length() == 0) name = "Unknown user"; email = rs.getString(2); if (rs.wasNull() || email.length() == 0) name = "Unknown email"; comment = rs.getString(3); if (rs.wasNull() || comment.length() == 0) name = "No comment"; out.println("<DL>"); out.println("<DT><B>" + name + "</B> (" + email + ") says"); out.println("<DD><PRE>" + comment + "</PRE>"); out.println("</DL>"); } } catch (SQLException e) { throw new ServletException(e); } finally { try { if (stmt != null) stmt.close(); } catch (SQLException ignored) { } pool.returnConnection(con); } }

  35. doGet/doPost • Updates, inserts and delets should call doPost method • Select (read) should call doGet

  36. Deleting a record… entire servlet in notes …omitted imports and init which makes connection //Process the HTTP Post request public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); PrintWriter out = new PrintWriter (response.getOutputStream()); Statement stmt=null; String query=""; out.println("<html>"); out.println("<head><title>Servlet</title></head>"); out.println("<body>"); try { stmt = con.createStatement (); String name = request.getParameter("name"); query="DELETE from table1 where name='" + name+"'"; out.println("Query: "+query+"<BR>"); int count=stmt.executeUpdate( query ); out.println("modified records ="+count); } catch (SQLException e2) { System.out.println("SQLException: "+e2); } finally{ out.println("</body></html>"); out.close();} }

  37. Deleting a record…continued //Process the HTTP Get request public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter out = new PrintWriter (response.getOutputStream()); out.println("<html>"); out.println("<head><title>Servlet</title></head>"); out.println("<body>"); out.println("servlet does not support get"); out.println("</body></html>"); out.close(); }}

  38. Context parameters in web.xml for guestbook connection <!-- info to init db connection --> <context-param> <param-name> connection.driver </param-name> <param-value> org.gjt.mm.mysql.Driver </param-value> </context-param> <context-param> <param-name> connection.url </param-name> <param-value> jdbc:mysql://localhost/test </param-value> </context-param> <context-param> <param-name> user </param-name> <param-value> bob </param-value> </context-param> <context-param> <param-name> password </param-name> <param-value> justabob </param-value> </context-param>

  39. Using session to hold connection information

  40. Using session to hold connection information • Code in next 3 slides is from a single file, shown in text examples 9-10 and 9-11 • I changed text redirect to go to my phonebook which lists a mysql table as html table • The SessionBinderListener class (called ConnectionHolder) saves a single connection associated with each session. • No changes are needed to ConnectionHolder text code

  41. Using session to hold connection information import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; class ConnectionHolder implements HttpSessionBindingListener { private Connection con = null; public ConnectionHolder(Connection con) { // Save the Connection this.con = con; try { con.setAutoCommit(false); // transactions can extend between web pages! } catch(SQLException e) { // Perform error handling } } public Connection getConnection() { return con; // return the cargo } public void valueBound(HttpSessionBindingEvent event) { // Do nothing when added to a Session } public void valueUnbound(HttpSessionBindingEvent event) { // Roll back changes when removed from a Session // (or when the Session expires) try { if (con != null) { con.rollback(); // abandon any uncomitted data con.close(); } } catch (SQLException e) { // Report it } }}

  42. Using session to hold connection Servlet public class ConnectionPerClient extends HttpServlet { public void init() throws ServletException { try { Class.forName("org.gjt.mm.mysql.Driver");//note this is MySQL not oracle driver } catch (ClassNotFoundException e) { throw new UnavailableException("Couldn't load OracleDriver"); } } public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { res.setContentType("text/plain"); PrintWriter out = res.getWriter(); HttpSession session = req.getSession(true); Connection con; // Synchronize: Without this two holders might be created for one client synchronized (session) { // Try getting the connection holder for this client ConnectionHolder holder = (ConnectionHolder) session.getAttribute("servletapp.connection"); // Create (and store) a new connection and holder if necessary if (holder == null) { try { holder = new ConnectionHolder(DriverManager.getConnection("jdbc:mysql://localhost/test", "bob", "justabob")); //note…this is my db and my table and my user/pw info session.setAttribute("servletapp.connection", holder); } catch (SQLException e) { log("Couldn't get db connection", e); } }

  43. Using session to hold connection Servlet // Get the actual connection from the holder con = holder.getConnection(); } // Now use the connection try { Statement stmt = con.createStatement(); stmt.executeUpdate("INSERT INTO guestlist(NAME,ID,EMAIL,CMT)values ('Didier B. Applebottom','993','Didier@zztop.edu','Zappa lives!')"); //note..you need to run from a form, not hardcode entry // Charge the credit card and commit the transaction in another servlet res.sendRedirect(res.encodeRedirectURL( req.getContextPath() + “DBPhoneLookup")); //note redirect change } catch (Exception e) { // Any error is grounds for rollback try { con.rollback(); session.removeAttribute("servletapp.connection"); } catch (Exception ignored) { } out.println("Order failed. Please contact technical support."); } }}

  44. Remarks on the next set of slides • These use a 3rd party connection broker class from javaexchange • Require a dat file to be in tomcat/bin • Require various package hierarchy (which I didn’t use) but see last sequence of slides for more remarks & examples.

  45. Running Servlet2 from Tomcat

  46. What you’ll need to do • Move the broker class and servlet2 class files into your webapp/web-inf/classes directory. (I created a new webapp called database). • The broker needs some other directories/files which came in the zip collection. These are in the org and com directories, specifically HttpServletJXGB imports: import com.javaexchange.dbConnectionBroker.*; import org.gjt.mm.mysql.*; I copied these two directory structures into my database/WEB_INF/classes directory

  47. You need a new web.xml for this webapp. <web-app xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd" version="2.4"> <!-- description of Web application --> <display-name> servlet database connections </display-name> <description> This is the Web application in which we work on database connections </description>

  48. Web.xml continued <!-- Servlet definitions --> <servlet> <servlet-name>Servlet2</servlet-name> <description> A simple servlet opens a mysql connectionn and displays contents of a table </description> <servlet-class> Servlet2 </servlet-class> </servlet> <servlet> <servlet-name>HttpServletJXGB</servlet-name> <description> broker to database </description> <servlet-class> HttpServletJXGB </servlet-class> </servlet> <!-- Servlet mappings --> <servlet-mapping> <servlet-name>HttpServletJXGB</servlet-name> <url-pattern>/HttpServletJXGB</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>Servlet2</servlet-name> <url-pattern>/Servlet2</url-pattern> </servlet-mapping> </web-app>

  49. Generating an html table

  50. Uses HtmlSQLResult class from text import java.sql.*; public class HtmlSQLResult { private String sql; private Connection con; public HtmlSQLResult(String sql, Connection con) { this.sql = sql; this.con = con; } public String toString() { // can be called at most once StringBuffer out = new StringBuffer(); // Uncomment the following line to display the SQL command at start of table // out.append("Results of SQL Statement: " + sql + "<P>\n");

More Related