Solved: Servlet SQL query | Experts Exchange

Can you post the select statement you have tried, and define the column (or columns) that relates the Item table to the brand table?

Jim

SQL_SERVER_DBA

lets see your html

Kuldeepchaturvedi

how that HTML page is being built? a static page will not work for you.. the page either needs to be build up using a jsp / from the servlet..

Mimi1979

ASKER

This is the index page:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252"></meta>
<title>TecnoPlus - Inventaire</title>
<link rel="stylesheet" type="text/css" href="style1.css"></link>
</head>
<body><table cellspacing="0" border="0" width="100%">
<tr>
<th>
<p>Bienvenue dans TechnoPlus</p>
<h1>Dans ce site, vous trouverez tous les produits disponible selon ce
que vous cherchez. Notre inventaire est à votre
disposition, vous n'avez qu'à cliquer sur le lien</h1>
</th>
</tr>
</table><p>
 
</p><form name="form1" action="servlet_affichercategories"
method="POST">
<input type="submit" name="afficherCat"
value="Afficher toutes categories"/>
</form></body>
</html>

Servlet_AfficherCategories ---:

package servlets;

import java.io.IOException;
import java.io.PrintWriter;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.*;
import javax.servlet.http.*;

public class Servlet_AfficherCategories extends HttpServlet {
private static final String CONTENT_TYPE =
"text/html; charset=windows-1252";

private Connection connexion = null;
private String URL = "jdbc:odbc:inventaire";

public void init(ServletConfig config) throws ServletException {
super.init(config);

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
connexion = DriverManager.getConnection(URL, "", "mp");
} catch (Exception e) {
e.printStackTrace();
connexion = null;
}
}

public void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException,
IOException
{

PrintWriter sortie = response.getWriter();
response.setContentType("text/html");
Statement requete = null;
ResultSet resultat = null;
try {
requete = connexion.createStatement();
resultat =
requete.executeQuery("Select no_categorie, nom_categorie from Categories");
} catch (Exception e) {
System.err.println("ERREUR: problème à la lecture de la selection");
e.printStackTrace();

}

if (resultat != null) {
// envoie la page HTML au client avec la table categories
sortie.println("<HTML><HEAD><TITLE>");
sortie.println("Toutes les categories de la BD Inventaire");
sortie.println("</TITLE>");
sortie.println("<link rel=stylesheet type=\"text/css\" href=\"style1.css\"></HEAD><BODY>");
sortie.println("<FORM name=\"form1\" action=\"servlet_afficherpieces\" method=\"post\">");

try{ while (resultat.next()){
String numCat = resultat.getString(1);
String nomCat = resultat.getString(2);
sortie.println("<H2>");
sortie.println("<input type=\"radio\" name=\"cate\" value=\"" + numCat + "\">" + nomCat +"<BR>");}}
catch (SQLException ex){ ex.printStackTrace();}
sortie.println(" <input type=\"submit\" name=\"soumission\" value=\"Soumettre\">" + "<BR>");
sortie.println("</H2>");
sortie.println("</form>");
} else
sortie.print("<H2>Une erreur s'est produite. " +
"Veuillez réessayer plus tard.</H2>");

sortie.println("</BODY></HTML>");
sortie.close(); // clôturer le flux.
}

public void destroy() {
try {
connexion.close();
} catch (Exception e) {
System.err.println("Problème à la fermeture de" +
" la base de données");
}
}
}

Servlet_AfficherPieces:

package servlets;

import java.io.IOException;
import java.io.PrintWriter;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.*;
import javax.servlet.http.*;

public class Servlet_AfficherPieces extends HttpServlet {
private static final String CONTENT_TYPE = "text/html; charset=windows-1252";

private Connection connexion = null;
private String URL = "jdbc:odbc:inventaire";

public void init(ServletConfig config) throws ServletException {
super.init(config);

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
connexion = DriverManager.getConnection(URL, "", "mp");
} catch (Exception e) {
e.printStackTrace();
connexion = null;
}
}

public void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
PrintWriter sortie;
String numCatChoisie = request.getParameter("cate");

response.setContentType( "text/html" );
sortie = response.getWriter();

// envoie la page HTML au client avec la categorie chosie
sortie.println( "<HTML><HEAD><TITLE>" );
sortie.println("Table de la categorie choisie");
sortie.println("</TITLE>");
sortie.println("<link rel=stylesheet type=\"text/css\" href=\"style1.css\"></HEAD><BODY>");
sortie.println("<H1>Categorie choisie : " + numCatChoisie + "</H1>");
sortie.println("<table border='1'><tr>");

//Creation de l<objet pour la requete
Statement requete = null;
ResultSet resultat = null;
try {
requete = connexion.createStatement();
resultat = requete.executeQuery("SELECT description FROM Pieces WHERE no_categorie='numCatChoisie'");
while ( resultat.next() ){
sortie.println("<td>" + resultat.getString(1) + "</td>");
sortie.println("</tr>");
}
resultat.close();
}
catch (SQLException e) {
System.err.println("ERREUR: problème à la lecture de la selection");
e.printStackTrace();}

sortie.println("</table></BODY></HTML>");
sortie.close(); // clôturer le flux.
}

public void destroy() {
try {
connexion.close();
} catch (Exception e) {
System.err.println("Problème à la fermeture de" +
" la base de données");
}
}
}

In Servlet_AfficherPieces, I want to display what the user selection depending what categories the user is selecting
I want to display the category#, description, brand name and the price of a categoryof the item the user has selected, example; `Hard drive`.
Thank you in advance

Mimi1979

ASKER

Does anyone has an idea??? or any suggestion?
Thank you

Kuldeepchaturvedi

>> this line in your last servlet

resultat = requete.executeQuery("SELECT description FROM Pieces WHERE no_categorie='numCatChoisie'");

needs to be

resultat = requete.executeQuery("SELECT description FROM Pieces WHERE no_categorie='"+numCatChoisie+"'");

remember you want to pass the value of the selected parameter not the name of the variable..!!!

Mimi1979

ASKER

I tried resultat = requete.executeQuery("SELECT description FROM Pieces WHERE no_categorie='"numCatChoisie"'"); and did not give any rows, but I didn`t try with + sign
I will try that
Thank you

Mimi1979

ASKER

I have now another error message --- when I run Servlet_AfficherCategories
Resource /HTMLDyn_servlets-labo2-context-root/Servlet_AfficherCategories not found on this server
I am not sure what this means.

Mimi1979

ASKER

When I click on Àfficher toutes categories`from index, which calls Servlet_AfficherCategories, it gives 'Resource /HTMLDyn_servlets-labo2-context-root/Servlet_AfficherCategories not found on this server'

But when I run directly Servlet_AfficherCategories it gives me : '405 Method Not Allowed
HTTP method GET is not supported by this URL' but I changed it to a doPost and still coming up
Do you have any ideas?
Thank you

Kuldeepchaturvedi

public void doG(HtettpServletRequest request,
HttpServletResponse response) throws ServletException, IOException
{
doPost(request,response);
}

adding this in servlet will take care of your 405 issue..

& error Resource /HTMLDyn_servlets-labo2-context-root/Servlet_AfficherCategories not found means your servlet mappings are not correct..

either fix your servlet mappings or fix

sortie.println("<FORM name=\"form1\" action=\"servlet_afficherpieces\" method=\"post\">");

to
sortie.println("<FORM name=\"form1\" action=\"/servlet/servlet_afficherpieces\" method=\"post\">");

in your first servlet

Mimi1979

ASKER

So I changed the sortie.println("<FORM name=\"form1\" action=\"servlet_afficherpieces\" method=\"post\">");

to
sortie.println("<FORM name=\"form1\" action=\"/servlet/servlet_afficherpieces\" method=\"post\">");
but I am getting
'404 Not Found
Resource /HTMLDyn_servlets-labo2-context-root/afficherCategories not found on this server'.
I tried to change it in the web.xml file as well but I am not sure that the url-patern is exactly

Mimi1979

ASKER

This is my web.xml ---

<?xml version = '1.0' encoding = 'windows-1252'?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee">
<description>Empty web.xml file for Web Application</description>
<servlet>
<servlet-name>afficherCategories</servlet-name>
<servlet-class>labo2.afficherCategories</servlet-class>
</servlet>
<servlet>
<servlet-name>afficherPiece</servlet-name>
<servlet-class>labo2.afficherPiece</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>afficherCategories</servlet-name>
<url-pattern>/affichercategories</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>afficherPiece</servlet-name>
<url-pattern>/afficherpiece</url-pattern>
</servlet-mapping>
<session-config>
<session-timeout>35</session-timeout>
</session-config>
<mime-mapping>
<extension>html</extension>
<mime-type>text/html</mime-type>
</mime-mapping>
<mime-mapping>
<extension>txt</extension>
<mime-type>text/plain</mime-type>
</mime-mapping>
</web-app>

Mimi1979

ASKER

I changed the package name to labo2 now on each servlet.
any ideas?
thanks
Mimi

Kuldeepchaturvedi

URL pattern is what system uses to find a particular servlet. according to your web.xml

<servlet-mapping>
<servlet-name>afficherPiece</servlet-name>
<url-pattern>/afficherpiece</url-pattern>
</servlet-mapping>

afficherpiece will get invoked when your URL is looking something like.

http://localhost:8080/youapp/affieherpiece..

which means your HTML form should be submitting to

sortie.println("<FORM name=\"form1\" action=\"/afficherpieces\" method=\"post\">");

Also try accessing your servlet directly from browser to see that you can access it.

Mimi1979

ASKER

When I access it from the Web I get same error message
HTTP Status 404 - /HTMLDyn_servlets-labo2-context-root/afficherCategories

type Status report

message /HTMLDyn_servlets-labo2-context-root/afficherCategories

description The requested resource (/HTMLDyn_servlets-labo2-context-root/afficherCategories) is not available.
Apache Tomcat/6.0.13
I don't see where my error is. The web.xml and the html form seem correct.

Kuldeepchaturvedi

your servlet name is

public class Servlet_AfficherCategories extends HttpServlet {

but the name in your web.xml is different..
<servlet>
<servlet-name>afficherCategories</servlet-name>
<servlet-class>labo2.afficherCategories</servlet-class>
</servlet>

make sure that the class name matches... also Java is case sensitive..
so
servlet_afficherpieces and Servlet_afficherpieces are two different names.. make sure your names are matching

Mimi1979

ASKER

I changed the name of the class to afficherCategories

when I run index.html I get he page with the form, as soon I click on the submit button then I get
404 Not Found
Resource /HTMLDyn_servlets-labo2-context-root/afficherCategories not found on this server

But when I run directly afficherCategories I get
'Une erreur s'est produite. Veuillez réessayer plus tard.'
wich is normal since I call directly the servlet and resultat is null. So I think the problem might be something else in the web.xml even all seems fine, it`s not recognizing the servlet. I dont know where else to look....

Kuldeepchaturvedi

where is your index page sitting?
everything else is looking okay to me..

can you post your directory structure and also the URL that you are using to access your servlet directly, as well as URL to access your index page...

I think somewhere your relative path is getting messed up.

Mimi1979

ASKER

ok, I found what the problem is. I am using JDeveloper and the application name has to be the same as the project name and Jdev will take automatically that name for the package.
I renamed my application labo2_servlets, now I am able to run the servlet afficherCategories but I get:
'Une erreur s'est produite. Veuillez réessayer plus tard.' wich is the statement if resultat=null

***************************************

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252"></meta>
<title> TecnoPlus - Inventaire</title>
<link rel=stylesheet type="text/css" href="style1.css">
</head>
<body ><table cellspacing="0" border="0" width="100%">
<tr>
<th>
<p> Bienvenue dans TechnoPlus </p>
<h1>
Dans ce site, vous trouverez tous les produits disponible selon ce
que vous cherchez.
Notre inventaire est à votre disposition, vous n'avez
qu'à cliquer sur le lien
</h1>
</th>
</tr>
</table><p>
<form name="form1" action="affichercategories" method="POST">
<input type="submit" name="afficherCat"
value="Afficher toutes categories">
</form>
</p></body>
</html>

***************************************

package labo2_servlets;

import java.io.IOException;
import java.io.PrintWriter;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.*;
import javax.servlet.http.*;

public class afficherCategories extends HttpServlet {
private static final String CONTENT_TYPE =
"text/html; charset=windows-1252";

private Connection connexion = null;
private String URL = "jdbc:odbc:inventaire";

public void init(ServletConfig config) throws ServletException {
super.init(config);

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
connexion = DriverManager.getConnection(URL, "", "mp");
} catch (Exception e) {
e.printStackTrace();
connexion = null;
}
}

public void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException,
IOException {
doPost(request, response);
}

public void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException,
IOException {

PrintWriter sortie = response.getWriter();
response.setContentType("text/html");
Statement requete = null;
ResultSet resultat = null;
try {
requete = connexion.createStatement();
resultat = requete.executeQuery("select no_categorie, nom_categorie from Categories");
} catch (Exception e) {
System.err.println("ERREUR: problème à la lecture de la selection");
e.printStackTrace();

}

sortie.println("<HTML><HEAD><TITLE>");
sortie.println("Toutes les categories de la BD Inventaire");
sortie.println("</TITLE>");
sortie.println("<link rel=stylesheet type=\"text/css\" href=\"style1.css\"></HEAD><BODY>");
if (resultat != null) {
// envoie la page HTML au client avec la table categories
//sortie.println("<FORM name=\"form1\" action=\"afficherpiece\" method=\"POST\">");
// sortie.println("<FORM name=\"form1\" action=\"*/afficherPieces\" method=\"post\">");

try {
while (resultat.next()) {
String numCat = resultat.getString(1);
String nomCat = resultat.getString(2);
sortie.println("<H2>");
sortie.println("<input type=\"radio\" name=\"cate\" value=\"" +
numCat + "\">" + nomCat + "<BR>");
}
} catch (SQLException ex) {
ex.printStackTrace();
}
//sortie.println(" <input type=\"submit\" name=\"soumission\" value=\"Soumettre\">" +
// "<BR>");
sortie.println("</H2>");
sortie.println("</form>");
} else
sortie.print("<H2>Une erreur s'est produite. " +
"Veuillez réessayer plus tard.</H2>");

sortie.println("</BODY></HTML>");
sortie.close(); // clôturer le flux.
}

public void destroy() {
try {
connexion.close();
} catch (Exception e) {
System.err.println("Problème à la fermeture de" +
" la base de données");
}
}
}
*********************************************

any ideas?

Mimi1979

ASKER

My DB was not loaded in ODBC, now I am getting all the 'categories' from Categories table in the HTML page when the first servlet is ran, now the second servlet I am still getting
'404 Not Found
Resource /labo2_servlets-labo2_servlets-context-root/afficherPieces not found on this server'

Here`s my structure
I have created my application as follow
labo2_servlets is the folder application
in ../labo2_servlets/public_html my index is sitting
in labo2_servlets/public_html/WEB-INF my web.xml is located
in labo2_servlets/src/labo2_servlets my servlets files are sitting (afficherCategories.java and afficherPieces.java)

and this is where JDev creates the classes files once the servlets have been ran (afficherCategories.class and afficherPieces.class)
.../labo2_servlets/classes/labo2_servlets
I am not sure how to run the servlet directly by the URL if I have Apache TomCat server ;-(
Jdev opens the browser directly by running the servlet

Kuldeepchaturvedi

on the browser when your first servlet is running ( showing all the category).. do a right click & look at the properties...

it should have the url that browser is using to access your servlet...
similarly on the other servlet where you are getting the 404 error, do the same and see the URL on that page as well...

they shd be looking similar. ( other than the servlet name)....

Mimi1979

ASKER

First servlet, the referring URL is http://192.168.1.100:8988/labo2_servlets-labo2_servlets-context-root/index.html and the address http://192.168.1.100:8988/labo2_servlets-labo2_servlets-context-root/affichercategories

The second servlet when I get the error message, the referring URL is http://192.168.1.100:8988/labo2_servlets-labo2_servlets-context-root/affichercategories and the address is
http://192.168.1.100:8988/labo2_servlets-labo2_servlets-context-root/afficherPieces
Is this correct?

Mimi1979

ASKER

ok now the two servlet are working, but not my query
I am not sure if the query is correct if I want to display items from two differents tables
This is the servlet executing the query depending what the user has selected
package labo2_servlets;

import java.io.IOException;
import java.io.PrintWriter;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.*;
import javax.servlet.http.*;

public class afficherPieces extends HttpServlet {
private static final String CONTENT_TYPE = "text/html; charset=windows-1252";

private Connection connexion = null;
private String URL = "jdbc:odbc:inventaire";

public void init(ServletConfig config) throws ServletException {
super.init(config);

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
connexion = DriverManager.getConnection(URL, "", "mp");
} catch (Exception e) {
e.printStackTrace();
connexion = null;
}
}

public void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException
{
doPost(request,response);
}

public void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
PrintWriter sortie;
String numCatChoisie = request.getParameter("cate");

response.setContentType( "text/html" );
sortie = response.getWriter();

// envoie la page HTML au client avec la categorie chosie
sortie.println( "<HTML><HEAD><TITLE>" );
sortie.println("Table de la categorie choisie");
sortie.println("</TITLE>");
sortie.println("<link rel=stylesheet type=\"text/css\" href=\"style1.css\"></HEAD><BODY>");
sortie.println("<H1>Categorie choisie : " + numCatChoisie + "</H1>");

//Creation de l<objet pour la requete
Statement requete = null;
ResultSet resultat = null;
try {
sortie.println("Dans le try");
requete = connexion.createStatement();
resultat =
requete.executeQuery("SELECT Pieces.no_categorie, Pieces.description, " +
"Fabricant.nom_fabricant, Pieces.prix_vente WHERE Pieces.no_fabricant=Fabricant.no_fabricant" +
" AND Pieces.no_categorie='" + numCatChoisie + "'");
} catch (Exception e) {
System.err.println("ERREUR: problème à la lecture de la selection");
e.printStackTrace();

}

if (resultat != null) {
sortie.println("Dans resultat");
sortie.println("<H2>");
sortie.println("<TABLE><TR><TH>" + "Numero de categorie" + "</TH>");
sortie.println("<TD>" + "Description" + "</TD>");
sortie.println("<TD>" + "Nom fabricant" + "</TD>");
sortie.println("<TD>" + "Prix vente" + "</TD></TR>");
try{
while (resultat.next()){
String numCat = resultat.getString(1);
String des = resultat.getString(2);
String nomFab = resultat.getString(3);
String prix = resultat.getString(4);
sortie.println("<TR><TH>" + numCat +"</TH>");
sortie.println("<TD>" + des + "</TD>");
sortie.println("<TD>" + nomFab + "</TD>");
sortie.println("<TD>" + prix + "</TD></TR>");
}
}
catch (SQLException ex){ ex.printStackTrace();}
sortie.println("</TABLE></H2>");
} else
sortie.print("<H2>Une erreur s'est produite. " +
"Veuillez réessayer plus tard.</H2>");

sortie.println("</BODY></HTML>");
sortie.close(); // clôturer le flux.
}

public void destroy() {
try {
connexion.close();
} catch (Exception e) {
System.err.println("Problème à la fermeture de" +
" la base de données");
}
}
}

Is my query correct?

Mimi1979

ASKER

the primary key in table Pieces is 'no_piece' and the primary key in table Fabricant is 'no_fabricant'
What I want to display is the no_categorie, nom_fabricant, prix from table Pieces and the description from table Fabricant depending what the selection is for no_categorie
Any ideas?

Mimi1979

ASKER

I have changed the query to this
requete.executeQuery("SELECT Pieces.no_categorie, Pieces.description," +
" Fabricant.nom_fabricant, Pieces.prix_vente FROM Pieces, Fabricant" +
" WHERE Pieces.no_fabricant=Fabricant.no_fabricant" +
" AND Fabricant.nom_fabricant=" +
"(SELECT nom FROM Fabricant" +
" WHERE no_fabricant=(SELECT no_fabricant FROM Pieces WHERE no_categorie='" +
numCatChoisie + "'" + "))" +
" AND Pieces.no_categorie='" + numCatChoisie + "'");
but still getting resultset = null, help me here please...
Thanks

Kuldeepchaturvedi

do one thing..

String query = "SELECT Pieces.no_categorie, Pieces.description," +
" Fabricant.nom_fabricant, Pieces.prix_vente FROM Pieces, Fabricant" +
" WHERE Pieces.no_fabricant=Fabricant.no_fabricant" +
" AND Fabricant.nom_fabricant=" +
"(SELECT nom FROM Fabricant" +
" WHERE no_fabricant=(SELECT no_fabricant FROM Pieces WHERE no_categorie='" +
numCatChoisie + "'" + "))" +
" AND Pieces.no_categorie='" + numCatChoisie + "'";

System.out.println(query);

requete.executeQuery(query);

this should print the query that you are trying to execute.
you can use the query on the database and see what results it provides.

Mimi1979

ASKER

I dont have a database. Is it beeter maybe that I create more than one query because the error I am getting is that the subquery returns more than one row...

Mimi1979

ASKER

We got it, thank you very much

This is what I did and worked for me

try {
//Creating the connexion
stat = connexion.createStatement();

String requete1 = "SELECT Pieces.no_categorie, Pieces.description, Fabricants.nom_fabricant, " +
"Pieces.prix_vente FROM Pieces, Fabricants " +
"WHERE Pieces.no_fabricant=Fabricants.no_fabricant " +
"AND Fabricants.nom_fabricant IN " +
"(SELECT nom_fabricant FROM Fabricants " +
"WHERE no_fabricant IN (SELECT no_fabricant FROM Pieces WHERE no_categorie='" + numCatChoisie+ "')) " +
"AND Pieces.no_categorie='" + numCatChoisie+ "'";
resultat = stat.executeQuery(requete1);

Thank you

Kuldeepchaturvedi

:-) glad to be of help.