Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Native Query throws weird exception 
Hello Michael,
I assume that the error is because the tour id is not being found in the returned result set. If so, it is likely because when EclipseLink searches for the "tour_id" field in the returned result, it gets null - you should probably put an alias on your coalesce field so that it returns as "tour_id" if that is the missing field. Be sure that the alias will be returned in the same case as the field is defined in JPA - case isn't important to the database but it is for java string comparisons. 
Best Regards,
Chris



Michael Simons wrote:

Hello,

Model: Vehicle<1:*>Tour : A vehicle has associated one or more trips (tours) that are driven
with that vehicle.

In the following query I want to select the vehicles in a certain scenario granted to the curent
user (that's what claim is for) and their tours, but also vehicles that have no tours
associated, so I use a left join:
  Query query = em.createNativeQuery ("SELECT a.*, coalesce(b.tour_id, 1), b.active, ... "+
    "FROM vehicle a "+
    "LEFT JOIN tour b on b.vehicle_id = a.vehicle_id "+
    "WHERE a.scenario_id = ? "+
    "  AND (a.claim_id is null or a.claim_id IN "+
      " (SELECT claim_id from login_user_claim z where z.login_user_id = ?))",
    "VehicleTourMapping");
    // The ... stands for the rest of fields of tour

The build-in MySQL "coalesce(...)" function returns the first of its arguments that's not null.
But when running the query an exception (see below) is thrown. The same exception is thrown when
not using coalesce(), of course.

The reduced query
  "SELECT * "+
    "FROM vehicle a "+
    "LEFT JOIN tour b on b.vehicle_id = a.vehicle_id "+
    "WHERE a.scenario_id = ? "+
    "  AND (a.claim_id is null or a.claim_id IN "+
      " (SELECT claim_id from login_user_claim z where z.login_user_id = ?))",
works, however.

Does anybody have any hints why my "trick" with "coalesce" doesn't work?

Kind Regards, Michael


Exception [EclipseLink-6044] (Eclipse Persistence Services - 2.0.1.v20100213-r6600):
org.eclipse.persistence.exceptions.QueryException
Exception Description: The primary key read from the row [DatabaseRecord(
	tour.tour_id => null
...more fields...
	tour.login_user_id => 1)] during the execution of the query was detected to be null.  Primary
keys must not contain null.
Query: ResultSetMappingQuery(referenceClass=Tour sql="SELECT a.*, coalesce(b.tour_id, 1),
b.active, ...more fields...  FROM vehicle a JOIN tour b on b.vehicle_id = a.vehicle_id where
a.scenario_id = ? and (a.claim_id is null or a.claim_id IN (SELECT claim_id from
login_user_claim z where z.login_user_id = ?))")
	at
org.eclipse.persistence.exceptions.QueryException.nullPrimaryKeyInBuildingObject(QueryException.java:863)
	at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:470)
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

Back to the top