Jpa: How to convert a native query result set to POJO class collection

java

jpa-mapping

pojo-conversion

sql-result-set

byNikita Barsukov·Dec 1, 2024

Mapping a native query to POJOs in JPA engages the @SqlResultSetMapping and EntityManager. Here's an example to kick things off:

@SqlResultSetMapping(
    name = "MyPojoMapping",
    entities = @EntityResult(
        entityClass = MyPojo.class,
        fields = {
            @FieldResult(name = "id", column = "id"), // These mappings, much like my cup of coffee, are completely Java bean-based!
            @FieldResult(name = "name", column = "name"),
            // ... sprinkle in more fields as needed
        }
    )
)

Query query = entityManager.createNativeQuery("SELECT id, name FROM my_table", "MyPojoMapping");
List<MyPojo> pojos = query.getResultList();

In this configuration, you'll define the mapping on the entity class. The MyPojo class should have fields that match your query columns so that Hibernate can spawn and populate your POJO objects.

JPA mapping techniques at a glance

JPA provides a set of tools and approaches that can be tailored to fit different scenarios beautifully. Think of JPA as your Swiss Army knife for query results and POJO class conversion.

Mapping native queries to POJO constructors (JPA 2.1)

Introduced in JPA 2.1, @ConstructorResult allows developers to map native queries straight onto a POJO's constructor:

@SqlResultSetMapping(
    name="PojoConstructorMapping",
    classes = @ConstructorResult(
        targetClass = MyPojo.class,
        columns = {
            @ColumnResult(name = "id", type = Long.class),
            @ColumnResult(name = "name", type = String.class)
            // ... because every constructor likes a little class (get it?)
        }
    )
)

Key insight here? Sequencing. When using @ConstructorResult, the constructor parameters of MyPojo must dance on the same beat as the column results specified in the mapping.

Storing named queries and result set mappings in orm.xml

For those who swear by XML configuration, orm.xml allows developers to define mappings outside of Java classes. Consider using orm.xml if you prefer a centralized and tidy XML configuration. Annotation hoarders, you've been warned. 😉

Dealing with intricate mappings using custom methods

In the grand theatre of mapping, sometimes the automatic assistants may not suffice. When faced with a complex mapping scenario, consider crafting a custom map method:

public MyPojo mapRowToObject(Object[] row) {
    MyPojo pojo = new MyPojo();
    pojo.setId((Long) row[0]);  // Who needs magic when you have casting spells?
    pojo.setName((String) row[1]);
    // ... and they lived happily ever after, mapping more fields as they pleased. The end.
    return pojo;
}

This approach empowers you with total control over data assignment from the result set to the POJO fields. Use this when you need to tell Java exactly what to do.

Advanced mapping intensifies

Sometimes, reaching for manual mapping and reflection can unlock those hidden passageways to extreme flexibility.

Reflection for POJO dressing on the fly

Java's powerful reflection capabilities enable dynamic attribute assignment, which you can leverage to create and populate POJOs in a generic fashion:

public <T> T mapRowToPojo(Class<T> type, Object[] row) {
    // Dynamically create instance and set fields, because we like to keep POJOs on their toes!
}

Don't go overboard with reflection though; the wild realm of reflection can cost you in performance and type safeness.

Enter JPA 2.1 and non-entity POJOs

JPA 2.1's @ConstructorResult is like the secret weapon in your arsenal — allowing exact mapping to non-entity POJOs:

@SqlResultSetMapping(
    name="PojoMapping",
    classes = @ConstructorResult(
        targetClass = MyPojo.class,
        columns = {
            @ColumnResult(name = "id", type = Long.class),
            @ColumnResult(name = "name", type = String.class)
        }
    )
)

Remember to synchronize the order and type of parameters expected by the POJO constructor with the SQL query to keep your database and your Java code in harmony.

Hibernate session to the rescue

For those times when you need to get deep with Hibernate features, you can unwrap the EntityManager and get your hands on the Hibernate session:

List<SomeDto> dtos = session.createSQLQuery("SELECT ...")
    .setResultTransformer(Transformers.aliasToBean(SomeDto.class))
    .list();

This enables you to use Hibernate methods that JPA's @SqlResultSetMapping might not support, like the Transformers.aliasToBean method.

Watch out for type and conversion pitfalls

One common mistake in mapping involves mismatching SQL result types with Java field types. Be wary of this and ensure data conversions are adequately handled to avoid exceptions that could ruin a programmer's day faster than a spilled coffee over a brand-new keyboard.

explain-codes / Java / Jpa: How to convert a native query result set to POJO class collection

Linked

Jpa or JDBC, how are they different?



Hibernate error - QuerySyntaxException: users is not mapped [from users]



How to map a composite key with JPA and Hibernate?



Sql JPA - Multiple columns as primary key



How to solve the “failed to lazily initialize a collection of role” Hibernate exception

