Anarkh的知识库

主题

在实际开发中，最为常见的是基于数据库的CRUD封装等，比如SpringBoot集成MySQL数据库，常用的方式有JPA和MyBatis； 本文主要介绍基于JPA方式的基础封装思路。@anarkh

  • ▶SpringBoot集成MySQL - 基于JPA的封装
  • 知识准备
  • MySQL相关
  • JPA相关
  • 接口相关
  • 实现案例
  • 准备DB
  • 定义实体
  • DAO层
  • Service层
  • BaseService
  • UserService
  • RoleService
  • Controller层
  • 运行测试
  • 示例源码

知识准备 ​

需要对MySQL，JPA以及接口封装有了解。

MySQL相关 ​

JPA相关 ​

接口相关 ​

  • SpringBoot接口 - 如何统一接口封装
  • 在以SpringBoot开发Restful接口时，统一返回方便前端进行开发和封装，以及出现时给出响应编码和信息。
  • SpringBoot接口 - 如何对参数进行校验
  • 在以SpringBoot开发Restful接口时, 对于接口的查询参数后台也是要进行校验的，同时还需要给出校验的返回信息放到上文我们统一封装的结构中。那么如何优雅的进行参数的统一校验呢？
  • SpringBoot接口 - 如何参数校验国际化
  • 上文我们学习了如何对SpringBoot接口进行参数校验，但是如果需要有国际化的信息，应该如何优雅处理呢？
  • SpringBoot接口 - 如何统一异常处理
  • SpringBoot接口如何对异常进行统一封装，并统一返回呢？以上文的参数校验为例，如何优雅的将参数校验的错误信息统一处理并封装返回呢？
  • SpringBoot接口 - 如何提供多个版本接口
  • 在以SpringBoot开发Restful接口时，由于模块，系统等业务的变化，需要对同一接口提供不同版本的参数实现（老的接口还有模块或者系统在用，不能直接改，所以需要不同版本）。如何更加优雅的实现多版本接口呢？
  • SpringBoot接口 - 如何生成接口文档
  • SpringBoot开发Restful接口，有什么API规范吗？如何快速生成API文档呢？
  • SpringBoot接口 - 如何访问外部接口
  • 在SpringBoot接口开发中，存在着本模块的代码需要访问外面模块接口或外部url链接的需求, 比如调用外部的地图API或者天气API。那么有哪些方式可以调用外部接口呢？
  • SpringBoot接口 - 如何对接口进行加密
  • 在以SpringBoot开发后台API接口时，会存在哪些接口不安全的因素呢？通常如何去解决的呢？本文主要介绍API接口有不安全的因素 以及常见的保证接口安全的方式 ，重点实践如何对接口进行签名 。
  • SpringBoot接口 - 如何保证接口幂等
  • 在以SpringBoot开发Restful接口时，如何防止接口的重复提交呢？ 本文主要介绍接口幂等相关的知识点，并实践常见基于Token实现接口幂等。
  • SpringBoot接口 - 如何实现接口限流之单实例
  • 在以SpringBoot开发Restful接口时，当流量超过服务极限能力时，系统可能会出现卡死、崩溃的情况，所以就有了降级和限流。在接口层如何做限流呢？ 本文主要回顾限流的知识点，并实践单实例限流的一种思路。
  • SpringBoot接口 - 如何实现接口限流之分布式
  • 上文中介绍了单实例下如何在业务接口层做限流，本文主要介绍分布式场景下限流的方案，以及什么样的分布式场景下需要在业务层加限流而不是接入层; 并且结合kailing开源的ratelimiter-spring-boot-starter在新窗口打开为例， 学习思路+代码封装+starter封装 。

实现案例 ​

本例主要简单示例下基于JPA DAO/Service层封装， 并且注意下如下例子MySQL是5.7版本，8.x版本相关例子也在示例源码在新窗口打开中。

准备DB ​

创建MySQL的schema test_db, 导入SQL 文件如下

sql
;
;
;
;
;
;
;
;
;
;





DROP TABLE IF EXISTS `tb_role`;
;
;
CREATE TABLE `tb_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `role_key` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
;





LOCK TABLES `tb_role` WRITE;
;
INSERT INTO `tb_role` VALUES (1,'admin','admin','admin','2021-09-08 17:09:15','2021-09-08 17:09:15');
;
UNLOCK TABLES;





DROP TABLE IF EXISTS `tb_user`;
;
;
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone_number` int(11) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
;





LOCK TABLES `tb_user` WRITE;
;
INSERT INTO `tb_user` VALUES (1,'anarkh','dfasdf','suzhou.daipeng@gmail.com',1212121213,'afsdfsaf','2021-09-08 17:09:15','2021-09-08 17:09:15');
;
UNLOCK TABLES;





DROP TABLE IF EXISTS `tb_user_role`;
;
;
CREATE TABLE `tb_user_role` (
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
;





LOCK TABLES `tb_user_role` WRITE;
;
INSERT INTO `tb_user_role` VALUES (1,1);
;
UNLOCK TABLES;
;

;
;
;
;
;
;
;

引入maven依赖

java
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- jpa-spec --->
<dependency>
    <groupId>com.github.wenhao</groupId>
    <artifactId>jpa-spec</artifactId>
    <version>3.1.0</version>
</dependency>

增加yml配置

yaml
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test_db?useSSL=false&autoReconnect=true&characterEncoding=utf8
    driver-class-name: com.mysql.jdbc.Driver
    username: root
    password: xxxxxxxxx
    initial-size: 20
    max-idle: 60
    max-wait: 10000
    min-idle: 10
    max-active: 200
  jpa:
    generate-ddl: false
    show-sql: false
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQLDialect
        format_sql: true
        use-new-id-generator-mappings: false

定义实体 ​

USER/ROLE

BaseEntity

java
package tech.anarkh.springboot.mysql57.jpa.entity;

import java.io.Serializable;


public interface BaseEntity extends Serializable {
}

User

java
package tech.anarkh.springboot.mysql57.jpa.entity;

import java.time.LocalDateTime;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;


@Getter
@Setter
@ToString
@Entity
@Table(name = "tb_user")
public class User implements BaseEntity {

    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    
    private String userName;

    
    private String password;

    
    private String email;

    
    private long phoneNumber;

    
    private String description;

    
    private LocalDateTime createTime;

    
    private LocalDateTime updateTime;

    
    @ManyToMany(cascade = {CascadeType.REFRESH}, fetch = FetchType.EAGER)
    @JoinTable(name = "tb_user_role", joinColumns = {
            @JoinColumn(name = "user_id")}, inverseJoinColumns = {@JoinColumn(name = "role_id")})
    private Set&lt;Role&gt; roles;

}

Role

java
package tech.anarkh.springboot.mysql57.jpa.entity;

import java.time.LocalDateTime;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;


@Getter
@Setter
@ToString
@Entity
@Table(name = "tb_role")
public class Role implements BaseEntity {

    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    
    private String name;

    
    private String roleKey;

    
    private String description;

    
    private LocalDateTime createTime;

    
    private LocalDateTime updateTime;

}

DAO层 ​

BaseDao

java
package tech.anarkh.springboot.mysql57.jpa.dao;

import java.io.Serializable;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.NoRepositoryBean;
import tech.anarkh.springboot.mysql57.jpa.entity.BaseEntity;


@NoRepositoryBean
public interface IBaseDao<T extends BaseEntity, I extends Serializable>
        extends JpaRepository&lt;T, I&gt;, JpaSpecificationExecutor&lt;T&gt; {
}

UserDao

java
package tech.anarkh.springboot.mysql57.jpa.dao;

import org.springframework.stereotype.Repository;
import tech.anarkh.springboot.mysql57.jpa.entity.User;


@Repository
public interface IUserDao extends IBaseDao&lt;User, Long&gt; {

}

RoleDao

java
package tech.anarkh.springboot.mysql57.jpa.dao;

import org.springframework.stereotype.Repository;
import tech.anarkh.springboot.mysql57.jpa.entity.Role;


@Repository
public interface IRoleDao extends IBaseDao&lt;Role, Long&gt; {

}

Service层 ​

BaseService ​

封装BaseService

java
package tech.anarkh.springboot.mysql57.jpa.service;

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

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;


public interface IBaseService<T, I extends Serializable> {

    
    T find(I id);

    
    List&lt;T&gt; findAll();

    
    List&lt;T&gt; findList(I[] ids);

    
    List&lt;T&gt; findList(Iterable&lt;I&gt; ids);

    
    Page&lt;T&gt; findAll(Pageable pageable);

    
    Page&lt;T&gt; findAll(Specification&lt;T&gt; spec, Pageable pageable);

    
    T findOne(Specification&lt;T&gt; spec);

    
    long count();

    
    long count(Specification&lt;T&gt; spec);

    
    boolean exists(I id);

    
    void save(T entity);

    
    void save(List&lt;T&gt; entities);

    
    T update(T entity);

    
    void delete(I id);

    
    void deleteByIds(List&lt;I&gt; ids);

    
    void delete(T[] entities);

    
    void delete(Iterable&lt;T&gt; entities);

    
    void delete(T entity);

    
    void deleteAll();

    
    List&lt;T&gt; findList(Specification&lt;T&gt; spec);

    
    List&lt;T&gt; findList(Specification&lt;T&gt; spec, Sort sort);


    
    void flush();

}

BaseService实现类

java
package tech.anarkh.springboot.mysql57.jpa.service.impl;

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

import javax.transaction.Transactional;

import lombok.extern.slf4j.Slf4j;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import tech.anarkh.springboot.mysql57.jpa.dao.IBaseDao;
import tech.anarkh.springboot.mysql57.jpa.entity.BaseEntity;
import tech.anarkh.springboot.mysql57.jpa.service.IBaseService;


@Slf4j
@Transactional
public abstract class BaseDoServiceImpl<T extends BaseEntity, I extends Serializable> implements IBaseService&lt;T, I&gt; {

    
    public abstract IBaseDao&lt;T, I&gt; getBaseDao();

    
    @Override
    public T find(I id) {
        return getBaseDao().findById(id).orElse(null);
    }

    
    @Override
    public List&lt;T&gt; findAll() {
        return getBaseDao().findAll();
    }

    
    @Override
    public List&lt;T&gt; findList(I[] ids) {
        List&lt;I&gt; idList = Arrays.asList(ids);
        return getBaseDao().findAllById(idList);
    }

    
    @Override
    public List&lt;T&gt; findList(Specification&lt;T&gt; spec) {
        return getBaseDao().findAll(spec);
    }

    
    @Override
    public List&lt;T&gt; findList(Specification&lt;T&gt; spec, Sort sort) {
        return getBaseDao().findAll(spec, sort);
    }

    
    @Override
    public T findOne(Specification&lt;T&gt; spec) {
        return getBaseDao().findOne(spec).orElse(null);
    }

    
    @Override
    public Page&lt;T&gt; findAll(Pageable pageable) {
        return getBaseDao().findAll(pageable);
    }

    
    @Override
    public long count() {
        return getBaseDao().count();
    }

    
    @Override
    public long count(Specification&lt;T&gt; spec) {
        return getBaseDao().count(spec);
    }

    
    @Override
    public boolean exists(I id) {
        return getBaseDao().findById(id).isPresent();
    }

    
    @Override
    public void save(T entity) {
        getBaseDao().save(entity);
    }

    
    @Override
    public void save(List&lt;T&gt; entities) {
        getBaseDao().saveAll(entities);
    }

    
    @Override
    public T update(T entity) {
        return getBaseDao().saveAndFlush(entity);
    }

    
    @Override
    public void delete(I id) {
        getBaseDao().deleteById(id);
    }

    
    @Override
    public void deleteByIds(List&lt;I&gt; ids) {
        getBaseDao().deleteAllById(ids);
    }

    
    @Override
    public void deleteAll() {
        getBaseDao().deleteAllInBatch();
    }

    
    @Override
    public void delete(T[] entities) {
        List&lt;T&gt; tList = Arrays.asList(entities);
        getBaseDao().deleteAll(tList);
    }

    
    @Override
    public void delete(Iterable&lt;T&gt; entities) {
        getBaseDao().deleteAll(entities);
    }

    
    @Override
    public void delete(T entity) {
        getBaseDao().delete(entity);
    }

    
    @Override
    public List&lt;T&gt; findList(Iterable&lt;I&gt; ids) {
        return getBaseDao().findAllById(ids);
    }

    
    @Override
    public Page&lt;T&gt; findAll(Specification&lt;T&gt; spec, Pageable pageable) {
        return getBaseDao().findAll(spec, pageable);
    }

    
    @Override
    public void flush() {
        getBaseDao().flush();
    }

}

UserService ​

UserService接口定义

java
package tech.anarkh.springboot.mysql57.jpa.service;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import tech.anarkh.springboot.mysql57.jpa.entity.query.UserQueryBean;
import tech.anarkh.springboot.mysql57.jpa.entity.User;


public interface IUserService extends IBaseService&lt;User, Long&gt; {

    
    Page&lt;User&gt; findPage(UserQueryBean userQueryBean, PageRequest pageRequest);

}

UserService实现类

java
package tech.anarkh.springboot.mysql57.jpa.service.impl;


import com.github.wenhao.jpa.Specifications;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import tech.anarkh.springboot.mysql57.jpa.dao.IBaseDao;
import tech.anarkh.springboot.mysql57.jpa.dao.IUserDao;
import tech.anarkh.springboot.mysql57.jpa.entity.User;
import tech.anarkh.springboot.mysql57.jpa.entity.query.UserQueryBean;
import tech.anarkh.springboot.mysql57.jpa.service.IUserService;

@Service
public class UserDoServiceImpl extends BaseDoServiceImpl&lt;User, Long&gt; implements IUserService {

    
    private final IUserDao userDao;

    
    public UserDoServiceImpl(final IUserDao userDao2) {
        this.userDao = userDao2;
    }

    
    @Override
    public IBaseDao&lt;User, Long&gt; getBaseDao() {
        return this.userDao;
    }

    
    @Override
    public Page&lt;User&gt; findPage(UserQueryBean queryBean, PageRequest pageRequest) {
        Specification&lt;User&gt; specification = Specifications.<User>and()
                .like(StringUtils.isNotEmpty(queryBean.getName()), "user_name", queryBean.getName())
                .like(StringUtils.isNotEmpty(queryBean.getDescription()), "description",
                        queryBean.getDescription())
                .build();
        return this.getBaseDao().findAll(specification, pageRequest);
    }

}

RoleService ​

RoleService接口定义

java
package tech.anarkh.springboot.mysql57.jpa.service;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import tech.anarkh.springboot.mysql57.jpa.entity.Role;
import tech.anarkh.springboot.mysql57.jpa.entity.query.RoleQueryBean;

public interface IRoleService extends IBaseService&lt;Role, Long&gt; {

    
    Page&lt;Role&gt; findPage(RoleQueryBean roleQueryBean, PageRequest pageRequest);

}

RoleService实现类

java
package tech.anarkh.springboot.mysql57.jpa.service.impl;

import com.github.wenhao.jpa.Specifications;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import tech.anarkh.springboot.mysql57.jpa.dao.IBaseDao;
import tech.anarkh.springboot.mysql57.jpa.dao.IRoleDao;
import tech.anarkh.springboot.mysql57.jpa.entity.Role;
import tech.anarkh.springboot.mysql57.jpa.entity.query.RoleQueryBean;
import tech.anarkh.springboot.mysql57.jpa.service.IRoleService;

@Service
public class RoleDoServiceImpl extends BaseDoServiceImpl&lt;Role, Long&gt; implements IRoleService {

    
    private final IRoleDao roleDao;

    
    public RoleDoServiceImpl(final IRoleDao roleDao2) {
        this.roleDao = roleDao2;
    }

    
    @Override
    public IBaseDao&lt;Role, Long&gt; getBaseDao() {
        return this.roleDao;
    }

    
    @Override
    public Page&lt;Role&gt; findPage(RoleQueryBean roleQueryBean, PageRequest pageRequest) {
        Specification&lt;Role&gt; specification = Specifications.<Role>and()
                .like(StringUtils.isNotEmpty(roleQueryBean.getName()), "name",
                        roleQueryBean.getName())
                .like(StringUtils.isNotEmpty(roleQueryBean.getDescription()), "description",
                        roleQueryBean.getDescription())
                .build();
        return this.roleDao.findAll(specification, pageRequest);
    }

}

Controller层 ​

UserController

java
package tech.anarkh.springboot.mysql57.jpa.controller;


import java.time.LocalDateTime;

import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import tech.anarkh.springboot.mysql57.jpa.entity.User;
import tech.anarkh.springboot.mysql57.jpa.entity.query.UserQueryBean;
import tech.anarkh.springboot.mysql57.jpa.entity.response.ResponseResult;
import tech.anarkh.springboot.mysql57.jpa.service.IUserService;


@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private IUserService userService;

    
    @ApiOperation("Add/Edit User")
    @PostMapping("add")
    public ResponseResult&lt;User&gt; add(User user) {
        if (user.getId()==null || !userService.exists(user.getId())) {
            user.setCreateTime(LocalDateTime.now());
            user.setUpdateTime(LocalDateTime.now());
            userService.save(user);
        } else {
            user.setUpdateTime(LocalDateTime.now());
            userService.update(user);
        }
        return ResponseResult.success(userService.find(user.getId()));
    }


    
    @ApiOperation("Query User One")
    @GetMapping("edit/{userId}")
    public ResponseResult&lt;User&gt; edit(@PathVariable("userId") Long userId) {
        return ResponseResult.success(userService.find(userId));
    }

    
    @ApiOperation("Query User Page")
    @GetMapping("list")
    public ResponseResult<Page&lt;User&gt;> list(@RequestParam int pageSize, @RequestParam int pageNumber) {
        return ResponseResult.success(userService.findPage(UserQueryBean.builder().build(), PageRequest.of(pageNumber, pageSize)));
    }
}

运行测试 ​

查询一个

查询分页列表

示例源码 ​

https://github.com/realanarkh/tech-anarkh-spring-demos