JPA NamedNativeQuery with SqlResultMapping

Question

I have a problem with native query. I have following entity:

    @Entity
@NamedNativeQueries({
    @NamedNativeQuery(name =  ExampleEntity.Q.getTestQuery, query = "SELECT a.name as name FROM ExampleEntity a WHERE a.id = 5", resultSetMapping = "nameMapping")
})

@SqlResultSetMapping(
    name = "nameMapping",
    entities = @EntityResult(
        entityClass = ExampleEntity.class,
        fields = {
            @FieldResult(name="name", column="name")
        }))


@Table(name = "ExampleEntity")
public class ExampleEntity implements Serializable {

    public static class Q {
        public static final String getTestQuery = "getTestQuery";
    }

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    @Column(name = "id")
    private Long id;

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

    @Column(name = "surname")
    private String surname;

and I am trying to call this query in my @Service with following code:

Query qz = em.createNativeQuery(ExampleEntity.Q.getTestQuery, "nameMapping");
qz.getResultList();

it returns error:

org.hibernate.exception.SQLGrammarException: could not extract ResultSet

It is only simple example, but shows my problem. Many thanks in advance!

Answer 1

The problem is that createNativeQuery method expects SQL query string as the first parameter rather than the name used to refer to the SQL query itself. Here is the method definition:

public Query createNativeQuery(String sqlString, String resultSetMapping);

which brings us to the following:

Query qz = em.createNativeQuery("SELECT a.id, a.name FROM ExampleEntity a WHERE a.id = 5", 
                                "nameMapping");

---

The preferred solution is to use createNamedQuery instead which expects the name of the SQL query defined in a metadata:

public Query createNamedQuery(String name);

so based on the above we can fix the exception with the following:

Query qz = em.createNamedQuery(ExampleEntity.Q.getTestQuery);