上篇文章中已经介绍了JPA表达式的用法,以及form表单的查询. 下面这篇文章将继续讲述JPA表达式的封装用法. 让开发更为简洁
首先感谢
wenhao的 https://github.com/wenhao/jpa-spec的项目,当时无意中在git上看到这个开源项目, 本作者已经分装了关于JPA的很多用法,非常的棒,跟作者也学到了很多! 当前开源组件用法可以去作者git上查看.
由于本人写接口习惯性和前端扩展联系到一起(职业病), 因为以上组件都是在后台服务中写死的查询参数,
以及查询方式等.我很不喜欢这样的方式. 所以我又突然奇想,想和前端传输的JSON参数整合,形成动态SQL进行查询,
废话不多,直接开始!
基于# jpa-spec 二次封装
- 排序类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Order {
/** 排序方式 desc asc **/
private Sort.Direction sort;
/** 属性名称 **/
private String name;
}
- 接受JSON参数类
@Data
public class SpecificationParam {
/**当前属性与其他属性是否是and查询,或者or查询,系统默认and查询
* 如: select * from table where a =1 and b = 1
* select * from table where a = 1 and (b=2 or c = 34)
**/
private Predicate.BooleanOperator ct = Predicate.BooleanOperator.AND;
/**查询方式**/
private Operation operation;
/**参数值**/
private List<Object> params;
/** 参数属性 **/
private String name;
}
- 查询方式枚举
public enum Operation {
BW,//bwteen
EQ,//equal
GE,//greaterThanOrEqualTo
GT,//greaterThan
IN,
LE,//lessThanOrEqualTo
LK,//like
LT,//lessThan
NE,//not equal
NI,//not in
NL;//not like
}
- 反射工具类
public class ReflectionUtils {
/**
* 获取类以及父类的属性类型
*
* @param clazz
* @return
*/
public static ArrayList<Class> getAllFieldClazzs(Class<?> clazz) {
ArrayList<Class> classs = Lists.newArrayList();
List<Class> tempClasss = null;
while (!clazz.equals(Object.class)) {
tempClasss = Arrays.stream(clazz.getDeclaredFields()).map(Field::getType).collect(Collectors.toList());
classs.addAll(tempClasss);
clazz = clazz.getSuperclass();
}
return classs;
}
/**
* 获取类里面指定的属性类型，检测到list类型属性会自动获取其泛型
*
* @param clazz
* @param name
* @return
*/
public static Class getAllFieldClass(Class<?> clazz, String name) {
Field field = null;
while (!clazz.equals(Object.class)) {
try {
field = clazz.getDeclaredField(name);
break;
} catch (NoSuchFieldException e) {
clazz = clazz.getSuperclass();
try {
field = clazz.getDeclaredField(name);
} catch (NoSuchFieldException e1) {
e1.printStackTrace();
}
}
}
return field.getType().equals(List.class) ? getListGenericClass(field) : field.getType();
}
/**
* 获取类里面指定的属性类型，检测到list类型属性会自动获取其泛型，并且可获取list泛型里面的指定属性
*
* @param clazz
* @param name
* @return
*/
public static Class getFieldClass(Class<?> clazz, String name) {
String[] nameList = name.split("\\.");
for (String tempName : nameList) {
clazz = getAllFieldClass(clazz, tempName);
}
return clazz;
}
/**
* 获取list的泛型类所有属性
*
* @param field
* @return
*/
public static Class getListGenericClass(Field field) {
ParameterizedType listGenericType = (ParameterizedType) field.getGenericType();
Type[] listActualTypeArguments = listGenericType.getActualTypeArguments();
return (Class) listActualTypeArguments[0];
}
/**
* 获取list的泛型类指定属性
*
* @param clazz
* @param name
* @return
*/
public static Class getListGenericClass(Class<?> clazz, String name) {
Field listField = null;
try {
listField = clazz.getDeclaredField(name);
} catch (Exception e) {
e.printStackTrace();
}
ParameterizedType listGenericType = (ParameterizedType) listField.getGenericType();
Type[] listActualTypeArguments = listGenericType.getActualTypeArguments();
return (Class) listActualTypeArguments[0];
}
}
- 时间枚举类
public enum DateFormat {
YEAR_MONTH_DAY_HOURS_MIN_SEC("yyyy-MM-dd HH:mm:ss");
private String dateFormat;
DateFormat(String dateFormat) {
this.dateFormat = dateFormat;
}
public String getDateFormat(){
return this.dateFormat;
}
}
- 查询实体类--表达式封装类
@Data
public class SearchEntity<T> {
private Integer page = 0;
private Integer size = 10;
private Class clazz;
private Set<Order> orders;
private List<SpecificationParam> params;
public Pageable getPageable() {
return PageRequest.of(page, size, this.getSorts(orders));
}
public Sort getSorts(Set<Order> orders) {
if(CollectionUtils.isEmpty(orders)){
return null;
}
List<Sort.Order> orderList = orders.stream().map(order -> new Sort.Order(order.getSort(), order.getName())).collect(Collectors.toList());
return Sort.by(orderList);
}
public Specification<T> getSpecification(Class clazz) {
this.clazz = clazz;
Specification result = null;
for (SpecificationParam sp : params) {
if (Predicate.BooleanOperator.AND.equals(sp.getCt())) {
if (Objects.isNull(result)) {
result = builderBehavior(Specifications.and(), sp).build();
} else {
result = result.and(builderBehavior(Specifications.and(), sp).build());
}
} else {
if (Objects.isNull(result)) {
result = builderBehavior(Specifications.or(), sp).build();
} else {
result = result.or(builderBehavior(Specifications.or(), sp).build());
}
}
}
return result;
}
private PredicateBuilder builderBehavior(PredicateBuilder predicateBuilder, SpecificationParam sp) {
PredicateBuilder result = null;
sp.setParams(parseParams(sp.getName(), sp.getParams()));
switch (sp.getOperation()) {
case BW:
result = predicateBuilder.between(sp.getName(), sp.getParams().get(0), sp.getParams().get(1));
break;
case EQ:
result = predicateBuilder.eq(sp.getName(), sp.getParams().stream().toArray());
break;
case GE:
result = predicateBuilder.ge(sp.getName(), (Comparable) sp.getParams().stream().findFirst().get());
break;
case GT:
result = predicateBuilder.gt(sp.getName(), (Comparable) sp.getParams().stream().findFirst().get());
break;
case IN:
result = predicateBuilder.in(sp.getName(), sp.getParams().stream().toArray());
break;
case LE:
result = predicateBuilder.le(sp.getName(), (Comparable) sp.getParams().stream().findFirst().get());
break;
case LK:
result = predicateBuilder.like(sp.getName(), sp.getParams().stream().toArray(String[]::new));
break;
case LT:
result = predicateBuilder.lt(sp.getName(), (Comparable) sp.getParams().stream().findFirst().get());
break;
case NE:
result = predicateBuilder.ne(sp.getName(), sp.getParams().stream().toArray());
break;
case NI:
result = predicateBuilder.notIn(sp.getName(), sp.getParams().stream().toArray());
break;
case NL:
result = predicateBuilder.notLike(sp.getName(), sp.getParams().stream().toArray(String[]::new));
break;
}
return result;
}
private List<Object> parseParams(String name, List<Object> params) {
Class clazz_ = ReflectionUtils.getFieldClass(clazz, name);
List<Object> resultParams = Lists.newArrayListWithCapacity(2);
if (clazz_.isEnum()) {
for (Object param : params) {
resultParams.add(Enum.valueOf(clazz_, param.toString()));
}
} else if (clazz_.equals(Date.class)) {
try {
for (Object param : params) {
String[] dateFormats = Lists.newArrayList(DateFormat.values()).stream().map(DateFormat::getDateFormat).collect(Collectors.toList()).stream().toArray(String[]::new);
resultParams.add(DateUtils.parseDate(param.toString(), dateFormats));
}
} catch (ParseException e) {
e.printStackTrace();
}
}
return Iterables.isEmpty(resultParams) ? params : resultParams;
}
}
以上代码就是基于# wenhao/jpa-spec组件二次开发,调用起来更为简单.
例子:
实体类 Label.java Template.java
@Data
@Entity
@Table(name = "m_label")
public class Label extends BaseEntity {
@Id
@GeneratedValue(generator = "system-uuid")
@GenericGenerator(name = "system-uuid", strategy = "uuid")
@Column(length = 64)
private String id;
/**
* 标签类型
*/
@Enumerated(EnumType.STRING)
private LabelEnum labelEnum;
/**
* 标签名称
*/
private String labelName;
/**
* 使用次数
*/
private Long useCount = 0L;
/**
* 标签填充色
*/
private String labelColor;
/**
* 标签填充色
*/
private String labelBoardColor;
/**
* 字体颜色
*/
private String labelFontColor;
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinTable(name = "m_label_template",
joinColumns = @JoinColumn(name = "label_id", referencedColumnName = "id"),
inverseJoinColumns = @JoinColumn(name = "template_id", referencedColumnName = "id"))
@JSONField(serialize = false)
private List<Template> templates;
@Data
@Entity
@Table(name = "m_template")
public class Template extends BaseEntity {
@Id
@GeneratedValue(generator = "system-uuid")
@GenericGenerator(name = "system-uuid", strategy = "uuid")
@Column(length = 64)
private String id;
/**
* 样式类型,0表示基础型、1表示特色型
*/
@Enumerated(EnumType.STRING)
private TemplateState templateState;
/**
* 样式文件
*/
private String templateFile;
/**
* 样式名称
*/
private String templateName;
/**
* 样式描述
*/
private String templateRemark;
/**
* 创建人
*/
@OneToOne
private User createBy;
/**
* 模板文件路径
*/
private String templateFileURL;
/**
* 模板文件名称
*/
private String templateFileName;
/**
* 模板缩略图地址
*/
private String templateIconURL;
/**
* 模板缩略图名称
*/
private String templateIconName;
/**
* 背景色，用于前端展示 浏览量
*/
private String backgroundColor;
/**
* 页面浏览量
*/
private String pageView;
@ManyToMany(fetch = FetchType.LAZY, mappedBy = "templates")
@NotFound(action = NotFoundAction.IGNORE)
//@JSONField(serialize = false)
private List<Label> labels;
}
Controller 接口
@PostMapping("/list")
@Authorization
protected ResponseEntity<String> response(@RequestBody SearchEntity<Label> searchEntity ) {
logger.info("标签列表:{}", JSON.toJSONString(searchEntity));
ResultBuilder list = labelService.list(searchEntity);
return response(list);
}
}
service接口
public ResultBuilder list(SearchEntity<Label> searchEntity) {
Page<Label> all = labelRepository.findAll(searchEntity.getSpecification(Label.class),searchEntity.getPageable());
return ResultBuilder.success().build(all);
}
前端参数传输
{
"orders": [{
"name": "labelEnum",
"sort": "ASC"
}, {
"name": "useCount",
"sort": "DESC"
}],
"params": [{
"name": "labelEnum",
"operation": "IN",
"params": ["DEFAULT", "TEMPLATE"]
}, {
"name": "isDeleted",
"operation": "EQ",
"params": ["N"]
}],
"page": 0,
"size": 10
}
当前JSON生成的动态sql为 select * from label where labelEnum in
('DEFAULT','TEMPLATE') and isDeleted ='N' order by labelEnum
asc, userCount desc limit 0,10
以上就是我封装的, 使用起来更是方便呢, 对于复杂的多对多,一对多等复杂关系,都可以进行复杂的查询,以及动态参数的拼接,前端只需传参,后台不需改变!是不是很省力省时省工呢
结束语: 当然这个有个小问题,就是关于时间的格式查询, 这里模式是 年月日,时分秒,没有进行过多的格式判断.
大家有什么新奇的想法,或者在使用过程中有什么问题,请及时告诉我,谢谢了
网友评论