SQL is an extremely highly effective question language. It offers sheer infinite prospects to extract and remodel data. One instance of that could be a window perform. It allows you to carry out operations on subsets of the desk rows accessible in your question.
The PostgreSQL documentation explains window features as:
A window perform performs a calculation throughout a set of desk rows which can be in some way associated to the present row. That is corresponding to the kind of calculation that may be performed with an mixture perform.
Nevertheless, window features don’t trigger rows to develop into grouped right into a single output row like non-window mixture calls would. As an alternative, the rows retain their separate identities.
https://www.postgresql.org/docs/present/tutorial-window.html
A typical instance of a window perform is a question that selects all staff with their wage and consists of the typical wage of every worker’s division. Right here is an instance of such a question in PostgreSQL’s SQL dialect.
SELECT firstName,
lastName,
division,
wage,
avg(wage) OVER (PARTITION BY division)
FROM Worker e
You outline the window perform by including an OVER clause to the decision of an aggregating perform. Inside that clause, you’ll be able to then specify the partition on which you need to apply the perform, outline the ordering inside the partition, and restrict your partition dimension. On this instance, I hold it easy and solely outline a easy partition.
As you’ll be able to see within the following screenshot, the question returned the chosen columns of the Worker desk, calculated the typical wage of each division, and ordered the workers of every division by their firstName.
As a Hibernate consumer, you would possibly now ask your self how you can use window features. The reply is determined by your Hibernate model. Beginning with model 6, you should use window features in your JPQL queries. Older Hibernate variations solely assist them by way of native queries. But when you know the way to map a local question’s resultset, this doesn’t introduce any limitations.
Window features in Hibernate 6
Hibernate 6 brings a number of proprietary enhancements to JPQL. Considered one of them is the assist for window features. That makes supporting totally different RDBMS and mapping your question outcomes to a DTO projection somewhat simpler.
The syntax for utilizing a window perform in JPQL is similar to SQL. It’s a must to add an OVER clause to your perform name to outline the window perform. Inside that window perform, you’ll be able to then:
- Use the PARTITION BY key phrase to outline the body on which you need to apply the perform.
- Add an ORDER BY clause to order the weather inside the body.
- Add a ROWS, RANGE, or GROUPS clause to outline what number of rows, which worth vary, or what number of totally different worth teams shall be included within the body. Not all RDMBS assist the modes RANGE and GROUPS. So, please examine your database’s documentation for extra data.
The default body consists of the present and all prior rows inside the present partition.
Let’s use these options to implement the question I confirmed you within the introduction. As you’ll be able to see within the code snippet, the JPQL assertion seems to be equivalent to the SQL assertion.
Record<Object[]> consequence = em.createQuery("""
SELECT firstName,
lastName,
division,
wage,
avg(wage) OVER (PARTITION BY division)
FROM Worker e""", Object[].class)
.getResultList();
You would possibly now surprise why the JPQL assertion seems to be equivalent to the beforehand proven SQL assertion. The reason being easy. You outline the JPQL assertion based mostly in your entity courses and their attributes and never based mostly in your desk mannequin. Hibernate then generates an SQL assertion based mostly on the supplied JPQL assertion and your mapping definitions. On this instance, the attribute names of the Worker entity class are equivalent to the columns of the Worker desk. So, there is no such thing as a seen distinction between each statements.
By defining your question as a JPQL assertion, you get the profit that Hibernate generates a database-specific SQL assertion based mostly on the supplied JPQL assertion. So, if that you must assist a number of RDBMS, Hibernate handles the variations within the supported SQL dialects.
Mapping the question outcomes
As you noticed within the earlier code snippet, my question returned a Record<Object[]>. That knowledge construction isn’t very snug to make use of, and it’s best to higher map it to a Record of DTO projections. On this instance, I’ll map every consequence document to an EmployeeInfo object.
public class EmployeeInfo {
personal String firstName;
personal String lastName;
personal String division;
personal Double wage;
personal Double avgSalary;
public EmployeeInfo() {}
public EmployeeInfo(String firstName, String lastName, String division, Double wage, Double avgSalary) {
this.firstName = firstName;
this.lastName = lastName;
this.division = division;
this.wage = wage;
this.avgSalary = avgSalary;
}
// getter and setter strategies
}
You’ve 2 principal choices to try this when executing a JPQL question. You may both use JPA’s constructor expressions or Hibernate’s ResultTransformer.
Mapping the question outcomes utilizing a constructor expression
You in all probability already know JPA’s constructor expressions out of your different JPQL queries. It begins with the key phrase new, adopted by the absolutely certified class identify, and defines the decision of a constructor that units all attribute values.
Record<EmployeeInfo> emps = em.createQuery("""
SELECT new com.thorben.janssen.EmployeeInfo(firstName,
lastName,
division,
wage,
avg(wage) OVER (PARTITION BY division))
FROM Worker e""", EmployeeInfo.class)
.getResultList();
For this JPQL question, Hibernate executes the next SQL question.
12:26:08,677 DEBUG [org.hibernate.SQL] - choose e1_0.firstName,e1_0.lastName,e1_0.division,e1_0.wage,avg(e1_0.wage) over(partition by e1_0.division order by e1_0.firstName) from Worker e1_0
As you’ll be able to see within the log output, the constructor name isn’t a part of the executed SQL assertion. The SQL assertion solely selects all values required to name the constructor, and Hibernate calls the constructor when processing the question consequence.
Mapping the question outcomes utilizing a TupleTransformer (aka ResultTransformer)
Hibernate’s TupleTransformer will get you an identical consequence however offers extra flexibility. As I defined in my information to ResultTransformers, you’ll be able to both implement Hibernate’s TupleTransformer interface or use one among Hibernate’s normal transformers. Hibernate then calls that transformer when processing every document of the question consequence.
On this instance, I’m utilizing Hibernate’s AliasToBeanResultTransformer. It calls the no-arguments constructor of my EmployeeInfo class and tries to discover a setter technique for every alias outlined within the question.
Record<EmployeeInfo> emps = session.createQuery("""
SELECT firstName as firstName,
lastName as lastName,
division as division,
wage as wage,
avg(wage) OVER (PARTITION BY division) as avgSalary
FROM Worker e""", Object[].class)
.setTupleTransformer(new AliasToBeanResultTransformer<EmployeeInfo>(EmployeeInfo.class))
.getResultList();
Window features in Hibernate 5
As talked about earlier, Hibernate 5’s JPQL implementation doesn’t assist window features. If you wish to use them, you need to outline and execute a native SQL question. Hibernate doesn’t parse these queries. It solely takes the supplied assertion and executes it. Meaning you should use all options supported by your database. However you should additionally deal with the variations within the supported SQL dialects in case you’re working with totally different RDBMS.
I take advantage of that method within the following examples to execute the SQL assertion I confirmed you within the introduction.
Record<Object[]> consequence = em.createNativeQuery("""
SELECT firstName,
lastName,
division,
wage,
avg(wage) OVER (PARTITION BY division)
FROM Worker e""")
.getResultList();
As defined earlier, Hibernate doesn’t modify the supplied native SQL assertion. It merely executes it and returns the consequence.
14:53:00,980 DEBUG [org.hibernate.SQL] - SELECT firstName,
lastName,
division,
wage,
avg(wage) OVER (PARTITION BY division) as avgSalary
FROM Worker e
Mapping the question outcomes
The native question within the earlier instance returns the consequence as a Record of Object[]. That’s not very snug to make use of, particularly if you wish to name another strategies with it. However as a reader of my weblog, you in all probability already know that you could inform Hibernate to map the consequence to a unique knowledge construction.
Mapping the question outcomes utilizing an @SqlResultSetMapping
I defined JPA’s @SqlResultSetMapping in nice element in a collection of weblog posts. For those who’re not already accustomed to it, I like to recommend studying the next articles:
You need to use the @SqlResultSetMapping annotation to specify how Hibernate shall map your question consequence. You may map it to managed entities, DTOs, scalar values, and mixtures of those 3. On this instance, I need Hibernate to name the EmployeeInfo‘s constructor that we used within the earlier instance. It accepts all attribute values and returns a completely initialized EmployeeInfo object.
@Entity
@SqlResultSetMapping(identify = "EmpInfoMapping",
courses = @ConstructorResult(targetClass = EmployeeInfo.class,
columns = {@ColumnResult(identify = "firstName"),
@ColumnResult(identify = "lastName"),
@ColumnResult(identify = "division"),
@ColumnResult(identify = "wage"),
@ColumnResult(identify = "avgSalary"),}))
public class Worker { ... }
Just like the constructor expression I confirmed you for Hibernate 6, Hibernate applies the @SqlResultSetMapping when processing the question consequence. So, the mapping doesn’t have an effect on the executed assertion. It solely modifications how Hibernate handles the question consequence.
Mapping the question consequence utilizing a ResultTransformer
It’s also possible to use Hibernate’s proprietary ResultTransformer to outline the mapping of the question consequence. The ResultTransformer interface is deprecated in model 5, however that shouldn’t stop you from utilizing them. As I clarify in my information to Hibernate’s ResultTransformer, the Hibernate group break up that interface into 2 interfaces in model 6, and you’ll simply migrate your implementations.
However on this case, you don’t even have to implement a customized transformer. Hibernate’s AliasToBeanResultTransformer simply maps your question consequence to your DTO class. You solely have to outline an alias for the return worth of your window perform.
Record<EmployeeInfo> emps = session.createNativeQuery("""
SELECT firstName as "firstName",
lastName as "lastName",
division as "division",
wage as "wage",
avg(wage) OVER (PARTITION BY division) as "avgSalary"
FROM Worker e""")
.setResultTransformer(new AliasToBeanResultTransformer(EmployeeInfo.class))
.getResultList();
Hibernate then executes the native question and calls the AliasToBeanResultTransformer for each document of the consequence set.
Conclusion
Window features are a strong SQL function. Beginning with Hibernate 6, you may as well use them in your JPQL question. As you noticed within the code examples, the JPQL syntax is similar to SQL. So, in case you already know SQL window features, you’ll not encounter any points utilizing them with JPQL.
For those who’re nonetheless utilizing Hibernate 5, you should use window features in native SQL statements. Hibernate executes these statements with out parsing them. So, you should use all options supported by your database. However Hibernate additionally not adjusts your question based mostly on the database-specific SQL dialect.
Impartial of the Hibernate model and the sort of question you’re executing, your question will return an Object[] or a Record of Object[]s. You may map these utilizing Hibernate’s proprietary ResultTransformer, or you should use JPA’s constructor expression in your JPQL question or an @SqlResultSetMapping annotation on your native question.
