JavaEE Sample::JPA::JPA Native SQL ResultSet Mapping

JpaNativeSqlResultSetMappingTest

In this sample we’re going to query a simple JPA Entity, using the JPA EntityManager Native Query, perform a select operation and map the query result using @SqlResultSetMapping.

@Entity()
@Table(name = "EMPLOYEE_NATIVE_SQL_RESULTSET_MAPPING")
@SqlResultSetMapping(name = "myMapping", entities = {@EntityResult(entityClass = Employee.class, fields = {@FieldResult(name = "identifier", column = "id"), @FieldResult(name = "simpleName", column = "name")})})
public class Employee implements Serializable {

    public Employee();
    private static final long serialVersionUID = 1L;
    @Id()
    private int identifier;
    @Column(length = 50)
    private String simpleName;

    public int getIdentifier();

    public void setIdentifier(int identifier);

    public String getSimpleName();

    public void setSimpleName(String simpleName);
}

The select operation is very simple. We just need to call the API method createNativeQuery on the EntityManager and use the mapping defined on Employee by the @SqlResultSetMapping annotation.

@SuppressWarnings("unchecked")
public List<Employee> get() {
    return em.createNativeQuery("select * from EMPLOYEE_NATIVE_SQL_RESULTSET_MAPPING", "myMapping").getResultList();
}

We’re just going to deploy the application as a web archive. Note the inclusion of the following files:

/META-INF/persistence.xml
/META-INF/create.sql
/META-INF/drop.sql
/META-INF/load.sql

The persistence.xml file is needed of course for the persistence unit definition. A datasource is not needed, since we can now use the new default datasource available in JEE7. We’re also using the new javax.persistence.schema-generation.* propertires to create, populate and drop the database.

@Deployment
public static WebArchive createDeployment() {
    WebArchive war = ShrinkWrap.create(WebArchive.class)
                               .addPackage("org.javaee7.jpa.nativesql.resultset.mapping")
                               .addAsResource("META-INF/persistence.xml")
                               .addAsResource("META-INF/create.sql")
                               .addAsResource("META-INF/drop.sql")
                               .addAsResource("META-INF/load.sql");
    System.out.println(war.toString(true));
    return war;
}

In the test, we’re just going to invoke the only available operation in the EmployeeBean and assert a few details to confirm that the native query was successfully executed.

@Test
public void testJpaNativeSqlResultSetMapping() {
    List<Employee> employees = employeeBean.get();
    assertFalse(employees.isEmpty());
    assertEquals(8, employees.size());
}

JPA Native SQL ResultSet Mapping

Using the EntityManager API to perform native SQL queries and map the result with @SqlResultSetMapping annotation

JpaNativeSqlResultSetMappingTest

Share the Knowledge

Help Improve

Recent Changelog