JSP & Servlet Database Connectivity with Example
In this Article, We will Create
CRUD(Create,Read,Upadate,Delete) application using JSP,Servlet and Mysql.
For the CRUD Application we need Following Tools :
1. Eclipse
IDE for J2EE Development
2. Mysql
Workbench
3. Mysql-connector.jar
4. jstl.jar
and standar.jar
First, Lets Create Database and Table for Users using SQL
Query,
If you are using Windows OS, open cmd or for linux users,
open terminal and type following command,
mysql -u root -p(password) [NOTE : type password withou
braces]
by typing above command, you will be, inside mysql directory
where you can type SQL scipt queries,
now, create database by following command,
create database UserInfo;
CREATE TABLE UserInfo.`users` (
`userid` int(11) NOT
NULL AUTO_INCREMENT,
`firstname`
varchar(45) DEFAULT NULL,
`lastname`
varchar(45) DEFAULT NULL,
`dob` date DEFAULT
NULL,
`email` varchar(100)
DEFAULT NULL,
PRIMARY KEY
(`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
Now, Before we create to develop project, we need to setup
server.
In Server tab, make right click → New → Choose Apache →
Tomcat v7.0. Now, Apache Tomcat 7.0 is set in the Environment.
Next, Create New Project, Select File → New → Dynamic Web
Project.
Give “JSPServletCRUDApp”
as project Name, and click Finish.
Copy, standard.jar, jstl.jar and mysql-connector.jar to
WEB-INF → lib folder.
Now, in eclipse editor, refresh the lib folder. And, expand
it by clicking on arrow symbol of it.
Select all 3 jar files together, right click on it, and
click on add to build path.
By, this step, your jar files are configured to the
project.
In this Project, We will follow, MVC Architecture. Now, if u
think that, what is MVC Architecture and how we follow it, in JSP-Servlet
Application, then I will give you it's brief introduction.
MVC(Model-View-Controller) Architecture.
In IT Companies, all major and Enterprise level Projects
follow MVC Architecture. It gives lots benefits to programmer. Using it, to
write and understand the code has become easy.
Model – In this part, business logic of application
is written. In our case, we'll write business logic of our application in java
class.
View – GUI of application represents View Part. In
our case, JSP is the View, and request will be sent from it.
Controller – It is core part of architecture. It
requests from the view part, and send it to Model(java class) and by
appropriate result, it gives response to user. In our application Servlet
represents controller.
So, lets start coding …...
Create four packages in src folder,
com.controller – contains Servlet
com.dao – contains logic for database operation
com.model – contains the getter and setter
com.util – contains the database connectivity code
Next, create a new Java class, in com.util folder.
Name it DBUtil.java. This class
handles the database connection to MySql Server.
package com.util;
import java.sql.*;
public class DBUtil {
private static Connection conn=null;
public static Connection getConnection(){
try{
String
username = "root";
String
passwd = "root";
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/UserInfo",username,passwd);
}catch(Exception e){
e.getMessage();
}
return conn;
}
public static void closeConnection(Connection conn){
try{
conn.close();
}catch(Exception e){
e.getMessage();
}
}
}
Note :- In my case, userid and password is root, you
have edit it, accounting to your, system configuration.
Now, create a new Java class. in com.model
folder. Name it “User.java” and insert these
following codes. Each of the variables in this class represents the field in
USERS table in our database. We will make getter and setter of variables.
package model;
import java.util.Date;
public class User {
private int userid;
private String firstName;
private String lastName;
private Date dob;
private String email;
public int getUserid() {
return userid;
}
public void setUserid(int userid) {
this.userid = userid;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public Date getDob() {
return dob;
}
public void setDob(Date dob) {
this.dob = dob;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "User
[userid=" + userid + ", firstName=" + firstName
+ ", lastName=" + lastName + ", dob=" + dob + ", email="
+ email + "]";
}
}
Next, Create new class in com.dao package, name it UserDao.java.
Dao Stands for Data Access Object. It contains, logic for database operations.
package com.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import model.User;
import util.DbUtil;
public class UserDao {
public void addUser(User user) {
Connection conn=null;
try {
conn = DbUtil.getConnection();
PreparedStatement
pStmt = conn
.prepareStatement("insert into users(firstname,lastname,dob,email) values (?,
?, ?, ? )");
pStmt.setString(1,
user.getFirstName());
pStmt.setString(2,
user.getLastName());
pStmt.setDate(3,
new
java.sql.Date(user.getDob().getTime()));
pStmt.setString(4,
user.getEmail());
pStmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
DbUtil.closeConnection(conn);
}
}
public void deleteUser(int userId) {
Connection conn = null;
try {
conn = DbUtil.getConnection();
PreparedStatement
pStmt = conn.prepareStatement("delete from users where
userid=?");
pStmt.setInt(1,
userId);
pStmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
DbUtil.closeConnection(conn);
}
}
public void updateUser(User user) {
Connection conn = null;
try {
conn = DbUtil.getConnection();
PreparedStatement
pStmt = conn.prepareStatement("update users set
firstname=?, lastname=?, dob=?, email=?" +
"where userid=?");
pStmt.setString(1,
user.getFirstName());
pStmt.setString(2,
user.getLastName());
pStmt.setDate(3,
new
java.sql.Date(user.getDob().getTime()));
pStmt.setString(4, user.getEmail());
pStmt.setInt(5,
user.getUserid());
pStmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
DbUtil.closeConnection(conn);
}
}
public List<User> getAllUsers() {
Connection conn = null;
List<User>
users = new ArrayList<User>();
try {
conn = DbUtil.getConnection();
Statement stmt =
conn.createStatement();
ResultSet rSet =
stmt.executeQuery("select * from users");
while (rSet.next()) {
User user = new User();
user.setUserid(rSet.getInt("userid"));
user.setFirstName(rSet.getString("firstname"));
user.setLastName(rSet.getString("lastname"));
user.setDob(rSet.getDate("dob"));
user.setEmail(rSet.getString("email"));
users.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DbUtil.closeConnection(conn);
}
return users;
}
public User getUserById(int userId) {
Connection conn = null;
User user = new User();
try {
conn = DbUtil.getConnection();
PreparedStatement
pStmt = conn.prepareStatement("select * from users where
userid=?");
pStmt.setInt(1,
userId);
ResultSet rSet =
pStmt.executeQuery();
if (rSet.next()) {
user.setUserid(rSet.getInt("userid"));
user.setFirstName(rSet.getString("firstname"));
user.setLastName(rSet.getString("lastname"));
user.setDob(rSet.getDate("dob"));
user.setEmail(rSet.getString("email"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DbUtil.closeConnection(conn);
}
return user;
}
}
In above code, you must have notice that, in each function,
connection is call from Dbutil class, means, it is not necessary to write same
code at every time and we can save time and data redundancy , and other, in
finally we have close connection. It is necessary to and good habit to close
the connection. Otherwise, connection instance cannot allocate to next method,
and run time error will occur.
Finally, create a new Servlet inside the com.controller
package and name it UserController.java
package com.controller;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import
javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import model.User;
import dao.UserDao;
public class UserController extends HttpServlet {
private static final long serialVersionUID = 1L;
private static String INSERT_OR_EDIT = "/user.jsp";
private static String LIST_USER = "/listUser.jsp";
private UserDao dao;
public UserController() {
super();
dao = new UserDao();
}
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String forward="";
String action = request.getParameter("action");
if (action.equalsIgnoreCase("delete")){
int userId = Integer.parseInt(request.getParameter("userId"));
dao.deleteUser(userId);
forward = LIST_USER;
request.setAttribute("users", dao.getAllUsers());
} else if (action.equalsIgnoreCase("edit")){
forward = INSERT_OR_EDIT;
int userId = Integer.parseInt(request.getParameter("userId"));
User user = dao.getUserById(userId);
request.setAttribute("user", user);
} else if (action.equalsIgnoreCase("listUser")){
forward = LIST_USER;
request.setAttribute("users", dao.getAllUsers());
}
else {
forward = INSERT_OR_EDIT;
}
RequestDispatcher view =
request.getRequestDispatcher(forward);
view.forward(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
User user =
new User();
user.setFirstName(request.getParameter("firstName"));
user.setLastName(request.getParameter("lastName"));
try {
Date
dob = new SimpleDateFormat("dd/MM/yyyy").parse(request.getParameter("dob"));
user.setDob(dob);
} catch (ParseException e) {
e.printStackTrace();
}
user.setEmail(request.getParameter("email"));
String
userid = request.getParameter("userid");
if(userid == null || userid.isEmpty())
{
dao.addUser(user);
}
else
{
user.setUserid(Integer.parseInt(userid));
dao.updateUser(user);
}
RequestDispatcher
view = request.getRequestDispatcher(LIST_USER);
request.setAttribute("users", dao.getAllUsers());
view.forward(request,
response);
}
}
Now, it’s time to create
the jsp, the view for our application. Under the WebContent folder, create a
jsp file, name it index.jsp
<%@ page language="java" contentType="text/html;
charset=EUC-KR"
pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC
"-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert
title here</title>
</head>
<body>
<jsp:forward page="/UserController?action=listUser" />
</body>
</html>
This jsp act as the entry point for our application. In this case,
it will redirect the request to our servlet to list all the users from the
database.
Next, create the jsp to list all the users in the WebContent
folder. Name it listUser.jsp
<%@ page language="java"
contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Show All Users</title>
</head>
<body>
<table border=1>
<thead>
<tr>
<th>User Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>DOB</th>
<th>Email</th>
<th colspan=2>Action</th>
</tr>
</thead>
<tbody>
<c:forEach items="${users}" var="user">
<tr>
<td><c:out value="${user.userid}"
/>
</td>
<td><c:out value="${user.firstName}"
/>
</td>
<td><c:out value="${user.lastName}"
/>
</td>
<td><fmt:formatDate pattern="yyyy-MMM-dd"
value="${user.dob}" />
</td>
<td><c:out value="${user.email}"
/>
</td>
<td><a
href="UserController?action=edit&userId=<c:out value="${user.userid}"/>">Update</a>
</td>
<td><a
href="UserController?action=delete&userId=<c:out value="${user.userid}"/>">Delete</a>
</td>
</tr>
</c:forEach>
</tbody>
</table>
<p>
<a href="UserController?action=insert">Add User</a>
</p>
</body>
</html>
In this jsp, we
have used JSTL to connect between the jsp and the servlet.
Next, create a new jsp in WebContent
folder and name it user.jsp
<%@ page language="java" contentType="text/html;
charset=EUC-KR"
pageEncoding="EUC-KR"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<link type="text/css"
href="css/ui-lightness/jquery-ui-1.8.18.custom.css"
rel="stylesheet" />
<script type="text/javascript" src="js/jquery-1.7.1.min.js"></script>
<script type="text/javascript" src="js/jquery-ui-1.8.18.custom.min.js"></script>
<title>Add new user</title>
</head>
<body>
<script>
$(function() {
$('input[name=dob]').datepicker();
});
</script>
<form method="POST" action='UserController'
name="frmAddUser">
User ID : <input type="text" readonly="readonly"
name="userid"
value="<c:out value="${user.userid}" />" /> <br /> First Name : <input
type="text"
name="firstName"
value="<c:out value="${user.firstName}" />" /> <br /> Last Name : <input
type="text"
name="lastName"
value="<c:out value="${user.lastName}" />" /> <br /> DOB : <input
type="text"
name="dob"
value="<fmt:formatDate
pattern="dd/MM/yyyy" value="${user.dob}" />" />(dd/MM/yyyy)
<br /> Email : <input type="text"
name="email"
value="<c:out value="${user.email}" />" /> <br /> <input
type="submit"
value="Submit" />
</form>
</body>
</html>
Last, check the
web.xml file located in WebContent—>WEB-INF folder in your project
structure. Make sure it looks like this
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>SimpleJspServletDB</display-name>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
<servlet>
<description></description>
<display-name>UserController</display-name>
<servlet-name>UserController</servlet-name>
<servlet-class>controller.UserController</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>UserController</servlet-name>
<url-pattern>/UserController</url-pattern>
</servlet-mapping>
</web-app>
In web.xml, there is tag,
“<welcome-file>”, by which, we can define, the first file to execute
while starting the application.
Now, Finally, it is time to configure the
application to the server and run the server.
Configure the application to server, by
following screenshot,
now, open the browser and paste the link,