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
