Java Servlet JSP 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 v9.0. Now, Apache Tomcat 9.0 is set in the Environment.
Next, Create New Project, Select File → New → Dynamic Web Project.
Give “MementoTechCRUD” 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.
also you can used MAVEN integration.
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.
Many It company working on MVC because this is easy to maintain.
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 = newSimpleDateFormat("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.
0 Comments