1

I currently have a Spring Boot application with Hibernate mapped entities. One of them is say an Employee, and I'd like to get all the employee usernames by department.

Something like the SQL:

SELECT username FROM employee WHERE departmentId=1 AND active=true;

Employee class:

@Entity
public class Employee extends BaseEntity implements Serializable {
    private int id;
    private String name;
    private String username;
    private String building;
    private Integer departmentId;
    private Boolean active;
    ... other stuff about them ...
    ... annotated getters and setters ...
}

I (believe I) can get all the Employees of department with something like:

public List<Employee> employeesInDepartment(Integer departmentId) {
    Session session = getSession();
    CriteriaBuilder builder = session.getCriteriaBuilder();
    CriteriaQuery<Employee> criteriaQuery = builder.createQuery(Employee.class);
    Root<Employee> root = criteriaQuery.from(Employee.class);
    criteriaQuery.where(builder.and(
            builder.equal(root.get("departmentId"), departmentId),
            builder.equal(root.get("active"), true)))
            .orderBy(builder.desc(root.get("timeCreated")))
            .select(root);
    return session.createQuery(criteriaQuery).list();
}

But say I want only a Set of their usernames or buildings they're in, then I don't need to fill in the other 20+ fields about each employee and just need to get back the set of one field.

    return session.createQuery(criteriaQuery).stream()
                .map(Comment::getUsername).collect(Collectors.toSet());

However this wastes the transmission of the 20+ fields from the DB storage, and DB network back to the application service, and seems like the query as shown at the top would be the right one to represent in JPA's object notation. Yet, I'm not sure what to write instead; there wasn't an obvious Hibernate way to get a filtered down entity collection, so the JPA way seemed right, but I'm finding the documentation not as easy to grasp as I hoped. Am I looking at something like:

public Set<String> usernamesOfEmployeesInDepartment(Integer departmentId) {
    Session session = getSession();
    CriteriaBuilder builder = session.getCriteriaBuilder();
    CriteriaQuery<Employee> criteriaQuery = builder.createQuery(Employee.class);
    Root<Employee> root = criteriaQuery.from(Employee.class);
    criteriaQuery.where(builder.and(
            builder.equal(root.get("departmentId"), departmentId),
            builder.equal(root.get("active"), true)))
            .orderBy(builder.desc(root.get("timeCreated")))
            .select(root).select(root.get("username"));
    return new HashSet<>(session.createQuery(criteriaQuery).list());
}

That doesn't end up giving me the right type… And if I wanted a distinct collection of a departments' employees' building locations, I need to throw in distinct there somewhere too, but where?

dlamblin
  • 43,965
  • 20
  • 101
  • 140
  • Can't you just create a normal query like `Set usernames = session.createQuery("SELECT DISTINCT e.username FROM Employee e WHERE e.departmentId = :id AND e.active=true").setParameter("id", departmentId).getResultList();` – PaulD May 26 '20 at 09:41
  • @PaulD My understanding, and it may not be right, is that a session query string goes to the DB skipping cached persistence objects, while a CriteriaQuery off a root class will include the unflushed persistence objects in the hibernate cache ; I could be wrong about that, but that's why I tried to build it this way. – dlamblin May 26 '20 at 10:52
  • `Session#createQuery` still uses HQL and thus should use the cache, as long as you have enabled it, see https://stackoverflow.com/questions/24342174/will-hql-query-use-hibernate-second-level-cache – PaulD May 26 '20 at 11:39
  • @PaulD Thanks; I assume you're right about that and I'll write the query as a string as you recommend. I remain curious however how the equivalent could be represented using the builder object notation. – dlamblin May 27 '20 at 08:51
  • maybe this helps with that https://stackoverflow.com/questions/12618489/jpa-criteria-api-select-only-specific-columns – PaulD May 27 '20 at 14:18

0 Answers0