This short tutorial will explain about creating a simple database application with JSP involving INSERT, UPDATE, DELETE, and also the SELECT process.
Preparing The Requirements
First, you need Apache Tomcat installed properly on you computer. I have written a tutorial about installing Apache Tomcat before this tutorial. You might want to read it first. Anyway, you also need to install MySQL as database server and the MySQL Connector for Java (available in .jar format) as the supporting library. You can search the MySQL Connector for Java with Google or find it at java.sun.com.
Copy your MySQL Connector JAR file to the lib folder under your Apache Tomcat installation directory. Then, restart your Apache Tomcat. Now, your Apache Tomcat is able to use that library.
Preparing Working Directory
Before you start to write your code, you might want to create a special folder to store all your projects. If you want it, you can create a folder under the webapps folder. In this example, I create a folder named work under the webapps folder to store my JSP files.
Creating The Database
Create a simple database with the following SQL command.
CREATE DATABASE db1; USE db1; CREATE TABLE item ( id char(3) NOT NULL default '', name varchar(20) NOT NULL default '', price int(6) NOT NULL default '0', PRIMARY KEY (`id`) );
Creating The Database Class
For interacting with the database, I will make a special class. But first, you have to create some folders under your working directory with this structure.
WEB-INF/
classes/
database/
And, this is the code for this class. Store it inside the database folder with name dbfunction.java.
package database;
import java.sql.*;
public class dbfunction{
private Connection con;
private Statement stat;
private ResultSet result;
public void createConnection(){
try{
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost/db1","user1","password");
}
catch(Exception ex){ }
}
public void createConnection(String driver, String url){
try{
Class.forName(driver);
con=DriverManager.getConnection(url);
}
catch(Exception ex){ }
}
public void createConnection(String driver, String dataSource, String userName, String password){
try{
Class.forName(driver);
con=DriverManager.getConnection(dataSource,userName,password);
}
catch(Exception ex){ }
}
public void closeConnection(){
try{
con.close();
}
catch(Exception ex){ }
}
public boolean updateRecord(String strSQL){
try{
stat=con.createStatement();
int status=stat.executeUpdate(strSQL);
if(status==1){
return true;
}
else{
return false;
}
}
catch(Exception ex){
return false;
}
}
public ResultSet queryRecord(String strSQL){
try{
stat=con.createStatement();
result=stat.executeQuery(strSQL);
return result;
}
catch(Exception ex){
return null;
}
}
}
Compile it.
The Application
Now, this is the time to create the main application. This application consists of three pages such as index.jsp, insert.jsp, and update.jsp. Save all those files inside your working directory.
index.jsp
This is the front page for displaying the records of the item table. It also has two links in each row for updating and deleting record. And, this is the code of this file.
<%@ page import="java.sql.*" %>
<html>
<head>
<script language="javascript" type="text/javascript">
function confirmDelete(url){
if(confirm("Are you sure want to delete this record?")){
location.href=url;
}
}
</script>
<title>Viewing Table</title>
</head>
<body>
<jsp:useBean id="dbfunc" scope="page" class="database.dbfunction" />
<center>
<%
try{
dbfunc.createConnection();
if(request.getParameter("delete")!=null){
if(dbfunc.updateRecord("DELETE FROM item WHERE id='" + request.getParameter("delete") + "'"))
out.println("<p><b>Deleting record success!</b></p>");
else
out.println("<p><b>Deleting record failed!</b></p>");
}
ResultSet rs=dbfunc.queryRecord("SELECT * FROM item");
out.println("<table border='1'>");
out.println("<tr><th>Item ID</th><th>Item Name</th><th>Unit Price</th><th colspan='2'> </th></tr>");
while(rs.next()){
out.println("<tr><td>" + rs.getString("id") + "</td><td>" + rs.getString("name") + "</td><td>" + rs.getString("price") + "</td><td><a href='update.jsp?id=" + rs.getString("id") + "'>Update</a></td><td><a href=javascript:confirmDelete('index.jsp?delete=" + rs.getString("id") + "')>Delete</a></td></tr>");
}
out.println("</table>");
out.println("<br>");
out.println("<a href='insert.jsp'>INSERT RECORD</a>");
dbfunc.closeConnection();
}
catch(Exception ex){
out.println("<p><b>" + ex.toString() + "</b></p>");
}
%>
</center>
</body>
</html>
insert.jsp
This page is for inserting the record to the item table.
<html>
<head><title>Inserting Record</title></head>
<body>
<center>
<jsp:useBean id="dbfunc" scope="page" class="database.dbfunction" />
<%
if(request.getParameter("insert")!=null){
try{
dbfunc.createConnection();
if(dbfunc.updateRecord("INSERT INTO item VALUES ('" + request.getParameter("itemId") + "', '" + request.getParameter("itemName") + "', '" + request.getParameter("unitPrice") + "')"))
out.println("<p><b>Inserting record success!</b></p>");
else
out.println("<p><b>Inserting record failed!</b></p>");
dbfunc.closeConnection();
}
catch(Exception ex){
out.println("<p><b>" + ex.toString() + "</b></p>");
}
}
%>
<form action="insert.jsp" method="POST">
<table>
<tr><td>Item ID</td><td>:</td><td><input type="text" name="itemId" size="10"></td></tr>
<tr><td>Item Name</td><td>:</td><td><input type="text" name="itemName" size="25"></td></tr>
<tr><td>Unit Price</td><td>:</td><td><input type="text" name="unitPrice" size="15"></td></tr>
</table>
<br>
<input type="submit" name="insert" value="INSERT">
</form>
<br>
<a href="index.jsp">VIEW TABLE</a>
</center>
</body>
</html>
update.jsp
This page is for inserting the record to the item table.
<%@ page import="java.sql.*" %>
<html>
<head><title>Updating Record</title></head>
<body>
<jsp:useBean id="dbfunc" scope="page" class="database.dbfunction" />
<center>
<%
try{
ResultSet rs=null;
dbfunc.createConnection();
if(request.getParameter("update")!=null){
if(dbfunc.updateRecord("UPDATE item SET id='" + request.getParameter("itemId") + "', name='" + request.getParameter("itemName") + "', price='" + request.getParameter("unitPrice") + "' WHERE id='" + request.getParameter("id") + "'"))
out.println("<p><b>Updating record success!</b></p>");
else
out.println("<p><b>Updating record failed!</b></p>");
}
if(request.getParameter("id")!=null)
rs=dbfunc.queryRecord("SELECT * FROM item WHERE id='" + request.getParameter("id") + "'");
if(rs.next()){
%>
<form action="update.jsp?id=<%= rs.getString("id") %>" method="POST">
<table>
<tr><td>Item ID</td><td>:</td><td><input type="text" name="itemId" value="<%= rs.getString("id") %>" size="10"></td></tr>
<tr><td>Item Name</td><td>:</td><td><input type="text" name="itemName" value="<%= rs.getString("name") %>" size="25"></td></tr>
<tr><td>Unit Price</td><td>:</td><td><input type="text" name="unitPrice" value="<%= rs.getString("price") %>" size="15"></td></tr>
</table>
<br>
<input type="submit" name="update" value="UPDATE">
</form>
<%
}
dbfunc.closeConnection();
}
catch(Exception ex){
out.println("<p><b>" + ex.toString() + "</b></p>");
}
%>
<br>
<a href="index.jsp">VIEW TABLE</a>
</center>
</body>
</html>
Screenshots
These are the screenshots when the application was being run.
-
Inserting record.
-
Viewing record.
-
Deleting record.
-
Updating record.
13 comments:
There is a revision in this article. In update.jsp file, I forgot to add <%@ page import="java.sql.*" %> before. But now, I have added it. Thanks to Abdul Rasyit who told me about this via email.
Hi Bogeyman,
thanx a lot for your blog.
It seems that i dont't get a connection to the database. index.jsp shows: java.lang.NullPointerException
insert: Inserting record failed!
inserting directly into db1/item is successfull.
How can i go on?
Vielen Dank ,
Rainer
You have done such a wonderful job keep it up..
Thanks for the code can we have some search function incorporated
Great Tutorial. easy way to handle jsp from database.
thanks for admin
very nice work... its help a lot for a novice like me...
hope you can do it also with servlet and JSPBean
Very Nice ! Thanks a Lot
its nice blog..very helping ..thanks bogeyman..keep writing such nice article on java
Thanks man, I was in pinch to understand all those stuff but your sample helped me =)
Thank you Man, you are the Best!!!
man can you teach me how to view this on the www? or simply as to make this as a website? what are the steps?what should i do?
i really need this,
hey_philip@yahoo.com
thanks in advance!
man u saved my job with this post.....
amazing....keep writing articles like these....
it is very difficult to get exact information we are looking for...
Thanks and Regards,
Ashutosh Jha
you are genius. Thank for your posts. It's really useful.
Post a Comment