Catching jsp/tomcat/java SQL error
I have been trying to get the sample code to work from an old book. I was finally able to get it to run under tomcat 4.1.31 but there is one bug I just cannot figure out how to handle. I am a beginner to Java/JSP/Tomcat and have about 16+ hours invested in this problem so far.
The delFruit.jsp calls methods from FruitConnect.java.
I found that if the fruit chosen for deletion was not involved in a referential integrity link to a FruitMonth file the code works fine. But if it is linked the delFruit.jsp just displays a blank screen and nothing is deleted. It is fine for it not to be deleted but I cannot find anyway to handle it. I tried to add the checkFruitForSale bandaid below but it causes me a blank screen as well.
Any help would be appreciated.
It would be great if I could just see what is happening when FruitConnect is processing code. I get no errors, just a blank screen. I'm going to try using NetBeans and their debugger next.
Here is delFruit:
<%@ page import="java.sql.*" %>
<jsp:include page="adminHeader.html" flush="true" />
<jsp:useBean id="fruitConnect" class="fruit1.FruitConnect" />
<%
String msg ="The following fruits have been deleted:<br> ";
//Build the list of fruits to delete
String delFruit[] = request.getParameterValues("delFruit");
//WE WILL ONLY GET CHECKBOXES THAT WERE CHECKED
String fruitList ="";
if (delFruit !=null){
int len = delFruit.length;
fruitConnect.dbConnect();
for (int i = 0; i < len; i++){
String passfruit = delFruit[i];
if (fruitConnect.checkFruitForSale(passfruit)){}
else{
fruitList = fruitList +"'" + delFruit[i] +"',";
msg = msg + delFruit[i] +"<br>";
out.print(msg);
}
out.print(msg);
}
fruitList = fruitList +"'nonsense'";
if (fruitConnect.deleteFruit(fruitList) < 1){
msg ="There was an error deleting the fruits from the database.";
}
else{
msg ="Deletion was successful";
}
fruitConnect.dbDisconnect();
}
else{
msg ="You must select some fruit to delete.";
}
%>
HERE IS FruitConnect.java
package fruit1;
import java.sql.*;
publicclass FruitConnect{
private Driver drv =null;
private Connection conn =null;
private ResultSet rs =null;
private Statement stmt =null;
public FruitConnect(){}
publicvoid dbConnect()throws Exception{
//drv = (Driver) Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
//conn = DriverManager.getConnection("jdbc:odbc:Fruit");
/////////////////////////////
try{
drv = (Driver) Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
conn = DriverManager.getConnection("jdbc:odbc:Fruit");
}
catch ( SQLException sqlException)
{
sqlException.printStackTrace();
}
finally
{
try{
}
catch (Exception exception)
{
}
}
///////////////////////////////
}
publicboolean getNextItem()throws Exception{
boolean ret = rs.next();
return(ret);
}
public String getItemNameString(String columnName)throws Exception{
String name = rs.getString(columnName);
return(name);
}
publicfloat getItemNameFloat(String columnName)throws Exception{
float name = rs.getFloat(columnName);
return(name);
}
publicboolean selectFruits()throws Exception{
String query ="SELECT Id, Name FROM Fruit";
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
boolean ret =false;
if (rs !=null)
ret =true;
return(ret);
}
publicboolean selectMonths()throws Exception{
String query ="SELECT Id, Name FROM Month";
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
boolean ret =false;
if (rs !=null)
ret =true;
return(ret);
}
publicboolean selectFruitForSale()throws Exception{
String query ="SELECT F.Name AS Fruit, M.Name AS Month, Price, Weight, (Price * Weight) AS TotalPrice FROM FruitMonth AS FM, Fruit AS F, Month AS M WHERE FM.FruitId = F.Id AND FM.MonthId = M.Id ORDER BY M.Id";
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
boolean ret =false;
if (rs !=null)
ret =true;
return(ret);
}
publicint insertFruit(String name)throws Exception{
String query ="INSERT INTO Fruit (Name) VALUES ('" + name +"')";
stmt = conn.createStatement();
int ret = stmt.executeUpdate(query);
return(ret);
}
publicint insertFruitForSale(String insertValues)throws Exception{
String query ="INSERT INTO FruitMonth (FruitId, MonthId, Price, Weight) VALUES (" + insertValues +")";
stmt = conn.createStatement();
int ret = stmt.executeUpdate(query);
return(ret);
}
publicint deleteFruit(String fruitList)throws Exception{
String query ="DELETE FROM Fruit WHERE Name IN (" + fruitList +")";
stmt = conn.createStatement();
int ret = stmt.executeUpdate(query);
return(ret);
}
publicvoid dbDisconnect()throws Exception{
stmt.close();
conn.close();
}
publicboolean checkFruitForSale(String thisfruit)throws Exception{
String query ="SELECT F.Name AS Fruit, M.Name AS Month, Price, Weight, (Price * Weight) AS TotalPrice FROM FruitMonth AS FM, Fruit AS F, Month AS M WHERE FM.FruitId = F.Id AND FM.MonthId = M.Id AND F.Name IN (" + thisfruit +") ORDER BY M.Id";
try{
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
}
catch ( SQLException sqlException)
{
sqlException.printStackTrace();
boolean ret =false;
if (rs !=null)
ret =true;
return(ret);
}
finally
{
try{
}
catch (Exception exception)
{
}
}
boolean ret =false;
if (rs !=null)
ret =true;
return(ret);
}
public String Check1(String columnName)throws Exception{
String name = rs.getString(columnName);
return(name);
}
}
