Spring Boot - 使用 JdbcTemplate 判断一张 MySQL 表是否已经存在

环境

操作系统：

Windows 10 x64

集成开发环境：

Spring Tool Suite 4 
Version: 4.14.0.RELEASE
Build Id: 202203131612

MySQL 数据库版本信息：

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper

Server version:         5.7.30 MySQL Community Server (GPL)

使用 JdbcTemplate 判断一张 MySQL 表是否已经创建

数据库

MySQL 数据库中的表：

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_hello-account |
+-------------------------+
| user                    |
+-------------------------+
1 row in set (0.00 sec)

使用 LIKE 匹配一张存在或不存在的表，返回：

mysql> SHOW TABLES LIKE 'user';
+--------------------------------+
| Tables_in_hello-account (user) |
+--------------------------------+
| user                           |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES LIKE 'persistent_logins';
Empty set (0.00 sec)

新建项目

新建 Spring Starter Project，填写项目信息：

勾选 JDBC API、MySQL Driver 等依赖：

项目结构

配置

项目创建完成，pom.xml 配置文件：

<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.6</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>
    
    <groupId>com.mk</groupId>
    <artifactId>Spring-Boot-Has-Table-Created</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>Spring-Boot-Has-Table-Created</name>
    <description>Has the table of MySQL been created?</description>
    
    <properties>
        <java.version>1.8</java.version>
    </properties>
    
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

修改 application.yml 配置文件，指定数据源等参数：

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://192.168.88.158:3307/hello-account?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=true
    username: root
    password: 123456

编码 & 测试

在测试类中，使用 SHOW TABLES SQL 语句查询表名列表：

package com.mk;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

@SpringBootTest
class SpringBootHasTableCreatedApplicationTests {

    @Test
    void contextLoads() {
    }

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void created() {
        String sql = "SHOW TABLES"; // https://dev.mysql.com/doc/refman/5.7/en/show-tables.html

        List<String> tableNameList = jdbcTemplate.query(sql, null, null, new ResultSetExtractor<List<String>>() {
            @Override
            public List<String> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<String> tableNameList = new ArrayList<>();

                while (rs.next()) {
//                    String tableName = rs.getString(1);
                    String tableName = rs.getString("Tables_in_hello-account");

                    tableNameList.add(tableName);
                }

                return tableNameList;
            }
        });
        
        System.out.println(tableNameList);
    }
}

控制台输出：

[user]

稍微修改测试类，使用 SHOW TABLES LIKE 'user' SQL 语句查询指定表名，如果该表存在，返回 true，否则返回 false：

package com.mk;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

@SpringBootTest
class SpringBootHasTableCreatedApplicationTests {

    @Test
    void contextLoads() {
    }

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void created() {
//        String sql = "SHOW TABLES"; // https://dev.mysql.com/doc/refman/5.7/en/show-tables.html
//
//        List<String> tableNameList = jdbcTemplate.query(sql, null, null, new ResultSetExtractor<List<String>>() {
//            @Override
//            public List<String> extractData(ResultSet rs) throws SQLException, DataAccessException {
//                List<String> tableNameList = new ArrayList<>();
//
//                while (rs.next()) {
                    String tableName = rs.getString(1);
//                    String tableName = rs.getString("Tables_in_hello-account");
//
//                    tableNameList.add(tableName);
//                }
//
//                return tableNameList;
//            }
//        });
//        
//        System.out.println(tableNameList);
        
        String sql = "SHOW TABLES LIKE 'user'";

        Boolean created = jdbcTemplate.query(sql, null, null, new ResultSetExtractor<Boolean>() {
            @Override
            public Boolean extractData(ResultSet rs) throws SQLException, DataAccessException {
                // true if the new current row is valid; false if there are no more rows.
                return rs.next();
            }
        });

        System.out.println(created);
    }
}

控制台输出：

true

参考

MySQL 5.7 Reference Manual / … / SHOW TABLES Statement