做个简单的网站多少钱,什么是企业营销型网站?,一般做网站的在哪里找,安宁区网站制作SpringBoot第23讲#xff1a;SpringBoot集成MySQL - 基于JPA的封装 在实际开发中#xff0c;最为常见的是基于数据库的CRUD封装等#xff0c;比如SpringBoot集成MySQL数据库#xff0c;常用的方式有JPA和MyBatis#xff1b; 本文是SpringBoot第23讲#xff0c;主要介绍基…SpringBoot第23讲SpringBoot集成MySQL - 基于JPA的封装 在实际开发中最为常见的是基于数据库的CRUD封装等比如SpringBoot集成MySQL数据库常用的方式有JPA和MyBatis 本文是SpringBoot第23讲主要介绍基于JPA方式的基础封装思路。 文章目录 SpringBoot第23讲SpringBoot集成MySQL - 基于JPA的封装1、知识准备1.1、MySQL相关1.2、JPA相关1.3、接口相关 2、实现案例2.1、准备DB2.2、定义实体2.3、DAO层2.4、Service层1、BaseService2、UserService3、RoleService 2.5、Controller层2.6、运行测试 3、示例源码 1、知识准备 需要对MySQLJPA以及接口封装有了解。 1.1、MySQL相关 MySQL第一讲MySQL索引规范 MySQL第六讲MySQL语法基础(库表操作/子查询/事务/权限管理) MySQL第三讲数据库基础面试题汇总(mysql调优/底层B tree机制/sql执行计划详解/索引优化详解/sql语句优化)
1.2、JPA相关
SpringBoot入门 - 添加内存数据库H2
1.3、接口相关
SpringBoot第11讲SpringBoot 如何统一接口封装 在以SpringBoot开发Restful接口时统一返回方便前端进行开发和封装以及出现时给出响应编码和信息。 SpringBoot第12讲SpringBoot接口如何对参数进行校验 在以SpringBoot开发Restful接口时, 对于接口的查询参数后台也是要进行校验的同时还需要给出校验的返回信息放到上文我们统一封装的结构中。 SpringBoot第13讲SpringBoot接口如何参数校验国际化 上文我们学习了如何对SpringBoot接口进行参数校验但是如果需要有国际化的信息应该如何优雅处理呢 SpringBoot第14讲SpringBoot 如何统一异常处理 SpringBoot接口如何对异常进行统一封装并统一返回呢以上文的参数校验为例如何优雅的将参数校验的错误信息统一处理并封装返回呢 SpringBoot第15讲SpringBoot如何提供多个版本接口 在以SpringBoot开发Restful接口时由于模块系统等业务的变化需要对同一接口提供不同版本的参数实现老的接口还有模块或者系统在用不能直接改所以需要不同版本。如何更加优雅的实现多版本接口呢 SpringBoot第16讲: 如何生成接口文档 SpringBoot开发Restful接口有什么API规范吗如何快速生成API文档呢 SpringBoot第18讲SpringBoot 如何访问外部接口 在SpringBoot接口开发中存在着本模块的代码需要访问外面模块接口或外部url链接的需求, 比如调用外部的地图API或者天气API。那么有哪些方式可以调用外部接口呢 SpringBoot第20讲SpringBoot如何对接口进行签名 在以SpringBoot开发后台API接口时会存在哪些接口不安全的因素呢通常如何去解决的呢本文主要介绍API接口有不安全的因素以及常见的保证接口安全的方式重点实践如何对接口进行签名。 SpringBoot第19讲SpringBoot 如何保证接口幂等 在以SpringBoot开发Restful接口时如何防止接口的重复提交呢 本文主要介绍接口幂等相关的知识点并实践常见基于Token实现接口幂等。 SpringBoot第21讲SpringBoot如何实现接口限流之单实例 在以SpringBoot开发Restful接口时当流量超过服务极限能力时系统可能会出现卡死、崩溃的情况所以就有了降级和限流。在接口层如何做限流呢 本文主要回顾限流的知识点并实践单实例限流的一种思路。 SpringBoot第22讲SpringBoot如何实现接口限流之分布式 上文中介绍了单实例下如何在业务接口层做限流本文主要介绍分布式场景下限流的方案以及什么样的分布式场景下需要在业务层加限流而不是接入层; 并且结合kailing开源的ratelimiter-spring-boot-starter为例 学习思路代码封装starter封装。
2、实现案例 本例主要简单示例下基于JPA DAO/Service层封装 并且注意下如下例子MySQL是5.7版本8.x版本相关例子也在示例源码中。 2.1、准备DB
创建MySQL的schema test_db, 导入SQL 文件如下
DROP TABLE IF EXISTS tb_role;
/*!40101 SET saved_cs_client character_set_client */;
/*!40101 SET character_set_client utf8 */;
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)
) ENGINEInnoDB AUTO_INCREMENT2 DEFAULT CHARSETutf8;
/*!40101 SET character_set_client saved_cs_client */;--
-- Dumping data for table tb_role
--LOCK TABLES tb_role WRITE;
/*!40000 ALTER TABLE tb_role DISABLE KEYS */;
INSERT INTO tb_role VALUES (1,admin,admin,admin,2021-09-08 17:09:15,2021-09-08 17:09:15);
/*!40000 ALTER TABLE tb_role ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table tb_user
--DROP TABLE IF EXISTS tb_user;
/*!40101 SET saved_cs_client character_set_client */;
/*!40101 SET character_set_client utf8 */;
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)
) ENGINEInnoDB AUTO_INCREMENT2 DEFAULT CHARSETutf8;
/*!40101 SET character_set_client saved_cs_client */;--
-- Dumping data for table tb_user
--LOCK TABLES tb_user WRITE;
/*!40000 ALTER TABLE tb_user DISABLE KEYS */;
INSERT INTO tb_user VALUES (1,qiwenjie,123456,1172814226qq.com,1212121213,afsdfsaf,2021-09-08 17:09:15,2021-09-08 17:09:15);
/*!40000 ALTER TABLE tb_user ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table tb_user_role
--DROP TABLE IF EXISTS tb_user_role;
/*!40101 SET saved_cs_client character_set_client */;
/*!40101 SET character_set_client utf8 */;
CREATE TABLE tb_user_role (user_id int(11) NOT NULL,role_id int(11) NOT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8;
/*!40101 SET character_set_client saved_cs_client */;--
-- Dumping data for table tb_user_role
--LOCK TABLES tb_user_role WRITE;
/*!40000 ALTER TABLE tb_user_role DISABLE KEYS */;
INSERT INTO tb_user_role VALUES (1,1);
/*!40000 ALTER TABLE tb_user_role ENABLE KEYS */;
UNLOCK TABLES;引入maven依赖
dependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdversion8.0.28/version
/dependency
dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-data-jpa/artifactId
/dependency
!-- jpa-spec ---
dependencygroupIdcom.github.wenhao/groupIdartifactIdjpa-spec/artifactIdversion3.1.0/version
/dependency增加yml配置
spring:datasource:url: jdbc:mysql://localhost:3306/db_user?useSSLfalseautoReconnecttruecharacterEncodingutf8driver-class-name: com.mysql.cj.jdbc.Driverusername: rootpassword: qwj930828initial-size: 20max-idle: 60max-wait: 10000min-idle: 10max-active: 200jpa:generate-ddl: falseshow-sql: falseproperties:hibernate:dialect: org.hibernate.dialect.MySQLDialectformat_sql: trueuse-new-id-generator-mappings: false2.2、定义实体 USER/ROLE BaseEntity
package springboot.mysql8.jpa.entity;import java.io.Serializable;/*** author qiwenjie*/
public interface BaseEntity extends Serializable {
}User
package springboot.mysql8.jpa.entity;import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import javax.persistence.*;
import java.time.LocalDateTime;
import java.util.Set;/*** author qiwenjie*/
Getter
Setter
ToString
Entity
Table(name tb_user)
public class User implements BaseEntity {/*** user id.*/IdGeneratedValue(strategy GenerationType.IDENTITY)Column(name id, nullable false)private Long id;/*** username.*/private String userName;/*** user pwd.*/private String password;/*** email.*/private String email;/*** phoneNumber.*/private long phoneNumber;/*** description.*/private String description;/*** create date time.*/private LocalDateTime createTime;/*** update date time.*/private LocalDateTime updateTime;/*** join to role table.*/ManyToMany(cascade {CascadeType.REFRESH}, fetch FetchType.EAGER)JoinTable(name tb_user_role, joinColumns {JoinColumn(name user_id)}, inverseJoinColumns {JoinColumn(name role_id)})private SetRole roles;
}Role
package springboot.mysql8.jpa.entity;import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import javax.persistence.*;
import java.time.LocalDateTime;/*** author qiwenjie*/
Getter
Setter
ToString
Entity
Table(name tb_role)
public class Role implements BaseEntity {/*** role id.*/IdGeneratedValue(strategy GenerationType.IDENTITY)Column(name id, nullable false)private Long id;/*** role name.*/private String name;/*** role key.*/private String roleKey;/*** description.*/private String description;/*** create date time.*/private LocalDateTime createTime;/*** update date time.*/private LocalDateTime updateTime;
}2.3、DAO层
BaseDao
package springboot.mysql8.jpa.dao;import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.NoRepositoryBean;
import springboot.mysql8.jpa.entity.BaseEntity;
import java.io.Serializable;/*** author qiwenjie*/
NoRepositoryBean
public interface IBaseDaoT extends BaseEntity, I extends Serializableextends JpaRepositoryT, I, JpaSpecificationExecutorT {
}UserDao
package springboot.mysql8.jpa.dao;import org.springframework.stereotype.Repository;
import springboot.mysql8.jpa.entity.User;/*** author qiwenjie*/
Repository
public interface IUserDao extends IBaseDaoUser, Long {}RoleDao
package springboot.mysql8.jpa.dao;import org.springframework.stereotype.Repository;
import springboot.mysql8.jpa.entity.Role;/*** author qiwenjie*/
Repository
public interface IRoleDao extends IBaseDaoRole, Long {}2.4、Service层
1、BaseService
封装BaseService
package springboot.mysql8.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;/*** author qiwenjie*/
public interface IBaseServiceT, I extends Serializable {/*** param id id* return T*/T find(I id);/*** return List*/ListT findAll();/*** param ids ids* return List*/ListT findList(I[] ids);/*** param ids ids* return List*/ListT findList(IterableI ids);/*** param pageable pageable* return Page*/PageT findAll(Pageable pageable);/*** param spec spec* param pageable pageable* return Page*/PageT findAll(SpecificationT spec, Pageable pageable);/*** param spec spec* return T*/T findOne(SpecificationT spec);/*** count.** return long*/long count();/*** count.** param spec spec* return long*/long count(SpecificationT spec);/*** exists.** param id id* return boolean*/boolean exists(I id);/*** save.** param entity entity*/void save(T entity);/*** save.** param entities entities*/void save(ListT entities);/*** update.** param entity entity* return T*/T update(T entity);/*** delete.** param id id*/void delete(I id);/*** delete by ids.** param ids ids*/void deleteByIds(ListI ids);/*** delete.** param entities entities*/void delete(T[] entities);/*** delete.** param entities entities*/void delete(IterableT entities);/*** delete.** param entity entity*/void delete(T entity);/*** delete all.*/void deleteAll();/*** find list.** param spec spec* return list*/ListT findList(SpecificationT spec);/*** find list.** param spec spec* param sort sort* return List*/ListT findList(SpecificationT spec, Sort sort);/*** flush.*/void flush();
}BaseService实现类
package springboot.mysql8.jpa.service.impl;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 springboot.mysql8.jpa.dao.IBaseDao;
import springboot.mysql8.jpa.entity.BaseEntity;
import springboot.mysql8.jpa.service.IBaseService;import javax.transaction.Transactional;
import java.io.Serializable;
import java.util.Arrays;
import java.util.List;/*** author qiwenjie*/
Slf4j
Transactional
public abstract class BaseDoServiceImplT extends BaseEntity, I extends Serializable implements IBaseServiceT, I {/*** return IBaseDao*/public abstract IBaseDaoT, I getBaseDao();/*** findById.** param id id* return T*/Overridepublic T find(I id) {return getBaseDao().findById(id).orElse(null);}/*** return List*/Overridepublic ListT findAll() {return getBaseDao().findAll();}/*** param ids ids* return List*/Overridepublic ListT findList(I[] ids) {ListI idList Arrays.asList(ids);return getBaseDao().findAllById(idList);}/*** find list.** param spec spec* return list*/Overridepublic ListT findList(SpecificationT spec) {return getBaseDao().findAll(spec);}/*** find list.** param spec spec* param sort sort* return List*/Overridepublic ListT findList(SpecificationT spec, Sort sort) {return getBaseDao().findAll(spec, sort);}/*** find one.** param spec spec* return T*/Overridepublic T findOne(SpecificationT spec) {return getBaseDao().findOne(spec).orElse(null);}/*** param pageable pageable* return Page*/Overridepublic PageT findAll(Pageable pageable) {return getBaseDao().findAll(pageable);}/*** count.** return long*/Overridepublic long count() {return getBaseDao().count();}/*** count.** param spec spec* return long*/Overridepublic long count(SpecificationT spec) {return getBaseDao().count(spec);}/*** exists.** param id id* return boolean*/Overridepublic boolean exists(I id) {return getBaseDao().findById(id).isPresent();}/*** save.** param entity entity*/Overridepublic void save(T entity) {getBaseDao().save(entity);}/*** save.** param entities entities*/Overridepublic void save(ListT entities) {getBaseDao().saveAll(entities);}/*** update.** param entity entity* return T*/Overridepublic T update(T entity) {return getBaseDao().saveAndFlush(entity);}/*** delete.** param id id*/Overridepublic void delete(I id) {getBaseDao().deleteById(id);}/*** delete by ids.** param ids ids*/Overridepublic void deleteByIds(ListI ids) {getBaseDao().deleteAllById(ids);}/*** delete all.*/Overridepublic void deleteAll() {getBaseDao().deleteAllInBatch();}/*** delete.** param entities entities*/Overridepublic void delete(T[] entities) {ListT tList Arrays.asList(entities);getBaseDao().deleteAll(tList);}/*** delete.** param entities entities*/Overridepublic void delete(IterableT entities) {getBaseDao().deleteAll(entities);}/*** delete.** param entity entity*/Overridepublic void delete(T entity) {getBaseDao().delete(entity);}/*** param ids ids* return List*/Overridepublic ListT findList(IterableI ids) {return getBaseDao().findAllById(ids);}/*** param spec spec* param pageable pageable* return Page*/Overridepublic PageT findAll(SpecificationT spec, Pageable pageable) {return getBaseDao().findAll(spec, pageable);}/*** flush.*/Overridepublic void flush() {getBaseDao().flush();}}2、UserService
UserService接口定义
package springboot.mysql8.jpa.service;import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import springboot.mysql8.jpa.entity.User;
import springboot.mysql8.jpa.entity.query.UserQueryBean;/*** author qiwenjie*/
public interface IUserService extends IBaseServiceUser, Long {/*** find by page.** param userQueryBean query* param pageRequest pageRequest* return page*/PageUser findPage(UserQueryBean userQueryBean, PageRequest pageRequest);
}UserService实现类
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 springboot.mysql8.jpa.dao.IBaseDao;
import springboot.mysql8.jpa.dao.IUserDao;
import springboot.mysql8.jpa.entity.User;
import springboot.mysql8.jpa.entity.query.UserQueryBean;
import springboot.mysql8.jpa.service.IUserService;Service
public class UserDoServiceImpl extends BaseDoServiceImplUser, Long implements IUserService {/*** userDao.*/private final IUserDao userDao;/*** init.** param userDao2 user dao*/public UserDoServiceImpl(final IUserDao userDao2) {this.userDao userDao2;}/*** return base dao*/Overridepublic IBaseDaoUser, Long getBaseDao() {return this.userDao;}/*** find by page.** param queryBean query* param pageRequest pageRequest* return page*/Overridepublic PageUser findPage(UserQueryBean queryBean, PageRequest pageRequest) {SpecificationUser specification Specifications.Userand().like(StringUtils.isNotEmpty(queryBean.getName()), user_name, queryBean.getName()).like(StringUtils.isNotEmpty(queryBean.getDescription()), description,queryBean.getDescription()).build();return this.getBaseDao().findAll(specification, pageRequest);}
}3、RoleService
RoleService接口定义
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import springboot.mysql8.jpa.entity.Role;
import springboot.mysql8.jpa.entity.query.RoleQueryBean;public interface IRoleService extends IBaseServiceRole, Long {/*** find page by query.** param roleQueryBean query* param pageRequest pageRequest* return page*/PageRole findPage(RoleQueryBean roleQueryBean, PageRequest pageRequest);}RoleService实现类
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 springboot.mysql8.jpa.dao.IBaseDao;
import springboot.mysql8.jpa.dao.IRoleDao;
import springboot.mysql8.jpa.entity.Role;
import springboot.mysql8.jpa.entity.query.RoleQueryBean;
import springboot.mysql8.jpa.service.IRoleService;Service
public class RoleDoServiceImpl extends BaseDoServiceImplRole, Long implements IRoleService {/*** roleDao.*/private final IRoleDao roleDao;/*** init.** param roleDao2 role dao*/public RoleDoServiceImpl(final IRoleDao roleDao2) {this.roleDao roleDao2;}/*** return base dao*/Overridepublic IBaseDaoRole, Long getBaseDao() {return this.roleDao;}/*** find page by query.** param roleQueryBean query* param pageRequest pageRequest* return page*/Overridepublic PageRole findPage(RoleQueryBean roleQueryBean, PageRequest pageRequest) {SpecificationRole specification Specifications.Roleand().like(StringUtils.isNotEmpty(roleQueryBean.getName()), name,roleQueryBean.getName()).like(StringUtils.isNotEmpty(roleQueryBean.getDescription()), description,roleQueryBean.getDescription()).build();return this.roleDao.findAll(specification, pageRequest);}
}2.5、Controller层
UserController
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.*;
import springboot.mysql8.jpa.entity.User;
import springboot.mysql8.jpa.entity.query.UserQueryBean;
import springboot.mysql8.jpa.entity.response.ResponseResult;
import springboot.mysql8.jpa.service.IUserService;import java.time.LocalDateTime;/*** author qiwenjie*/
RestController
RequestMapping(/user)
public class UserController {Autowiredprivate IUserService userService;/*** param user user param* return user*/ApiOperation(Add/Edit User)PostMapping(add)public ResponseResultUser 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()));}/*** return user list*/ApiOperation(Query User One)GetMapping(edit/{userId})public ResponseResultUser edit(PathVariable(userId) Long userId) {return ResponseResult.success(userService.find(userId));}/*** return user list*/ApiOperation(Query User Page)GetMapping(list)public ResponseResultPageUser list(RequestParam int pageSize, RequestParam int pageNumber) {return ResponseResult.success(userService.findPage(UserQueryBean.builder().build(), PageRequest.of(pageNumber, pageSize)));}
}2.6、运行测试
查询一个
查询分页列表 3、示例源码
todo
