1. 关于JPA争议
这里不讨论JPA和mybatis的好处,智者见智仁者见仁吧. 个人比较喜欢JPA的操作.
可能开发效率上更为方便吧.节省很多的时间
2. JPA简单的用法
1. 用过JPA的同学,都知道JPA有简单方法,就是驼峰式方法命名规范,这是一种入门级的API.
2. 第二种查询可以用@Query注解,进行查询,同样注解方式也有两种方式
1> 原生HQL方式
2> 采用原生SQL方式(这种对于动态参数的支持不是很好,有时间写一章关于原生sql的方法)
3. 稍微高级的用法就是JPA投影.(稍后介绍)
4. JPA高级用法就是Specification表达式.这篇文章专门花时间介绍下这个表达式
3. 普通表达式用法
3.1 这里用一个简单例子介绍下表达式的普通用法
1. 定义一个实体类
@Entity
@Table(name = "t_article")
@Data
public class Article implements Serializable{
private static final long serialVersionUID = 6112067846581696118L;
@Id
@GeneratedValue
private Integer aid;
private String title;
private Date postTime;
private Date lastEditTime;
private String ip;
private String tag;
private boolean forbidComment;//禁止评论
@ManyToOne
@JoinColumn(name = "uid")
private User user;
}
2. 定义用户表
@Entity
@Table(name = "t_user")
@Data
public class User implements Serializable {
private static final long serialVersionUID = 3703405133265901053L;
@Id
@GeneratedValue
private Integer uid;
private String nickname;
private String password;
}
3. 如果定义一个controller接口, 进行查询用户和文章的关系,那么咱们定义一个实体类
public class SearchArticle implements Serializable {
private static final long serialVersionUID = -1082122462716689486L;
private int page = 1;
private int limit;
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME,pattern = "yyyy-MM-dd HH:mm:ss")
private Date postTimeStart;
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME,pattern = "yyyy-MM-dd HH:mm:ss")
private Date postTimeEnd;
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME,pattern = "yyyy-MM-dd HH:mm:ss")
private Date recTimeStart;
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME,pattern = "yyyy-MM-dd HH:mm:ss")
private Date recTimeEnd;
private String nickname;
}
4. 定义一个文章接口
public interface ArticleRepository extends JpaRepository<Article,Integer>,JpaSpecificationExecutor<Article> {}
这里不需要实现任何接口
5. 定义查询接口,实现类
@Autowired
private ArticleRepository articleRepository;
@Override
public QueryResult<ArticleModel> findArticle(SearchArticle searchArticle) {
Sort sort = new Sort(Sort.Direction.DESC,"postTime");
Specification<Article> specification = getWhereClause(searchArticle);
Page<Article> all = articleRepository.findAll(specification, new PageRequest(searchArticle.getPage() - 1, searchArticle.getLimit(),sort));
QueryResult<ArticleModel> result = new QueryResult<>();
List<ArticleModel> list = new ArrayList<>(searchArticle.getLimit());
for (Article article:all.getContent()){
ArticleModel model = new ArticleModel(article.getAid(),article.getTitle(),article.getPostTime(),article.isRecommend(),
article.getRecommendTime(),article.getIp(),article.getUser().getUid(),article.getUser().getNickname());
list.add(model);
}
result.setRows(list);
result.setTotal(all.getTotalElements());
return result;
}
/**
* 动态生成where语句
* @param searchArticle
* @return
*/
private Specification<Article> getWhereClause(final SearchArticle searchArticle){
return new Specification<Article>() {
@Override
public Predicate toPredicate(Root<Article> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> predicate = new ArrayList<>();
if(searchArticle.getPostTimeStart()!=null){
predicate.add(cb.greaterThanOrEqualTo(root.get("postTime").as(Date.class), searchArticle.getPostTimeStart()));
}
if(searchArticle.getPostTimeEnd()!=null){
predicate.add(cb.lessThanOrEqualTo(root.get("postTime").as(Date.class), searchArticle.getPostTimeEnd()));
}
if(searchArticle.getRecTimeStart()!=null){
predicate.add(cb.greaterThanOrEqualTo(root.get("recommendTime").as(Date.class), searchArticle.getRecTimeStart()));
}
if (searchArticle.getRecTimeEnd()!=null){
predicate.add(cb.lessThanOrEqualTo(root.get("recommendTime").as(Date.class), searchArticle.getRecTimeEnd()));
}
if (StringUtils.isNotBlank(searchArticle.getNickname())){
//两张表关联查询
Join<Article,User> userJoin = root.join(root.getModel().getSingularAttribute("user",User.class),JoinType.LEFT);
predicate.add(cb.like(userJoin.get("nickname").as(String.class), "%" + searchArticle.getNickname() + "%"));
}
Predicate[] pre = new Predicate[predicate.size()];
return query.where(predicate.toArray(pre)).getRestriction();
}
};
}
这样的表达式就已经完成了.但是代码量实在是太大了.如果每次都来一次这样的查询,那么service将是很庞大的.
就算公共的方法抽出来,也不是一个很好地方式.
4. 由于每次都写这样的代码.太辛苦了.下面是花时间进行简单的封装
1. 当前的封装,支持一个多表关联查询,并且只支持AND查询,同时支持分页,order by,group by等
2. 而且当前封装和前端结合, 前端只需动态的传入json参数,后台进行动态解析成SQL,进行查询,
如果前端加了一个参数,后台是无须进行修改代码逻辑的.这一点还是很方便的.
4.1 下面进行代码展示
1. 公共参数接受类
public class QueryConditionVo {
private List<ConditionField> conditions = Lists.newArrayList();
private Pagination pagination = new Pagination();
private Map<String, String> sort = Maps.newHashMap();
private String params;
public QueryConditionVo() {
ConditionField stateCondition = new ConditionField();
stateCondition.setName("isDeleted");
stateCondition.setOperator("EQ");
stateCondition.setValue("N");
conditions.add(stateCondition);
}
public ConditionField getConditionField(String key) {
return conditions.stream().filter(condition -> condition.getName().equals(key)).findFirst().orElse(null);
}
public String getParams() {
return params;
}
public void setParams(String params) {
this.params = params;
}
public List<ConditionField> getConditions() {
return conditions;
}
public void setConditions(List<ConditionField> conditions) {
this.conditions = conditions;
}
public Pagination getPagination() {
return pagination;
}
public void setPagination(Pagination pagination) {
this.pagination = pagination;
}
public Map<String, String> getSort() {
return sort;
}
public void setSort(Map<String, String> sort) {
this.sort = sort;
}
@Override
public String toString() {
return "QueryConditionVo{" +
"conditions=" + conditions +
", pagination=" + pagination +
", sort=" + sort +
'}';
}
}
2. 分页类
@Data
public class Pagination {
private int page = 0;
private int size = 10;
}
3. 属性查询类
@Data
public class ConditionField {
//属性
private String name;
//类似sql中的判断
private String operator;
//值
private String value;
public ConditionField() {
}
public ConditionField(String name, String operator, String value) {
this.name = name;
this.operator = operator;
this.value = value;
}
public static ConditionField deleteConditionField() {
ConditionField deleteCondition = new ConditionField();
deleteCondition.setName("isDeleted");
deleteCondition.setOperator("eq");
deleteCondition.setValue("N");
return deleteCondition;
}
}
4. 查询SQL时定义的方法
public class CriteriaCondition {
public enum Operator {
EQ, NEQ, LIKE, GT, LT, GE, LE, NULL, IN
}
private String fieldName;
private Object value;
private Operator operator;
public CriteriaCondition(String fieldName, Operator operator, Object value) {
this.fieldName = fieldName;
this.value = value;
this.operator = operator;
}
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public Object getValue() {
return value;
}
public void setValue(Object value) {
this.value = value;
}
public Operator getOperator() {
return operator;
}
public void setOperator(Operator operator) {
this.operator = operator;
}
}
5. 解析JSON为JPA表达式类
public class QueryHandler<T> {
private static final Logger logger = LoggerFactory.getLogger(QueryHandler.class);
private QueryConditionVo queryConditionVo;
public QueryHandler(QueryConditionVo queryConditionVo) {
this.queryConditionVo = queryConditionVo;
}
public Specification<T> buildSpecificationOr(final Class clazz) {
return new Specification<T>() {
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
List<Predicate> predicates = new ArrayList<Predicate>();
Predicate predicate = null;
In in = null;
check(root, criteriaBuilder, predicates, predicate, in, clazz);
if (CollectionUtils.isEmpty(predicates)) {
return predicate;
}
predicate = criteriaBuilder.or(predicates.toArray(new Predicate[predicates.size()]));
return predicate;
}
};
}
public Specification<T> buildSpecification(final Class clazz, String... params) {
return new Specification<T>() {
@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
List<Predicate> predicates = new ArrayList<Predicate>();
Predicate predicate = null;
In in = null;
check(root, criteriaBuilder, predicates, predicate, in, clazz);
predicate = criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
if (params != null && params.length > 0) {
for (String param : params) {
criteriaQuery.groupBy(root.get(param));
}
}
return predicate;
}
};
}
public Specification<T> buildSpecification(final Class clazz) {
return buildSpecification(clazz, null);
}
private void check(Root<T> root, CriteriaBuilder criteriaBuilder, List<Predicate> predicates, Predicate predicate, In in, Class clazz) {
List<CriteriaCondition> criteriaConditions = getCriteriaConditionList();
for (CriteriaCondition filter : criteriaConditions) {
Object value = filter.getValue();
String fieldName = filter.getFieldName();
Path path = null;
if (fieldName.contains(".")) {
String[] fields = fieldName.split("\\.");
if (fields.length == 2) {
path = root.join(fields[0]).get(fields[1]);
}
if (fields.length == 3) {
path = root.join(fields[0]).join(fields[1]).get(fields[2]);
}
if (fields.length == 4) {
path = root.join(fields[0]).join(fields[1]).join(fields[2]).get(fields[3]);
}
} else {
path = root.get(fieldName);
Field field = null;
try {
field = clazz.getDeclaredField(fieldName);
} catch (NoSuchFieldException e) {
//不存在的field,尝试从父类获取
try {
field = clazz.getSuperclass().getDeclaredField(fieldName);
} catch (Exception e1) {
continue;
}
}
if (field.getType().isEnum()) {
value = Enum.valueOf((Class<Enum>) field.getType(), value.toString());
}
if (field.getType().isInstance(new Timestamp(System.currentTimeMillis()))) {
if (!"null".equals(value)) {
value = Timestamp.valueOf(value.toString());
}
}
}
switch (filter.getOperator()) {
case GE:
predicate = criteriaBuilder.greaterThanOrEqualTo(path, (Comparable) value);
break;
case GT:
predicate = criteriaBuilder.greaterThan(path, (Comparable) value);
break;
case LT:
predicate = criteriaBuilder.lessThan(path, (Comparable) value);
break;
case LE:
predicate = criteriaBuilder.lessThanOrEqualTo(path, (Comparable) value);
break;
case NEQ:
predicate = criteriaBuilder.notEqual(path, value);
break;
case EQ: {
predicate = criteriaBuilder.equal(path, value);
break;
}
case NULL:
predicate = criteriaBuilder.isNull(path);
break;
case LIKE:
predicate = criteriaBuilder.like(path, "%" + value + "%");
break;
case IN:
in = criteriaBuilder.in(path);
if (value != null) {
String inStr = value.toString();
String[] vals = inStr.split(",");
for (String val : vals) {
try {
Long _v = Long.parseLong(val);
in.value(_v);
} catch (Exception e) {
in.value(val);
}
}
}
break;
default:
break;
}
if (predicate != null) {
predicates.add(predicate);
}
if (in != null) {
predicates.add(in);
}
}
}
/**
* 生成分页、排序条件
*
* @return
*/
public Pageable buildPage() {
Pagination pagination = queryConditionVo.getPagination();
Map<String, String> sort = queryConditionVo.getSort();
if (pagination == null) {
return null;
}
if (queryConditionVo.getSort() == null) {
return PageRequest.of(pagination.getPage(), pagination.getSize());
} else {
Sort sortObject = null;
for (Map.Entry<String, String> entry : sort.entrySet()) {
Sort tempSort = parseSortCondition(entry.getKey(), entry.getValue());
if (tempSort == null) {
continue;
}
sortObject = (sortObject == null) ? tempSort : (sortObject.and(tempSort));
}
if (sortObject != null) {
return PageRequest.of(pagination.getPage(), Math.min(50, pagination.getSize()), sortObject);
} else {
return PageRequest.of(pagination.getPage(), Math.min(50, pagination.getSize()));
}
}
}
/**
* 生成查询条件
*
* @return
*/
public List<CriteriaCondition> getCriteriaConditionList() {
List<ConditionField> conditions = queryConditionVo.getConditions();
if (conditions == null) {
return null;
}
List<CriteriaCondition> criteriaConditions = new ArrayList<CriteriaCondition>();
for (ConditionField field : conditions) {
CriteriaCondition filter = parseSearchFilter(field);
if (filter == null) {
continue;
}
criteriaConditions.add(filter);
}
return criteriaConditions;
}
/**
* 解析查询条件
*
* @param field
* @return
*/
private CriteriaCondition parseSearchFilter(ConditionField field) {
CriteriaCondition.Operator operator = null;
if (StringUtils.equalsIgnoreCase(field.getOperator(), "like")) {
operator = CriteriaCondition.Operator.LIKE;
} else if (StringUtils.equalsIgnoreCase(field.getOperator(), "le")) {
operator = CriteriaCondition.Operator.LE;
} else if (StringUtils.equalsIgnoreCase(field.getOperator(), "lt")) {
operator = CriteriaCondition.Operator.LT;
} else if (StringUtils.equalsIgnoreCase(field.getOperator(), "eq")) {
operator = CriteriaCondition.Operator.EQ;
} else if (StringUtils.equalsIgnoreCase(field.getOperator(), "neq")) {
operator = CriteriaCondition.Operator.NEQ;
} else if (StringUtils.equalsIgnoreCase(field.getOperator(), "gt")) {
operator = CriteriaCondition.Operator.GT;
} else if (StringUtils.equalsIgnoreCase(field.getOperator(), "ge")) {
operator = CriteriaCondition.Operator.GE;
} else if (StringUtils.equalsIgnoreCase(field.getOperator(), "null")) {
operator = CriteriaCondition.Operator.NULL;
} else if (StringUtils.equalsIgnoreCase(field.getOperator(), "in")) {
operator = CriteriaCondition.Operator.IN;
}
if (operator != null) {
return new CriteriaCondition(field.getName(), operator, field.getValue());
}
return null;
}
/**
* 生成排序条件
*
* @param key
* @param value
* @return
*/
private Sort parseSortCondition(String key, String value) {
if (StringUtils.equalsIgnoreCase(value, "asc")) {
return new Sort(Sort.Direction.ASC, key);
} else if (StringUtils.equalsIgnoreCase(value, "desc")) {
return new Sort(Sort.Direction.DESC, key);
}
return null;
}
}
以上5个大类就是我封装的类, 前端传入JSON参数,后台动态解析SQL,进行实体查询,下面给出例子
1. controller层
@PostMapping("/list")
public ResponseEntity<String> list(@RequestBody String body) {
logger.info("模板分页查询:{}", body);
QueryConditionVo queryConditionVo = JSONObject.parseObject(body, QueryConditionVo.class);
return response(templateService.list(queryConditionVo));
}
这里的response是我封装在父类的一个函数
2. service层
public Page<Template> list(QueryConditionVo queryConditionVo) {
QueryHandler<Template> templateQueryHandler = new QueryHandler<>(queryConditionVo);
return templateRepository.findAll(templateQueryHandler.buildSpecification(Template.class), templateQueryHandler.buildPage());
}
3. 前端参数传输
{
"conditions": [{
"name": "isDeleted",
"operator": "EQ",
"value": "N"
}, {
"name": "labels.labelName",
"operator": "EQ",
"value": "标签1"
},{
"name": "templateState",
"operator": "EQ",
"value": "SPECIAL/BASE"
},{
"name": "templateName",
"operator": "LIKE",
"value": "模板名称"
}],
"pagination": {
"page": 0,
"size": 10
},
"sort": {
"gmtCreate": "desc",
"pageView": "desc"
}
}
conditions:节点就是查询参数,当前JSON节点意思就是select * from template left join label on tempate.id= label.template_id where isDelete='N' and label_name = '标签一' and tempalteState ='BASE' and templateName like '%名称%'.
pagination: 分页参数,JPA默认从0开始
sort: 排序
5. JPA表达式总结
1. 原生表达式写法,直观,但是代码量比较多,而且如果前端查询参数扩展的话,后台也是需要跟着改变.有点麻烦
2. 当前的封装,简化了form表单的查询,同时支持分页,动态参数,排序,分组.调用起来也是几行代码的事情.
说完优点,就是缺点了. 缺点就是当前的封装只是支持全部AND查询,全部OR查询,并不是很友好的支持and,or同时共用, 不过大家不用担心. 我已经做出了改造. 下一篇文章会
JPA原生SQL也会专门写一篇文章进行简述
大家在用JPA的时候会怎么玩呢..欢迎交流哦
网友评论