Connecting to MySQL database
and retrieving and displaying data in JSP page
This tutorial shows you how to connect to MySQL database and retrieve the
data from the database. In this example we will use tomcat version 4.0.3 to run
our web application.
Creating Table in the database.
Using a JDBC driver
org.gjt.mm.mysql.Driver Driver to connect to the database.
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
Connecting to your database
To connect to the database getConnection() function of
the DriverManager class is used.
con=DriverManager.getConnection(url);
Executing Query and Processing result set
In this example we will use a table called jakartaproject
having five fields in table. We will use the
following query to retrieve all the records from the table:
select * from jakartaproject
Here is the code of our JSP file
<%@ page language="java"
import="java.sql.*"%>
<html>
<head><title>Read from mySQL Database</title>
</head>
<body>
<p align="center"><b>Following records are selected from the 'jakartaproject' table.</b><br> </p>
<div align="center" width="85%">
<center>
<table border="1" borderColor="#ffe9bf" cellPadding="0" cellSpacing="0" width="658" height="63">
<tbody>
<td bgColor="#008080" width="47" align="center" height="19"><font color="#ffffff"><b>Sr.
No.</b></font></td>
<td bgColor="#008080" width="107" height="19"><font color="#ffffff"><b>Project</b></font></td>
<td bgColor="#008080" width="224" height="19"><font color="#ffffff"><b>Url
Address</b></font></td>
<td bgColor="#008080" width="270" height="19"><font color="#ffffff"><b>Description
of the project</b></font></td>
<%
String DRIVER = "org.gjt.mm.mysql.Driver";
Class.forName(DRIVER).newInstance();
Connection con=null;
ResultSet rst=null;
Statement stmt=null;
try{
String url="jdbc:mysql://192.168.10.2/tutorial?user=tutorial&password=tutorial";
int i=1;
con=DriverManager.getConnection(url);
stmt=con.createStatement();
rst=stmt.executeQuery("select * from jakartaproject ");
while(rst.next()){
if (i==(i/2)*2){
%>
<tr>
<td bgColor="#ffff98" vAlign="top" width="47" align="center" height="19"><%=i%>.</td>
<td bgColor="#ffff98" vAlign="top" width="107" height="19"><%=rst.getString(2)%></td>
<td bgColor="#ffff98" vAlign="top" width="224" height="19"><a
href="<%=rst.getString(3)%>"><%=rst.getString(3)%></a> </td>
<td bgColor="#ffff98" vAlign="top" width="270" height="19"><%=rst.getString(4)%></td>
</tr>
<%
}else{
%>
<tr>
<td bgColor="#ffcc68" vAlign="top" width="47" align="center" height="19"><%=i%>.</td>
<td bgColor="#ffcc68" vAlign="top" width="107" height="19"><%=rst.getString(2)%></td>
<td bgColor="#ffcc68" vAlign="top" width="224" height="19"><a
href="<%=rst.getString(3)%>"><%=rst.getString(3)%></a> </td>
<td bgColor="#ffcc68" vAlign="top" width="270" height="19"><%=rst.getString(4)%></td>
</tr>
<% }
i++;
}
rst.close();
stmt.close();
con.close();
}catch(Exception e){
System.out.println(e.getMessage());
}
%>
</tbody>
</table>
</center>
</div>
</body>
</html> |
Deploying web application
Put the code in tomcat and test the application through
browser. The browser should display the display the data stored in the table.
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Current Comments
6 comments so far (post your own) View All Comments Latest 10 Comments:give me answer
Posted by d.penchalaiah on Thursday, 07.26.07 @ 18:37pm | #22003
I got many things in your website.it is very use.
Posted by a.avul dhavuthu on Tuesday, 07.10.07 @ 18:48pm | #21032
Hello guys,
I am new to struts, I am able to insert the values into the database, but unable to retrieve those values into the jsp page.
I am getting the values in an ArrayList and using iterator trying to diplay in JSP but no o/p is coming . the values are present in database
Posted by vardhaman on Friday, 06.29.07 @ 13:12pm | #20369
How can I write the above connection program in a struts action and retrieve the result in a JSP page please give the code for struts and JSP
Posted by Abhisek Das on Thursday, 05.31.07 @ 10:44am | #17824
1. org.gjt.mm.mysql.Driver: Can't seem to find it :
2. new to the stated items. Seeking links to good tutorials and reference materials, as well as someone's hindsight and experience in working with the aforementioned.
Thanks
Posted by anon on Sunday, 05.13.07 @ 06:37am | #15765
i have a problem on retrieving data from a table ,but i want to display the detials of one particular person,when he will login.
im using dbbean for database connection.And jsp pages for login.
thank you
Posted by kriss on Friday, 04.27.07 @ 13:44pm | #15006