自动建表 只要将exce文档l传入程序 他就会自动执行，自动在oracle数据库中建表
事情起因：昨天，项目经理让我建表，一般一两张表，建就完了，不在话下，随便几下就建好了，可偏偏他喵的一堆表，一堆字段，我的妈耶
我当时看完人傻了，我想了又想，这玩意真完完整整建下来，一天就废了，而且可能啥也学不到，一天下来不是在建表的路上就是在excel里复制然后粘贴的途中，所以我毅然决然的决定他喵的写个小程序吧 虽然我还是个菜鸡！
这个可能还不算程序，就当是一个小功能吧！
功能展示先看下效果图
excel文档：（格式是固定的，但也可以自行更改程序代码，小编时间原因格式没有设置的很好，表名及表名注释需写在第二行）
然后将准备好的excel文档通过postman传入程序：
程序执行完成后，数据库中的样子（主键或联合主键 字段类型，非空约束及注释都能实现导入，外键暂不支持，可以自己设置）
主键：
导入依赖
pom.xml
4.0.0 com.table create 0.0.1-SNAPSHOT 1.18.10 2.1.2 5.1.47 3.9 1.2.13 1.4.7 2.6.0 org.springframework.boot spring-boot-starter-parent 2.2.6.RELEASE org.springframework.boot spring-boot-starter-web org.springframework.boot spring-boot-starter-aop org.springframework.boot spring-boot-starter-validation org.mybatis.spring.boot mybatis-spring-boot-starter ${mybatis-spring-boot-starter.version} com.oracle.database.jdbc ojdbc6 11.2.0.4 com.github.pagehelper pagehelper-spring-boot-starter ${pagehelper-spring-boot-starter.version} org.apache.commons commons-lang3 ${commons-lang3.version} org.springframework.boot spring-boot-starter-data-redis org.springframework.boot spring-boot-starter-test test org.junit.vintage junit-vintage-engine org.projectlombok lombok ${lombok.version} javax.mail ${mail.version} io.springfox springfox-swagger2 2.9.2 com.github.xiaoymin swagger-bootstrap-ui 1.9.6 org.bouncycastle bcprov-jdk15on 1.56 commons-codec commons-codec 1.11 cn.hutool hutool-all 5.3.9 org.jsoup jsoup 1.13.1 org.apache.poi poi 3.17 org.apache.poi poi-ooxml 3.17 org.springframework.boot spring-boot-maven-plugin ZIP repackage
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;
@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> list = new ArrayList>();
// 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;
@Service
public class tableImpl implements tableService {
@Autowired
TableMapper mapper;
@Override
public DataUtilResult sqlContect(List> excelList) {
try {
if (excelList.isEmpty()) {
return DataUtilResult.getFailureResult("list为空", null);
}
String sqlAll = new String();
String tableName = new String();
String tableCName = new String();
List pk = new ArrayList<>();
List sqlName = new ArrayList<>();
for (ArrayList 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> exportListFromExcel(InputStream is, String str) throws IOException {
List> rowlist = new ArrayList>();
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 list = new ArrayList();
// 跳过第一行的目录
if (count == 0) {
totalCells = row.getLastCellNum();
count++;
continue;
}
// 如果当前行没有数据，跳出循环
for(int n=0;n
DataUtilResult.ava
package com.table.create.util;
import java.io.Serializable;
public class DataUtilResult 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 DataUtilResult getSuccessResult(T result){
return new DataUtilResult(0, "success",result);
}
public static DataUtilResult getSuccessResult(T result,T result2){
return new DataUtilResult(0, "success",result,result2);
}
public static DataUtilResult getServiceErrorResult(String message, T result){
if(null== message || "".equals(message)) {
message = "业务数据不存在！";
}
return new DataUtilResult(-2, message,result);
}
public static DataUtilResult getWithoutAccess(String message, T result){
if(null== message || "".equals(message)){
message = "该用户不在白名单内，无法访问页面";
}
return new DataUtilResult(400,message,result);
}
public static DataUtilResult getFailureResult(String message, T result){
if(null== message || "".equals(message)) {
message = "系统发生未处理异常！";
}
return new DataUtilResult(-1, message,result);
}
public static DataUtilResult 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(code,message,result);
}
}
mapper层
package com.table.create.mapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
@Mapper
public interface TableMapper {
int CreateTable(@Param("sql") String sql);
int UpdateTable(@Param("sql") String sql);
int DeleteTable(@Param("sql") String sql);
}
mapper.xml
${sql}
${sql}
DROP TABLE IF EXISTS ${tableName}
做完感触：爽歪歪，哈哈哈，现在心情很巴适！！几十张表，写程序几小时，建表几分钟， 哈哈哈
如果小编写的有帮助到你，麻烦点个赞赞哦！哈哈
