Convert SQL into Criteria statement

Question

I want to convert this SQL query to Criteria statement, I'm using createCriteria(Entity.class) function.

The following is the SQL query I want to convert:

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)

The following are JPA entities used:

@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) );

Check the Hibernate ORM paragraph about Criteria for more details.

PS: user is a special keyword for many databases. I would escape it when using it as name of the table:

@Table(name = "`user`")