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

In this guide, we'll show you how to create a Spring MVC application that interacts with a MySQL database to find employees based on their location. Additionally, we'll write a JUnit test to verify the functionality.

1. Setting Up Dependencies

In your pom.xml, add the following dependencies:

<!-- Spring Web MVC and Spring JDBC -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-webmvc</artifactId>
    <version>5.x.x.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.x.x.RELEASE</version>
</dependency>

<!-- MySQL Connector -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.x.x</version>
</dependency>

<!-- JUnit -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13</version>
    <scope>test</scope>
</dependency>

2. Database Setup

Assuming you have a table in MySQL:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    location VARCHAR(50)
);

3. Spring MVC Configuration

Create a configuration class:

@Configuration
@EnableWebMvc
@ComponentScan(basePackages = "com.example.demo")
public class WebConfig implements WebMvcConfigurer {

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/your_database_name");
        dataSource.setUsername("your_username");
        dataSource.setPassword("your_password");
        return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    // Other configurations...
}

4. DAO Layer

@Repository
public class EmployeeDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Employee> findEmployeesByLocation(String location) {
        String sql = "SELECT * FROM employees WHERE location = ?";
        return jdbcTemplate.query(sql, new Object[]{location}, new EmployeeRowMapper());
    }

    class EmployeeRowMapper implements RowMapper<Employee> {
        public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
            Employee employee = new Employee();
            employee.setId(rs.getInt("id"));
            employee.setName(rs.getString("name"));
            employee.setLocation(rs.getString("location"));
            return employee;
        }
    }
}

5. Service Layer

@Service
public class EmployeeService {

    @Autowired
    private EmployeeDAO employeeDAO;

    public List<Employee> getEmployeesByLocation(String location) {
        return employeeDAO.findEmployeesByLocation(location);
    }
}

6. Controller

@Controller
@RequestMapping("/employee")
public class EmployeeController {

    @Autowired
    private EmployeeService employeeService;

    @GetMapping("/location")
    public ModelAndView getEmployeesByLocation(@RequestParam String location) {
        List<Employee> employees = employeeService.getEmployeesByLocation(location);
        ModelAndView modelAndView = new ModelAndView("employees");
        modelAndView.addObject("employees", employees);
        return modelAndView;
    }
}

7. JUnit Test

@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@ContextConfiguration(classes = {WebConfig.class})
public class EmployeeControllerTest {

    @Autowired
    private WebApplicationContext wac;

    private MockMvc mockMvc;

    @Before
    public void setup() {
        this.mockMvc = MockMvcBuilders.webAppContextSetup(this.wac).build();
    }

    @Test
    public void testGetEmployeesByLocation() throws Exception {
        mockMvc.perform(get("/employee/location?location=NY"))
               .andExpect(status().isOk())
               .andExpect(view().name("employees"));
    }
}

8. JSP View (`employees.jsp`)

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
    <title>Employees by Location</title>
</head>
<body>
    <h1>Employees in ${location}</h1>
    <table>
        <tr>
            <th>ID</th>
            <th>Name</th>
        </tr>
        <c:forEach items="${employees}" var="employee">
            <tr>
                <td>${employee.id}</td>
                <td>${employee.name}</td>
            </tr>
        </c:forEach>
    </table>
</body>
</html>

This setup provides a basic structure to find employees by location using Spring MVC, MySQL, and JUnit. Adjustments may be necessary based on the specific requirements and configurations of your application.

JUnit Testing in Spring MVC with MySQL:

Description: This example focuses on writing JUnit tests for Spring MVC components, specifically testing functionality that involves MySQL database interactions.

Code Snippet: (JUnit Test)

@RunWith(SpringRunner.class)
@ContextConfiguration(classes = { AppConfig.class, PersistenceConfig.class })
@Transactional
public class UserControllerTest {

    @Autowired
    private UserController userController;

    @Autowired
    private UserService userService;

    @Autowired
    private UserRepository userRepository;

    @Test
    public void testGetUserById() {
        // Perform JUnit test for getUserById method
    }

    // Other test methods...
}