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 version = "1.0" encoding = "UTF-8" ?>
< project xmlns = "http://maven.apache.org/POM/4.0.0" xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
< modelVersion >4.0.0</ modelVersion >
< groupId >com.employees</ groupId >
< artifactId >SpringMVCFindEmployee</ artifactId >
< packaging >war</ packaging >
< version >0.0.1-SNAPSHOT</ version >
< name >SpringMVCFindEmployee Maven Webapp</ name >
< properties >
< failOnMissingWebXml >false</ failOnMissingWebXml >
< spring-version >5.1.0.RELEASE</ spring-version >
</ properties >
< dependencies >
< dependency >
< groupId >junit</ groupId >
< artifactId >junit</ artifactId >
< version >4.12</ version >
< scope >test</ scope >
</ dependency >
< dependency >
< groupId >org.mockito</ groupId >
< artifactId >mockito-all</ artifactId >
< version >1.9.5</ version >
< scope >test</ scope >
</ dependency >
< dependency >
< groupId >org.springframework</ groupId >
< artifactId >spring-webmvc</ artifactId >
< version >${spring-version}</ version >
</ dependency >
< dependency >
< groupId >org.springframework</ groupId >
< artifactId >spring-context</ artifactId >
< version >${spring-version}</ version >
</ dependency >
< dependency >
< groupId >org.springframework</ groupId >
< artifactId >spring-test</ artifactId >
< version >${spring-version}</ version >
</ dependency >
< dependency >
< groupId >org.apache.tomcat</ groupId >
< artifactId >tomcat-jasper</ artifactId >
< version >9.0.12</ version >
</ dependency >
< dependency >
< groupId >javax.servlet</ groupId >
< artifactId >javax.servlet-api</ artifactId >
< version >3.1.0</ version >
< scope >provided</ scope >
</ dependency >
< dependency >
< groupId >javax.servlet</ groupId >
< artifactId >jstl</ artifactId >
< version >1.2</ version >
</ dependency >
< dependency >
< groupId >mysql</ groupId >
< artifactId >mysql-connector-java</ artifactId >
< version >8.0.11</ version >
</ dependency >
< dependency >
< groupId >org.springframework</ groupId >
< artifactId >spring-jdbc</ artifactId >
< version >${spring-version}</ version >
</ dependency >
</ dependencies >
< build >
< finalName >SpringMVCFindEmployee</ finalName >
< sourceDirectory >src/main/java</ sourceDirectory >
< plugins >
< plugin >
< groupId >org.apache.maven.plugins</ groupId >
< artifactId >maven-compiler-plugin</ artifactId >
< version >3.5.1</ version >
< configuration >
< source >1.8</ source >
< target >1.8</ target >
</ configuration >
</ plugin >
<!-- This should be added to overcome Could not initialize
class org.apache.maven.plugin.war.util.WebappStructureSerializer -->
< plugin >
< groupId >org.apache.maven.plugins</ groupId >
< artifactId >maven-war-plugin</ artifactId >
< version >3.3.2</ version >
</ plugin >
</ plugins >
</ build >
</ project >
|
Let’s see some important java files.
Bean class
Employee.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
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
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 version = "1.0" encoding = "UTF-8" ?>
< context:component-scan base-package = "com.employees.controllers" />
< bean class = "org.springframework.web.servlet.view.InternalResourceViewResolver" >
< property name = "prefix" value = "/WEB-INF/jsp/" />
< property name = "suffix" value = ".jsp" />
</ bean >
< bean id = "ds" class = "org.springframework.jdbc.datasource.DriverManagerDataSource" >
< property name = "driverClassName" value = "com.mysql.cj.jdbc.Driver" />
<!-- As we are using test database, it is given as test here
Modify it according to your database name useSSL=false
is required to overcome SSL errors -->
< property name = "username" value = "root" />
< property name = "password" value = "*****" />
<!--Specify correct password here -->
</ bean >
< bean id = "jt" class = "org.springframework.jdbc.core.JdbcTemplate" >
< property name = "dataSource" ref = "ds" />
</ bean >
< bean id = "dao" class = "com.employees.dao.EmployeeDao" >
< property name = "template" ref = "jt" />
</ bean >
</ beans >
|
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
// Beautify the code if required, // This will provide a hyperlink and // it will go to the employeesearchform.jsp <center> <a href= "employeesearchform" >Search Employees By Location</a></center>
|
employeesearchform.jsp
<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <%@ 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 >Search Employees</ title >
</ head >
< body >
< h1 >Search Employees</ h1 >
< form:form method = "post" action = "/SpringMVCFindEmployee/searchEmployee" >
< table >
< tr >
< td >Employee Name : </ td >
< td >
< form:input path = "name" />
</ td >
</ tr >
< tr >
< td >Choose a Location : </ td >
< td >
< form:select path = "location" >
< form:option value = "Location1" label = "Location1" />
< form:option value = "Location2" label = "Location2" />
</ form:select >
</ td >
</ tr >
< tr >
< td > </ td >
< td >< input type = "submit" value = "Search" /></ td >
</ tr >
</ table >
</ form:form >
</ body >
</ html >
|
Output:
After entering details, the output is shown via
welcome.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1" %> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" < html >
< head >
< meta http-equiv = "Content-Type" content = "text/html; charset=ISO-8859-1" >
< title >Welcome</ title >
</ head >
< body >
< table >
< tr >
< td > Employee Name :</ td >
< td >${firstname}</ td >
</ tr >
< tr >
< td > Availability :</ td >
< td >${availability} </ td >
< td >${day}</ td >
< td > at ${location}</ td >
</ tr >
< tr >
</ tr >
< tr >
</ tr >
< tr >
< td >< a href = "employeesearchform" >Search Again</ a >
</ td >
</ tr >
</ table >
</ body >
</ html >
|
We can check the same via our test cases as well
EmployeeControllerTest.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.