<--Back

JPA Tutorial 2 - Working with Queries 1

In this tutorial, you experiment with queries to get a feel for how the interface actually behaves. For this tutorial we stick to using JUnit 4 to write a "test" for each of our tutorials.

Background

The Entity Manager allows you to create queries using its own query language called Java Persistence Query Language, JPQL. Rather than cover the syntax directly, this tutorial presents you with several queries and asks you to use each one of these queries against the entities we created in JPA Tutorial 1 - Getting Started.

Queries in JPQL are not like SQL queries. Where a SQL query deals with tables and columns, a JPQL query deals with objects and attributes. In many cases there is no difference, but it is possible that a single object maps to multiple tables (think of a many to many relationship with join tables). When you are working with JPQL, you don't think about join tables; you think in terms of objects and their relationships. As you'll find out, the name of your Objects and attributes are case sensitive.

For this tutorial, we continue using JUnit 4 by writing a unit test for each of of a series of provided queries. Each unit test will perform some set up, execute a query and then it will programmatically validate the results. In many cases we won't know the results, so we'll actually run the unit test, figure out the results then effectively document how the query interface works by putting asserts in our unit tests.

Term
Description
JUnit
A Unit Test tool. JUnit allows you to create individual test methods. Each test method runs alone and performs any necessary setup, executes code under test, validates that the results programmatically (so you don't have to look at a bunch of output) and then cleans up after itself).
Unit Test
A single test method that tests one thing. For this tutorial, we will use one unit test method per query. To denote a method as a test method, we use the @Test annotation.
Query
An expression executed against the database to create, retrieve, updated= and remove records from tables.
JPQL
A query language where you express what you want in terms of Objects and relationships between objects instead of directly in SQL using tables and columns.

The following queries will get you started experimenting with the JPA query interface. Your task is to do the following for each query:
  1. Review the query
  2. Predict the results before you do anything
  3. Compose a unit test to exercise the query (make sure your test produces no output)
  4. Execute the test to see if your prediction was correct
  5. Make the test pass


The Queries

Queries

In these example, the variable "em" is an entity manger initialized just like it was in the first tutorial.

Empty String
Setup: None required
  • em.createQuery("");

Unknown Class
Setup: None required
  • em.createQuery("from IncorrectClassName");

Minimal "Get All"
Setup: None required
  • em.createQuery("from Person").getResultList();

Successfully Get a Single Object
Setup: Insert exactly 1 Person entity in the database
  • final Person p = (Person) em.createQuery("from Person").getSingleResult();

Unsuccessfully Try to get a Single Object When There Are None
Setup: Make sure there are no Person entities in the database
  • em.createQuery("from Person").getSingleResult();

Unsuccessfully Try to get a Single Object With Too Many
Setup: Insert two or more Person entities in the database
  • em.createQuery("from Person").getSingleResult();

Find By Primary Key
Setup: Insert a Person in the database, make sure to get the key of the object inserted
  • final Person p = em.find(Person.class, personKey);

Unsuccessfully Find by Primary Key
Setup: None required
  • final Person p = em.find(Person.class, -42);

Search Using Query Parameter and Storing Result as List<?>
Setup: Insert one person record where the Person's first name = "Brett".
    final List<?> list = em.createQuery("from Person where p.firstName = ?1")
        .setParameter(1, "Brett").getResultList();

Search Using Query Parameter and Storing Result as List<Person>
Setup: Insert one person record where the Person's first name = "Brett".
    final List<Person> list = em.createQuery(
        "from Person where firstName = ?1").setParameter(1, "Brett")
        .getResultList();

Do Find by Primary Key and Queries Return == Objects
Setup: Insert one person record and store the primary key. Also make sure the first name of the person equals "Brett".
    final Person pByKey = em.find(Person.class, personKey);
 
    final Person pByWhere = (Person) em.createQuery(
        "SELECT p from Person p where firstName='Brett'")
        .getSingleResult();

Use Wrong Class Name
Setup: None required
  • em.createQuery("from PERSON").getSingleResult();

Use Wrong Field Name
Setup: None required
  • em.createQuery("from Person p where p.FirstName='Brett'");

Use Column Name Instead of Field Name
Setup: None required, but maybe insert a single person whose first name = "Brett".
  • em.createQuery("from Person p where p.firstName='Brett'").getResultList();

Use a Parameter but Provide Wrong Index
Setup: None required
  • em.createQuery("from Person p where p.firstName=?1").setParameter(0, "Brett");

Set Parameter Where There are None: Version 1
Setup: None required
  • em.createQuery("from Person p where p.firstName='Brett'").setParameter(1, "Brett");

Set Parameter When There Are None: Version 2
Setup: None required
  • em.createQuery("from Person p where p.firstName='?1'").setParameter(1, "Brett");


JPA Tutorial 2 Project Setup

For this next section, where you see <project>, replace it with JpaTutorial2.

Create Java Project

Next we need to create a Java project. We'll keep the source separate from the bin directory:
  1. Pull down the File menu and select New:Project
  2. Select Java Project and click on Next
  3. Enter a project name: <project>, again read this to know why I did not use a space in the project name.
  4. Make sure "Create new project in workspace" is selected.
  5. Make sure the JRE selected is 1.5.x or higher. If such a JRE does not show in the list, you can add it through Window->Preferences->JAVA->Installed JRE's.
  6. Select Create separate source and output folders
  7. Click Finish

Create folders and packages

  1. Expand your <project> folder
  2. Select the src directory
  3. Right-click, select new:Folder
  4. Use the name META-INF
  5. Make sure <project> is still selected, right-click and select New:Source Folder
  6. Enter test and click OK

Add Required Libraries

We now need to add two libraries. Note that these steps assume you've already worked through the first tutorial and are working in the same workspace. If you, you'll need to create user libraries. Review Creating User Libraries.
  1. Edit the project properties. Select your <project> and either press alt-enter or right-click and select properties.
  2. Select Java Build Path
  3. Click on the Libraries tab
  4. Click on Add Library
  5. Select User Libraries and click Next
  6. Select JPA_JSE by click on the check box
  7. Click OK
  8. Click on Add Library again
  9. Click on JUnit
  10. Click Next
  11. In the pull-down list, select JUnit 4
  12. Click Finish
  13. Click OK

If you'd like some background information on JUnit, please go here.

Configure Persistence Unit

We now need to create the Persistent Unit definition. We are going to create a file called persistence.xml in the src/META-INF directory with the following contents:

persistence.xml

<persistence>
    <persistence-unit name="examplePersistenceUnit" 
                      transaction-type="RESOURCE_LOCAL">
        <properties>
            <property name="hibernate.show_sql" value="false" />
            <property name="hibernate.format_sql" value="false" />
 
            <property name="hibernate.connection.driver_class" 
                      value="org.hsqldb.jdbcDriver" />
            <property name="hibernate.connection.url" 
                      value="jdbc:hsqldb:mem:mem:aname" />
            <property name="hibernate.connection.username" value="sa" />
 
            <property name="hibernate.dialect" 
                      value="org.hibernate.dialect.HSQLDialect" />
            <property name="hibernate.hbm2ddl.auto" value="create" />
        </properties>
    </persistence-unit>
</persistence>

The Steps

  1. Expand your <project>
  2. Select the src directory
  3. Find the src/META-INF directory (if one does not exist, right-click, select New:Folder, enter META-INF and press enter)
  4. Right click the src/META-INF, select new:File.
  5. Enter persistence.xml for the name and press "OK" (Note: all lowercase. It won't make a difference on Windows XP but it will on Unix.)
  6. Copy the contents (above) into the file and save it

Copy Entities From JpaTutorial1

  1. Open up your Tutorial1 project
  2. Expand the src folder
  3. Select entity
  4. Right-click, select Copy
  5. Open up your Tutorial2 project
  6. Select the src folder
  7. Right-click, select Paste

A First Test/Example

Test Setup

We have a bit of setup/initialization code we need to do before we can get started. We have seen all of this code before in Tutorial 1. The difference here is that we are going to work though some refactorings to get to where we were in tutorial 1. Here are the various parts:
Configure the Logger
    BasicConfigurator.configure();
    Logger.getLogger("org").setLevel(Level.ERROR);

Create the Entity Manager Factory
    final EntityManagerFactory emf = Persistence
        .createEntityManagerFactory("examplePersistenceUnit");

Create the Entity Manager
    final EntityManager em = emf.createEntityManager();

Create Entities
    final Address a1 = new Address("A Rd.", "", "Dallas", "TX", "75001");
    final Person p1 = new Person("Brett", 'L', "Schuchert", a1);
 
    final Address a2 = new Address("B Rd.", "S2", "OkC", "OK", "73116");
    final Person p2 = new Person("FirstName" + System.currentTimeMillis(),
        'K', "LastName", a2);

Use the Entity Manager
    em.getTransaction().begin();
    em.persist(p1);
    em.persist(p2);
    em.flush();

Perform a Query and Verify it Works
    final int numberFound = em.createQuery("Select p from Person p")
        .getResultList().size();
    assertEquals(2, numberFound);

The Whole Thing

Here's all of this put together. Note that we're going to refactor this heavily coming up.
package entity;
 
import static org.junit.Assert.assertEquals;
 
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
 
import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;
import org.junit.Test;
 
public class JpaApiTests {
 
    @Test
    public void example1InsertTwoPeople() {
        BasicConfigurator.configure();
        Logger.getLogger("org").setLevel(Level.ERROR);
 
        final EntityManagerFactory emf = Persistence
                .createEntityManagerFactory("examplePersistenceUnit");
        final EntityManager em = emf.createEntityManager();
 
        final Address a1 = new Address("A Rd.", "", "Dallas", "TX", "75001");
        final Person p1 = new Person("Brett", 'L', "Schuchert", a1);
 
        final Address a2 = new Address("B Rd.", "S2", "OkC", "OK", "73116");
        final Person p2 = new Person("FirstName" + System.currentTimeMillis(),
                'K', "LastName", a2);
 
        em.getTransaction().begin();
        em.persist(p1);
        em.persist(p2);
        em.flush();
 
        final int numberFound = em.createQuery("Select p from Person p")
                .getResultList().size();
        assertEquals(2, numberFound);
    }
}

Get it Running

After creating your Test Class, verify that it runs and that this test passes:
  1. Right-click anywhere in your class' editor pane.
  2. Select Run As:JUnit Test


Second Example and Refactoring

There are several things for which we need some support code:
  • Per test method setup
  • Per test method cleanup
  • One-time logger initialization
  • Inserting a single record

Before putting all of this together, let's examine each of these things.

Per test method setup

public class JpaApiTests {
    private EntityManagerFactory emf;
    private EntityManager em;
 
    @Before
    public void initEmfAndEm() {
        emf = Persistence.createEntityManagerFactory("examplePersistenceUnit");
        em = emf.createEntityManager();
    }
}

This creates two fields. We then use the JUnit 4 @Before annotation to initialize that before the execution of each individual unit test. For details, please see here.

Per test method cleanup

    @After
    public void closeEmfAndEm() {
        em.close();
        emf.close();
    }

This example uses the JUnit 4 @After annotation to cleanup up resources we've allocation after the execution of each individual unit test. For details, please see here.

One-time logger initialization

    @BeforeClass
    public static void initializeLogging() {
        BasicConfigurator.configure();
        Logger.getLogger("org").setLevel(Level.ERROR);
    }
This example uses the JUnit 4 @BeforeClass annotation to perform one-time initialization for the whole class. For details, please see here.

In this case, the first line in the method performs basic configuration of the Log4J logging system. The second line sets the default logging level for any class whose package starts with org to ERROR. This significantly reduces the output. It is possible to reduce the output one level further by setting it to FATAL.

Inserting a single record

    private int insertPerson() {
        final Address a1 = new Address("A Rd.", "", "Dallas", "TX", "75001");
        final Person p1 = new Person("Brett", 'L', "Schuchert", a1);
 
        if (!em.getTransaction().isActive()) {
            em.getTransaction().begin();
        }
        em.persist(p1);
        return p1.getId();
    }

Rewrite and New Method

With these changes in hand, we can rewrite the previous test method as follows:
    @Test
    public void example1InsertTwoPeople() {
        insertPerson();
        insertPerson();
 
        final int numberFound = em.createQuery("Select p from Person p")
                .getResultList().size();
        assertEquals(2, numberFound);
    }

Here's a second test to justify all of this refactoring.
    @Test
    public void example2() {
        final int primaryKey = insertPerson();
        final Person p = (Person) em.find(Person.class, primaryKey);
        assertNotNull(p);
    }

Putting it all Together

And finally, here's all of the above changes together in one place.
package entity;
 
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
 
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
 
import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
 
public class JpaApiTests {
    private EntityManagerFactory emf;
    private EntityManager em;
 
    @BeforeClass
    public static void initializeLogging() {
        BasicConfigurator.configure();
        Logger.getLogger("org").setLevel(Level.ERROR);
    }
 
    @Before
    public void initEmfAndEm() {
        emf = Persistence.createEntityManagerFactory("examplePersistenceUnit");
        em = emf.createEntityManager();
    }
 
    @After
    public void closeEmfAndEm() {
        em.close();
        emf.close();
    }
 
    @Test
    public void example1InsertTwoPeople() {
        insertPerson();
        insertPerson();
 
        final int numberFound = em.createQuery("Select p from Person p")
                .getResultList().size();
        assertEquals(2, numberFound);
    }
 
    @Test
    public void example2() {
        final int primaryKey = insertPerson();
        final Person p = (Person) em.find(Person.class, primaryKey);
        assertNotNull(p);
    }
 
    private int insertPerson() {
        final Address a1 = new Address("A Rd.", "", "Dallas", "TX", "75001");
        final Person p1 = new Person("Brett", 'L', "Schuchert", a1);
 
        if (!em.getTransaction().isActive()) {
            em.getTransaction().begin();
        }
        em.persist(p1);
        return p1.getId();
    }
}


One Possible Solution

package entity;
 
import java.util.List;
 
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.NoResultException;
import javax.persistence.NonUniqueResultException;
import javax.persistence.Persistence;
 
import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;
import org.hibernate.hql.ast.QuerySyntaxException;
import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
 
public class QueriesTest extends Assert {
    private EntityManagerFactory emf;
    private EntityManager em;
 
    @BeforeClass
    public static void initLogger() {
        // Produce minimal output.
        BasicConfigurator.configure();
 
        // Comment this line to see a lot of initialization
        // status logging.
        Logger.getLogger("org").setLevel(Level.ERROR);
    }
 
    @Before
    public void initEmfAndEm() {
        emf = Persistence.createEntityManagerFactory("examplePersistenceUnit");
        em = emf.createEntityManager();
    }
 
    @After
    public void closeEmAndEmf() {
        em.close();
        emf.close();
    }
 
    @Test(expected = IllegalArgumentException.class)
    public void unsuccessfulWithBadExceptionThrownEmptyQuery() {
        Logger.getLogger("org").setLevel(Level.FATAL);
        try {
            // This one got me and I wasted several hours looking at
            // the wrong rabbit hole. I was paying attention to the
            // error but not looking at the line that was failing.
            em.createQuery("");
        } finally {
            Logger.getLogger("org").setLevel(Level.ERROR);
        }
    }
 
    @Test
    public void unsuccessfulUnknownClass() {
        try {
            // This fails because IncorrectClassName is not registered.
            // If we were using JEE rather than JSE, this implies that
            // there is no class named IncorrectClassName anywhere
            // in the class path that has the @Entity annotation (or
            // is mapped via XML).
            em.createQuery("from IncorrectClassName");
        } catch (IllegalArgumentException e) {
            assertEquals(e.getCause().getClass(), QuerySyntaxException.class);
            assertEquals(
                    ("org.hibernate.hql.ast.QuerySyntaxException: " + 
                    "IncorrectClassName is not mapped " +
                    "[from IncorrectClassName]"),
                    e.getMessage());
        }
    }
 
    @Test
    public void successfulNotUsingSelect() {
        em.createQuery("from Person").getResultList();
    }
 
    @Test
    public void successfulSingleResult() {
        clearPersonTable();
        insertPerson();
 
        // This query has the potential to fail since it is returning
        // all Person entities, but it does not because I've only
        // inserted one.
        final Person p = (Person) em.createQuery("from Person")
                .getSingleResult();
        assertEquals("Brett", p.getFirstName());
    }
 
    @Test(expected = NoResultException.class)
    public void unsuccessfulSingleResultNoEntries() {
        // Notice that if we just want to get all rows from
        // an Entity's table, this is the minimal query
        em.createQuery("from Person").getSingleResult();
    }
 
    @Test(expected = NonUniqueResultException.class)
    public void unsuccessfulSingleResultTooManyEntries() {
        insertPerson();
        insertPerson();
 
        // This will fail because we expect a single result
        // but in fact there are 2 results returned.
        em.createQuery("from Person").getSingleResult();
    }
 
    @Test
    public void successfulFindByPrimaryKey() {
        final int personKey = insertPerson();
 
        // Note, we provide Person.class as the first parameter
        // so the underling method, which is a generic method
        // can return the right type. Also, because we provide
        // the class, the only thing that might happen is that
        // we do not find a Person in the Person table. It is
        // not possible for find to return the wrong type since
        // it picks up its table name from the Person.class.
        final Person p = em.find(Person.class, personKey);
        assertEquals("Brett", p.getFirstName());
    }
 
    @Test
    public void unsuccessfulLookupByKeyNothingFound() {
        clearPersonTable();
 
        // Note the lack of an "expected = ..." in the @Test
        // annotation. Find returns null if it cannot find
        // the object with the provided key. It does not throw
        // an exception.
        final Person p = em.find(Person.class, -42);
        assertNull(p);
    }
 
    @Test
    public void successfulSearchUsingQueryParameter() {
        insertPerson();
 
        // Note, the return type of this method is List<?>, not List<Person>.
        // See the next method for the other option...
        final List<?> list = em.createQuery("from Person where firstName = ?1")
                .setParameter(1, "Brett").getResultList();
        assertEquals(1, list.size());
    }
 
    /**
     * This method does the same thing as the one above it. But to avoid a
     * warning about type safety I am using the annotation
     * 
     * @SuppressWarnings. When you start writing Data Access Objects, you'll
     *                    probably go this route.
     * 
     * For those of you who know generic parameters, it is not possible to get
     * this to work in a type-safe manner due to "erasure." Look up "java
     * generics erasure".
     * http://today.java.net/pub/a/today/2003/12/02/explorations.html
     */
 
    @SuppressWarnings("unchecked")
    @Test
    public void theOtherOption() {
        insertPerson();
 
        final List<Person> list = em.createQuery(
                "from Person where firstName = ?1").setParameter(1, "Brett")
                .getResultList();
        assertEquals(1, list.size());
 
    }
 
    @Test
    public void successfulSameInMemoryObjectsReturnedFromDifferntQueries() {
        final int personKey = insertPerson();
 
        final Person pByKey = em.find(Person.class, personKey);
 
        final Person pByWhere = (Person) em.createQuery(
                "SELECT p from Person p where firstName='Brett'")
                .getSingleResult();
 
        // are these objects == (same object in memory)?
        assertSame(pByKey, pByWhere);
    }
 
    @Test(expected = IllegalArgumentException.class)
    public void unsuccessfulCaseWrongOnClass() {
        // fails because we're naming a class, not a table
        // So instead of PERSON we must use Person
        em.createQuery("from PERSON").getSingleResult();
    }
 
    @Test(expected = IllegalArgumentException.class)
    public void unsuccessfulWrongFieldNameUsedInWhereWithNamedPerson() {
        insertPerson();
 
        // failes because the attribute is not called FirstName but
        // is instead called firstName (first letter should be
        // lower case following the java beans standard.
        em.createQuery("from Person p where p.FirstName='Brett'");
    }
 
    @Test
    public void successfulColumnNameNotCaseSensitive() {
        insertPerson();
 
        // Note that we are not qualifying FirstName with p,
        // so it is interpreted as a column name rather than
        // a fieldName that must follow java beans naming
        // conventions
        em.createQuery("from Person p where FirstName='Brett'").getResultList();
    }
 
    @Test(expected = IllegalArgumentException.class)
    public void unsuccessfulSettingParamterWithWrongIndex() {
        // Indexes are 1-based, not 0-based.
        em.createQuery("from Person p where FirstName='Brett'").setParameter(0,
                "Brett");
    }
 
    @Test(expected = IllegalArgumentException.class)
    public void unsuccessfulSettingParameterWhereThereAreNone() {
        // There's no parameter here, this simply fails
        em.createQuery("from Person p where FirstName='Brett'").setParameter(1,
                "Brett");
    }
 
    @Test(expected = IllegalArgumentException.class)
    public void unsuccessfulDoNotQuoteStringParameters() {
        em.createQuery("from Person p where FirstName='?1'").setParameter(1,
                "Brett");
    }
 
    /**
     * Even though we **begin** a transaction, we never commit it. So when we
     * close the em, nothing that was flushed will actually be committed.
     * 
     */
    private int insertPerson() {
        final Address a1 = new Address("A Rd.", "", "Dallas", "TX", "75001");
        final Person p1 = new Person("Brett", 'L', "Schuchert", a1);
 
        if (!em.getTransaction().isActive()) {
            em.getTransaction().begin();
        }
        em.persist(p1);
        return p1.getId();
    }
 
    private void clearPersonTable() {
        if (!em.getTransaction().isActive()) {
            em.getTransaction().begin();
        }
 
        em.createQuery("delete from Person").executeUpdate();
    }
}


Individual Page Links

Tutorial 2 - Background
Tutorial 2 - The Queries
JPA Tutorial Project Setup
Tutorial 2 - The First Example
Tutorial 2 - Second Example
Tutorial 2 - One Example Solution

<--Back