@ElementCollection with @Embeddable in native SQL query

Hibernate ORM
1

Hi,

I’m struggling with joining and selecting @Embeddable in native query.
Here is an example:

data model:

CREATE SEQUENCE person_seq;
CREATE TABLE IF NOT EXISTS person(
    id BIGINT PRIMARY KEY NOT NULL DEFAULT NEXTVAL ('person_seq')
);

CREATE TABLE IF NOT EXISTS person_phones(
    person_id BIGINT NOT NULL,
    phone_number VARCHAR(256) NOT NULL,
    dial_code VARCHAR(10),
    CONSTRAINT FK_PERSON_ID FOREIGN KEY (person_id) REFERENCES person (id)
);

ORM:

@Entity
@Table(name = "person")
public class Person {
    @Id
    private Long id;

    @ElementCollection
    @CollectionTable(name = "person_phones", joinColumns = @JoinColumn(name = "person_id"))
    @Column(name = "phone_number")
    private Set<PhoneNumberWithDialCode> phoneNumbers;
}

@Embeddable
public class PhoneNumberWithDialCode {
    @Column(name = "phone_number")
    private String phoneNumber;

    @Column(name = "dial_code")
    private String dialCode;
}

and the query (please don’t ask why we use native here. I just simplified much more complex query to reproduce the issue on simpler case):

return entityManager.createNativeQuery("" +
                "SELECT {p.*}, {pp.*} FROM person p LEFT JOIN person_phones pp ON p.id = pp.person_id")
                .unwrap(NativeQuery.class)
                .addEntity("p", Person.class)
                .addJoin("pp", "p.phoneNumbers")
                .getResultList();

This query ends with NullPointerException:

java.lang.NullPointerException
	at org.hibernate.loader.custom.sql.SQLQueryParser.resolveProperties(SQLQueryParser.java:229)
	at org.hibernate.loader.custom.sql.SQLQueryParser.resolveCollectionProperties(SQLQueryParser.java:186)
	at org.hibernate.loader.custom.sql.SQLQueryParser.substituteBrackets(SQLQueryParser.java:147)
	at org.hibernate.loader.custom.sql.SQLQueryParser.process(SQLQueryParser.java:64)
	at org.hibernate.loader.custom.sql.SQLCustomQuery.<init>(SQLCustomQuery.java:117)
	at org.hibernate.engine.query.internal.NativeQueryInterpreterStandardImpl.createQueryPlan(NativeQueryInterpreterStandardImpl.java:70)
	at org.hibernate.engine.query.spi.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:213)
	at org.hibernate.internal.AbstractSharedSessionContract.getNativeQueryPlan(AbstractSharedSessionContract.java:549)

I already debugged it and the problem is as following:
In SqlQueryReturnProcessor.addCollection() method persister is not added to context, as the @ElementCollection is neither one-to-many nor mamy-to-many, so the following if-statement is not entered, however then in SQLQueryParser.resolveProperties() method, it tries to call selectFragment() method on persister field which is of course null in this case, which causes NPE:

image
image1782×1323 255 KB

Any idea what could be wrong here? I don’t know if I’m doing someting wrong or it’s a bug.
The above code was tested on Hibernate 5.2.4.Final
Any ideas what could be wrong here?

I also tried following:

SELECT {p.*}, {pp.element.*} FROM ...

same result

SELECT {p.*}, {pp.element.phone_number} FROM ...

different exception:
org.hibernate.QueryException: No column name found for property [element.phone_number] for alias [pp] [SELECT {p.*}, {pp.element.phone_number} FROM ...

I’d appreciate any help as I run out of ideas.
For any help, thanks in advance!

2

@Wojciech_Nowak sounds like you are trying to use native query alias injection with an @ElementCollection, and that is not supported by Hibernate ORM. I would kindly ask you to try to create a reproducer with our test case template and if you are able to reproduce the issue, create a new improvement request in our issue tracker and attach that reproducer.

Since you’ve already looked at the code, and possibly identified what’s missing to support this, that would be a great opportunity to make a direct contribution to Hibernate ORM. Let us know if we can help you achieve that.