上篇文章中已經介紹了JPA表達式的用法,以及form表單的查詢. 下面這篇文章將繼續(xù)講述JPA表達式的封裝用法. 讓開發(fā)更為簡潔
首先感謝
wenhao的 https://github.com/wenhao/jpa-spec的項目,當時無意中在git上看到這個開源項目, 本作者已經分裝了關于JPA的很多用法,非常的棒,跟作者也學到了很多! 當前開源組件用法可以去作者git上查看.
由于本人寫接口習慣性和前端擴展聯(lián)系到一起(職業(yè)病), 因為以上組件都是在后臺服務中寫死的查詢參數,
以及查詢方式等.我很不喜歡這樣的方式. 所以我又突然奇想,想和前端傳輸的JSON參數整合,形成動態(tài)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查詢,系統(tǒng)默認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組件二次開發(fā),調用起來更為簡單.
例子:
實體類 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;
/**
* 創(chuàng)建人
*/
@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生成的動態(tài)sql為 select * from label where labelEnum in
('DEFAULT','TEMPLATE') and isDeleted ='N' order by labelEnum
asc, userCount desc limit 0,10
以上就是我封裝的, 使用起來更是方便呢, 對于復雜的多對多,一對多等復雜關系,都可以進行復雜的查詢,以及動態(tài)參數的拼接,前端只需傳參,后臺不需改變!是不是很省力省時省工呢
結束語: 當然這個有個小問題,就是關于時間的格式查詢, 這里模式是 年月日,時分秒,沒有進行過多的格式判斷.
大家有什么新奇的想法,或者在使用過程中有什么問題,請及時告訴我,謝謝了
