Spring MVC and Spring JDBC Transaction Tutorial with Examples

Instroduction
Script to create tables
Create Maven Project
Configure Maven & web.xml
datasource-cfg.properties
Configure Spring MVC
Java Classes
Views
Run Application

See more:
Spring Boot, Spring JDBC and Spring Transaction

1. Instroduction

The document is based on:

Eclipse 4.6 (NEON)
Spring MVC 4
Spring JDBC

2. Script to create tables

ORACLE:

create table DEPARTMENT (
      DEPT_ID number(10,0) not null,
      DEPT_NAME varchar2(255 char) not null,
      DEPT_NO varchar2(20 char) not null unique,
      LOCATION varchar2(255 char),
      primary key (DEPT_ID)
  );

MySQL:

create table DEPARTMENT (
  DEPT_ID integer not null,
  DEPT_NAME varchar(255) not null,
  DEPT_NO varchar(20) not null,
  LOCATION varchar(255),
  primary key (DEPT_ID),
  unique (DEPT_NO)
);

SQLServer:

Create table DEPARTMENT (
   DEPT_ID int not null,
   DEPT_NAME varchar(255) not null,
   DEPT_NO varchar(20) not null,
   LOCATION varchar(255),
   primary key (DEPT_ID),
   unique (DEPT_NO)
);

3. Create Maven Project

File/New/Other..

Enter:

Group Id: org.o7planning
Artifact Id: SpringMVCJdbcTransaction
Package: org.o7planning.tutorial.springmvcjdbc

Project was created:

Do not worry with the error message when Project has been created. The reason is that you have not declared Servlet library.

Using Java >= 6.

4. Configure Maven & web.xml

Using Servlet >= 3.

web.xml

<web-app xmlns="http://java.sun.com/xml/ns/javaee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
          http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
    version="3.0">
 
    <display-name>SpringMVCJdbcTransaction Web Application</display-name>
 
</web-app>

In pom.xml, I declared JDBC libaries to 3 types of database Oracle, MySQL and SQL Server.

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
    http://maven.apache.org/maven-v4_0_0.xsd">
    
    <modelVersion>4.0.0</modelVersion>
    <groupId>org.o7planning</groupId>
    <artifactId>SpringMVCJdbcTransaction</artifactId>
    <packaging>war</packaging>
    <version>1.0.0</version>
    <name>SpringMVCJdbcTransaction Maven Webapp</name>
    <url>http://maven.apache.org</url>

    <properties>
        <java-version>1.7</java-version>
    </properties>

    <repositories>
        <!-- Repository for ORACLE JDBC Driver -->
        <repository>
            <id>codelds</id>
            <url>https://code.lds.org/nexus/content/groups/main-repo</url>
        </repository>
    </repositories>

    <dependencies>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>

        <!-- Servlet API -->
        <!-- http://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
            <scope>provided</scope>
        </dependency>

        <!-- Jstl for jsp page -->
        <!-- http://mvnrepository.com/artifact/javax.servlet/jstl -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>


        <!-- JSP API -->
        <!-- http://mvnrepository.com/artifact/javax.servlet.jsp/jsp-api -->
        <dependency>
            <groupId>javax.servlet.jsp</groupId>
            <artifactId>jsp-api</artifactId>
            <version>2.2</version>
            <scope>provided</scope>
        </dependency>

        <!-- Spring dependencies -->
        <!-- http://mvnrepository.com/artifact/org.springframework/spring-core -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>4.3.3.RELEASE</version>
        </dependency>

        <!-- http://mvnrepository.com/artifact/org.springframework/spring-web -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>4.3.3.RELEASE</version>
        </dependency>

        <!-- http://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>4.3.3.RELEASE</version>
        </dependency>


        <!-- http://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.3.3.RELEASE</version>
        </dependency>

        <!-- http://mvnrepository.com/artifact/org.springframework/spring-tx -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>4.3.3.RELEASE</version>
        </dependency>

        <!-- MySQL JDBC driver -->
        <!-- http://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.4</version>
        </dependency>

        <!-- Oracle JDBC driver -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>

        <!-- SQLServer JDBC driver (JTDS) -->
        <!-- http://mvnrepository.com/artifact/net.sourceforge.jtds/jtds -->
        <dependency>
            <groupId>net.sourceforge.jtds</groupId>
            <artifactId>jtds</artifactId>
            <version>1.3.1</version>
        </dependency>


    </dependencies>


    <build>
        <finalName>SpringMVCJdbcTransaction</finalName>
        <plugins>
        
            <!-- Config: Maven Tomcat Plugin -->
            <!-- http://mvnrepository.com/artifact/org.apache.tomcat.maven/tomcat7-maven-plugin -->
            <plugin>
                <groupId>org.apache.tomcat.maven</groupId>
                <artifactId>tomcat7-maven-plugin</artifactId>
                <version>2.2</version>
                <!-- Config: contextPath and Port (Default - /SpringMVCJdbcTransaction,8080) -->
                <!--
                <configuration>
                    <path>/</path>
                    <port>8899</port>
                </configuration>
                -->    
            </plugin>
        </plugins>
    </build>    

</project>

5. datasource-cfg.properties

In this tutorial, 3 types of different databases as Oracle, MySQL and SQL Server will be supported, you can configure file datasource-cfg.xml by one of the following samples:

datasource-cfg.properties (ORACLE)

# DataSource (ORACLE)

ds.database-driver=oracle.jdbc.driver.OracleDriver
ds.url=jdbc:oracle:thin:@localhost:1521:db12c
ds.username=simplehr
ds.password=12345

datasource-cfg.properties (MySQL)

# DataSource

ds.database-driver=com.mysql.jdbc.Driver
ds.url=jdbc:mysql://localhost:3306/mydatabase
ds.username=root
ds.password=12345

datasource-cfg.properties (SQL Server)

# DataSource

ds.database-driver=net.sourceforge.jtds.jdbc.Driver
ds.url=jdbc:jtds:sqlserver://localhost:1433/simplehr;instance=SQLEXPRESS
ds.username=shoppingcart
ds.password=12345

6. Configure Spring MVC

SpringWebAppInitializer.java

package org.o7planning.springmvcjdbc.config;

import javax.servlet.FilterRegistration;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletRegistration;

import org.springframework.web.WebApplicationInitializer;
import org.springframework.web.context.support.AnnotationConfigWebApplicationContext;
import org.springframework.web.filter.CharacterEncodingFilter;
import org.springframework.web.servlet.DispatcherServlet;

public class SpringWebAppInitializer implements WebApplicationInitializer {

    @Override
    public void onStartup(ServletContext servletContext) throws ServletException {
        AnnotationConfigWebApplicationContext appContext = new AnnotationConfigWebApplicationContext();
        appContext.register(ApplicationContextConfig.class);

        ServletRegistration.Dynamic dispatcher = servletContext.addServlet("SpringDispatcher",
                new DispatcherServlet(appContext));
        dispatcher.setLoadOnStartup(1);
        dispatcher.addMapping("/");

        // UtF8 Charactor Filter.
        FilterRegistration.Dynamic fr = servletContext.addFilter("encodingFilter", CharacterEncodingFilter.class);

        fr.setInitParameter("encoding", "UTF-8");
        fr.setInitParameter("forceEncoding", "true");
        fr.addMappingForUrlPatterns(null, true, "/*");
    }

}

WebMvcConfig.java

package org.o7planning.springmvcjdbc.config;

import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.DefaultServletHandlerConfigurer;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;

@Configuration
@EnableWebMvc
public class WebMvcConfig extends WebMvcConfigurerAdapter {

 
    @Override
    public void addResourceHandlers(ResourceHandlerRegistry registry) {

        // Default..
    }

    @Override
    public void configureDefaultServletHandling(DefaultServletHandlerConfigurer configurer) {
        configurer.enable();
    }

}

In ApplicationContextConfig you need to declare 2 Spring BEAN is dataSource and transactionManager.

dataSource BEAN will load the database information in ds/datasouce-cfg.properties.

ApplicationContextConfig.java

package org.o7planning.springmvcjdbc.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.context.annotation.PropertySources;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.web.servlet.view.InternalResourceViewResolver;

@Configuration
@ComponentScan("org.o7planning.springmvcjdbc.*")

@EnableTransactionManagement

// Load to Environment.
@PropertySources({ @PropertySource("classpath:ds/datasource-cfg.properties") })

public class ApplicationContextConfig {

   // The Environment class serves as the property holder
   // and stores all the properties loaded by the @PropertySource
   @Autowired
   private Environment env;

   @Bean(name = "viewResolver")
   public InternalResourceViewResolver getViewResolver() {
       InternalResourceViewResolver viewResolver = new InternalResourceViewResolver();

       viewResolver.setPrefix("/WEB-INF/pages/");
       viewResolver.setSuffix(".jsp");

       return viewResolver;
   }

   @Bean(name = "dataSource")
   public DataSource getDataSource() {
       DriverManagerDataSource dataSource = new DriverManagerDataSource();

       // See: datasouce-cfg.properties
       dataSource.setDriverClassName(env.getProperty("ds.database-driver"));
       dataSource.setUrl(env.getProperty("ds.url"));
       dataSource.setUsername(env.getProperty("ds.username"));
       dataSource.setPassword(env.getProperty("ds.password"));

       System.out.println("## getDataSource: " + dataSource);

       return dataSource;
   }

   @Bean(name = "transactionManager")
   public DataSourceTransactionManager getTransactionManager() {
       DataSourceTransactionManager txManager = new DataSourceTransactionManager();

       DataSource dataSource = this.getDataSource();
       txManager.setDataSource(dataSource);

       return txManager;
   }

}

7. Java Classes

Department.java

package org.o7planning.springmvcjdbc.model;

public class Department {

    private Integer deptId;
    private String deptNo;
    private String deptName;
    private String location;

    public Department() {

    }

    public Department(Integer deptId, String deptNo, String deptName,
            String location) {
        this.deptId = deptId;
        this.deptNo = deptNo;
        this.deptName = deptName;
        this.location = location;
    }

    public Integer getDeptId() {
        return deptId;
    }

    public void setDeptId(Integer deptId) {
        this.deptId = deptId;
    }

    public String getDeptNo() {
        return deptNo;
    }

    public void setDeptNo(String deptNo) {
        this.deptNo = deptNo;
    }

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

    public String getLocation() {
        return location;
    }

    public void setLocation(String location) {
        this.location = location;
    }
}

DepartmentMapper.java

package org.o7planning.springmvcjdbc.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.o7planning.springmvcjdbc.model.Department;
import org.springframework.jdbc.core.RowMapper;

public class DepartmentMapper implements RowMapper<Department> {

    public static final String BASE_SQL = //
            "Select d.dept_id,d.dept_no,d.dept_name,d.location "//
                    + " from Department d ";

    @Override
    public Department mapRow(ResultSet rs, int rowNum) throws SQLException {
        Integer deptId = rs.getInt("dept_id");
        String deptNo = rs.getString("dept_no");
        String deptName = rs.getString("dept_name");
        String location = rs.getString("location");

        return new Department(deptId, deptNo, deptName, location);
    }

}

@Transactional can annotate on the method, or on the class, it will work on all methods of class.

DepartmentDAO.java

package org.o7planning.springmvcjdbc.dao;

import java.util.List;

import javax.sql.DataSource;

import org.o7planning.springmvcjdbc.mapper.DepartmentMapper;
import org.o7planning.springmvcjdbc.model.Department;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
@Transactional
public class DepartmentDAO extends JdbcDaoSupport {

    @Autowired
    public DepartmentDAO(DataSource dataSource) {
        this.setDataSource(dataSource);
    }

    private int getMaxDeptId() {
        String sql = "Select max(d.dept_id) from Department d";

        Integer value = this.getJdbcTemplate().queryForObject(sql, Integer.class);
        if (value == null) {
            return 0;
        }
        return value;
    }

    public Department findDepartment(String deptNo) {
        String sql = DepartmentMapper.BASE_SQL //
                + " where d.dept_no = ?";

        Object[] params = new Object[] { deptNo };
        
        DepartmentMapper mapper = new DepartmentMapper();

        Department dept = this.getJdbcTemplate().queryForObject(sql, params, mapper);
        return dept;
    }

    public List<Department> listDepartment() {
        String sql = DepartmentMapper.BASE_SQL;

        Object[] params = new Object[] {};
        DepartmentMapper mapper = new DepartmentMapper();

        List<Department> list = this.getJdbcTemplate().query(sql, params, mapper);
        return list;
    }

    public void insertDepartment(String deptName, String location) {
        String sql = "Insert into Department (dept_id,dept_no,dept_name,location) "//
                + " values (?,?,?,?) ";
        int deptId = this.getMaxDeptId() + 1;
        String deptNo = "D" + deptId;
        Object[] params = new Object[] { deptId, deptNo, deptName, location };
        this.getJdbcTemplate().update(sql, params);
    }

}

Controller:

MainController.java

package org.o7planning.springmvcjdbc.controller;

import java.util.List;

import org.o7planning.springmvcjdbc.dao.DepartmentDAO;
import org.o7planning.springmvcjdbc.model.Department;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

@Controller
public class MainController {

    @Autowired
    private DepartmentDAO departmentDAO;

    @RequestMapping(value = { "/" }, method = RequestMethod.GET)
    public String welcomePage(Model model) {
        departmentDAO.insertDepartment("HR", "Chicago");
        departmentDAO.insertDepartment("INV", "Hanoi");
        List<Department> list = departmentDAO.listDepartment();
        model.addAttribute("departments", list);
        return "index";
    }
    
}

8. Views

index.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
 
 <!DOCTYPE html>
 <html>
  <head>
      <meta charset="UTF-8">
      <title>Department List</title>
  </head>
  <body>
      <div align="center">
          <h1>Department List</h1>
          <table border="1">
              <tr>
                <th>No</th>
                <th>Dept No</th>
                <th>Dept Name</th>
                <th>Location</th>
              </tr>
              <c:forEach var="dept" items="${departments}" varStatus="status">
              <tr>
                  <td>${status.index + 1}</td>
                  <td>${dept.deptNo}</td>
                  <td>${dept.deptName}</td>
                  <td>${dept.location}</td>                            
              </tr>
              </c:forEach>                
          </table>
      </div>
  </body>
</html>