Code ví dụ Spring MVC + Spring JDBC + Maven + MySQL

Code ví dụ Spring MVC + Spring JDBC + Maven + MySQL

Ở bài này mình sẽ kết hợp Spring MVC với Spring JDBC để thực hiện ví dụ thêm, sửa, xóa dữ liệu với database.

Các công nghệ sử dụng trong bài viết này

• Spring 5.0.2.RELEASE
• Maven
• JDK 1.8
• Eclipse
• Server tomcat
• MySQL

Tạo Database

Tạo database demo-spring-mvc-jdbc với table customer

CREATE SCHEMA `demo-spring-mvc-jdbc` ;

CREATE TABLE `demo-spring-mvc-jdbc`.`customer` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `address` VARCHAR(255) NULL,
  PRIMARY KEY (`id`));

Tạo maven project:

Thư viện sử dụng:

<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>stackjava.com</groupId>
  <artifactId>SpringMVC-JDBC</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>
  <properties>
    <spring.version>5.0.2.RELEASE</spring.version>
    <jstl.version>1.2</jstl.version>
  </properties>
  <dependencies>

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

    <!-- Spring JDBC -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <!-- MySQL -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.45</version>
    </dependency>

    <!-- jsp-api -->
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>jsp-api</artifactId>
      <version>2.0</version>
      <scope>provided</scope>
    </dependency>

    <!-- jstl -->
    <dependency>
      <groupId>jstl</groupId>
      <artifactId>jstl</artifactId>
      <version>${jstl.version}</version>
    </dependency>
  </dependencies>
</project>

File thông tin kết nối database:

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/demo-spring-mvc-jdbc
jdbc.username=root
jdbc.password=admin1234

File Spring config:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
  xmlns:tx="http://www.springframework.org/schema/tx" xmlns:mvc="http://www.springframework.org/schema/mvc"
  xmlns:jdbc="http://www.springframework.org/schema/jdbc"
  xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.3.xsd
    http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.3.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-4.3.xsd
    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">
  
  <context:component-scan base-package="stackjava.com.springmvcjdbc" />
  <bean
    class="org.springframework.web.servlet.view.InternalResourceViewResolver">
    <property name="prefix">
      <value>/WEB-INF/views/jsp/</value>
    </property>
    <property name="suffix">
      <value>.jsp</value>
    </property>
  </bean>

  <bean
    class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <property name="location">
      <value>/resources/jdbc.properties</value>
    </property>
  </bean>

  <bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="${jdbc.driverClassName}" />
    <property name="url" value="${jdbc.url}" />
    <property name="username" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
  </bean>

  <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"></property>
  </bean>
  
  <!-- Enable Annotation based Declarative Transaction Management -->
  <tx:annotation-driven proxy-target-class="true"
    transaction-manager="transactionManager" />
    
  <!-- Creating TransactionManager Bean, since JDBC we are creating of type 
    DataSourceTransactionManager -->
  <bean id="transactionManager"
    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
  </bean>
  
</beans>

File entity:

package stackjava.com.springmvcjdbc.entities;

public class Customer {
  private int id;
  private String name;
  private String address;

  public Customer() {
  }

  public Customer(String name, String address) {
    this.name = name;
    this.address = address;
  }

  public Customer(int id, String name, String address) {
    this.id = id;
    this.name = name;
    this.address = address;
  }

  // getter - setter

}

File CustomerMapper dùng để convert dữ liệu query sang đối tượng Customer:

package stackjava.com.springmvcjdbc.entities;

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

import org.springframework.jdbc.core.RowMapper;

public class CustomerMapper implements RowMapper<Customer> {

  public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
    Customer customer = new Customer();
    customer.setId(rs.getInt("id"));
    customer.setName(rs.getString("name"));
    customer.setAddress(rs.getString("address"));
    return customer;
  }

}

File Controller:

package stackjava.com.springmvcjdbc.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;

import stackjava.com.springmvcjdbc.entities.Customer;
import stackjava.com.springmvcjdbc.service.CustomerService;

@Controller
public class CustomerController {

  @Autowired
  private CustomerService customerService;

  @RequestMapping(value={"/", "/customer-list"})
  public String listCustomer(Model model) {
    model.addAttribute("listCustomer", customerService.findAll());
    return "customer-list";
  }

  @RequestMapping("/customer-save")
  public String insertCustomer(Model model) {
    model.addAttribute("customer", new Customer());
    return "customer-save";
  }

  @RequestMapping("/customer-view/{id}")
  public String viewCustomer(@PathVariable int id, Model model) {
    Customer customer = customerService.findById(id);
    model.addAttribute("customer", customer);
    return "customer-view";
  }
  
  @RequestMapping("/customer-update/{id}")
  public String updateCustomer(@PathVariable int id, Model model) {
    Customer customer = customerService.findById(id);
    model.addAttribute("customer", customer);
    return "customer-update";
  }

  @RequestMapping("/saveCustomer")
  public String doSaveCustomer(@ModelAttribute("Customer") Customer customer, Model model) {
    customerService.save(customer);
    model.addAttribute("listCustomer", customerService.findAll());
    return "customer-list";
  }

  @RequestMapping("/updateCustomer")
  public String doUpdateCustomer(@ModelAttribute("Customer") Customer customer, Model model) {
    customerService.update(customer);
    model.addAttribute("listCustomer", customerService.findAll());
    return "customer-list";
  }
  
  @RequestMapping("/customerDelete/{id}")
  public String doDeleteCustomer(@PathVariable int id, Model model) {
    customerService.delete(id);
    model.addAttribute("listCustomer", customerService.findAll());
    return "customer-list";
  }
}

Lưu ý, khi delete thì người ta hiếm khi dùng method GET. mà sẽ đưa nó vào form và sử dụng method POST.

Ở đây mình để method GET để cho ví dụ dễ hiểu và ngắn gọn.

File Service:

Thường thì controller sẽ không gọi trực tiếp đến DAO mà nó sẽ thông qua 1 tầng service

Tầng service sẽ thực hiện validate bussiness, kiểm tra, tổng hợp.

1 Method trong service có thể gọi tới nhiều method trong DAO còn ở tầng DAO người ta sẽ chia nhỏ các method thành từng đơn vị nhỏ nhất để sử dụng lại trong service.

package stackjava.com.springmvcjdbc.service;

import java.util.List;

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

import stackjava.com.springmvcjdbc.dao.CustomerDAO;
import stackjava.com.springmvcjdbc.entities.Customer;

@Service
@Transactional
public class CustomerService {

  @Autowired
  private CustomerDAO customerDAO;
  
  public List<Customer> findAll() {
    return customerDAO.findAll();
  }

  public Customer findById(int id) {
    return customerDAO.findById(id);
  }
  
  public void save(Customer customer){
    // validate business
    customerDAO.save(customer);
  }

  public void update(Customer customer){
    // validate business
    customerDAO.update(customer);
  }
  
  public void delete(int id){
    // validate business
    customerDAO.delete(id);
  }
}

File DAO:

package stackjava.com.springmvcjdbc.dao;

import java.util.List;

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

import stackjava.com.springmvcjdbc.entities.Customer;
import stackjava.com.springmvcjdbc.entities.CustomerMapper;

@Repository
@Transactional
public class CustomerDAO {

  @Autowired
  private JdbcTemplate jdbcTemplate;

  public void save(Customer customer) {
    String sql = "INSERT INTO customer (name, address) VALUES (?, ?)";
    jdbcTemplate.update(sql, customer.getName(), customer.getAddress());
  }

  public void delete(int id) {
    String sql = "DELETE FROM customer WHERE id = " + id;
    jdbcTemplate.update(sql);
  }
  
  public void update(Customer customer) {
    String sql = "UPDATE customer SET name = ?, address = ? WHERE id = ? ";
    jdbcTemplate.update(sql, customer.getName(), customer.getAddress(), customer.getId());
  }

  public Customer findById(int id) {
    String sql = "SELECT * FROM customer WHERE id = ?";
    return jdbcTemplate.queryForObject(sql, new CustomerMapper(), id);
  }

  public List<Customer> findAll() {
    String sql = "SELECT * FROM customer";
    return jdbcTemplate.query(sql, new CustomerMapper());
  }

}

File View:

<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<html>
<head>
<title>Helo Spring MVC + JDBC</title>
<style>
table, th, td {
  border: 1px solid black;
}
td {
  padding-right: 30px;
}
</style>
</head>
<body>
  <c:url value="/customer-save" var="urlSave"/>
  <c:url value="/customer-view/" var="urlView"/>
  <c:url value="/customer-update/" var="urlUpdate"/>
  <c:url value="/customerDelete/" var="urlDelete"/>
  <h1>List Customer:</h1>
  <a href="${urlSave}">Add Customer</a>
  <br />
  <br />


  <table>
    <tr>
      <th>Id</th>
      <th>Name</th>
      <th>Address</th>
      <th>View</th>
      <th>Edit</th>
      <th>Delete</th>
    </tr>
    <c:if test="${not empty listCustomer}">
      <c:forEach var="customer" items="${listCustomer}">
        <tr style="border: 1px black solid">
          <td>${customer.id}</td>
          <td>${customer.name}</td>
          <td>${customer.address}</td>
          <td> <a href="${urlView}/${customer.id}">View</a></td>
          <td> <a href="${urlUpdate}/${customer.id}">Edit</a></td>
          <td> <a href="${urlDelete}/${customer.id}">Delete</a></td>
        </tr>
      </c:forEach>
    </c:if>
  </table>

</body>
</html>

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

<html>
<head>
<title>Helo Spring MVC + JDBC</title>
</head>
<body>
  <a href="<c:url value="/customer-list" />" >List Customer</a><br />

  <h1>Add new Customer:</h1>
  <c:url value="/saveCustomer" var="saveCustomer"/>
  <form:form action="${saveCustomer}" method="POST"
    modelAttribute="customer">
    	Name: <form:input path="name" /> <br/> <br/>
    	Address: <form:input path="address" /> <br/> <br/>
    <button type="submit">Submit</button>
  </form:form>

</body>
</html>

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

<html>
<head>
<title>Helo Spring MVC + JDBC</title>
</head>
<body>
  <a href="<c:url value="/customer-list" />">List Customer</a>
  <br />

  <h1>Edit Customer:</h1>
  <c:url value="/updateCustomer" var="updateCustomer" />
  <form:form action="${updateCustomer}" method="POST" modelAttribute="customer">
      Id: <form:input path="id" readonly="true" /> <br/> <br/>
    	Name: <form:input path="name" /> <br/> <br/>
    	Address: <form:input path="address" /> <br/> <br/>
    <button type="submit">Submit</button>
  </form:form>

</body>
</html>

<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<html>
<head>
<title>Helo Spring MVC + JDBC</title>
</head>
<body>
  <a href="<c:url value="/customer-list" />" >List Customer</a><br />
  <h1>View Customer:</h1>	
  Customer ID: ${customer.id} <br/>
  Customer Name: ${customer.name} <br/>
  Customer Address: ${customer.address} <br/>
</body>
</html>

Demo:

 

Thêm mới customer

View Customer:

Update customer:

 

Delete customer:

 

Code ví dụ Spring MVC + Spring JDBC + Maven + MySQL

Okay, Done!

Download code ví dụ trên tại đây

 

References:

https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html