Hey guys in this post, we will discuss deleting the record from MySQL database in JSF web application with full coding example.
Table of Contents
Complete example
We will create this example step by step, follow this tutorial till the end
Read More:
- Check the Complete JavaServer Faces (JSF) Tutorial
- Check the Complete Spring Data JPA Tutorial
- Check the Complete Spring Security Tutorial
- Check the Javascript Projects for Beginners
- Check the Spring JDBC Tutorial
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
andpassword
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
—
—
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.