It is always the best practice to design the database and table schemas using an ERD model. After finalizing our schema designs, follow these steps to build our DAO layer:

1. Let us open a MySQL workbench or a command line terminal, log in using the username root and password spring5mysql, and create the following schema definition of the hrs database:
   
2. Since we will be dealing with database models, create a package, org.packt.dissect.mvc.model.data, that will contain data models representing the schema of the preceding tables. Given the two tables, create the Employee and Department data models found here:

public class Department { 
 
  private Integer id; 
  private Integer deptId; 
  private String name; 
 
// getters and setters 
} 
 
public class Employee { 
 
  private Integer id; 
  private Integer empId; 
  private String firstName; 
  private String lastName; 
  private Integer age; 
  private String email; 
  private Date birthday; 
  private Integer deptId; 
 
// getters and setters 
} 

3. We need to update the URL details of our jdbc.properties since we will be accessing our newly created schema, hrs:

jdbc.driverClassName=com.mysql.jdbc.Driver 
jdbc.url=jdbc:mysql://localhost:3306/hrs?autoReconnect=true 
&useSSL=true&serverSslCert=classpath:config/spring5packt.crt 
jdbc.username=root 
jdbc.password=spring5mysql 

4. Now, inject org.springframework.jdbc.core.simple.SimpleJdbcInsert in the SpringDbConfig context. This Spring JDBC API manages the JDBC transactions:

@Bean 
public SimpleJdbcInsert jdbcInsert() 
  throws PropertyVetoException { 
     return new SimpleJdbcInsert(dataSource()); 
}

5. This implementation for the data access template and callback fits with Spring 5.0 because of its multithreading characteristics, while using the low-level JdbcTemplate. SimpleJdbcInsert is easier to configure than JdbcTemplate and can easily work with database configurations without so many unnecessary metadata. Now, create the Data Access Object (DAO) layer, which uses the two data models in exposing the database transactions. This layer is composed of interfaces (such as DepartmentDao), which are used to fetch their implementations (such as DepartmentDaoImpl) once needed by the @Controller in saving, retrieving, updating, and deleting records. The following is the Department interface and its implementation class DepartmentDaoImpl:

package org.packt.dissect.mvc.dao; 
 
public interface DepartmentDao { 
  public List<Department> getDepartments(); 
  public Department getDepartmentData(Integer id); 
  public void addDepartmentBySJI(Department dept); 
  public void addDepartmentByJT(Department dept); 
  public void updateDepartment(Department dept); 
  public void delDepartment(Integer deptId); 
} 
 
package org.packt.dissect.mvc.dao.impl; 
 
@Repository 
public class DepartmentDaoImpl implements DepartmentDao{ 
  
  @Autowired 
  private SimpleJdbcInsert jdbcInsert; 
 
  @Override 
  public List<Department> getDepartments() { 
    String sql = "SELECT * FROM department"; 
    List<Department> depts = 
       jdbcInsert.getJdbcTemplate().query(sql,  
       new RowMapper<Department>() { 
 
      @Override 
      public Department mapRow(ResultSet rs, 
        int rowNum) throws SQLException { 
 
         Department dept = new Department(); 
         dept.setId(rs.getInt("id")); 
         dept.setDeptId(rs.getInt("deptId")); 
         dept.setName(rs.getString("name")); 
         return dept; 
      } 
    }); 
    return depts; 
  } 
  // refer to sources 
} 

All DAO implementation classes must have a @Repository annotation which tells Spring that these classes are valid @Bean and classified persistence layer classes, triggering some special exceptions and code translators at runtime.

6. Create a TestDepartmentDao class in src estjava to perform initial testing without injecting them yet to the @Controller for request-response transactions:

@RunWith(SpringJUnit4ClassRunner.class) 
@WebAppConfiguration 
@ContextConfiguration(classes = { SpringDbConfig.class,  
    SpringDispatcherConfig.class }) 
public class TestDepartmentDao { 
 
  @Autowired 
  private DepartmentDao departmentDaoImpl; 
  
  @Test 
  public void testDetachedDepartment(){ 
    Department rec = new Department(); 
    rec.setDeptId(9999); 
    rec.setName("Security Department"); 
    departmentDaoImpl.addDepartmentBySJI(rec); 
  } 
  
  @Test 
  public void testPopulateDepartment(){ 
    Department rec1 = new Department(); 
    rec1.setDeptId(1); 
    rec1.setName("Engineering Department"); 
    
    Department rec2 = new Department(); 
    rec2.setDeptId(2); 
    rec2.setName("Human Resources Department"); 
    
    // refer to sources 
    
    departmentDaoImpl.addDepartmentByJT(rec1); 
    departmentDaoImpl.addDepartmentByJT(rec2); 
 
    // refer to sources 
  } 
// refer to sources 
} 

7. Repeat the preceding processes for EmployeeDao.

8. Save all files and be ready to connect our DAO layer to the @Controller.