The Spring Boot Jdbc Mysql Example will show you how to create a Spring Boot JDBC application using MySQL and HikariCP. Our application will accept arguments to perform either “insert” or “query” function. If arguments is “insert” it will insert a row with data input. If arguments is “query” it will query all rows on the table
Other interesting posts you may like
Let’s begin:
Project Structure
In this Spring Boot Jdbc Mysql Example, we create a standard Maven project structure like this
Maven Dependency
Our Spring Boot Jdbc Mysql Example will use JDBC, MySQL and HikariCP connection pool, so that we must add these dependencies in the pom.xml
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
<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.javabycode</groupId> <artifactId>spring-boot-jdbc-mysql-example</artifactId> <packaging>jar</packaging> <version>1.0</version> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>1.5.2.RELEASE</version> </parent> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <!-- JDBC --> <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> <!-- HikariCP connection pools --> <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> |
JDBC Properties Configuration
For example, we have a database schema named javabcyode. We configure JDBC and HikariCP settings in the jdbc.properties file and place that file in the classpath directory
|
1 2 3 4 5 6 7 8 9 10 11 |
#spring.datasource.initialize=true spring.datasource.url=jdbc:mysql://localhost/javabycode? spring.datasource.username=javabycode spring.datasource.password=mypassword spring.datasource.driver-class-name=com.mysql.jdbc.Driver # HikariCP settings #60 sec spring.datasource.hikari.connection-timeout=60000 # max 10 spring.datasource.hikari.maximum-pool-size=10 |
Create Database Table
Create a tenant table using the following script
|
1 2 3 4 5 6 7 |
DROP TABLE IF EXISTS tenant; CREATE TABLE tenant ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL, created_date DATE NOT NULL, PRIMARY KEY (id)); |
Initialize data for the tenant table using the script below
|
1 2 |
INSERT INTO tenant(name,email,created_date)VALUES('David Pham','david@yahoo.com', '2017-04-11'); INSERT INTO tenant(name,email,created_date)VALUES('Bill Gates','bill@yahoo.com', '2017-04-12'); |
Dig deeper: We just have to push two above scripts schema.sql and data.sql in the classpath directory and do nothing. Then Spring Boot will take care to create table and insert data for us. Because Spring boot enables the dataSource initializer by default and loads SQL scripts from the classpath.
Create TenantRepository
We create TenantRepository class and inject JdbcTemplate via @Autowired. This repository takes care of the database side
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
package com.javabycode.dao; import com.javabycode.model.Tenant; 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 TenantRepository { @Autowired private JdbcTemplate jdbcTemplate; public List<Tenant> findAll() { List<Tenant> result = jdbcTemplate.query( "SELECT id, name, email, created_date FROM tenant", (rs, rowNum) -> new Tenant(rs.getInt("id"), rs.getString("name"), rs.getString("email"), rs.getDate("created_date")) ); return result; } public void addTenant(String name, String email) { jdbcTemplate.update("INSERT INTO tenant(name, email, created_date) VALUES (?,?,?)", name, email, new Date()); } } |
Create Tenant Model
Our Tenant model is simple like this
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
package com.javabycode.model; import java.util.Date; public class Tenant { int id; String name; String email; Date date; public Tenant(int id, String name, String email, Date date) { this.id = id; this.name = name; this.email = email; this.date = date; } @Override public String toString() { return "Tenant{" + "id=" + id + ", name='" + name + '\'' + ", email='" + email + '\'' + ", date=" + date + '}'; } //getter and setter } |
Create SpringBootApplication class
Our MyConsoleApplication class implements the CommandLineRunner interface, accept arguments to perform either “insert” or “query” function.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
package com.javabycode; import com.javabycode.dao.TenantRepository; import com.javabycode.model.Tenant; import com.zaxxer.hikari.HikariDataSource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.CommandLineRunner; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.PropertySource; import javax.sql.DataSource; import java.util.List; import static java.lang.System.exit; @SpringBootApplication @Configuration @PropertySource("classpath:jdbc.properties") public class MyConsoleApplication implements CommandLineRunner { @Autowired DataSource dataSource; @Autowired private TenantRepository tenantRepository; public static void main(String[] args) throws Exception { SpringApplication.run(MyConsoleApplication.class, args); } @Override public void run(String... args) throws Exception { if (args.length<=0){ // Print the HikariDataSource settings System.out.println("Datasource = " + dataSource); HikariDataSource myDatasource = (HikariDataSource)dataSource; System.out.println("Datasource pool size = " + myDatasource.getMaximumPoolSize()); } if (args.length>0 && args[0].equalsIgnoreCase("insert")) { System.out.println("Add tenant..."); String name = args[1]; String email = args[2]; tenantRepository.addTenant(name, email); } if (args.length>0 && args[0].equalsIgnoreCase("query")) { System.out.println("Display all tenants..."); List<Tenant> list = tenantRepository.findAll(); list.forEach(x -> System.out.println(x)); } System.out.println("Done!"); exit(0); } } |
Demo Spring Boot JDBC Application
Go to the project directory in the console
#Packaging jar file
|
1 |
mvn package |
#Run jar file to initialize the database, create the table and insert data and display the datasource information
|
1 2 3 4 |
java -jar target/spring-boot-jdbc-1.0.jar Datasource = HikariDataSource (HikariPool-1) Datasource pool size = 10 |
Run the command to insert a new tenant, don’t for get disable database initialize process
java -Dspring.datasource.initialize=false -jar target/spring-boot-jdbc-1.0.jar insert jdbcUser jdbcPassword
|
1 2 |
Add tenant... Done! |
Query all tenants in the database
java -Dspring.datasource.initialize=false -jar target/spring-boot-jdbc-mysql-example-1.0.jar query
|
1 2 3 4 5 |
Display all tenants... Tenant{id=1, name='David Pham', email='david@yahoo.com', date=2017-04-11} Tenant{id=2, name='Bill Gates', email='bill@yahoo.com', date=2017-04-12} Tenant{id=3, name='jdbcUser', email='jdbcPassword', date=2017-04-14} Done! |
That’s all on the Spring Boot Jdbc Mysql Example.
References
Spring Boot – Working with SQL databases
Spring Boot – Database initialization
HikariCP
Download complete source code, click link below
spring-boot-jdbc-mysql-example.zip (240 downloads)
