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.
