Spring MVC with MySQL and Junit - Finding Employees Based on Location

Last Updated : 01 May, 2023

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

<?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                               http://maven.apache.org/maven-v4_0_0.xsd">    <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>    <url>http://maven.apache.org</url>    <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>       <!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->       <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>       <!-- https://mvnrepository.com/artifact/org.apache.tomcat/tomcat-jasper -->       <dependency>          <groupId>org.apache.tomcat</groupId>          <artifactId>tomcat-jasper</artifactId>          <version>9.0.12</version>       </dependency>       <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->       <dependency>          <groupId>javax.servlet</groupId>          <artifactId>javax.servlet-api</artifactId>          <version>3.1.0</version>          <scope>provided</scope>       </dependency>       <!-- https://mvnrepository.com/artifact/javax.servlet/jstl -->       <dependency>          <groupId>javax.servlet</groupId>          <artifactId>jstl</artifactId>          <version>1.2</version>       </dependency>       <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->       <dependency>          <groupId>mysql</groupId>          <artifactId>mysql-connector-java</artifactId>          <version>8.0.11</version>       </dependency>       <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->       <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

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

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans"         xmlns:context="http://www.springframework.org/schema/context"         xmlns:mvc="http://www.springframework.org/schema/mvc"         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="                     http://www.springframework.org/schema/beans                           http://www.springframework.org/schema/beans/spring-beans.xsd                   http://www.springframework.org/schema/context                             http://www.springframework.org/schema/context/spring-context.xsd                    http://www.springframework.org/schema/mvc                              http://www.springframework.org/schema/mvc/spring-mvc.xsd">    <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="url" value="jdbc:mysql://localhost:3306/test?useSSL=false" />       <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

Java

// 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

HTML

<%@ 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

HTML

<%@ 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>       <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

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());       }           }