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!

Exception in service.test() with cause = 'org.hibernate.exception.SQLGrammarException: could not extract ResultSet' and exception = 'org.hibernate.exception.SQLGrammarException: could not extract ResultSet' javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet — sentinel, Nov 16 '17 at 20:35

wypieprz · Accepted Answer · 2017-11-17T00:16:52.130

0

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);

edited Nov 17 '17 at 00:16

answered Nov 16 '17 at 22:14

wypieprz

7,981
4
43
46

I was trying solve issue this way, but i received: org.postgresql.util.PSQLException: The column name id1_7_0_ was not found in this ResultSet. but I have not colummn like this... – sentinel Nov 16 '17 at 23:09
The PSQL exception is related to id column. Add `@FieldResult(name="id", column="id")` to the fields mapping and update the query e.g. `SELECT a.id, a.name FROM ExampleEntity a WHERE a.id = 5` – wypieprz Nov 17 '17 at 00:14

JPA NamedNativeQuery with SqlResultMapping

1 Answers1