16 - Spring JDBC - Exercise

Task

1. Create a new project using Spring Initializr https://start.spring.io/ . In the dependencies section on the right hand side select JDBC API and MySQL Driver. Name your project SpringDB.

2. Download your project and unzip it into your IdeaProjects folder. Open the project in your IntelliJ. Run Maven install if necessary.

3. Open application.properties and type in your database address and credentials (make sure you provide the correct password for your database!)

   spring.datasource.url=jdbc:mysql://127.0.0.1:3306/employee_sys spring.datasource.username=root spring.datasource.password= spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver

4. Create a java class that will match one of your tables. For example, for this SQL table we would create a class called Department with three private attributes - int dep_id, String dep_name and int team_id. Provide setters for all three attributes and generate toString method.

   Open

   

5. Create a new class called DepartmentRepository. Annotate it with @Repository. Inside the class, autowire a variable of type JdbcTemplate.

   @Autowired JdbcTemplate jdbcTemplate;

6. Create an inner class DepartmentRowMapper that implements the RowMapper<Department> interface. It needs to provide one method mapRow that takes two parameters - one of type ResultSet, one int for row number. It can throw SQLException. Within the body of the method provide the mapping between a row in the table and an object of type Department.

   private class DepartmentRowMapper implements RowMapper<Department> { public Department mapRow(ResultSet rs, int rowNum) throws SQLException{ Department dept = new Department(); dept.setDep_id(rs.getInt("dep_id")); dept.setDep_name(rs.getString("dep_name")); dept.setTeam_id(rs.getInt("team_id")); return dept; } }

7. Within the body of the DepartmentRepository, create a public method that will return a list of Department type objects. The method does not need parameters. Inside the body of the method, use the jdbcTemplate 's query method, pass in your SQL query and an instance of the DepartmentRowMapper class. Return the list of departments.

8. Make your SpringDBApplication class implement the CommandLineRunner interface. Provide the run method.

9. Autowire your DepartmentRepository class into the SpringDBApplication. Within the run method, invoke the findAllDepartments() method created on the repository and print out all the departments.