Configure a GlassFish JDBC connection pool to connect to Oracle as follows.

1. Start your lab application on a local GlassFish server. The application won’t do much yet, but this will start the GlassFish server. You could also start the server using start-domain as described in the previous lab.

2. Open localhost:4848 (the GlassFish admin tool), go to Resources→JDBC→JDBC Connection Pools and create a new connection pool with these settings:

   Pool Name: OraclePool
   Resource Type: javax.sql.DataSource
   Database Driver Vendor: Oracle

   and these “Additional Properties”:

   url: jdbc:oracle:thin:@localhost:1521:XE

   Note that you can use this admin tool to control the GlassFish server and to debug applications (see Applications for a list of deployed applications and Monitoring Data for server logs.

3. Set this (new) JDBC resource as the default connection pool by choosing Resources→JDBC→JDBC Resources→jdbc/__default and setting “Pool Name” to OraclePool (rather than DerbyPool).

When your application requests a JDBC connection, GlassFish will now automatically provide this new connection pool. For details on this process, see JDBC Connection Pools in Glassfish.

Save answers to the following questions in a notes.txt file in the root of your project.

1. Compare and contrast the database connection you built in Guide 12 (in Intellij IDEA) and this database connection (in GlassFish). Do we need both? If so, explain why; if not, explain why not.

Configure a JPA persistence unit for your application as follows.

1. Modify the persistence unit in src/META-INF/persistence.xml as follows.

   • persistence-unit tags — Set an appropriate unit name and add JTA transation support as follows.

     <persistence-unit name="cpdbEntities" transaction-type="JTA">

   • properties — Set the persistence unit properties as follows.
     • URL: jdbc:oracle:thin:@localhost:1521:XE
     • Driver: oracle.jdbc.OracleDriver
     • the standard CPDB username and password.

2. In the persistence pane, generate a JPA mapping for the CPDB Person, Household, PersonTeam and Team tables.

   1. Click mouse-right on the cpdbEntities persistence unit and choose Generate Persistence Mapping→By Database Schema.
   2. Set the database source to your CPDB database source.
   3. Create a new package/directory for the entities called models.
   4. Select the Person, Household, PersonTeam and Team tables (only). Open each of the tables in the mapping wizard and ensure that only the basic fields are selected; for now, don’t select any of Intellij’s inferred relationships or any of the foreign keys (e.g., Person.householdId).

   This will create a new entity classes in models/*.java. You may need to re-enter the DB username and password in persistence.xml, so check on that.

You can run the application now, but it will only present a default webpage. Add answers to the following questions to notes.txt.

1. How does Intellij map the database tables into Java entity classes?
2. What is the PK class do?
3. Does Intellij default to field or property annotations?

Add the following application code to the src/ directory.

• Resource — Add a CPDBResource.java class based on this sample code, asking IDEA to import the required libraries (using alt-enter).
• Application — Copy the MyApplication.java code from your lab 11 solution, modifying the resource class to the name of your new CPDB resource class.

Then build a run configuration for the app and verify that it works. The application should implement a RESTful service that responds to the following URLs.

• cpdb/hello — a simple hello-world message
• cpdb/person/x — a JSON-formatted version of the person record with ID x
• cpdb/people — a JSON-formatted list of all the person records.

Verify that the service implements the given URLs and then add answers to the following questions to notes.txt.

1. Does this application perform marshalling? If so, explain what the marshalling does; if not, explain why it’s not needed.
2. Does this code perform any injection? Again, explain…
3. What does the entity manager do in this code, if anything?

Add annotations to your Person entity class to support the following relationships.

• ManyToOne — Add a new field of type Household to the Person entity class, add the accessor methods (getName() & setName()) and annotate its get accessor as follows.

  @ManyToOne
  @JoinColumn(name = "HOUSEHOLDID", referencedColumnName = "ID")

  Run the application again and verify that the appropriate household entity now appears in the marshalled Person entity that is returned.

• ManyToMany — Add a new list of Team entities to the Person entity class, add the accessor methods for this list attribute and annotate its get accessor as follows.

  @ManyToMany
  @JoinTable(name = "PERSONTEAM", schema = "CPDB",
             joinColumns = @JoinColumn(name = "PERSONID", referencedColumnName = "ID", nullable = false),
             inverseJoinColumns = @JoinColumn(name = "TEAMNAME", referencedColumnName = "NAME", nullable = false))

  Run the application again and verify that the appropriate team entities now appear in the marshalled Person entity that is returned.

Add answers to the following questions to notes.txt.

1. How does JPA return the household and team entities related to a given person?

Add the following PUT/POST/DELETE resources for the Person table that respond to the following URLs.

• PUT cpdb/person/x — Modify the given person entity, it it exists, using the values in the JSON-formatted person entity passed with the request.

• POST cpdb/people — Add the person entity passed with the request to the database, allowing the CPDB sequence to assign a new, unique ID number, and linking the person to the existing household with the ID given in the passed person entity. You may assume that the household record with the given ID exists. Note that passing in the ID may require that you add a transient field for the householdId foreign key.

• DELETE cpdb/person/x — Delete the person entity with the given ID, it it exists.

Your RESTful service should now include GET/PUT/POST/DELETE for Person records and their associated Household. No other tables or relationships are required to be supported (e.g., Teams). Though not required for this homework, it would be good review which of these commands are idempotent and which are not.