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

Question

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 @SqlResultSetMapping. 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 @Entity 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 1

Actually I found the answer I was looking for:

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

@SqlResultSetMapping(
        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"/>
    </constructor-result>
</sql-result-set-mapping>

Allowing me to do define it without needing an entity.

Answer 2

You can map native sql to POJO using JPA. The POJO just needs @Entity and a @Id. A simple example:

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class TodoQueryModel {

    @Id
    private Long id;

    private String description;

    public Long getId() {
        return id;
    }

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

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    @Override
    public String toString() {
        return "TodoQueryModel [id=" + id + ", description=" + description
            + "]";
    }
}

Some method in your JPA impl:

private void queryWithNativeSQL() {
    List<TodoQueryModel> todoList = em.createNativeQuery("SELECT id, description FROM [whatever you want as long as it returns 'id' and 'description' as column names]", TodoQueryModel.class)
        .setParameter(1, "foobar");
        .getResultList();

    for (TodoQueryModel todo : todoList) {
        System.out.println(todo);
    }

    System.out.println("Size: " + todoList.size());
}

You can also use @Column(name="barfoo") to map columns to attributes who's names don't match.

The @Id column needs to uniquely identify the instance in the JPA context.

Answer 3

In the past (before JPA) we used iBatis as ORM tool (now called Mybatis). I'm still a big fan of it because you have a lot of flexibility in the way to write your SQL. You can really optimize your queries, espacially if you want to decide in which order joins are executed. All SQL statements and mappings (columns to POJO and vice-versa) are done in XML file. In the current version it is also possible to use annotations I think like you would to with JPA.

More info: http://mybatis.github.io/mybatis-3/