Spingboot JPA CriteriaBuilder 如何获取指定字段

目录

• Spingboot JPA CriteriaBuilder获取指定字段
• Java JPA CriteriaBuilder使用
• 一个复杂的查询例子

Spingboot JPA CriteriaBuilder获取指定字段

废话不说直接贴代码

public class ActivityVO implements Serializable {
 private static final long serialVersionUID = 1L;
 private int id;
 private String name;
 private String thumb;
 public ActivityVO(int id, String name, String thumb) {
  this.id    = id;
  this.name  = name;
  this.thumb = thumb;
 }

 public ActivityVO(){
 }

 public int getId() {
  return id;
 }

 public void setId(int id) {
  this.id = id;
 }

 public String getName() {
  return name;
 }

 public void setName(String name) {
  this.name = name;
 }

 public String getThumb() {
  return thumb;
 }

 public void setThumb(String thumb) {
  this.thumb = thumb;
 }
}

import org.hibernate.annotations.NotFound;
import org.hibernate.annotations.NotFoundAction;
import java.io.Serializable;
import javax.persistence.*;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.List;
import java.util.Map;
import java.util.Objects; 

/**
 * The persistent class for the activity database table.
 *
 */
@Entity
@Table(name="activity")
//@JsonIgnoreProperties({"user"})
//@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
//@NamedQuery(name = "Task.findByTaskName", query = "select t from Task t where t.taskName = ?1")
public class ActivityEntity implements Serializable {
	private static final long serialVersionUID = 1L;
	public ActivityEntity(int id, String name, String thumb) {
		this.id                  = id;
		this.name                = name;
	}

	private int        id;
	private Integer    userId;
	private String     name;
	private String     thumb;
	private byte       type;
	private byte       mode;
	private String     notes;
	private String     content;
	private BigDecimal deposit;
	private byte       status;
	private int        productNum;
	private Integer    participantNum;
	private Integer    wxId;
	private Integer    mpId;
	private String     mpRelpyKeyword;
	private BigInteger startTime;
	private byte       lotteryType;
	private BigInteger lotteryTime;
	private int        maxParticipantNum;
	private byte       enableBlacklist;
    private byte enableSharing;
	private byte isVisible;
	private BigInteger createTime;
	private BigInteger updateTime;
	public ActivityEntity() {
	}

	@Id
	@Column(name = "id")
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	@Basic
	@Column(name = "user_id")
	public Integer getUserId() {
		return userId;
	}

	public void setUserId(Integer userId) {
		this.userId = userId;
	}

	@Basic
	@Column(name = "name")
	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@Basic
	@Column(name = "thumb")
	public String getThumb() {
		return thumb;
	}

	public void setThumb(String thumb) {
		this.thumb = thumb;
	}

	@Basic
	@Column(name = "type")
	public byte getType() {
		return type;
	}

	public void setType(byte type) {
		this.type = type;
	}

	@Basic
	@Column(name = "mode")
	public byte getMode() {
		return mode;
	}

	public void setMode(byte mode) {
		this.mode = mode;
	}

	@Basic
	@Column(name = "notes")
	public String getNotes() {
		return notes;
	}

	public void setNotes(String notes) {
		this.notes = notes;
	}

	@Basic
	@Column(name = "content")
	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}

	@Basic
	@Column(name = "deposit")
	public BigDecimal getDeposit() {
		return deposit;
	}

	public void setDeposit(BigDecimal deposit) {
		this.deposit = deposit;
	}

	@Basic
	@Column(name = "status")
	public byte getStatus() {
		return status;
	}

	public void setStatus(byte status) {
		this.status = status;
	}

	@Basic
	@Column(name = "product_num")
	public int getProductNum() {
		return productNum;
	}

	public void setProductNum(int productNum) {
		this.productNum = productNum;
	}

	@Basic
	@Column(name = "participant_num")
	public Integer getParticipantNum() {
		return participantNum;
	}

	public void setParticipantNum(Integer participantNum) {
		this.participantNum = participantNum;
	}

	@Basic
	@Column(name = "wx_id")
	public Integer getWxId() {
		return wxId;
	}

	public void setWxId(Integer wxId) {
		this.wxId = wxId;
	}

	@Basic
	@Column(name = "mp_id")
	public Integer getMpId() {
		return mpId;
	}

	public void setMpId(Integer mpId) {
		this.mpId = mpId;
	}

	@Basic
	@Column(name = "mp_relpy_keyword")
	public String getMpRelpyKeyword() {
		return mpRelpyKeyword;
	}

	public void setMpRelpyKeyword(String mpRelpyKeyword) {
		this.mpRelpyKeyword = mpRelpyKeyword;
	}

	@Basic
	@Column(name = "start_time")
	public BigInteger getStartTime() {
		return startTime;
	}
	public void setStartTime(BigInteger startTime) {
		this.startTime = startTime;
	}

	@Basic
	@Column(name = "lottery_type")
	public byte getLotteryType() {
		return lotteryType;
	}

	public void setLotteryType(byte lotteryType) {
		this.lotteryType = lotteryType;
	}

	@Basic
	@Column(name = "lottery_time")
	public BigInteger getLotteryTime() {
		return lotteryTime;
	}

	public void setLotteryTime(BigInteger lotteryTime) {
		this.lotteryTime = lotteryTime;
	}

	@Basic
	@Column(name = "max_participant_num")
	public int getMaxParticipantNum() {
		return maxParticipantNum;
	} 

	public void setMaxParticipantNum(int maxParticipantNum) {
		this.maxParticipantNum = maxParticipantNum;
	}

	@Basic
	@Column(name = "enable_blacklist")
	public byte getEnableBlacklist() {
		return enableBlacklist;
	}

	public void setEnableBlacklist(byte enableBlacklist) {
		this.enableBlacklist = enableBlacklist;
	}

	@Basic
	@Column(name = "enable_sharing")
	public byte getEnableSharing() {
		return enableSharing;
	}

	public void setEnableSharing(byte enableSharing) {
		this.enableSharing = enableSharing;
	}

	@Basic
	@Column(name = "is_visible")
	public byte getIsVisible() {
		return isVisible;
	}

	public void setIsVisible(byte isVisible) {
		this.isVisible = isVisible;
	}
	@Basic
	@Column(name = "create_time")
	public BigInteger getCreateTime() {
		return createTime;
	}
	public void setCreateTime(BigInteger createTime) {
		this.createTime = createTime;
	}

	@Basic
	@Column(name = "update_time")
	public BigInteger getUpdateTime() {
		return updateTime;
	}

	public void setUpdateTime(BigInteger updateTime) {
		this.updateTime = updateTime;
	}

	@Override
	public boolean equals(Object o) {
		if (this == o) return true;
		if (o == null || getClass() != o.getClass()) return false;
		ActivityEntity that = (ActivityEntity) o;
		return id == that.id &&
				type == that.type &&
				mode == that.mode &&
				status == that.status &&
				productNum == that.productNum &&
				lotteryType == that.lotteryType &&
				maxParticipantNum == that.maxParticipantNum &&
				enableBlacklist == that.enableBlacklist &&
				Objects.equals(userId, that.userId) &&
				Objects.equals(name, that.name) &&
				Objects.equals(thumb, that.thumb) &&
				Objects.equals(notes, that.notes) &&
				Objects.equals(content, that.content) &&
				Objects.equals(deposit, that.deposit) &&
				Objects.equals(participantNum, that.participantNum) &&
				Objects.equals(wxId, that.wxId) &&
				Objects.equals(mpId, that.mpId) &&
				Objects.equals(mpRelpyKeyword, that.mpRelpyKeyword) &&
				Objects.equals(startTime, that.startTime) &&
				Objects.equals(lotteryTime, that.lotteryTime) &&
				Objects.equals(createTime, that.createTime) &&
				Objects.equals(updateTime, that.updateTime) &&
				Objects.equals(user, that.user);
	}

	@Override
	public int hashCode() {
		return Objects.hash(id, userId, name, thumb, type, mode, notes, content, deposit, status, productNum, participantNum, wxId, mpId, mpRelpyKeyword, startTime, lotteryType, lotteryTime, maxParticipantNum, enableBlacklist, createTime, updateTime, user);
	}

	@Override
	public String toString() {
		return "ActivityEntity{" +
				"id=" + id +
				", userId=" + userId +
				", name='" + name + '\'' +
				", thumb='" + thumb + '\'' +
				", type=" + type +
				", mode=" + mode +
				", notes='" + notes + '\'' +
				", content='" + content + '\'' +
				", deposit=" + deposit +
				", status=" + status +
				", productNum=" + productNum +
				", participantNum=" + participantNum +
				", wxId=" + wxId +
				", mpId=" + mpId +
				", mpRelpyKeyword='" + mpRelpyKeyword + '\'' +
				", startTime=" + startTime +
				", lotteryType=" + lotteryType +
				", lotteryTime=" + lotteryTime +
				", maxParticipantNum=" + maxParticipantNum +
				", enableBlacklist=" + enableBlacklist +
				", createTime=" + createTime +
				", updateTime=" + updateTime +
				", user=" + user +
				'}';
	}
}

 @PersistenceContext//@Autowired和@PersistenceContext注解任取一
    EntityManager entityManager;
    public List<ActivityVO> findAllByPage(Pageable pageable) throws Exception {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
        Root<ActivityEntity> root = criteriaQuery.from(ActivityEntity.class);
        criteriaQuery.multiselect( root.get("id"), root.get("name"),root.get("thumb") );

        Predicate restrictions = criteriaBuilder.conjunction();
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("id"), 1));
        criteriaQuery.where(restrictions);
        criteriaQuery.orderBy(criteriaBuilder.desc(root.get("id")));

        TypedQuery<Tuple> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
        List<Tuple> tuples = query.getResultList();

        ActivityVO adminWrapper = null;
        List<ActivityVO> lists = new ArrayList<ActivityVO>();
        for (Tuple t : tuples) {
            adminWrapper = new ActivityVO();
            adminWrapper.setId((Integer) t.get(0));
            adminWrapper.setName((String)t.get(1));
            adminWrapper.setThumb((String)t.get(2));
            lists.add(adminWrapper);
        }
        return lists;
    }
}

Java JPA CriteriaBuilder使用

一个复杂的查询例子

包含常用的所有查询方法

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();　　　　 //查询结果所需要的类型(Entity相对应)
        CriteriaQuery<Entity> criteriaQuery = criteriaBuilder.createQuery(Entity.class);　　　　 //查询所需要的主体类（Entity0相对应）
        Root<Entity0> root = criteriaQuery.from(Entity0.class);　　　　 //查询结果-select（此处查询所有符合条件的主体类）
        criteriaQuery.select(root);　　　　 //过滤条件用Predicate方法拼接
        Predicate restrictions = criteriaBuilder.conjunction();　　　　 //过滤条件——equal（当Entity0关联member类时，Entity0：member=m：1）
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
        //过滤条件——like
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.like(root.<String>get("str"), "%"+str+"%"));
        //用户名查询（member里面的username匹配） ———— 多层查询 ———— 子查询的一种：适用于m:1或1:1（即多对一或一对一）
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.like(root.get("member").<String>get("username"), "%"+username+"%"));
        //子查询（规范写法，先判断查询内容是否存在）（适用于1：m）（即一对多）
        if (searchType != null || searchValue != null || hasExpired != null || status != null || type != null || isPendingReceive != null || isPendingRefunds != null || isAllocatedStock != null || businessType != null) {
            //建立子查询　　　　　　  Subquery<Order> orderSubquery = criteriaQuery.subquery(Order.class);
            Root<Order> orderSubqueryRoot = orderSubquery.from(Order.class);
            orderSubquery.select(orderSubqueryRoot);　　　　　　　//子查询和父查询相关联
            Predicate orderRestrictions = criteriaBuilder.equal(orderSubqueryRoot.<MergeOrder>get("mergeOrder"), root);
            //子查询过滤条件拼接
            if (searchType != null && searchValue != null) {if ("phone".equals(searchType)) {
                    orderRestrictions = criteriaBuilder.and(orderRestrictions, criteriaBuilder.like(orderSubqueryRoot.<String>get("phone"), "%"+searchValue+"%"));
                }
            }if (type != null) {
                CriteriaBuilder.In<Order.Type> in = criteriaBuilder.in(orderSubqueryRoot.<Order.Type>get("type"));
                in.value(type);
                orderRestrictions = criteriaBuilder.and(orderRestrictions, in);
            }
            //and、or以及判断是否为null，比较（>）的使用（比较可以用于日期比较）
            if (hasExpired != null) {
                orderRestrictions = criteriaBuilder.and(orderRestrictions, criteriaBuilder.or(orderSubqueryRoot.get("expire").isNull(), criteriaBuilder.greaterThan(orderSubqueryRoot.<Date>get("expire"), new Date())));
            }
            // not的使用方法（不符合上述过滤条件），notEqual的使用，<（小于）的使用
            if (isPendingReceive != null) {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("paymentMethodType"), PaymentMethod.Type.cashOnDelivery));
                Predicate predicate = criteriaBuilder.and(criteriaBuilder.or(orderSubqueryRoot.get("expire").isNull()
                        , criteriaBuilder.greaterThan(orderSubqueryRoot.<Date>get("expire"), new Date()))
                        , criteriaBuilder.notEqual(orderSubqueryRoot.get("status"), Order.Status.completed)
                        , criteriaBuilder.lessThan(orderSubqueryRoot.<BigDecimal>get("amountPaid"), orderSubqueryRoot.<BigDecimal>get("amount")));
                if (isPendingReceive) {
                    orderRestrictions = criteriaBuilder.and(orderRestrictions, criteriaBuilder.not(predicate));
                }
            }// 多层查询使用if (businessType != null) {
                orderRestrictions = criteriaBuilder.and(orderRestrictions, criteriaBuilder.equal(orderSubqueryRoot.get("store").get("business").get("businessType"), businessType));
            }　　　　　　　// 拼接过滤条件
            orderSubquery.where(orderRestrictions);
            // 和总条件拼接（exists的使用）
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(orderSubquery));
        }
        criteriaQuery.where(restrictions);　　　　 TypedQuery<Entity> query = entityManager.createQuery(criteriaQuery);　　　　 Entity singleResult = query.getSingleResult();

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。