FavTuts

Favourite tutorials for developers

Spring Boot

Spring Boot JDBC + MySQL + HikariCP example

ByFavTuts Editorial

Jun 14, 2022 #connection pool, #hikaricp, #JDBC, #MySQL, #Spring Boot, #spring jdbc

In this article, we will show you how to create a Spring Boot JDBC application + MySQL and HikariCP.

Tools used in this article :

  1. Spring Boot 1.5.1.RELEASE
  2. MySQL 5.7.x
  3. HikariCP 2.6
  4. Maven
  5. Java 8

Note

Related – Spring Boot JDBC + Oracle database + Commons DBCP2 example

1. Project Structure

A standard Maven project structure.

2. Project Dependency

Declares a spring-boot-starter-jdbc for JDBC application.

pom.xml

<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/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.favtuts</groupId>
    <artifactId>spring-boot-jdbc</artifactId>
    <packaging>jar</packaging>
    <version>1.0</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.1.RELEASE</version>
    </parent>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>

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

		<!-- exclude tomcat jdbc connection pool, use HikariCP -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.tomcat</groupId>
                    <artifactId>tomcat-jdbc</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!-- exclude tomcat-jdbc, Spring Boot will use HikariCP automatically  -->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>2.6.0</version>
        </dependency>

        <!-- For MySQL -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.40</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <!-- Package as an executable jar/war -->
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>

        </plugins>
    </build>

</project>

Terminal

$ mvn dependency:tree
[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building spring-boot-jdbc 1.0
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- maven-dependency-plugin:2.10:tree (default-cli) @ spring-boot-jdbc ---
[INFO] com.favtuts:spring-boot-jdbc:jar:1.0
[INFO] +- org.springframework.boot:spring-boot-starter:jar:1.5.1.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot:jar:1.5.1.RELEASE:compile
[INFO] |  |  \- org.springframework:spring-context:jar:4.3.6.RELEASE:compile
[INFO] |  |     +- org.springframework:spring-aop:jar:4.3.6.RELEASE:compile
[INFO] |  |     \- org.springframework:spring-expression:jar:4.3.6.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot-autoconfigure:jar:1.5.1.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter-logging:jar:1.5.1.RELEASE:compile
[INFO] |  |  +- ch.qos.logback:logback-classic:jar:1.1.9:compile
[INFO] |  |  |  \- ch.qos.logback:logback-core:jar:1.1.9:compile
[INFO] |  |  +- org.slf4j:jcl-over-slf4j:jar:1.7.22:compile
[INFO] |  |  +- org.slf4j:jul-to-slf4j:jar:1.7.22:compile
[INFO] |  |  \- org.slf4j:log4j-over-slf4j:jar:1.7.22:compile
[INFO] |  +- org.springframework:spring-core:jar:4.3.6.RELEASE:compile
[INFO] |  \- org.yaml:snakeyaml:jar:1.17:runtime
[INFO] +- org.springframework.boot:spring-boot-starter-jdbc:jar:1.5.1.RELEASE:compile
[INFO] |  \- org.springframework:spring-jdbc:jar:4.3.6.RELEASE:compile
[INFO] |     +- org.springframework:spring-beans:jar:4.3.6.RELEASE:compile
[INFO] |     \- org.springframework:spring-tx:jar:4.3.6.RELEASE:compile
[INFO] +- com.zaxxer:HikariCP:jar:2.6.0:compile
[INFO] |  \- org.slf4j:slf4j-api:jar:1.7.22:compile
[INFO] \- mysql:mysql-connector-java:jar:5.1.40:compile
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 1.164 s
[INFO] Finished at: 2017-02-12T23:42:47+08:00
[INFO] Final Memory: 20M/309M
[INFO] ------------------------------------------------------------------------

Database Connection Pooling
Spring Boot uses Tomcat pooling tomcat-jdbc by default, and follow this sequence to find the connection pool :

Tomcat pool -->> - HikariCP -->> Commons DBCP -->> Commons DBCP2

Read this official Spring Boot doc – Connection to a production database

3. JdbcTemplate

3.1 Spring Boot will register a JdbcTemplate bean automatically, injects it via @Autowired

CustomerRepository.java

package com.favtuts.dao;

import com.favtuts.model.Customer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Repository
public class CustomerRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

	// Find all customers, thanks Java 8, you can create a custom RowMapper like this : 
    public List<Customer> findAll() {

        List<Customer> result = jdbcTemplate.query(
                "SELECT id, name, email, created_date FROM customer",
                (rs, rowNum) -> new Customer(rs.getInt("id"),
                        rs.getString("name"), rs.getString("email"), rs.getDate("created_date"))
        );

        return result;

    }

	// Add new customer
    public void addCustomer(String name, String email) {

        jdbcTemplate.update("INSERT INTO customer(name, email, created_date) VALUES (?,?,?)",
                name, email, new Date());

    }


}

3.2 Customer Model.

Customer.java

package com.favtuts.model;

import java.util.Date;

public class Customer {

    int id;
    String name;
    String email;
    Date date;

    public Customer(int id, String name, String email, Date date) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.date = date;
    }

    //getters and setters and toString...
}

4. Database Initialization

Spring boot enables the dataSource initializer by default and loads SQL scripts (schema.sql and data.sql) from the root of the classpath.

4.1 Create a customer table.

schema.sql

DROP TABLE IF EXISTS customer;
CREATE TABLE customer (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  created_date DATE NOT NULL,
  PRIMARY KEY (id));

4.2 Insert 3 rows into the customer table.

data.sql

INSERT INTO customer(name,email,created_date)VALUES('favtuts','111@yahoo.com', '2017-02-11');
INSERT INTO customer(name,email,created_date)VALUES('yflow','222@yahoo.com', '2017-02-12');
INSERT INTO customer(name,email,created_date)VALUES('zilap','333@yahoo.com', '2017-02-13');

Note

For detail, please refer to this official article – Spring Database initialization

4.3 To log the SQL scripts above, enable debug for org.springframework.jdbc

logback.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration>

    <statusListener class="ch.qos.logback.core.status.NopStatusListener" />

    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <layout class="ch.qos.logback.classic.PatternLayout">
            <Pattern>
                %d{yyyy-MM-dd HH:mm:ss} %-5level %logger{36} - %msg%n
            </Pattern>
        </layout>
    </appender>

    <logger name="org.springframework.jdbc" level="error" additivity="false">
        <appender-ref ref="STDOUT"/>
    </logger>

    <logger name="com.favtuts" level="error" additivity="false">
        <appender-ref ref="STDOUT"/>
    </logger>

    <root level="error">
        <appender-ref ref="STDOUT"/>
    </root>

</configuration>

5. Configuration

Configure MySQL and HikariCP settings.

application.properties

#disbale Spring banner
spring.main.banner-mode=off

# Loads SQL scripts? schema.sql and data.sql
#spring.datasource.initialize=true

spring.datasource.url=jdbc:mysql://localhost/favtutsjava?useSSL=false
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

# HikariCP settings
# spring.datasource.hikari.*

#60 sec
spring.datasource.hikari.connection-timeout=60000
# max 5
spring.datasource.hikari.maximum-pool-size=5

6. @SpringBootApplication

Spring Boot console or CommandLineRunner application, accept arguments to perform either “display” or “insert” function.

SpringBootConsoleApplication.java

package com.favtuts;

import com.favtuts.dao.CustomerRepository;
import com.favtuts.model.Customer;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import javax.sql.DataSource;
import java.util.List;

import static java.lang.System.exit;

@SpringBootApplication
public class SpringBootConsoleApplication implements CommandLineRunner {

    @Autowired
    DataSource dataSource;

    @Autowired
    private CustomerRepository customerRepository;

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

    @Override
    public void run(String... args) throws Exception {

        System.out.println("DATASOURCE = " + dataSource);

        // If you want to check the HikariDataSource settings
        //HikariDataSource newds = (HikariDataSource)dataSource;
        //System.out.println("DATASOURCE = " + newds.getMaximumPoolSize());

        if (args.length <= 0) {
            System.err.println("[Usage] java xxx.jar {insert name email | display}");
        } else {
            if (args[0].equalsIgnoreCase("insert")) {
                System.out.println("Add customer...");
                String name = args[1];
                String email = args[2];
                customerRepository.addCustomer(name, email);
            }

            if (args[0].equalsIgnoreCase("display")) {
                System.out.println("Display all customers...");
                List<Customer> list = customerRepository.findAll();
                list.forEach(x -> System.out.println(x));
            }
            System.out.println("Done!");
        }
        exit(0);
    }
}

Done.

7. DEMO

Terminal

$ mvn package

# 1. The database is initialized, the table is created, data is inserted
$ java -jar target/spring-boot-jdbc-1.0.jar

DATASOURCE = HikariDataSource (HikariPool-1)
[Usage] java xxx.jar {insert name email | display}

# 2. Disable database initialize process, and insert a new customer
$ java -Dspring.datasource.initialize=false -jar target/spring-boot-jdbc-1.0.jar insert newUser newPassword

DATASOURCE = HikariDataSource (null)
Add customer...
Done!

# 3. Display all customers
$ java -Dspring.datasource.initialize=false -jar target/spring-boot-jdbc-1.0.jar display
DATASOURCE = HikariDataSource (null)
Display all customers...
Customer{id=1, name='favtuts', email='111@yahoo.com', date=2017-02-11}
Customer{id=2, name='yflow', email='222@yahoo.com', date=2017-02-12}
Customer{id=3, name='zilap', email='333@yahoo.com', date=2017-02-13}
Customer{id=4, name='newUser', email='newPassword', date=2017-02-12}
Done!

Download Source Code

$ git clone https://github.com/favtuts/java-spring-tutorials.git
$ cd spring-boot-jdbc-mysql
$ mvn spring-boot:run

References

  1. Spring Boot – Working with SQL databases
  2. Spring Boot – Database initialization
  3. Spring Boot common application properties
  4. Spring Boot – DataSourceBuilder
  5. HikariCP

By FavTuts Editorial

Related Post

Spring Boot

Validation with Spring Boot – the Complete Guide

Dec 10, 2024 FavTuts Editorial
Spring Boot

How to Record Request and Response Bodies in Sping Boot Applications

Dec 8, 2024 FavTuts Editorial
Spring Boot

How to create a Spring Boot console application

Dec 4, 2024 FavTuts Editorial

Leave a Reply

Your email address will not be published. Required fields are marked *

You missed

Spring Boot

Validation with Spring Boot – the Complete Guide

December 10, 2024 FavTuts Editorial
Spring Boot

How to Record Request and Response Bodies in Sping Boot Applications

December 8, 2024 FavTuts Editorial
Spring Boot

How to create a Spring Boot console application

December 4, 2024 FavTuts Editorial
PHP

How To Change Your PHP Settings on Ubuntu

November 30, 2024 FavTuts Editorial