JPA SQLResultSetMapping for SQL Aliases not Working At All?

Question

Native SQL with aliased field names + remapping to receive managed entities is required for more complex queries with joined tables.

However, the mapping of the SQL aliases leads to an exception where the aliased fields cannot be found. Can anybody detect an error in the code below, or is SQLResultSetMapping broken? (The sample below is intentionally simple to allow quick checking)

RDBMS H2, DDL

create table A(
   ID INTEGER DEFAULT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
   VAL VARCHAR(10)
);
insert into A (val) values ('val1');
insert into A (val) values ('val2');

Java class

@Entity

@NamedNativeQuery(name = "queryall",
    query="select ID as AID, val from A",
    resultSetMapping = "mapping") 

@SqlResultSetMapping(name = "mapping",
    entities = @EntityResult(
        entityClass = A.class,
            fields = {@FieldResult(name = "ID", column = "AID")})
)  


public class A implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private Integer id;
    @Column(name = "VAL")
    private String val;

    public A() {
    }

    public A(Integer id) {
        this.id = id;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getVal() {
        return val;
    }

    public void setVal(String val) {
        this.val = val;
    }

    @Override
    public String toString() {
        return "entities.A[ id=" + id +", val="+val+ " ]";
    }


    public static void main(String[] args) {
        EntityManagerFactory entityManagerFactory =  
                Persistence.createEntityManagerFactory("JavaApplication6PU");
        EntityManager em = entityManagerFactory.createEntityManager();
        Query sqlQuery = em.createNamedQuery("queryall");
        List list = sqlQuery.getResultList();
        for (Iterator<A> iterator = list.iterator(); iterator.hasNext();) {
            a = iterator.next();
            System.out.println(String.format("entity %s, managed: %s", a, em.contains(a)));    
        }
     }
}

Execution stops with exception:

[EL Warning]: 2018-01-12 21:45:42.748--UnitOfWork(1823014131)--Exception     
[EclipseLink-6044] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd):     
org.eclipse.persistence.exceptions.QueryException
Exception Description: The primary key read from the row [DatabaseRecord(
    A.ID => null
    A.VAL => val1)] during the execution of the query was detected to be null.  
Primary keys must not contain null.
Query: ResultSetMappingQuery(name="queryall" referenceClass=A sql="select ID as AID, val from A")

This, in other words, means: No mapping has taken place -> aliased fields not found

The same when the mapping is announced in adhoc Queries.

Query sqlQuery = em.createNativeQuery("select ID as AID, val from A","mapping");

If resultClass is used instead of resultSetMapping and no SQL aliases exist, the output is as it should be. (This proves that there is no misspelling of fields or any other error)

@NamedNativeQuery(name = "queryall",
    query="select ID, val from A",
    resultClass = A.class)

Output:

entity entities.A[ id=1, val=val1 ], managed: true
entity entities.A[ id=2, val=val2 ], managed: true

JPA SQLResultSetMapping for SQL Aliases not Working At All?

0 Answers0