springboot设置自动建表mysql springboot动态建表

转载

墨染心语 2024-03-04 09:54:52

文章标签 spring bc maven 文章分类 MySQL 数据库

前言

一、导入相关的Maven依赖

二、编写数据源的配置文件

三、编写Swagger2的配置文件

四、编写VO类

五、编写Controller层

六、编写Service层

七、开始测试

总结

前言

本次实践，采用了SpringBoot 2.1.4(JDK8) + maven 3.6.3 + JdbcTemplate + Oracle19c + swagger2（1.8.1版本的UI）

其中 maven3.6.3 充当依赖管理；Oracle19C 作为数据库载体； swagger2充当前端，进行接口测试；

主角是SpringBoot + JdbcTemplate

新建SpringBoot项目、搭建Oracle数据库、配置相关开发环境之类的操作，不在本文的实践范围内，所以就不说了。以下开始正文：

一、导入相关的Maven依赖

<?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.1.4.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.alltobenice</groupId>
    <artifactId>personalproject1</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>personalproject1</name>
    <description>Personal_Program_Exercise</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>


    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.1.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--        Oracle依赖-->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.4</version>
        </dependency>

        <!--        lombok依赖-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
            <version>1.18.12</version>
        </dependency>

        <!-- swagger2 -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.8.0</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.8.0</version>
        </dependency>
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>swagger-bootstrap-ui</artifactId>
            <version>1.8.1</version>
        </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>

二、编写数据源的配置文件

在建立SpringBoot项目之后，src/main/resources目录下会有一个空白的文件：application.properties；把数据源的相关信息往这里填写就可以了。

有些大佬可以调成yml格式的也是Ok，一步一步来。

本次实践，我就用properties的格式了。

server.port=9527 #网页访问这个服务要访问的端口

spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver #数据库驱动
spring.datasource.url=jdbc:oracle:thin:@localhost:15211:localOracle #数据库路径
spring.datasource.username=yourUserName    #连接数据库要用到的用户名跟密码
spring.datasource.password=yourPassWord

三、编写Swagger2的配置文件

在src/main/java目录下，建里config包，专门放置配置文件，本次实践较为简单，就建立一个Swagger2的配置文件即可；

springboot设置自动建表mysql springboot动态建表_bc

代码如下：

package com.alltobenice.personalproject1.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
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;

/**
 * @ClassName: SwaggerConfig
 * @Description: Swagger配置类
 * @Author: 
 * @Since: 2021/6/15 17:23
 */

@Configuration
@EnableSwagger2
@ComponentScan(basePackages = "com.alltobenice.personalproject1.controller")
public class SwaggerConfig {
    @Bean
    public Docket creatRestApi(){
        return new Docket(DocumentationType.SWAGGER_2).apiInfo(apiInfo())
                .select()
                .apis(RequestHandlerSelectors.basePackage("com.alltobenice.personalproject1.controller"))
//                .apis(RequestHandlerSelectors.withClassAnnotation(Api.class))
                .paths(PathSelectors.any())
                .build();
    }

    private ApiInfo apiInfo(){
        return new ApiInfoBuilder()
                .title("接口列表")
                .version("v1.0")
//                .termsOfServiceUrl("http://localhost:9527/demo/swagger-ui.html")
                .description("接口测试")
                .build();
    }
}

四、编写VO类

用VO类来封装从前端传来的参数

springboot设置自动建表mysql springboot动态建表_bc_02

代码如下：

package com.alltobenice.personalproject1.vo;

import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

/**
 * @ClassName: TableInfo
 * @Description: 封装从前端传来，用于建表的信息
 * @Author: 
 * @Since: 2021/6/15 11:56
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class TableInfo {
    @ApiModelProperty("表名称")
    private String tableName;

    @ApiModelProperty("表编码")
    private String tableEncode;

    @ApiModelProperty("表备注")
    private String tableComment;

    @ApiModelProperty("字段信息")
    private List<ColumnInfo> columnInfo;
}

package com.alltobenice.personalproject1.vo;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @ClassName: ColumnInfo
 * @Description: 封装从前端传来，用于建表的字段信息
 * @Author: 
 * @Since: 2021/6/15 12:33
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel(value = "ColumnInfo")
public class ColumnInfo{

    @ApiModelProperty("字段名")
    private String columnName;

    @ApiModelProperty("字段编码")
    private String columnEncode;

    @ApiModelProperty("字段类型")
    private String columnType;

    @ApiModelProperty("字段备注")
    private String columnComment;

    @ApiModelProperty("字段大小")
    private Integer columnSize;

    @ApiModelProperty("是否可以为空")
    private Boolean isNotEmpty;

    @ApiModelProperty("是否是主键")
    private Boolean isPrimary;
}

五、编写Controller层

写完VO层以后，就可以开始Controller层的编写啦。

springboot设置自动建表mysql springboot动态建表_bc_03

代码如下：RestResult作为一个【统一结果返回】类，我放到base包下，不是这次实践的重点，所以就不展示了。统一结果集的返回类，网上一搜一大把，我就不丢人显眼了。

package com.alltobenice.personalproject1.controller;

import com.alltobenice.personalproject1.base.RestResult;
import com.alltobenice.personalproject1.vo.TableInfo;
import com.alltobenice.personalproject1.service.TableManageService;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

/**
 * @ClassName: TableManageController
 * @Description: 数据表管理控制器
 * @Author: 
 * @Since: 2021/6/15 13:46
 */

@RestController
@RequestMapping("/TableManage")
public class TableManageController {
    @Autowired
    TableManageService tableManageService;

    @PostMapping("/createTable")
    @ApiOperation(value = "建表语句", response = RestResult.class)
    public RestResult createTable(@RequestBody TableInfo tableInfo){
        tableManageService.creatTable(tableInfo);
        return RestResult.success();
    }

}

六、编写Service层

springboot设置自动建表mysql springboot动态建表_maven_04

代码如下：

package com.alltobenice.personalproject1.service;

import com.alltobenice.personalproject1.vo.TableInfo;


/**
 * @InterfaceName: TableManageService
 * @Description: 表管理服务层
 * @Author: 
 * @Since: 2021/6/15 17:38
 */

public interface TableManageService {

    void creatTable(TableInfo tableInfo);

}

取字段信息的时候遇到了一个小问题：如果我写入的是【驼峰式的命名规则】，那么就不能直接通过字符串拼接，写入数据库，因为Oracle的数据库都是【大写带下划线的命名规则】，所以在拼接字符串的时候，写了个命名规则转换的工具类，做调整；

package com.alltobenice.personalproject1.service.impl;

import com.alltobenice.personalproject1.vo.ColumnInfo;
import com.alltobenice.personalproject1.vo.TableInfo;
import com.alltobenice.personalproject1.service.TableManageService;
import com.alltobenice.personalproject1.util.GetFieldStringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @ClassName: TableManageServiceImpl
 * @Description: 表管理服务层
 * @Author:
 * @Since: 2021/6/15 17:55
 */
@Service
public class TableManageServiceImpl implements TableManageService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    //建表
    @Override
    public void creatTable(TableInfo tableInfo) {

        //建立一个动态的字符串，用于拼接sql语句；
        StringBuffer stringSql = new StringBuffer();

        //取出建表需要的表名
        String tableEncode = tableInfo.getTableEncode();

        //拼接
        stringSql.append("create table " + tableEncode + "(\r\n");

        //取出字段，以及字段的信息
        List<ColumnInfo> columns = tableInfo.getColumnInfo();
        String fieldSQL = GetFieldStringUtil.getFieldSQL(columns);
        stringSql.append(fieldSQL + "\r\n)");
        String stringSQL = stringSql.toString();
        System.out.println(stringSQL);

        try{
            jdbcTemplate.execute(stringSQL);
        } catch (Exception e){
            e.printStackTrace();
        }


    }
}

抽取字段信息的工具类

package com.alltobenice.personalproject1.util;

import com.alltobenice.personalproject1.vo.ColumnInfo;
import org.springframework.beans.BeanUtils;

import java.util.List;

/**
 * @ClassName: GetFieldStringUtil
 * @Description: 获取字段信息工具类
 * @Author: 
 * @Since: 2021/6/17 10:00
 */
public class GetFieldStringUtil {
    public static String getFieldSQL(List<ColumnInfo> columnInfos){
        StringBuffer sql = new StringBuffer();
        String isPrimary = new String(); //flag的作用，判定是否要添加主键语句
        int i = 0;
        long count = columnInfos.size();//用于判定 字段数组是否是最后一个
        for(ColumnInfo column : columnInfos){
            ColumnInfo columnTarget = new ColumnInfo();
            BeanUtils.copyProperties(column,columnTarget);
            //取出字段信息
            String columnName = columnTarget.getColumnName();
            String columnEncode = columnTarget.getColumnEncode();  //转格式化，驼峰命名规则->下划线式的命名规则
            String columnComment = columnTarget.getColumnComment();
            String columnType = columnTarget.getColumnType();
            Integer columnSize = columnTarget.getColumnSize();
            Boolean columnIsNotEmpty = columnTarget.getIsNotEmpty();
            Boolean columnIsPrimary = columnTarget.getIsPrimary();
            sql.append(NameTransformUtil.toUnderScoreName(columnEncode))
                    .append(" ")
                    .append(columnType.toUpperCase())
                    .append("(" + columnSize.toString() + ")");
            //如果非空 True为非空，False为空
            if(columnIsNotEmpty){
                sql.append(" NOT NULL");
            }
            //如果是主键 True为是，False为否
            if(columnIsPrimary ){
                isPrimary = "PRIMARY KEY (" + NameTransformUtil.toUnderScoreName(columnEncode) + ")" ;
            }
            //逗号分行
            if( i == count - 1 && isPrimary.isEmpty()){
                sql.append("\r\n");
            }else{
                sql.append(",\r\n");
            }
            i++;

        }
        if("".equals(isPrimary)){
            return sql.toString();
        }else{
            sql.append(isPrimary);
            return sql.toString();
        }
    }
}