JSBach JSBach - 1 year ago 74
Java Question

How to map a Native Query to a POJO, when I do not have any Entity on my project?

I am translating an ETL process from a tool to a Java batch API application. In this ETL process. In the current version (using the tool) we have many SQL statements that join different tables in order to generate the desired output. Translating to Java, JPA is now available.

I would like to use native queries. This would be nice because it would not require creating entities for every table used in the query and I could use POJOs for the result of the queries (also, I would not need to rewrite the queries). Reading this answer I know I could use

. The problem is that I do not have any entity in my project, so I do not know where to put this annotation. Is there anywhere I can put this annotation so the entity manager finds it?

PS: in my proof of concepts I am currently manually converting from an array of objects to the POJO, but I really don't like this approach.

Adding the
annotation to the POJO will cause my application not to start:

Caused by: org.hibernate.HibernateException: Missing table: MyTable

I am not sure (searching for it right now), but I think it could be caused by this property in my persistence.xml

<property name="hibernate.hbm2ddl.auto" value="validate"/>

Answer Source

Actually I found the answer I was looking for:

I can define @SqlResultSetMapping's behavior using XML in orm.xml, so this definition:

        name = "BookValueMapping",
        classes = @ConstructorResult(
                targetClass = BookValue.class,
                columns = {
                    @ColumnResult(name = "id", type = Long.class),
                    @ColumnResult(name = "title"),
                    @ColumnResult(name = "version", type = Long.class),
                    @ColumnResult(name = "authorName")}))

Would be defined in XML like this:

<sql-result-set-mapping name="BookValueMappingXml">
    <constructor-result target-class="org.thoughts.on.java.jpa.value.BookValue">
        <column name="id" class="java.lang.Long"/>
        <column name="title"/>
        <column name="version" class="java.lang.Long"/>
        <column name="authorName"/>

Allowing me to do define it without needing an entity.