Status: Verified
GOAL:
How to code a servlet that will run against Allaire JRun which uses JNDI and DataSource.
FIX:
The servlet uses DataSource object instead of DriverManager to get connections so it can implement connection pooling. NOTE: it calls JNDI API context.lookup to lookup a service. It also uses a finally statement to close a pooled connection.
import javax.naming.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import javax.sql.*;
public class demoHTTPServlet extends HttpServlet {
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException
{
PrintWriter output;
Date today = new Date(System.currentTimeMillis());
response.setContentType("text/html");
output = response.getWriter();
// Create and send HTML page to the client
StringBuffer buf = new StringBuffer();
String username= "administrator";
String password= "";
InitialContext context = null;
Connection con = null;
try {
// Get the JNDI context
context = new InitialContext();
// Lookup the datasource
DataSource ds = (DataSource)context.lookup("java:comp/env/jdbc/test");
// Get the connection
con = ds.getConnection();
// Create the prepared statement
PreparedStatement pst =
con.prepareStatement("select * from balances order by balance",
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = pst.executeQuery();
buf.append("<HTML><HEAD><TITLE>\n");
buf.append("Sales by state report\n");
buf.append("</TITLE></HEAD><BODY>\n");
buf.append("<h1><i>Sales Order Report by State for " + today.toString() + "</i></h1>\n");
buf.append("<table border=3 bordercolor=\"#0066FF\">\n");
buf.append("<tr>\n");
buf.append("<td width=\"209\" height=\"32\">State</td>");
buf.append("<td width=\"209\" height=\"32\">Number of Orders</td>");
buf.append("</tr>\n");
while (rs.next()) {
String s = rs.getString(1);
float b = rs.getFloat(2);
buf.append("<tr>\n");
buf.append("<td>\n");
buf.append(s);
buf.append("</td><td>");
buf.append(b);
buf.append("</td>\n");
buf.append("</tr>\n");
}
rs.close();
con.close();
context.close();
buf.append("</table>\n");
buf.append("</BODY></HTML>\n");
output.println(buf.toString());
}
catch (SQLException ex) {
buf.append("<HTML><HEAD><TITLE>\n");
buf.append("Sales by state report\n");
buf.append("</TITLE></HEAD><BODY>\n");
buf.append("<font face=\"Arial\" size=\"5\" color=\"#C00000\"><b><i>" +
"We're sorry, but the request could not be processed.<br>" +
"The error message is:</i></b></font><hr><h2>");
buf.append("SQLState: " + ex.getSQLState() + "\n");
buf.append("Message: " + ex.getMessage() + "\n");
buf.appe.nd("Vendor: " + ex.getErrorCode() + "\n</h2><hr>");
buf.append("<font face=\"Arial\" size=\"5\" color=\"#0000C0\"><b><i>" +
"Please try again later and let us know if the problem " +
"persists.</i></b></font>");
buf.append("</BODY></HTML>\n");
output.println(buf.toString());
}
catch (Exception ex) {
throw new ServletException(ex.getMessage(), ex);
}
finally {
if (con != null) {
// Always close the connection
try {
con.close();
}
catch (SQLException ex) {
}
}
if (context != null) {
try {
context.close();
}
catch (NamingException ex) {
}
}
}
}
}.