One of the first things people need to consider when creating a REST API or an application, in general, is where to store the data it may need to access. Fortunately, it’s incredibly easy to sit your Spring Boot app on top of a database due to the Spring Boot devs making it configurable either via code, or configuration files. This post will show you how to connect your app to the most common relational databases.
Prerequisites
Simply enough, if you create a Spring Boot project using the initializr, you just have to add a database driver dependency of your choice from the list and a Spring Data JPA dependency. Obviously, in terms of database setup, this will vary depending on which DB you want to use. The general idea is to create a database user with a password, create a database, and ensure that the user can access the database.
PostgreSQL
Database Setup
To configure the database, you’ll need to install PostgreSQL (we recommend Docker for this, watch this space for a post on how to configure this), then in short terms, run the following commands:
Connect to your Database, for a linux install, it would be: sudo -u postgres psql
postgres=# create database springboot-test-db;
postgres=# create user spring-boot-user with encrypted password 'somerandompassword';
postgres=# grant all privileges on database springboot-test-db to spring-boot-user;App Creation
To create a project using PostgreSQL as the data store, fire up the Spring Boot Initializr with the PostgreSQL driver and Spring Data JPA dependencies
Properties Setup
We’ve used basic inputs for the above and you may want to tweak certain variables such as the metadata and any extra dependencies you may need. Once you have completed the form and downloaded the output, import it into your IDE of choice! Spring boot makes the next steps very simple, all you have to do is add the following into your application.properties file:
spring.datasource.url=jdbc:mysql://localhost/spring-boot-test-db spring.datasource.username=spring-boot-user spring.datasource.password=somerandompassword spring.datasource.driver-class-name=org.hibernate.dialect.PostgreSQLDialect
If you have configured everything correctly, simply start your app and it should connect to the database for you!
MySQL
Database Setup
Obviously, a prerequisite for connecting a Spring Boot application to MySQL is actually having MySQL installed. Once you have it installed somewhere, create a user, database and give the user access:
mysql> create database SPRING_BOOT_TEST_DB;
mysql> create user 'SPRING_BOOT_USER'@'localhost' identified by 'somerandompassword';
mysql> grant all privileges on SPRING_BOOT_TEST_DB.* TO 'SPRING_BOOT_USER'@'localhost';App Creation
Next, we need to create our Spring Boot project using the Initializr with the Spring JPA and MySQL driver dependencies.
Properties Setup
As with the PostgreSQL example above, you will need the following in the application.properties file, using the examples we used in the database setup section, it would look like this:
spring.datasource.url=jdbc:mysql://localhost:3306/SPRING_BOOT_TEST_DB
spring.datasource.username=SPRING_BOOT_USER
spring.datasource.password=somerandompassword
spring.datasource.driver-class-name=com.mysql.jdbc.DriverMicrosoft SQL Server
Database Setup
As the SQL Server documentation is incredibly well written, I’ll just link you there instead!
Create a User: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user?view=sql-server-ver15
Create a Database: https://docs.microsoft.com/en-us/sql/relational-databases/databases/create-a-database?view=sql-server-ver15
App Creation
Next, create your application using the Initializr, adding the SQL Server driver and Spring Data JPA dependencies:
Properties Setup
You know the script by now if you’ve read any of the other sections of this post. Simply update your application.properties file with the below, swapping out the values where appropriate to fit your environment. Once done, start your app and you’re good to go!
spring.datasource.url=jdbc:sqlserver://localhost;databaseName=spring-boot-db
spring.datasource.username=spring-boot-user
spring.datasource.password=somerandompassword
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver