Okay, so, I have been pulling my hair out of my head trying to fix this one, and I'm almost ready to beat somebody's a** because trying to correct this is an exercise in futility, but here goes...
No matter what I do, the following
servlet won't work because of a faulty
JDBC connection:
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.text.*;
public class ProductLookup extends HttpServlet
{
private static void processResultSet (ServletOutputStream out, ResultSet rs)
{
DecimalFormat formatter = new DecimalFormat ("#0.00");
try
{
System.out.println ("\n\n+++++ Processing ResultSet +++++\n");
while (rs.next() )
{
out.println ("<Product>");
out.println ("<Title>" + rs.getString(1) + "</Title>");
out.println ("<Author>" + rs.getString(2) + "</Author>");
out.println ("<ISBN>" + rs.getString(3) + "</ISBN>");
out.println ("<Cost>" + formatter.format(rs.getDouble(4)) + "</Cost>");
if (rs.getInt (5) > 0)
out.println ("<InStock>Yes</InStock>");
else
out.println ("<InStock>No</InStock>");
out.println ("</Product>");
}
System.out.println ("\n\n+++++ ResultSet Processed +++++\n");
}
catch (IOException e)
{
System.err.println (e.getMessage () ) ;
System.exit (4); // Output error
}
catch (SQLException e)
{
System.err.println (e.getMessage () ) ;
System.exit (5); // Processing error
}
}
public void doGet (HttpServletRequest request, HttpServletResponse response)
{
try
{
JDBCUtility.loadJDBCDriverOracle ();
Connection conn = JDBCUtility.getConnectionOracle ();
// Statement stmt = JDBCUtility.makeStatement (conn);
Statement stmt = JDBCUtility.makeStatement (conn);
response.setContentType ("text/xml");
ServletOutputStream out = response.getOutputStream();
out.println ("<?xml version=\"1.0\" standalone=\"yes\"?>");// xml header
out.println ("<?xml-stylesheet type=\"text/xsl\" href=\"http://localhost:8880/Course5/ProductList.xsl\"?>"); // xsl ss
out.println ("<ProductList>");// root element
// Prepare query based on uploaded user search criteria
String title = request.getParameter ("title");
String author = request.getParameter ("author");
String query = "SELECT Title, Author, ISBN, Cost, QuantityOnHand FROM Products ";
if ( title != null & ! title.equals ("") )
query = query + "WHERE Title LIKE '" + title + "' ";
if (author != null & ! author.equals ("") )
if (query.indexOf ("WHERE") == -1 )
query = query + "WHERE Author LIKE '" + author + "' ";
else
query = query + "AND Author LIKE '" + author + "' ";
ResultSet rs = JDBCUtility.getResultSet(stmt, query);
processResultSet (out, rs);// print results as xml
JDBCUtility.closeThingsDown (rs, stmt, conn);// close db stuff
out.println ("</ProductList>");// close root
}
catch (IOException e)
{
System.err.println (e.getMessage () );
}
}
public void doPost (HttpServletRequest request, HttpServletResponse response)
{
doGet (request, response);
}
}
Here is the JDBC Connection Utility, appropriately named JDBCUtility.java:
import java.sql.*;
import java.io.*;
public class JDBCUtility {
/** load driver for Oracle data base */
public static void loadJDBCDriverOracle () {
System.out.println ("JDBCUtility: Loading Register driver...");
System.out.println(1);
try {
System.out.println(2);
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
System.out.println(2);
//DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
}
catch (SQLException e) {
System.err.println (e.getMessage ());
System.exit (1); // Driver error - exit with a code of 1
}
System.out.println ("JDBCUtility: Register driver loaded...");
}//end loadJDBCDriverOracle() method
/** load driver for Oracle data base */
public static void loadJDBCDriverAccess () {
System.out.println ("JDBCUtility: Loading Access driver...");
try {
//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//loads Access driver
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch (ClassNotFoundException e) {
System.err.println (e.getMessage ());
System.exit (1); // Driver error with error code of 1
}
System.out.println ("JDBCUtility: Access driver loaded...");
}//end loadJDBCDriverAccess() method
/** get connection for Oracle data base */
public static Connection getConnectionOracle () {
System.out.println ("JDBCUtility: getting connection...");
Connection conn = null;
try {
String jdbc_url = "jdbc
racle:thin:@localhost:1521
racle";
conn = DriverManager.getConnection (jdbc_url, "scott", "tiger");
}
catch (SQLException e) {
System.err.println (e.getMessage () ) ;
System.exit (2); // Driver failure
}
System.out.println ("JDBCUtility: connection created ...");
return conn;
}//end getConnectionOracle() method
/** get connection for Access data base. Assumes that you have an ODBC connection to the data base named 'BookNook' */
public static Connection getConnectionAccess () {
System.out.println ("JDBCUtility: getting connection...");
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc
dbc:BookNook");
}
catch (SQLException e) {
System.err.println (e.getMessage () ) ;
System.exit (2); // Driver failure
}
System.out.println ("JDBCUtility: connection created ...");
return conn;
}//end getConnectionAccess() method
/** establishes the statement object for the data base. You must have already made the Connection */
public static Statement makeStatement (Connection conn) {
Statement stmt = null;
try {
System.out.println ("JDBCUtility: Making a Statement...");
stmt = conn.createStatement ();
}
catch (SQLException e) {
System.err.println (e.getMessage () ) ;
System.exit (3); // Driver failure
}
System.out.println ("JDBCUtility: Statement created...");
return stmt;
}//end makeStatement(Connection conn) method
/** will execute a SELECT statement and return a ResultSet object */
public static ResultSet getResultSet (Statement stmt, String query) {
ResultSet rs = null;
try {
System.out.println ("JDBCUtility: Creating resultSet from query " + query + ".");
rs = stmt.executeQuery (query);
}
catch (SQLException e)
{
System.err.println (e.getMessage () ) ;
System.exit (6);// ResultSet error
}
System.out.println ("JDBCUtility: Resultset created...");
return rs;
}//end getResultSet(Statement stmt, String query) method
/**will execute an UPDATE, INSERT or DELETE statement and return an int indicating how many rows have been changed*/
public static int updateTable (Statement stmt, String query) {
int rowsUpdated = 0;
try
{
System.out.println ("JDBCUtility: Preparing to update row with query " + query + ".");
rowsUpdated = stmt.executeUpdate (query);
}//end try block
catch (SQLException e) {
System.err.println (e.getMessage () ) ;
System.exit (6); // ResultSet error
}//end catch block
System.out.println ("JDBCUtility: Update completed... updated " + rowsUpdated + " rows.");
return rowsUpdated;
}//end updateTable(Statement stmt, String query) method
/** this method will close down the resultset, the statement, and the connection */
public static void closeThingsDown (ResultSet rs, Statement stmt, Connection conn) {
try {
System.out.println ("JDBCUtility: Closing Things Down...");
rs.close();
System.out.println ("JDBCUtility: ResultSet Closed...");
stmt.close();
System.out.println ("JDBCUtility: Statement closed...");
conn.close();
System.out.println ("JDBCUtility: Connection closed...");
}
catch (SQLException e) {
System.err.println (e.getMessage () ) ;
System.exit (9); // Closure failure
}
}//end closeThingsDown(ResultSet rs, Statement stmt, Connection conn) method
/** this method will close down the resultset, the statement, and the connection */
public static void closeThingsDown (Statement stmt, Connection conn)
{
try
{
System.out.println ("JDBCUtility: Closing Things Down...");
stmt.close ();
System.out.println ("JDBCUtility: Statement closed...");
conn.close();
System.out.println ("JDBCUtility: Connection closed...");
}
catch (SQLException e)
{
System.err.println (e.getMessage () ) ;
System.exit (9); // Closure failure
}
}//end closeThingsDown(Statement stmt, Connection conn) method
}//end JDBCUtility class
I have the classpath loaded properly, making me wonder if God doesn't want me to become a
Java programmer. I sincerely hope one of you can help me out. Thanks.
Sincerely,
Mr. Thursday Night