web analytics

Data, data everywhere 

Twitter LinkedIn RSS
magnify
Home Java JPA and native SQL queries – part un
formats

JPA and native SQL queries – part un

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 every useful 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 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
@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:

22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
@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:

39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
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.

 
 Share on Facebook Share on Twitter Share on Reddit Share on LinkedIn
2 Comments  comments 

2 Responses

  1. […] a previous article I outlined how one can use JPA’s @SqlResultMapping annotation to permit one to utilize […]

  2. […] written about using native SQL queries with JPA and Java annotations previously; a recent blog post by Lukas Eder nicely outlines the issues with native queries and JPA. However, […]

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>