Extracting Complex Report Data from Relational Databases with Native Queries and JPA

For reporting and other tasks, it may not be enough to create JPA entites that are mapped to physical tables or views. Complex queries may be constructed on the fly. Large datasets may be split over different tables yet with the same column layout making views a difficult approach to use. Group by clauses may change depending on the users reporting needs. There are quite a few scenarios when dealing with complex reporting and data requirements such as controlling market risk, calculating profit and loss figures or else complex ad-hoc reporting needs. What options do we have when there is need to encapsulate the reporting logic in an Java EE component such as a CDI Bean or an EJB and offering a service interface for remote clients?

  • Executing a native query on an entity manager instance and handling a list of lists of objects?
  • Retrieve the JDBC Connection Object and do some “magic”?
  • Resort to Spring or even MyBatis?

JPA is a very elegant solution when it comes to CRUD (create, read update delete). What is less known: JPA also has something in stock for this more complex querying and reporting need!

Entities without a Table

Usually, you define a JPA entity on a Java POJO with the annotations @Entity and @Table (this could also be defined in a xml mapping file – I’ll use annotations in this post).

An entity may also be simply used as the result row of an arbitrary native query. And yes: native queries are the right tool for the job.

Keep in mind that, as Gavin King (the inventor of Hibernate) stated in a remarkable comment, ‘Systems like Hibernate [and JPA] are designed as “leaky abstractions”‘ (see here). In our case, the “leaky abstraction” is the possibility to use native SQL in JPA and therefore by-passing the ORM layer. It is possible and wanted to use plain SQL in situations where the ORM is not the right tool for the job. What’s nice: a native query even does the mapping to a POJO of the query results for you.

The population of the POJO and mapping of result set columns to POJO properties is done by your JPA provider. Just do not annotate your entity with the @Table tag! In the example below I use a much stripped down entity that represents a “tableless” entity holding P&L data (Profit and Loss) by portfolioId and valDate (valuation date).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
...
@Entity
public class PortfolioAggregation implements Serializable {
 
    private static final long serialVersionUID = 1L;
 
    //This resembles the business key:
    private Date valDate;
    private long portfolioId;
    //KEY - END
 
    private double plNet;
    private double plGross;
 
    public PortfolioAggregation() {
    }
 
    @Id
    @Temporal(TemporalType.DATE)
    public Date getValDate() {
        return valDate;
    }
 
    public void setValDate(Date valDate) {
        this.valDate = valDate;
    }
 
    @Id
    public long getPortfolioId() {
        return portfolioId;
    }
 
    public void setPortfolioId(long portfolioId) {
        this.portfolioId = portfolioId;
    }
 
    public double getPlNet() {
        return plNet;
    }
 
    public void setPlNet(double plNet) {
        this.plNet = plNet;
    }
 
    public double getPlGross() {
        return plGross;
    }
 
    public void setPlGross(double plGross) {
        this.plGross = plGross;
    }
 
    //Class definition truncated from here ... 
 
}

Now, when creating the native query, you simply submit in addition to the SQL, the class type of our entity that is to be populated from each resultset row of the query.

1
2
3
4
String sql = "select a.valDate valDate, a.portfolioId portfolioId, sum(plNet) plNet, sum(plGross) plGross FROM ..."; //SQL truncated!
Query q = entityManager.createNativeQuery(sql, PortfolioAggregation.class);@SuppressWarnings("unchecked")
List<PortfolioAggregation> aggs = q.getResultList();

The SQL is not complete. It’s just to show how it is basically used and actually much more complex. It is important that each column name from the result relates to a property name of the entity. As you may see, there are also sum() aggregate functions in the query. I used so-called alias names for the aggregat functions, that in turn relate to a property name of our PortfolioAggregation class. This information is needed, so that the JPA implementation is able to properly populate your entity instances. In my eyes a nice feature. Callers of the portfolio aggregation service that receive a list of PortfolioAggregation objects may use them freely: in reporting tools such as Jasper Reports, show the data in tables at the client side and even manipulate it (since setters are included – which is not necessary) to show changes in different scenarios or implement other methods that manipulate the data (transiently) according to your client needs. Also you should be aware that this entity is not persistable! It is, from a persistence perspective, read only.

What I do not show in this code snippet is the way how the actual SQL query is built. This sometimes can become messy. Database Views or Named native queries might help, but more often plain string concatenation is used :-(((

Currently I am looking for ways around this dilemma. In a short conversation on Twitter Lukas Eder pointed me to a possible solution: jOOQ. jOOQ delivers a fluent API to construct SQL Queries and results may be mapped to POJOs. Quite interesting and I’ll definitively have a look at it and blog about my experiences with it – stay tuned!

 

One thought on “Extracting Complex Report Data from Relational Databases with Native Queries and JPA

  1. Pingback: MyBatis-CDI: Persistence Alternative for Java EE | Dinesh Ram Kali.

Leave a Reply

Your email address will not be published. Required fields are marked *