Database Operations with Spring Boot & JDBC

In this post, we will go through the configuration to setup Spring Boot with MySQL access and operations. In order to make the connection, we will use JDBC.

Intro

JDBC stands for Java DataBase Connectivity, and it is a Java API for implementing SQL queries. The basic JDBC configuration file looks like this:

//第一步，注册驱动程序  
//com.MySQL.jdbc.Driver  
Class.forName("数据库驱动的完整类名");

//第二步，获取一个数据库的连接  
Connection conn = DriverManager.getConnection("数据库地址","用户名","密码");   

//第三步，创建一个会话  
Statement stmt=conn.createStatement();   

//第四步，执行SQL语句  
stmt.executeUpdate("SQL语句"); 

//或者查询记录  
ResultSet rs = stmt.executeQuery("查询记录的SQL语句");  

//第五步，对查询的结果进行处理  
while(rs.next()){  
//操作  
}  

//第六步，关闭连接  
rs.close();  
stmt.close();  
conn.close();

So now we will use a demo, and set it up step by step.

Config

Note: The configuration below is based on Spring Boot Hello World Demo with web starter only.

Initialize DB in MySQL

DROP DATABASE demo_springboot;

CREATE DATABASE demo_springboot;

USE demo_springboot;

DROP TABLE IF EXISTS user;

CREATE TABLE user (
    id 	 INT(11)	NOT NULL 	AUTO_INCREMENT,
    name	 VARCHAR(100)	NOT NULL,
    password VARCHAR(100)	NOT NULL,
  	PRIMARY KEY (id)
) 
ENGINE=INNODB 
AUTO_INCREMENT=1 
DEFAULT CHARSET=utf8;

Connect Spring Boot to MySQL

Add dependencies in pom.xml:

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

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

Add Configurations to resources/application.properties (use cj.jdbc):

# 数据源基本配置
spring.datasource.url=jdbc:mysql://localhost:3306/demo_springboot?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=

Check the following dependencies in pom.xml:

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

    <exclusions>
        <exclusion>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </exclusion>
    </exclusions>
</dependency>

<dependency>
    <groupId>org.junit.jupiter</groupId>
    <artifactId>junit-jupiter-api</artifactId>
    <version>5.3.2</version>
    <scope>test</scope>
</dependency>

<dependency>
    <groupId>org.junit.jupiter</groupId>
    <artifactId>junit-jupiter-engine</artifactId>
    <version>5.3.2</version>
    <scope>test</scope>
</dependency>

And the following plugins:

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>

        <plugin>
            <artifactId>maven-surefire-plugin</artifactId>
            <version>2.22.0</version>
        </plugin>
    </plugins>
</build>

Test Connection (DemoApplicationTests under test folder):

package merikanto.demo;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;


@SpringBootTest
class DemoApplicationTests {

    @Autowired
    private DataSource dataSource;

    @Test
    public void datasourceTest() throws SQLException {
        // 获取数据库连接对象
        Connection connection = dataSource.getConnection();
        // 判断连接对象是否为空
        System.out.println(connection != null);
        connection.close();
    }
}

Run Maven with the test class, and the connection is successful if it returns true.

MySQL Operations via Spring Boot

Add JdbcController class to main. Then we will do Create and Read from CRUD.

package merikanto.demo.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

@RestController
public class JdbcController {

    // 注入 jdbcTemplate
    @Autowired
    JdbcTemplate jdbcTemplate;

    // 查询 user 表中的所有记录
    @GetMapping("/users/queryAll")
    public List<Map<String, Object>> queryAll() {
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from user");
        return list;
    }

    // 向 user 表中新增一条记录
    @GetMapping("/users/insert")
    public Object insert(String name, String password) {
        if (StringUtils.isEmpty(name) || StringUtils.isEmpty(password)) {
            return false;
        }
        jdbcTemplate.execute("insert into user(`name`,`password`) value (\"" + name + "\",\"" + password + "\")");
        return true;
    }
}

Then we try it in the browser, we shall see that the newly added records are fetched via queryAll.

/users/insert?name=merikanto&password=123

/users/queryAll

And that is the basics for Spring Boot’s Database connection.

P. S. Hello Controller

package merikanto.demo;

import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.bind.annotation.RequestMapping;

@RestController
public class HelloController {

    @RequestMapping("/")
    public String index() {
        return "Hello World!";
    }
}