JPA and native SQL queries – part deux

[former_member204193]

In a previous article I outlined how one can use JPA’s @SqlResultMapping annotation to permit one to utilize JPA’s createNativeQuery() method and map an SQL native query to a result set composed of something other than JPA-managed object instances.

In this follow-on article I’ll comment on a recent JPA extension to the @SqlResultMapping annotation, the @ConstructorResult annotation, which provides a much greater degree of flexibility for Java EE applications. As with my last article, I will be using the LeagueDB database, an example database for a hockey league I constructed with the help of my students in my recent Java Enterprise Applications course, using Java EE 7 with Apache Derby 10.11 as the database server and Hibernate 4.3.6 as the JPA implementation.

In this example, we will create a query that returns the top 10 hockey players with the most points (goals and assists) in the entire league. Player statistics in the leagueDB database are kept in a seperate “playerstats” table that contains their scoring statistics for each season, as illustrated in this portion of the database’s E-R diagram (this from Oracle 11g):

Here’s the query to retrieve the top ten players in scoring, all time:

@SuppressWarnings("unchecked")

  List<PlayersTotalPoints> allPlayers = em.createNativeQuery(

    "SELECT firstname, lastname, t.teamname, points " +

    "FROM (SELECT p.playerid, firstname, lastname, coalesce( sum(goals + assists), 0) as points " +

          "FROM player p LEFT OUTER JOIN playerstats ps ON(ps.player = p.playerID) " +

   "GROUP BY p.playerid, p.firstname, p.lastname ) DT " +

       "JOIN ROSTER r ON( DT.playerid = r.player ) " +

       "JOIN TEAM t ON( t.teamid = r.team ) " +

       "JOIN LEAGUE l ON( t.league = l.leagueid ) " +

    "WHERE l.leaguename = 'National Hockey League' " +

    "ORDER BY points DESC ", "TotalPointsMapping").setMaxResults(10)

    .getResultList();

The query includes an outer join to ensure that all players are considered in the computation of the top ten list, even if they don’t have a row in the PLAYERSTATS table – perhaps a result of injury or a recent signing. Also note that we are using the setMaxResults() method, which automatically adds the “FETCH FIRST 10 ROWS ONLY” syntax to the statement before it is sent to the Derby 10.11 server.

The query above illustrates an example where a report is desired from the database but instantiating all of the objects from the database to compute the result is unnecessary and expensive; we are only interested in the top ten players. To store these results, we can fashion a non-managed class and subsequently iterate over instances of that class within our application as desired. Here is the PlayersTotalPoints class:

public class PlayersTotalPoints {

  private String firstName;

  private String lastName;

  private String teamName;

  private Long points;

  public PlayersTotalPoints( String fn, String ln, String tn, Long pts ) {

  this.firstName = fn;

  this.lastName = ln;

  this.teamName = tn;

  this.points = pts;

  }

  public String getFirstName()

  {

  return firstName;

  }

  public void setFirstName( String name )

  {

  this.firstName = name;

  }

  public String getLastName()

  {

  return lastName;

  }

  public void setLastName( String name )

  {

  this.lastName = name;

  }

  public String getTeamName() {

  return teamName;

  }

  public void setTeamName(String teamName) {

  this.teamName = teamName;

  }

  public Long getPoints()

  {

  return points;

  }

  public void setPoints( Long value )

  {

  this.points = value;

  }

}

The important part of the PlayersTotalPoints class is the constructor. Using the constructor’s signature, we can specify an @SqlResultMapping for this result using the @ConstructorResult annotation, so that JPA (Hibernate) knows how to instantiate each PlayersTotalPoints object:

 

@SqlResultSetMapping(

   name = "TotalPointsMapping",

   classes = @ConstructorResult(

                     targetClass = PlayersTotalPoints.class,

                     columns = {

                  @ColumnResult(name = "lastName"),

                @ColumnResult(name = "firstName"),

                 @ColumnResult(name = "teamName"),

                 @ColumnResult(name = "points", type = Long.class)

                      }

             )

)

The useful @ConstructorResult annotation, then, provides the JPA developer with a straightforward mechanism to permit using JPA’s result set mapping facilities for arbitrary queries.

There’s just one problem.

Note that the source code for the PlayersTotalPoints class doesn’t contain the matching @SqlResultMapping annotation, which is the place one would expect to put it. This is because with JPA 2.1 one cannot use the @SqlResultMapping annotation on an arbitrary Java class – you can only use it with a class that utilizes the @Entity annotation. Consequently, you have the situation where you want to use a non-managed class, which is likely independent of other application logic in your Java EE application, but you have to pollute the source code of a shared, JPA-managed class to do so.

Aside: It is possible to specify the mapping in another way, using an XML file – the default name of this file is orm.xml, and like persistence.xml it is placed in the META-INF directory for your application. However, using the built-in Java annotations is significantly more convenient, and less error-prone, that using the XML solution. See Thorben Jannsen’s blog for an example.

This tight coupling of application-specific query logic and JPA-managed entities is unfortunate. Not only is it undesirable to modify or extend the annotations for a manged entity – when all you want to do is handle an unmanaged set of values – but the namespace for all of the mapping names is global across all Java EE application components that use the JPA-managed class(es). For an enterprise application utilizing hundreds of tables, thousands of columns, and billions of rows, there may be scores, if not hundreds, of various reporting queries sprinkled throughout an application. Ideally, the @SqlResultMapping annotation should be de-coupled from @Entity, to reduce this source-code coupling of Java EE application components.