JPA Native SQL

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/
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=JpaNativeSqlTest

Using the EntityManager API to perform native SQL queries

JpaNativeSqlTest

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

@Entity()
@Table(name = "EMPLOYEE_NATIVE_SQL")
public class Employee implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id()
    private int id;
    @Column(length = 50)
    private String name;

    public Employee();

    public Employee(String name);

    public int getId();

    public void setId(int id);

    public String getName();

    public void setName(String name);
}

The select operation is very simple. We just need to call the API method createNativeQuery on the EntityManager.

@SuppressWarnings("unchecked")
public List<Employee> get() {
    return em.createNativeQuery("select * from EMPLOYEE_NATIVE_SQL", Employee.class).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")
                               .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 testNativeSql() {
    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 project by Roberto Cortez
  • 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/

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

Good Luck!