Fetch Records From Database Using JSP in Java

Introduction

This article explains how to fetch data from a database using JSP in Java. The NetBeans IDE is used to create this application.

Fetch Records from Database

This article explains how to fetch records from a database using a JSP in Java. For this application we need the following tools:

Oracle10g Database
Tomcat Server
NetBeans IDE

We need to create the following files:

businfo table
index.jsp file
findbus.jsp file

1. businfo table

For fetching records we need to have a table with multiple records. In this application I use a "businfo" table syntax for creating this table as in the following:

create table businfo
(
b_no number(20), b_route varchar2(30),
b_src varchar2(30), b_dest varchar2(30)
b_arrtime varchar2(20), b_desttime varchar2(20),
b_stop varchar2(30)
);

To insert data the syntax is:

insert into userlogin values ('', '', '', '');

For example I inserted a single row using:

insert into businfo values (12345, 'Delhi-Agra', 'Delhi', 'Agra', '9:00 A:M', '14:00 P:M', 'Mathura');

For creating manually

To manually create a table go to "Registration Form".

Creating other files

We need to use the following procedure to create the other files.

Step 1

Open the NetBeans IDE.

NetBeans IDE

Step 2

Choose "Java web" -> "Web application" as in the following:

Java Web Application

Step 3

Type your project name as "BusInformationSystem" and click on "Next" as in the following:

BusInformationSystem

Step 4

Select Java version and server wizard as in the following.

Step 5

Now change the code of our default "index.jsp" file with the following code.

index.jsp

This page gets the user choice of the source city and destination city they want to travel.

<%@page contentType="text/html" pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<title>Bus Information System</title>

</head>

<body>

<table>

<tr>

</tr>

<tr>

</tr>

</table>

</form>

</body>

</html>

Step 6

Now create another JSP file named "findbus" and write the following code there.

findbus.jsp

Based on their source city name we fetch the records from the database table named "businfo" and display it to the user.

<%@page import="java.sql.*" %>

String cname = request.getParameter("csname");

String cdname = request.getParameter("cdname");

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@mcndesktop07", "bus", "bus");

PreparedStatement st = con.prepareStatement("select * from businfo where b_src=?");

st.setString(1, cname);

ResultSet rs = st.executeQuery();

ResultSetMetaData rsmd = rs.getMetaData();

if (rs.next()) {

<jsp:include page="index.jsp"/>

out.print("<center><h1>Result:</h1></center>");

out.print("<b>Source:</b> " + cname + "");

out.print("<br>" + "<b>Destination:</b> " + cdname);

out.print("<table width=25% border=1>");

out.print("<tr>");

out.print("<td>" + rsmd.getColumnName(1) + "</td>");

out.print("<td>" + rs.getInt(1) + "</td></tr>");

out.print("<tr><td>" + rsmd.getColumnName(2) + "</td>");

out.print("<td>" + rs.getString(2) + "</td></tr>");

out.print("<tr><td>" + rsmd.getColumnName(3) + "</td>");

out.print("<td>" + rs.getString(3) + "</td></tr>");

out.print("<tr><td>" + rsmd.getColumnName(4) + "</td>");

out.print("<td>" + rs.getString(4) + "</td></tr>");

out.print("<tr><td>" + rsmd.getColumnName(5) + "</td>");

out.print("<td>" + rs.getString(5) + "</td></tr>");

out.print("<tr><td>" + rsmd.getColumnName(6) + "</td>");

out.print("<td>" + rs.getString(6) + "</td></tr>");

out.print("<tr><td>" + rsmd.getColumnName(7) + "</td>");

out.print("<td>" + rs.getString(7) + "</td></tr>");

out.println("</table>");

} else
{

out.println("Bus Not Available for this route....");

<jsp:include page="index.jsp"/>

<%}

} catch (Exception ex) {

System.out.println(ex);

}

Step 7

Now our application is ready to run.

Right-click on the "index.jsp" file and choose "run". The following output will be generated.

Step 8

Now we enter an incorrect entry. Since we have only a single entry we check for other cities like:

Step 9

Now we check for our city path that we have stored in the database as in the following: