How to Retrieve data from database in jsp

This section describes you about getting data from database table in JSP.

How to Retrieve data from database in jsp

In this section we will discuss about how to fetch data from database table. We will give a simple example which will demonstrate you about fetching data from database table.

Example

We are going to discus about retrieve data from database in jsp.First of all we have created table in MySQL with some fields (id, user_id, password, name, email). Then we have inserted dummy data into the table using the "INSERT INTO tableName values(val1, val2, val3, val4...., valN)" because, we need to fetch data into a JSP page. Then we have created a JSP file/page and make a database connection at the JSP page then write a SQL query "SELECT * from tableName" for retrieving the data from database table and execute this query using executeQuery(sql) method of Statement interface and store the result into ResultSet. Then we have used the getXXXXX() method of ResultSet interface.  Then we have displayed the fetched data in the JSP page.

Here is the video tutorial of "How to retrieve data from database and Display in JSP Page?":

In this example we will use eclipse for writing the JSP and Tomcat 6.0.29 for deploying the application.

Syntax

<%=resultSet.getString("") %>

First off all We have a create table in MySQL.

CREATE TABLE `record` (                 
          `id` varchar(15) NOT NULL,            
          `user_id` varchar(30) DEFAULT NULL,   
          `password` varchar(50) DEFAULT NULL,  
          `name` varchar(50) DEFAULT NULL,      
          `email` varchar(50) DEFAULT NULL,     
          PRIMARY KEY (`id`)                    
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1  

Example

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>

<%
String id = request.getParameter("userId");
String driverName = "com.mysql.jdbc.Driver";
String connectionUrl = "jdbc:mysql://localhost:3306/";
String dbName = "jsptutorials";
String userId = "root";
String password = "root";

try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
%>
<h2 align="center"><font><strong>Retrieve data from database in jsp</strong></font></h2>
<table align="center" cellpadding="5" cellspacing="5" border="1">
<tr>

</tr>
<tr bgcolor="#A52A2A">
<td><b>id</b></td>
<td><b>user_id</b></td>
<td><b>Password</b></td>
<td><b>Name</b></td>
<td><b>Email</b></td>
</tr>
<%
try{ 
connection = DriverManager.getConnection(connectionUrl+dbName, userId, password);
statement=connection.createStatement();
String sql ="SELECT * FROM record";

resultSet = statement.executeQuery(sql);
while(resultSet.next()){
%>
<tr bgcolor="#DEB887">

<td><%=resultSet.getString("id") %></td>
<td><%=resultSet.getString("user_id") %></td>
<td><%=resultSet.getString("password") %></td>
<td><%=resultSet.getString("name") %></td>
<td><%=resultSet.getString("email") %></td>

</tr>

<% 
}

} catch (Exception e) {
e.printStackTrace();
}
%>
</table>

Output :

When you will deploy this JSP page on Server then the output will be as follows :

Download Source Code