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

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

}

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

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

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

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