Skip to content

10. Oracle Express 11g Release 2

We will now discuss porting what was done with MySQL 5 to Oracle Express 11g Release 2.

10.1. Setting up the development environment

10.1.1. Eclipse environment

We will be working with the following Eclipse environment:

  

The Oracle projects listed above can be found in the folder [<examples>/spring-database-config\oracle\eclipse].

Note: Press [Alt-F5] to regenerate all Maven projects.

Launch Oracle Express and its client [OraManager] (see section 23.6). We will generate:

  • the [dbproduits] database using the [generic-create-dbproduits] project;
  • the [dbproduitscategories] database with the [generic-create-dbproduitscategories] project;

10.1.2. Creating Users

Everything now takes place in [OraManager]. The Oracle DBMS must be running. We use the system / system credentials for the system administrator (this must have been configured beforehand). We will create two users:

  • [DBPRODUITS / dbproduits], who will be the owner of the [dbproduits] database;
  • [DBPRODUCTSCATEGORIES / dbproducts-categories], who will be the owner of the [dbproducts-categories] database;
  • in [6-7], the credentials are [system / system];
  • in [14]: enter dbproduits;
  • in [16], the user has been created but does not have sufficient permissions to log in. We will grant them using an SQL script [17-18];
  • in [19], enter the username in uppercase;

We do the same thing to create the user [DBPRODUITSCATEGORIES / dbproduitscategories]:

  

10.1.3. Installing the Oracle JDBC driver in the Maven repository

The Oracle JDBC driver is not available in the central Maven repositories. It must be downloaded from Oracle [http://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html]:

Once downloaded, it must be installed in the local Maven repository. This is done using the following DOS script [install.bat] [2]:

"%M2_HOME%\bin\mvn.bat" install:install-file -Dfile=ojdbc6.jar -Dpackaging=jar -DgroupId=com.oracle.jdbc -DartifactId=ojdbc6 -Dversion=1.0

where [%M2_HOME%] should be replaced with the path to the Maven installation directory (see section 23.2). Once this is done, the JDBC driver can then be imported into Maven projects using the following configuration:


        <dependency>
            <groupId>com.oracle.jdbc</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>1.0</version>
</dependency>

10.1.4. Creating the [dbproduits] database

Now that we have a user [DBPRODUITS / dbproduits], we will connect to Oracle using these credentials:

  • in [6], enter dbproduits;
  • in [9], the [DBPRODUITS] database we will be using;

In the [ConfigJdbc] class of the [oracle-config-jdbc] project, the connection parameters used are as follows:


    public final static String DRIVER_CLASSNAME = "oracle.jdbc.OracleDriver";
    public final static String URL_DBPRODUITS = "jdbc:oracle:thin:@localhost:1521:xe";
    public final static String USER_DBPRODUITS = "DBPRODUITS";
    public final static String PASSWD_DBPRODUITS = "dbproduits";
    public final static String URL_DBPRODUCTCATEGORIES = "jdbc:oracle:thin:@localhost:1521:xe";
    public final static String USER_PRODUCT_CATEGORIES_DB = "PRODUCT_CATEGORIES_DB";
public final static String PASSWD_DBPRODUITSCATEGORIES = "dbproduitscategories";

You must adapt these to your Oracle configuration.

In the [oracle-config-jpa-eclipselink] project, the JPA entity is defined as follows:

  

package generic.jpa.entities.dbproduits;

import generic.jdbc.config.ConfigJdbc;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

@Entity(name="Product1")
@Table(name = ConfigJdbc.TAB_PRODUCTS)
public class Product {

    // fields
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="genSeqProducts")
  @SequenceGenerator(name="genSeqProducts", sequenceName="PRODUCTS_SEQUENCE", allocationSize=5)
    @Column(name = ConfigJdbc.PRODUCTS_ID_TABLE)
    private Long id;
    @Column(name = ConfigJdbc.PRODUCTS_NAME, unique = true, length = 30, nullable = false)
    private String name;
    @Column(name = ConfigJdbc.PRODUCTS_CATEGORY, nullable = false)
    private int category;
    @Column(name = ConfigJdbc.TAB_PRODUITS_PRIX, nullable = false)
    private double price;
    @Column(name = ConfigJdbc.TAB_PRODUITS_DESCRIPTION, length = 100, nullable = false)
    private String description;

...
}
  • lines 18-19: the strategy for generating the primary key of the [PRODUITS] table is [strategy=GenerationType.SEQUENCE]. For MySQL, we used the strategy [ ] [@GeneratedValue(strategy = GenerationType.IDENTITY)]. With Oracle Express 11g, this strategy cannot be used;
  • line 18: we specify that the primary key will be generated using a number generator, often referred to as a sequence;
  • line 19: the sequence generator (the name attribute references the generator from line 18) will create a sequence named [PRODUITS_SEQUENCE] in the [dbproduits] database. Because we want portability between JPA implementations, it is important to name the sequence. Otherwise, without line 19, the three JPA implementations will create sequences with different names, making it impossible for JPA2 to use a database created by JPA1;

We are ready to run the [generic-create-dbproduits-eclipselink] configuration:

Running this configuration creates two objects:

  • a table [PRODUCTS];
  • a sequence named [PRODUITS_SEQUENCE]

The DDL for the [PRODUCTS] table is as follows:

 

The primary key [ID] is not auto-incremented as it was with MySQL. However, the [spring-jdbc-03] project assumes that the DBMS handles the generation of primary keys for the [PRODUCTS] table. We will create a trigger. A trigger is a stored procedure within the DBMS that executes under certain conditions. We will create a trigger that, with each new insertion, generates the primary key of the inserted product based on the [PRODUCTS_SEQUENCE] sequence created by the JPA configuration.

  • In [6], the trigger [PRODUCTS_ID_TRIGGER] [4] will be executed before each insertion;
  • in [7], a stored procedure specific to the Oracle DBMS. It specifies that the [ID] field of the row to be inserted must be initialized with the next value from the generator named [PRODUITS_SEQUENCE];
 

The [dbproduits] database is now ready. Execute the following configurations:

  • [spring-jdbc-generic-01.IntroJdbc01];
  • [spring-jdbc-generic-01.IntroJdbc02];
  • [spring-jdbc-generic-03.JUnitTestDao1];
  • [spring-jdbc-generic-03.JUnitTestDao2];

They all have to succeed.

10.1.5. Generating the [dbproduitscategories] database

  

We will now run the [generic-create-dbproduitscategories] project, which will generate the [dbproduitscategories] database. Before doing so, in [OraManager], we connect using the credentials [DBPRODUITSCATEGORIES / dbproduitscategories] so that we can observe the changes made to the [dbproduitscategories] database:

  

The JPA entities used have the following primary key generation strategies:

[Category]


public class Category implements AbstractCoreEntity {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE,generator="genSeqCategories")
  @SequenceGenerator(name="genSeqCategories", sequenceName="CATEGORIES_SEQUENCE", allocationSize=5)
    @Column(name = ConfigJdbc.TAB_JPA_ID)
    protected Long id;

[Product]


public class Product implements AbstractCoreEntity {
    // properties
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="genSeqProducts2")
  @SequenceGenerator(name="genSeqProducts2", sequenceName="PRODUCTS_SEQUENCE", allocationSize=5)
    @Column(name = ConfigJdbc.TAB_JPA_ID)
    protected Long id;

[Role]


public class Role implements AbstractCoreEntity {
    // properties
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="genSeqRoles")
  @SequenceGenerator(name="genSeqRoles", sequenceName="ROLES_SEQUENCE", allocationSize=5)
    @Column(name = ConfigJdbc.TAB_JPA_ID)
    protected Long id;

[User]


public class User implements AbstractCoreEntity {
    // properties
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="genSeqUsers")
  @SequenceGenerator(name="genSeqUsers", sequenceName="USERS_SEQUENCE", allocationSize=5)
    @Column(name = ConfigJdbc.TAB_JPA_ID)
    protected Long id;

[UserRole]


public class UserRole implements AbstractCoreEntity {
    // properties
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="genSeqUsersRoles")
  @SequenceGenerator(name="genSeqUsersRoles", sequenceName="USERS_ROLES_SEQUENCE", allocationSize=5)
    @Column(name = ConfigJdbc.TAB_JPA_ID)
    protected Long id;

As was done for the [dbproduits] database, five sequences will be generated. These are used by JPA implementations to generate primary keys. JPA implementations do not use triggers as we did previously, but instead query the sequences to obtain the next primary key. We will also generate the primary keys using triggers. These are required for the [spring-jdbc-04] project.

We run the [generic-create-dbproduitscategories-eclipselink] configuration:

and we get the following result:

  

We then generate five triggers to generate the primary keys for the five tables:

 

The triggers are associated with the tables as follows:

Table

Trigger

Sequence
CATEGORIES
CATEGORIES_ID_TRIGGER
CATEGORIES_SEQUENCE
PRODUCTS
PRODUCTS_ID_TRIGGER
PRODUCTS_SEQUENCE
ROLES
ROLES_ID_TRIGGER
ROLES_SEQUENCE
USERS
USERS_ID_TRIGGER
USERS_SEQUENCE
USERS_ROLES
USERS_ROLES_ID_TRIGGER
USERS_ROLES_SEQUENCE
  

The [spring-jdbc-04] project requires that the [VERSIONING] column have a default value in each of the tables:

We do this for all five tables.

Now, run the configurations:

  • [spring-jdbc-generic-04.JUnitTestDao];
  • [spring-jpa-generic-JUnitTestDao-hibernate-eclipselink];

Both should pass.

10.2. Configuring the JDBC layer

 

The [oracle-config-jdbc] project configures the [JDBC] layer of the following test architecture:

The project is analogous to the [mysql-config-jdbc] configuration project for the JDBC layer of the MySQL DBMS (see Section 3.3). We present only the changes:

The [pom.xml] file imports the Oracle JDBC driver:


<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>dvp.spring.database</groupId>
    <artifactId>generic-config-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>generic jdbc configuration</name>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.2.3.RELEASE</version>
    </parent>

    <dependencies>
        <!-- variable dependencies ********************************************** -->
        <!-- JDBC driver for the DBMS -->
        <dependency>
            <groupId>com.oracle.jdbc</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>1.0</version>
        </dependency>
        <!-- static dependencies ********************************************** -->
        ....
    </dependencies>
...
</project>
  • lines 18-22: the Oracle JDBC driver replaces the MySQL driver;

The second change is in the [ConfigJdbc] class, which defines the database credentials:


    // connection parameters
    public final static String DRIVER_CLASSNAME = "oracle.jdbc.OracleDriver";
    public final static String URL_DBPRODUITS = "jdbc:oracle:thin:@localhost:1521:xe";
    public final static String USER_DBPRODUITS = "DBPRODUITS";
    public final static String PASSWD_DBPRODUITS = "dbproduits";
    public final static String PRODUCT_CATEGORIES_DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
    public final static String USER_PRODUCT_CATEGORIES_DB = "PRODUCT_CATEGORIES_DB";
public final static String PASSWD_PRODUCT_CATEGORIES_DB = "dbproducts-categories";

The third change is to the maximum number of parameters that a can support:


    // maximum number of parameters for a [PreparedStatement]
    public final static int MAX_PREPAREDSTATEMENT_PARAMETERS = 1000;

The [JUnitTestPushTheLimits] test generates SQL statements for 5,000 products, which will generate [PreparedStatement] objects with 5,000 parameters. MySQL supported this value, but Oracle did not. We lowered this value to 1,000, and it works.

 

Note: Press [Alt-F5] to regenerate all Maven projects.

The [oracle-config-jpa-eclipseLink] project configures the [JPA] layer of the test architecture:

The project is similar to the [mysql-config-jpa-eclipselink] configuration project (see Section 7.3) for the EclipseLink JPA layer of the MySQL DBMS. We present only the changes:

The first is in the [ConfigJpa] class in the definition of the [jpaVendorAdapter] bean:


    // the JPA provider
    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
        // Note: the JPA entities and the EclipseLink configuration are in the META-INF/persistence.xml file
        EclipseLinkJpaVendorAdapter eclipseLinkJpaVendorAdapter = new EclipseLinkJpaVendorAdapter();
        eclipseLinkJpaVendorAdapter.setShowSql(false);
        eclipseLinkJpaVendorAdapter.setDatabase(Database.ORACLE);
        eclipseLinkJpaVendorAdapter.setGenerateDdl(true);
        return eclipseLinkJpaVendorAdapter;
}
  • Line 7: We tell the JPA implementation that it will be working with an Oracle database. The JPA implementation will then adopt Oracle's proprietary data types and SQL.

The second change concerns the primary key generation strategy. The new strategy was presented in Section 10.1.

10.4. Configuring the Hibernate JPA Layer

 

Note: Press [Alt-F5] to regenerate all Maven projects.

The [oracle-config-jpa-hibernate] project is analogous to the [mysql-config-jpa-hibernate] project (Section 6.3) with the same modifications that guided the porting of [mysql-config-jpa-eclipselink] to the [oracle-config-jpa-eclipselink] project (Section 10.3).

With these modifications in place, the execution of the [spring-jpa-generic-JUnitTestDao-hibernate-eclipselink] configuration should succeed.

10.5. Configuring the OpenJpa JPA layer

 

Note: Press [Alt-F5] to regenerate all Maven projects.

The [oracle-config-jpa-openjpa] project is similar to the [mysql-config-jpa-openjpa] project (section 8.3) with the same modifications that were used to port the [mysql-config-jpa-eclipselink] project to the [oracle-config-jpa-eclipselink] project (Section 10.3).

With these modifications in place, the execution of the [spring-jpa-generic-JUnitTestDao-openjpa] configuration should succeed.

https://tahe.developpez.com
This tutorial written by Serge Tahé is made available to the public under the terms of the Creative Commons Attribution – Non-Commercial – ShareAlike 3.0 Unported.