A Simple CRUD Tutorial Using Java Servlet/JSP and MySQL

crudcover

In this tutorial, we will create a simple CRUD operations for a student using Servlet/JSP, JDBC and MySQL.

I assume you have already basic knowledge of Java.

For this tutorial, we need these following tools: (newer versions will do)

  1. Eclipse IDE for Java EE Developers, I use luna version
  2. MySQL Community Server
  3. Apache Tomcat 8.0
  4. MySQL Connector Jar file, you can get this jar from your MySQL directory: C:\Program Files\MySQL\Connector.J 5.1.
  5. servlet-api.jar, you can get this jar from tomcat directory under lib folder. We need this jar to use the servlet library.
  6. jstl.jar and standard.jar, you can get these jars in your tomcat directory \apache-tomcat-8.0.20\webapps\examples\WEB-INF\lib.

Here is the project directory in case you miss something along the way:

CRUDProject-projectExplorer

After you have done installing the necessary tools, create a new dynamic web project in your eclipse IDE. Make sure you check “generate web.xml” in the creation of the project.

Then copy the jars to the /WEB-INF/lib (see the project directory above).

Create four packages under src folder:

  • com.junald.controller, contains the servlets
  • com.junald.dao, contains the classes or interfaces for the database logic operations.
  • com.junald.model, contains the Plain Old Java Object (POJO).
  • com.junald.util, contains the utility classes in our application. In this project, however, we only have one utility class and it is used for connecting to the database.

We will create first our database table using this script:


CREATE TABLE `student` (
 `studentId` int(5) NOT NULL AUTO_INCREMENT,
 `firstName` varchar(25) DEFAULT NULL,
 `lastName` varchar(25) DEFAULT NULL,
 `course` varchar(15) DEFAULT NULL,
 `year` int(2) DEFAULT NULL,
 PRIMARY KEY (`studentId`));

Create a new class and name it “DBUtil” and put it under com.junald.util package. Then insert these following codes.


package com.junald.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtil {

	private static Connection conn;

	public static Connection getConnection() {
		if( conn != null )
			return conn;

		InputStream inputStream = DBUtil.class.getClassLoader().getResourceAsStream( "/db.properties" );
		Properties properties = new Properties();
		try {
			properties.load( inputStream );
			String driver = properties.getProperty( "driver" );
			String url = properties.getProperty( "url" );
			String user = properties.getProperty( "user" );
			String password = properties.getProperty( "password" );
			Class.forName( driver );
			conn = DriverManager.getConnection( url, user, password );
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return conn;
	}

	public static void closeConnection( Connection toBeClosed ) {
		if( toBeClosed == null )
			return;
		try {
			toBeClosed.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

The class above is in charge of connecting and disconnecting to the database. Also it will retrieve database settings from a properties file.

Now create properties file, name it “db.properties” and put it under src folder. Then insert these codes.


driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/UserDB
user=mcjunald
password=hulaanmo

The settings above will vary depending what driver you use, database name, database username and password. Just make necessary changes.

Create a new class and name it “Student” and put it under com.junald.model package. Then insert these following codes.

package com.junald.model;

public class Student {

	private int studentId;
	private String firstName;
	private String lastName;
	private String course;
	private int year;

	public int getStudentId() {
		return studentId;
	}
	public void setStudentId(int studentId) {
		this.studentId = studentId;
	}
	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 String getCourse() {
		return course;
	}
	public void setCourse(String course) {
		this.course = course;
	}
	public int getYear() {
		return year;
	}
	public void setYear(int year) {
		this.year = year;
	}
	@Override
	public String toString() {
		return "Student [studentId=" + studentId + ", firstName=" + firstName
				+ ", lastName=" + lastName + ", course=" + course + ", year="
				+ year + "]";
	}

}

The class above is just a POJO and each property of the class represents a field in our database table.

Now we will create the class and interface necessary for database logic operation.

Create a new interface, name it “StudentDAO” and put it under com.junald.dao package.

package com.junald.dao;

import java.util.List;

import com.junald.model.Student;

public interface StudentDAO {
	public void addStudent( Student student );
	public void deleteStudent( int studentId );
	public void updateStudent( Student student );
	public List<Student> getAllStudents();
	public Student getStudentById( int studentId );
}

By the way DAO means Data Access Object it is one of J2EE pattern. Please click this to learn more about DAO pattern.

Create a new class and name it “StudentDAOImplementation” and  put it under com.junald.dao package.

package com.junald.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 com.junald.model.Student;
import com.junald.util.DBUtil;

public class StudentDAOImplementation implements StudentDAO {

	private Connection conn;

	public StudentDAOImplementation() {
		conn = DBUtil.getConnection();
	}
	@Override
	public void addStudent( Student student ) {
		try {
			String query = "insert into student (firstName, lastName, course, year) values (?,?,?,?)";
			PreparedStatement preparedStatement = conn.prepareStatement( query );
			preparedStatement.setString( 1, student.getFirstName() );
			preparedStatement.setString( 2, student.getLastName() );
			preparedStatement.setString( 3, student.getCourse() );
			preparedStatement.setInt( 4, student.getYear() );
			preparedStatement.executeUpdate();
			preparedStatement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	@Override
	public void deleteStudent( int studentId ) {
		try {
			String query = "delete from student where studentId=?";
			PreparedStatement preparedStatement = conn.prepareStatement(query);
			preparedStatement.setInt(1, studentId);
			preparedStatement.executeUpdate();
			preparedStatement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	@Override
	public void updateStudent( Student student ) {
		try {
			String query = "update student set firstName=?, lastName=?, course=?, year=? where studentId=?";
			PreparedStatement preparedStatement = conn.prepareStatement( query );
			preparedStatement.setString( 1, student.getFirstName() );
			preparedStatement.setString( 2, student.getLastName() );
			preparedStatement.setString( 3, student.getCourse() );
			preparedStatement.setInt( 4, student.getYear() );
			preparedStatement.setInt(5, student.getStudentId());
			preparedStatement.executeUpdate();
			preparedStatement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	@Override
	public List<Student> getAllStudents() {
		List<Student> students = new ArrayList<Student>();
		try {
			Statement statement = conn.createStatement();
			ResultSet resultSet = statement.executeQuery( "select * from student" );
			while( resultSet.next() ) {
				Student student = new Student();
				student.setStudentId( resultSet.getInt( "studentId" ) );
				student.setFirstName( resultSet.getString( "firstName" ) );
				student.setLastName( resultSet.getString( "lastName" ) );
				student.setCourse( resultSet.getString( "course" ) );
				student.setYear( resultSet.getInt( "year" ) );
				students.add(student);
			}
			resultSet.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return students;
	}
	@Override
	public Student getStudentById(int studentId) {
		Student student = new Student();
		try {
			String query = "select * from student where studentId=?";
			PreparedStatement preparedStatement = conn.prepareStatement( query );
			preparedStatement.setInt(1, studentId);
			ResultSet resultSet = preparedStatement.executeQuery();
			while( resultSet.next() ) {
				student.setStudentId( resultSet.getInt( "studentId" ) );
				student.setFirstName( resultSet.getString( "firstName" ) );
				student.setLastName( resultSet.getString( "LastName" ) );
				student.setCourse( resultSet.getString( "course" ) );
				student.setYear( resultSet.getInt( "year" ) );
			}
			resultSet.close();
			preparedStatement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return student;
	}

}

The class above is necessary for CRUD operations in the database.

Now we will create the main controller or the servlet of the application.

Create a new servlet and name it “StudentController” and put it under com.junald.controller package. Here is the code.


package com.junald.controller;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.junald.dao.StudentDAO;
import com.junald.dao.StudentDAOImplementation;
import com.junald.model.Student;

@WebServlet("/StudentController")
public class StudentController extends HttpServlet {

	private StudentDAO dao;
	private static final long serialVersionUID = 1L;
	public static final String lIST_STUDENT = "/listStudent.jsp";
	public static final String INSERT_OR_EDIT = "/student.jsp";

    public StudentController() {
    	dao = new StudentDAOImplementation();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String forward = "";
		String action = request.getParameter( "action" );

		if( action.equalsIgnoreCase( "delete" ) ) {
			forward = lIST_STUDENT;
			int studentId = Integer.parseInt( request.getParameter("studentId") );
			dao.deleteStudent(studentId);
			request.setAttribute("students", dao.getAllStudents() );
		}
		else if( action.equalsIgnoreCase( "edit" ) ) {
			forward = INSERT_OR_EDIT;
			int studentId = Integer.parseInt( request.getParameter("studentId") );
			Student student = dao.getStudentById(studentId);
			request.setAttribute("student", student);
		}
		else if( action.equalsIgnoreCase( "insert" ) ) {
			forward = INSERT_OR_EDIT;
		}
		else {
			forward = lIST_STUDENT;
			request.setAttribute("students", dao.getAllStudents() );
		}
		RequestDispatcher view = request.getRequestDispatcher( forward );
		view.forward(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		Student student = new Student();
		student.setFirstName( request.getParameter( "firstName" ) );
		student.setLastName( request.getParameter( "lastName" ) );
		student.setCourse( request.getParameter( "course" ) );
		student.setYear( Integer.parseInt( request.getParameter( "year" ) ) );
		String studentId = request.getParameter("studentId");

		if( studentId == null || studentId.isEmpty() )
			dao.addStudent(student);
		else {
			student.setStudentId( Integer.parseInt(studentId) );
			dao.updateStudent(student);
		}
		RequestDispatcher view = request.getRequestDispatcher( lIST_STUDENT );
		request.setAttribute("students", dao.getAllStudents());
		view.forward(request, response);
	}
}

We need a form for inserting students data. So create a new JSP and name it “student.jsp“. Then insert the following code.


<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">

<title>Add New Student</title>
</head>
<body>
	<form action="StudentController.do" method="post">
		<fieldset>
			<div>
				<label for="studentId">Student ID</label> <input type="text"
					name="studentId" value="<c:out value="${student.studentId}" />"
					readonly="readonly" placeholder="Student ID" />
			</div>
			<div>
				<label for="firstName">First Name</label> <input type="text"
					name="firstName" value="<c:out value="${student.firstName}" />"
					placeholder="First Name" />
			</div>
			<div>
				<label for="lastName">Last Name</label> <input type="text"
					name="lastName" value="<c:out value="${student.lastName}" />"
					placeholder="Last Name" />
			</div>
			<div>
				<label for="course">Course</label> <input type="text" name="course"
					value="<c:out value="${student.course}" />" placeholder="Course" />
			</div>
			<div>
				<label for="year">Year</label> <input type="text" name="year"
					value="<c:out value="${student.year}" />" placeholder="Year" />
			</div>
			<div>
				<input type="submit" value="Submit" />
			</div>
		</fieldset>
	</form>
</body>
</html>

And for displaying the students data, we need to create a new JSP and name it “listStudent.jsp“. Then insert the following code.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!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=ISO-8859-1">
<title>Show All Students</title>
</head>
<body>
	<table>
		<thead>
			<tr>
				<th>Student ID</th>
				<th>First Name</th>
				<th>Last Name</th>
				<th>Course</th>
				<th>Year</th>
				<th colspan="2">Action</th>
			</tr>
		</thead>
		<tbody>
			<c:forEach items="${students}" var="student">
				<tr>
					<td><c:out value="${student.studentId}" /></td>
					<td><c:out value="${student.firstName}" /></td>
					<td><c:out value="${student.lastName}" /></td>
					<td><c:out value="${student.course}" /></td>
					<td><c:out value="${student.year}" /></td>
					<td><a
						href="StudentController.do?action=edit&studentId=<c:out value="${student.studentId }"/>">Update</a></td>
					<td><a
						href="StudentController.do?action=delete&studentId=<c:out value="${student.studentId }"/>">Delete</a></td>
				</tr>
			</c:forEach>
		</tbody>
	</table>
	<p>
		<a href="StudentController.do?action=insert">Add Student</a>
	</p>
</body>
</html>

For the default page of the application, create a new JSP file and name it index.jsp. Then insert the following code.


<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<!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=ISO-8859-1">
<title>Welcome</title>
</head>
<body>
	<jsp:forward page="/StudentController?action=listStudent"></jsp:forward>
</body>
</html>

And finally, the code for our deployment descriptor define in the web.xml. Make sure it is the same as the code below.

<?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" 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>CRUD Project</display-name>
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
  <servlet>
  <servlet-name>StudentController</servlet-name>
  <servlet-class>com.junald.controller.StudentController</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>StudentController</servlet-name>
  <url-pattern>/StudentController.do</url-pattern>
  </servlet-mapping>
</web-app>

So we are done. Run it using your tomcat server!

app-screenshot

If you want to learn more about servlets and JSP’s, I would recommend a book: Head First Sevlet/JSP.

You can download the all source code from my Github account.

If you liked this tutorial, please subcribe or follow to my blog and like my facebook page.

Advertisements

25 thoughts on “A Simple CRUD Tutorial Using Java Servlet/JSP and MySQL

  1. Hello, Actually I try exactly what you given in this tutorial ie. “Crud operation Tutorial” , But Doesn’t Understand why you take “StudentController.do” instead of “StudentController” and also I don’t understand the Mapping you did in “web.xml” file. It is Very Good Tutorial I found But Something is Confusing for me…So I want clear my doubts.
    Other Thing is that when I run this Program I got error that CrudApp/StudentController not found i.e. HTTP 404 error in Tomcat Server. CrudApp is my project’s name…So Can You Please Tell Me What Exactly wrong with me…eagrly waiting for your reply
    Thanks in Advance

    Like

    • remove all the references of StudentController.do from the project and replace by StudentController. Worked for me.Also, remove any reference of servelet-api.jar as well from the project path.

      Like

  2. why you take “StudentController.do” instead of “StudentController” and also I don’t understand the Mapping you did in “web.xml” file.
    I got ERROR:HTTP 404 error in Tomcat Server.
    Please suggest me something..

    Like

  3. hi! i am trying this ..when i done i face the problem as
    java.lang.NumberFormatException: For input string: “<c:out value=4"
    java.lang.NumberFormatException.forInputString(Unknown Source)
    java.lang.Integer.parseInt(Unknown Source)
    java.lang.Integer.parseInt(Unknown Source)
    com.customerlist.servlet.CustomerList.doPost(CustomerList.java:64)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
    my coding is here..
    while( resultSet.next() ) {
    Customer customer = new Customer();
    customer.setCustomerId( resultSet.getInt( "CustomerId" ) );
    customer.setCustomerName( resultSet.getString( "CustomerName" ) );
    customer.setCustomerAddress( resultSet.getString( "CustomerAddress" ) );
    customer.setCustomerplanId( resultSet.getInt( "CustomerplanId" ) );
    customers.add(customer);
    }
    plz help me

    Like

  4. Getting a http 500 error can you help me with that
    Exception report

    message An exception occurred processing JSP page /index.jsp at line 10

    description The server encountered an internal error that prevented it from fulfilling this request.

    : An exception occurred processing JSP page /index.jsp at line 10

    7: Welcome
    8:
    9:
    10:
    11:
    12:

    Like

  5. i have modified the code and add student login to the StudentDAO and StudentDAOImplementation but i dont know how to call it in the servlet ;

    StudentDAO:

    public boolean validate(String UserName, String Password);

    StudentDAOImplementation:
    public boolean validate(String UserName, String Password) {
    boolean status = false;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
    String query = “SELECT * FROM Users WHERE UserName=? and Password=?”;
    preparedStatement = conn.prepareStatement( query );

    preparedStatement.setString(1, UserName);
    preparedStatement.setString(2, Password);
    resultSet = preparedStatement.executeQuery();

    status=resultSet.next();

    } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }finally {
    if (conn != null) {
    try {
    conn.close();
    } catch (SQLException e) {
    e.printStackTrace();
    }
    }
    if (preparedStatement != null) {
    try {
    preparedStatement.close();
    } catch (SQLException e) {
    e.printStackTrace();
    }
    }
    if (resultSet != null) {
    try {
    resultSet.close();
    } catch (SQLException e) {
    e.printStackTrace();
    }
    }
    }

    return status;
    }

    how do i call it in the servlet

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s