Spring Boot中怎樣使用JDBC

本篇文章為大家展示了Spring Boot中怎樣使用JDBC，內(nèi)容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

一、JDBC是什么？

JDBC API 屬于Java APIJDBC用于以下幾種功能：連接到數(shù)據(jù)庫、執(zhí)行SQL語句

二、Spring Boot中如何使用JDBC

2.1 創(chuàng)建 Spring Boot Project 時引入 JDBC API 依賴和 MySQL Driver依賴，以及Spring Web依賴（測試時用到）

可以在POM中找到引入的JDBC依賴和mysql依賴：
JDBC 依賴：

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

MySql 驅(qū)動依賴：

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

2.2 配置數(shù)據(jù)庫連接

新增配置文件：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

注意：com.mysq.jdbc.Driver 被廢棄了，需要使用com.mysql.cj.jdbc.Driver

2.3 查看使用的數(shù)據(jù)源和數(shù)據(jù)庫連接

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; //自動配置數(shù)據(jù)源，使用yml配置

    @Test
    void contextLoads() throws SQLException {
        System.out.println("數(shù)據(jù)源：" + dataSource.getClass());

        Connection connection = dataSource.getConnection();
        System.out.println("數(shù)據(jù)庫連接：" + connection);
        connection.close();
    }

}

默認數(shù)據(jù)源：class com.zaxxer.hikari.HikariDataSource

數(shù)據(jù)庫連接：HikariProxyConnection@1335157064 wrapping com.mysql.cj.jdbc.ConnectionImpl@7ff8a9dc

三、自動配置原理

自動配置文件路徑：org.springframework.boot.autoconfigure.jdbc

DataSourceConfiguration用來自動導(dǎo)入數(shù)據(jù)源（根據(jù)各種判斷）

/**
     * Tomcat Pool DataSource configuration.
     */
    @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 {

        @Bean
        @ConfigurationProperties(prefix = "spring.datasource.tomcat")

3.1 自動選擇數(shù)據(jù)源

如果導(dǎo)入了org.apache.tomcat.jdbc.pool.DataSource數(shù)據(jù)源，并且配置的spring.datasource.type配置的是org.apache.tomcat.jdbc.pool.DataSource，或沒配置type也使用tomcat數(shù)據(jù)源

3.2 HikariDataSource數(shù)據(jù)源也類似這樣判斷。

3.3 默認使用tomcat數(shù)據(jù)源

3.4 默認支持以下數(shù)據(jù)源

org.apache.tomcat.jdbc.pool、HikariDataSource、org.apache.commons.dbcp2

3.5 支持自定義數(shù)據(jù)源

使用DataSourceBuilder創(chuàng)建數(shù)據(jù)源，利用反射創(chuàng)建響應(yīng)type的數(shù)據(jù)源，并且綁定相關(guān)屬性

    /**
     * Generic DataSource configuration.
     */
    @Configuration(proxyBeanMethods = false)
    @ConditionalOnMissingBean(DataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type")
    static class Generic {

        @Bean
        DataSource dataSource(DataSourceProperties properties) {
          //使用DataSourceBuilder創(chuàng)建數(shù)據(jù)源，利用反射創(chuàng)建響應(yīng)type的數(shù)據(jù)源，并且綁定相關(guān)屬性
            return properties.initializeDataSourceBuilder().build();
        }

    }

3.6 DataSourceInitializerInvoker 運行腳本

/**
 * Bean to handle {@link DataSource} initialization by running {@literal schema-*.sql} on
 * {@link InitializingBean#afterPropertiesSet()} and {@literal data-*.sql} SQL scripts on
 * a {@link DataSourceSchemaCreatedEvent}.
 *
 * @author Stephane Nicoll
 * @see DataSourceAutoConfiguration
 */
class DataSourceInitializerInvoker implements ApplicationListener<DataSourceSchemaCreatedEvent>, InitializingBean {

createSchema() 創(chuàng)建表 （文件名規(guī)則 schema-*.sql）
initSchema() 執(zhí)行數(shù)據(jù)腳本 （文件名規(guī)則 data-*.sql）

getScripts() 來獲取需要執(zhí)行的腳本

private List<Resource> getScripts(String propertyName, List<String> resources, String fallback) {
  if (resources != null) {
    return getResources(propertyName, resources, true);
  }
  String platform = this.properties.getPlatform();
  List<String> fallbackResources = new ArrayList<>();
  fallbackResources.add("classpath*:" + fallback + "-" + platform + ".sql");
  fallbackResources.add("classpath*:" + fallback + ".sql");
  return getResources(propertyName, fallbackResources, false);
}

1) fallback = "schema", platform="all",會自動執(zhí)行根目錄下：schema-all.sql 或schema.sql 文件

2) fallback = "data", platform="all",會自動執(zhí)行根目錄下：data-all.sql 或data.sql 文件

isEnabled() 方法判斷是否開啟了自動執(zhí)行腳本

有三種模式：NEVER，EMBEDDED（默認），Always

疑問：用EMBEDDED模式返回false，開關(guān)關(guān)閉，不執(zhí)行腳本，這是為啥呢？

用Always模式則每次啟動spring boot重復(fù)執(zhí)行腳本（創(chuàng)建表腳本都是先判斷有沒有表，有則刪除后重建）

private boolean isEnabled() {
  DataSourceInitializationMode mode = this.properties.getInitializationMode();
  if (mode == DataSourceInitializationMode.NEVER) {
    return false;
  }
  if (mode == DataSourceInitializationMode.EMBEDDED && !isEmbedded()) {
    return false;
  }
  return true;
}

3.7 通過配置文件指定需要執(zhí)行腳本

schema:
  - classpath:department.sql

創(chuàng)建出的 department 表

四、JdbcTemplate

JdbcTemplateAutoConfiguration.java 文件 自動注入了JdbcTemplate。（JdbcTemplate用來操作數(shù)據(jù)庫）

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

}

五、配置Swagger用來測試

5.1 pom.xml文件 添加swagger依賴

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

5.2 添加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("玩轉(zhuǎn)Spring Boot 接口文檔")
                .description("This is a restful api document of Spring Boot.")
                .version("1.0")
                .build();
    }

}

5.3 訪問Swagger文檔

http://localhost:8081/swagger-ui.html

回到頂部

六、測試

6.1 新增部門

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

表記錄

6.2 查詢所有部門

@ApiOperation(value = "2.查詢所有部門")
@GetMapping("/getAllDepartment")
public List<Map<String, Object>> getAllDepartment() {
  List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from department");
  return list;
}

6.3 根據(jù)id查詢某個部門

@ApiOperation(value = "3.根據(jù)id查詢某個部門")
@ApiImplicitParams({
  @ApiImplicitParam(name = "id", value = "需要查詢的部門id")
})
@GetMapping("/{id}")
public Map<String, Object> getDepartmentById(@PathVariable Long id) {
  String sql = "select * from department where id = " + id;
  List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
  return list.get(0);
}

6.4 根據(jù)id更新部門名稱

@ApiOperation(value = "根據(jù)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);
  int result = jdbcTemplate.update(sql);
  return result;
}

6.5 根據(jù)id刪除部門

@ApiOperation(value = "根據(jù)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);
  int result = jdbcTemplate.update(sql);
  return result;
}

七、報錯和解決方案：

7.1 問題1

java.sql.SQLException:null, message from server: "Host 'Siri' is not allowed to connect to this MySQL server"

解決方案：
執(zhí)行命令：

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

執(zhí)行結(jié)果：

Query OK, 1 row affected

如下圖所示：

7.2 問題2

Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value '?й???????' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support.

解決方案：
配置spring.datasource.url 時，增加參數(shù)：serverTimezone=UTC

上述內(nèi)容就是Spring Boot中怎樣使用JDBC，你們學(xué)到知識或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識儲備，歡迎關(guān)注億速云行業(yè)資訊頻道。