,

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

Out of the box, Spring Boot is very easy to use with the H2 Database. If the H2 database is found on your classpath, Spring Boot will automatically set up an in memory H2 database for your use. But what if you want to use MySQL? Naturally, Spring Boot has support for MySQL and a number of other popular relational databases.

Previously, I wrote about creating a web application using Spring Boot.  Let’s say we want to deploy this application to production and we’ve decided to use MySQL for the database. Changing Spring Boot from H2 to MySQL is easy to do.

MySQL Configuration

For this example, I’m using MySQL installed locally on my MacBook Pro. You’ll need to have a database defined for your use. For this example, I want to create a database for my use. Using the command prompt, you can log into MySQL with this command:

Use the following command to create a database.

You only need to use these commands if you want to use a new database. MySQL is a very robust database. The full capabilities of MySQL are beyond the scope of this tutorial.

MySQL Dependencies

First we need to add the MySQL database drivers to our project. You will need to add the following dependency to your Maven POM file.

POM.xml

Spring Boot Properties

We need to override the H2 database properties being set by default in Spring Boot. The nice part is, Spring Boot sets default database properties only when you don’t. So, when we configure MySQL for use. Spring Boot wont setup the H2 database anymore.

The following properties are need to configure MySQL with Spring Boot. You can see these are pretty standard Java data source properties. Since in my example project, I’m using JPA too, we need to configure Hibernate for MySQL too.

NOTE: If this was actually a production database, you not tell Hibernate to use the create-drop option. This tells Hibernate to recreate the database on startup. Definitely not the behavior we want. You can set this property to the following values: none, validate, update, create-drop. If this was actually a production database, you probably would want to use validate.

Running Spring Boot with MySQL

This is all that needs to be changed to use MySQL with Spring Boot. When you start the project now, MySQL will be used by the Spring Boot application for the database.

Free Introduction to Spring Tutorial

Are you new to the Spring Framework? Checkout my Free Introduction to Spring Online Tutorial.

 

Get The Source

The source code for this post is available on GitHub here. You can download the source and build the project using Maven.

1
Share
,

H2 Database Console

Frequently when developing Spring based applications, you will use the H2 in memory database during your development process. Its light, fast, and easy to use. It generally does a great job of emulating other RDBMs which you see more frequently for production use (ie, Oracle, MySQL, Postgres). When developing Spring Applications, its common to use JPA/Hibernate and leverage Hibernate’s schema generation capabilities. With H2, your database is created by Hibernate every time you start the application. Thus, the database is brought up in a known and consistent state. It also allows you to develop and test your JPA mappings.

H2 ships with a web based database console, which you can use while your application is under development. It is a convenient way to view the tables created by Hibernate and run queries against the in memory database.  Here is an example of the H2 database console.

h2 database console

Configuring Spring Boot for the H2 Database Console

H2 Maven Dependency

Spring Boot has great built in support for the H2 database. If you’ve included H2 as an option using the Spring Initializr, the H2 dependency is added to your Maven POM as follows:

This setup works great for running our Spring Boot application with the H2 database out of the box, but if want to enable the use of the H2 database console, we’ll need to change the scope of the Maven from runtime, to compile. This is needed to support the changes we need to make to the Spring Boot configuration. Just remove the scope statement and Maven will change to the default of compile.

The H2 database dependency in your Maven POM should be as follows:

Spring Configuration

Normally, you’d configure the H2 database in the web.xml file as a servlet, but Spring Boot is going to use an embedded instance of Tomcat, so we don’t have access to the web.xml file. Spring Boot does provide us a mechanism to use for declaring servlets via a Spring Boot ServletRegistrationBean.

The following Spring Configuration declares the servlet wrapper for the H2 database console and maps it to the path of /console.

WebConfiguration.java

Note – Be sure to import the proper WebServlet class (from H2).

If you are not using Spring Security with the H2 database console, this is all you need to do. When you run your Spring Boot application, you’ll now be able to access the H2 database console at http://localhost:8080/console.

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

Spring Security Configuration

If you’ve enabled Spring Security in your Spring Boot application, you will not be able to access the H2 database console. With its default settings under Spring Boot, Spring Security will block access to H2 database console.

To enable access to the H2 database console under Spring Security you need to change three things:

  • Allow all access to the url path /console/*.
  • Disable CRSF (Cross-Site Request Forgery). By default, Spring Security will protect against CRSF attacks.
  • Since the H2 database console runs inside a frame, you need to enable this in in Spring Security.

The following Spring Security Configuration will:

  • Allow all requests to the root url (“/”) (Line 12)
  • Allow all requests to the H2 database console url (“/console/*”) (Line 13)
  • Disable CSRF protection (Line 15)
  • Disable X-Frame-Options in Spring Security (Line 16)

CAUTION: This is not a Spring Security Configuration that you would want to use for a production website. These settings are only to support development of a Spring Boot web application and enable access to the H2 database console. I cannot think of an example where you’d actually want the H2 database console exposed on a production database.

SecurityConfiguration.java

Using the H2 Database Console

Simply start your Spring Boot web application and navigate to the url http://localhost:8080/console and you will see the following logon screen for the H2 database console.

h2 database console logon screen

Spring Boot Default H2 Database Settings

Before you login, be sure you have the proper H2 database settings. I had a hard time finding the default values used by Spring Boot, and had to use Hibernate logging to find out what the JDBC Url was being used by Spring Boot.

Value Setting
Driver Class org.h2.Driver
JDBC URL jdbc:h2:mem:testdb
User Name sa
Password  <blank>

Conclusion

I’ve done a lot of development using the Grails framework. The Grails team added the H2 database console with the release of Grails 2. I quickly fell in love with this feature. Well, maybe not “love”, but it became a feature of Grails I used frequently. When you’re developing an application using Spring / Hibernate (As you are with Grails), you will need to see into the database. The H2 database console is a great tool to have at your disposal.

Maybe we’ll see this as a default option in a future version of Spring Boot. But for now, you’ll need to add the H2 database console yourself. Which you can see isn’t very hard to do.

Free Spring Tutorial
Check out my FREE Introduction to Spring Course!
25
Share