Servlets Chapter 9

Download Report

Transcript 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
• 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.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”);
MYSQL admin
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.
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
MySQLCC
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.*;
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) { } } }}
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.
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();
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();
}
}
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 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);
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
Phone lookup checks the table to verify guest
added
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);
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','[email protected]','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); } }}
}//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 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());
}
}
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);
}
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>");
}
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();
}
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); } }
doGet/doPost
• Updates, inserts and delets should call
doPost method
• Select (read) should call doGet
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();} }
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();
}}
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>
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 SessionBinderListener class (called
ConnectionHolder) saves a single connection
associated with each session.
• No changes are needed to ConnectionHolder
text code
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
} }}
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);
}
}
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','[email protected]','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.");
} }}
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.
Running Servlet2 from Tomcat
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
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>
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>
Generating an html table
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");
HtmlSQLResult from text, slide 2
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();
// 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");
}
HtmlSQLResult slide 3
// 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();
}
}
modifying servlet 2 doGet()
public void doGet(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = new PrintWriter (response.getOutputStream());
Connection conn=myBroker.getConnection();
Statement stmt=null;
String query;
out.println("<html>");
out.println("<head><title>Servlet1</title></head>");
out.println("<body>");
//out.println("Hello World...Servlet2 is running!<BR>");
query="select * from table1";
hsr=new HtmlSQLResult(query,conn);
String htmltable=hsr.toString();//can call just once
myBroker.freeConnection(conn); // Release connection back to pool
out.println(htmltable);
out.println();
out.close();
}
Deleting a record
Mike was deleted
doGet method of DeleteRec servlet
public void doGet(HttpServletRequest request, HttpServletResponse response) throws
ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = new PrintWriter (response.getOutputStream());
Connection conn=myBroker.getConnection();
Statement stmt=null;
String query="";
out.println("<html>");
out.println("<head><title>Servlet1</title></head>");
out.println("<body>");
//out.println("Hello World...Servlet2 is running!<BR>");
try {
stmt = conn.createStatement ();
query="DELETE from table1 where age=20";
out.println("Query: "+query+"<BR>");
int count=stmt.executeUpdate( query );
out.println("modified records ="+count);
}catch (SQLException e2) {
System.out.println("SQLException: "+e2);
}finally {
try{if(stmt != null) {stmt.close();}} catch(SQLException e1){System.out.println("SQLException:
"+e1);}
myBroker.freeConnection(conn); // Release connection back to pool
}
out.println("</body></html>");
out.close();
Add a record
AddRec uses preparedStatement class
public void doGet(HttpServletRequest request, HttpServletResponse response) throws
ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = new PrintWriter (response.getOutputStream());
Connection conn=myBroker.getConnection();
Statement stmt=null;
String query="";
out.println("<html>");
out.println("<head><title>Servlet1</title></head>");
out.println("<body>");
try {
PreparedStatement pstmt=conn.prepareStatement("insert into table1
(name,grade,age) values (?,?,?)");
pstmt.clearParameters();
pstmt.setString(1,"Godzilla");//would be form params
pstmt.setDouble(2,1.0);
pstmt.setInt(3,123);
pstmt.executeUpdate( );
}
catch (SQLException e2) { System.out.println("SQLException: "+e2);}
finally { try{if(stmt != null) {stmt.close();}}
catch(SQLException e1){System.out.println("SQLException: "+e1);}
myBroker.freeConnection(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://csci345.oneonta.edu:8080/databa
se/AddARecordServlet">
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.getParameter("name");
String sage = request.getParameter("age");
String sgrade = request.getParameter("grade");
int age=Integer.parseInt(sage);
double grade=Double.parseDouble(sgrade);
out.println("adding"+name+","+grade+","+age);
try {
PreparedStatement pstmt=conn.prepareStatement("insert into table1
(name,grade,age) values (?,?,?)");
pstmt.clearParameters();
pstmt.setString(1,name);
pstmt.setDouble(2,grade);
pstmt.setInt(3,age);
pstmt.executeUpdate( );
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
J2EE-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 J2EE 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.forName("com.mysql.Driver").newInstance();
//then
try {
Connection C = DriverManager.getConnection
("jdbc:mysql://mysql.yourdomainname/database_com_au?
user=user_name&password=password"); [use this code for MySQL]
}
catch (SQLException E) {
System.out.println("SQLException: " + E.getMessage());
System.out.println("SQLState: " + E.getSQLState());
System.out.println("VendorError: " + E.getErrorCode());
}
• The various MysqlDataSource classes
support the following parameters (through
standard "setter" methods):
• user
• password
• serverName (see the previous section
about fail-over hosts)
• databaseName
• port
The DriverManager needs to be told which JDBC
drivers it should try to make Connections with
• The easiest way to do this is to use Class.forName() on
the class that implements the java.sql.Driver interface.
With MySQL 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
DriverManager
• The following section of Java code shows how you might
register MySQL Connector/J from the main() method of
your application.
example
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
... try { Connection conn =
DriverManager.getConnection("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.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
Opening a Connection to MySQL
• 3.1.2. Opening a Connection to MySQL
• After the driver has been registered with the
DriverManager, you can obtain a Connection instance
that is connected to a particular database by calling
DriverManager.getConnection():
• Example 3.2. Obtaining a Connection From the
DriverManager
• This example shows how you can obtain a Connection
instance from the DriverManager. There are a few
different signatures for the getConnection() 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.DriverManager;
import java.sql.SQLException;
... try {
Connection
DriverManager.getConnection("jdbc:mysql://localhost/test?user=bob&password=xyz");
// Do something with the Connection .... }
catch (SQLException ex) {
// handle any errors
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode()); }
•
Once a Connection is established, it can be used to create Statements and
PreparedStatements, 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 HttpServletJXGB
extends HttpServlet
• In notes
DbConnectionBroker in notes
• Is pretty big…like 500 lines
Using dbconnectionbroker in servlets
classes
com
javaexchange
DbConnectionBroker
DbConnectionBroker
(java)
HttpServletJXB
oreilly
etc
JXGBConfig.dat needs to be in Tomcat bin
or have path set carefully
dbDriver=org.gjt.mm.mysql.Driver
dbServer=jdbc:mysql://localhost/test
dbLogin=
dbPassword=
minConns=1
maxConns=20
logFileString=p:\\classes\\connections.log
maxConnTime=2
My addrec servlet
My addrec servlet I extended HttpServlet, not
HttpJXBServlet so I put this in init:
public void init(ServletConfig config) throws ServletException {
super.init(config);
if(myBroker == null) { // Only created by first servlet to call
Properties p = new Properties();
try {
p.load(new
FileInputStream("JXGBconfig.dat"));
String dbDriver = (String) p.get("dbDriver");
String dbServer = (String) p.get("dbServer");
String dbLogin = (String) p.get("dbLogin");
String dbPassword = (String) p.get("dbPassword");
int minConns = Integer.parseInt((String) p.get("minConns"));
int maxConns = Integer.parseInt((String) p.get("maxConns"));
String logFileString = (String) p.get("logFileString");
status=status+dbDriver+dbServer+dbLogin+dbPassword+minConns+maxConns;
double maxConnTime =
(new Double((String)p.get("maxConnTime"))).doubleValue();
myBroker = new
DbConnectionBroker(dbDriver,dbServer,dbLogin,dbPassword,
minConns,maxConns,logFileString,maxConnTime);
status="connected";
}
catch (FileNotFoundException f) {status="file not found";}
catch (IOException e) {status="other io prob";}
}//if broker null
}//init
doGet
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
response.setContentType("text/html");
PrintWriter out = new PrintWriter (response.getOutputStream());
out.println("<html>");
out.println("<head><title>Servlet1</title></head>");
out.println("<body>"); out.println(status);
Connection conn=myBroker.getConnection();
Statement stmt=null;
String query="";
out.println("Hello World...addrec is running!<BR>");
try {
PreparedStatement pstmt=conn.prepareStatement("insert into table1 (name,grade,age) values (?,?,?)");
pstmt.clearParameters();
pstmt.setString(1,"XXXXXXX");//////adding this item
pstmt.setDouble(2,1.0);
pstmt.setInt(3,123);
pstmt.executeUpdate( );
}catch (SQLException e2) {
System.out.println("SQLException: "+e2);
}finally {
try{if(stmt != null) {stmt.close();}} catch(SQLException e1){System.out.println("SQLException: "+e1);}
myBroker.freeConnection(conn); // Release connection back to pool
}
out.println("</body></html>");
out.close();
}
In MySQLcontrol center, you can see record was
successfully added
Generating an HTML table from a db table
DBTable servlet
public class DBTable extends HttpServlet{
protected static DbConnectionBroker myBroker;
private HtmlSQLResult hsr;
//same code for init() as in AddRec servlet
//Process the HTTP Get request
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
response.setContentType("text/html");
PrintWriter out = new PrintWriter (response.getOutputStream());
Connection conn=myBroker.getConnection();
Statement stmt=null;
String query;
out.println("<html>");
out.println("<head><title>Servlet1</title></head>");
out.println("<body>");
//out.println("Hello World...Servlet2 is running!<BR>");
query="select * from table1";
hsr=new HtmlSQLResult(query,conn);
String htmltable=hsr.toString();//can call just once
myBroker.freeConnection(conn); // Release connection back to pool
out.println(htmltable);
out.println();
out.close();
}
//Process the HTTP Post request
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException,
IOException {
}
public String getServletInfo() {
return "DBTable";
}
code to generate html table from query resultset
import java.sql.*;
public class HtmlResultSet {
private ResultSet rs;
public HtmlResultSet(ResultSet rs) {
this.rs = rs;
}
public String toString() { // can be called at most once
StringBuffer out = new StringBuffer();
// Start a table to display the result set
out.append("<TABLE>\n");
try {
ResultSetMetaData rsmd = rs.getMetaData();
int numcols = rsmd.getColumnCount();
// 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"); }
catch (SQLException e) {
out.append("</TABLE><H1>ERROR:</H1> " + e.getMessage() + "\n");
return out.toString(); }}
}
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
IncludeServlet
• 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
IncludeServlet
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("text/html");
PrintWriter out = res.getWriter();
String[] queries =req.getParameterValues("query");
String query=queries[0];
System.out.println(query);
HttpSession session = req.getSession();
session.setAttribute("lookup.data", query);
out.print("set attribute in session");
RequestDispatcher dispatcher =
req.getRequestDispatcher("/servlet/DBLookup");
out.print("dispatcher include call");
dispatcher.include(req, res);
out.print("back from dispatcher");
ResultSet rs =(ResultSet) session.getAttribute("result.set");
if(rs!=null){
try{
out.print("<TABLE>\n");
//code here to print out result set as table appears elsewhere. This doGet is in slide notes
out.print("</TABLE>\n");
}//try
catch(Exception e){out.print("<H1>sql ERROR:</H1> " );}
}//if rs!=null
else {
out.print("<H1>rs is null</H1> " );
}
}
DBLookup: get query from session, connect, and
execute a statement, place resultset in session
public void doGet( HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
try {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
HttpSession session = request.getSession();
String query=(String)session.getAttribute("lookup.data");
out.print("in db servlet get attribute in session"+query);
Class.forName("org.gjt.mm.mysql.Driver");
// Get a Connection to the database
con = DriverManager.getConnection( "jdbc:mysql://localhost/test", "root", "");//or user= "root",
pw=""Class.forName("org.gjt.mm.mysql.Driver");
// Get a Connection to the database
// JDBC Connection
//if (conn == null){
//prepareConnection();
stmt = con.createStatement();
rs = stmt.executeQuery(query);
out.print("back from query in dbservlet");
session.setAttribute("result.set",rs);
out.print("set rs attribute in session in dbservlet");
}catch(Exception e){out.print("sql error");}
}