Entity Maping Configuration

Consider a scenario mapping a bidirectional association between Division and Personnel. The Division entity represents organizational units, while Personnel represents employees assigned to those units.

@Entity
@Table(name = "organizational_unit")
public class Division {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "unit_designation")
    private String unitName;
    
    @OneToMany(mappedBy = "division", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    private Set<Personnel> members = new HashSet<>();
    
    // Accessors omitted for brevity
}

@Entity
@Table(name = "employee")
public class Personnel {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long pk;
    
    private String fullName;
    private String role;
    private Double annualSalary;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "unit_id", nullable = true)
    private Division division;
    
    // Accessors omitted for brevity
}

Single-Table Query Patterns

Full entity retrieval loads all persistent properties of the mapped class:

String query = "from Personnel p";
List<Personnel> staff = session.createQuery(query, Personnel.class)
    .getResultList();

For retrieving specific attributes rather than complete entities, use projection. This returns a list of object arrays where each index corresponds to the selected field order:

String projection = "select p.fullName, p.annualSalary from Personnel p";
List<Object[]> attributes = session.createQuery(projection, Object[].class)
    .getResultList();

for (Object[] row : attributes) {
    String name = (String) row[0];
    Double salary = (Double) row[1];
}

To eliminate duplicate values from result sets:

String distinctRoles = "select distinct p.role from Personnel p";
List<String> uniquePositions = session.createQuery(distinctRoles, String.class)
    .getResultList();

Conditional Filtering and Sorting

Where clauses support logical operators, comparison predicates, and pattern matching:

String conditional = "from Personnel p where p.annualSalary between :min and :max " +
                    "and p.division is not null and p.fullName like :pattern";
List<Personnel> filtered = session.createQuery(conditional, Personnel.class)
    .setParameter("min", 60000.0)
    .setParameter("max", 120000.0)
    .setParameter("pattern", "S%")
    .getResultList();

Sorting results requires the order by clause:

String ordered = "from Personnel p order by p.annualSalary desc, p.fullName asc";
List<Personnel> sorted = session.createQuery(ordered, Personnel.class)
    .getResultList();

Pagination and Aggregation

Limit result sets using firstResult and maxResults:

String paginated = "from Personnel p order by p.pk";
List<Personnel> page = session.createQuery(paginated, Personnel.class)
    .setFirstResult(20)
    .setMaxResults(10)
    .getResultList();

Aggregate functions compute values across result sets:

String aggregates = "select count(p), avg(p.annualSalary), max(p.annualSalary) from Personnel p";
Object[] statistics = session.createQuery(aggregates, Object[].class)
    .getSingleResult();

Grouping operations categorize results, with having clauses filtering aggregated groups:

String grouped = "select p.role, count(p), avg(p.annualSalary) from Personnel p " +
                "group by p.role having count(p) > 2";
List<Object[]> roleStatistics = session.createQuery(grouped, Object[].class)
    .getResultList();

Multi-Table Join Operations

Inner joins return records with matching values in both entities:

String innerJoin = "select p.fullName, d.unitName from Personnel p join p.division d";
List<Object[]> innerResults = session.createQuery(innerJoin, Object[].class)
    .getResultList();

Outer joins preserve records from one table regardless of matches. Left outer joins retain all division records even without assigned personnel:

String leftOuter = "select d.unitName, p.fullName from Division d left join d.members p";
List<Object[]> outerResults = session.createQuery(leftOuter, Object[].class)
    .getResultList();

Handling Non-Entity Result Sets

When queries combine fields from multiple tables without targeting a specific entity, scalar results provide generic data access:

String scalar = "select p.fullName, d.unitName, p.annualSalary from Personnel p, Division d " +
               "where p.division.id = d.id";
List<Object[]> tuples = session.createQuery(scalar, Object[].class).getResultList();

for (Object[] tuple : tuples) {
    System.out.printf("Employee: %s, Unit: %s, Compensation: %s%n", 
                     tuple[0], tuple[1], tuple[2]);
}

Alternatively, constructor expressions map results directly to Data Transfer Objects:

public class PersonnelView {
    private String employeeName;
    private String departmentName;
    private Double compensation;
    
    public PersonnelView(String employeeName, String departmentName, Double compensation) {
        this.employeeName = employeeName;
        this.departmentName = departmentName;
        this.compensation = compensation;
    }
}

String constructorQuery = "select new com.example.PersonnelView(" +
                         "p.fullName, d.unitName, p.annualSalary) " +
                         "from Personnel p join p.division d";
List<PersonnelView> views = session.createQuery(constructorQuery, PersonnelView.class)
    .getResultList();