Configuring A Database For Spring Boot

Sat, 09/09/2017 - 10:03

In the previous post we built a "hello world" application on Spring Boot. In this post we will be configuring the same application for a database connection. If you have not followed the previous tutorial, I highly encourage to do it, because this post is the logical continuation of the previous one. Now that we have our basic app running from the previous post, we can add two more Maven dependencies to pom.xml, because after all we need to be able to interact with a database server. In our case it is going to be the MySQL. We need MySQL JDBC Driver that will allow us to connect easily to live MySQL data, so we add "mysql-connector-java" dependency to pom.xml. In order to carry out the persistent related actions to and from the database  we will use the Spring Data JPA, which in its turn can be added through "spring-boot-starter-data-jpa" maven dependency. The complete pom.xml file will look like this.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.0.RELEASE</version>
  </parent>
  <groupId>com.aralmighty</groupId>
  <artifactId>demo-app</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>
  <name>Demo App</name>
  <description>Spring Boot Demo App</description>
  <dependencies>
  	<dependency>
  		<groupId>org.springframework.boot</groupId>
  		<artifactId>spring-boot-starter-web</artifactId>
  	</dependency>
  	<dependency>
  		<groupId>org.apache.tomcat.embed</groupId>
  		<artifactId>tomcat-embed-jasper</artifactId>
  	</dependency>
  	<dependency>
  		<groupId>javax.servlet</groupId>
  		<artifactId>jstl</artifactId>
  	</dependency>
  	<dependency>
  		<groupId>mysql</groupId>
  		<artifactId>mysql-connector-java</artifactId>
  	</dependency>
  	<dependency>
  		<groupId>org.springframework.boot</groupId>
  		<artifactId>spring-boot-starter-data-jpa</artifactId>
  	</dependency>
  </dependencies>
</project>

Additionally, we need to configure the database connection in the application.properties file. I have a MySQL server installed locally on my ubuntu machine, so I am going to connect to it. If you do not have it, you can easily install it by typing in your terminal the following installation command

$ sudo apt-get install mysql-server

In the process of the installation a dialog box will show up asking to type New password for the MySQL "root" user. The "root" will be your username and whatever you type will be your password to authenticate when connecting to the database. I typed "aralmighty" as my password so I am going to use that password when configuring my application.properties file. I will follow the instruction given in the official docs of Spring Boot, which can be found here.

spring.mvc.view.prefix=/WEB-INF/
spring.mvc.view.suffix=.jsp

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

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.generate-ddl=true
spring.jpa.show-sql=true

The first two lines are left from the previous post and do not need any explanation. The next four are standing for the DataSource configuration, controlled by external configuration properties in spring.datasource.*. The general format for the jdbc connection url is

jdbc:SQLSERVER://HOST:PORT/DATABASE

My MySQL server runs on my local machine using the default port number (3306). If I had PostgreSQL, it would listen on port 5432 by default and the jdbc connection url would be "jdbc:postgresql://localhost:5432/demo_db". In the case of PostgreSQL the driver class name is  

org.postgresql.jdbc.Driver

which could be found in its specific dependency jar for PostgreSQL connection that we did not choose to include into our pom.xml dependences, because we use MySQL here =D . 

The "demo_db" database name, as you have perhaps noticed in our configuration must still be created. The next 4 lines are used to explicitly configure JPA settings through  spring.jpa.* properties that for more details I refer you here. To create the "demo_db" database we go to terminal and type

$ mysql -u root -p

After typing the authentication password (in my case it is "aralmighty" as I mentioned above), go ahead and create the database.

mysql> CREATE DATABASE demo_db;

and now if we type "SHOW DATABASES;" we should be able to see the created "demo_db" database in the list of databases.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo_db            |
| mysql              |
| performance_schema |
| phpmyadmin         |
+--------------------+
5 rows in set (0.00 sec)

At this point our configuration for connecting the demo_db database is complete and we are good to stop here. In my future tutorials I will use this configuration to demonstrate the mapping of entity classes to the tables of the demo_db database.