Using JDBC with Spring Boot: Configuration, Auto‑Configuration, JdbcTemplate, Swagger Integration, and Common Troubleshooting

JDBC Overview

The JDBC API is a Java standard for connecting to relational databases and executing SQL statements.

1. Using JDBC in Spring Boot

Add the required Maven dependencies for Spring Boot JDBC starter, MySQL driver, and Spring Web:

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

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

Configure the datasource in src/main/resources/application.yml :

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/study-spring-boot?serverTimezone=UTC&useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8
    driverClassName: com.mysql.cj.jdbc.Driver

Note that com.mysq.jdbc.Driver is deprecated; use com.mysql.cj.jdbc.Driver instead.

Test the datasource with a simple Spring Boot test:

package com.jackson0714.springboot;

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 Springboot05DataJdbcApplicationTests {
    @Autowired
    DataSource dataSource; // auto‑configured datasource

    @Test
    void contextLoads() throws SQLException {
        System.out.println("DataSource: " + dataSource.getClass());
        Connection connection = dataSource.getConnection();
        System.out.println("Database connection: " + connection);
        connection.close();
    }
}

Typical output shows a HikariDataSource and a HikariProxyConnection wrapping the MySQL connection.

2. Auto‑Configuration Principle

Spring Boot’s auto‑configuration classes reside under org.springframework.boot.autoconfigure.jdbc . The DataSourceConfiguration class selects an appropriate datasource implementation (Tomcat pool, Hikari, DBCP2) based on classpath detection and the spring.datasource.type property.

Example of Tomcat pool configuration (simplified):

@Configuration(proxyBeanMethods = false)
@ConditionalOnClass(org.apache.tomcat.jdbc.pool.DataSource.class)
@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.tomcat.jdbc.pool.DataSource", matchIfMissing = true)
static class Tomcat { ... }

Generic datasource creation uses DataSourceBuilder and reflection to instantiate the type specified by spring.datasource.type :

@Configuration(proxyBeanMethods = false)
@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type")
static class Generic {
    @Bean
    DataSource dataSource(DataSourceProperties properties) {
        return properties.initializeDataSourceBuilder().build();
    }
}

The DataSourceInitializerInvoker runs schema-*.sql and data-*.sql scripts during startup, with three modes: NEVER , EMBEDDED (default), and ALWAYS . The isEnabled() method decides whether to execute scripts based on the selected mode and the presence of an embedded database.

Custom script locations can be defined in application.yml :

schema:
  - classpath:department.sql

3. JdbcTemplate

The class JdbcTemplateAutoConfiguration automatically creates a JdbcTemplate bean when a DataSource is present:

@Configuration(proxyBeanMethods = false)
@ConditionalOnClass({ DataSource.class, JdbcTemplate.class })
@ConditionalOnSingleCandidate(DataSource.class)
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties(JdbcProperties.class)
@Import({ JdbcTemplateConfiguration.class, NamedParameterJdbcTemplateConfiguration.class })
public class JdbcTemplateAutoConfiguration { }

4. Swagger for API Documentation

Add Swagger dependencies:

<dependency>
  <groupId>io.springfox</groupId>
  <artifactId>springfox-swagger2</artifactId>
  <version>2.9.2</version>
</dependency>
<dependency>
  <groupId>io.springfox</groupId>
  <artifactId>springfox-swagger-ui</artifactId>
  <version>2.9.2</version>
</dependency>

Create SwaggerConfig.java :

package com.jackson0714.springboot.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@Configuration
@EnableSwagger2
public class SwaggerConfig {
    @Bean
    public Docket createRestApi() {
        return new Docket(DocumentationType.SWAGGER_2)
                .apiInfo(apiInfo())
                .select()
                .apis(RequestHandlerSelectors.any())
                .paths(PathSelectors.any())
                .build();
    }

    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("Spring Boot API Documentation")
                .description("This is a RESTful API document of Spring Boot.")
                .version("1.0")
                .build();
    }
}

Access the UI at http://localhost:8081/swagger-ui.html .

5. CRUD Tests with JdbcTemplate

Creating a department:

@ApiOperation(value = "1.新增部门")
@ApiImplicitParams({
  @ApiImplicitParam(name = "name", value = "部门名称")
})
@PostMapping("/create")
public int createDepartment(@RequestParam String name) {
  String sql = String.format("insert into department(departmentName) value('%s')", name);
  int result = jdbcTemplate.update(sql);
  return result;
}

Querying all departments:

@ApiOperation(value = "2.查询所有部门")
@GetMapping("/getAllDepartment")
public List
> getAllDepartment() {
  return jdbcTemplate.queryForList("select * from department");
}

Querying by ID:

@ApiOperation(value = "3.根据id查询某个部门")
@ApiImplicitParams({
  @ApiImplicitParam(name = "id", value = "需要查询的部门id")
})
@GetMapping("/{id}")
public Map
getDepartmentById(@PathVariable Long id) {
  String sql = "select * from department where id = " + id;
  List
> list = jdbcTemplate.queryForList(sql);
  return list.get(0);
}

Updating a department name:

@ApiOperation(value = "根据id更新部门名称")
@ApiImplicitParams({
  @ApiImplicitParam(name = "id", value = "需要更新的部门id"),
  @ApiImplicitParam(name = "name", value = "需要更新的部门名称")
})
@PostMapping("/update")
public int updateDepartmentById(@RequestParam Long id, @RequestParam String name) {
  String sql = String.format("update department set departmentName = '%s' where id = %d", name, id);
  return jdbcTemplate.update(sql);
}

Deleting a department:

@ApiOperation(value = "根据id删除部门")
@ApiImplicitParams({
  @ApiImplicitParam(name = "id", value = "需要删除的部门id")
})
@PostMapping("/delete")
public int deleteDepartment(@RequestParam Long id) {
  String sql = String.format("delete from department where id = %d", id);
  return jdbcTemplate.update(sql);
}

6. Common Errors and Solutions

Problem 1: java.sql.SQLException: Host 'Siri' is not allowed to connect to this MySQL server . Fix by granting remote access:

use mysql;
select host from user;
update user set host = '%' where user = 'root';

Problem 2: Time‑zone error The server time zone value '…' is unrecognized . Resolve by adding serverTimezone=UTC to the JDBC URL.

These steps complete a functional Spring Boot application that uses JDBC, auto‑configuration, JdbcTemplate, and Swagger for API testing while handling typical connection issues.