-1

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)
du-it
  • 2,561
  • 8
  • 42
  • 80
  • It "doesn't work"? My, that's a descriptive definition of the problem. Perhaps you could state WHAT happens? what exception+stack trace? what result? what SQL query was invoked when you executed it? – Neil Stockton Aug 11 '14 at 07:47

1 Answers1

0

By now I made it work using a database view instead of a table for the @Entity(...) annotation. Yet I don't have the requirement to update the entity. However, it is possible to update a view as long as only one table' column are modified and an INNER JOIN is used: http://dev.mysql.com/doc/refman/5.6/en/view-updatability.html

du-it
  • 2,561
  • 8
  • 42
  • 80