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.
![Server and Version]()
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>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Bus Information System</title>
</head>
<body>
<form action="findbus.jsp">
<table>
<tr>
<td>From:</td>
<td><input type="text" name="csname"></td>
<td>To: </td>
<td><input type="text" name="cdname"></td>
</tr>
<tr>
<td><input type="submit" value="Check Bus"></td>
</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.
![Output]()
Step 8
Now we enter an incorrect entry. Since we have only a single entry we check for other cities like:
![Provide wrong detail]()
![Invalid data]()
Step 9
Now we check for our city path that we have stored in the database as in the following:
![Delhi to Agra]()
![Delhi-Agra bus info]()