JPA Queries

Recently started having to use JPA (at the whim of some senior) without having had any proper training on it. As expected I have run into a myraid of issues, one of them being referring to attributes of an embedded primary key class (ie: an entity generated from a table named <tableName>PK). Trying to refer to attributes of the embedded class using standard JPQL would result in errors such as “No data type for node org.hibernate.hql.ast.tree.AggregateNode”.

My temporary solution is to use named native queries instead, allowing me to write straight SQL. For example, in the entity class itself, you would have something like:


@Entity
@Table(name="LOG_ENTRY")
@NamedNativeQueries ({
 @NamedNativeQuery(
   name = "getLatestLogEntryDate",
   query = "SELECT * " +
   "FROM LOG_ENTRY e " +
   "WHERE e.start_date_time = (SELECT MAX(start_date_time) FROM LOG_ENTRY) ",
   resultClass = LogEntry.class
 )
})


public class LogEntry implements Serializable {
 private static final long serialVersionUID = 1L;

 @EmbeddedId
 private LogEntryPK id;

 @Column(name="AVG_TIME")
 private BigDecimal avgTime;

...

}


@Embeddable
public class LogEntryPK implements Serializable {
 //default serial version id, required for serializable classes.
 private static final long serialVersionUID = 1L;

 @Column(name="SERVER_ID")
 private String serverId;

    @Temporal( TemporalType.TIMESTAMP)
 @Column(name="START_DATE_TIME")
 private java.util.Date startDateTime;

    @Temporal( TemporalType.TIMESTAMP)
 @Column(name="END_DATE_TIME")
 private java.util.Date endDateTime;

...

}


This allows reference to startDateTime within the ServiceLogEntryPK class.


Another issue was developing queries that needed to return composite data, which is not itself an entity. For example, returning server_id, start_date_time, and the average of avg_time, grouped by month. This can be done by adding a named native query to the LogEntry class as follows:


@NamedNativeQueries ({
 @NamedNativeQuery(
   name = "getLogEntriesAggregateMonthly",
   query = "SELECT 'All Servers' as server_id, TO_CHAR(start_date_time, 'YYYY-MM') DATE_FIELD, AVG(e.AVG_TIME) as AVG_TIME " + 
   "FROM LOG_ENTRY e " +  
   "WHERE start_date_time >= TO_DATE(?1, 'yy.MM.dd') " + 
   "AND end_date_time <= TO_DATE(?2, 'yy.MM.dd') " +
   "AND server_id like (?3) " +
   "GROUP BY TO_CHAR(start_date_time, 'YYYY-MM')",
         resultSetMapping = "LogSummary"
 )
})
@SqlResultSetMappings({
 @SqlResultSetMapping(name="LogSummary")
})


The SqlResultSetMapping name is just a String token, and can be pretty much any randomly chosen string. (Don't ask me what the purpose of this is)

A good discussion on the weaknesses of JPA queroes can be found here: http://heapspace.blogspot.com/2009/03/jpa-strengths-and-weaknesses.html