将sql查询转换为CriteriaQuery

Question

我有两个表，它们通过外键维护它们之间的父子关系。查询如下所示。我想将criteriaquery与jpa一起使用。有谁能帮我解答一下标准查询吗?这两个实体类看起来会是什么样子Ps:如果有任何自定义实体类需要除了这两个实体类帮助我，以及。

Select parent.notification_id,parent.city,parent.name,parent.accountNo,
case when child.accountNo is not null then 'Yes' else 'No' end as checked
FROM parent
JOIN child ON parent.notification_id=child.notification_id_child
AND child.accountNo='test' WHERE parent.city='delhi' or parent.city='all' or parent.accountNo="test";

表'child'的列'notification_id_child'是外键，指向表'parent'的主键。

Answer 1

您可以使用多种策略来实现这一点:

1. MappedSuperclass(父类将使用此注释而不是实体进行映射)
2. 单表(每个层次的单表，您可以使用@DiscriminatorColumn JPA注释来标识每个层次)
3. join Table(每个类对应父类和子类)在这个场景中，您必须在公共列上连接两个表才能获取结果。

这些是关于连接表的一些很好的答案

在Spring Data JPA中连接两个表实体

关于discrimintaorColumn用法的一些好答案链接

如何访问JPA中的discriminator列

Answer 2

最后，我设法解决了这个问题。我的实体类和标准查询如下所示。

父实体

@Entity
@Table(name="parent")
public class Parent{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="notification_id")
private Long notificationId;
@Column(name="city")
private String city;
@Column(name="name")
private String name;
@Column(name="accountNo")
private String accountNo;
@JoinColumn(name="notification_id_child")
@OneToMany
private List<Child> child;
//Getters Setters
}

儿童实体

@Entity
@Table(name="child")
public class Child{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="id")
private Long id;
@Column(name="accountNo")   
private String accountNo;
@Column(name="notification_id_child")
private String notificationIdChild;
//Getters Setters
}

自定义实体

public class CustomEntity{
private Long notificationId;
private String city;
private String accountNo;
private String checked;
}

标准查询

@PersistenceContext
EntitiManager em;    
CriteraBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<CustomEntity> cq = cb.createQuery(CustomEntity.class);
Root<Parent> parentEntity = cq.from(Parent.class);
Join<Parent,Child> join = parentEntity.join("child", JoinType.LEFT);
join.on(cb.equal(join.get("accountNo"),"test"));

Path<String> notificationIdPath = parentEntity.get("notificationId");
Path<String> cityPath = parentEntity.get("city");
Path<String> accountNoPath = parentEntity.get("accountNo");
cq.multiselect(notificationIdPath, cityPath, accountNoPath,
cb.selectCase().when(join.get("accountNo").isNotNull(),"Yes").otherwise("No"));

Path<String> accountNoPath = parentEntity("accountNo");
Predicate accountNoPredicate = cb.equal(accountNoPath, "test"); 
Predicate cityPredicateAll = cb.equal(cityPath,"all");
Predicate cityPredicateSpecified = cb.equal(cityPath,"delhi");

cq.where(cb.or(cityPredicateAll, cityPredicateSpecified, accountNoPredicate));
TypedQuery<CustomEntity> query = em.createQuery(cq);
List<CustomEntity> CustomEntityList = query.getResult();