Spingboot JPA CriteriaBuilder 如何获取指定字段

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 findAllByPage(Pageable pageable) throws Exception {

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

CriteriaQuery criteriaQuery = criteriaBuilder.createTupleQuery();

root 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 query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);

List tuples = query.getResultList();

ActivityVO adminWrapper = null;

List lists = new ArrayList();

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 criteriaQuery = criteriaBuilder.createQuery(Entity.class);　　　　 //查询所需要的主体类（Entity0相对应）

Root 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.get("str"), "%"+str+"%"));

//用户名查询（member里面的username匹配） ———— 多层查询 ———— 子查询的一种：适用于m:1或1:1（即多对一或一对一）

restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.like(root.get("member").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 orderSubquery = criteriaQuery.subquery(Order.class);

Root orderSubqueryRoot = orderSubquery.from(Order.class);

orderSubquery.select(orderSubqueryRoot);　　　　　　　//子查询和父查询相关联

Predicate orderRestrictions = criteriaBuilder.equal(orderSubqueryRoot.get("mergeOrder"), root);

//子查询过滤条件拼接

if (searchType != null && searchValue != null) {if ("phone".equals(searchType)) {

orderRestrictions = criteriaBuilder.and(orderRestrictions, criteriaBuilder.like(orderSubqueryRoot.get("phone"), "%"+searchValue+"%"));

}

}if (type != null) {

CriteriaBuilder.In in = criteriaBuilder.in(orderSubqueryRoot.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.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.get("expire"), new Date()))

, criteriaBuilder.notEqual(orderSubqueryRoot.get("status"), Order.Status.completed)

, criteriaBuilder.lessThan(orderSubqueryRoot.get("amountPaid"), orderSubqueryRoot.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 query = entityManager.createQuery(criteriaQuery);　　　　 Entity singleResult = query.getSingleResult();

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。