Ok, this is gonna be a bit harsh on the reader, hence the 10 dukes.
I am creating a search page that creates its querys dynamically. first, the jsp connects to the database. The user inputs a string into a jsp form and clicks search. the value is passed to this connection bean. The value is passed to the method getRecipeSelection, which processes the string value entered by the user, and changes it into an sql query through various other methods. I know that this value is correct when it is finally returned, as i have created many printlns to check it. The final println is found just before the bean returns the value.
The problem is, the value doesn reach the jsp itself, as i have place a println there as well.
package dbconnection;
import java.sql.*;
import java.util.*;
public class ConnectionBean{
public Connection con = null;
private static ResultSet rs, rs1, rs2, rs3, rs4, rs5, rs6, rs7, rs8;
private static Vector victor = new Vector();
private static Vector ingredHandler = new Vector();
private static Vector ingredHandler2 = new Vector();
private static Vector firstlayerHandler = new Vector();
private static Vector nextHandler = new Vector();
private static Vector lastHandler = new Vector();
public static final String delims = " .,:!?-`'\"()\t ";
public static String t;
public static boolean isDelim (String str) {return (delims.indexOf(str) > 0);}
public String getSelectedRecipe(String search) { //INPUT ENTERS HERE
String out = new String ("out");
String url = "jdbc:odbc:database";
Connection con;
Statement stmt;
String c = new String("String");
String firstlayer = "select CustomerID, AllergiesLayer1 from customerdata where CustomerID = 1";
String query1a = "select CustomerID, AllergiesLayer2 from customerdata where CustomerID = 1";
String query1 = "select CustomerID, AllergiesLayer3 FROM customerdata WHERE (CustomerID = 1)";
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException es) {
System.err.print("Class Not found");
}
try {
con = DriverManager.getConnection(url, "myLogin", "myPassword");
stmt = con.createStatement();
/////////////////////////////////////////////////////
//get layer 1 contents, and split them
/////////////////////////////////////////////////////
ResultSet rs6 = stmt.executeQuery(firstlayer);
String onelayer = new String();
StringBuffer layer1list = new StringBuffer();
while (rs6.next()) {
layer1list.append(rs6.getString("AllergiesLayer1"));
}
onelayer = removeStopWords(layer1list);
layer1sorting(onelayer);
System.out.println(firstlayerHandler);
String first = new String();
if(firstlayerHandler.size()>0){
for(int n = 0; n<firstlayerHandler.size(); n++)
{
first = (firstlayerHandler.elementAt(n) + "'");
String query1d = "select IngredientType, PrimaryKey from BaseIngred where IngredientType = '" + first +"";
System.out.println(query1d);
///////////////////////////////////////////////////////////////////////////////
ResultSet rs7 = stmt.executeQuery(query1d);
while (rs7.next()) {
nextHandler.add(rs7.getString("PrimaryKey"));
}}
System.out.println(nextHandler);
}
String firstadder = new String();
if(nextHandler.size()>0){
for(int n = 0; n<nextHandler.size(); n++)
{
firstadder = (nextHandler.elementAt(n).toString());
System.out.println(firstadder);
String querye = "select subMenu, menuNumber from subMenu where menuNumber = " + firstadder +"";
System.out.println(querye);
///////////////////////////////////////////////////////////////////////////////
ResultSet rs8 = stmt.executeQuery(querye);
while (rs8.next()) {
ingredHandler.add(rs8.getString("subMenu"));
}
System.out.println(victor);
System.out.println("test2");
}}
/////////////////////////////////////////////////////
//get layer 2 contents and split them
/////////////////////////////////////////////////////
ResultSet rs = stmt.executeQuery(query1a);
String en = new String();
StringBuffer layer2list = new StringBuffer();
while (rs.next()) {
layer2list.append(rs.getString("AllergiesLayer2"));
//System.out.println(ingredHandler.elementAt(0));
}
en = removeStopWords(layer2list);
layer2sorting(en);
System.out.println(ingredHandler);
System.out.println("test");
/////////////////////////////////////////////////////
//added the contents of layer 2 to ingredHandler
///////////////////////////////////////////////////// and submenu =
String limit = new String();
if(ingredHandler.size()>0){
for(int n = 0; n<ingredHandler.size(); n++)
{
limit = (ingredHandler.elementAt(n) + "'");
String query1b = "select subMenu, PrimaryNumber from subMenu where subMenu = '" + limit +"";
System.out.println(query1b);
///////////////////////////////////////////////////////////////////////////////
ResultSet rs2 = stmt.executeQuery(query1b);
while (rs2.next()) {
ingredHandler2.add(rs2.getString("PrimaryNumber"));
}}
System.out.println(ingredHandler2);
}
String limits = new String();
if(ingredHandler2.size()>0){
for(int n = 0; n<ingredHandler2.size(); n++)
{
limits = (ingredHandler2.elementAt(n).toString());
System.out.println(limits);
String query1c = "select Ingredient, MenuNumber from Ingredient where MenuNumber LIKE '" + limits +"'";
///////////////////////////////////////////////////////////////////////////////
ResultSet rs3 = stmt.executeQuery(query1c);
System.out.println("test");
while (rs3.next()) {
victor.add("Ingredients NOT LIKE '%" + rs3.getString("Ingredient") + "%'");
}
System.out.println(victor);
}}
///////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
/////Anything beyond here is third layer code
/////////////////////////////////////////////////////////////////////////////
ResultSet rs4 = stmt.executeQuery(query1);
StringBuffer sb = new StringBuffer();
while(rs4.next()){
sb.append(rs4.getString("AllergiesLayer3"));
}
String sb12 = new String();
sb12 = removeStopWords(sb);
formatting(sb12);
String t = new String();;
t = IngredQuery(t, search);
System.out.println(t + "t has stuff in it"); /*THIS VALUE OF 'T', THE STRING TO BE
RETURNED, IS CORRECT*/
}
catch(SQLException ex) {
System.err.println(ex);
}
return t; //RETURN IS FOUND HERE, BUT THIS DOESNT APPEAR TO RETURN
// stmt.close();
// con.close();
}
public static String formatting (String sb) {
String formattedWord = new String();
StringTokenizer tok = new StringTokenizer(sb,";");
while(tok.hasMoreTokens()){
String word = tok.nextToken();
formattedWord = ("Ingredients NOT LIKE '%" + word +"%'");
//System.out.println(word);
victor.add(formattedWord);
}
return sb;
}
public static String layer2sorting (String sb) {
StringTokenizer tok = new StringTokenizer(sb,";");
while(tok.hasMoreTokens()){
String word = tok.nextToken();
ingredHandler.add(word);
}
return sb;
}
public static String layer1sorting (String sb) {
StringTokenizer tok = new StringTokenizer(sb,";");
while(tok.hasMoreTokens()){
String word = tok.nextToken();
firstlayerHandler.add(word);
}
return sb;
}
public static String removeStopWords(StringBuffer str)
{
String str1 = new String(str);
StringTokenizer tokenizer = new StringTokenizer(str1,delims, true);
StringBuffer sb = new StringBuffer();
while (tokenizer.hasMoreTokens())
{
String token = tokenizer.nextToken();
if ((!isDelim(token)))
sb.append(token);
};
//System.out.println(sb);
return (sb.toString());
}
//public static String IngredLimit (String t) {
// return t;
//}
public static String IngredQuery (String t, String r) {
r = "steak";
StringBuffer newquery = new StringBuffer();
StringBuffer content = new StringBuffer();
newquery.append("select RecipeName, Pic, PicIsAt, Ingredients, Instructions FROM Recipe WHERE RecipeName LIKE '%"+ r + "%'");
for(int i = 0; i<(victor.size())-1; i++)
{content.append(victor.elementAt(i) + " and ");
}
content.append(victor.elementAt(victor.size()-1));
if (victor.size()>0)
{newquery.append(" and ");}
newquery.append(content);
t = newquery.toString();
return t;
}
public boolean Connect()
{
try
{
//String url = "jdbc:odbc:database";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=M:\\dbexample\\database.mdb", "myLogin", "myPassword");
return true;
}catch (Exception ex)
{ System.out.println(ex);
return false;
}
}
public boolean DisConnect()
{
try
{
con.close();
return true;
}catch(Exception ex)
{System.out.println(ex);
return false;
}
}
}
The JSP follows
<jsp:useBean id="connection" scope="session" class="dbconnection.ConnectionBean" class="dbconnection.getSelectedRecipe"/>
<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" errorPage="errorpage.jsp" %>
<HTML>
<HEAD>
<TITLE>JSP JDBC Example 1</TITLE>
</HEAD>
<BODY>
<align="center"> This is a very basic way to access the recipes that are available
To completely benefit from this service you should sign up as a member,
and fill out a dietary information form so that we can match you to recipes
that can completely suit your needs.
<br></br>
Your Search gave the following results<p>
<!-- Set the scripting language to java and -->
<!-- import the java.sql package -->
<%
if(connection.Connect()){
String place = request.getParameter("description");
String t = connection.getSelectedRecipe(place);
System.out.println(t + "for the system");
out.println(t);
//connection.DisConnect();
//
//java.sql.Statement statement = connection.con.createStatement();
//java.sql.ResultSet rs = statement.executeQuery(t);
//while (rs.next())
// {
// out.println("<A HREF = 'recipeframe.jsp?name="+rs.getString("RecipeNo")+"'>"+rs.getString("RecipeName")+"</a><br>");
//
// }
}
else
{
%>
Error! Cannot connect to the Service<%}%>
</BODY>
</HTML>
Can anyone help? this is fairly urgent