How do I tell Hibernate to not create a table for this Entity?

Question

I'm using SqlResultSetMapping and the Entity annotations (SqlResultSetMapping requires an Entity with an Id) to tell Hibernate how to populate instances of Foo with native query results data.

Non-persisted entity:

@SqlResultSetMapping(name = "fooMapping", entities = @EntityResult(entityClass = Foo.class))
@Entity
public class Foo {
    @Id
    public Long row_id;
    public String name;
}

Native query:

String sql = "SELECT id AS row_id, friendlyName AS name FROM SomeTable"; 
Query q = JPA.em().createNativeQuery(sql, "fooMapping");
List<Foo> fooList = q.getResultList();

The problem is, a table called "Foo" gets created automatically for me (using Play! Framework in dev mode), but Foo is not a model and should not be persisted.

How do I instruct hibernate not to create this table?

score 2 · Accepted Answer · answered Oct 29 '13 at 20:46

Using @ConstructorResult will work great once it's available for your persistence layer. Until then, there is a Hibernate-specific approach using an org.hibernate.SQLQuery and an org.hibernate.transform.ResultTransformer that does not depend on @SqlResultSetMapping. Because a POJO is populated, Hibernate finds no @Entity to automatically turn into a table.

Non-persisted POJO:

public class Foo {
    public Long row_id;
    public String name;
}

ResultTransformer:

public static class FooResultTransformer implements ResultTransformer {

    @Override
    public List transformList(List list) { return list; }

    @Override
    public Object transformTuple(Object[] tuple, String[] aliases) {
        List<String> aliasList = Arrays.asList(aliases);
        Foo foo = new Foo();
        foo.row_id = ((Number) getValue(tuple, aliasList, "row_id", 0L))
            .longValue();
        foo.name = (String) getValue(tuple, aliasList, "name", null);
        return foo;
    }

    private static Object getValue(Object[] tuple, List<String> aliases,
            String field, Object defaultValue)
    {
        // unchecked for berevity
        if (tuple[aliases.indexOf(field)] == null) {
            return defaultValue;
        }
        return tuple[aliases.indexOf(field)];
    }

}

Native SQLQuery:

String sql = "SELECT id AS row_id, friendlyName AS name FROM SomeTable";
Session session = JPA.em().unwrap(Session.class);
SQLQuery q = session.createSQLQuery(sql);

q.setResultTransformer( new FooResultTransformer() );
List<Foo> fooList = q.list();

score 1 · Answer 2 · answered Oct 14 '13 at 20:12

Unfortunately this isn't easy...

If you are using JPA 2.1 support for @ConstructorResult (seems there's only support in hibernate 4.3.0.Beta2 so you might not be using), you can use @ConstructorResult as follows:

@SqlResultSetMapping(name="fooMapping", 
  classes={ 
    @ConstructorResult(targetClass=Foo.class, columns={
        @ColumnResult(name="row_id", type=Integer.class),
        @ColumnResult(name="name", type=String.class)
    })
  }
)

public class Foo {

    public Long row_id;
    public String name;

    public Foo(Long rowId, String name) {
    ...
    }

}

Looking forward to this; I'm going to hold out for an answer that works on JPA 2.0 or non-beta Hibernate. — Chris Betti, Oct 15 '13 at 03:53

How do I tell Hibernate to not create a table for this Entity?

2 Answers2