Partner – JPA Buddy – NPI (tag=JPA/Hibernate)
announcement - icon

The right tools can and will save a lot of time. As long as you are using Hibernate and IntelliJ IDEA you can boost your coding speed and quality with JPA Buddy. It will help in a lot of the day-to-day work:

  • Creating JPA entities that follow best practices for efficient mapping
  • Creating DTOs from entities and MapStruct mappers using convenient visual tools
  • Generating entities from the existing database or Swagger-generated POJOs
  • Visually composing methods for Spring Data JPA repositories
  • Generating differential SQL to update your schema in accordance with your changes in entities
  • Autogenerating Flyway migrations and Liquibase changelogs comparing entities with the database or two databases
  • … and a lot more

Simply put, you'll learn and use the best practices of Hibernate and surrounding technology and become a lot more!

Definitely visit the JPA Buddy site to see its features in action closer.

Course – LSD (cat=Persistence)

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:


1. Introduction

In this guide, we'll take a look at SqlResultSetMapping, out of the Java Persistence API (JPA).

The core functionality here involves mapping result sets from database SQL statements into Java objects.

2. Setup

Before we look at its usage, let's do some setup.

2.1. Maven Dependency

Our required Maven dependencies are Hibernate and H2 Database. Hibernate gives us the implementation of the JPA specification.  We use H2 Database for an in-memory database.

2.2. Database

Next, we'll create two tables as seen here:

 name VARCHAR(10));

The EMPLOYEE table stores one result Entity object. SCHEDULE_DAYS contains records linked to the EMPLOYEE table by the column employeeId:

 employeeId BIGINT,
 dayOfWeek  VARCHAR(10));

A script for data creation can be found in the code for this guide.

2.3. Entity Objects

Our Entity objects should look similar:

public class Employee {
    private Long id;
    private String name;

Entity objects might be named differently than database tables. We can annotate the class with @Table to explicitly map them:

@Table(name = "SCHEDULE_DAYS")
public class ScheduledDay {

    private Long id;
    private Long employeeId;
    private String dayOfWeek;

3. Scalar Mapping

Now that we have data we can start mapping query results.

3.1. ColumnResult

While SqlResultSetMapping and Query annotations work on Repository classes as well, we use the annotations on an Entity class in this example.

Every SqlResultSetMapping annotation requires only one property, name. However, without one of the member types, nothing will be mapped. The member types are ColumnResult, ConstructorResult, and EntityResult.

In this case, ColumnResult maps any column to a scalar result type:


The ColumnResult property name identifies the column in our query:

  name = "FridayEmployees",
  query = "SELECT employeeId FROM schedule_days WHERE dayOfWeek = 'FRIDAY'",
  resultSetMapping = "FridayEmployeeResult")

Note that the value of resultSetMapping in our NamedNativeQuery annotation is important because it matches the name property from our ResultSetMapping declaration.

As a result, the NamedNativeQuery result set is mapped as expected. Likewise, StoredProcedure API requires this association.

3.2. ColumnResult Test

We'll need some Hibernate specific objects to run our code:

public static void setup() {
    emFactory = Persistence.createEntityManagerFactory("java-jpa-scheduled-day");
    em = emFactory.createEntityManager();

Finally, we call the named query to run our test:

public void whenNamedQuery_thenColumnResult() {
    List<Long> employeeIds = em.createNamedQuery("FridayEmployees").getResultList();
    assertEquals(2, employeeIds.size());

4. Constructor Mapping

Let's take a look at when we need to map a result set to an entire object.

4.1. ConstructorResult

Similarly to our ColumnResult example, we will add the SqlResultMapping annotation on our Entity class, ScheduledDay. However, in order to map using a constructor, we need to create one:

public ScheduledDay (
  Long id, Long employeeId, 
  Integer hourIn, Integer hourOut, 
  String dayofWeek) { = id;
    this.employeeId = employeeId;
    this.dayOfWeek = dayofWeek;

Also, the mapping specifies the target class and columns (both required):

          @ColumnResult(name="id", type=Long.class),
          @ColumnResult(name="employeeId", type=Long.class),

The order of the ColumnResults is very important. If columns are out of order the constructor will fail to be identified. In our example, the ordering matches the table columns, so it would actually not be required.

@NamedNativeQuery(name = "Schedules",
  query = "SELECT * FROM schedule_days WHERE employeeId = 8",
  resultSetMapping = "ScheduleResult")

Another unique difference for ConstructorResult is that the resulting object instantiation as “new” or “detached”.  The mapped Entity will be in the detached state when a matching primary key exists in the EntityManager otherwise it will be new.

Sometimes we may encounter runtime errors because of mismatching SQL datatypes to Java datatypes. Therefore, we can explicitly declare it with type.

4.2. ConstructorResult Test

Let's test the ConstructorResult in a unit test:

public void whenNamedQuery_thenConstructorResult() {
  List<ScheduledDay> scheduleDays
    = Collections.checkedList(
      em.createNamedQuery("Schedules", ScheduledDay.class).getResultList(), ScheduledDay.class);
    assertEquals(3, scheduleDays.size());
    assertTrue( -> c.getEmployeeId().longValue() == 3));

5. Entity Mapping

Finally, for a simple entity mapping with less code, let's have a look at EntityResult.

5.1. Single Entity

EntityResult requires us to specify the entity class, Employee. We use the optional fields property for more control. Combined with FieldResult, we can map aliases and fields that do not match:

      entityClass = com.baeldung.sqlresultsetmapping.Employee.class,
          @FieldResult(name="name", column="name")})})

Now our query should include the aliased column:

  query="SELECT id as employeeNumber, name FROM EMPLOYEE",
  resultSetMapping = "EmployeeResult")

Similarly to ConstructorResult, EntityResult requires a constructor. However, a default one works here.

5.2. Multiple Entities

Mapping multiple entities is pretty straightforward once we have mapped a single Entity:

  name = "EmployeeScheduleResults",
  entities = {
    @EntityResult(entityClass = com.baeldung.sqlresultsetmapping.Employee.class),
    @EntityResult(entityClass = com.baeldung.sqlresultsetmapping.ScheduledDay.class)

5.3. EntityResult Tests

Let's have a look at EntityResult in action:

public void whenNamedQuery_thenSingleEntityResult() {
    List<Employee> employees = Collections.checkedList(
      em.createNamedQuery("Employees").getResultList(), Employee.class);
    assertEquals(3, employees.size());
    assertTrue( -> c.getClass() == Employee.class));

Since the multiple entity results join two entities, the query annotation on only one of the classes is confusing.

For that reason, we define the query in the test:

public void whenNamedQuery_thenMultipleEntityResult() {
    Query query = em.createNativeQuery(
      "SELECT,,, d.employeeId, d.dayOfWeek "
        + " FROM employee e, schedule_days d "
        + " WHERE = d.employeeId", "EmployeeScheduleResults");
    List<Object[]> results = query.getResultList();
    assertEquals(4, results.size());
    assertTrue(results.get(0).length == 2);

    Employee emp = (Employee) results.get(1)[0];
    ScheduledDay day = (ScheduledDay) results.get(1)[1];

    assertTrue(day.getEmployeeId() == emp.getId());

6. Conclusion

In this guide, we looked at different options for using the SqlResultSetMapping annotationSqlResultSetMapping is a key part to the Java Persistence API.

Code snippets can be found over on GitHub.

Course – LSD (cat=Persistence)

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:

res – Persistence (eBook) (cat=Persistence)
Comments are closed on this article!