Springboot实现：通过excel文档自动在oracle数据库中建表 2021

Springboot实现：通过excel文档自动在oracle数据库中建表 2021

实现功能

自动建表 只要将exce文档l传入程序 他就会自动执行，自动在oracle数据库中建表

事情起因：

昨天，项目经理让我建表，一般一两张表，建就完了，不在话下，随便几下就建好了，可偏偏他喵的一堆表，一堆字段，我的妈耶


我当时看完人傻了，我想了又想，这玩意真完完整整建下来，一天就废了，而且可能啥也学不到，一天下来不是在建表的路上就是在excel里复制然后粘贴的途中，所以我毅然决然的决定他喵的写个小程序吧 虽然我还是个菜鸡！

这个可能还不算程序，就当是一个小功能吧！

功能展示

先看下效果图
excel文档：（格式是固定的，但也可以自行更改程序代码，小编时间原因格式没有设置的很好，表名及表名注释需写在第二行）

然后将准备好的excel文档通过postman传入程序：

程序执行完成后，数据库中的样子（主键或联合主键 字段类型，非空约束及注释都能实现导入，外键暂不支持，可以自己设置）

主键：

实现流程

导入依赖
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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.table</groupId>
    <artifactId>create</artifactId>
    <version>0.0.1-SNAPSHOT</version>


    <properties>
        <lombok.version>1.18.10</lombok.version>
        <mybatis-spring-boot-starter.version>2.1.2</mybatis-spring-boot-starter.version>
        <mysql-connector.version>5.1.47</mysql-connector.version>
        <commons-lang3.version>3.9</commons-lang3.version>
        <pagehelper-spring-boot-starter.version>1.2.13</pagehelper-spring-boot-starter.version>
        <mail.version>1.4.7</mail.version>

        <pulsar.version>2.6.0</pulsar.version>

    </properties>

    <!--增加springboot parent依赖-->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.6.RELEASE</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--aop配置-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <!--jsr303验证-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>


        <!--mybatis 配置-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis-spring-boot-starter.version}</version>
        </dependency>
        <!--        <dependency>-->
        <!--            <groupId>mysql</groupId>-->
        <!--            <artifactId>mysql-connector-java</artifactId>-->
        <!--            <version>${mysql-connector.version}</version>-->
        <!--        </dependency>-->
        <!--Oracle-->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.4</version>
        </dependency>


        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>${pagehelper-spring-boot-starter.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>${commons-lang3.version}</version>
        </dependency>
        <!--redis 依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>
        <!--test测试依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
        </dependency>
        <dependency>
            <groupId>javax.mail</groupId>
            <artifactId>mail</artifactId>
            <version>${mail.version}</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>

        <!-- 这里使用 swagger-bootstrap-ui 替代了原有的ui~ -->
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>swagger-bootstrap-ui</artifactId>
            <version>1.9.6</version>
        </dependency>


        <dependency>
            <groupId>org.bouncycastle</groupId>
            <artifactId>bcprov-jdk15on</artifactId>
            <version>1.56</version>
        </dependency>
        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
            <version>1.11</version>
        </dependency>

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.3.9</version>
        </dependency>


        <dependency>
            <!-- jsoup HTML parser library @ https://jsoup.org/ -->
            <groupId>org.jsoup</groupId>
            <artifactId>jsoup</artifactId>
            <version>1.13.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <layout>ZIP</layout>
                    <!--是否包含lib-->
                    <!--如果不包含再启动命令中添加加载外部lib 的参数
                    -spring.config.location=/var/application.properties
                    -Dloader.path=/var/lib-->

                    <!--                    <includes>-->
                    <!--                        <include>-->
                    <!--                            <groupId>nothing</groupId>-->
                    <!--                            <artifactId>nothing</artifactId>-->
                    <!--                        </include>-->
                    <!--                    </includes>-->
                </configuration>
                <executions>
                    <execution>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

Controller层：

package com.table.create.Controller;


import com.table.create.Service.tableService;
import com.table.create.util.DataUtilResult;
import com.table.create.util.ReadExcel;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Ldh
 * @create 2021-10-08 15:00
 */
@RestController
@RequestMapping("/admin")
public class ImportExcel {

    Logger logger = LoggerFactory.getLogger(ImportExcel.class);

    @Autowired
    public tableService service;

    @PostMapping("/import")
    public DataUtilResult addAccountInfo(@RequestParam MultipartFile file,HttpServletRequest request) {
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
        // IO流读取文件
        InputStream in = null;
        try {
            //读取上传数据，获取文件数据输入流
            in = file.getInputStream();
            String fileName = file.getOriginalFilename();
            if (fileName == null || StringUtils.isBlank(fileName.trim())){
                fileName = "";
            }else if(fileName.contains(".")){
                fileName = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
            }else{
                fileName = "";
            }
            String postfix = fileName;
            list = new ReadExcel().exportListFromExcel(in, postfix);
           return service.sqlContect(list);
        } catch (Exception e) {
            logger.error("文件上传失败！", e);
            return DataUtilResult.getFailureResult("文件上传失败！", e.getMessage());
        }
    }

}

service层：

package com.table.create.Service.impl;

import com.table.create.Service.tableService;
import com.table.create.mapper.TableMapper;
import com.table.create.util.DataUtilResult;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

/**
 * @author Ldh
 * @create 2021-10-08 16:01
 */
@Service
public class tableImpl implements tableService {


    @Autowired
    TableMapper mapper;

    @Override
    public DataUtilResult sqlContect(List<ArrayList<String>> excelList) {
        try {
            if (excelList.isEmpty()) {
                return DataUtilResult.getFailureResult("list为空", null);
            }
            String sqlAll = new String();
            String tableName = new String();
            String tableCName = new String();
            List<String> pk = new ArrayList<>();
            List<String> sqlName = new ArrayList<>();
            for (ArrayList<String> list : excelList) {
                String r1 = list.get(0);
                String r2 = list.get(1);
                String r3 = list.get(2);
                String r4 = list.get(3);
                String r5 = list.get(4);
                String r6 = list.get(5);
                if (StringUtils.isBlank(r3) && StringUtils.isBlank(r5)) {
                    tableName = r1;
                    tableCName = r2;
                    continue;
                }
                if ("N".equals(r3.trim()) && StringUtils.isBlank(r4)) {
                    r3 = "not null";
                } else {
                    r3 = "";
                }
                if ("Y".equals(r4.trim())) {
                    pk.add(r1);
                    r4 = "";
                }

                String sql = r1 + " " + r2 + " " + r3 + " " + r4 + ",";
                sqlAll += sql;
                String sql1 = r1 + " IS '" + r5 + r6 + "'";
                sqlName.add(sql1);
            }
            String pk1 = new String();
            if(!pk.isEmpty()){
                for(String temp : pk){
                    pk1 +=temp+",";
                }
            }else{
                return DataUtilResult.getFailureResult("主人,表没有主键哦！",tableName);
            }
            pk1 = pk1.substring(0, pk1.length() - 1);
            sqlAll = sqlAll + "CONSTRAINT "+tableName+"_pk"+" PRIMARY KEY ("+pk1+")";
            sqlAll = "create table " + tableName + " (" + sqlAll + ")";
            for (int i = 0; i < sqlName.size(); i++) {
                if (sqlName.get(i).contains("\n")) {
                    sqlName.set(i, sqlName.get(i).replace("\n", ""));
                }
                sqlName.set(i, "COMMENT ON COLUMN LCRMTEST." + tableName + "." + sqlName.get(i));
            }
            mapper.CreateTable(sqlAll);
            for (String temp : sqlName) {
                mapper.UpdateTable(temp);
            }
            String cName = "COMMENT ON TABLE LCRMTEST." + tableName + " IS " + "'" + tableCName + "'";
            mapper.UpdateTable(cName);

            return DataUtilResult.getSuccessResult("主人,表创建成功",tableName);
        } catch (Exception e) {
            return DataUtilResult.getFailureResult("失败", e.getMessage());
        }
    }
}

util层

ReadExcel .java

package com.table.create.util;


import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;


public class ReadExcel {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";

    // 判断Excel的版本,获取Workbook
    public static Workbook getWorkbok(InputStream in, String str) throws IOException {
        Workbook wb = null;
        if (EXCEL_XLS.equals(str)) { // Excel 2003
            wb = new HSSFWorkbook(in);
        } else if (EXCEL_XLSX.equals(str)) { // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }

    // 判断文件是否是excel
    public static void checkExcelVaild(File file) throws Exception {
        if (!file.exists()) {
            throw new Exception("文件不存在");
        }
        if (!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
            throw new Exception("文件不是Excel");
        }
    }

    // 由指定的Sheet导出至List
    public List<ArrayList<String>> exportListFromExcel(InputStream is, String str) throws IOException {

        List<ArrayList<String>> rowlist = new ArrayList<ArrayList<String>>();
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        // 同时支持Excel 2003、2007

        Workbook workbook = getWorkbok(is, str); // 判断所传文件的格式

        int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
        for (int numSheet = 0; numSheet < sheetCount; numSheet++) {
            Sheet sheet = workbook.getSheetAt(numSheet);
            if (sheet == null) {
                continue;
            }

            // 为跳过第一行目录设置count
            int count = 0;
            int totalCells = 0;
            for (Row row : sheet) {
                Boolean flag = false;
                ArrayList<String> list = new ArrayList<String>();
                // 跳过第一行的目录
                if (count == 0) {
                    totalCells = row.getLastCellNum();
                    count++;
                    continue;
                }
                // 如果当前行没有数据，跳出循环
                for(int n=0;n<totalCells;n++){
                    if (null != row.getCell(n) && StringUtils.isNotBlank(row.getCell(n).toString())) {
                        flag=true;
                    }
                }
                if(!flag){
                    break;
                }
                String rowValue = "";
                for (int c=0;c<totalCells;c++) {
                    Cell cell = row.getCell(c);
                    if (cell == null) {
                        list.add("");
                        continue;
                    }
                    int cellType = cell.getCellType();
                    String cellValue = "";
                    switch (cellType) {
                        case Cell.CELL_TYPE_STRING: // 文本
                            cellValue = cell.getRichStringCellValue().getString() + "";
                            break;
                        case Cell.CELL_TYPE_NUMERIC: // 数字、日期
                            if (DateUtil.isCellDateFormatted(cell)) {
                                cellValue = fmt.format(cell.getDateCellValue()) + "";
                            } else {
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                cellValue = String.valueOf(cell.getRichStringCellValue().getString()) + "";
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN: // 布尔型
                            cellValue = String.valueOf(cell.getBooleanCellValue()) + "";
                            break;
                        case Cell.CELL_TYPE_BLANK: // 空白
                            cellValue = cell.getStringCellValue() + "";
                            break;
                        case Cell.CELL_TYPE_ERROR: // 错误
                            cellValue = "错误";
                            break;
                        case Cell.CELL_TYPE_FORMULA: // 公式
                            // 得到对应单元格的公式
                            // cellValue = cell.getCellFormula() + "#";
                            // 得到对应单元格的字符串
                            String s = cell.getCellFormula() + ""; // 公式
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            // String.valueOf(cell.getRichStringCellValue().getString()) + "";//内容
                            cellValue = s;

                            int index = cellValue.indexOf("(\"");
                            int end_index = cellValue.indexOf("\",");
                            cellValue = cellValue.substring(index + 2, end_index);// 获取文件网站信息
                            String cellvalue2 = String.valueOf(cell.getRichStringCellValue().getString());
                            list.add(cellvalue2);  //先单独增加内容信息
                            break;
                        default:
                            cellValue = "";
                    }
                    list.add(cellValue);//每一行的值得list
                }
                rowlist.add(list);//每一行的list
            }
        }
        return rowlist;
    }
}

DataUtilResult.ava

package com.table.create.util;

import java.io.Serializable;

public class DataUtilResult<T> implements Serializable {

    private int code;
    private String message;
    private T result;
    private T result2;

    private DataUtilResult() {}

    private DataUtilResult(int code, String message, T result) {
        this.code = code;
        this.message = message;
        this.result=result;
    }
    private DataUtilResult(int code, String message, T result,T result2) {
        this.code = code;
        this.message = message;
        this.result=result;
        this.result2=result2;
    }

    public int getCode() {
        return code;
    }

    public void setCode(int code) {
        this.code = code;
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public T getResult() {
        return result;
    }

    public void setResult(T result) {
        this.result = result;
    }

    public T getResult2() {
        return result2;
    }

    public void setResult2(T result) {
        this.result2 = result2;
    }

    /**
     * 调用默认成功
     */
    public static <T> DataUtilResult<T> getSuccessResult(T result){
        return new DataUtilResult<T>(0, "success",result);
    }

    public static <T> DataUtilResult<T> getSuccessResult(T result,T result2){
        return new DataUtilResult<T>(0, "success",result,result2);
    }
    /**
     * 业务数据不存在
     * @param message 错误提示文字
     * @param result 结果
     */
    public static <T> DataUtilResult<T> getServiceErrorResult(String message, T result){
        if(null== message || "".equals(message)) {
            message = "业务数据不存在！";
        }
        return new DataUtilResult<T>(-2, message,result);
    }

    /**
     * 无访问权限
     * @param message 错误提示文字
     * @param result 结果
     */
    public static <T> DataUtilResult<T> getWithoutAccess(String message, T result){
        if(null== message || "".equals(message)){
            message = "该用户不在白名单内，无法访问页面";
        }
        return new DataUtilResult<T>(400,message,result);
    }

    /**
     * 系统发生未处理异常
     * @param message 错误提示文字
     * @param result 结果
     */
    public static <T> DataUtilResult<T> getFailureResult(String message, T result){
        if(null== message || "".equals(message)) {
            message = "系统发生未处理异常！";
        }
        return new DataUtilResult<T>(-1, message,result);
    }

    /**
     * 自定义异常
     * @param <T>
     * @return
     */
    public static <T> DataUtilResult<T> getCodeResult(ResultMsgEnum msgEnum, T result){
        String message = msgEnum.getMessage();
        Integer code = msgEnum.getCode();
        if(null== message || "".equals(message)){
            message = "系统发生未处理异常！";
        }
        code = code==null? -100 :code;
        return new DataUtilResult<T>(code,message,result);
    }

}

mapper层

package com.table.create.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

/**
 * @author Ldh
 * com.table.create.mapper.TableMapper.CreateTable
 * @create 2021-10-08 19:37
 */
@Mapper
public interface TableMapper {


    /**
     * 创建表
     * @param
     * @return
     */
    int CreateTable(@Param("sql") String sql);

    /**
     * 更新表
     * @param
     * @return
     */
    int UpdateTable(@Param("sql") String sql);

    /**
     * 删除表
     * @param
     * @return
     */
    int DeleteTable(@Param("sql") String sql);


}

mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.table.create.mapper.TableMapper">

<update id="CreateTable" parameterType="java.lang.String">
  ${sql}
</update>

<update id="UpdateTable" parameterType="java.lang.String">
  ${sql}
</update>

<update id="DeleteTable" parameterType="java.lang.String">
  DROP TABLE IF EXISTS ${tableName}
</update>


</mapper>

做完感触：爽歪歪，哈哈哈，现在心情很巴适！！几十张表，写程序几小时，建表几分钟， 哈哈哈

如果小编写的有帮助到你，麻烦点个赞赞哦！哈哈