本文介绍spring boot,spring jdbc和spring transaction的使用

项目结构

依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<scope>runtime</scope>
</dependency>

application

spring.datasource.url=jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=123
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

server.tomcat.uri-encoding=UTF-8
server.port=8888

model层

// 模型层 User.java

package com.springlearn.learn.model;

public class User{
    private Integer id;
    private String name;
    private Integer age;
    private String sex;

    public User(Integer id, String name, Integer age, String sex) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.sex = sex;
    }

    public Integer getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public Integer getAge() {
        return age;
    }

    public String getSex() {
        return sex;
    }

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

    public void setName(String name) {
        this.name = name;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

}

mapper层

// UserMapper.java
// 这个层mapper是将数据映射到model层 

package com.springlearn.learn.mapper;


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

import com.springlearn.learn.model.User;

import org.springframework.jdbc.core.RowMapper;

public class UserMapper implements RowMapper<User> {
    public static final String sql = "select * from test";

    @Override
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        Integer id = rs.getInt("id");
        String name = rs.getString("name");
        Integer age = rs.getInt("age");
        String sex = rs.getString("sex");

        return new User(id, name, age, sex);
    }

}

dao层

// UserDao.java
// dao就是Data Access Object，数据访问对象

// @Transactional(propagation = Propagation.MANDATORY) 声明事务 
// MANDATORY只能在其他事物中调用此事务
// REQUIRES_NEW 创建一个新事务并且执行，其他事务挂起
// rollbackFor = UserTransactionException.class 只要抛出了UserTransactionException异常，事务回滚

package com.springlearn.learn.dao;

import java.util.List;

import javax.sql.DataSource;

import com.springlearn.learn.exception.UserTransactionException;
import com.springlearn.learn.mapper.UserMapper;
import com.springlearn.learn.model.User;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

@Repository
@Transactional
public class UserDao extends JdbcDaoSupport {

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

    public List<User> getUser() {
        String sql = UserMapper.sql;

        UserMapper userMapper = new UserMapper();
        List<User> list = this.getJdbcTemplate().query(sql, userMapper);

        return list;
    }

    public User getUserById(Integer id) {
        String sql = UserMapper.sql+" where id = ?";
        Object[] params = new Object[]{id};
        UserMapper userMapper = new UserMapper();
        User user = this.getJdbcTemplate().queryForObject(sql, params, userMapper);
        return user;
    }   

    @Transactional(propagation = Propagation.MANDATORY)
    public void addAge(Integer id, int addnum) throws UserTransactionException{
        User user = this.getUserById(id);
        if(user == null) {
            throw new UserTransactionException("User not found" + id);
        }
        int newAge = user.getAge() + addnum;
        if(newAge < 0) {
            throw new UserTransactionException("The Age in the User" + id + "is illegal" + newAge);
        }
        user.setAge(newAge);

        String sql = "update test set age = ? where id = ?;";
        this.getJdbcTemplate().update(sql, user.getAge(),user.getId());
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = UserTransactionException.class)
    public void setAge(int id, int addnum) throws UserTransactionException{
        addAge(id, addnum);
    }
}

exception层

// UserTransactionException.java
package com.springlearn.learn.exception;

public class UserTransactionException extends Exception {
    private static final long serialVersionUID = -3128681006635769411L;

    public UserTransactionException(String message) {
        super(message);
    }
}

MainController

// MainController.java
package com.springlearn.learn.controller;


import java.util.List;

import com.springlearn.learn.dao.UserDao;
import com.springlearn.learn.exception.UserTransactionException;
import com.springlearn.learn.model.User;

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;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
public class MainController {

    @Autowired
    private UserDao userdao;

    @ResponseBody
    @RequestMapping(value="/", method=RequestMethod.GET)
    public List<User> showAllUser(Model model) {
        List<User> list = userdao.getUser();

        model.addAttribute("userinfos", list);

        return list;
    }

    @ResponseBody
    @RequestMapping(value = "/setAge", method = RequestMethod.GET)
    public String setUserAge(@RequestParam(value="id") int id, @RequestParam(value="addnum") int addnum) {
        try{
            userdao.setAge(id, addnum);
            return "Ok";
        }catch (UserTransactionException e){
            return e.getMessage();
        }
        
    }
}