Setting up a database connection

In every application, there is a need to access some data and conduct some operations on it. Most frequently, this source of data is a datastore of some kind, namely a database. Spring Boot makes it very easy to get started in order to connect to the database and start consuming the data via the JPA, among others.

Getting ready

In our previous example, we created the basic application that will execute a command-line runner by printing a message in the logs. Let's enhance this application by adding a connection to a database.

Earlier, we already added the necessary jdbc and data-jpa starters as well as an H2 database dependency to our build file. Now we will configure an in-memory instance of the H2 database.

In the case of an embedded database, such as H2, Hyper SQL Database (HSQLDB), or Derby, no actual configuration is required besides including the dependency on one of these in the build file. When one of these databases is detected in the classpath and a DataSource bean dependency is declared in the code, Spring Boot will automatically create one for you.

To demonstrate the fact that just by including the H2 dependency in the classpath, we will automatically get a default database, let's modify our StartupRunner.java file to look as follows:

public class StartupRunner implements CommandLineRunner { 
    protected final Log logger = LogFactory.getLog(getClass()); 
    @Autowired 
    private DataSource ds; 
    @Override 
    public void run(String... args) throws Exception { 
        logger.info("DataSource: "+ds.toString()); 
    } 
} 

Now, if we proceed with the running of our application, we will see the name of the datasource printed in the log, as follows:

2017-12-16 21:46:22.067 com.example.bookpub.StartupRunner   
:DataSource: org.apache.tomcat.jdbc.pool.DataSource@4...  {...driverClassName=org.h2.Driver; ... }
  

So, under the hood, Spring Boot recognized that we've autowired a DataSource bean dependency and automatically created one initializing the in-memory H2 datastore. This is all well and good, but probably not all too useful beyond an early prototyping phase or for the purpose of testing. Who would want a database that goes away with all the data as soon as your application shuts down and you have to start with a clean slate every time you restart the application?

How to do it...

Let's change the defaults in order to create an embedded H2 database that will not store data in-memory, but rather use a file to persist the data among application restarts, by performing the following steps:

1. Open the file named application.properties under the src/main/resources directory from the root of our project and add the following content:

spring.datasource.url = jdbc:h2:~/test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE 
spring.datasource.username = sa 
spring.datasource.password = 

2. Start the application by executing ./gradlew clean bootRun from the command line
3. Check your home directory, and you should see the following file in there: test.mv.db

The user home directory is located under /home/<username> on Linux and under /Users/<username> on macOS X.

How it works...

Even though, by default, Spring Boot makes certain assumptions about the database configuration by examining the classpath for the presence of supported database drivers, it provides you with easy configuration options to tweak the database access via a set of exposed properties grouped under spring.datasource.

The things that we can configure are url, username, password, driver-class-name, and so on. If you want to consume the datasource from a JNDI location, where an outside container creates it, you can configure this using the spring.datasource.jndi-name property. The complete set of possible properties is fairly large, so we will not go into all of them. However, we will cover more options in Chapter 5, Application Testing, where we will talk about mocking data for application tests using a database.

By looking at various blogs and examples, you may notice that some places use dashes in property names like driver-class-name, while others use camel-cased variants: driverClassName. In Spring Boot, these are actually two equally supported ways of naming the same property, and they get translated into the same thing internally.

If you want to connect to a regular (non-embedded) database, besides just having the appropriate driver library in the classpath, we need to specify the driver of our choice in the configuration. The following code snippet is what the configuration to connect to MySQL would resemble:

    spring.datasource.driver-class-name: com.mysql.jdbc.Driver
    spring.datasource.url:   
    jdbc:mysql://localhost:3306/springbootcookbook
    spring.datasource.username: root
    spring.datasource.password:
  

If we wanted Hibernate to create the schema automatically, based on our entity classes, we would need to add the following line to the configuration:

    spring.jpa.hibernate.ddl-auto=create-drop
  

Don't do it in the production environment, otherwise, on startup, all the table schemas and data will be deleted! Use the update or validate values instead, where needed.

You can go even further in the abstraction layer and, instead of autowiring a DataSource object, you could go straight for JdbcTemplate. This would instruct Spring Boot to automatically create a DataSource and then create a JdbcTemplate wrapping the datasource, thus providing you with a more convenient way of interacting with a database in a safe way. The code for JdbcTemplate is as follows:

@Autowired 
private JdbcTemplate jdbcTemplate; 

You can also look in the spring-boot-autoconfigure source at an org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration file to see the code behind the datasource creation magic.