Hibernate is by far the most popular of the various object-relational mapping tools available for Java, and is one of the ORM toolkits that supports the Java Persistence API, which forms part of the Java EE 7 specification. The current JPA specification version is 2.1 (released April 2013), and you can find the specification for JPA 2.1 online. The reference implementation for JPA 2.1 is Oracle EclipseLink – in years past it was JBoss’ Hibernate.

As with Hibernate’s HQL language, the JPQL query language supported by JPA supports a basic SQL query specification: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY clauses. JPQL also supports outer joins, and most recently JPA 2.1 offers support for an explicit ON condition for outer joins, using the familiar ON keyword and syntax so familiar to developers of SQL applications.

The problem, of course, is that JPQL, as with Hibernate’s HQL language, doesn’t support everyuseful SQL construction you might like to use in an application. I deliberately include the word useful here because not all SQL constructions have the same utility. Nonetheless, SQL constructions such as derived tables, particularly with outer joins, are virtually essential in all SQL applications, and derived tables are not supported in either of the HQL or JPQL languages.

Further complicating matters is that with JPA – as with Hibernate – the underlying assumption is that Java applications will utilize JPA services to retrieve JPA-managed objects from the underlying relational database, and once instantiated as objects by the object-relational mapper, the application will reference and manipulate these objects, updating them as necessary until issuing a save() request from the JPA entity manager.

Of course, in production applications one will want to query the database in all sorts of ways, using SQL’s expressive power to (1) minimize the amount of data that must be processed to compute the intended result, (2) avoid multiple requests sent to the server (the N+1 SELECTs problem), and (3) to minimize the amount of result data that has to be transmitted to the application over the wire. To do this, JPA offers two critically important facilities. The first is that JPA permits the developer to create a query using native SQL, using the EntityManager’s createNativeQuery() method. The second is the ability to specify what JPA terms a result set mapping, using the @ResultSetMapping annotation, so that applications can explicitly tell JPA how to map arbitrary queries to unmanaged Java POJO objects – or plain scalar variables, if one chooses – avoiding the tedious conversion between relational values and Java object types that JPA and other ORM toolkits were designed to help developers avoid in the first place.

In the following example, I construct an SQL query that returns the number of remaining games in April for teams in the LeagueDB database, an example database I constructed with the help of my Conestoga College students in my recent Java Enterprise Applications course using Java EE 7 and Hibernate 4.3.6 as the JPA implementation. Here is the native SQL query:


@SuppressWarnings("unchecked")
   List<Object[]> teamsGames = em.createNativeQuery(
  "SELECT t.*, gameCount " +
  "FROM TEAM t JOIN " +
  "( SELECT teamID as TID, SUM(totalGames) as gameCount " +
  "FROM (SELECT t.teamID, COUNT(g.GAMEID) as totalGames " +
       "FROM LEAGUE l JOIN TEAM t ON(l.LEAGUEID = t.LEAGUE) " +
         "LEFT OUTER JOIN GAME g ON( t.TEAMID = g.HOME ) " +
       "WHERE g.GAMEDATE BETWEEN '2015-04-01' and '2015-04-30' AND (g.HOMESCORE IS NULL AND g.VISITORSCORE IS NULL) " +
       "GROUP BY t.teamID " +
       "UNION ALL " +
       "SELECT t.teamID, COUNT(g.GAMEID) as totalGames " +
       "FROM LEAGUE l JOIN TEAM t ON(l.LEAGUEID = t.LEAGUE) " +
       "LEFT OUTER JOIN GAME g ON( t.TEAMID = g.VISITOR ) " +
       "WHERE g.GAMEDATE BETWEEN '2015-04-01' and '2015-04-30' AND (g.HOMESCORE IS NULL AND g.VISITORSCORE IS NULL) " +
       "GROUP BY t.teamID ) DT " +
  "GROUP BY teamID ) DT ON(DT.TID = t.teamID) " +
  "ORDER BY t.teamName", "TeamsAndGamesMapping")
    .getResultList();

Not altogether complicated, but the lack of support in HQL or JPQL for derived tables in a query’s FROM clause make it necessary to write this SQL request using a native SQL dialect; in this case, Apache Derby 10.11. (Aside: it wasn’t that long ago when including a UNION query expression within an SQL derived table would cause many DBMS parsers to choke on a perfectly legal SQL statement. Happily, Derby 10.11 isn’t one of these).

The em.createNativeQuery() method contains an overload so that one can specify the name of a mapping – in this case “TeamsandGamesMapping” – so that JPA can create the correct POJO objects and types when iterating through the result set. In this case, note that the query is returning all of the attributes of the Team table, which permits instantiation of Team objects as the result set is processed, but in addition the query’s SELECT list contains a scalar value, gameCount, containing the number of games remaining in April for each team. So to specify this mapping, one can utilize the @SqlResultSetMapping annotation:


@SqlResultSetMapping(
  name = "TeamsAndGamesMapping",
  entities = {
       @EntityResult(
            entityClass = Team.class,
  fields = {
       @FieldResult(name = "teamID", column = "TEAMID"),
       @FieldResult(name = "league", column = "LEAGUE"),
       @FieldResult(name = "sponsor", column = "SPONSOR"),
       @FieldResult(name = "teamName", column = "TEAMNAME"),
       @FieldResult(name = "website", column = "WEBSITE"),
       @FieldResult(name = "headCoach", column = "HEADCOACH"),
       @FieldResult(name = "assistantCoach", column = "ASSTCOACH"),
       @FieldResult(name = "GM", column = "manager"),
       @FieldResult(name = "trainer", column = "TRAINER") }
  ) },
  columns = @ColumnResult(name = "gameCount", type = Long.class) )

I’ve been deliberately verbose with the @SqlResultSetMapping annotation above, as the @FieldResult annotations for the Team class are only required if the expression in the SELECT list has a different name than what’s specified in the @Entity mapping for the Team class. Otherwise Hibernate (JPA) can figure out how to call the Team() constructor for each row in the result set.

The @ColumnResult annotation, on the other hand, tells JPA how to map the gameCount attribute returned in the SELECT list, and consequently is mandatory.

The @SqlResultSetMapping annotation is coupled with the @Entity annotation for the JPA-managed class; if more than one, they are combined into a set of annotations wrapped by the @SqlResultSetMappings annotation (note the plural). With these annotations my Team class looks like this:


import java.util.*;
import javax.persistence.*;
/* Class definition for the Team object in LeagueDB.
* With result set mappings, the "column" is the name of the attribute in
* the SELECT list of the query, which may be aliased. The "name" is the property
* name in the Team object that the value maps to.
*
* Correct capitalization of "name" properties that match the Java POJO properties
* is essential. */
@Entity
@SqlResultSetMappings({
  @SqlResultSetMapping(
  name = "TeamsAndGMMapping",
  entities = {
  @EntityResult(
  entityClass = Team.class,
  fields = {
  @FieldResult(name = "teamID", column = "TEAMID"),
  @FieldResult(name = "league", column = "LEAGUE"),
  @FieldResult(name = "sponsor", column = "SPONSOR"),
  @FieldResult(name = "teamName", column = "TEAMNAME"),
  @FieldResult(name = "website", column = "WEBSITE"),
  @FieldResult(name = "headCoach", column = "HEADCOACH"),
  @FieldResult(name = "assistantCoach", column = "ASSTCOACH"),
  @FieldResult(name = "GM", column = "MANAGER"),
  @FieldResult(name = "trainer", column = "TRAINER")
  } ),
  @EntityResult(
  entityClass = Staff.class,
  fields = {
  @FieldResult(name = "staffID", column = "STAFFID"),
  @FieldResult(name = "firstName", column = "FIRSTNAME"),
  @FieldResult(name = "lastName", column = "LASTNAME"),
  @FieldResult(name = "streetAddress", column = "STREETADDRESS"),
  @FieldResult(name = "city", column = "CITY"),
  @FieldResult(name = "stateProvince", column = "STATE_PROVINCE"),
  @FieldResult(name = "country", column = "COUNTRY"),
  @FieldResult(name = "postalCode", column = "POSTALCODE"),
  @FieldResult(name = "phone", column = "PHONE"),
  @FieldResult(name = "mobile", column = "MOBILE"),
  @FieldResult(name = "email", column = "EMAIL") } )
  } ),
  @SqlResultSetMapping(
  name = "TeamsAndGamesMapping",
  entities = {
  @EntityResult(
  entityClass = Team.class,
  fields = {
  @FieldResult(name = "teamID", column = "TEAMID"),
  @FieldResult(name = "league", column = "LEAGUE"),
  @FieldResult(name = "sponsor", column = "SPONSOR"),
  @FieldResult(name = "teamName", column = "TEAMNAME"),
  @FieldResult(name = "website", column = "WEBSITE"),
  @FieldResult(name = "headCoach", column = "HEADCOACH"),
  @FieldResult(name = "assistantCoach", column = "ASSTCOACH"),
  @FieldResult(name = "GM", column = "manager"),
  @FieldResult(name = "trainer", column = "TRAINER") }
  ) },
  columns = @ColumnResult(name = "gameCount", type = Long.class) )
  } )
@Table(name = "TEAM",schema = "GPAULLEY")
public class Team {
  @Id
  @Column(name = "TEAMID")
  private String teamID;
  @ManyToOne(optional=false)
  @JoinColumn(name="LEAGUE", nullable=false)
  private League league;
  @Column(name = "SPONSOR")
  private String sponsor;
  @Column(name = "TEAMNAME")
  private String teamName;
  @Column(name = "WEBSITE")
  private String website;
    @OneToMany(targetEntity=Roster.class, mappedBy="team")
    private Set<Roster> roster = new HashSet<Roster>();
  @ManyToOne(optional=false)
  @JoinColumn(name="HEADCOACH", nullable=false)
  private Staff headCoach;
  @ManyToOne(optional=false)
  @JoinColumn(name="ASSTCOACH", nullable=false)
  private Staff assistantCoach;
  @ManyToOne(optional=false)
  @JoinColumn(name="MANAGER", nullable=false)
  private Staff GM;
  @ManyToOne(optional=false)
  @JoinColumn(name="TRAINER", nullable=false)
  private Staff trainer;
    public Team()
  {
  }
    public String getTeamID()
  {
     return teamID;
  }
    public void setTeamID( String teamID )
  {
     this.teamID = teamID;
  }
    public League getLeague()
  {
     return league;
  }
    public void setLeague( League league )
  {
     this.league = league;
  }
    public String getSponsor()
  {
     return sponsor;
  }
    public void setSponsor( String name )
  {
     this.sponsor = name;
  }
    public String getTeamName()
  {
     return teamName;
  }
    public void setTeamName( String name )
  {
     this.teamName = name;
  }
    public String getWebsite()
  {
     return website;
  }
    public void setWebsite( String url )
  {
     this.website = url;
  }
    public Staff getHeadCoach()
  {
     return headCoach;
  }
    public void setHeadCoach( Staff coach )
  {
     this.headCoach = coach;
  }
    public Staff getAssistantCoach()
  {
     return assistantCoach;
  }
    public void setAssistantCoach( Staff coach )
  {
     this.assistantCoach = coach;
  }
    public Staff getTrainer()
  {
     return trainer;
  }
    public void setTrainer( Staff coach )
  {
     this.trainer = coach;
  }
    public Staff getGM()
  {
     return GM;
  }
    public void setGM( Staff manager )
  {
     this.GM = manager;
  }
    public Set<Roster> getRoster()
    {
    return roster;
    }
    public void setRoster( Set<Roster> roster )
    {
    this.roster = roster;
    }
    public void addPlayer( Roster teammate )
  {
  ((Roster) roster).setTeam( this ); // With inverse=true, the setTeam method causes the UPDATE to be issued
  roster.add(teammate);
  }
    public void deletePlayer( Roster teammate )
  {
  ((Roster) roster).setTeam( new Team() ); // With inverse=true, the setTeam method causes the UPDATE to be issued
  roster.remove(teammate);
  }
}

For this (simple) example, annotating the Team class to support native SQL query SQL mappings doesn’t seem like such a bad idea. However, in part deux of this article, we’ll see where this leads with other constructions, in particular when we wish to construct a set of unmanaged POJO objects from the result set of a native SQL query.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply