Sometimes you do not want to select an entire entity. For example, you may only need id, name, or an aggregated value. EzyJPA can help you map a query result to a custom result class.

Query

Let's say we have a repository method like this:

@EzyQuery(
    value = "select employeeId, firstName " +
        "from ezyfox_jpa_employee " +
        "where employeeId = ?0",
    nativeQuery = true
)
Optional<EmployeeIdAndFirstNameResult> findEmployeeIdAndFirstNameByEmployeeIdOptional(
    String employeeId
);

In this query, we only select 2 columns:

employeeId, firstName

So instead of returning the full Employee entity, the method returns EmployeeIdAndFirstNameResult.

Result Class

With the 2 selected columns above, we need to create a result class with fields in the same order:

@Getter
@Setter
@EzyQueryResult
public class EmployeeIdAndFirstNameResult {
    private String employeeId;
    private String firstName;
}

Do not forget the @EzyQueryResult annotation. EzyJPA uses this annotation to recognize the class as a query result type and create a deserializer for it.

When the query returns multiple selected values, EzyJPA receives the result as an array-like value and binds it to the result class. That means the order of selected columns should match the order of fields in the result class.

For example:

select employeeId, firstName

matches:

private String employeeId;
private String firstName;

If you change the query to:

select firstName, employeeId

then the result class should also follow that order, or you should create another result class that matches the selected values.

Return One Nullable Result

You can return one nullable result directly:

@EzyQuery(
    value = "select employeeId, firstName " +
        "from ezyfox_jpa_employee " +
        "where employeeId = ?0",
    nativeQuery = true
)
EmployeeIdAndFirstNameResult findEmployeeIdAndFirstNameByEmployeeId(
    String employeeId
);

If no row is found, EzyJPA returns null.

Return One Optional Result

You can return Optional<ResultClass>:

@EzyQuery(
    value = "select employeeId, firstName " +
        "from ezyfox_jpa_employee " +
        "where employeeId = ?0",
    nativeQuery = true
)
Optional<EmployeeIdAndFirstNameResult> findEmployeeIdAndFirstNameByEmployeeIdOptional(
    String employeeId
);

If no row is found, EzyJPA returns Optional.empty().

Return A List Result

You can also return a list of result objects:

@EzyQuery(
    value = "select employeeId, firstName " +
        "from ezyfox_jpa_employee " +
        "where firstName = ?0",
    nativeQuery = true
)
List<EmployeeIdAndFirstNameResult> findEmployeeIdAndFirstNameListByFirstName(
    String firstName
);

For list results, EzyJPA maps every row to one result object.

Using resultType

In most cases, EzyJPA can infer the result class from the method return type:

List<EmployeeIdAndFirstNameResult> findEmployeeIdAndFirstNameListByFirstName(
    String firstName
);

or:

Optional<EmployeeIdAndFirstNameResult> findEmployeeIdAndFirstNameByEmployeeIdOptional(
    String employeeId
);

However, you can also declare the result class explicitly with resultType:

@EzyQuery(
    value = "select employeeId, firstName " +
        "from ezyfox_jpa_employee " +
        "where employeeId = ?0",
    nativeQuery = true,
    resultType = EmployeeIdAndFirstNameResult.class
)
EmployeeIdAndFirstNameResult findEmployeeIdAndFirstNameByEmployeeId(
    String employeeId
);

This is useful when the return type alone is not enough, or when you want to make the mapping intention explicit.

JPQL Projection

Custom query result is not only for native SQL. You can use it with JPQL too:

@EzyQuery(
    value = "select e.employeeId, e.firstName " +
        "from Employee e " +
        "where e.employeeId = ?0",
    resultType = EmployeeIdAndFirstNameResult.class
)
EmployeeIdAndFirstNameResult findEmployeeIdAndFirstNameByEmployeeId(
    String employeeId
);

For JPQL projection queries, you usually select fields from the entity:

select e.employeeId, e.firstName from Employee e

Single Column Result

If you only need one column, create a result class with one field:

@Getter
@Setter
@EzyQueryResult
public class EmployeeIdResult {
    private String employeeId;
}

Repository method:

@EzyQuery(
    value = "select employeeId " +
        "from ezyfox_jpa_employee " +
        "where employeeId = ?0",
    nativeQuery = true
)
EmployeeIdResult findEmployeeIdByEmployeeId(String employeeId);

You can also return an optional result:

@EzyQuery(
    value = "select employeeId " +
        "from ezyfox_jpa_employee " +
        "where employeeId = ?0",
    nativeQuery = true
)
Optional<EmployeeIdResult> findEmployeeIdByEmployeeIdOptional(String employeeId);

Aggregation Result

Custom result classes are also useful for aggregation queries.

For example:

@Getter
@Setter
@EzyQueryResult
public class SumBookPriceResult {
    private BigDecimal sum;
}

Repository method:

@EzyQuery(
    value = "select sum(price) " +
        "from book",
    nativeQuery = true
)
SumBookPriceResult sumBookPrice();

Or with JPQL:

@EzyQuery(
    value = "select sum(e.price) " +
        "from Book e",
    resultType = SumBookPriceResult.class
)
SumBookPriceResult sumBookPrice();

Register Result Classes Manually

If your result class is in a scanned package, @EzyQueryResult is enough:

@EzyQueryResult
public class EmployeeIdResult {
    private String employeeId;
}

If you do not scan the package, you can register the result class manually when building the database context:

EzyDatabaseContext databaseContext = new EzyJpaDatabaseContextBuilder()
    .queryResultClass(EmployeeIdResult.class)
    .entityManagerFactory(entityManagerFactory)
    .build();

You can also register multiple result classes:

EzyDatabaseContext databaseContext = new EzyJpaDatabaseContextBuilder()
    .queryResultClasses(
        EmployeeIdResult.class,
        EmployeeIdAndFirstNameResult.class
    )
    .entityManagerFactory(entityManagerFactory)
    .build();

Custom Deserializer

By default, EzyJPA creates a result deserializer automatically for @EzyQueryResult classes.

If you need full control over mapping, you can provide your own deserializer:

public class EmployeeIdResultDeserializer
    implements EzyResultDeserializer<EmployeeIdResult> {

    @Override
    public EmployeeIdResult deserialize(Object data) {
        EmployeeIdResult result = new EmployeeIdResult();
        result.setEmployeeId(String.valueOf(data));
        return result;
    }
}

Then register it:

EzyDatabaseContext databaseContext = new EzyJpaDatabaseContextBuilder()
    .addResultDeserializer(
        EmployeeIdResult.class,
        new EmployeeIdResultDeserializer()
    )
    .entityManagerFactory(entityManagerFactory)
    .build();

Notes

The return type should match the selected result. If you select only employeeId and firstName, return a result class such as EmployeeIdAndFirstNameResult, not the full Employee entity.

For one result, you can return either ResultClass or Optional<ResultClass>.

For many results, return List<ResultClass>.

Use @EzyQueryResult or register the result class manually so EzyJPA can create the deserializer.

The selected column order should match the field order in the result class.

You can see how to connect to multi datasources.

EzyJPA: Custom Query Result