Spring Boot JAP + MySQL实战

可参考使用STS创建Spring Boot 项目创建一个新的Spring Boot 项目，创建项目时选择Web、JPA和MySQL等选项。

• application.yml中的配置信息
• 基于注解的shitibean
• 基于JPA接口的简单应用
• 多表多条件动态组合查询以及分页和排序

application.yml中的配置信息

spring:
  datasource:
    url: jdbc:mysql://192.168.10.58:3306/jianshu?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
    username: root
    password: infcn123
    platform: mysql
  jpa:
    database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
    hibernate:
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
    database: mysql
    show-sql: true
    
  http:
    encoding:
      force: true
      charset: UTF-8
      enabled: true
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    joda-date-time-format: yyyy-MM-dd HH:mm:ss
    time-zone:
      GMT+8
    serialization:
      fail-on-empty-beans: false
security:
  basic:
    enabled: false
server:
  tomcat:
    uri-encoding: UTF-8
  port: 8080

实体Bean配置

用户配置

import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.validation.constraints.Size;
import org.springframework.data.annotation.CreatedDate;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;
import lombok.experimental.Accessors;

/**
 * 系统用户
 * 
 * @author 李庆海
 */
@Data
@Accessors(chain = true)
@Entity
@Table(name = "SYS_USER")
public class User implements java.io.Serializable {
    private static final long serialVersionUID = 1L;
    /***/
    @Id
    @Column(name = "ID", unique = true, nullable = false, length = 32)
    private String id;

    /** 登录账号 */
    @Column(name = "LOGIN_NAME", nullable = false, length = 50)
    @Size(max = 50)
    private String loginName;

    /** 用户名称 */
    @Column(name = "USER_NAME", nullable = true, length = 50)
    private String userName;

    /** 登录密码 */
    @Column(name = "PASSWORD", nullable = false, length = 32)
    @JsonIgnore
    private String password;

    /** 注册日期 */
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "CREATE_DATE", nullable = false)
    @CreatedDate
    private Date registDate;

    /** 用户状态 */
    @Column(name = "ENABLED", nullable = false, length = 1)
    private Integer enabled;

    /**
     * 用户的角色
     */
    @ManyToOne(cascade = {}, fetch = FetchType.EAGER)
    @JoinColumn(name = "ROLE_ID")
    private Role role;

}

角色配置

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.Data;
import lombok.experimental.Accessors;

/**
 * 系统角色
 * 
 * @author 李庆海
 *
 */
@Data
@Accessors(chain = true)
@Entity
@Table(name = "SYS_ROLE")
public class Role implements Serializable {
    private static final long serialVersionUID = -2139369027015540299L;

    /** 主键 */
    @Id
    @Column(name = "ID", unique = true, length = 32, nullable = false)
    private String id;

    /** 角色描述 */
    @Column(name = "DES", length = 255, nullable = false)
    private String des;

    /** 角色名称 */
    @Column(name = "TITLE", length = 20, nullable = true)
    private String title;

    /** 角色排序 */
    @Column(name = "SORTER", length = 3, nullable = false)
    private Integer sorter;

}

基于JPA接口的简单应用

JpaRepository实现了PagingAndSortingRepository接口，PagingAndSortingRepository接口实现了CrudRepository接口，CrudRepository接口实现了Repository接口。save方法既可以新增又可以更新，只是在更新一个数据库中不存在的对象时会添加一个指定id的实例对象。findBy系列只能实现简单的、固定参数的查询，对于动态查询无能为力。

继承JpaRepository的RoleRepository 角色接口

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import cn.com.infcn.jianshu.model.Role;
/**
 * 系统角色数据访问类
 * @author 李庆海
 *
 */
public interface RoleRepository extends JpaRepository<Role, String> {
    @Query("select max(t.sorter) from Role t")
    Integer getMaxSorter();
}

RoleService服务类

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.ExampleMatcher;
import org.springframework.data.domain.ExampleMatcher.GenericPropertyMatchers;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import cn.com.infcn.jianshu.dao.RoleRepository;
import cn.com.infcn.jianshu.model.Role;
import cn.com.infcn.jianshu.util.StringUtil;

/**
 * 系统角色Service类
 * 
 * @author 李庆海
 *
 */
@Service
public class RoleService {
    @Autowired
    private RoleRepository roleRepository;

    /**
     * 创建角色
     * SORTER字段是获取数据库最大的sorter字段加1
     * @param title 角色名称
     * @param des 角色描述
     * @return Role
     */
    @Transactional
    public Role create(String title,String des) {
        Integer sorter = roleRepository.getMaxSorter();
        if(null==sorter){
            sorter=0;
        }
        Role model =new Role()
            .setId(StringUtil.generateUUID())
            .setTitle(title)
            .setDes(des)
            .setSorter(sorter+1);
        return this.roleRepository.save(model);
    }

    /**
     * 删除角色
     * 
     * @param id  角色主键
     */
    @Transactional
    public void delete(String id) {
        this.roleRepository.delete(id);
    }

    /**
     * 根据主键获取Model
     * 
     * @param id  角色主键
     * @return Role
     */
    public Role read(String id) {
        return this.roleRepository.findOne(id);
    }

    /**
     * 判断是否存在同名的角色
     * 
     * @param name
     *            名称
     * @return true 或 false
     */
    public boolean exists(String name) {
        ExampleMatcher matcher = ExampleMatcher.matching().withMatcher("title", GenericPropertyMatchers.exact());
        Example<Role> ex = Example.of(new Role().setTitle(name), matcher);
        return this.roleRepository.exists(ex);
    }

    /**
     * 查询所有角色
     * 
     * @return
     */
    public List<Role> findRoles() {
        return this.roleRepository.findAll();
    }
}

多表多条件动态组合查询

要想实现多表多条件动态组合查询还需要多继承一个接口JpaSpecificationExecutor。

继承JpaRepository和JpaSpecificationExecutor的UserRepository 用户接口

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import cn.com.infcn.jianshu.model.User;

/**
 * 系统用户数据访问类
 * @author 李庆海
 *
 */
public interface UserRepository extends JpaRepository<User, String>,JpaSpecificationExecutor<User> {
    /**
     * 根据登录账号和登录密码查询用户
     * @param loginName 登录账号
     * @param password 登录密码
     * @return User
     */
    @Query("from User t where t.loginName=:loginName and t.password=:password and t.enabled=1")
    public User findUser(@Param(value = "loginName") String loginName, @Param(value = "password") String password);

    /**
     * 根据登录账号查询用户信息
     * @param loginName 登录账号
     * @return User
     */
    @Query("from User t where t.loginName=:loginName")
    public User findUserByLoginName(@Param(value = "loginName")String loginName);

    /**
     * 重置密码
     * @param id 用户编号
     * @param password 新密码
     */
    @Modifying
    @Query("update User u set u.password=:password where u.id=:id")
    public void updatePassword(@Param(value = "id") String id, @Param(value = "password") String password);

    
    /**
     * 更新系统用户的状态
     * @param id 用户编号
     * @param enabled 用户状态
     */
    @Modifying
    @Query("update User u set u.enabled=:enabled where u.id=:id")
    public void updateEnabled(@Param(value = "id") String id, @Param(value = "enabled") int enabled);
}

UserService服务类

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.ExampleMatcher;
import org.springframework.data.domain.ExampleMatcher.GenericPropertyMatchers;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import cn.com.infcn.jianshu.dao.UserRepository;
import cn.com.infcn.jianshu.model.Role;
import cn.com.infcn.jianshu.model.User;
import cn.com.infcn.jianshu.util.StringUtil;

@Service
public class UserService {
    
    @Autowired
    private UserRepository userRepository;
    
    /**
     * 添加用户，注册
     * 
     * @param loginName
     *            登录账号
     * @param password
     *            登录密码
     * @param userName
     *            用户名称
     * @param roleId
     *            用户角色
     * @return
     */
    @Transactional
    public User create(String loginName, String password, String userName,String roleId) {
        User user = new User()
            .setLoginName(loginName)
            .setUserName(userName)
            .setPassword(password)
            .setRegistDate(new Date())
            .setEnabled(1)
            .setRole(new Role().setId(roleId))
            .setId(StringUtil.generateUUID());
        return this.userRepository.saveAndFlush(user);
    }

    /**
     * 根据登录账号和密码查询用户
     * 
     * @param loginName
     *            登录账号
     * @param password
     *            登录密码
     * @return
     */
    public User login(String loginName, String password) {
        return this.userRepository.findUser(loginName, password);
    }

    /**
     * 根据登录账号查询用户信息
     * 
     * @param loginName
     *            登录账号
     * @return
     */
    public User getUser(String loginName) {
        return this.userRepository.findUserByLoginName(loginName);
    }

    /**
     * 重置密码
     * 
     * @param id
     *            用户编号
     * @param password
     *            新密码
     */
    @Transactional
    public void updatePassword(String id, String password) {
        this.userRepository.updatePassword(id, password);
    }

    /**
     * 注销用户，不是删除
     * 
     * @param id
     *            用户编号
     */
    @Transactional
    public void cancel(String id) {
        this.userRepository.updateEnabled(id, 0);
    }

    /**
     * 判断登录账号是否已经存在，登录账号唯一
     * 
     * @param loginName
     *            登录账号
     * @return
     */
    public boolean exists(String loginName) {
        ExampleMatcher matcher = ExampleMatcher.matching().withMatcher("loginName", GenericPropertyMatchers.exact());
        Example<User> ex = Example.of(new User().setLoginName(loginName), matcher);
        return this.userRepository.exists(ex);
    }

    /**
     * 根据主键获取用户信息
     * 
     * @param id
     *            用户编号
     * @return
     */
    public User getOne(String id) {
        return this.userRepository.getOne(id);
    }

    /**
     * 多条件查询用户
     * 
     * @param userName
     *            用户名称
     * @param roleId
     *            用户角色编号
     * @param start
     *            注册开始日期
     * @param end
     *            注册结束日期
     * @param page
     *            分页，从0开始
     * @param size
     *            每页的行数
     * @return
     */
    public Page<User> findDatas(String userName, String roleId, Date start, Date end, int page, int size) {
        Pageable pr = new PageRequest(page, size, new Sort(Direction.DESC, "registDate"));
        return this.userRepository.findAll(new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery,
                    CriteriaBuilder criteriaBuilder) {
                List<Predicate> list = new ArrayList<Predicate>();
                // root.get("xx属性")表示获取xx属性这个字段名称,like表示执行like查询,%zt%表示值
                if (null != userName) {
                    Predicate p = criteriaBuilder.like(root.get("userName"), "%" + userName + "%");
                    list.add(p);
                }
                if (null != roleId) {
                    //关联查询，User关联Role
                    Predicate p = criteriaBuilder.equal(root.join("role", JoinType.INNER).get("id"), roleId);
                    list.add(p);
                }
                if (null != start) {
                    Predicate p = criteriaBuilder.greaterThanOrEqualTo(root.get("registDate"), start);
                    list.add(p);
                }
                if (null != end) {
                    Predicate p = criteriaBuilder.lessThanOrEqualTo(root.get("registDate"), end);
                    list.add(p);
                }

                Predicate[] ps = new Predicate[list.size()];
                // 将查询条件联合起来之后返回Predicate对象
                return criteriaBuilder.and(list.toArray(ps));
            }
        }, pr);
    }
}