Configuring and Accessing Datasource With FlyWayDB: Getting Started With Java Spring

We already built a basic CRUD application with Spring Boot in my Building a Basic CRUD RESTFull Spring Boot MVC application. There, we created our Rest Controllers and integrated it with an AngularJS front-end. Despite that, we did not integrate it with a database, we just give back stubbed data so far. It is well known that is no web application is any of worth without a proper DataSource configured up to store data in it. In this module, we focus on configuring and accessing DataSource with FlyWayDB in Spring Boot.

This will involve working with the Spring Boot starters and the auto-configuration. We work with the Data JPA framework, entity models, and the FlywayDB database migration framework. After this article, you will have a better understanding of how Spring Boot integrates with 3rd party frameworks as well with the usage of Spring-Boot starters.

Configuring and Accessing DataSource With FlyWayDB

We will continue our application, which we started before. You can download it set up to date through the link that I provided at the start of the article. But before we jump right into it, I would like to talk a bit about the architecture.

System Architecture

Since we talking about persistence, we will focus on the server side layer of our application. There we currently have a Rest API set up with controllers using the Spring MVC framework. This sits on the top of our server-side stack. It tied into our Spring Data JPA layer so our Spring MVC Controllers has a persistence layer that it can utilize. Finally, our JPA ORM layer will communicate with our database.

Here we will utilize an H2 embedded database server that is easy to set up, but you can also use some other technologies like Oracle, MS-SQL, MySQL.

One other piece that you see there is the FlyWayDB component. It always a good thing incorporate a framework in our project that can manage the migration and version of our database. This where the FlyWayDB comes into the scene. We will use this framework to handle us these tasks automatically. You will see how easy to use it.

To use all of these technologies, we only need to include the Spring Boot data JPA starter dependency to our project. The Spring Boot auto-configurator will set up the Spring Data JPA and all of the dependencies that relate to this. Hence h2 and FlyWayDB not included in the starter dependency we need to add manually. Despite that, they will auto set up for us by Spring Boot as well.

Setting Up the H2 Database

For this, we need to include our Spring Data JPA stater and H2 database dependencies in our pom.xml.

<dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>

    <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
  </dependency>

After this, you can check out under the pom's Dependency hierarchy that what related jar files are pulled down to our project.

After that, just start your application and you can see in the logs that our JPA container is set up and the h2 database dependency is detected because of our database dialect is configured to H2Dialect.

Despite the auto-configuration, we can handle the behavior of our H2 database server simply by adding some records to our application.properties. The H2 database has a built-in console GUI where can hang around and control our server directly for example by running SQL queries. Let's set our database connection and enable it.

logging.level.org.org.springframework=DEBUG
server.port=8080

spring.h2.console.enabled=true
spring.h2.console.path=/h2

spring.datasource.url=jdbc:h2:file:~/spring-boot-demo
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driver-class-name=org.h2.Driver

Here we set that we would like to use the file system to store data instead of memory and the name of our database with the user credentials.

After that, if you open the browser and go the http://localhost:8080/h2 you should see the h2 database console GUI, where you log in with the basic credentials and the appropriate database URL that we have given above.

After you sign in, you can see the management GUI where you can manage your databases. Currently, it is quite empty though.

As our next step, we will create our table with the FlyWayDB database migration framework.

Integrating FlyWayDB

Firstly, before we jump in, I think is very important to clear out what is FlayWayDB? I took this quote from the original website:

Version control for your database.
Robust schema evolution across all your environments.
With ease, pleasure and plain SQL.

All in all, it helps you update your relational database in an automated, controlled way. You do this through versioned SQL scripts. Every time your program starts up, the framework gets out the latest SQL files from your project and runs it on the database. In the database, there is a schema_version table that has the responsibility to keep things in synchronized.

Let's see the steps how we can use it.

Add FlyWay Dependency

  <dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

Create Migration Scripts

Make a new SQL file under the db/migration folder. The file name of the should start with V + version number + __. This is the structure where the FlyWay will search after your scripts. Here insert the SQL script that will create our Shipwreck table.

Migrate On App Startup

Restart the application and if you check the H2 console again you can see, that our table created. Furthermore, if you check the schema_version table you will see current database version and the file that ran through the appropriate migration.

Adding Spring Data JPA

To deal with persistent data, we first need to add the Spring Data JPA starter pack to our project dependencies.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

This will pull all the necessary libraries that need to save and retrieve any objects to our database, what we created above. We won't go further to Spring Data JPA for now, because it is a huge topic. In the case that you not familiar with the technology, I will cover it in some of my upcoming posts. Or check out the official documentation.

To define our persistence object, add the following annotations to our Shipwreck.java class as below:

@Entity
public class Shipwreck {

  @Id
  @GeneratedValue(strategy=GenerationType.IDENTITY)
  Long id;
  String name;
  String description;
  String condition;
  Integer depth;
  Double latitude;
  Double longitude;
  Integer yearDiscovered;

Here we added three annotations to our class:

• @Entity: This is the keyword that defines that this class will be a persistent object
• @Id: Defines the primary key property
• @GeneratedValue: Sets the value generation strategy for our primary key. In our case, it will be auto-generated by the database engine

Lastly, add our Repository class to the project:

At this point, we created an interface that extends the JpaRepository class, and it contains all the necessary CRUD implementations by default, which satisfies our needs for now. All we needed to do is to give the appropriate object what we would like to handle and its primary key's type.

Wire the Layers Together

For the next step, just change the stub calls to real repository calls in our Controller class:

@Autowired
private ShipwreckRepository shipwreckRepository;

@RequestMapping(value = "shipwrecks", method = RequestMethod.GET)
public List<Shipwreck> list(){
  return shipwreckRepository.findAll();
}

@RequestMapping(value = "shipwrecks", method = RequestMethod.POST)
public Shipwreck create(@RequestBody Shipwreck shipwreck){
  return shipwreckRepository.save(shipwreck);
}

@RequestMapping(value = "shipwrecks/{id}", method = RequestMethod.GET)
public Shipwreck get(@PathVariable Long id){
  return shipwreckRepository.getOne(id);
}

@RequestMapping(value = "shipwrecks/{id}", method = RequestMethod.PUT)
public Shipwreck update(@PathVariable Long id, @RequestBody Shipwreck shipwreck){

  Shipwreck exsistingShipwreck = shipwreckRepository.getOne(id);
  BeanUtils.copyProperties(shipwreck, exsistingShipwreck);

  return shipwreckRepository.save(shipwreck);
}

@RequestMapping(value = "shipwrecks/{id}", method = RequestMethod.DELETE)
public void delete(@PathVariable Long id){
  Shipwreck exsistingShipwreck = shipwreckRepository.getOne(id);

  shipwreckRepository.delete(exsistingShipwreck);
}

To achieve this, auto-wire the ShipwreckRepository class. After this, we can call the appropriate operations across it. As you can see, to update or delete objects, we have to deal with the managed version of it. To get these objects, we need to get them from the database then delete this or update its the properties to the newer ones. To update, we use the BeanUtils class, which solves our problem quickly via the copyProperties method.

Well, that's all. If you run the application, you should see no records because we didn't save anyone yet to our database.

Test it by adding a new record on the web UI:

Then, check that truly exists in our database:

Summary

In this module, we covered how you can integrate your application with a persistent database. We used a simple h2 database to achieve our goals. I introduced you the FlyWayDB database migration tool, which is an easy way to deal with database versioning quite easily, by automating the process. The Spring Boot framework auto-configured them up quickly by adding the appropriate dependencies to our pom.xml. Lastly, we created and auto-wired a simple CRUD Spring Data JPA repository class that communicates with the database.

Hope you enjoyed my article. If you have any thoughts or questions, please share in the comments.