Skip to main content

Question on JPA @SqlResultSetMapping when using a native query

6 replies [Last post]
bbergquist
Offline
Joined: 2007-04-02

I have a native query that I am creating using EntityManager.createNativeQuery. The query will only return scalar columns of various entities, but will not return any entities.

From the spec, I cannot tell if I need a SqlResultSetMapping annotation or not. On page 72 of the spec, it says "Scalar result types can be included in the query result by specifying the ColumnResult annotation in the metadata." In the annotation definition, the EntityResult property is optional and the ColumnResult property is optional.

So do I need to specify a SqlResultSetMapping with only ColumnResult annotations?

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
mvatkina
Offline
Joined: 2005-04-04

This is what the spec says:

[i]3.6.6 SQL Queries
Queries may be expressed in native SQL. The result of a native SQL query may consist of entities, scalar values, or a combination of the two. [...] When multiple entities are returned by a SQL query, the entities must be specified and mapped to the
column results of the SQL statement in a SqlResultSetMapping metadata definition.[/i]

So no, if you return back only scalar values, you don't need SqlResultSetMapping.

Regards,
-marina

bbergquist
Offline
Joined: 2007-04-02

Thank you very much for the insight. I think the spec should be updated to handle this case and an example given. The one example given does have a mix of entity and scalar values and the scalar is described in the mapping.

A little more information. If you do specify an SqlResultSetMapping that only has scalar values, the result is an Object array with the correct size of the number of Objects from the result set, but each element of the array is null. I'm not sure if this is the correct behavior or not.

mvatkina
Offline
Joined: 2005-04-04

It's probably not - the SqlResultSetMapping should be either rejected or handled correctly.

thanks,
-marina

dparker61
Offline
Joined: 2007-07-24

I found this thread while trying to figure out how to get data back from a native query.

I need to generate a bunch of different monotonically increasing ids. These are not primary keys, and I need access to an id value before it gets assigned to an entity. In regular SQL I would use an update/select on an id generation table, so I'm trying to do the same thing in JPA.

Given a native sql query like "select id from tab where type = 'x'", how do I go about getting the id value (an integer) back from the query? None of the examples in the spec or other sources seem to give an example of this. When I try
Integer id = (Integer) query.getSingleResult()
I get a ClassCastException that indicates I am trying to mis-cast a Vector.

A pointer to some example code would be greatly appreciated. Thanks.

frank_martinez
Offline
Joined: 2007-08-31

Hi.
This is what i did, and it works as expected:

@Stateless(name="ImagesServiceImpl")
public class ImagesServiceImpl implements ImagesService {

@PersistenceContext
EntityManager em;

public Long getNewImageId() {
Query q = em.createNativeQuery(
"SELECT nextval('global_next_image_id_seq') AS ID");
try {
return (Long)((List) q.getSingleResult()).get(0);
}
catch(Exception ex) {
throw new RuntimeException(ex.getMessage(), ex);
}
}

}

poenya_gua
Offline
Joined: 2009-05-07

i have same problem too.
but i tried frank_martinez's code and it work

thx