SelimSql

Database Management System

Main Page User Guide Download Java Code Android Contact

Spring MVC + JdbcTemplate CRUD Example

By Zülkif Güven | May 1, 2018
In this tutorial, Spring MVC JdbcTemplate operation is explained.

Brief Description of Terminology

JdbcTemplate is the essential class of the JDBC core package. It simplifies the use of JDBC operations. Spring JDBC performs the following actions: JDBC is acronym of Java Database Connectivity.

CRUD is acronym of Create, Retrieve, Update, Delete.


Used Technologies



Project Main View

This sample web application explains the CRUD operations on USER table:




1. Building SelimSQL Database

Build a new database and user table by executing the following script.
For executing script, you can download and install this tool: SelimSQL Database Developer

a) Build a new database using SSqlDeveloper tool:

JDBC Class: selimsql.jdbc.DbDriver
JDBC.Url  : jdbc:selimsql:fileNewDatabase:/MyData/MyDb

Press the "Connect" button.
Enter the user and password of you.

(jdbc.username): admin
(jdbc.password) may be empty!

b) Build a new table and related indexes by executing the following 4 scripts one by one:

Create Table User (
  Id integer NOT NULL,
  Code VARCHAR(20) NOT NULL,
  Name VARCHAR(20) NOT NULL,
  Surname VARCHAR(20) NOT NULL,
  Password VARCHAR(20) NOT NULL,
  Email VARCHAR(50) NOT NULL,
  Phone VARCHAR(20),
  Status SmallInt NOT NULL
);
--Note: Select the script above and press "Sql Execute" button.

Create Unique Index UserPK On User(Id);

Create Unique Index UserCodeUIdx On User(Code);

Create Unique Index UserEmailUIdx On User(Email);

c) Insert a User row example(optional):

Insert into User(Id, Code, Name, Surname, Email, Password, Phone, Status)
Values(1, 'admin', 'Admin', 'Admin', 'admin@test.com', 'admin', null, 1);

d) After the execution of script;

check "src\main\resources\spring\application.properties" named config file in project as follow:
jdbc.driverClassName=selimsql.jdbc.DbDriver

#Database jdbc connection url that you have just built:
jdbc.url=jdbc:selimsql:file:C:/MyData/MyDb
jdbc.username=admin

#Database user password that you have entered:
jdbc.password=[may be empty]


2. Project Maven Dependency

Add spring framework and necessary database dependencies in Maven pom.xml file:
<?xml version="1.0" encoding="UTF-8"?>
<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/xsd/maven-4.0.0.xsd">

   <modelVersion>4.0.0</modelVersion>
   <groupId>com.selimsql.lesson</groupId>
   <artifactId>SpringMvcJdbcTemplate</artifactId>
   <name>SpringMvcJdbcTemplate</name>
   <packaging>war</packaging>
   <version>1.0-SNAPSHOT</version>

     <properties>
      <java.version>1.7</java.version>
      
      <springframework.version>4.0.6.RELEASE</springframework.version>

      <!-- Database:SelimSql -->
      <selimSqlDb.version>1.2.7</selimSqlDb.version>

      <!-- Web -->
      <javax.servlet.version>3.1.0</javax.servlet.version>
      <javax.jsp.version>2.3.1</javax.jsp.version>
      <jstl.version>1.2</jstl.version>
   </properties>

   <dependencies>
      <!-- Spring -->
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-webmvc</artifactId>
         <version>${springframework.version}</version>
      </dependency>
      
      <!-- Spring JdbcTemplate -->
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-orm</artifactId>
         <version>${springframework.version}</version>
      </dependency>

      <!-- Servlet + JSP + JSTL -->
      <dependency>
         <groupId>javax.servlet</groupId>
         <artifactId>javax.servlet-api</artifactId>
         <version>${javax.servlet.version}</version>
      </dependency>
      <dependency>
         <groupId>javax.servlet.jsp</groupId>
         <artifactId>javax.servlet.jsp-api</artifactId>
         <version>${javax.jsp.version}</version>
      </dependency>
      <dependency>
          <groupId>javax.servlet</groupId>
          <artifactId>jstl</artifactId>
          <version>${jstl.version}</version>
      </dependency>

      <!-- jsr303 validation -->
      <dependency>
         <groupId>javax.validation</groupId>
         <artifactId>validation-api</artifactId>
         <version>1.1.0.Final</version>
      </dependency>

      <!-- BasicDataSource -->
      <dependency>
         <groupId>commons-dbcp</groupId>
         <artifactId>commons-dbcp</artifactId>
         <version>1.4</version>
      </dependency>
      
      <!-- SelimSql Database Driver -->
      <dependency>
         <groupId>selimSql</groupId>
         <artifactId>selimSql</artifactId>
         <version>${selimSqlDb.version}</version>
      </dependency>
   </dependencies>
   
   <!-- For SelimSql Database Driver -->
   <repositories>
      <repository>
         <id>com.selimsql.database.driver.release</id>
         <name>SelimSql Database Driver Repository</name>
         <url>http://www.selimsql.com/release</url>
      </repository>
   </repositories>


   <build>
      <finalName>SpringMvcJdbcTemplate</finalName>
      <pluginManagement>
         <plugins>
            <plugin>
                 <groupId>org.apache.maven.plugins</groupId>
                 <artifactId>maven-compiler-plugin</artifactId>
                 <version>3.0</version>
                 <configuration>
                     <source>${java.version}</source>
                     <target>${java.version}</target>
                       <showWarnings>true</showWarnings>
                 </configuration>
             </plugin>

            <plugin>
               <groupId>org.apache.maven.plugins</groupId>
               <artifactId>maven-war-plugin</artifactId>
               <version>2.3</version>
               <configuration>
                  <failOnMissingWebXml>false</failOnMissingWebXml>
               </configuration>
            </plugin>
         </plugins>
      </pluginManagement>
   </build>
</project>



3. Table Row Data Model

Add a User model to record user’s data:
package com.selimsql.lesson.domain;

import java.io.Serializable;

public class User implements Serializable {
   private Integer id;
   private String code;
   private String name;
   private String surname;
   private String password;
   private String email;
   private String phone;
   private Integer status;

   //Getter and Setter methods..
}


4. Data Access Object(DAO) Layer

User DAO interface class:
package com.selimsql.lesson.dao;

import java.util.List;

import com.selimsql.lesson.domain.User;
import com.selimsql.lesson.dto.UserDTO;

public interface UserDao {

	List<User> queryUserList(UserDTO userDTO);

	User findById(Integer id);

	User findByCode(String code);

	User findByEmail(String email);

	int fetchMaxId();

	int insertRow(User row);

	int updateRow(User row);

	int deleteRow(User row);
}


User Dao implementation class for JDBC CRUD operations:
package com.selimsql.lesson.dao;

import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.selimsql.lesson.domain.User;
import com.selimsql.lesson.dto.UserDTO;
import com.selimsql.lesson.util.Util;

@Repository("userDao")
public class UserDaoImpl implements UserDao {

  private String ENTITY_TABLE_NAME;

  private JdbcTemplate jdbcTemplate;


  @Autowired
  public UserDaoImpl(DataSource dataSource) {
    this.ENTITY_TABLE_NAME = "User";
    this.jdbcTemplate = new JdbcTemplate(dataSource);
  }

  @Override
  public List<User> queryUserList(UserDTO userDTO) {
    String sql = "Select * from " + ENTITY_TABLE_NAME;

    String whereStr = "";
    String code = userDTO.getCode();
    List<Object> listPrm = new ArrayList<Object>();
    if (StringUtils.hasLength(code)) {
      whereStr = " and Code like ?";
      listPrm.add(code + "%");
    }

    String name = userDTO.getName();
    if (StringUtils.hasLength(name)) {
      whereStr += " and Name like ?";
      listPrm.add(name + "%");
    }

    String surname = userDTO.getSurname();
    if (StringUtils.hasLength(surname)) {
      whereStr += " and Surname like ?";
      listPrm.add(surname + "%");
    }

    if (StringUtils.hasLength(whereStr)) {
      sql += " Where " + whereStr.substring(5);
    }

    Object[] params = listPrm.toArray();

    sql += " Order by Id";

    RowMapper<User> rowMapper = BeanPropertyRowMapper.newInstance(User.class);
    RowMapperResultSetExtractor<User> rowMapperResultSetExtractor = new RowMapperResultSetExtractor<User>(rowMapper);
    List<User> list = jdbcTemplate.query(sql, params, rowMapperResultSetExtractor);
    return list;
  }

  @Override
  public User findById(Integer id) {
    String sql = "Select * from " + ENTITY_TABLE_NAME + " Where id = ?";
    RowMapper<User> rowMapper = BeanPropertyRowMapper.newInstance(User.class);

    List<User> list = jdbcTemplate.query(sql, new Object[]{id}, new RowMapperResultSetExtractor<User>(rowMapper, 1));
    int count = (list==null ? 0 : list.size());
    if (count==0)
      return null;

    return list.get(0);
  }

  @Override
  public User findByCode(String code) {
    String sql = "Select * from " + ENTITY_TABLE_NAME + " Where Code = ?";

    RowMapper<User> rowMapper = BeanPropertyRowMapper.newInstance(User.class);
    List<User> list = jdbcTemplate.query(sql, new Object[]{code}, new RowMapperResultSetExtractor<User>(rowMapper, 1));
    int count = (list==null ? 0 : list.size());
    if (count==0)
      return null;

    return list.get(0);
  }

  @Override
  public User findByEmail(String email) {
    String sql = "Select * from " + ENTITY_TABLE_NAME + " Where Email = ?";

    RowMapper<User> rowMapper = BeanPropertyRowMapper.newInstance(User.class);
    List<User> list = jdbcTemplate.query(sql, new Object[]{email}, new RowMapperResultSetExtractor<User>(rowMapper, 1));
    int count = (list==null ? 0 : list.size());
    if (count==0)
      return null;

    return list.get(0);
  }

  @Override
  public int fetchMaxId() {
    String sql = "Select Max(id) From " + ENTITY_TABLE_NAME;

    Integer max = jdbcTemplate.queryForObject(sql, Integer.class);
    return Util.getInt(max);
  }


  @Override
  public int insertRow(User row) {
    String sql = "Insert into " + ENTITY_TABLE_NAME + "(Id, Code, Name, Surname, Password, Email, Phone, Status)"
          + " Values(?, ?, ?, ?, ?, ?, ?, ?)";

    Object[] values = new Object[]{row.getId(), row.getCode(), row.getName(), row.getSurname(), row.getPassword(),
              row.getEmail(), row.getPhone(), row.getStatus()};

    int rowCount = jdbcTemplate.update(sql, values);
    return rowCount;
  }

  @Override
  public int updateRow(User row) {
    String sql = "Update " + ENTITY_TABLE_NAME
          + " Set Code = ?, Name = ?, Surname = ?, Password = ?,"
          + " Email = ?, Phone = ?, Status = ?"
          + " Where Id = ?";

    Object[] values = new Object[]{row.getCode(), row.getName(), row.getSurname(), row.getPassword(),
              row.getEmail(), row.getPhone(), row.getStatus(),
              row.getId()};
    int rowCount = jdbcTemplate.update(sql, values);
    return rowCount;
  }

  @Override
  public int deleteRow(User row) {
    String sql = "Delete from " + ENTITY_TABLE_NAME + " Where Id = ?";
    int rowCount = jdbcTemplate.update(sql, row.getId());
    return rowCount;
  }
}


5. Service Layer

User Service Implementation:
package com.selimsql.lesson.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.selimsql.lesson.dao.UserDao;
import com.selimsql.lesson.domain.User;
import com.selimsql.lesson.dto.UserDTO;

@Service("userService")
public class UserServiceImpl implements UserService {

   private UserDao userDao;

   @Autowired
   public void setUserDao(UserDao userDao) {
      this.userDao = userDao;
   }

   @Override
   public List<User> queryUserList(UserDTO userDTO) {
      List<User> list = userDao.queryUserList(userDTO);
      return list;
   }

   @Override
   public User findById(Integer id) {
      User user = userDao.findById(id);
      return user;
   }

   @Override
   public User findByCode(String code) {
      return userDao.findByCode(code);
   }

   @Override
   public User findByEmail(String email) {
      return userDao.findByEmail(email);
   }

   @Override
   public int fetchMaxId() {
      return userDao.fetchMaxId();
   }

   @Override
   public int insertRow(User row) {
      return userDao.insertRow(row);
   }

   @Override
   public int updateRow(User row) {
      return userDao.updateRow(row);
   }

   @Override
   public int deleteRow(User row) {
      return userDao.deleteRow(row);
   }
}


6. Controller Layer

User controller service: Scope is session based for keeping every user query parameters. User CRUD operations are validated also.
package com.selimsql.lesson.controller;

import java.io.Serializable;
import java.util.List;

import javax.validation.Valid;

import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.validation.BindingResult;
import org.springframework.validation.Errors;
import org.springframework.validation.ValidationUtils;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.selimsql.lesson.configuration.SpringAppContext;
import com.selimsql.lesson.constant.Constants;
import com.selimsql.lesson.constant.StatusEnum;
import com.selimsql.lesson.domain.User;
import com.selimsql.lesson.dto.UserDTO;
import com.selimsql.lesson.exceptions.CustomRuntimeException;
import com.selimsql.lesson.util.Util;

@Controller
@Scope(value = "session")
public class UserController implements Serializable {

   private static final long serialVersionUID = 1L;

   private static final String VIEW_userList      = "userList";
   private static final String VIEW_userRow       = "userRow";
   private static final String VIEW_userRowSuccess= "userRowSuccess";

   private UserDTO userDTO;
   private List<User> listUser;


   private UserController() {
      super();
      resetUserDTO();
   }

   private UserDTO resetUserDTO() {
      userDTO = new UserDTO(); //Empty query params at first!
      return userDTO;
   }

   private List<User> resetListUser() {
      listUser = null; //Empty list at first!
      return listUser;
   }
   
   @RequestMapping(value = { "/", "/home", "/userList" }, method = RequestMethod.GET)
   public String userListFirst(ModelMap model) {
      resetListUser();

      return userListShow(model);
   }

   
   @RequestMapping(value = "/userQuery", method = RequestMethod.POST)
   public String userQuery(@Valid UserDTO userDTO, ModelMap model) {
      this.userDTO = userDTO;
      this.listUser = SpringAppContext.getUserServiceBean().queryUserList(userDTO);

      return userListShow(model);
   }

   @RequestMapping(value = "/userListShow", method = RequestMethod.GET)
   //In: this.userDTO
   //In: this.listUser
   public String userListShow(ModelMap model) {
      model.addAttribute("userDTO", userDTO);
      model.addAttribute("listUser", listUser);
      return VIEW_userList;
   }

   @RequestMapping(value = "/userListRefresh", method = RequestMethod.GET)
   public String userListRefresh(ModelMap model) {
      this.listUser = SpringAppContext.getUserServiceBean().queryUserList(userDTO);
      return userListShow(model);
   }


   /**
    * This method will provide the page to add a new user.
    */
   @RequestMapping(value = "/newUser", method = RequestMethod.GET)
   public String newUser(ModelMap model) {
      User user = new User();
      int maxId = SpringAppContext.getUserServiceBean().fetchMaxId();
      user.setId(Integer.valueOf(maxId + 1));
      user.setStatus(StatusEnum.ACTIVE.getValue());

      model.addAttribute("user", user);
      model.addAttribute("operation", Constants.AUTHORITYVALUE_Add);

      return VIEW_userRow;
   }

   
   @RequestMapping(value = "/newUser", method = RequestMethod.POST)
   public String newUserSave(@Valid User user, BindingResult result, ModelMap model) {
      Integer userId = (user==null ? null : user.getId());

      final boolean addUser = true;
      validateNewData(user, addUser, result);

      if (result.hasErrors()) {
         model.addAttribute("operation", Constants.AUTHORITYVALUE_Add);
         return VIEW_userRow;
      }

      SpringAppContext.getUserServiceBean().insertRow(user);

      model.addAttribute("success", "UserId:" + userId + ", " + user.getName() + " "
      								+ user.getSurname() + " row has been added.");

      return VIEW_userRowSuccess;
   }

   private void validateNewData(User user, boolean add, Errors errors) {
      Integer userId = (user==null ? null : user.getId());
      if (Util.getInt(userId) <= 0) {
         errors.rejectValue("id", "required.Id");
         //return;
      }
      else
      //if valid
      {
         User userBefore = SpringAppContext.getUserServiceBean().findById(userId);
         if (add) {
            if (userBefore!=null)
               errors.rejectValue("id", "Row.idX.already.exist.Please.enter.different.id",
                                        new Object[]{userId}, "Error");
         }
         else
         //if (update)
         {
            if (userBefore==null)
               errors.rejectValue("id", "message.generic",
                                        new Object[]{"UserId:" + userId + " row is absent!"}, "Error");
         }
      }

      //------------------------------
      String code = (user==null ? null : user.getCode());
      if (Util.isEmpty(code)) {
         errors.rejectValue("code", "required.Code");
      }
      else {
         User userBefore = SpringAppContext.getUserServiceBean().findByCode(code);
         if (userBefore!=null) {
            if (add) {
               errors.rejectValue("code", "Row.codeX.already.exist.Please.enter.different.code",
                                          new Object[]{code}, "Error");
            }
            else
            //if (update)
            if (Util.isNotEqual(userId, userBefore.getId()))
            {
               errors.rejectValue("code", "message.generic",
                                  new Object[]{"UserCode:" + code + " has already been defined!"}, "Error");
            }
         }

         String codeSmoothed = Util.codeFromStr(code);
         if (Util.isNotEqual(code, codeSmoothed)) {
            errors.rejectValue("code", "message.generic",
                               new Object[]{"Code is invalid! Suggestion code:" + codeSmoothed}, "Error");
         }
      }


      //------------------------------
      String email = (user==null ? null : user.getEmail());
      if (Util.isEmpty(email)) {
         errors.rejectValue("email", "required.Email");
      }
      else {
         User userBefore = SpringAppContext.getUserServiceBean().findByEmail(email);
         if (userBefore!=null) {
            if (add) {
               errors.rejectValue("email", "Row.emailX.already.exist.Please.enter.different.email",
                                   new Object[]{email}, "Error");
            }
            else
            //if (update)
            if (Util.isNotEqual(userId, userBefore.getId()))
            {
               errors.rejectValue("email", "message.generic",
                                  new Object[]{"UserEmail:" + email + " has already been defined!"}, "Error");
            }
         }

         if (Util.isEMailValid(email)==false) {
            errors.rejectValue("email", "invalid.Email");
         }
      }


      ValidationUtils.rejectIfEmpty(errors, "name", "required.Name");
      ValidationUtils.rejectIfEmpty(errors, "surname", "required.Surname");
      ValidationUtils.rejectIfEmpty(errors, "password", "required.Password");
      ValidationUtils.rejectIfEmpty(errors, "status", "required.Status");
   }//validate_NewData

   
   @RequestMapping(value = "/showUser-{id}", method = RequestMethod.GET)
   public String showUser(@PathVariable String id, ModelMap model) {
      Integer userId = Integer.valueOf(Util.getInt(id));
      User user = SpringAppContext.getUserServiceBean().findById(userId);
      if (user==null)
         throw new CustomRuntimeException("PU", "UserId:" + userId + " row is absent!");

      model.addAttribute("user", user);
      model.addAttribute("operation", Constants.AUTHORITYVALUE_Show);

      return VIEW_userRow;
   }


   /**
    * This method will provide page to update an existing user.
    */
   @RequestMapping(value = "/updateUser-{id}", method = RequestMethod.GET)
   public String updateUser(@PathVariable String id, ModelMap model) {
      Integer userId = Integer.valueOf(Util.getInt(id));
      User user = SpringAppContext.getUserServiceBean().findById(userId);
      if (user==null)
         throw new CustomRuntimeException("PU", "UserId:" + userId + " row is absent!");

      model.addAttribute("user", user);
      model.addAttribute("operation", Constants.AUTHORITYVALUE_Update);

      return VIEW_userRow;
   }

   /**
    * This method will be called on form submission, handling POST request for
    * updating user in database. It also validates the user input
    */
   @RequestMapping(value = "/updateUser-{id}", method = RequestMethod.POST)
   public String updateUser(@Valid User user, BindingResult result,
         ModelMap model, @PathVariable String id) {

      final boolean addUser = false; //update!
      validateNewData(user, addUser, result);

      if (result.hasErrors()) {
         model.addAttribute("operation", Constants.AUTHORITYVALUE_Update);
         return VIEW_userRow;
      }

      SpringAppContext.getUserServiceBean().updateRow(user);

      model.addAttribute("success", "UserId:" + id + ", " + user.getName() + " "
                        + user.getSurname() + " row has been updated.");

      return VIEW_userRowSuccess;
   }

   
   @RequestMapping(value = "/deleteUser-{id}", method = RequestMethod.GET)
   public String deleteUser(@PathVariable String id, ModelMap model) {
      Integer userId = Integer.valueOf(Util.getInt(id));
      User user = SpringAppContext.getUserServiceBean().findById(userId);
      if (user==null)
         throw new CustomRuntimeException("PU", "UserId:" + userId + " row is absent!");

      model.addAttribute("user", user);
      model.addAttribute("operation", Constants.AUTHORITYVALUE_Delete);

      return VIEW_userRow;
   }
   
   @RequestMapping(value = "/deleteUser-{id}", method = RequestMethod.POST)
   public String deleteUser(@Valid User user, BindingResult result,
         ModelMap model, @PathVariable String id)
   {
      SpringAppContext.getUserServiceBean().deleteRow(user);

      model.addAttribute("success", "UserId:" + id + ", " + user.getName() + " "
                        + user.getSurname() + " row has been deleted.");

      return VIEW_userRowSuccess;
      //return "redirect:/" + VIEW_userList;
   }
}


7. Application Home Page

"userList.jsp" named file is home page. UserController directs the flow to this page.
This page displays the user list with insert, update and delete action links.
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page trimDirectiveWhitespaces="true"%>

<%@ taglib prefix="f" uri="http://www.springframework.org/tags/form"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>

<!DOCTYPE html>
<html lang='en'>
<head>
 <jsp:include page="/WEB-INF/viewsCommon/htmlHeader.jsp"/>
</head>

<body>

<div class="generic-container">

<!-- Default panel contents -->
<h2>
 <span>User List</span>
</h2>

 <c:url value="/userQuery" var="actionUrl"/>
 <f:form method="POST" action="${actionUrl}" modelAttribute="userDTO">
 <table>
   <tr>
      <td align="right">Code</td>
      <td>
         <f:input type="text" path="code" id="code"/>
      </td>

      <td align="right">Name</td>
      <td>
         <f:input type="text" path="name" id="name"/>
      </td>

      <td align="right">Surname</td>
      <td>
         <f:input type="text" path="surname" id="surname"/>
      </td>
   </tr>

   <tr>
      <td> </td>
      <td colspan="3">
         <input type="submit" value="Query"/>

           
         <a href="<c:url value='/newUser'/>">New User</a>
      </td>
   </tr>
 </table>
 </f:form>

<br/>
<table>
    <thead>
    <tr>
        <th align="right">Id</th>
        <th align="left">Code</th>
        <th align="left">Name</th>
        <th align="left">Surname</th>
        <th align="left">Email</th>
        <th align="center">Status</th>
        <th align="left">Operation</th>
    </tr>
   </thead>

    <tbody>
 <c:forEach items="${listUser}" var="user">
      <tr>
         <td align="right">${user.id}</td>
         <td align="left">${user.code}</td>
         <td align="left">${user.name}</td>
         <td align="left">${user.surname}</td>
         <td align="left">${user.email}</td>
         <td align="center"><fmt:message key="Status${user.status}"/></td>

         <td>
          <a href="<c:url value='/showUser-${user.id}'/>">Show</a>
          <a href="<c:url value='/updateUser-${user.id}'/>">Update</a>
          <a href="<c:url value='/deleteUser-${user.id}'/>">Delete</a>
         </td>
      </tr>
    </c:forEach>
     </tbody>
   </table>
</div>

</body>
</html>


8. Row Edit Form Page

"userRow.jsp" named file is for CRUD page. UserController directs the flow to this page for insert, update or delete the row.
User add/update operations are validated like this:



<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page trimDirectiveWhitespaces="true"%>

<%@ taglib prefix="f" uri="http://www.springframework.org/tags/form"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>

<!DOCTYPE html>
<html>
<head>
 <jsp:include page="/WEB-INF/viewsCommon/htmlHeader.jsp"/>
</head>

<body>
<div class="container">

<h3>User Row(${operation})</h3>

<br/>
<f:form method="POST" modelAttribute="user">
   <table>
    <tr>
      <td align="right">Id (*)</td>
      <td>
         <c:choose>
            <c:when test="${operation eq 'Add'}">
               <f:input type="text" path="id" id="id" placeholder="UserId"/>
               <f:errors path="id" class="has-error" cssClass="error"/>
            </c:when>
            <c:otherwise>
               <f:input type="text" path="id" id="id" disabled="true"/>
            </c:otherwise>
         </c:choose>
      </td>
    </tr>


    <tr>
      <td align="right">Name (*)</td>
      <td>
         <f:input type="text" path="name" id="name"/>
         <f:errors path="name" class="has-error"/>
      </td>
    </tr>

    <tr>
      <td align="right">Surname (*)</td>
      <td>
         <f:input type="text" path="surname" id="surname"/>
         <f:errors path="surname" class="has-error"/>
      </td>
    </tr>

    <tr>
      <td align="right">Code (*)</td>
      <td>
         <f:input type="text" path="code" id="code"/>
         <f:errors path="code" class="has-error"/>
      </td>
    </tr>

    <tr>
      <td align="right">Password (*)</td>
      <td>
         <f:input type="password" path="password" id="password"/>
         <f:errors path="password" class="has-error"/>
      </td>
    </tr>

    <tr>
      <td align="right">Email (*)</td>
      <td>
         <f:input type="text" path="email" id="email"/>
         <f:errors path="email" class="has-error"/>
      </td>
    </tr>

    <tr>
      <td align="right">Telephone</td>
      <td>
         <f:input type="text" path="phone" id="phone"/>
      </td>
    </tr>


    <tr>
      <td align="right">Status (*)</td>
      <td>
         <f:select path="status" id="status">
            <f:option value="1"><fmt:message key="Status1"/></f:option>
            <f:option value="0"><fmt:message key="Status0"/></f:option>
            <f:option value="9"><fmt:message key="Status9"/></f:option>
         </f:select>
         <f:errors path="status" class="has-error"/>
      </td>
    </tr>

    <tr>
      <td> </td>
      <td>
       <c:choose>
         <c:when test="${operation eq 'Show'}">
            <a href="<c:url value='/userListShow'/>">Okay</a>
         </c:when>
         <c:otherwise>
            <input type="submit" value="${operation}"/>
              or  
            <a href="<c:url value='/userListShow'/>">Cancel</a>
         </c:otherwise>
       </c:choose>
      </td>
    </tr>
   </table>
</f:form>

</div>

</body>
</html>


9. Spring MVC Root Application Configuration

"RootConfig.java" class is for root application configuration:
package com.selimsql.lesson.configuration;

import org.springframework.context.MessageSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.support.ResourceBundleMessageSource;
import org.springframework.web.servlet.ViewResolver;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;
import org.springframework.web.servlet.view.InternalResourceViewResolver;
import org.springframework.web.servlet.view.JstlView;

@Configuration
@EnableWebMvc
@ComponentScan(basePackages = "com.selimsql.lesson")
public class RootConfig extends WebMvcConfigurerAdapter {

   @Bean
   public ViewResolver viewResolver() {
      InternalResourceViewResolver viewResolver = new InternalResourceViewResolver();
      viewResolver.setViewClass(JstlView.class);
      viewResolver.setPrefix("/WEB-INF/views/");
      viewResolver.setSuffix(".jsp");
      return viewResolver;
   }

    /**
     * Configure MessageSource to lookup any validation/error message in internationalized property files
     */
   @Bean
   public MessageSource messageSource() {
       ResourceBundleMessageSource messageSource = new ResourceBundleMessageSource();
       messageSource.setBasename("messages/messages");
       return messageSource;
   }

   /**
     * Configure ResourceHandlers to serve static resources like CSS/Javascript etc...
     */
    @Override
    public void addResourceHandlers(ResourceHandlerRegistry registry) {
        registry.addResourceHandler("/static/**").addResourceLocations("/static/");
    }
}


10. Application Database DataSource Configuration

"DbConfiguration.java" class is for database datasource configuration:
package com.selimsql.lesson.configuration;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
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.core.env.Environment;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@ComponentScan({ "com.selimsql.lesson.configuration" })
@PropertySource(value = { "classpath:spring/application.properties" })
public class DbConfiguration {

    @Autowired
    private Environment environment;

    @Bean
    public DataSource getDataSource()
    {
       BasicDataSource dataSource = new BasicDataSource();
       
       String driverClassName = environment.getProperty("jdbc.driverClassName");
       dataSource.setDriverClassName(driverClassName);

       String driverUrl = environment.getProperty("jdbc.url");
       dataSource.setUrl(driverUrl);

       dataSource.setUsername(environment.getProperty("jdbc.username"));
       dataSource.setPassword(environment.getProperty("jdbc.password"));

       return dataSource;
   }
}


11. Spring Application Context

For getting java class beans manually:
package com.selimsql.lesson.configuration;

import org.springframework.beans.BeansException;
import org.springframework.beans.factory.BeanCreationException;
import org.springframework.beans.factory.NoSuchBeanDefinitionException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;
import org.springframework.util.Assert;

import com.selimsql.lesson.service.UserService;

@Component
public class SpringAppContext implements ApplicationContextAware {

    private static ApplicationContext appContext;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
       appContext = applicationContext;
    }

    private static Object getBean(String beanName) {
      Assert.notNull(appContext, "ApplicationContext must be not null!");

      try {
         Object obj = appContext.getBean(beanName);
         return obj;
      }
      catch(NoSuchBeanDefinitionException nsbex) {
         throw nsbex;
      }
      catch(BeanCreationException bcex) {
         //throw bcex;
         return null;
      }
      catch(Exception ex) {
         return null;
      }
    }

    public static UserService getUserServiceBean() {
       return (UserService)getBean("userService");
    }
}


12. Spring Application Initializer

No "web.xml" is needed by this initializer class:
package com.selimsql.lesson.configuration;

import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;

public class AppInitializer extends AbstractAnnotationConfigDispatcherServletInitializer {
   
   @Override
   protected Class<?>[] getRootConfigClasses() {
      return new Class[] { RootConfig.class };
   }

   @Override
   protected Class<?>[] getServletConfigClasses() {
      return null;
   }

   @Override
   protected String[] getServletMappings() {
      return new String[] { "/" };
   }
}


Run Web Application

Build and deploy this application on a Web Application Server.
For example, you can deploy this on "Apache Tomcat Version8" server.

Open a browser and try this link:
http://localhost:8080/SpringMvcJdbcTemplate/


Download Project Example

Download source: SpringMvcJdbcTemplateSSqlUserCRUDExample.zip
File size: 30KB


References



Related Posts

Related Posts


Android Mobile Apps

Copyright © 2009 - 2019 SelimSql