Springboot JPA实现对数据库表统一的增删改查
首先建立一个公共的BaseEntity对应数据库中的表格公共字段，其他Entity都从它继承：
@Data
@MappedSuperclass
public class BaseEntity implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "_id")
protected Long id;
@Column(name = "create_time", columnDefinition = "datetime COMMENT '创建时间'")
@Temporal(TIMESTAMP)
@CreationTimestamp
protected Date createTime;
@Column(name = "update_time", columnDefinition = "datetime COMMENT '修改时间时间'")
@Temporal(TIMESTAMP)
@UpdateTimestamp
protected Date updateTime;
}
然后再建立一个公共的BaseDto，用于对应BaseEntity的各字段：
@Data
public class BaseDTO implements Serializable {
protected Long id;
@JSONField(format = "yyyy-MM-dd HH:mm:ss")
protected Date createdTime;
@JSONField(format = "yyyy-MM-dd HH:mm:ss")
protected Date updateTime;
public String[] uniqueKeys() {
return null;
}
}
还得建立一个BaseRepository的Repository接口，用于操作数据库：
@NoRepositoryBean
public interface BaseRepository<T> extends JpaRepository<T, Long>, JpaSpecificationExecutor<T>, QueryByExampleExecutor<T> {
}
最后建立BaseController类（或者BaseService也可以，后续根据控制类还是服务类去继承就好了），用于实现公共的增删改查操作：
public class BaseController<E extends BaseEntity, S extends BaseRepository<E>, T extends BaseDTO> {
private final Logger logger = LoggerFactory.getLogger(BaseController.class);
private String classTName;
private S repo;
@Autowired
private EntityManager entityManager;
public BaseController(Class<?> classT, S repo){
this.classTName = classT.getSimpleName();
this.repo = repo;
}
public E dto2Entity(T dto){ //实际调用时将被子类同名方法替换
logger.error("Shoud not be called.");
return null;
}
public T entity2Dto(E entity){ //实际调用时将被子类同名方法替换
logger.error("Shoud not be called.");
return null;
}
@RequestMapping(value = "/all", method = {RequestMethod.GET})
public BaseBodyResponse all() {
logger.info("All {}.", classTName);
List<E> entityList = repo.findAll();
List<T> dtoList = new ArrayList<>();
if(entityList != null) {
for (E entity : entityList) {
T dto = entity2Dto(entity);
dtoList.add(dto);
}
}
return new BaseBodyResponse(true, "", dtoList);
}
//只支持String类型的字段，根据字段名返回distinct list<string>。
@RequestMapping(value = "/allbyfield", method = {RequestMethod.GET})
public BaseBodyResponse allByField(@RequestParam String field) {
logger.info("AllByField {}:{}", classTName, field);
Type genType = this.getClass().getGenericSuperclass();
if(!(genType instanceof ParameterizedType)) {
return new BaseBodyResponse(false, "Server error!");
}
Type[] params = ((ParameterizedType) genType).getActualTypeArguments();
if (!(params[0] instanceof Class)){
return new BaseBodyResponse(false, "Server error!");
}
Class entityClass = (Class)params[0]; //0就是就是本类的第一个参数模板类，即E。
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<String> query = cb.createQuery(String.class);
Root<String> root = query.from(entityClass);
query.multiselect(root.get(field)).distinct(true);
query.where(cb.equal(cb.literal(1), 1));
List<String> fieldValueList = entityManager.createQuery(query).getResultList();
return new BaseBodyResponse(true, "", fieldValueList);
}
@RequestMapping(value = "/add", method = {RequestMethod.POST})
public BaseResponse add(@RequestBody T model) {
logger.info("Add {}:{}", classTName, JSON.toJSONString(model));
BaseResponse resp = new BaseResponse(false, "添加失败");
T uniqueObj = getUniqueObj(model);
if (null != uniqueObj) {
logger.error("Data has exist, {}", JSON.toJSONString(model));
resp.update(false, "数据已经存在");
}else {
model.setId(null);
repo.save(this.dto2Entity(model));
resp.update(true, "添加成功");
}
return resp;
}
private T getUniqueObj(T model) {
String[] uniqueKeys = model.uniqueKeys();
if (uniqueKeys != null && uniqueKeys.length > 0) {
Specification<E> specification = (Root<E> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
List<Predicate> predicateList = new ArrayList<>();
for (String fieldName : uniqueKeys) {
Object fieldValue = getFieldValueByName(model, fieldName);
predicateList.add(cb.equal(root.get(fieldName), fieldValue));
}
if(predicateList != null && !predicateList.isEmpty()) {
Predicate[] predicateArray = new Predicate[predicateList.size()];
predicateList.toArray(predicateArray);
query.where(cb.and(predicateArray));
}
return query.getRestriction();
};
Optional<E> entity = repo.findOne(specification); //实际specification在这里才会去计算。
if(entity.isPresent()) {
return this.entity2Dto(entity.get());
}
}
return null;
}
private Object getFieldValueByName(Object o, String fieldName){
String firstLetter = fieldName.substring(0, 1).toUpperCase();
String getter = "get" + firstLetter + fieldName.substring(1);
try {
Method method = o.getClass().getMethod(getter, new Class[]{});
Object value = method.invoke(o, new Object[]{});
return value;
}catch (Exception e){
logger.error("Exception: "+e.getMessage(), e);
}
return null;
}
@RequestMapping(value = "/delete", method = {RequestMethod.GET})
public BaseResponse delete(@RequestParam Long id) {
logger.info("Delete {}:{}", classTName, id);
Optional<E> entity = repo.findById(id);
if (entity.isPresent()) {
repo.deleteById(id);
return new BaseResponse(true, "删除成功");
} else {
return new BaseResponse(false, "数据不存在");
}
}
@RequestMapping(value = "/edit", method = {RequestMethod.POST})
public BaseResponse edit(@RequestBody T model) {
logger.info("Edit {} :{}", classTName, JSON.toJSONString(model));
BaseResponse resp = new BaseResponse(false, "修改失败");
T uniqueObj = getUniqueObj(model);
if (null != uniqueObj && !uniqueObj.getId().equals(model.getId())) {
logger.error("Data has exist And ID not match", JSON.toJSONString(model));
resp.setMessage("数据冲突");
} else {
E entity = this.dto2Entity(model);
repo.save(entity);
resp.update(true, "修改成功");
}
return resp;
}
@RequestMapping(value = "/load", method = {RequestMethod.GET})
public BaseBodyResponse load(@RequestParam Long id) {
logger.info("Load {}:{}", classTName, id);
Optional<E> entity = repo.findById(id);
if(entity.isPresent()) {
T dto = this.entity2Dto(entity.get());
return new BaseBodyResponse(true, "", dto);
}else {
return new BaseBodyResponse(false, "数据不存在");
}
}
@GetMapping("/page")
public YunResult page(
@RequestParam(name = "pageSize", required = true) Integer pageSize,
@RequestParam(name = "pageNum", required = true) Integer pageNum,
@RequestParam(name = "total", required = false) Integer total,
@RequestParam(name = "sidx", required = false) List<String> sidx, //排序字段名
@RequestParam(name = "sord", required = false) List<String> sord, // desc 或 asc
@RequestParam(name = "filters", required = false) String filters // 过滤字段
) {
// pageSize=15&pageNum=1
// pageSize=15&pageNum=1&total=0&sidx=XX&sord=DESC&sidx=YY&sord=ASC&filters=
// pageSize=15&pageNum=1&filters=%7B%22groupOp%22%3A%22AND%22%2C%22rules%22%3A%5B%7B%22groupOp%22%3A%22or%22%2C%22rules%22%3A%5B%7B%22field%22%3A%22channel%22%2C%22op%22%3A%22eq%22%2C%22data%22%3A%22facebook%22%7D%2C%7B%22field%22%3A%22opResult%22%2C%22op%22%3A%22eq%22%2C%22data%22%3A%22ALL%22%7D%5D%7D%2C%7B%22field%22%3A%22channel%22%2C%22op%22%3A%22eq%22%2C%22data%22%3A%22facebook%22%7D%2C%7B%22field%22%3A%22status%22%2C%22op%22%3A%22eq%22%2C%22data%22%3Atrue%7D%5D%7D
// 上面的filters参数是json方式，然后urlEncode编码后的值。
logger.info("Page {}: pageSize: {}, pageNum: {}, total: {}, sidx: {}, sord: {}, filters: {}",
classTName, pageSize, pageNum, total, sidx, sord, filters);
//处理Page分页 、Sort排序 和Specification过滤
PageRequest pageRequest = this.getPageRequest(pageSize, pageNum, sidx, sord);
Specification<E> specification = this.getSpecification(filters);
//处理结果
Page<E> page = repo.findAll(specification, pageRequest); //实际specification在这里才会去计算。
SimplePageView<T> pageView = new SimplePageView<>(pageNum, pageSize);
if (page != null) {
List<T> dtoList = new ArrayList<>();
List<E> entityList = page.getContent();
for(E entity: entityList){
dtoList.add(this.entity2Dto(entity));
}
pageView.setData(dtoList);
pageView.setTotal(page.getTotalElements());
} else {
pageView.setData(null);
pageView.setTotal(0L);
}
return YunResult.success(pageView);
}
//处理Page分页 和Sort排序
public PageRequest getPageRequest(Integer pageSize, Integer pageNum, List<String> sidx, List<String> sord){
PageRequest pageRequest = null;
if(sidx != null && (!sidx.isEmpty())) {
List<Sort.Order> orderList = new ArrayList<>();
for(int i = 0; i< sidx.size(); i++) {
Sort.Order order = null;
if(sord != null && (!sord.isEmpty()) && sord.get(i) != null) {
order = new Sort.Order(Sort.Direction.fromString(sord.get(i)), sidx.get(i));
} else{
order = new Sort.Order(Sort.Direction.ASC, sidx.get(i));
}
orderList.add(order);
}
Sort sort = Sort.by(orderList);
pageRequest = PageRequest.of(pageNum - 1, pageSize, sort);
}else{
pageRequest = PageRequest.of(pageNum - 1, pageSize);
}
return pageRequest;
}
//处理Specification过滤（SQL的where字句）
//说明：
//输入例子： {"groupOp":"AND","rules":[{"groupOp":"or","rules":[{"field":"channel","op":"eq","data":"facebook"},{"field":"opResult","op":"eq","data":"ALL"}]},{"field":"channel","op":"eq","data":"facebook"},{"field":"status","op":"eq","data":true}]}
//相当于： where (channel=facebook or op_result=ALL) and channel=facebook and status=TRUE
//枚举字段值要用'String'方式映射；boolean字段值要用json的true/false来传入。
//支持的op操作(不区分大小写)：is null, is not null, eq, neq, like, not like, in, not in, gt, ge, lt, le, 不支持between。
//支持的grougOp操作(不区分大小写)：and, or, not。
public Specification<E> getSpecification(String filters) {
return (Root<E> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
Predicate predicate = this.buildPredicateRecursion(filters, root, cb);
//Predicate predicateTrue = cb.equal(cb.literal(1), 1);
if(predicate != null) {
query.where(cb.and(predicate));
return query.getRestriction();
}
return null;
};
}
private Predicate buildPredicateRecursion(String filters,
Root<E> root, CriteriaBuilder cb) {
Predicate predicate = null;
if(StringUtils.isNoneBlank(filters)) {
JSONObject jsonObject = JSONObject.parseObject(filters);
String groupOp = jsonObject.getString("groupOp");
if(StringUtils.isNoneBlank(groupOp)){
JSONArray rules = (JSONArray) jsonObject.get("rules");
if(rules != null){
List<Predicate> predicateListTmp = new ArrayList<Predicate>();
for(int i=0 ;i < rules.size(); i++){
JSONObject rule = rules.getJSONObject(i);
String groupOpTmp = rule.getString("groupOp");
Predicate predicateTmp = null;
if(StringUtils.isNoneBlank(groupOpTmp)) { //递归处理带groupOn的条件，实际就是实现了括号操作符的作用
predicateTmp = this.buildPredicateRecursion(rule.toString(), root, cb);
}else { //处理不带groupOn的条件
predicateTmp = this.getPredicate(root, cb, rule);
}
if(predicateTmp != null) {
predicateListTmp.add(predicateTmp);
}else{
logger.error("{} predicateTmp is null or empty.", classTName);
}
}
if(predicateListTmp != null && !predicateListTmp.isEmpty()) {
Predicate[] predicateArray = new Predicate[predicateListTmp.size()];
predicateListTmp.toArray(predicateArray);
switch (groupOp.toLowerCase()) {
case "and":
predicate = cb.and(predicateArray);
break;
case "or":
predicate = cb.or(predicateArray);
break;
case "not":
predicate = cb.not(predicateArray[0]);
break;
default:
logger.error("{} Not support groupOn: {}", classTName, groupOp);
break;
}
}
}else{
logger.error("{} rules is null or empty.", classTName);
}
}else {
logger.error("{} groupOn is null or empty.", classTName);
}
}
return predicate;
}
private Predicate getPredicate(Root<E> root, CriteriaBuilder cb, JSONObject jsonObject){
String field = jsonObject.getString("field");
String op = jsonObject.getString("op");
Object data = jsonObject.get("data");
String className = jsonObject.get("data").getClass().getSimpleName();
Predicate predicate = null;
switch (op.toLowerCase()) {
case "is null":
predicate = cb.isNull(root.get(field));
break;
case "is not null":
predicate = cb.isNotNull(root.get(field));
break;
case "eq":
predicate = cb.equal(root.get(field), data);
break;
case "neq":
predicate = cb.notEqual(root.get(field), data);
break;
case "like":
predicate = cb.like(root.get(field).as(String.class), jsonObject.getString("data"));
break;
case "not like":
predicate = cb.notLike(root.get(field).as(String.class), jsonObject.getString("data"));
break;
case "in":
if(className.toLowerCase().equals("string")) {
CriteriaBuilder.In<String> inClause = cb.in(root.get(field).as(String.class));
for (String inStr : jsonObject.getString(field).split(",")) {
inClause.value(inStr);
}
predicate = inClause;
}else{
logger.error("{} Only support string type for 'in' op.", classTName);
}
break;
case "not in":
if(className.toLowerCase().equals("string")) {
CriteriaBuilder.In<String> inClause = cb.in(root.get(field).as(String.class));
for (String inStr : jsonObject.getString(field).split(",")) {
inClause.value(inStr);
}
predicate = cb.not(inClause);
}else{
logger.error("{} Only support string type for 'in' op.", classTName);
}
break;
case "gt":
switch (className.toLowerCase()) {
case "integer":
Integer dataInt = jsonObject.getInteger("data");
predicate = cb.gt(root.get(field).as(Integer.class), dataInt);
break;
case "long":
Long dataLong = jsonObject.getLong("data");
predicate = cb.gt(root.get(field).as(Long.class), dataLong);
break;
case "double":
Double dataDouble = jsonObject.getDouble("data");
predicate = cb.gt(root.get(field).as(Double.class), dataDouble);
break;
case "float":
Float dataFloat = jsonObject.getFloat("data");
predicate = cb.gt(root.get(field).as(Float.class), dataFloat);
break;
default:
logger.error("{} Not support calssName: {}", classTName, className);
}
break;
case "ge":
switch (className.toLowerCase()) {
case "integer":
Integer dataInt = jsonObject.getInteger("data");
predicate = cb.ge(root.get(field).as(Integer.class), dataInt);
break;
case "long":
Long dataLong = jsonObject.getLong("data");
predicate = cb.ge(root.get(field).as(Long.class), dataLong);
break;
case "double":
Double dataDouble = jsonObject.getDouble("data");
predicate = cb.ge(root.get(field).as(Double.class), dataDouble);
break;
case "float":
Float dataFloat = jsonObject.getFloat("data");
predicate = cb.gt(root.get(field).as(Float.class), dataFloat);
break;
default:
logger.error("{} Not support calssName: {}", classTName, className);
}
break;
case "lt":
switch (className.toLowerCase()) {
case "integer":
Integer dataInt = jsonObject.getInteger("data");
predicate = cb.lt(root.get(field).as(Integer.class), dataInt);
break;
case "long":
Long dataLong = jsonObject.getLong("data");
predicate = cb.lt(root.get(field).as(Long.class), dataLong);
break;
case "double":
Double dataDouble = jsonObject.getDouble("data");
predicate = cb.lt(root.get(field).as(Double.class), dataDouble);
break;
case "float":
Float dataFloat = jsonObject.getFloat("data");
predicate = cb.gt(root.get(field).as(Float.class), dataFloat);
break;
default:
logger.error("{} Not support calssName: {}", classTName, className);
}
break;
case "le":
switch (className.toLowerCase()) {
case "integer":
Integer dataInt = jsonObject.getInteger("data");
predicate = cb.le(root.get(field).as(Integer.class), dataInt);
break;
case "long":
Long dataLong = jsonObject.getLong("data");
predicate = cb.le(root.get(field).as(Long.class), dataLong);
break;
case "double":
Double dataDouble = jsonObject.getDouble("data");
predicate = cb.le(root.get(field).as(Double.class), dataDouble);
break;
case "float":
Float dataFloat = jsonObject.getFloat("data");
predicate = cb.gt(root.get(field).as(Float.class), dataFloat);
break;
default:
logger.error("{} Not support calssName: {}", classTName, className);
}
break;
default:
logger.error("{} Not support op: {}", classTName, op);
break;
}
return predicate;
}
}
后续具体到各个表时扩展上面的各个类即可，比如有个Test表需要增删改查，那么：
TestEntity类 extends BaseEntity；
TestDto类 extends BaseDto;
TestRepo类 extends BaseRepository<TestEntity>
控制类：
@RestController
@RequestMapping("/a_database/test")
public class TestController extends BaseController<TestEntity, TestEntityRepo, TestDto>{
private final Logger logger = LoggerFactory.getLogger(TestController.class);
private TestEntityRepo repo;
public TestController(TestEntityRepo repo){
super(TestController.class, repo);
this.repo = repo;
}
@Override
public TestEntity dto2Entity(TestDto dto){ //必须实现
TestEntity entity = new TestEntity();
BeanUtils.copyProperties(dto, entity);
return entity;
}
@Override
public TestDto entity2Dto(TestEntity entity){ //必须实现
TestDto dto = new TestDto();
BeanUtils.copyProperties(entity, dto);
return dto;
}
}
说明：上述代码中的BaseResponse、BaseBodyResponse、YunResult只是用来装载返回的数据的，就不再列出代码了。
测试：
根据主键id得到某条具体记录：http://127.0.0.1:80/a_database/test/load?id=2
其他分页page、edit、add、delete、all、allbyfield操作类似，参考BaseController代码即可。