Servlets Chapter 9 database connectivity Servlets and db

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

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

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.

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. for. Name() method: Class. for. Name(“sun. jdbc. odbc. Jdbc. Odbc. Driver”); • Once loaded, the driver registers itself with the java. sql. Driver. Manager 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 Driver. Manager. get. Connection(): Connection con= Driver. Manager. get. Connection(“jdbc etc”, ”user”, ”pw”);

MYSQL admin

administration • Some slides show the mysqlcc (control center) but since we already have apache/php it is easier to continue to use PHPMy. Admin. • 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.

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. • My. SQLMy. Admin is a good GUI for managing your db

My. SQLCC

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.

Add user

New user bob

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

Saving table/viewing table fields

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

The phonelookup servlet import java. io. *; import java. sql. *; import javax. servlet. http. *; public class DBPhone. Lookup extends Http. Servlet { public void do. Get(Http. Servlet. Request req, Http. Servlet. Response res) throws Servlet. Exception, IOException { Connection con = null; Statement stmt = null; Result. Set rs = null; res. set. Content. Type("text/html"); Print. Writer out = res. get. Writer(); try { // Load (and therefore register) the Oracle Driver Class. for. Name("org. gjt. mm. mysql. Driver"); // Get a Connection to the database con = Driver. Manager. get. Connection( "jdbc: mysql: //localhost/test", "bob", "justabob"); //or user= “root”, pw=”” // Create a Statement object stmt = con. create. Statement(); // Execute an SQL query, get a Result. Set rs = stmt. execute. Query("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. get. String("name") + " " + rs. get. String("email")); }//actually added more to get all columns out. println("</UL>"); out. println("</BODY></HTML>"); } catch(Class. Not. Found. Exception e) { out. println("Couldn't load database driver: " + e. get. Message()); } catch(SQLException e) { out. println("SQLException caught: " + e. get. Message()); } finally { // Always close the database connection. try { if (con != null) con. close(); } catch (SQLException ignored) { } } }}

Phone lookup (using guestbook table)

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.

Html. SQL result class presents query result as an html table public class Html. SQLResult { private String sql; private Connection con; public Html. SQLResult(String sql, Connection con) { this. sql = sql; this. con = con; } public String to. String() { // can be called at most once String. Buffer out = new String. Buffer(); // 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. create. Statement(); if (stmt. execute(sql)) { // There's a Result. Set to be had Result. Set rs = stmt. get. Result. Set(); out. append("<TABLE>n"); Result. Set. Meta. Data rsmd = rs. get. Meta. Data(); int numcols = rsmd. get. Column. Count();

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. get. Column. Label(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. get. Object(i); if (obj != null) out. append(obj. to. String()); else out. append(" "); } 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. get. Update. Count()); } } catch (SQLException e) { out. append("</TABLE><H 1>ERROR: </H 1> " + e. get. Message()); } return out. to. String(); } }

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

Here are just the parts that differ from previous phonebook example public void init() throws Servlet. Exception { try { // Load (and therefore register) the Oracle Driver Class. for. Name("org. gjt. mm. mysql. Driver"); // Get a Connection to the database con = Driver. Manager. get. Connection( "jdbc: mysql: //localhost/test", "bob", "justabob"); } catch (Class. Not. Found. Exception e) { throw new Unavailable. Exception("Couldn't load database driver"); } catch (SQLException e) { throw new Unavailable. Exception("Couldn't get db connection"); } } public void do. Get(Http. Servlet. Request req, Http. Servlet. Response res) throws Servlet. Exception, IOException { res. set. Content. Type("text/html"); Print. Writer out = res. get. Writer(); out. println("<HTML><HEAD><TITLE>Phonebook</TITLE></HEAD>"); out. println("<BODY>"); Html. SQLResult result = new Html. SQLResult("SELECT NAME, EMAIL, CMT, ID FROM guestlist", con);

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

Phone lookup checks the table to verify guest added

add a guest servlet public class Add. AGuest. Pool extends Http. Servlet { private Connection. Pool pool; public void init() throws Servlet. Exception { try { pool = new Connection. Pool("org. gjt. mm. mysql. Driver", "jdbc: mysql: //localhost/test", "bob", "justabob", 5); connections catch (Exception e) { throw new Unavailable. Exception("Couldn't create connection pool"); } } public void do. Get(Http. Servlet. Request req, Http. Servlet. Response res) throws Servlet. Exception, IOException {do. Post(req, res); } public void do. Post(Http. Servlet. Request req, Http. Servlet. Response res) throws Servlet. Exception, IOException { Connection con = null; res. set. Content. Type("text/plain"); Print. Writer out = res. get. Writer(); try { con = pool. get. Connection(); // Turn on transactions con. set. Auto. Commit(false); Statement stmt = con. create. Statement(); stmt. execute. Update("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. return. Connection(con); } }} }//get

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

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

Guestbook servlet revisited after pressing button (code in notes)

Guestbook servlet: some notes • Init gets a pool of connections: public void init() throws Servlet. Exception { try { Servlet. Context context = get. Servlet. Context(); synchronized (context) { // A pool may already be saved as a context attribute pool = (Connection. Pool) context. get. Attribute("pool"); if (pool == null) { // Construct a pool using our context init parameters // connection. driver, connection. url, user, password, etc pool = new Connection. Pool(new Context. Properties(context), 3); context. set. Attribute("pool", pool); } } } catch (Exception e) { throw new Unavailable. Exception( "Failed to fetch a connection pool from the context: " + e. get. Message()); } }

Guestbook servlet: some notes • do. Get and do. Post are a series of method calls: public void do. Get(Http. Servlet. Request req, Http. Servlet. Response res) throws Servlet. Exception, IOException { res. set. Content. Type("text/html"); Print. Writer out = res. get. Writer(); print. Header(out); print. Form(out); print. Messages(out); print. Footer(out); } // Add a new entry, then dispatch back to do. Get() public void do. Post(Http. Servlet. Request req, Http. Servlet. Response res) throws Servlet. Exception, IOException { handle. Form(req, res); do. Get(req, res); }

Guestbook servlet: some notes • Printing a form: private void print. Form(Print. Writer 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>"); }

Handle. Form is insert record function private void handle. Form(Http. Servlet. Request req, Http. Servlet. Response res) throws Servlet. Exception { String name = req. get. Parameter("name"); String email = req. get. Parameter("email"); String comment = req. get. Parameter("comment"); Connection con = null; Prepared. Statement pstmt = null; try { con = pool. get. Connection(); // Use a prepared statement for automatic string escaping pstmt = con. prepare. Statement(INSERT); long time = System. current. Time. Millis(); pstmt. set. String(1, Long. to. String(time)); pstmt. set. String(2, name); pstmt. set. String(3, email); pstmt. set. String(4, comment); pstmt. execute. Update(); } catch (SQLException e) { throw new Servlet. Exception(e); } finally { try { if (pstmt != null) pstmt. close(); } catch (SQLException ignored) { } pool. return. Connection(con); } // Make note we have a new last modified time last. Modified = System. current. Time. Millis(); }

print. Messages method provides Read functionality private void print. Messages(Print. Writer out) throws Servlet. Exception { String name, email, comment; Connection con = null; Statement stmt = null; Result. Set rs = null; try { con = pool. get. Connection(); stmt = con. create. Statement(); rs = stmt. execute. Query(SELECT_ALL); while (rs. next()) { name = rs. get. String(1); if (rs. was. Null() || name. length() == 0) name = "Unknown user"; email = rs. get. String(2); if (rs. was. Null() || email. length() == 0) name = "Unknown email"; comment = rs. get. String(3); if (rs. was. Null() || 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 Servlet. Exception(e); } finally { try { if (stmt != null) stmt. close(); } catch (SQLException ignored) { } pool. return. Connection(con); } }

do. Get/do. Post • Updates, inserts and delets should call do. Post method • Select (read) should call do. Get

Deleting a record… entire servlet in notes …omitted imports and init which makes connection //Process the HTTP Post request public void do. Post(Http. Servlet. Request request, Http. Servlet. Response response) throws Servlet. Exception, IOException { response. set. Content. Type("text/html"); Print. Writer out = new Print. Writer (response. get. Output. Stream()); Statement stmt=null; String query=""; out. println("<html>"); out. println("<head><title>Servlet</title></head>"); out. println("<body>"); try { stmt = con. create. Statement (); String name = request. get. Parameter("name"); query="DELETE from table 1 where name='" + name+"'"; out. println("Query: "+query+"<BR>"); int count=stmt. execute. Update( query ); out. println("modified records ="+count); } catch (SQLException e 2) { System. out. println("SQLException: "+e 2); } finally{ out. println("</body></html>"); out. close(); } }

Deleting a record…continued //Process the HTTP Get request public void do. Get(Http. Servlet. Request request, Http. Servlet. Response response) throws Servlet. Exception, IOException { Print. Writer out = new Print. Writer (response. get. Output. Stream()); 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(); }}

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

Using session to hold connection information

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 Session. Binder. Listener class (called Connection. Holder) saves a single connection associated with each session. • No changes are needed to Connection. Holder text code

Using session to hold connection information import java. io. *; import java. sql. *; import javax. servlet. http. *; class Connection. Holder implements Http. Session. Binding. Listener { private Connection con = null; public Connection. Holder(Connection con) { // Save the Connection this. con = con; try { con. set. Auto. Commit(false); // transactions can extend between web pages! } catch(SQLException e) { // Perform error handling } } public Connection get. Connection() { return con; // return the cargo } public void value. Bound(Http. Session. Binding. Event event) { // Do nothing when added to a Session } public void value. Unbound(Http. Session. Binding. Event 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 } }}

Using session to hold connection Servlet public class Connection. Per. Client extends Http. Servlet { public void init() throws Servlet. Exception { try { Class. for. Name("org. gjt. mm. mysql. Driver"); //note this is My. SQL not oracle driver } catch (Class. Not. Found. Exception e) { throw new Unavailable. Exception("Couldn't load Oracle. Driver"); } } public void do. Get(Http. Servlet. Request req, Http. Servlet. Response res) throws Servlet. Exception, IOException { res. set. Content. Type("text/plain"); Print. Writer out = res. get. Writer(); Http. Session session = req. get. Session(true); Connection con; // Synchronize: Without this two holders might be created for one client synchronized (session) { // Try getting the connection holder for this client Connection. Holder holder = (Connection. Holder) session. get. Attribute("servletapp. connection"); // Create (and store) a new connection and holder if necessary if (holder == null) { try { holder = new Connection. Holder(Driver. Manager. get. Connection("jdbc: mysql: //localhost/test", "bob", "justabob")); //note…this is my db and my table and my user/pw info session. set. Attribute("servletapp. connection", holder); } catch (SQLException e) { log("Couldn't get db connection", e); } }

Using session to hold connection Servlet // Get the actual connection from the holder con = holder. get. Connection(); } // Now use the connection try { Statement stmt = con. create. Statement(); stmt. execute. Update("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. send. Redirect(res. encode. Redirect. URL( req. get. Context. Path() + “DBPhone. Lookup")); //note redirect change } catch (Exception e) { // Any error is grounds for rollback try { con. rollback(); session. remove. Attribute("servletapp. connection"); } catch (Exception ignored) { } out. println("Order failed. Please contact technical support. "); } }}

Remarks on the next set of slides • These use a 3 rd 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.

Running Servlet 2 from Tomcat

What you’ll need to do • Move the broker class and servlet 2 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 Http. Servlet. JXGB imports: import com. javaexchange. db. Connection. Broker. *; import org. gjt. mm. mysql. *; I copied these two directory structures into my database/WEB_INF/classes directory

You need a new web. xml for this webapp. <web-app xmlns="http: //java. sun. com/xml/ns/j 2 ee" xmlns: xsi="http: //www. w 3. org/2001/XMLSchema-instance" xsi: schema. Location="http: //java. sun. com/xml/ns/j 2 ee/web-app_2_4. xsd" version="2. 4">  <display-name> servlet database connections </display-name> <description> This is the Web application in which we work on database connections </description>

Web. xml continued  <servlet-name>Servlet 2</servlet-name> <description> A simple servlet opens a mysql connectionn and displays contents of a table </description> <servlet-class> Servlet 2 </servlet-class> </servlet> <servlet-name>Http. Servlet. JXGB</servlet-name> <description> broker to database </description> <servlet-class> Http. Servlet. JXGB </servlet-class> </servlet>  <servlet-mapping> <servlet-name>Http. Servlet. JXGB</servlet-name> <url-pattern>/Http. Servlet. JXGB</url-pattern> </servlet-mapping> <servlet-name>Servlet 2</servlet-name> <url-pattern>/Servlet 2</url-pattern> </servlet-mapping> </web-app>

Generating an html table

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

Html. SQLResult from text, slide 2 try { Statement stmt = con. create. Statement(); if (stmt. execute(sql)) { // There's a Result. Set to be had Result. Set rs = stmt. get. Result. Set(); out. append("<TABLE>n"); Result. Set. Meta. Data rsmd = rs. get. Meta. Data(); int numcols = rsmd. get. Column. Count(); // Title the table with the result set's column labels out. append("<TR>"); for (int i = 1; i <= numcols; i++) out. append("<TH>" + rsmd. get. Column. Label(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. get. Object(i); if (obj != null) out. append(obj. to. String()); else out. append(" "); } out. append("</TR>n"); }

Html. SQLResult slide 3 // End the table out. append("</TABLE>n"); } else { // There's a count to be had out. append("<B>Records Affected: </B> " + stmt. get. Update. Count()); } } catch (SQLException e) { out. append("</TABLE><H 1>ERROR: </H 1> " + e. get. Message()); } return out. to. String(); } }

modifying servlet 2 do. Get() public void do. Get(Http. Servlet. Request request, Http. Servlet. Response response) throws Servlet. Exception, IOException { response. set. Content. Type("text/html"); Print. Writer out = new Print. Writer (response. get. Output. Stream()); Connection conn=my. Broker. get. Connection(); Statement stmt=null; String query; out. println("<html>"); out. println("<head><title>Servlet 1</title></head>"); out. println("<body>"); //out. println("Hello World. . . Servlet 2 is running!<BR>"); query="select * from table 1"; hsr=new Html. SQLResult(query, conn); String htmltable=hsr. to. String(); //can call just once my. Broker. free. Connection(conn); // Release connection back to pool out. println(htmltable); out. println(); out. close(); }

Deleting a record

Mike was deleted

do. Get method of Delete. Rec servlet public void do. Get(Http. Servlet. Request request, Http. Servlet. Response response) throws Servlet. Exception, IOException { response. set. Content. Type("text/html"); Print. Writer out = new Print. Writer (response. get. Output. Stream()); Connection conn=my. Broker. get. Connection(); Statement stmt=null; String query=""; out. println("<html>"); out. println("<head><title>Servlet 1</title></head>"); out. println("<body>"); //out. println("Hello World. . . Servlet 2 is running!<BR>"); try { stmt = conn. create. Statement (); query="DELETE from table 1 where age=20"; out. println("Query: "+query+"<BR>"); int count=stmt. execute. Update( query ); out. println("modified records ="+count); }catch (SQLException e 2) { System. out. println("SQLException: "+e 2); }finally { try{if(stmt != null) {stmt. close(); }} catch(SQLException e 1){System. out. println("SQLException: "+e 1); } my. Broker. free. Connection(conn); // Release connection back to pool } out. println("</body></html>"); out. close();

Add a record

Add. Rec uses prepared. Statement class public void do. Get(Http. Servlet. Request request, Http. Servlet. Response response) throws Servlet. Exception, IOException { response. set. Content. Type("text/html"); Print. Writer out = new Print. Writer (response. get. Output. Stream()); Connection conn=my. Broker. get. Connection(); Statement stmt=null; String query=""; out. println("<html>"); out. println("<head><title>Servlet 1</title></head>"); out. println("<body>"); try { Prepared. Statement pstmt=conn. prepare. Statement("insert into table 1 (name, grade, age) values (? , ? )"); pstmt. clear. Parameters(); pstmt. set. String(1, "Godzilla"); //would be form params pstmt. set. Double(2, 1. 0); pstmt. set. Int(3, 123); pstmt. execute. Update( ); } catch (SQLException e 2) { System. out. println("SQLException: "+e 2); } finally { try{if(stmt != null) {stmt. close(); }} catch(SQLException e 1){System. out. println("SQLException: "+e 1); } my. Broker. free. Connection(conn); } out. println("</body></html>"); out. close(); }

Same business, using html form

Servlet output

Display table using servlet

The form…really should use post <FORM Method=GET Action="http: //csci 345. oneonta. edu: 8080/databa se/Add. ARecord. Servlet"> name <input type=text name="name"><p> age <input type=text name="age"><p> grade <input type=text name="grade"><p> <input type =submit> </form>

Changes to addrec String name = request. get. Parameter("name"); String sage = request. get. Parameter("age"); String sgrade = request. get. Parameter("grade"); int age=Integer. parse. Int(sage); double grade=Double. parse. Double(sgrade); out. println("adding"+name+", "+grade+", "+age); try { Prepared. Statement pstmt=conn. prepare. Statement("insert into table 1 (name, grade, age) values (? , ? )"); pstmt. clear. Parameters(); pstmt. set. String(1, name); pstmt. set. Double(2, grade); pstmt. set. Int(3, age); pstmt. execute. Update( );

Servlets and mysql The next set of slides redo the last set but start with java files from javaexchange.

Setting up the connectors • The java connectors are in a subdirectory of mysql • The html/pdf/etc documentation is in the docs subdirectory of this

You shouldn’t have to change classpath settings • • If you are developing servlets and/or JSPs, and your application server is J 2 EE-compliant, you should put the driver's. jar file in the WEB-INF/lib subdirectory of your webapp, as this is the standard location for third party class libraries in J 2 EE web applications. (note – if you don’t already have one, the lib directory is in web-inf at the same level as classes)

New driver class in j-connectors is com. mysql. Driver Class. for. Name("com. mysql. Driver"). new. Instance(); //then try { Connection C = Driver. Manager. get. Connection ("jdbc: mysql: //mysql. yourdomainname/database_com_au? user=user_name&password=password"); [use this code for My. SQL] } catch (SQLException E) { System. out. println("SQLException: " + E. get. Message()); System. out. println("SQLState: " + E. get. SQLState()); System. out. println("Vendor. Error: " + E. get. Error. Code()); }

• The various Mysql. Data. Source classes support the following parameters (through standard "setter" methods): • user • password • server. Name (see the previous section about fail-over hosts) • database. Name • port

The Driver. Manager needs to be told which JDBC drivers it should try to make Connections with • The easiest way to do this is to use Class. for. Name() on the class that implements the java. sql. Driver interface. With My. SQL Connector/J, the name of this class is com. mysql. jdbc. Driver. With this method, you could use an external configuration file to supply the driver class name and driver parameters to use when connecting to a database. • Example 3. 1. Registering the Driver With the Driver. Manager • The following section of Java code shows how you might register My. SQL Connector/J from the main() method of your application.

example import java. sql. Connection; import java. sql. Driver. Manager; import java. sql. SQLException; . . . try { Connection conn = Driver. Manager. get. Connection("jdbc: mysql: //localhost/tes t? user=monty&password=greatsqldb"); // Do something with the Connection. . } catch (SQLException ex) { // handle any errors System. out. println("SQLException: " + ex. get. Message()); System. out. println("SQLState: " + ex. get. SQLState()); System. out. println("Vendor. Error: " + ex. get. Error. Code()); }

Opening a Connection to My. SQL • 3. 1. 2. Opening a Connection to My. SQL • After the driver has been registered with the Driver. Manager, you can obtain a Connection instance that is connected to a particular database by calling Driver. Manager. get. Connection(): • Example 3. 2. Obtaining a Connection From the Driver. Manager • This example shows how you can obtain a Connection instance from the Driver. Manager. There a few different signatures for the get. Connection() method. You should see the API documentation that comes with your JDK for more specific information on how to use them.

The connection import java. sql. Connection; import java. sql. Driver. Manager; import java. sql. SQLException; . . . try { Connection Driver. Manager. get. Connection("jdbc: mysql: //localhost/test? user=bob&password=xyz"); // Do something with the Connection. . } catch (SQLException ex) { // handle any errors System. out. println("SQLException: " + ex. get. Message()); System. out. println("SQLState: " + ex. get. SQLState()); System. out. println("Vendor. Error: " + ex. get. Error. Code()); } • Once a Connection is established, it can be used to create Statements and Prepared. Statements, as well as retrieve metadata about the database. This is explained in the following sections.

A DBBroker class • http: //www. purpletech. com/code/ • Link has source for a db connector class in java to handle servlet to mysql connections.

public class Http. Servlet. JXGB extends Http. Servlet • In notes

Db. Connection. Broker in notes • Is pretty big…like 500 lines

Using dbconnectionbroker in servlets classes com javaexchange Db. Connection. Broker (java) Http. Servlet. JXB oreilly etc

JXGBConfig. dat needs to be in Tomcat bin or have path set carefully db. Driver=org. gjt. mm. mysql. Driver db. Server=jdbc: mysql: //localhost/test db. Login= db. Password= min. Conns=1 max. Conns=20 log. File. String=p: \classes\connections. log max. Conn. Time=2

My addrec servlet

My addrec servlet I extended Http. Servlet, not Http. JXBServlet so I put this in init: public void init(Servlet. Config config) throws Servlet. Exception { super. init(config); if(my. Broker == null) { // Only created by first servlet to call Properties p = new Properties(); try { p. load(new File. Input. Stream("JXGBconfig. dat")); String db. Driver = (String) p. get("db. Driver"); String db. Server = (String) p. get("db. Server"); String db. Login = (String) p. get("db. Login"); String db. Password = (String) p. get("db. Password"); int min. Conns = Integer. parse. Int((String) p. get("min. Conns")); int max. Conns = Integer. parse. Int((String) p. get("max. Conns")); String log. File. String = (String) p. get("log. File. String"); status=status+db. Driver+db. Server+db. Login+db. Password+min. Conns+max. Conns; double max. Conn. Time = (new Double((String)p. get("max. Conn. Time"))). double. Value(); my. Broker = new Db. Connection. Broker(db. Driver, db. Server, db. Login, db. Password, min. Conns, max. Conns, log. File. String, max. Conn. Time); status="connected"; } catch (File. Not. Found. Exception f) {status="file not found"; } catch (IOException e) {status="other io prob"; } }//if broker null }//init

do. Get public void do. Get(Http. Servlet. Request request, Http. Servlet. Response response) throws Servlet. Exception, IOException { response. set. Content. Type("text/html"); Print. Writer out = new Print. Writer (response. get. Output. Stream()); out. println("<html>"); out. println("<head><title>Servlet 1</title></head>"); out. println("<body>"); out. println(status); Connection conn=my. Broker. get. Connection(); Statement stmt=null; String query=""; out. println("Hello World. . . addrec is running!<BR>"); try { Prepared. Statement pstmt=conn. prepare. Statement("insert into table 1 (name, grade, age) values (? , ? )"); pstmt. clear. Parameters(); pstmt. set. String(1, "XXXXXXX"); //////adding this item pstmt. set. Double(2, 1. 0); pstmt. set. Int(3, 123); pstmt. execute. Update( ); }catch (SQLException e 2) { System. out. println("SQLException: "+e 2); }finally { try{if(stmt != null) {stmt. close(); }} catch(SQLException e 1){System. out. println("SQLException: "+e 1); } my. Broker. free. Connection(conn); // Release connection back to pool } out. println("</body></html>"); out. close(); }

In My. SQLcontrol center, you can see record was successfully added

Generating an HTML table from a db table

DBTable servlet public class DBTable extends Http. Servlet{ protected static Db. Connection. Broker my. Broker; private Html. SQLResult hsr; //same code for init() as in Add. Rec servlet //Process the HTTP Get request public void do. Get(Http. Servlet. Request request, Http. Servlet. Response response) throws Servlet. Exception, IOException { response. set. Content. Type("text/html"); Print. Writer out = new Print. Writer (response. get. Output. Stream()); Connection conn=my. Broker. get. Connection(); Statement stmt=null; String query; out. println("<html>"); out. println("<head><title>Servlet 1</title></head>"); out. println("<body>"); //out. println("Hello World. . . Servlet 2 is running!<BR>"); query="select * from table 1"; hsr=new Html. SQLResult(query, conn); String htmltable=hsr. to. String(); //can call just once my. Broker. free. Connection(conn); // Release connection back to pool out. println(htmltable); out. println(); out. close(); } //Process the HTTP Post request public void do. Post(Http. Servlet. Request request, Http. Servlet. Response response) throws Servlet. Exception, IOException { } public String get. Servlet. Info() { return "DBTable"; }

code to generate html table from query resultset import java. sql. *; public class Html. Result. Set { private Result. Set rs; public Html. Result. Set(Result. Set rs) { this. rs = rs; } public String to. String() { // can be called at most once String. Buffer out = new String. Buffer(); // Start a table to display the result set out. append("<TABLE>n"); try { Result. Set. Meta. Data rsmd = rs. get. Meta. Data(); int numcols = rsmd. get. Column. Count(); // Title the table with the result set's column labels out. append("<TR>"); for (int i = 1; i <= numcols; i++) { out. append("<TH>" + rsmd. get. Column. Label(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. get. Object(i); if (obj != null) out. append(obj. to. String()); else out. append(" "); } out. append("</TR>n"); } // End the table out. append("</TABLE>n"); } catch (SQLException e) { out. append("</TABLE><H 1>ERROR: </H 1> " + e. get. Message() + "n"); return out. to. String(); }} }

Servlet communication to accomplish a goal • It makes sense for a single servlet to do the db connection and query. • A front end servlet might collect information to form a query, send the query to this db servlet and display the result. • The session is one place where the information (query string and result set) can be placed. • Servlet include is one mechanism to get two servlets to work together.

An html to collect a query and the results of two servlets collaborating

Include. Servlet • collects query from html form • puts it in the session • performs a dispatcher include to the DBLookup servlet (to keep control of the session) • Looks in the session for the resultset • Displays results as it likes

Include. Servlet public void do. Get(Http. Servlet. Request req, Http. Servlet. Response res) throws Servlet. Exception, IOException { res. set. Content. Type("text/html"); Print. Writer out = res. get. Writer(); String[] queries =req. get. Parameter. Values("query"); String query=queries[0]; System. out. println(query); Http. Session session = req. get. Session(); session. set. Attribute("lookup. data", query); out. print("set attribute in session"); Request. Dispatcher dispatcher = req. get. Request. Dispatcher("/servlet/DBLookup"); out. print("dispatcher include call"); dispatcher. include(req, res); out. print("back from dispatcher"); Result. Set rs =(Result. Set) session. get. Attribute("result. set"); if(rs!=null){ try{ out. print("<TABLE>n"); //code here to print out result set as table appears elsewhere. This do. Get is in slide notes out. print("</TABLE>n"); }//try catch(Exception e){out. print("<H 1>sql ERROR: </H 1> " ); } }//if rs!=null else { out. print("<H 1>rs is null</H 1> " ); } }

DBLookup: get query from session, connect, and execute a statement, place resultset in session public void do. Get( Http. Servlet. Request request, Http. Servlet. Response response) throws Servlet. Exception, IOException { response. set. Content. Type("text/html"); Print. Writer out = response. get. Writer(); try { Connection con = null; Statement stmt = null; Result. Set rs = null; Http. Session session = request. get. Session(); String query=(String)session. get. Attribute("lookup. data"); out. print("in db servlet get attribute in session"+query); Class. for. Name("org. gjt. mm. mysql. Driver"); // Get a Connection to the database con = Driver. Manager. get. Connection( "jdbc: mysql: //localhost/test", "root", ""); //or user= "root", pw=""Class. for. Name("org. gjt. mm. mysql. Driver"); // Get a Connection to the database // JDBC Connection //if (conn == null){ //prepare. Connection(); stmt = con. create. Statement(); rs = stmt. execute. Query(query); out. print("back from query in dbservlet"); session. set. Attribute("result. set", rs); out. print("set rs attribute in session in dbservlet"); }catch(Exception e){out. print("sql error"); } }