Lab 10: JPA Queries

In this lab, you will write a program that uses Java Persistence to query entities.

Objectives:

To work with the Java Persistence Query Language (JPQL)

Part 1: Setup

In this part, you will import an Eclipse project that contains entities, session EJBs and a Web application into which you can enter and execute queries.

Steps:

Your first job is to import the completed Eclipse project that define the entities that you will query as well as EJB session beans and a simple Web-based user interface. Follow these steps:
1. From the Eclipse menu, choose File - Import to start the wizard.
2. On the first wizard page, select General - Existing Projects into Workspace, then press Next.
3. On the next page, for the Select Archive file, press the Browse button and navigate to {Lab Installation Directory}/starters/lab10 and select the lab10.zip file. If your computer has the standard lab setup on Windows XP, the path to the file is:
```
C:\jeeclass\starters\lab10\lab10.zip              
               
```
  On Windows Vista or later:
```
C:\Users\username\jeeclass\starters\lab10\lab10.zip              
               
```
  Press Next.
4. On the next wizard page, press Finish.

Examine the Lab10Web project's Java Resources/src folder and note that it defines four entity classes. Examine the domain.Customer, domain.Order, domain.LineItem and domain.Product entities and familiarize yourself with them.

Note that the entities define relationships. Please sketch a UML class diagram in the space below:
```
           
           
           
           
           
           
           
           
           
           
           
           
```

Examine the Java Resources/src/queries package and note that it defines a stateless session EJB named QueryEvaluator.

Please examine it and ensure that you understand it.

Examine the WebContent folder and note that it defines the following JSPs:
- index.jsp defines an input form into which you can enter JPQL queries against the entities.
- results.jsp displays the results of the query that you enter into index.jsp.
Note also that the Web project defines a simple servlet named servlets.ProcessQueriesServlet that runs in response to the input form. The servlet essentially invokes the session EJB's evaluateSingleResultQuery or evaluateMultipleResultQuery and stores the results in the HTTP session so that the results.jsp can display them.

Before you can run the Web application, you need to create the database tables and populate them with sample data. We have provided you with an SQL script for this purpose; follow these steps to run the script:
1. Find the H2 administrative console's Web page. If you can't find it, restart H2 as you did in earlier labs.
2. In Eclipse, use File - Open to open {Lab Installation Directory/starters/lab10/createandpopulate.sql. Examine this database script and see if you can make sense of it.
  
  Press Ctrl+A to highlight the entire file, then press Ctrl+C to copy to the clipboard.
3. In the H2 admin console, click on the entry field, then press Ctrl+V to paste the script.
  
  Press the Run (Ctrl+Enter) button to execute the script. It may take several moments since the script is large. You will know when it's complete when you see the CUSTOMER, LINEITEM, etc. show up in the left-hand list.
4. To ensure that there's good data in the tables, press Clear, then in the entry box , enter SELECT * FROM Customer and press the Run (Ctrl+Enter) toolbar button. You should see 50 rows of Customer information.
5. You can leave the H2 admin console window up so that you can explore the database whenever you like, but for now, go back to Eclipse.

Part 2: Running Queries

In this part, you will use the provided Web application to test some simple queries against the Customer, Order LineItem and Product entities.

Steps:

To get started, expand lab10Web/WebContent and right-click on index.jsp and choose Run As - Run on Server, and press Finish. Eclipse should deploy the application and display a Web page into which you can enter JPQL queries.

The Web page shows a UML diagram of the entities for your convenience, and has the following input widgets:
- A multi-line entry field into which you can type JPQL queries. It's OK to press Enter when typing a query to split it across multiple lines for readability.
- A single-line entry field into which you can type a fully qualified class name. This field is required: you will need to figure out the correct "return" type depending on the query that you enter.
- Two radio buttons that let you choose whether the application displays a single or multiple result entities. Again, you will need to select one of these options depending on your query.

Now let's try a simple query:
1. In the Query multi-line entry field, enter:
```
SELECT c FROM Customer AS c             
             
```
  This query should return all Customer entities.
2. In the Target entity class entry field, enter:
```
domain.Customer             
             
```
  Note that your query returns Customer entities and that domain.Customer is the fully qualified name of the Customer entity class.
3. Select the Multiple Results(List) option. The query should return more than one Customer entity.
4. Press the Submit button -- you should see a table showing all of the Customer entities in the database.

Follow these steps to enter a slightly different query:
1. At the top of the browser pane, press the "left-arrow" icon to go back to the query page.
2. Modify the query, adding text so it looks like:
```
SELECT c FROM Customer AS c
WHERE c.customerID=2                       
             
```
3. The returned class is still Customer, so you don't need to modify the Target Entity entry field, but the new query should return a single entity, so select the Single Result option.
4. Press the Submit button to see the single entity displayed. The name should be Sue Jones.

Repeat the above to first display all of the Product entities, then only the Product entity with productID=2.

Note: Be sure to change the Target Entity entry field to domain.Product!

Write down your querys' text here:
```
// display all products         
         



         
// display product #2         




         
```

Now that you've learned a bit about how to use the Web page, your mission, should you choose to accept it, is to craft queries to answer the following questions. Some of these are a bit tricky, so don't feel bad if you can't figure out how to do all of them.
1. Display all orders that are not yet complete.
  
  Hint: Be sure to set the Target entity class name correctly!
```
             
             
             
```
2. Display all line items that have a quantity greater than 22.
```
             
             
             
```
3. Display the count of products (Hint: target entity class should be java.lang.Long).
```
             
             
             
```
4. Display the average product price (Hint: target entity class should be java.lang.Double).
```
             
             
             
```
5. Display all customers sorted by area code in descending order.
```
             
             
             
```
6. Display all products that are lenses, but are not filters.
```
             
             
             
```
7. Display all products with above average prices.
```
             
             
             
```
8. Display all orders for customer #20 that are not yet complete.
  
  Hint: The field that implements the relationship in Customer to Order is named orders.
```
             
             
             
```