Een Todo application with Spring MVC and JDBC template

In this web application we will use spring mvc and spring jdbc template as technologies

   jsp-servlet 
     + src
     | + main
     | | + java 
     | |   + com
     | |     + litteworld
     | |       + web
     | |         + model
     | |            - Todo.java
     | |         + controllers
     | |            - TodoController.java
     | |         + services
     | |            - JDBCTemplateTodoService.java
     | + webapp
     |   - todoForm.jsp
     |   - todoList.jsp
     |   + WEB-INF
     |     - web.xml 
     |     - spring-context.xml 
     - pom.xml
     - create.sql

Maven POM

In the POM we define the library dependencies of this project

The dependencies are:

spring-core
spring-webmvc
spring-jdbc
mysql-connector
servlet
jstl

We will use the jetty plugin


<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>com.littleworld</groupId>
  <artifactId>spring-template</artifactId>
  <packaging>war</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>spring3 jsp</name>

  <properties>
    <jdk.version>1.8</jdk.version>
    <jstl.version>1.2</jstl.version>
    <servlet.version>3.1.0</servlet.version>
    <springframework.version>4.2.3.RELEASE</springframework.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>jstl</artifactId>
      <version>${jstl.version}</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>${servlet.version}</version>
      <scope>provided</scope>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>${springframework.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>${springframework.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>${springframework.version}</version>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.37</version>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.3</version>
        <configuration>
          <source>${jdk.version}</source>
          <target>${jdk.version}</target>
        </configuration>
      </plugin>

      <plugin>
        <groupId>org.eclipse.jetty</groupId>
        <artifactId>jetty-maven-plugin</artifactId>
        <version>9.3.6.v20151106</version>
        <configuration>
          <scanIntervalSeconds>10</scanIntervalSeconds>
          <webApp>
            <contextPath>/todo</contextPath>
          </webApp>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

Spring configuration

We will use Spring MVC. Spring MVC uses a DispatcherServlet as central processing unit.
The spring context is responsible for the lifecycle management of the components in our application
Our spring context is stored in the file spring-context.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">
    <servlet>
      <servlet-name>appServlet</servlet-name>
      <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
      <init-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>/WEB-INF/spring-context.xml</param-value>
      </init-param>
      <load-on-startup>1</load-on-startup>
    </servlet>
      
    <servlet-mapping>
      <servlet-name>appServlet</servlet-name>
      <url-pattern>/</url-pattern>
    </servlet-mapping>
</web-app>

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/mvc"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xmlns:beans="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

	<!-- Enables the Spring MVC @Controller programming model -->
	<annotation-driven />

	<!-- Handles HTTP GET requests for /resources/** by efficiently serving 
		up static resources in the $webappRoot/resources directory -->
	<resources mapping="/resources/**" location="/resources/" />

	<!-- Resolves views selected for rendering by @Controllers to .jsp resources 
		in the /WEB-INF/views directory -->
	<beans:bean
		class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<beans:property name="prefix" value="/WEB-INF/views/" />
		<beans:property name="suffix" value=".jsp" />
	</beans:bean>

	<beans:bean name="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<beans:property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<beans:property name="url" value="jdbc:mysql://localhost:3306/todo" />
                <beans:property name="username" value="root" />
                <beans:property name="password" value="mysql" />
	</beans:bean>

	<beans:bean id="todoService" class="com.littleworld.web.services.JDBCTemplateTodoService" />

	<context:component-scan base-package="com.littleworld.web" />

</beans:beans>

Model

This project uses the todo as model
The fields in the model will be used in the views form and list.
Apart from the id field.


/* generated by: ControllerGenerator Sun Nov 29 21:06:57 CET 2015 */
package com.littleworld.web.model;

public class Todo {
  int id;

  String task;


  public Todo() {}

  public Todo(int id, String task) {
    this.id = id;
    this.task = task;
  }

  public int getId() {
    return id;
  }

  public void setId(int id)  {
    this.id = id;
  } 

  public String getTask() {
    return task;
  }

  public void setTask(String task)  {
    this.task = task;
  } 
}

Controllers

The controller contains the CRUD methods: create, findAll, findById, Update and Delete


/* generated by: ControllerGenerator Sun Nov 29 21:06:57 CET 2015 */
package com.littleworld.web.controllers;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import com.littleworld.web.services.*;
import com.littleworld.web.model.*;

@Controller
public class TodoController {

  @Autowired  
  private JDBCTemplateTodoService todoService;

  @ModelAttribute("todoForm")
  public Todo todoForm() {
    return new Todo();
  }

  @RequestMapping(value = "/create", method = RequestMethod.POST)
  public String create(@ModelAttribute("todoForm") Todo todo) {
    todoService.create(todo);
    return "redirect:/todos";
  }

  @RequestMapping(value = "/todos", method = RequestMethod.GET)
  public String findAll(Model model) {
    model.addAttribute("todos", todoService.findAll());
    return "todoList";
  }

  @RequestMapping(value = "/find/{id}", method = RequestMethod.GET)
  public String todoById(@PathVariable int id, Model model) {
    model.addAttribute("todo", todoService.findById(id));
     return "todoView";
  }

  @RequestMapping(value = "/delete/{id}", method = RequestMethod.GET)
  public String delete(@PathVariable int id) {
    todoService.delete(id);
    return "redirect:/todos";
  }
}

Services

The service layer

JDBCTemplateTodoService contains the CRUD methodes supporting that are called from the controller In the JDBCTemplateTodoService the todos are presisted in a mysql database

create will add a todo
findAll will returns all the todos
delete will delete a todo


package com.littleworld.web.services;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.littleworld.web.model.Todo;

@Transactional
@Repository
public class JDBCTemplateTodoService  {

    JdbcTemplate jdbcTemplate;
    
    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public void createTable() {
      jdbcTemplate.execute("drop table todo");

      jdbcTemplate.execute("create table todo ( id integer primary key auto_increment, task varchar(255));");
    }

    public Todo create(Todo todo ) {
      jdbcTemplate.update("INSERT INTO todo(id, task) VALUES (?, ?)",
          new Object[] { todo.getId(), todo.getTask() });   
      return todo;    
    }

    public List<Todo> findAll() {
        List<Todo> list =  jdbcTemplate.query(
            "SELECT id, task FROM todo",
            new RowMapper<Todo>() {      
              public Todo mapRow(ResultSet rs, int rowNum) throws SQLException {
                Todo todo = new Todo();
                todo.setId(rs.getInt("id")); 
                todo.setTask(rs.getString("task")); 
                return todo;
              }                
            //(rs, rowNum) -> new Todo(rs.getInt("id") , rs.getString("task")  )
        });
        return list;
    }
 
    public Todo findById(int id) {
        List<Todo> list =  jdbcTemplate.query(
            "SELECT id, task FROM todo WHERE id = ?", new Object[]{ id },
            new RowMapper<Todo>() {
              public Todo mapRow(ResultSet rs, int rowNum) throws SQLException {
                Todo todo = new Todo();
                todo.setId(rs.getInt("id")); 
                todo.setTask(rs.getString("task")); 
                return todo;
              }
            //(rs, rowNum) -> new Todo(rs.getInt("id") , rs.getString("task")  )
        });
        return list.get(0);
    }

    public void delete(int id) {
       jdbcTemplate.update("DELETE FROM todo where id = ? ", id);
    }

    public Todo updateTodo(Todo todo) {
      jdbcTemplate.update("UPDATE todo SET todo(id= ?, task= ? WHERE id = ?" , 
          new Object[] {todo.getId(), todo.getTask(), todo.getId()});
      return todo;   
}
}

the create table script

create table todo (
  id integer primary key auto_increment,
  task varchar(255)
);

The HTML View

HTML List

The list page act as the landing page

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html>
<html>
<head>
  <title>todo list</title>
</head>
<body>
  <jsp:include page="todoForm.jsp" />
  <hr>
  <table>
    <tr><th>task</th></tr>
 
    <c:forEach items="${todos}" var="todo">
    <tr><td>${todo.task}</td><td><a href="delete/${todo.id}">delete</a></tr>
    </c:forEach>  

  </table>
</body>
</html>

The HTML form

<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form" %>

<form:form action="create" method="post" modelAttribute="todoForm">
  <table>
    <tr><td>task:</td><td><form:input type='text' path='task'/></td></tr>
    <tr><td><button type="submit">OK</button></td><td></td></tr>
  </table>
</form:form>

Run the application

To run the application. Goto the root directory of the application and type

(Maven should in the enviroment variable PATH; and JAVA_HOME should be set to the jdk installation dir)

mvn jetty:run

localhost:8080/todo/list