Olaf Heimburger's Blog

You may have noticed it, this is the missing step from the last post...

Deploying the Database

Once the database schema modeling is finished it needs to be deployed to the database. There are several options and a good database administrator (DBA) could improve the database behaviour considerably. Since this is not a DBA blog we leave this as an exercise to the reader.

Scripts to Create the Database User

In an Oracle Database a database user is the same as the schema user by default. It is also a good habit to create a tablespace for that user. First, we create a script for creating the tablespace:

1. Click on the Offline Database Sources
2. Open the Context Menu
3. Select New ...
4. Expand Database Tier
5. Select Database Files
6. On the right select SQL File
7. Click the OK button
8. In the opening Create SQL File window enter File Name: create_survey_tablespace.sql and make sure that the Directory Name has OnlineSurvey/Database/database (in JDeveloper 10.1.3) or OnlineSurvey/Database/database/SURVEY (in JDeveloper 11g) at the end.
9. Click on the OK button to create an empty file.
10. You will notice that the file appears right below the create_survey_schema.sql file in the Application Navigator.
11. In the tab named create_survey_tablespace.sql enter the following line. You might need to change your file path!
    

    CREATE TABLESPACE survey datafile 'C:\oracle\xe\oradata\XEsurvey.dbf'
    SIZE 500k reuse autoextend ON;

    
    
12. Repeat steps -10 to create a file called create_survey_user.sql. Put the following code in this file:
    

    DROP USER survey;
    CREATE USER survey IDENTIFIED BY survey DEFAULT TABLESPACE survey TEMPORARY
      TABLESPACE temp;
    GRANT connect,resource TO survey;
    

Running the SQL Files ...

All we need to do is to run the SQL files to complete the database deployment.

... In JDeveloper 10.1.3

Before we can do this, we need to check whether JDeveloper is configured to use SQL*Plus to execute the SQL scripts:

1. Open Tools->Preferences
2. Select Database Connections
3. If the text field for SQL*Plus Executable is completed you're done, otherwise we have to point it to the right executable.
4. Click on the Browse button
5. In the new Open file dialog navigate to the $ORACLE_HOME directory for XE. In the $ORACLE_HOME/bin directory you'll find an executable named sqlplus.exe or sqlplus.
6. Select sqlplus.exe and click on Open
7. Once this is done, select the create_survey_tablespace.sql and from the Context Menu follow the Run in SQL to select the sysXE entry. This will open a terminal window for SQL*Plus. For security reasons the password of the sys database user has to be entered. When this is done the script will be run and creates the tablespace.
8. Now do the same with the create_survey_user.sql.
   

To use this new user for later connections we create another Database Connection called surveyXE in the Connection Navigator (see ADF in Action: Setting up the Gear, Verifying the Database Connection for more details on this). With this connection we run the create_survey_schema.sql to create the full schema.

... In JDeveloper 11g

In 11g things are slightly different. We have Global Connections in the Resource Palette and Application-specific Connections in the Application Resources accordion.

To reuse the global database connection, we expand the sysXE database connection from the Resource Palette and drag it to the Application Resources Connections entry. (This is really cool!) It creates a Connections->Database->sysXE structure for the application also.

The rest is similar to JDeveloper 10.1.3. Just try it.

You should now have the Survey schema in your database. If not post a comment...

Online Survey Application, Database Project, Iteration 1

It is time to verify you are on the right track. Your database project should like mine. Here are the screenshots for both JDeveloper versions.

Database Project In JDeveloper 10.1.3

Database Project In JDeveloper 11g

Application Layers

Modern computer applications are composed of several layers to make the different application tasks better manageable. The most prominent and widely used ... is the Model-View-Controller (MVC) Pattern. This pattern was introduced by the Smalltalk language. The MVC is one of the best understood and widely used implementation strategies. The same applies to most ADF applications and we will use it in this series as well.

The Model Layer

After having completed the database model, it is time to have some thoughts about the model layer. The model layer acts as the data holding layer. It arranges the data objects with their attributes and relationships. Methods for adding, deleting and modifying these objects are supplied. Very often this layer also supplies methods for retrieving and persisting the data.

Model Layer Choices

Oracle ADF supports many ways to implement the model layer. We will have a look at these three possible choices:

• EJB 3.0 / Java Persistence API (JPA)
• TopLink
• ADF Business Components

Each of these choices deserves a separate coverage as they tend to be different in many ways.

General Considerations

Before we go into the details we have to consider some things:

• Plural vs Singular Naming - Database designers use conventions different from Java designers. Most visible is the naming convention. While database designers use plural names, Java designers use singular names. The Model Layer should take care of that.
• Using Existing Sequence Generators - To make the database model as useful as possible Oracle database designers often use Sequences for generating primary keys. This is particularly useful if more than one application accesses the database. The model layer should be able to use the existing Sequences.

Enterprise Java Beans 3.0

Although named after the ill-fated Enterprise Java Beans standards up to 2.1, the latest version specifies many state-of-the-art technologies and leverages lessons learned from technologies like dependency injection, Hibernate's and TopLink's Entity and Session Management. EJB 3 implements Configuration by Exception, where default values apply as long as these are deduceable from provide information. This results in an easier to develop habit which very often uses a single file per bean including every definition needed.

EJB 3 Entities

To mark the change to earlier versions, EJB 3 removed the Bean from the term Entity Bean and uses Entity. Entities are not bound to the container anymore and can move between the different layers (Model to View to Model and so on) without a specific impact. As a result some design patterns of the EJB 2.x world are not fully applicable (eg. Data Transfer Objects) while others are a possible candidates for an update (eg. Data Access Object). EJB 3 Entities implement the Java Persistence API.

Java Persistence API

The authors of the Java Persistence API (JPA) have learned a lot from ORM frameworks like TopLink or Hibernate and other techniques. The JPA is much more complete then the previous EJB definitions in terms of attribute mapping, inheritance and the query language (JPA Query Language (JQL)).

Implementing the EJB 3 Model Layer

A very easy way to implement the EJB 3 Model Layer is by using the existing Database Model as developed in the previous posts (see Setting up the Gear, Creating the Database, and Deploying the Database Model). Additionally you should make sure that your database is up and running and a Database Connection called survey configured. The steps in JDeveloper 10.1.3 and 11g are quite similar.

1. Create a Model Layer Project
2. Import the EJB 3 from Database Tables
3. Check the generated files
4. Create a Session Facade
5. Create a Test Client

Steps for JDeveloper 10.1.3

Step 1: Create a Model Layer Project

To create a new Model Layer Project select OnlineSurvey in the Application Navigator, open the Context Menu and chose the New Project... menu entry. In the New Gallery select an Empty Project and click on OK. In the Create Project window set the Project Name to ModelEJB30 and click on the OK button. Select the newly created ModelEJB30 project in the Application Navigator and open the Project Properties window with a double-click. In the Project Properties window, check the values of Project Content and make sure that the Default Package is set to demo.survey.model.ejb30. Click OK to close this window.

Step 2: Import the EJB 3 from Datbase Tables

Select the ModelEJB30 project in the Application Navigator. Open the New Gallery from the Context Menu -> New .... In the New Gallery expand the Business Tier and select EJB. On the right-hand side select Entities From Tables (JPA/EJB 3.0) and click on the OK button.

• In the Create Entities From Tables wizard step 1 select the survey connection. Click on Next to get the database connection.
• In step 2 click on the Query button to get all available tables. Since we only have tables the check box for Tables is sufficient. Once the table names appear, select all of them.
• Move all entries from Available to Selected, by clicking on the >> button. Note that all names have changed to typical Java type names but in plural (eg. Answers (SURVEY.ANSWERS)). The values in parentheses tell you about the table relationship. Select every entry and see how the text field under the Selected list changes its value. Here we can change the names from plural to singular names. Change the names accordingly. You should have these names: Answer, Author, Item, Question, Response, Survey
• Click Next to go to wizard step 3. In step 3 of the wizard accept the default values and click Next to go to wizard step 4.
• This step can be used for changing the names as well, but is less user-friendly. Click Next to go to the Summary and click on Finish to close the wizard. Now all six entities will be created.

You can find them in the Application Navigator under ModelEJB30->Application Sources->demo.survey.model.ejb30.

Step 3: Check the generated files

To check the generated files open them by selecting Answer.java, hold the Shift key, select Survey.java, open the Context Menu and select Go To Bean Class. Every selected file will be opened in a Code Editor. Select the tab named Answer.java to bring it to the front. Change the code to get the lines below:

@Entity
@NamedQuery(name = "Answer.findAll", query = "select o from Answer o")
@SequenceGenerator(name = "SEQUENCE_ANSWERS", sequenceName = "SEQ_ANSWERS", allocationSize = 1)
@Table(name = "ANSWERS")
public class Answer implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQUENCE_ANSWERS")
    @Column(nullable = false)
    private Long id;

While adding this code, JDeveloper will suggest possible values and actions to be taken. Only import the types javax.persistence.SequenceGenerator, javax.persistence.GeneratedValue and javax.persistence.GenerationType. You should explore how the Ctrl-Blank short cut helps you to provide the correct annotation parameters.
Do the same for the other files Author.java, Item.java, Question.java, Response.java, Survey.java but use SEQ_AUTHORS with SEQUENCE_AUTHORS, SEQ_ITEMS with SEQUENCE_ITEMS, SEQ_QUESTIONS with SEQUENCE_QUESTIONS, SEQ_RESPONSES with SEQUENCE_RESPONSES, and SEQ_SURVEYS with SEQUENCE_SURVEYS.

Step 4: Create a Session Facade

Click on ModelEJB30 in the Application Navigator. Any other entry of the ModelEJB30 project will do, also. Select New... from the Context Menu and navigate to Business Tier -> EJB in the New Gallery. On the right-hand side select Session Bean (EJB 1.1/2.x/3.0) and click on the OK button to open the 4 step Create Session Bean wizard.

• In wizard step 1 change the EJB Name to SurveySession, accept the pre-selected defaults and click on Next
• In wizard step 2 make sure that all methods are checked (expand the tree to see which methods are available). Click Next to go to the next wizard step.
• Step 3 shows the default values for the bean class and step 4 - Click Next to go from step 3 to step 4 - shows the implemented interfaces. Remote and Local interfaces are good enough for our scenario.
• Clicking Next in step 4 directs us to the wizard Summary window which we close by clicking on the Finish to go to the next wizard step.
  

A SurveySessionBean will be added to the project. Double-click on it and you'll see the generated Session Facade in the Code Editor.

Step 5: Create a Test Client

To finish the JDeveloper 10.1.3 tasks the creation of a test client needs to be done. Click on the SurveySessionBean in the Application Navigator and open the Context Menu. Find the New Sample Java Client entry and click on it. Accept all default values and click on the OK button to generate the test client. Open it and change the main method as follows:

    public static void main(String[] args) {
        try {
            final Context context = getInitialContext();
            SurveySession surveySession = 
                (SurveySession)context.lookup("SurveySession");
            Author author = new Author();
            author.setName("Olaf Heimburger");
            surveySession.persistEntity(author);
            for (Author author : 
                 (List<Author>)surveySession.queryAuthorFindAll()) {
                System.out.println("id = " + author.getId());
                System.out.println("name = " + author.getName());
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

To test the Session Facade and the Test Client do the following:

1. Select the SurveySessionBean, open the Context Menu and select Run. Wait until you see Oracle Containers for J2EE 10g (10.1.3.3.0)  initialized.
2. Select the SurveySessionClient, open the Context Menu and select Run. You should get some output.
   

Steps for JDeveloper 11g

Step 1: Create a Model Layer Project

To create a new Model Layer Project select OnlineSurvey in the Application Navigator, click on the Applications Menu on the right-hand side select the New Project... menu entry. In the New Gallery select an Empty Project and click on OK. In the Create Project window set the Project Name to ModelEJB30 and click on the OK button. Select the newly created ModelEJB30 project in the Application Navigator and open the Project Properties window with a double-click. In the Project Source Paths window, check the values of Project Source Paths and make sure that the Default Package is set to demo.survey.model.ejb30. Click OK to close this window.

Step 2: Import the EJB 3 from Datbase Tables

Select the ModelEJB30 project in the Application Navigator. Open the New Gallery from the Context Menu -> New .... In the New Gallery expand the Business Tier and select EJB. On the right-hand side select Entities From Tables and click on the OK button.

• In the Create Entities From Tables wizard step 1 select EJB 3.0 - JPA Entities. Click on Next to select the Persistence Unit. Since we haven't created one yet, click on the New ... button. In the New Persistence Unit window set the Name to survey and the JTA Datasource Name to jdbc/surveyDS. Click OK to close the window. Click Next to go to the next step.
• In step 3 make sure that the Online Database Connection radio button is checked. Click Next to go to the next step.
• Step 4 lets you define the necessary details for the Database connection and the Offline Database. The Connection should be survey. If this is not available click on the green plus button to create a connection with survey as the Connection Name. For the Offline Database click on the New button and in the Create Offline Database window set the Name and Default Schema to SURVEY. From the Database to emulateOracle10g Express Edition Release 2. Click OK to close this window. Note that this is not really convenient as we did this in the Database project already. So far I didn't found a way to reuse the definitions from the Database project and hope for a dependency-like implementation in the future.
• Click Next to go to step 5. Click on the Query button to get all available tables. Once the table names appear, select all of them. Move all entries from Available to Selected, by clicking on the >> button.
• Click Next to go to wizard step 6. In this step accept the default values and click Next to go to wizard step 7. In step 7 we have to change the names from plural to singular names. From the drop-down list select one entry after another and remove the last letter s from the Entity Name.
  Do this carefully and slowly. In 11g TP 3 chances are that you do it to quick or go to far and you are not able to change anything afterwards. Only solution is start again with the whole wizard. I speak from experience.
• Click on Next to go to the Summary step. Click on Finish to create all six entities.

You can find them in the Application Navigator under ModelEJB30->Application Sources->demo.survey.model.ejb30.
drop-down select

Step 3: Check the generated files

To check the generated files open them by selecting Answer.java, hold the Shift key, select Survey.java, open the Context Menu and select Go To Bean Class. Every selected file will be opened in the Code Editor. Select the tab named Answer.java to bring it to the front. Change the code to get the lines below:

@Entity
@NamedQuery(name = "Answer.findAll", query = "select o from Answer o")
@SequenceGenerator(name = "SEQUENCE_ANSWERS", sequenceName = "SEQ_ANSWERS", allocationSize = 1)
@Table(name = "ANSWERS")
public class Answer implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQUENCE_ANSWERS")
    @Column(nullable = false)
    private Long id;

While adding this code, JDeveloper will suggest possible values and actions to be taken. Only import the types javax.persistence.GenerationType. A big improvement is that the EJB 3 Annotations are already known by JDeveloper and do not require any additional action. You should explore how the Ctrl-Blank short cut helps you to provide the correct annotation parameters.
Do the same for the other files Author.java, Item.java, Question.java, Response.java, Survey.java but use SEQ_AUTHORS with SEQUENCE_AUTHORS, SEQ_ITEMS with SEQUENCE_ITEMS, SEQ_QUESTIONS with SEQUENCE_QUESTIONS, SEQ_RESPONSES with SEQUENCE_RESPONSES, and SEQ_SURVEYS with SEQUENCE_SURVEYS.

Step 4: Create a Session Facade

Click on ModelEJB30 in the Application Navigator. Any other entry of the ModelEJB30 project will do, also. Select New... from the Context Menu and navigate to Business Tier -> EJB in the New Gallery. On the right-hand side select Session Bean and click on the OK button to open a 6 step Create Session Bean wizard.

• Since the EJB version is already known the wizard starts a step 2, by using the Back button you can go to step 1 but this is not needed.
• In step 2 change the EJB Name to SurveySession, accept the pre-selected defaults and click on Next to go to the next wizard step.
• In step 3 make sure that all methods are checked (expand the tree to see which methods are available). Click Next to go to the next wizard step.
• Step 4 shows the default values for the bean class and Step 5 - Click Next to go from step 4 to step 5 - shows the implemented interfaces. Remote and Local interfaces are good enough for our scenario. Clicking Next in Step 5 directs us to the wizard Summary window which we close by clicking on the Finish button.

A SurveySessionBean will be added to the project. Double-click on it and you'll see the generated Session Facade in the Code Editor.

Step 5: Create a Test Client

To finish the JDeveloper 11g tasks, the creation of a test client needs to be done. Click on the SurveySessionBean in the Application Navigator and open the Context Menu. Find the New Sample Java Client entry and click on it. Accept all default values and click on the OK button to generate the test client. Open it and change the main method as follows:

    public static void main(String[] args) {
        try {
            final Context context = getInitialContext();
            SurveySession surveySession = 
                (SurveySession)context.lookup("SurveySession");
            Author author = new Author();
            author.setName("Olaf Heimburger");
            surveySession.persistEntity(author);
            for (Author author : 
                 (List<Author>)surveySession.queryAuthorFindAll()) {
                System.out.println("id = " + author.getId());
                System.out.println("name = " + author.getName());
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

To test the Session Facade and the Test Client do the following:

1. Select the SurveySessionBean, open the Context Menu and select Run. Wait until you see Embedded OC4J Server startup time: 83594 ms.
2. Select the SurveySessionClient, open the Context Menu and select Run.

What Have We Done So Far?

First of all we have created a number of Entities for the Tables that we created in the Database project. Each Entity has it's own Sequence Generator assigned, using the @GeneratedValue annotation. Since we used the default SEQUENCE statement for the database, ie without an explicit INCREMENT option, we had to adjust the default allocationSize for JPA layer from 50 to 1 in the @SequenceGenerator annotation. Finally we created a Session Facade for persisting, retrieving and updating our Entities conveniently. The very last step was to test the Facade with the Author class.

References

• EJB 3 in Action, Debu Panda, Reza Rahman, Derek Lane, Manning, ISBN 978-1-933988-34-4
• Java Persistence with Hibernate, Christian Bauer, Gavin King, Manning, ISBN 1-932394-88-5