Home Jsp How to Retrieve data from database in jsp

Related Tutorials


 
 

Share on Google+Share on Google+

How to Retrieve data from database in jsp

Advertisement
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.

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 = "naulej";
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

Advertisements

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: June 29, 2013

Related Tutorials

Discuss: How to Retrieve data from database in jsp  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:2
siya
February 27, 2014
doubt regarding the program

can u please give the complete guide as to how to run the same program in eclipse environment..thanks in advance !
newbie
April 1, 2014
how i can edit data in this table?

if i add Edit link(a href) after each row. how i can fill data of this row and then edit them? thanks sir !
DMCA.com