Using JDBC in Spring Boot: Dependency Setup, DataSource Configuration, Auto‑Configuration, JdbcTemplate, Swagger Integration, and Common Troubleshooting

1. What is JDBC? JDBC (Java Database Connectivity) is a Java API that enables connecting to a database and executing SQL statements.

2. Adding JDBC dependencies in a Spring Boot project

<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>

3. Configuring the datasource

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: com.mysq.jdbc.Driver is deprecated; use com.mysql.cj.jdbc.Driver instead.

4. Verifying the datasource

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 a MySQL connection.

5. Spring Boot auto‑configuration principle

The auto‑configuration classes reside in org.springframework.boot.autoconfigure.jdbc . DataSourceConfiguration decides which datasource implementation to create (Tomcat, Hikari, DBCP2, or a custom one) based on classpath presence and spring.datasource.type property.

Key snippets:

@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 { ... }

Similar conditional logic exists for Hikari and generic datasource creation.

6. JdbcTemplate auto‑configuration

@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 { }

This class automatically registers a JdbcTemplate bean for convenient database operations.

7. Adding Swagger for API testing

io.springfox
springfox-swagger2
2.9.2
io.springfox
springfox-swagger-ui
2.9.2

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 接口文档")
                .description("This is a restful api document of Spring Boot.")
                .version("1.0")
                .build();
    }
}

Swagger UI can be accessed at http://localhost:8081/swagger-ui.html .

8. CRUD API implementation using JdbcTemplate

@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;
}

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

@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);
}

@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);
}

@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);
}

Each endpoint can be tested via Swagger UI, and screenshots in the original article show successful operations.

9. Common errors and solutions

Problem 1: java.sql.SQLException: Host 'Siri' is not allowed to connect to this MySQL server

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

After executing the statements, the user can connect from any host.

Problem 2: Time‑zone error: The server time zone value '…' is unrecognized

Solution: add serverTimezone=UTC to the JDBC URL in application.yml .

Overall, the article provides a step‑by‑step guide to set up JDBC in Spring Boot, use JdbcTemplate for database CRUD, expose the APIs with Swagger, and troubleshoot typical connection issues.