,

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 DatabaseH2

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 12c Database

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 hibernateJPA 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.

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

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

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

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

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

Console Output

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

Running the Oracle Database

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

application.properties

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.

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.

In this example, we may have compiled in Maven with the h2 profile active in the applicaiton.properties file. By passing the command line argument as shown above, the application.properties vaule will be overriden 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.

0
Share

When you start with Spring Boot, it will automatically support H2 if no other data sources have been defined and H2 is found on the classpath. I’ve been using H2 for development for sometime now. It works very well.  All modern relational databases are going to support ANSI SQL. But each is going to have its own nuances and extensions. One thing of the things I like about H2 is its Oracle compatibility mode. It allows H2 to act like a Oracle database. It’s not perfect, but it does do a pretty good job.

The Spring Framework is the most popular Java framework used for building enterprise class applications. Oracle is the most popular database used in the enterprise. So chances are, if you developing Spring Applications, sooner or later, you’re going to be persisting to an Oracle database.

Oracle Database Driver

The Oracle JDBC drivers are not in public Maven repositories due to legal restrictions. This is really rather annoying. Oracle, if you’re reading this – really? Come on, fix this. Please.

So, if you are in a company, chances are you will have a nexus installation with the Oracle JDBC jar installed. But if you are not, you will need to download the JDBC driver from Oracle (after accepting the terms and conditions you probably won’t read). And then you can install it into your local Maven repository manually.

You can install a JAR into your Maven repository using this Maven command. You may need to adjust the version and name depending on the JDBC driver version you download.

Spring Boot Configuration for Oracle

Maven Dependency

You will need to add the Oracle Driver to your Maven (or Gradle) dependencies.

Oracle Datasource

The easiest approach is to create a configuration bean in the package structure of your Spring Boot application. This will create a new Oracle datasource for your Spring Boot application. Once you specify a data source, Spring Boot will no longer create the H2 data source for you automatically.

Free Spring Tutorial
Check out my free Introduction to Spring Course!

Spring Boot Basic Configuration for Oracle

Spring Boot Properties

Configuring a different datasource in Spring Boot is very simple. When you supply datasource properties in Spring Boot’s application.properties file, Spring Boot will use them to configure the datasource. To configure Spring Boot for Oracle, add the following lines to your properties file.

Spring Boot Advanced Configuration for Oracle

Oracle is a highly advanced and highly configurable RDBMS. There is a reason Oracle is the #1 database in the enterprise. The basic example above will work for just about any JDBC data source you need to configure for use with Spring Boot. They will all have a url, user name, password, and driver class. But with Oracle, there are a number of advanced properties you may need to set. Especially if you’re using Oracle RAC.

Spring Boot will set vendor specific properties using spring.datasource.<property name>. And you absolutely can go this route. However, based on my experience, it might be time to switch to a Java based configuration. Spring Boot will create the data source from just the properties file, or will forgo the automatic data source creation if you’re a more traditional method in Spring to define the data source bean.

In this section, I’m going to show you how to use a Spring configuration bean to create the Oracle JDBC datasource.

Oracle Properties

In this example, I’m going to show you how to externalise the Oracle connection properties to a properties file.

In our Spring Boot application.properties file we want to set the following properties.

Next, on our Configuration class for Oracle, we want to add the following annotation:

This tells Spring to look for the property prefix of Oracle when binding properties. Now if our configuration class has a property called ‘whatever’, Spring would try to bind the property value of ‘oracle.whatever’ to the property in the configuration class.

Now if we add the following properties to our configuration class, Spring will use them in the creation of our Oracle data source.

The final Oracle configuration class looks like this:

OracleConfiguration.class

Hibernate Configuration

We will want to tell Hibernate to use the Oracle dialect. We do this by adding the following property to the Spring Boot application.properties file.

Required

Optional

If you’re used to using the H2 database, database tables will automatically be generated by Hibernate. If you want the same behavior in Oracle, you’ll need to set the ddl-auto property of Hibernate to ‘create-drop’. The Spring Boot documentation has additional information about database initialization. To have tables automatically created in Oracle, set the following property in your application.properties file.

Amazon RDS

In testing the code for this post, I spooled up an Oracle instance using Amazon RDS. This makes creating an Oracle database crazy easy. If you want to test this out yourself, I’ve checked in the code on GitHub here. You can check it out, and setup your own Oracle instance on Amazon RDS. Just update the connection properties in application.properities. I branched the code from my tutorial series on building a web application with Spring Boot. Everything will work – EXCEPT create and save. Oracle handles ID generation a little differently and I did not update the JPA mapping for this.

Free Introduction to Spring Tutorial

Are you new to the Spring Framework? Checkout my FREE Introduction to Spring Course!
8
Share