retreiving data through jsp

hi folks... I want to retreive bus information through a jsp from a table bus that i have created ... the scenery is somewhat like this... the user will input his desired departure city and arrival city form two listboxes...these inputs will be passed on to the jsp... The following code i wrote is not giving me fruitful result, it is not able to retreive the data from the table...

this is the index.html page---->> ( here the user selects his desired departure city and arrival city as two inputs from the listbooxes)

the following is the findbus.jsp code...

<HTML>
    <HEAD>
        <TITLE>Fetching Data From a Database</TITLE>
    </HEAD>

<BODY>
        <H1> Available buses -->> </H1>
<%@ page import="java.sql.*" %>
<% Class.forName("com.mysql.jdbc.Driver"); %>
        <%
            Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/busdb", "root", "pass");

Statement statement = connection.createStatement();

String departure = request.getParameter("departure");
            String arrival = request.getParameter("arrival");

ResultSet resultset =
                statement.executeQuery("select bus_name from bus where departure = '" + departure + "' and arrival = '" + arrival + "'") ;

if(!resultset.next()) {
                out.println("Sorry, no bus available ! ");
            } else {
        %>

</TR>
           <TR>
               <TD> <%= resultset.getString(1) %> </TD>
               
           </TR>
       </TABLE>
       <BR>
       <%
           }
       %>
    </BODY>
</HTML>

Please correct me.... Thank you in advance to all of you... hoping for quick solutions...

oh no no ...this is not the way jsp's are used!
use servlets, jstl, and clean jsp's...
if you do so, who knows, your problem could be solved!

First, check if your sql-java code actually works and you get desired results...
if so...your half way!

olivier dutranoit wrote:oh no no ...this is not the way jsp's are used!
use servlets, jstl, and clean jsp's...
if you do so, who knows, your problem could be solved!

First, check if your sql-java code actually works and you get desired results...
if so...your half way!

Thanks oliver for your kind response.... Can you show me the way how to do this through a servlet ??

Hello!

sorry if my comment looked rude to you...
Actually, you have to work via the mvc-model.
seperate business logic from data and views etc...
Google for it, and you'll find tons of examples.

now, for your problem...
you say ", it is not able to retreive the data from the table... "

what are you getting? an error? blank page? what happens? can you be more specific? is it an error on the query execution or something?

olivier dutranoit wrote:Hello!

sorry if my comment looked rude to you...
Actually, you have to work via the mvc-model.
seperate business logic from data and views etc...
Google for it, and you'll find tons of examples.

now, for your problem...
you say ", it is not able to retreive the data from the table... "

what are you getting? an error? blank page? what happens? can you be more specific? is it an error on the query execution or something?

Actually , in my case, even if i enter an id( that exists in my database), the ' Sorry no busses available' message appears.. what is actually happening ???

Now for the Servlet approach that you suggested.. should my approach be like sending the input parameters from the html page to a servlet where the servlet will use variables to store the inputs with getParameter() ??? and after that should i implement the same thing which i did using only jsp ???

well its pretty clear what happens...
your resultset.next() returns no data...

so...there is a problem with your sql-connection or sql itself...

be sure that your query is ok

can you visualize your query before exection?

the ("select bus_name from bus where departure = '" + departure + "' and arrival = '" + arrival + "'") ;

well... now I have followed the approach of sending the form data to a servlet ( which will retreive the data from the database) and then forward the data to a jsp for the viewing purpose.. I have done the following .. But i am getting errors.. Please help...

===========================================================
This is the form ( index.jsp) through which the user will send his input....

====================================================

This is the servlet code (busServlet.java)...

import java.io.*;

import java.util.*;

import java.sql.*;

import javax.servlet.*;

import javax.servlet.http.*;

public class busServlet extends HttpServlet{

private ServletConfig config;

//Setting JSP page

String page="view.jsp";

public void init(ServletConfig config)

throws ServletException{

this.config=config;

}

public void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException,IOException
{

PrintWriter out = response.getWriter();

//Establish connection to MySQL database

String connectionURL = "jdbc:mysql://localhost:3306/busdb";

Connection connection
= null;

ResultSet rs;

response.setContentType("text/html");

List dataList = new ArrayList();
  String departure = request.getParameter("departure");
  String arrival = request.getParameter("arrival");

try {

// Load the database driver

Class.forName("com.mysql.jdbc.Driver");

// Get a Connection to the database

connection = DriverManager.getConnection(connectionURL, "root", "pass");

//Select the data from the database

String sql = "select bus_name from bus";

Statement s = connection.createStatement();

s.executeQuery (sql);

rs = s.getResultSet();

while (rs.next ()){

//Add records into data list

dataList.add(rs.getString("bus_name"));

}

rs.close ();

s.close ();

}catch(Exception e){

System.out.println("Exception is ;"+e);

}

request.setAttribute("data",dataList);

//Disptching request

RequestDispatcher dispatcher = request.getRequestDispatcher(page);

if (dispatcher != null){

dispatcher.forward(request, response);

}

===========================================================

the following is the jsp page( view.jsp).......

========================================================

and at last this is the web.xml code....

Please tell me where i am wrong and please correct my code .... when i run this application the index page shows up ... when i select the two choices in the listboxes ( departure and arrival ) and click submit... the following error shows up on the browser...

HTTP Status 404 - /bus2/busServlet.java

type Status report

message /bus2/busServlet.java

description The requested resource (/bus2/busServlet.java) is not available.

I'm not even going to get started on how poor a practice and irresponsible it is to be putting Java code into a JSP in 2011, that's apparently a lost cause.

But what is the .java suffix doing on your URL?

Bear Bibeault wrote:I'm not even going to get started on how poor a practice and irresponsible it is to be putting Java code into a JSP in 2011, that's apparently a lost cause.

But what is the .java suffix doing on your URL?

okay... but i want to mention here that i am not an expert .. I am just in my learning phase...

OK, fair enough. For the first point you should know that putting Java into JSPs is an irresponsibly poor practice that has been discredited for almost 10 years now. You should update your JSP knowledge with the JSTL and EL and never, ever, ever, put Java code into a JSP.

Secondly, novices to JSP and web apps should read the following articles:

The Secret Life of JSPs

The Front Man

And lastly, your URL should follow the pattern that you mapped in the deployment descriptor. So I'm not sure why you felt the need to stick .java onto the URL. Just use the URL you mapped.