Last Updated on May 25, 2019 by Simanta

Often when using Spring for enterprise application development, while developing code on your laptop, you will want to run against a in memory H2 database, and later you will deploy the code to a server and have it run against enterprise database such as Oracle. While Oracle is the #1 RDBMS used in the enterprise, it’s a little heavy weight to use on a laptop. You can run Oracle on a laptop. I’ve done it a number of times. But it’s a lot to maintain and consumes a lot of system resources. It’s far simpler to use H2. Running H2 locally also allows you to focus on development and not playing Oracle DBA.

You may be used to dealing with a legacy code base, in which you dependent on an Oracle database. When you’re using an ORM technology like Hibernate, the persistence layer is fairly well decoupled, which allows you to easily run your code against multiple databases.

In this post, I’m going to show you how to use Spring profiles to configure your application for multiple data sources. This will allow your application to run locally from your laptop using an in memory H2 database, and then against an Oracle database. We’ll use Spring’s dependency injection to inject the datasource into our application at run time. The active profile will control which data source is selected by Spring to configure and use for dependency injection.

JPA Identity Mapping

For a long time relational databases supported a auto-increment data type, which is often used for the primary key. Until Oracle 12c, this feature was not supported. For the identity mapping in your JPA domain classes you need to use a sequence generator. This means a database sequence will be used to get the next primary key value when creating new entities.

In this example, we will be configuring our JPA mapping to use a database sequence for database identity. This means both data sources need to support the use of a database sequence.

In Oracle 12c, auto increment was added as a new feature. This looks like it is supported by Hibernate 5, however, at the time of writing I could not get this feature working. It looked like a potential bug in Hibernate.

Databases

H2

By default, Spring Boot will configure an H2 database for us. H2 is a great database to develop against because it has an Oracle compatibility mode.

When configuring H2 we will want the following features:

• Oracle Compatibility Mode
• Hibernate using the Oracle dialect
• Auto creation of the database on startup via a schema generation script.

Oracle

When developing in the enterprise you’re level of access to Oracle will vary by the organization you are working for. Some companies allow their developers DBA access to development and QA systems. More and more often developers will have restricted access to Oracle in development, QA, and production. Thus, often your database account will not be able to create or modify database tables. Because of this, I’m going to show you how to configure Hibernate to verify the database. To do this, we will need to override the default Spring Boot property.

In contrast to the H2 data source, we will want the following features for Oracle:

• Do not create or update the database. Validate the database against the JPA mappings.

Properties common to the H2 configuration are:

• Use the same Oracle Dialect in Hibernate we are using for the H2 data source.

Spring Profiles

The Spring Framework has an awesome feature introduced in the Spring 3.1 called ‘Profiles’. Through the use of Profiles you can define multiple Spring Beans for the same type, but control which Spring Bean Spring will create for you based on which of the profiles are active. This is a very powerful feature of Spring which allows you to compose your application for different environments.

Profile Support in Spring Boot

Properties Files

Spring Boot will by default scan the properties files “application.properties”. Spring Boot also will by default load properties files for specific active profiles. By following the naming convention of “application”, dash (-), profile name, dot (.) properties for the file name, Spring Boot will automatically load properties files specific to the active profiles.

In our example, we will be setting up a profile for the Oracle data source called “oracle”, and we will use a properties file called “application-oracle.properties” for our Spring Boot configuration propertes. When we set the profile “oracle” active, Spring Boot will automatically load the application-oracle.properties file. The default properties file is also loaded.

Thus when setting up your properties files of Spring Boot, you can place all the common properties in the default properties file (application.properties). And profile specific properties in profile properties files (application-<profile name>.properties).

Schema Customization in Spring Boot

Schema Creation Files

Spring Boot has the ability to run custom SQL scripts on startup. These can be used to create or modify the database, or to load data into the database. For our use case, we want to setup the database in H2. The H2 database is going to be created in memory each time our Spring Boot app starts up, thus we want to run our DDL statements on startup only when the H2 profile is active.

NOTE: Hibernate does have the ability to automatically generate the DDL to create the database. The schema script will run before Hibernate’s schema generation. Thus, if you’re using the create-drop mode, the database objects created in the schema script will get replaced by the Hibernate generated schema. Which effectively overwrites our customizations, and is a behavior we do not want.

Our approach to configure this is very similar setting up the properties files. Spring Boot will execute any SQL script named schema.sql found on the classpath. For our use case, we only want this script to run for the H2 database. In this case, we can use the data source platform setting. This is very similar to Spring Profiles. Actually, I’m not sure why the Spring team decided to break away from Profiles and introduce the data source platform. Key point is to remember this is different from Spring Profiles.

We can configure our SQL script to run for a datasource platform by:

1. name the file schema-<datasource platform>.sql . Example: schema-h2.sql
2. Set the property spring.datasource.platform . Example: spring.datasource.platform=h2

Configuring Spring Boot for H2 and Oracle

Now that I’ve covered some the core concepts of using profiles to control multiple data sources in Spring Boot, I’ll walk you through step by step in setting up the Spring Boot configuration. For this example, I’m going to fork the code from my tutorial series on creating a Web Application with Spring Boot and Thymeleaf.

JPA Configuration

Database Sequence

When you’re using a database sequence with Hibernate for primary key generation, it can cause an extra database query for each insert. One way around this is to have the sequence increment by 100. Thus Hibernate will only need to read the sequence once every 100 inserts. This will significantly improve performance on an application with heavy inserts. Increasing the cache of the database sequence will also have a large impact on insert performance.

NOTE: Hibernate’s schema will generate the database sequence in H2 for us. However, I want to override the default increment and cache settings, hence the need for the schema SQL script.

schema-h2.sql

Here is the SQL script we will ask Spring Boot to run when creating the H2 in memory database. The syntax is the same for Oracle.

DROP TABLE PRODUCT;

CREATE TABLE PRODUCT (
ID NUMBER(10,0) NOT NULL AUTO_INCREMENT,
DESCRIPTION VARCHAR2(255) DEFAULT NULL,
IMAGE_URL VARCHAR2(255) DEFAULT NULL,
PRICE NUMBER(19,2) DEFAULT NULL,
PRODUCT_ID VARCHAR2(255) DEFAULT NULL,
VERSION NUMBER(10, 0) DEFAULT NULL,
PRIMARY KEY (ID));

DROP SEQUENCE PRODUCT_ID_SEQ;

CREATE SEQUENCE PRODUCT_ID_SEQ
  MINVALUE 1
  MAXVALUE 9999999999999999
  START WITH 1
  INCREMENT BY 100
  CACHE 100;

JPA Identity Configuration

From my previous example, we need to update the Product class to use the database sequence for the primary key generation. In the example below, I’m configuring the Product class to use the database sequence PRODUCT_ID_SEQ , and set the allocation size to 100, which corresponds to the INCREMENT BY  value of the database sequence.

Product.class

@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "prooduct_id_seq")
    @SequenceGenerator(name="prooduct_id_seq", sequenceName = "PRODUCT_ID_SEQ", allocationSize = 100)
    private Integer id;

Database Configuration

H2 Spring Boot Configuration

For H2 we need to override four properties.

• We need to tell H2 to start in its Oracle compatibility mode by setting this option in the JDBC url for H2.
• Set the datasource platform to ‘H2’. This enables Spring Boot to run the SQL script schema-h2.sql  on startup.
• Override the default behavior of Hibernate to not create and drop the database. (ddl-auto=none).
• Allow Hibernate to continue on error. In the SQL script we’re asking to drop objects which may or may not be there. We’re setting this to true, to ignore the errors and continue.

Spring Boot will configure the remaining H2 properties using its default values.

application-h2.properties

spring.datasource.url=jdbc:h2:mem:testdb;Mode=Oracle
spring.datasource.platform=h2
spring.jpa.hibernate.ddl-auto=none
spring.datasource.continue-on-error=true

Oracle Spring Boot Configuration

I previously did a blog post on setting up Spring Boot and Oracle here, in which I showed how to setup Oracle as the primary datasource for a Spring Boot application. This time we want the Oracle datasource only to be enabled only when the profile ‘oracle’ is active. We do this by setting the Oracle specific properties in a file called ‘application-oracle.properties’, which Spring Boot will only load when the profile ‘oracle’ is active.

We also want to override the hibernate schema generation property from the default of create-drop, to validate.

application-oracle.properties

spring.jpa.hibernate.ddl-auto=validate

#Basic Spring Boot Config for Oracle
spring.datasource.url=jdbc:oracle:thin:@//springframework.guru.csi0i9rgj9ws.us-east-1.rds.amazonaws.com:1521/ORCL
spring.datasource.username=scott
spring.datasource.password=tiger
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

Running the Spring Boot Application

Unlike my previous examples of the Spring Boot web application, in this example, you will need to set an active profile for the application to work properly. If you do not, Spring Boot will configure the H2 database with the default properties, which will start up. But I expect once you started to add more records to the Product table it would fail with duplicate primary keys because the sequence was created incorrectly.

Hibernate Dialect

In our example, for both H2 and Oracle, we want Hibernate to be using the appropriate Hibernate dialect. We set this in the file application.properties, which Spring Boot will load by default for all profiles.

application.properties

spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect

Running the H2 Database

Here I’m introducing the use of the property “spring.profiles.active”. With this property you can set a list of active profiles for Spring. Spring will accept a comma separated list of strings. To set up our H2 database properly, we need to set the ‘h2’ profile active, as shown below.

application.properties

spring.profiles.active=h2

spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect

Console Output

In the console output on startup we can see our schema script getting executed.

 2015-09-30 12:14:22.200  INFO 5596 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from URL [file:/Users/jt/src/springframework.guru/springbootwebapp/target/classes/schema-h2.sql]
2015-09-30 12:14:22.218  INFO 5596 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from URL [file:/Users/jt/src/springframework.guru/springbootwebapp/target/classes/schema-h2.sql] in 18 ms.

Running the Oracle Database

To have Spring Boot run Oracle, we just need to change the active profile from ‘h2’ to ‘oracle’.

application.properties

spring.profiles.active=oracle

spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect

Console Output

Here in the console output on startup, we can see the schema script is not run, but Hibernate validates the Product table in the Oracle database we connected to.

 2015-09-30 12:16:23.498  INFO 5615 --- [           main] o.h.tool.hbm2ddl.SchemaValidator         : HHH000229: Running schema validator
2015-09-30 12:16:23.498  INFO 5615 --- [           main] o.h.tool.hbm2ddl.SchemaValidator         : HHH000102: Fetching database metadata
2015-09-30 12:16:25.510  INFO 5615 --- [           main] o.hibernate.tool.hbm2ddl.TableMetadata   : HHH000261: Table found: SCOTT.PRODUCT
2015-09-30 12:16:25.510  INFO 5615 --- [           main] o.hibernate.tool.hbm2ddl.TableMetadata   : HHH000037: Columns: [image_url, price, product_id, description, id, version]

Deploying Spring Boot

So far, I’ve been showing you how to change the active profile in the application.properties file with the assumption you will be running Spring Boot from your IDE. But in reality, you’re going to want to deploy your Spring Boot application to a server. The easiest way to do this is to allow Maven (or Gradle) to create an executable JAR file for us. In this case, to set the active profile we can simply pass the following command line argument.

--spring.profiles.active=oracle

In this example, we may have compiled in Maven with the h2 profile active in the application.properties file. By passing the command line argument as shown above, the application.properties value will be overridden with the command line value. This is not a behavior of Spring Boot specifically, but more of how properties work in the Spring Framework in general. It is this type of behavior which allows you to generate a single build artifact, which can be deployed to many different runtime environments. This is a very powerful feature in the Spring Framework, which makes it so well suited for enterprise application development.

Get the Source

The source code for this post is available here on github. The H2 connection will work fine. For the Oracle database, you will need to configure Spring Boot for the properties of your Oracle database.