Spring Boot JDBC + MySQL + HikariCP example
In this article, we will show you how to create a Spring Boot JDBC application + MySQL and HikariCP.
Tools used in this article :
- Spring Boot 1.5.1.RELEASE
- MySQL 5.7.x
- HikariCP 2.6
- Maven
- Java 8
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.
<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.mkyong</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>
$ 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.mkyong: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] ------------------------------------------------------------------------
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
package com.mkyong.dao;
import com.mkyong.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.
package com.mkyong.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.
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.
INSERT INTO customer(name,email,created_date)VALUES('mkyong','[email protected]', '2017-02-11');
INSERT INTO customer(name,email,created_date)VALUES('yflow','[email protected]', '2017-02-12');
INSERT INTO customer(name,email,created_date)VALUES('zilap','[email protected]', '2017-02-13');
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
<?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.mkyong" 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.
#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/mkyong?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.
package com.mkyong;
import com.mkyong.dao.CustomerRepository;
import com.mkyong.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
$ 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='mkyong', email='[email protected]', date=2017-02-11}
Customer{id=2, name='yflow', email='[email protected]', date=2017-02-12}
Customer{id=3, name='zilap', email='[email protected]', date=2017-02-13}
Customer{id=4, name='newUser', email='newPassword', date=2017-02-12}
Done!
Following this to get to a external profile yml configuration with encrypted properties but I am getting
Failed to configure a DataSource: ‘url’ attribute is not specified and no embedded datasource could be configured.
Reason: Failed to determine a suitable driver class
application-dev.yml
spring:
profiles:
active: dev
spring:
datasource:
driverClassName: net.sourceforge.jtds.jdbc.Driver
url: jdbc:jtds:sqlserver://url/Database;instance=name;sendStringParametersAsUnicode=false
username: User
password: ENC(tLchEP8ixM4GfLP3Qv0kUaTcuHBMPz8k)
Netbeans, jdk1.8.221, -Dspring.profiles.active=dev -Dmaven.test.skip=true
I assume I no longer need:
@Configuration
@ConfigurationProperties(“spring.datasources”)
public class SqlServerConfig {
private static final Logger logger = LoggerFactory.getLogger(SqlServerConfig.class);
private String driverClassName;
private String url;
private String username;
private String password;
@Profile(“dev”)
@Bean
public String devDatabaseConnection() {
System.out.println(“Connection for DEV”);
System.out.println(this.toString());
logger.debug(“Connection for DEV”);
logger.debug(this.toString());
return “”;
}
@Profile(“prod”)
@Bean
public String prodDatabaseConnection() {
System.out.println(“Connection for PROD”);
System.out.println(this.toString());
return “”;
}
@Override
public String toString() {
return “SqlServerConfig{” + “driverClassName=” + driverClassName + “, url=” + url + “, username=” + username + “, password=” + password + ‘}’;
}
}
Thank you
Never could get it to run (on Cinnamon Mint version 18, Java 1.8)
When we deploy it as a war in tomcat; it takes the default pool jars used by tomcat. Hence HikariCP would not be used at that time. Any solution for the same ??
In application.properties file add this line
spring.datasource.type = com.zaxxer.hikari.HikariDataSource
Thumbs up! You can use the DataSource object to create a Sql2o object.
Hello, Firstly thanks for the post. Its great. I have a query of the other properties of HikariCP in spring boot, Is there any list which i can use? . Currently I am am confused what is the proper values to be mentioned in the properties file .
For example as you have mentioned
spring.datasource.hikari.connection-timeout=60000
# max 5
spring.datasource.hikari.maximum-pool-size=5
I checked using Jconsole , i see different values in the attributes for HikariCP . For example Maximum pool Size is shown to be 10 and not 5.
set datasource type, once done it worked for me.
spring.datasource.type=com.zaxxer.hikari.HikariDataSource