Spring MVC with MySQL and Junit Finding Employees Based on Location (original) (raw)
In real-world scenarios, organizations are existing in different localities. Employees are available in many locations. Sometimes they work in different 2 locations i.e. for a few days, they work on location 1 and for a few other days, they work on location 2. Let's simulate this scenario via MySQL queries and prepare a Spring MVC application that interacts with MySQL and get the required details. And also let us see JUNIT test cases as well.
Required MySQL Queries:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
DROP TABLE test.employeesdetails;
CREATE TABLE employeesdetails (
id int(6) unsigned NOT NULL,
Name varchar(50) DEFAULT NULL,
AvailableDays varchar(200) DEFAULT NULL,
location varchar(50) DEFAULT NULL,
qualification varchar(20) DEFAULT NULL,
experience int(11) DEFAULT NULL,
gender varchar(10) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
INSERT INTO test.employeesdetails
(id,Name,AvailableDays,location,qualification,
experience,gender) VALUES
(1,'EmployeeA','Monday,Tuesday,Friday','Location1','BE',5,'Female');
INSERT INTO test.employeesdetails
(id,Name,AvailableDays,location,qualification,
experience,gender) VALUES
(2,'EmployeeB','Monday,Wednesday,Friday','Location1','MCA',3,'Female');
INSERT INTO test.employeesdetails
(id,Name,AvailableDays,location,qualification,
experience,gender) VALUES
(3,'EmployeeC', 'Wednesday,Thursday','Location2','BE',5,'Female');
INSERT INTO test.employeesdetails
(id,Name,AvailableDays,location,qualification,
experience,gender) VALUES
(4,'Employees','Saturday,Sunday','Location2','MBA',4,'Male');
INSERT INTO test.employeesdetails
(id,Name,AvailableDays,location,qualification,
experience,gender) VALUES
(5,'EmployeeE','Tuesday,Thursday','Location2','MCA',3,'Female');
INSERT INTO test.employeesdetails
(id,Name,AvailableDays,location,qualification,
experience,gender) VALUES
(6,'EmployeeA','Wednesday,Thursday','Location2','BE',5,'Female');
SELECT * FROM test.employeesdetails;
Output of test.employeesdetails:
With this setup, let us start the Spring MVC project that interacts with MySQL and produce the details upon our queries
Implementation
Project Structure:
This is a Maven-driven project. Let's start with
pom.xml
XML `
4.0.0 com.employees SpringMVCFindEmployee war 0.0.1-SNAPSHOT SpringMVCFindEmployee Maven Webapp http://maven.apache.org false 5.1.0.RELEASE junit junit 4.12 test org.mockito mockito-all 1.9.5 test org.springframework spring-webmvc ${spring-version} org.springframework spring-context ${spring-version} org.springframework spring-test ${spring-version} org.apache.tomcat tomcat-jasper 9.0.12 javax.servlet javax.servlet-api 3.1.0 provided javax.servlet jstl 1.2 mysql mysql-connector-java 8.0.11 org.springframework spring-jdbc ${spring-version} SpringMVCFindEmployee src/main/java org.apache.maven.plugins maven-compiler-plugin 3.5.1 1.8 1.8 org.apache.maven.plugins maven-war-plugin 3.3.2
`
Let's see some important java files.
Bean class
Employee.java
Java `
public class Employee { // All instance variables should // match with the columns present // in MySQL test.employeedetails table private int id; private String name; private float salary; private String availableDays; private String location; private String qualification; private int experience; private String gender; public String getLocation() { return location; }
public void setLocation(String location) {
this.location = location;
}
public String getQualification() {
return qualification;
}
public void setQualification(String qualification) {
this.qualification = qualification;
}
public int getExperience() {
return experience;
}
public void setExperience(int experience) {
this.experience = experience;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getSalary() {
return salary;
}
public void setSalary(float salary) {
this.salary = salary;
}
public String getAvailableDays() {
return availableDays;
}
public void setAvailableDays(String availableDays) {
this.availableDays = availableDays;
}}
`
EmployeeController.java
Java `
import com.employees.beans.Employee; import com.employees.dao.EmployeeDao; import java.sql.SQLException; import java.util.StringTokenizer; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.ModelAttribute; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.SessionAttributes; import org.springframework.web.servlet.ModelAndView;
@Controller @SessionAttributes("employee") public class EmployeeController { @Autowired EmployeeDao dao;
@Autowired public EmployeeController(EmployeeDao dao)
{
this.dao = dao;
}
@ModelAttribute("employee")
public Employee getEmployee()
{
return new Employee();
}
// for searchform
@RequestMapping("/employeesearchform")
public String searchform(Model m)
{
m.addAttribute("command", new Employee());
return "employeesearchform";
}
// It provides search of employees in model object
@RequestMapping(value = "/searchEmployee",
method = RequestMethod.POST)
public ModelAndView
searchEmployee(@ModelAttribute("employee")
Employee employee)
{
ModelAndView mav = null;
Employee employee1;
try {
employee1 = dao.getEmployeesByNameAndLocation(
employee.getName(), employee.getLocation());
mav = new ModelAndView("welcome");
if (null != employee1) {
System.out.println(
employee1.getId() + "..."
+ employee1.getName() + ".."
+ employee1.getAvailableDays()
+ "..chosen location.."
+ employee.getLocation());
StringTokenizer st = new StringTokenizer(
employee1.getAvailableDays(), ",");
boolean isAvailable = false;
while (st.hasMoreTokens()) {
// System.out.println(st.nextToken());
// if
// (st.nextToken().equalsIgnoreCase(employee.getAvailableDays()))
// {
isAvailable = true;
break;
//}
}
mav.addObject("firstname",
employee1.getName());
if (isAvailable) {
mav.addObject("availability",
"Available on");
}
else {
mav.addObject("availability",
"Not Available on");
}
mav.addObject("day",
employee1.getAvailableDays());
mav.addObject("location",
employee.getLocation());
}
else {
mav.addObject("firstname",
employee.getName());
mav.addObject("availability",
"Not Available ");
mav.addObject("location",
employee.getLocation());
}
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return mav;
}}
`
EmployeeDao.java
Java `
import com.employees.beans.Employee; import java.sql.SQLException; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate;
public class EmployeeDao { // We can straight away write SQL queries related to // MySQL as we are using JdbcTemplate JdbcTemplate template;
public void setTemplate(JdbcTemplate template)
{
this.template = template;
}
public Employee
getEmployeesByNameAndLocation(String employeeName,
String locationName)
throws SQLException
{
String sql
= "select * from employeesdetails where name=? and location = ?";
return template.queryForObject(
sql,
new Object[] { employeeName, locationName },
new BeanPropertyRowMapper<Employee>(
Employee.class));
}
public Employee
getEmployeesByGender(String gender,
String availabledays)
throws SQLException
{
String sql
= "select * from employeesdetails where gender=? and availabledays = ?";
return template.queryForObject(
sql, new Object[] { gender, availabledays },
new BeanPropertyRowMapper<Employee>(
Employee.class));
}
public Employee
getEmployeesByQualification(String qualification,
String availabledays)
throws SQLException
{
String sql
= "select * from employeesdetails where qualification=? and availabledays = ?";
return template.queryForObject(
sql,
new Object[] { qualification, availabledays },
new BeanPropertyRowMapper<Employee>(
Employee.class));
}
public Employee
getEmployeesByExperience(int experienceInYears)
throws SQLException
{
String sql
= "select * from employeesdetails where experience=?";
return template.queryForObject(
sql, new Object[] { experienceInYears },
new BeanPropertyRowMapper<Employee>(
Employee.class));
}}
`
We need to have an important file called spring-servlet.xml. This will have the MySQL connectivity information
XML `
<context:component-scan base-package="com.employees.controllers" />
`
Ok, now let us use JSP pages to search the employees by using the Spring MVC project and the available data present in the MySQL
index.jsp
Java `
// Beautify the code if required, // This will provide a hyperlink and // it will go to the employeesearchform.jsp
Search Employees By Location`
employeesearchform.jsp
HTML `
<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form"%>
<%@ taglib uri="http://www.oracle.com/technetwork/java/index.html" prefix="c"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1" %>
Search Employees
| Employee Name : | |
| Choose a Location : | |
`
Output:
After entering details, the output is shown via
welcome.jsp
HTML `
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1" %>
Welcome| Employee Name : | ${firstname} | ||
| Availability : | ${availability} | ${day} | at ${location} |
| Search Again |
`
We can check the same via our test cases as well
EmployeeControllerTest.java
Java `
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.get; import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.status;
import org.junit.Assert; import org.junit.Before; import org.junit.Test; import org.junit.runner.RunWith; import org.mockito.InjectMocks; import org.mockito.MockitoAnnotations; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import org.springframework.test.context.web.WebAppConfiguration; import org.springframework.test.web.servlet.MockMvc; import org.springframework.test.web.servlet.setup.MockMvcBuilders; import org.springframework.web.context.WebApplicationContext;
import com.employees.beans.Employee; import com.employees.controllers.EmployeeController; import com.employees.dao.EmployeeDao;
@ContextConfiguration(locations = { "file:src/main/webapp/WEB-INF/spring-servlet.xml" }) @RunWith(SpringJUnit4ClassRunner.class) @WebAppConfiguration public class EmployeeControllerTest {
@InjectMocks
private EmployeeController employeeController;
private MockMvc mockMvc;
@Autowired
private EmployeeDao dao;
@Autowired
WebApplicationContext webApplicationContext;
@Before
public void setup() {
MockitoAnnotations.initMocks(this);
this.mockMvc = MockMvcBuilders.standaloneSetup(employeeController).build();
}
@Test
// 404 error thrown when coming from invalid resources
public void testCreateSearchEmployeesPageFormInvalidUser() throws Exception {
this.mockMvc.perform(get("/"))
.andExpect(status().isNotFound());
}
@Test
// positive testcase
public void testSearchEmployeesByNameAndCheckAvailability() throws Exception {
Employee employee = new Employee();
employee.setName("EmployeeA");
employee.setLocation("Location1");
employee = dao.getEmployeesByNameAndLocation(employee.getName(),employee.getLocation());
Assert.assertEquals(1, employee.getId());
Assert.assertEquals("Monday,Tuesday,Friday", employee.getAvailableDays());
}
@Test
// Negative testcase
public void testSearchEmployeesByNameAndCheckAvailabilityWithNotEqualsValues() throws Exception {
Employee employee = new Employee();
employee.setName("EmployeeA");
employee.setLocation("Location2");
employee = dao.getEmployeesByNameAndLocation(employee.getName(),employee.getLocation());
Assert.assertNotEquals(10, employee.getId());
Assert.assertNotEquals("Tuesday,Thursday", employee.getAvailableDays());
}
@Test
//Negative testcase i.e. Given gender as Male and available days as Saturday
public void testSearchEmployeesByGender() throws Exception {
Employee employee = new Employee();
employee.setGender("Male");
employee.setAvailableDays("Saturday,Sunday");
employee = dao.getEmployeesByGender(employee.getGender(),employee.getAvailableDays());
Assert.assertEquals(4, employee.getId());
Assert.assertNotEquals("EmployeeB", employee.getName());
Assert.assertNotEquals(1, employee.getExperience());
}
@Test
// Negative testcase i.e. Given gender as Male and available days as Saturday
public void testSearchEmployeesByGenderWithCorrectResults() throws Exception {
Employee employee = new Employee();
employee.setGender("Male");
employee.setAvailableDays("Saturday,Sunday");
employee = dao.getEmployeesByGender(employee.getGender(),employee.getAvailableDays());
Assert.assertEquals(4, employee.getId());
Assert.assertNotEquals("EmployeeB", employee.getName());
Assert.assertNotEquals(1, employee.getExperience());
}
@Test
// Negative testcase i.e. giving experience as 4 years and checking
// as the name of the doctor to be DoctorE instead of DoctorD
public void testSearchEmployeesByExperience() throws Exception {
Employee employee = new Employee();
employee.setExperience(4);
employee = dao.getEmployeesByExperience(employee.getExperience());
Assert.assertEquals(4, employee.getId());
Assert.assertNotEquals("EmployeeF", employee.getName());
}
@Test
public void testSearchEmployeesByQualification() throws Exception {
Employee employee = new Employee();
employee.setQualification("MBA");
employee.setAvailableDays("Saturday,Sunday");
employee = dao.getEmployeesByQualification(employee.getQualification(),employee.getAvailableDays());
Assert.assertEquals(4, employee.getId());
Assert.assertEquals("EmployeeD", employee.getName());
Assert.assertNotEquals(15, employee.getExperience());
}
}
`
On executing the test cases, we can see the below output
One can simulate this kind of scenario, and prepare a spring MVC project along with JUNIT test cases.