将SQL转换为Criteria语句

Question

我想把这个SQL查询转换成Criteria语句，我使用createCriteria(Entity.class)函数。

下面是我要转换的SQL查询:

select sm.id, sm.mutation_type, u.id, u.display_name, advisor.id, advisor.display_name from mutation sm
inner join user u on sm.user_id = u.id
inner join user_advisor ua on u.id = ua.user_id
inner join user advisor on advisor.id = ua.advisor_id
where advisor_id in (2, 3)

以下是使用的JPA实体:

@Entity
public class Mutation {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private long id;
private String mutationType;
@ManyToOne(fetch = FetchType.LAZY, cascade = {})
@JoinColumn(name = "user_id")
private User user;
}

@Entity
@Table(name = "user")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "user_type", discriminatorType = DiscriminatorType.STRING, length = 32)
@DiscriminatorValue(value = "user")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
@Access(value = AccessType.PROPERTY)
protected int id;
protected String displayName;

@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "user_advisor", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "advisor_id"))
protected Set<Advisor> advisors;

}

@Entity
@DiscriminatorValue(value = "advisor")
public class Advisor extends User {
}

Answer 1

CriteriaBuilder builder = sessionFactory.getCriteriaBuilder();
CriteriaQuery<Object[]> query = builder.createQuery(Object[].class);
Root<Mutation> mutation = query.from(Mutation.class);
Join<Object, Object> user = mutation.join("user");
Join<Object, Object> advisor = user.join("advisors");
query.multiselect( mutation.get( "id" ), mutation.get( "mutationType" ), user.get( "id" ), user.get( "displayName" ), advisor.get( "id" ), advisor.get( "displayName" ) );
query.where( advisor.get("id").in(2, 3) );

查看Hibernate ORM关于Criteria的段落，了解更多细节。

PS:user是许多数据库的特殊关键字。当使用它作为表名时，我会转义它:

@Table(name = "`user`")