• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JDBC Connection (or "I Am At My Wits End")

 
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Author
Posts: 93
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
Whats the actual exception? I see a lot of code, but you have not said what the problem is. Are you not getting a database connection?
It looks like you are running this in a Servlet or app container. A lot of times you need to deploy the JDBC driver to a location the Servlet/Application server knows about or you have to pack up the JDBC drivers in your war file.
Many application servers will ignore whats in your CLASSPATH and look in their own ext or lib directories for additional. (i.e. By default JBoss would need the JDBC drivers located in $JBOSS_HOME/server/default/lib in order to see the "JDBC" drivers. Also if you are using the Oracle JDBC drivers, you have to rename the file from classes*.zip to classes*.jar. Many containers/app servers will only load the classes if they are contained in a JAR and not a zip.
Hope that helps.
Thanks,
John
 
Aaron Fenzi
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
John,
My apologies. I'm receiving the following exceptions:
java.lang.NoClassDefFoundError: oracle/jdbc/driver/OracleDriver
at JDBCUtility.loadJDBCDriverOracle(JDBCUtility.java:10)
at ProductLookup.doGet(ProductLookup.java:50)

Aaron
 
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here is what I do to get a connection in a non-distributed servlet application on Tomcat 4.x. This has worked for me using Tomcat on Win2K accessing a SQL Server 2000 DB, and using Tomcat on Linux accessing a MySQL DB... with the exact same code -- I just use a different props file.
1) A reference to the connection is stored in session scope... one session gets one connection. A better alternative is probably to have sessions share from a connection pool at application scope.
2) The connection parameters are kept in a properties file. This includes the driver name, url, user id and password.
3) The actual driver jars are kept in the /WEB-INF/lib directory, and get deployed in the WAR file.
What follows is a method in one of the classes that requires a JDBC Connection object. The "logger" references are to a java.util.logging.Logger object -- I highly recommend that you use at least this to generate logging info, especially in a web application. JDBC_CONNECTION_REFERENCE is just a final String.
Good luck.

[ October 15, 2002: Message edited by: Philip Shanks ]
 
Aaron Fenzi
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
John,
My apologies. I'm receiving the following exceptions:
java.lang.NoClassDefFoundError: oracle/jdbc/driver/OracleDriver
at JDBCUtility.loadJDBCDriverOracle(JDBCUtility.java:10)
at ProductLookup.doGet(ProductLookup.java:50)

Aaron
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"MrThursdayNight",
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp.
We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please edit your profile and select a new name which meets the requirements.
Thanks.
Dave
 
Aaron Fenzi
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
David,
Good point. I'll do so later.
To the others:
The problem lies with Oracle 9i for some reason. The directory structure seems to be a bit different, as does the newest jakarta-tomcat directory. This problem will probably take me a while, so if anyone has any suggestions, let me know.
Thanks, folks.
Aaron
 
Ranch Hand
Posts: 114
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
your tomcat/servlet container is not finding the oracle driver classes.
you have to extract the oracle driver files in the Classpath or in web-inf/classes folder of ur web application
 
Aaron Fenzi
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Prabhat,
I extracted the files in my classpath to no avail, which was frustrating, but I hadn't tried to extract the files in the WEB-INF/classes folder. I'll try that and let you know what happens.
Aaron

Incidentally, for all you Xbox owners, I implore all of you to obtain the "Rocky" game whenever you can. It WILL change your life.
 
Aaron Fenzi
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Prabhat, I tried to do so, to no avail. I may have to give up Java and run a hot dog stand or something.
 
We're being followed by intergalactic spies! Quick! Take this tiny ad!
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic