JSF Delete Data from Database Table




Hey guys in this post, we will discuss deleting the record from MySQL database in JSF web application with full coding example.

Complete example


We will create this example step by step, follow this tutorial till the end

Read More:

Create Dynamic Web Project


To create a dynamic web project, you can read the following tutorial

Add the JDBC driver Jar file


Download and add the Jar file to the WEB-INF/lib folder of the JSF application.

Download: http://dev.mysql.com/downloads

Create database


Run the following queries in MySQL database

CREATE DATABASE jsfwebapp;

USE jsfwebapp;

CREATE TABLE tbl_employees
(
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    department VARCHAR(255) NOT NULL
);

INSERT INTO tbl_employees (name, email, department)
VALUES ("Sharath", "[email protected]", "IT");
INSERT INTO tbl_employees (name, email, department)
VALUES ("Praveen", "[email protected]", "Sales");
INSERT INTO tbl_employees (name, email, department)
VALUES ("Ramesh", "[email protected]", "Marketing");

Define the connection pool


Create META_INF/context.xml file and add the following content

<Context>
	<Resource 
		name="jdbc/employee_tracker"
		auth="Container"
		type="javax.sql.DataSource"
		maxActive="20"
		maxIdle="5"
		maxWait="10000"
		username="root"
		password="root"
		driverClassName="com.mysql.jdbc.Driver"
		url="jdbc:mysql://localhost:3306/jsfwebapp"
	/>
</Context>

name="jdbc/employee_tracker", this is just a name of the connection pool. Later we will use the same name in the configuration file to reference the datasource.

Make sure to the change the database username and password as per your installation

Configure the reference


Open web.xml file and add the following content

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
  <display-name>JSF-hello-world</display-name>
  <servlet>
    <servlet-name>Faces Servlet</servlet-name>
    <servlet-class>javax.faces.webapp.FacesServlet</servlet-class>
    <load-on-startup>1</load-on-startup>
  </servlet>
  <servlet-mapping>
    <servlet-name>Faces Servlet</servlet-name>
    <url-pattern>/faces/*</url-pattern>
  </servlet-mapping>
  <resource-ref>
  	<description>Employee Tracker</description>
  	<res-ref-name>jdbc/employee_tracker</res-ref-name>
  	<res-type>javax.sql.DataSource</res-type>
  	<res-auth>Container</res-auth>
  </resource-ref>
</web-app>

Here <res-ref-name> is the same name that we given in creating the connection pool



Create a model class


Create Employee.java inside the in.bushansirgur.jsf.beans and add the following content

package in.bushansirgur.jsf.beans;

import javax.faces.bean.ManagedBean;

@ManagedBean
public class Employee {
	
	private int id;
	
	private String name;
	
	private String department;
	
	private String email;
	
	public Employee() {
	
	}
	
	public Employee(int id, String name, String department, String email) {
		this.id = id;
		this.name = name;
		this.department = department;
		this.email = email;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getDepartment() {
		return department;
	}

	public void setDepartment(String department) {
		this.department = department;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}
}

This is just a bean class that annotated with @ManagedBean annotation and contains private fields, setters, getters and toString()

Create a Util class


Create EmployeeDbUitl.java inside in.bushansirgur.jsf.util package and add the following content

package in.bushansirgur.jsf.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import in.bushansirgur.jsf.beans.Employee;

public class EmployeeDbUtil {
	
	private static EmployeeDbUtil instance;
	private DataSource datasource;
	private String jndiName = "java:comp/env/jdbc/employee_tracker";
	
	public static EmployeeDbUtil getInstance() throws Exception {
		if (instance == null) {
			instance = new EmployeeDbUtil();
		}
		return instance;
	}
	
	private EmployeeDbUtil() throws Exception {
		datasource = getDataSource();
	}
	
	private DataSource getDataSource() throws NamingException {
		Context context = new InitialContext();
		DataSource datasource = (DataSource) context.lookup(jndiName);
		return datasource;
	}
	
	public List<Employee> getEmployees() throws Exception {
		List<Employee> employees = new ArrayList<>();
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		
		try {
			connection = datasource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select * from tbl_employees");
			while(resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String email = resultSet.getString("email");
				String department = resultSet.getString("department");
				Employee employee = new Employee(id, name, department, email);
				employees.add(employee);
			}
			return employees;
		}
		catch(Exception ex) {
			ex.printStackTrace();
		}
		return new ArrayList<Employee>();
	}

	public void saveEmployee(Employee employee) throws Exception{
		
		Connection connection = null;
		PreparedStatement statement = null;
		
		try {
			
			connection = datasource.getConnection();
			String sql = "insert into tbl_employees(name, department, email) values (?, ?, ?)";
			statement = connection.prepareStatement(sql);
			
			statement.setString(1, employee.getName());
			statement.setString(2, employee.getDepartment());
			statement.setString(3, employee.getEmail());
			
			statement.execute();
		}catch (Exception ex) {
			ex.printStackTrace();
		}
		
	}

	public Employee getEmployee(int employeeId) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		
		try {
			connection = datasource.getConnection();
			String sql = "SELECT * FROM tbl_employees WHERE id=?";
			statement = connection.prepareStatement(sql);
			
			statement.setInt(1, employeeId);
			resultSet = statement.executeQuery();
			
			Employee employee = null;
			if (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String department = resultSet.getString("department");
				String email = resultSet.getString("email");
				
				employee = new Employee(id, name, department, email);
			} else {
				throw new Exception("Could not find employee id"+employeeId);
			}
			return employee;
		}catch (Exception ex) {
			ex.printStackTrace();
		}
		return null;
	}

	public void deleteEmployee(int id) {
		
		Connection connection = null;
		PreparedStatement statement = null;
		
		try {
			connection = datasource.getConnection();
			String sql = "DELETE FROM tbl_employees WHERE id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			statement.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
}

Create a controller


Create EmployeeController.java inside in.bushansirgur.jsf.controller package and add the following content

package in.bushansirgur.jsf.controller;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.ExternalContext;
import javax.faces.context.FacesContext;

import in.bushansirgur.jsf.beans.Employee;
import in.bushansirgur.jsf.util.EmployeeDbUtil;

@ManagedBean
@SessionScoped
public class EmployeeController {
	
	private List<Employee> employees;
	private EmployeeDbUtil employeeDbUtil;
	
	public EmployeeController() throws Exception {
		employees = new ArrayList<Employee>();
		employeeDbUtil = EmployeeDbUtil.getInstance();
	}
	
	public List<Employee> getEmployees () {
		return employees;
	}
	
	public void loadEmployees () {
		employees.clear();
		try {
			employees = employeeDbUtil.getEmployees();
		}catch (Exception ex) {
			addErrorMessage (ex);
		}
	}
	
	private void addErrorMessage(Exception ex) {
		FacesMessage message = new FacesMessage(ex.getMessage());
		FacesContext.getCurrentInstance().addMessage(null, message);
	}
	
	public String saveEmployee(Employee employee) {
		try {
			employeeDbUtil.saveEmployee(employee);
		}catch (Exception ex) {
			addErrorMessage (ex);
		}
		return "dashboard";
	}
	
	public String deleteEmployee(int id) {
		try {
			employeeDbUtil.deleteEmployee(id);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "dashboard";
	}
	
}	

We will mark this class with @ManagedBean annotation and @SessionScoped annotation, which is scoped to single user

Create a response page


Create dashboard.xhtml inside WebContent and add the following content

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
		xmlns:h="http://xmlns.jcp.org/jsf/html"
		xmlns:ui="http://xmlns.jcp.org/jsf/facelets"
		xmlns:c="http://xmlns.jcp.org/jsf/core">
	
	<c:metadata>
		<c:event listener="#{employeeController.loadEmployees()}" type="preRenderView" />
	</c:metadata>
	
	
	<h:head>
		<title>Dashboard</title>
		<h:outputStylesheet library="css" name="dashboard.css" />
	</h:head>
	
	<h:body>
		
		<h2>Employees List</h2>
		<hr/>
		<h:button value="Create Employee" outcome="create-employee-form" />
		<br/>
		<h:form>
			<h:dataTable value="#{employeeController.employees}" var="employee" border="1">
				
				<h:column>
				
					<c:facet name="header">Name</c:facet>	
					#{employee.name}
				
				</h:column>
				<h:column>
				
					<c:facet name="header">Department</c:facet>	
					#{employee.department}
				
				</h:column>
				<h:column>
				
					<c:facet name="header">Email</c:facet>	
					#{employee.email}
				
				</h:column>
				
				<h:column>
				
					<c:facet name="header">Actions</c:facet>
					<h:commandLink value="Delete" 
						onclick="if (!confirm('Are you sure want to delete this employee?')) return false"
						action="#{employeeController.deleteEmployee(employee.id)}"/>
				</h:column>
			</h:dataTable>
		</h:form>
		<h:messages globalOnly="true"/>
		
	</h:body>

</html>

Run the app


To run the application, right click on the project, select Run As -> choose Run on Server

Open the browser, navigate to the URL http://localhost:8080/JSF-hello-world/faces/dashboard.xhtml

21

22

23
That’s it for this post. If you like this post, then please share this with your friends and collogues. Also share this post on your social media profiles as well.



Bushan Sirgur

Hey guys, I am Bushan Sirgur from Banglore, India. Currently, I am working as an Associate project in an IT company.

Leave a Reply