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

5 August 2024

0

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

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.

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

Implementation

XML

Java

Java

Java

XML

Java

HTML

HTML

Java

Java Program for Longest Common Subsequence

Maximum height of Tree when any Node can be considered as Root

Print Fibonacci sequence using 2 variables

LEAVE A REPLY Cancel reply

Most Popular

How to Set Up a VPN on Any Device in 2024 (Full Guide) by Tim Mocan

The Pixel 9 Pro Fold proved you shouldn’t buy first-gen Google products

The latest One UI 7 beta hints at Samsung’s foldable plans for 2025

This might be our first glimpse at the OnePlus Open 2’s new design

Recent Comments

EDITOR PICKS

How to Set Up a VPN on Any Device in 2024 (Full Guide) by Tim Mocan

The Pixel 9 Pro Fold proved you shouldn’t buy first-gen Google products

The latest One UI 7 beta hints at Samsung’s foldable plans for 2025

POPULAR POSTS

How to Set Up a VPN on Any Device in 2024 (Full Guide) by Tim Mocan

The Pixel 9 Pro Fold proved you shouldn’t buy first-gen Google products

The latest One UI 7 beta hints at Samsung’s foldable plans for 2025

POPULAR CATEGORY

ABOUT US

FOLLOW US