JSP Get Data From Database

In this section we will discuss about how to get data from database using JSP.

JSP Get Data From Database

JSP Get Data From Database

In this section we will discuss about how to get data from database using JSP.

To get data from database to a JSP page we will first required an installed database system and the corresponding database driver for Java that can intermediate the Java code and database system code. In my example I am using the MySQL database system and the provided driver for this db system is com.mysql.jdbc.Driver.

Example

Here I am giving a simple example which lets you understand to fetch data from database in JSP. In this example I am just showing a list of countries name available in the database table. For this I have created a html page where created a submit button inside the form to show the countries name list. To display the database table's data I have created a JSP page where written the Java code for loading the database driver to fetch the table value. Inside this code create a connection to connect with the database system by providing the url, id, and password. Then write the code for execute the SQL query that selects all the country name and put it into a result set. Then get all the data from the result set.

Directory Structure of this example

Source code

index.html

<!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=ISO-8859-1">
<title>Show country list</title>
</head>
<body>
<form method="get" action="index.jsp">
<p>Click on button to show the country list
<input type="submit" value="ShowCountriesList"/></p>
</form>
</body>
</html>

getData.jsp

<%@ page language="java" contentType="text/html;
charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 
Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ page import="java.sql.*" %>
<html>
<head>
<meta http-equiv="Content-Type" 
content="text/html; charset=ISO-8859-1">
<title>Get Country List</title>
</head>
<body>
<%! String driver = "com.mysql.jdbc.Driver";%>
<%! String url = "jdbc:mysql://localhost/record"; %>
<%! String uid = "root"; %>
<%! String psw = "root"; %>
<%! String sql ="SELECT countryName from country"; %>
<%
Connection con;
PreparedStatement ps;
ResultSet rs;

try
{
Class.forName(driver);
con = DriverManager.getConnection(url,uid,psw);
ps = con.prepareStatement(sql);
rs = ps.executeQuery();


if(!rs.next()) {
out.println("Sorry, No list found");
} 
else
{ 
%>
<table border="1">
<tr>
<th>countryName</th>
</tr>
<tr>
<%
while(rs.next())
{
%>

<td> <%= rs.getString("countryName") %> </td>
</tr> 
<%
}
%>
</table>
<br>
<%
}
}
catch(SQLException sqle)
{
out.println(sqle);
}
%>
</body>
</html>

Download Source Code