File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes JDBC Connection (or Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Connection (or "I Am At My Wits End")" Watch "JDBC Connection (or "I Am At My Wits End")" New topic
Author

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

Aaron Fenzi
Greenhorn

Joined: Oct 15, 2002
Posts: 21
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


Java-ridden fury with an attitude.<p>If you don't get caught, it's not cheating.
John Carnell
Author
Ranch Hand

Joined: Sep 27, 2002
Posts: 71
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


John Carnell<br />Principal Architect<br /> <br />Netchange, LLC<br />1161 HillCrest Heights<br />Green Bay, WI 54313<br /> <br />john.carnell@netchange.us<br /> <br /> <br />Author of <a href="http://www.amazon.com/exec/obidos/ASIN/159059228X/ref=jranch-20" target="_blank" rel="nofollow">Pro Jakarta Struts, Second Edition</a>
Aaron Fenzi
Greenhorn

Joined: Oct 15, 2002
Posts: 21
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
Philip Shanks
Ranch Hand

Joined: Oct 15, 2002
Posts: 189
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 ]

Philip Shanks, SCJP - Castro Valley, CA
My boss never outsources or has lay-offs, and He's always hiring. I work for Jesus! Prepare your resume!
Aaron Fenzi
Greenhorn

Joined: Oct 15, 2002
Posts: 21
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
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

"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

Joined: Oct 15, 2002
Posts: 21
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
prabhat kumar
Ranch Hand

Joined: Apr 11, 2001
Posts: 114
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

Joined: Oct 15, 2002
Posts: 21
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

Joined: Oct 15, 2002
Posts: 21
Prabhat, I tried to do so, to no avail. I may have to give up Java and run a hot dog stand or something.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC Connection (or "I Am At My Wits End")