post

Initialize a Database Using SQL Scripts with Spring Boot

1. Story

  • There are many cases in which we need to initialize an SQL database at application startup.
  • Either creating schema based on DDL scripts or feeding initializing data into database by DML scripts may be required in application startup.
  • Spring Boot provides simple features for developers to run both DDL and DML scripts automatically.
  • In this post we use this feature practically.
 

2. Create Project

You can find the complete project at Github.

Create a new project by using online Spring initializer either at start.spring.io or on Intellij _Idea > New Project > Spring Initializr.

And in dependencies window select 3 dependencies that we named them in "dependencies section" of this post.

3. Dependencies

The only needed dependency that do the job for running SQL scripts automatically is spring-boot-starter-data-jdbc. But we also want to use h2database as a simple embeded database. And at last we use spring-boot-starter-web to make our project a simple running project, so our dependencies in pom.xml file looks like this:

4. Configure H2 Database

Because of its simplicity we choose embedded h2 as our SQL databse. So no need to have a SQL database like MySQL or Oracle to be installed on your environment.

H2 is a lightweight SQL database written by Java which can also be embedded in a Java application(in memory).

And now as you can guess is the time for telling Spring_data_jdbc how to connect to our database instance of h2. We tell these things as some configuration statements. So let's open "application.properties" file in "resources" folder and fill it like this one:


Please note that we want to have a GUI from h2 so we added "spring.h2.console.enabled=true" in the properties file. And by this setting by running the application, we also will have an h2 console on our web-browser which we talk about it in following sections of this post.

Other properties are about addressing our desired h2 database. These settings seems to be much clear and self descriptive.

5. DDL Scripts

In SQL, DDL stands for Data Definition Language. DDL statements create, modify, and remove database objects such as tables and indexes.

We add a file named schema.sql to the resource folder to be loaded to classpath. This file should contains all the DDL scripts for our desired database schema.


6. DML Scripts

In SQL, DML stands for Data Manipulation Language. DML statementa used for adding (inserting), deleting, and modifying (updating) data in a database.

In this step we create a file named "data.sql" in "resource" folder to be added into the classpath and fill it with our desired DML statements:


There are two insert command in our data.sql file and of course you can add any other commands for INSERT, UPDATE, and DELETE data.

7. See Results in Console

Hey congrats, that's all, now you can run your application and if there is no bugs you would get a message like "Started DatabaseInitializationApplication in 1.828 seconds (JVM running for 2.548)" in your console/terminal.

So good. Let's open a browse and go to http://localhost:8080/h2-console. Login with the credentials that you declared in application.properties file.

And at last you can find your automatically generated table(s) with its inserted data in the h2 console just like below image.


8. Multi Datasources

If you need more than one datasource in your application, then you must determine one of them as the primary one, so Spring-data-jdbc can do things on this primary datasource.

If you do not determine the primary datasource then while starting the application h2console and also Spring-data-jdbc could not find a primary datasource and return error message. So we add second datasource properties in the application.properties file and also we define two beans for primary and second datasource and determine one of them with @Primary.


9. Further Reading

Spring Boot can also supports Higher-level Migration Tools, like Flyway or Liquibase for doing database migrations at startup. This topic is out of the context of this post and you can read more about that in Spring Documents.


Thanks for Reading. Good lock.

post

Jdbc Template vs others

Recently we used JdbcTemplate in one of our projects. I tend to use Hibernate which is an ORM, whenever we need to work with databases. But lets have a short comparison between different options a java developer has for working with databases.

1. Java Database Connectivity (JDBC)

At first we can focus on the most basic alternative which is Java Database Connectivity (JDBC). JDBC is a standard java API for accessing data between the Java Standard Edition and a wide range of databases. In short we can say JDBC API tasks are:
  • make and handle connections to database
  • creating SQL statements
  • executing SQL statements in target database
  • viewing and modifying resulting records
In most cases JDBC programming is a pain for developers as they need to manage primary things such as connections with repeating some basic codes.

2. Hibernate (ORM)

Hibernate object relational mapping provides a framework for mapping an object-oriented domain model to a relational database. So developer can focus only on the domain model and deliver all database related concerns into Hibernate. For example you can define a POJO class and relate it into a database table, instantiate an object from your class and tell Hibernate to save that object into the database table. That's great as Hibernate handles almost all JDBC tasks itself. But what if you need to work with database schema.

3. JdbcTemplate vs Hibernate-and-JDBC

It would be necessary in some situations to work with schema directly. For example you need to read data from a table which don't know its structure. JDBC can be a pain and also Hibernate tries to hide database concepts and elements. So Spring introduces JdbcTemplate which uses JDBC internally and provides a more facilitated API. By JdbcTemplate we achieve the power of JDBC but no need for write boiler plate code. The Spring JdbcTemplate has following advantages over standard JDBC:
  • JdbcTemplate has mechanism to clean-up resources automatically i.e. releasing the database connections
  • JdbcTemplate converts Jdbc SQLExceptions into RuntimeExceptions which allows the programmer to react more flexible to the errors
  • JdbcTemplate converts the vendor specific error messages into more understandable and unified set of error messages
  • JdbcTemplate provides methods to write the SQL queries directly