Monthly Archives: May 2022

How to Connect to a Database from Spring Boot

In this post, I will show how we can connect to a database from Spring Boot Application. This post will focus on relational databases, but you can also connect MongoDB from Spring Boot application.

Add the database dependency

To connect your Spring Boot application to the database, you can add either of the following dependencies and it will connect to the database.

<dependency>
    <groupdId>org.springframework.boot</groupdId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    <version>2.6.7</version>
</dependency>

OR if you are a gradle fan like me, you can use this

implementation 'org.springframework.boot:spring-boot-starter-jdbc:2.6.7'

If you use JDBC dependency, then in your code, you will have to use JDBCTemplate to make database calls. This is a nice feature of Spring Boot. Nevertheless, it takes away a good set of segregation in architecture design. To avoid this, we can have a domain layer where we can use repositories to make database calls.

Therefore, another way to connect to the database is to use the JPA dependency.

<dependency>
    <groupdId>org.springframework.boot</groupdId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
    <version>2.6.7</version>
</dependency>

OR

implementation 'org.springframework.boot:spring-boot-starter-data-jpa:2.6.7'

Spring Data JPA makes it easy to implement JPA-based repositories. You can learn more about Spring Data JPA.

Configure JDBC Database driver

After all, you will be able to call the database through your repository calls if there is a real connection between your app and the database. For this reason, you will need a JDBC driver appropriate to your database. It can be PostgreSQL, MySQL, Microsoft SQL Server, or NoSQL database (like MongoDB OR Cassandra)  driver.

Add the following dependency if you are using PostgreSQL:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.3.5</version>
    <scope>runtime</scope>
</dependency>

OR for Gradle

implementation 'org.postgresql:postgresql:42.3.5'

If using MySQL:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.29</version>
    <scope>runtime</scope>
</dependency>

Note that we are marking these dependencies for runtime use. Every time your application needs some data, it will call the backend and the backend will connect to the database. During this communication, the driver will come into the picture.

Database connection properties

We have configured our required dependencies. Overall, we need to connect our spring boot application to the database now.  We will set up the database properties for the same. These properties include database URL, username, and password. Most of these properties start with spring.datasource.*.

Depending on what database you are using, the values for the properties will be different. The following section shows the properties of MySQL Database:


spring.datasource.url=jdbc:mysql://127.0.0.1/springbatchdemo
spring.datasource.username = databaseuser
spring.datasource.password = databasepassword
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

These properties help to create a database connection pool. We can then programmatically call database details so our application can connect to the database. There are other datasource properties for Hikari . Hikari is a JDBC Datasource implementation that provides database connection pooling.


spring.datasource.hikari.connection-test-query=SELECT 1
spring.datasource.hikari.maximum-pool-size=50
spring.datasource.hikari.minimum-idle=5
logging.level.com.zaxxer.hikari=DEBUG

Spring Boot uses Hikari by default. You can also use Tomcat for database connection pooling.

Demo – Connect Spring Boot Application to Database

To demonstrate this connection, I have created a simple application called databasedemo. I added the properties as shown above. Obviously, I added some debug logging as well for Hikari.

The main class looks like the below:

 


package com.betterjavacode.databasedemo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DatabasedemoApplication {

	public static void main(String[] args) {
		SpringApplication.run(DatabasedemoApplication.class, args);
	}

}

Now, if I start my application, I will see the log that shows our spring boot application connects to the database.


2022-05-15 00:46:48.069  INFO 8324 --- [           main] c.b.d.DatabasedemoApplication            : Starting DatabasedemoApplication using Java 1.8.0_212 on YMALI2019 with PID 8324 (C:\projects\databasedemo\build\classes\java\main started by Yogesh Mali in C:\projects\databasedemo)
2022-05-15 00:46:48.074  INFO 8324 --- [           main] c.b.d.DatabasedemoApplication            : No active profile set, falling back to 1 default profile: "default"
2022-05-15 00:46:48.811  INFO 8324 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2022-05-15 00:46:48.840  INFO 8324 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 9 ms. Found 0 JPA repository interfaces.
2022-05-15 00:46:49.324 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : Driver class com.mysql.cj.jdbc.Driver found in Thread context class loader sun.misc.Launcher$AppClassLoader@659e0bfd
2022-05-15 00:46:49.566 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : HikariPool-1 - configuration:
2022-05-15 00:46:49.570 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : allowPoolSuspension................................false
2022-05-15 00:46:49.570 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : autoCommit................................true
2022-05-15 00:46:49.570 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : catalog................................none
2022-05-15 00:46:49.570 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : connectionInitSql................................none
2022-05-15 00:46:49.571 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : connectionTestQuery................................"SELECT 1"
2022-05-15 00:46:49.571 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : connectionTimeout................................30000
2022-05-15 00:46:49.571 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : dataSource................................none
2022-05-15 00:46:49.571 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : dataSourceClassName................................none
2022-05-15 00:46:49.571 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : dataSourceJNDI................................none
2022-05-15 00:46:49.572 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : dataSourceProperties................................{password=}
2022-05-15 00:46:49.572 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : driverClassName................................"com.mysql.cj.jdbc.Driver"
2022-05-15 00:46:49.573 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : exceptionOverrideClassName................................none
2022-05-15 00:46:49.573 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : healthCheckProperties................................{}
2022-05-15 00:46:49.573 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : healthCheckRegistry................................none
2022-05-15 00:46:49.573 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : idleTimeout................................600000
2022-05-15 00:46:49.573 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : initializationFailTimeout................................1
2022-05-15 00:46:49.573 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : isolateInternalQueries................................false
2022-05-15 00:46:49.573 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : jdbcUrl................................jdbc:mysql://127.0.0.1/springbatchdemo?autoReconnect=true&useSSL=false
2022-05-15 00:46:49.573 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : keepaliveTime................................0
2022-05-15 00:46:49.573 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : leakDetectionThreshold................................0
2022-05-15 00:46:49.574 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : maxLifetime................................1800000
2022-05-15 00:46:49.574 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : maximumPoolSize................................10
2022-05-15 00:46:49.574 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : metricRegistry................................none
2022-05-15 00:46:49.574 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : metricsTrackerFactory................................none
2022-05-15 00:46:49.574 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : minimumIdle................................10
2022-05-15 00:46:49.574 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : password................................
2022-05-15 00:46:49.575 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : poolName................................"HikariPool-1"
2022-05-15 00:46:49.575 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : readOnly................................false
2022-05-15 00:46:49.575 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : registerMbeans................................false
2022-05-15 00:46:49.575 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : scheduledExecutor................................none
2022-05-15 00:46:49.575 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : schema................................none
2022-05-15 00:46:49.575 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : threadFactory................................internal
2022-05-15 00:46:49.576 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : transactionIsolation................................default
2022-05-15 00:46:49.576 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : username................................"root"
2022-05-15 00:46:49.576 DEBUG 8324 --- [           main] com.zaxxer.hikari.HikariConfig           : validationTimeout................................5000
2022-05-15 00:46:49.576  INFO 8324 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2022-05-15 00:46:49.879 DEBUG 8324 --- [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@49bf29c6
2022-05-15 00:46:49.882  INFO 8324 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2022-05-15 00:46:49.979  INFO 8324 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2022-05-15 00:46:49.984 DEBUG 8324 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Pool stats (total=1, active=0, idle=1, waiting=0)
2022-05-15 00:46:49.997 DEBUG 8324 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@6e7b7614
2022-05-15 00:46:50.008 DEBUG 8324 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@2f467c2f
2022-05-15 00:46:50.018 DEBUG 8324 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@1ea3f201
2022-05-15 00:46:50.028 DEBUG 8324 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@4f698360
2022-05-15 00:46:50.037 DEBUG 8324 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@a97f000
2022-05-15 00:46:50.046 DEBUG 8324 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@4b27b8a8
2022-05-15 00:46:50.056 DEBUG 8324 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@26e87d5e
2022-05-15 00:46:50.065 DEBUG 8324 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@19511557
2022-05-15 00:46:50.072 DEBUG 8324 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@17cd85b6
2022-05-15 00:46:50.073 DEBUG 8324 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - After adding stats (total=10, active=0, idle=10, waiting=0)
2022-05-15 00:46:50.108  INFO 8324 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate ORM core version 5.6.8.Final
2022-05-15 00:46:50.448  INFO 8324 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.1.2.Final}
2022-05-15 00:46:51.392  INFO 8324 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect

Spring Boot offers a DataSource object that you can inject where you want to connect to the database. You can also set all the data source properties programmatically. Besides, now you can either use repositories or jdbcTemplate to fetch any database object from the database. 

Conclusion

In this post, I showed how one can connect a spring boot application to the database. Spring Boot has made this easier by taking away a lot of boilerplate code. This allows developers to focus on business logic and not worry about database-level coding.