In JDBCInitServlet (Example 1 and Example 2) Connection to a DB2 database from the variables we initialize from the servlet configuration f...
In JDBCInitServlet (Example 1 and Example 2) Connection to a DB2 database from the variables we initialize from the servlet configuration file. This example demonstrates how to make a connection to an external resource, and print the results back in the response.
Example 1 - Connecting to a JDBC Database
package itso.servjsp.servletapi;
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class JDBCInitServlet extends SimpleInitServlet {
protected Connection conn = null;
public void init(ServletConfig config) throws ServletException {
super.init(config);
try {
// load JDBC driver
Class.forName(mydriver).newInstance();
conn = DriverManager.getConnection(myurl, myuserID, mypassword);
System.out.println("Connection successful..");
}
catch (SQLException se) { System.out.println(se); }
catch (Exception e) { e.printStackTrace(); }
}
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("TEXT/HTML");
PrintWriter out = res.getWriter();
out.println("<HTML>");
out.println("<TITLE>JDBC Init Connection</TITLE>");
out.println("<BODY>");
try { executeSQL(out); }
catch (SQLException se) { se.printStackTrace(); }
out.println("</BODY></HTML>");
out.close();
}
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class JDBCInitServlet extends SimpleInitServlet {
protected Connection conn = null;
public void init(ServletConfig config) throws ServletException {
super.init(config);
try {
// load JDBC driver
Class.forName(mydriver).newInstance();
conn = DriverManager.getConnection(myurl, myuserID, mypassword);
System.out.println("Connection successful..");
}
catch (SQLException se) { System.out.println(se); }
catch (Exception e) { e.printStackTrace(); }
}
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("TEXT/HTML");
PrintWriter out = res.getWriter();
out.println("<HTML>");
out.println("<TITLE>JDBC Init Connection</TITLE>");
out.println("<BODY>");
try { executeSQL(out); }
catch (SQLException se) { se.printStackTrace(); }
out.println("</BODY></HTML>");
out.close();
}
Example 2 - Sql access
public void executeSQL(PrintWriter out) throws SQLException{
Statement stmt = conn.createStatement();
String sql = "SELECT * FROM DEPARTMENT";
stmt.executeQuery(sql);
ResultSet rs = stmt.getResultSet();
int count = 1;
while (rs.next()) {
out.println("<B>"+rs.getString("DEPTNAME")+"</B><BR><BLOCKQUOTE>");
String sql2 = "SELECT * FROM EMPLOYEE WHERE WORKDEPT = '" +
rs.getString("DEPTNO") + "'";
Statement stmt2 = conn.createStatement();
stmt2.executeQuery(sql2);
ResultSet rs2 = stmt2.getResultSet();
while(rs2.next()) {
out.println(rs2.getString("FIRSTNME") + " " +
rs2.getString("LASTNAME") + "<br>");
}
out.println("</BLOCKQUOTE>");
}
}}
Statement stmt = conn.createStatement();
String sql = "SELECT * FROM DEPARTMENT";
stmt.executeQuery(sql);
ResultSet rs = stmt.getResultSet();
int count = 1;
while (rs.next()) {
out.println("<B>"+rs.getString("DEPTNAME")+"</B><BR><BLOCKQUOTE>");
String sql2 = "SELECT * FROM EMPLOYEE WHERE WORKDEPT = '" +
rs.getString("DEPTNO") + "'";
Statement stmt2 = conn.createStatement();
stmt2.executeQuery(sql2);
ResultSet rs2 = stmt2.getResultSet();
while(rs2.next()) {
out.println(rs2.getString("FIRSTNME") + " " +
rs2.getString("LASTNAME") + "<br>");
}
out.println("</BLOCKQUOTE>");
}
}}
The JDBCInitServlet extends the SimpleInitServlet. This demonstrates that we can consider designing base servlet classes at an application level and then extend them for a specific function. This servlet was built primarily to demonstrate functionality; exception handling has been left out in order to keep the code concise.
We choose to extend the SimpleInitServlet, and override the doGet method to make our processing specific to this example. The executeSQL method performs the actual SQL database calls.
This servlet connects to the SAMPLE database that is installed with DB2. It must first load up the database driver and make the connection. In this case, we choose to make the connection object (Connection conn) a shared instance variable, which we reuse from servlet request to servlet request, but initialize only once.
The connection information (user ID, password, URL, and driver) is specified in the JDBCInitServlet.servlet file, which must be copied to the appropriate directory.
