Is it possible with JPA to query a database to get an entity filled with an additional field belonging to another table/entity?
I have a reservations table holding a foreign key to a record(an entity) in another table pois which has to columns of interest: poiId and poiType.
Instead of having a field ReservationEntity.poi (to finally obtain poi.poiType) I want to have a ReservationEntity.poiId and ReservationEntity.poiType and I wonder if it's possible to achieve this through a NamedQuery:
@NamedQuery(name="ReservationEntity.findByRfId", query="SELECT r, p.poiType FROM ReservationEntity r LEFT JOIN PoiEntity p ON r.poiId = p.poiId WHERE r.rfId = :rfId")
...since you read my question you can imagine that this DOESN'T work. ;-)
Is it possible to do it in such a kind?
Here's the exception:
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'POITYPE' in 'field list'
Error Code: 1054
Call: SELECT reservationId, endTime, notification, poiId, POITYPE, startTime, status, timeZone, tstamp, type FROM reservations WHERE (reservationId = ?)
bind => [1 parameter bound]
Query: ReadObjectQuery(name="ReservationEntity.findById" referenceClass=ReservationEntity sql="SELECT t1.reservationId, t1.authInfo, t1.endTime, t1.evsp, t1.notification, t1.poiId, t1.POITYPE, t1.startTime, t1.status, t1.timeZone, t1.tstamp, t1.type FROM reservations t1 LEFT OUTER JOIN pois t0 ON (t1.poiId = t0.poiId) WHERE (t1.reservationId = ?)")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)