JPA Native SQL ResultSet Mapping

Run
How to run the sample
The source code for this sample can be found in the javaee7-samples GitHub repository. The first thing we need to do is to get the source by downloading the repository and then go into the samples folder:
git clone git://github.com/javaee-samples/javaee7-samples.git
cd javaee7-samples/jpa/native-sql-resultset-mapping/
Now we are ready to start testing. You can run all the tests in this sample by executing:
mvn test
Or you can run individual tests by executing one of the following:
mvn test -Dtest=JpaNativeSqlResultSetMappingTest

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

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());
}

Share the Knowledge

Find this sample useful? Share on

There's a lot more about JavaEE to cover. If you're ready to learn more, check out the other available samples.

Help Improve

Find a bug in the sample? Something missing? You can fix it by editing the source, making the correction and sending a pull request. Or report the problem to the issue tracker

Recent Changelog

  • Jul 15, 2014: Removed header license. the licensing is now referenced in the license file in the root of the project by Roberto Cortez
  • Feb 02, 2014: Fix include expression in test case by Aslak Knutsen
  • Jan 28, 2014: Added name and description to pom files by Roberto Cortez
  • Jan 28, 2014: Added test for native-sql-resultset-mapping project by Roberto Cortez
  • Nov 06, 2013: Update employeebean.java by fotofinder
  • Sep 17, 2013: Removing netbeans configuration file by Arun Gupta
  • Aug 27, 2013: Moving the source code from http://svn.java.net/svn/glassfish~svn/branches/arun/javaee7-samples/samples/ by Arun Gupta
How to help improve this sample
The source code for this sample can be found in the javaee7-samples GitHub repository. The first thing you need to do is to get the source by downloading the repository and then go into the samples folder:
git clone git://github.com/javaee-samples/javaee7-samples.git
cd javaee7-samples/jpa/native-sql-resultset-mapping/

Do the changes as you see fit and send a pull request!

Good Luck!