Spring MVC with JdbcTemplate

Spring MVC with JdbcTemplate
February 15, 2017 No Comments Uncategorized Vikram Patil

Spring JdbcTemplate is a technique to connect the database and execute SQL queries. Spring framework use JdbcTemplate class to simplifies the JDBC development. This class helps to developer to avoid common errors , reduce boilerplate code , connect the database, execute SQL queries ,extract result set and close the connection. It provides methods to write the queries directly, so it saves a lot of work and time. It also catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy.

Steps for creating Spring MVC application with JdbcTemplate :

  • MySql Database:
    Execute the following MySql script to create test database and employee table in your MySql.
    db
  • Create Maven Project:
    Using the spring-mvc-archetype create the Spring project in Eclipse. The Final structure of project as follows.

structure

  • Configuring Maven:
    Add the following dependencies in your pom.xml file configure the Maven project.

pom

  • Model class:
    We will use DAO Pattern for JDBC operations, so let’s create a java bean that will model our Employee table. The Code a JavaBean class Employee.java as follows:

model

This class contains 4 properties with constructors and setter and getters.

  • DAO Interface and Implementation :
    The EmployeeDAO.java interface defines methods for performing operations on the employee table.

dao

In above code saveOrUpdate(), getEmployee(), deleteEmployee() and getById() these are 4 methods defines.

The EmployeeDaoImpl.java class implements the methods which are defined in the EmployeeDao interface.

impl1
impl2
impl3

In above code EmployeeDaoImpl class implements the EmployeeDao interface using the implements keyword. Using this technique we can implements all methods of EmployeeDao interface.

saveOrUpdate() method use for insert and update operations. If the employee object having id greater than zero then update operation will execute otherwise insert operation will execute.

getEmployee() method select the all records from employee table and using RowMapper map a rows in the result set to the emp which is object of Employee class.

deleteEmployee() method get the employee id and delete the record from employee table of this id.

getById() method get the employee id and get the record from employee table. Using ResultSetExtractor extract a single row and set to the employee object.

Controller:
EmployeeController.java is the controller of this application. Controller contains the following code:
controller1
controller2

In above code EmployeeController class declares as a Controller using the @Controller annotation.

@Autowired annotation let inject an instance of EmployeeDao implementation into this controller automatically.

@RequestMapping annotation maps web requests onto specific handler methods.

Configure web.xml :
web

This file declare DispatcherServlet class for configuration of Spring MVC-based application and ContextLoaderListener along with the Spring configuration files dispatcher-servlet.xml.

Configure dispatcher-servlet.xml:

dispatcher

In this file components-scan defines the base package of application and next bean will resolve the view and add prefix string /WEB_INF/views/ and suffix .jsp to the view in ModelAndView.

The dataSource bean defines the database connection with driverClassName, url, username, password. You may have to change the database URL, username and password according to your environments.

The employeeDao bean returns an implementation of the EmployeeDAO interface, which is the EmployeeDaoImpl class. This bean will be injected to the controller class.sss

Index.jsp:
index

Index page contains two links New Employee and View Employee.

addEmployee.jsp:

addemp

This form page use for add new employee or update old employee.
This jsp page uses Spring form tags to bind the values of the form to a model object.

viewEmployee.jsp:
viewemp

This page display the employee list as well as action links edit and delete employee.

Run on Server:
Right click the project and select Run As –> Run on Server.
output1

Click on New Employee link then below New/Edit form will be display.

output2

Click on Viw Employee link then below Employee List will be display.

output3

Using Edit or Delete link you can edit or delete the record.

About The Author

Leave a reply

Your email address will not be published. Required fields are marked *