using MySQL Join for Servlet
[nobr]When i call for the ResultSet Object for this Query Nothing is displaying.Here is the JOIN statement.
rs = stmt.executeQuery(
"SELECT hotels.hotel_name, hotels.hotel_email, htlusers.user_id"+
"FROM htlusers"+
"JOIN hotels ON hotels.hotel_id = htlusers.user_hotel_id"+
"WHERE htlusers.user_hotel_id = '"+this_hotel+"'");
I am Printing the Result using this.
while(rs.next())
{
out.println(rs.getObject(1).toString()+"<br>");
out.println(rs.getObject(2).toString()+"<br>");
out.println(rs.getObject(3).toString()+"<br>");
}
[/nobr]
[926 byte] By [
Ajaxranda] at [2007-11-27 3:33:16]
# 1
This is not directly a Java-related problem, but likely a SQL-related problem. Print the complete statement and try to execute the query straight on the database using kind of an admin tool. If it works, then copy the plain query in a Statement. If it works, then go step by step further.
Apart from that, you data model (or maybe the query) likely needs a redesign. The JOIN on the htlusers is on the same field as the WHERE on htlusers which proves odd database design (this should work however).
# 6
[nobr]package my.hotel;
import java.io.*;
import java.net.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class hdata_thisHotel extends HttpServlet
{
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
String this_hotel = request.getParameter("hid");
response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.print("<html><body>");
out.print("<code><pre>");
out.println("<a href=\"hdata_insert\">Add New Hotel</a>\t<a href=\"hdata_preview\" target=\"mainFrame\">View Hotel</a><br>");
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String nor=null;
try
{
Class.forName("org.gjt.mm.mysql.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost/my_db?user=root&password=dba");
stmt = con.createStatement();
rs = stmt.executeQuery(
"SELECT hotels.hotel_name, hotels.hotel_email, htlusers.user_id"+
"FROM htlusers"+
"JOIN hotels ON hotels.hotel_id = htlusers.user_hotel_id"+
"WHERE htlusers.user_hotel_id = '"+this_hotel+"'");
while(rs.next())
{
String hNameIn=rs.getObject(2).toString();
String hNameOut=hNameIn.toUpperCase();
out.print(hNameOut+"<br>");
out.print("--<br>");
out.println("ID: "+rs.getObject(1).toString());
out.println("Mail : "+rs.getObject(3).toString());
out.print("<br><br>");
out.print("SYSTEM USERS<a href=\"hdata_adduser\">ADD USER</a><br>");
out.print("--<br>");
out.print("<br><br>");
out.print("OTHER INFO<br>");
out.print("--<br>");
}
}
catch(Exception e)
{
}
out.println("</body>");
out.println("</html>");
out.close();
}
}
[/nobr]
# 7
Oh I think i fixed the problem. It was my bad mistake in the SQl String.
Since this is on several lines i forgot to put spaces on by end of those lines.
rs = stmt.executeQuery(
"SELECT hotels.hotel_name, hotels.hotel_email, htlusers.user_id "+
"FROM htlusers "+
"JOIN hotels ON hotels.hotel_id = htlusers.user_hotel_id "+
"WHERE htlusers.user_hotel_id = '"+this_hotel+"'");
