JPA 使用criteria简单查询工具类方式-FinClip官网

JPA 使用criteria简单查询工具类方式

网友投稿 845 2022-11-14

JPA 使用criteria简单查询工具类方式

目录使用criteria简单查询工具类首先创建类并实现Specification接口新建ExpandCriterion接口使用criteria前提是dao接口必须实现JpaSpecificationExecutor接口打包JPA动态查询(CriteriaQuery) eq、ge、gt封装JPA动态查询（CriteriaQuery）EntityManager管理器，通过spring管理Page分页和结果封装类IBaseDao接口实现了BaseDaoImplIBaseDao接口

使用criteria简单查询工具类

以前用jpa写了一个条件筛选的查询数据如下，才知道那么渣渣，就是一个表，根据前端来筛选数据，写的如下

首先就是判断前端传来的参数就写了那么多，现在才发现是渣渣中的渣渣，而且还费时，用criteria很快就搞定

首先创建类并实现Specification接口

import java.util.ArrayList;

import java.util.List;

import javax.persistence.criteria.CriteriaBuilder;

import javax.persistence.criteria.CriteriaQuery;

import javax.persistence.criteria.Predicate;

import javax.persistence.criteria.Root;

import org.springframework.data.jpa.domain.Specification;

public class ExpandCriteria implements Specification{

private List criterions = new ArrayList();

public Predicate toPredicate(Root root, CriteriaQuery query,

CriteriaBuilder builder) {

if (!criterions.isEmpty()) {

List predicates = new ArrayList();

for(ExpandCriterion c : criterions){

predicates.add(c.toPredicate(root, query,builder));

}

// 将所有条件用 and 联合起来

if (predicates.size() > 0) {

return builder.and(predicates.toArray(new Predicate[predicates.size()]));

}

return builder.conjunction();

}

/**

* 增加简单条件表达式

* @Methods Name add

* @Create In 2012-2-8 By lee

* @param expression0 void

public void add(ExpandCriterion criterion){

if(criterion!=null){

criterions.add(criterion);

}

public static void main(String[] args) {

//使用示例Demo

// Criteria c = new Criteria();

// c.add(Restrictions.like("code", searchParam.getCode(), true));

// c.add(Restrictions.eq("level", searchParam.getLevel(), false));

// c.add(Restrictions.eq("mainStatus", searchParam.getMainStatus(), true));

// c.add(Restrictions.eq("flowStatus", searchParam.getFlowStatus(), true));

// c.add(Restrictions.eq("createUser.userName", searchParam.getCreateUser(), true));

// c.add(Restrictions.lte("submitTime", searchParam.getStartSubmitTime(), true));

// c.add(Restrictions.gte("submitTime", searchParam.getEndSubmitTime(), true));

// c.add(Restrictions.eq("needFollow", searchParam.getIsfollow(), true));

// c.add(Restrictions.ne("flowStatus", searchParam.getMainStatus() true));

// c.add(Restrictions.in("solveTeam.code",teamCodes, true));

// repository.findAll(c);

}

新建ExpandCriterion接口

import javax.persistence.criteria.CriteriaBuilder;

import javax.persistence.criteria.CriteriaQuery;

import javax.persistence.criteria.Predicate;

import javax.persistence.criteria.Root;

public interface ExpandCriterion {

public enum Operator {

EQ, NE, LIKE, GT, LT, GTE, LTE, AND, OR

}

public Predicate toPredicate(Root root, CriteriaQuery query,

CriteriaBuilder builder);

}

新建Restrictions.java

import java.util.Collection;

import org.springframework.util.StringUtils;

import com.sll.iot.dao.base.criteria.ExpandCriterion.Operator;

public class Restrictions {

/**

* 等于

* @param fieldName

* @param value

* @param ignoreNull

* @return

public static SimpleExpression eq(String fieldName, Object value, boolean ignoreNull) {

if(StringUtils.isEmpty(value))return null;

return new SimpleExpression (fieldName, value, Operator.EQ);

}

/**

* 不等于

* @param fieldName

* @param value

* @param ignoreNull

* @return

public static SimpleExpression ne(String fieldName, Object value, boolean ignoreNull) {

if(StringUtils.isEmpty(value))return null;

return new SimpleExpression (fieldName, value, Operator.NE);

}

/**

* 模糊匹配

* @param fieldName

* @param value

* @param ignoreNull

* @return

public static SimpleExpression like(String fieldName, String value, boolean ignoreNull) {

if(StringUtils.isEmpty(value))return null;

return new SimpleExpression (fieldName, value, Operator.LIKE);

}

/**

* 大于

* @param fieldName

* @param value

* @param ignoreNull

* @return

public static SimpleExpression gt(String fieldName, Object value, boolean ignoreNull) {

if(StringUtils.isEmpty(value))return null;

return new SimpleExpression (fieldName, value, Operator.GT);

}

/**

* 小于

* @param fieldName

* @param value

* @param ignoreNull

* @return

public static SimpleExpression lt(String fieldName, Object value, boolean ignoreNull) {

if(StringUtils.isEmpty(value))return null;

return new SimpleExpression (fieldName, value, Operator.LT);

}

/**

* 大于等于

* @param fieldName

* @param value

* @param ignoreNull

* @return

public static SimpleExpression lte(String fieldName, Object value, boolean ignoreNull) {

if(StringUtils.isEmpty(value))return null;

return new SimpleExpression (fieldName, value, Operator.GTE);

}

/**

* 小于等于

* @param fieldName

* @param value

* @param ignoreNull

* @return

public static SimpleExpression gte(String fieldName, Object value, boolean ignoreNull) {

if(StringUtils.isEmpty(value))return null;

return new SimpleExpression (fieldName, value, Operator.LTE);

}

/**

* 并且

* @param criterions

* @return

public static LogicalExpression and(ExpandCriterion... criterions){

return new LogicalExpression(criterions, Operator.AND);

}

/**

* 或者

* @param criterions

* @return

public static LogicalExpression or(ExpandCriterion... criterions){

return new LogicalExpression(criterions, Operator.OR);

}

/**

* 包含于

* @param fieldName

* @param value

* @return

@SuppressWarnings("rawtypes")

public static LogicalExpression in(String fieldName, Collection value, boolean ignoreNull) {

if(ignoreNull&&(value==null||value.isEmpty())){

return null;

}

SimpleExpression[] ses = new SimpleExpression[value.size()];

int i=0;

for(Object obj : value){

ses[i]=new SimpleExpression(fieldName,obj,Operator.EQ);

i++;

}

return new LogicalExpression(ses,Operator.OR);

}

新建SimpleExpression.java

import javax.persistence.criteria.CriteriaBuilder;

import javax.persistence.criteria.CriteriaQuery;

import javax.persistence.criteria.Expression;

import javax.persistence.criteria.Path;

import javax.persistence.criteria.Predicate;

import javax.persistence.criteria.Root;

public class SimpleExpression implements ExpandCriterion{

private String fieldName; //属性名

private Object value; //对应值

private Operator operator; //计算符

protected SimpleExpression(String fieldName, Object value, Operator operator) {

this.fieldName = fieldName;

this.value = value;

this.operator = operator;

}

public String getFieldName() {

return fieldName;

}

public Object getValue() {

return value;

}

public Operator getOperator() {

return operator;

}

@SuppressWarnings({ "rawtypes", "unchecked" })

public Predicate toPredicate(Root root, CriteriaQuery query,

CriteriaBuilder builder) {

Path expression = null;

if(fieldName.contains(".")){

String[] names = fieldName.split(".");

expression = root.get(names[0]);

for (int i = 1; i < names.length; i++) {

expression = expression.get(names[i]);

}

}else{

expression = root.get(fieldName);

}

switch (operator) {

case EQ:

return builder.equal(expression, value);

case NE:

return builder.notEqual(expression, value);

case LIKE:

return builder.like((Expression) expression, "%" + value + "%");

case LT:

return builder.lessThan(expression, (Comparable) value);

case GT:

return builder.greaterThan(expression, (Comparable) value);

case LTE:

return builder.lessThanOrEqualTo(expression, (Comparable) value);

case GTE:

return builder.greaterThanOrEqualTo(expression, (Comparable) value);

default:

return null;

}

LogicalExpression.java

import java.util.ArrayList;

import java.util.List;

import javax.persistence.criteria.CriteriaBuilder;

import javax.persistence.criteria.CriteriaQuery;

import javax.persistence.criteria.Predicate;

import javax.persistence.criteria.Root;

public class LogicalExpression implements ExpandCriterion {

private ExpandCriterion[] criterion; // 逻辑表达式中包含的表达式

private Operator operator; //计算符

public LogicalExpression(ExpandCriterion[] criterions, Operator operator) {

this.criterion = criterions;

this.operator = operator;

}

public Predicate toPredicate(Root root, CriteriaQuery query,

CriteriaBuilder builder) {

List predicates = new ArrayList();

for(int i=0;i

predicates.add(this.criterion[i].toPredicate(root, query, builder));

}

switch (operator) {

case OR:

return builder.or(predicates.toArray(new Predicate[predicates.size()]));

default:

return null;

}

使用criteria前提是dao接口必须实现JpaSpecificationExecutor接口

改造如下

//条件查询

@Override

public Paging query(Paging paging,String channelName,String operator) {

Pageable pageReq = new PageRequest(paging.getCurrentPage()-1, paging.getPageSize());

Page pageChanel=null;

ExpandCriteria criteria = new ExpandCriteria();

if(StringUtil.isNotEmpty(channelName)){

criteria.add(Restrictions.like("name", channelName, false));

}

if(StringUtil.isNotEmpty(operator)){

criteria.add(Restrictions.eq("operator",Operator.valueOf(operator), false));

}

pageChanel=channelRepository.findAll(criteria, pageReq);

if(pageChanel!=null){

paging.setTotalCount((int)pageChanel.getTotalElements());

paging.setData(pageChanel.getContent());

paging.setTotalPage(pageChanel.getTotalPages());

}

return paging;

}

都不用在dao接口写什么东西

使用方法就是demo