Excel生成Oracle数据库表sql工具类
1.解决问题: 开发文档中字段比较多的时候,建表sql(Oracle下划线命名规范)比较麻烦,容易出错~~ (主要是懒)
特意手写一个工具,根据excel字段,生成建表的sql语句.
~~~末尾附GitHub传送门~~~
2.Java代码
Excel头:
public class ExcelHead {
private String excelName; //Excel名
private String entityName; //实体类属性名
private boolean required=false; //值必填
get()/set()
}Excel实体:
public class Excel {
private String item; //属性列
private String type; //类型
private String isNeed; //是否必填
private String annotation; //注释
private Integer maxLength; //最大长度
get()/set()
}ExcelUtils工具类 :
(借鉴而来)
package com.utils;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class ExcelUtils {
private static final String FULL_DATA_FORMAT = "yyyy/MM/dd HH:mm:ss";
private static final String SHORT_DATA_FORMAT = "yyyy/MM/dd";
/**
* Excel表头对应Entity属性 解析封装javabean
*
* @param classzz 类
* @param in excel流
* @param fileName 文件名
* @param excelHeads excel表头与entity属性对应关系
* @param <T>
* @return
* @throws Exception
*/
public static <T> List<T> readExcelToEntity(Class<T> classzz, InputStream in, String fileName, List<ExcelHead> excelHeads) throws Exception {
checkFile(fileName); //是否EXCEL文件
Workbook workbook = getWorkBoot(in, fileName); //兼容新老版本
List<T> excelForBeans = readExcel(classzz, workbook, excelHeads); //解析Excel
return excelForBeans;
}
/**
* 解析Excel转换为Entity
*
* @param classzz 类
* @param in excel流
* @param fileName 文件名
* @param <T>
* @return
* @throws Exception
*/
public static <T> List<T> readExcelToEntity(Class<T> classzz, InputStream in, String fileName) throws Exception {
return readExcelToEntity(classzz, in, fileName,null);
}
/**
* 校验是否是Excel文件
*
* @param fileName
* @throws Exception
*/
public static void checkFile(String fileName) throws Exception {
if (!StringUtils.isEmpty(fileName) && !(fileName.endsWith(".xlsx") || fileName.endsWith(".xls"))) {
throw new Exception("不是Excel文件！");
}
}
/**
* 兼容新老版Excel
*
* @param in
* @param fileName
* @return
* @throws IOException
*/
private static Workbook getWorkBoot(InputStream in, String fileName) throws IOException {
if (fileName.endsWith(".xlsx")) {
return new XSSFWorkbook(in);
} else {
return new HSSFWorkbook(in);
}
}
/**
* 解析Excel
*
* @param classzz 类
* @param workbook 工作簿对象
* @param excelHeads excel与entity对应关系实体
* @param <T>
* @return
* @throws Exception
*/
private static <T> List<T> readExcel(Class<T> classzz, Workbook workbook, List<ExcelHead> excelHeads) throws Exception {
List<T> beans = new ArrayList<T>();
int sheetNum = workbook.getNumberOfSheets();
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
String sheetName=sheet.getSheetName();
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
Row head = sheet.getRow(firstRowNum);
if (head == null)
continue;
short firstCellNum = head.getFirstCellNum();
short lastCellNum = head.getLastCellNum();
Field[] fields = classzz.getDeclaredFields();
for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) {
Row dataRow = sheet.getRow(rowIndex);
if (dataRow == null)
continue;
T instance = classzz.newInstance();
if(CollectionUtils.isEmpty(excelHeads)){ //非头部映射方式，默认不校验是否为空，提高效率
firstCellNum=dataRow.getFirstCellNum();
lastCellNum=dataRow.getLastCellNum();
}
for (int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++) {
Cell headCell = head.getCell(cellIndex);
if (headCell == null)
continue;
Cell cell = dataRow.getCell(cellIndex);
headCell.setCellType(Cell.CELL_TYPE_STRING);
String headName = headCell.getStringCellValue().trim();
if (StringUtils.isEmpty(headName)) {
continue;
}
ExcelHead eHead = null;
if (!CollectionUtils.isEmpty(excelHeads)) {
for (ExcelHead excelHead : excelHeads) {
if (headName.equals(excelHead.getExcelName())) {
eHead = excelHead;
headName = eHead.getEntityName();
break;
}
}
}
for (Field field : fields) {
if (headName.equalsIgnoreCase(field.getName())) {
String methodName = MethodUtils.setMethodName(field.getName());
Method method = classzz.getMethod(methodName, field.getType());
if (isDateFied(field)) {
Date date=null;
if(cell!=null){
date=cell.getDateCellValue();
}
if (date == null) {
volidateValueRequired(eHead,sheetName,rowIndex);
break;
}
method.invoke(instance, cell.getDateCellValue());
} else {
String value = null;
if(cell!=null){
cell.setCellType(Cell.CELL_TYPE_STRING);
value=cell.getStringCellValue();
}
if (StringUtils.isEmpty(value)) {
volidateValueRequired(eHead,sheetName,rowIndex);
break;
}
method.invoke(instance, convertType(field.getType(), value.trim()));
}
break;
}
}
}
beans.add(instance);
}
}
return beans;
}
/**
* 是否日期字段
*
* @param field
* @return
*/
private static boolean isDateFied(Field field) {
return (Date.class == field.getType());
}
/**
* 空值校验
*
* @param excelHead
* @throws Exception
*/
private static void volidateValueRequired(ExcelHead excelHead,String sheetName,int rowIndex) throws Exception {
if (excelHead != null && excelHead.isRequired()) {
throw new Exception("《"+sheetName+"》第"+(rowIndex+1)+"行:\""+excelHead.getExcelName() + "\"不能为空！");
}
}
/**
* 类型转换
*
* @param classzz
* @param value
* @return
*/
private static Object convertType(Class classzz, String value) {
if (Integer.class == classzz || int.class == classzz) {
return Integer.valueOf(value);
}
if (Short.class == classzz || short.class == classzz) {
return Short.valueOf(value);
}
if (Byte.class == classzz || byte.class == classzz) {
return Byte.valueOf(value);
}
if (Character.class == classzz || char.class == classzz) {
return value.charAt(0);
}
if (Long.class == classzz || long.class == classzz) {
return Long.valueOf(value);
}
if (Float.class == classzz || float.class == classzz) {
return Float.valueOf(value);
}
if (Double.class == classzz || double.class == classzz) {
return Double.valueOf(value);
}
if (Boolean.class == classzz || boolean.class == classzz) {
return Boolean.valueOf(value.toLowerCase());
}
if (BigDecimal.class == classzz) {
return new BigDecimal(value);
}
/* if (Date.class == classzz) {
SimpleDateFormat formatter = new SimpleDateFormat(FULL_DATA_FORMAT);
ParsePosition pos = new ParsePosition(0);
Date date = formatter.parse(value, pos);
return date;
}*/
return value;
}
/**
* 获取properties的set和get方法
*/
static class MethodUtils {
private static final String SET_PREFIX = "set";
private static final String GET_PREFIX = "get";
private static String capitalize(String name) {
if (name == null || name.length() == 0) {
return name;
}
return name.substring(0, 1).toUpperCase() + name.substring(1);
}
public static String setMethodName(String propertyName) {
return SET_PREFIX + capitalize(propertyName);
}
public static String getMethodName(String propertyName) {
return GET_PREFIX + capitalize(propertyName);
}
}
}Constant常量 :
package com.utils;
public interface Constant {
String pre = " create table <<tableName>> \n" +
"(\n" +
"<<itemContent>> \n" +
")\n" +
"/\n" +
"comment on table <<tableName>> is \'<<tableNameDesc>>\'" +
"\n" +
"/\n" +
"<<annotationContent>>";
//oracle数据类型相关
String NUMBER = "NUMBER";
String VARCHAR2 = "VARCHAR2";
String DATE = "DATE";
String NOT_NULL = "not null";
String STRING_MAX_LENGTH = "256";//默认字符串最大位数
String ROW_PRE = " ";//行开头缩进
}ChangeChar工具类 (驼峰转下划线大写) :
我的另一篇博客有 : https://www.cnblogs.com/coloz/p/10911174.html
ExcelToSql:( 处理Excel中数据,各种拼接替换模板和常量)
package com.utils;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import java.io.*;
import java.util.List;
public class ExcelToSql {
/**
* @param tableName
* @param tableNameDesc
* @param excelPath
* @throws Exception
*/
public static String createSql(String tableName, String tableNameDesc, String excelPath) throws Exception {
//String tableName = "MY_USER"; //表名
// String tableNameDesc = "我的用户表"; //表名描述
// String excelPath = "D:\\code\\excelToSql\\src\\main\\resources\\Excel.xlsx";
File file = new File(excelPath); //文件位置
FileInputStream fileInputStream = new FileInputStream(file);
String name = file.getName();
List<Excel> list = ExcelUtils.readExcelToEntity(Excel.class, fileInputStream, name);
// System.out.println("list :" + list);
String sqlStr = Constant.pre; //总的部分,先添加前置部分
String itemContent = ""; //具体表字段信息
String annotationContent = ""; //后面注释内容
String rowModelEnd = ",\n"; //最后一行不加
if (CollectionUtils.isNotEmpty(list)) {
for (int i = 0; i < list.size(); i++) {
//oracle
Excel vo = list.get(i);
if (vo != null) {
String item = vo.getItem();
String annotation = vo.getAnnotation();
String type = vo.getType(); //数据类型
String isNeed = vo.getIsNeed(); //是否必填
Integer maxLength = vo.getMaxLength(); //最大长度
if (StringUtils.isBlank(item)) {
continue; //列不存在直接跳过
}
String ITEM_LINE = ChangeChar.camelToUnderline(item, 2); //对应oracle数据库列名转大写下划线
//用于替换sql语句中的占位 字符串<< >>
String itemNew = ITEM_LINE; //oracle大写
String annotationNew = annotation;
String typeNew = Constant.VARCHAR2; //默认字符串类型
String isNeedNew = ""; //默认不必填
String maxLengthNew = ""; //最大长度
if (type.contains("int") || type.contains("Num") || type.contains("Int")
|| type.contains("数") || type.contains("num")) {
typeNew = Constant.NUMBER;
} else if (type.contains("date") || type.contains("Date") || type.contains("日期")) {
typeNew = Constant.DATE;
}
if (isNeed.contains("是") || isNeed.contains("true") || isNeed.contains("True")) {
isNeedNew = Constant.NOT_NULL;
}
if (isNeed.contains("是") || isNeed.contains("true") || isNeed.contains("True")) {
isNeedNew = Constant.NOT_NULL;
}
//如果是字符串类型没有指定长度,默认256
if (maxLength == null || maxLength < 0) {
if (typeNew.equals(Constant.VARCHAR2)) {
maxLengthNew = "(" + Constant.STRING_MAX_LENGTH + ")";
}
} else {
maxLengthNew = "(" + maxLength + ")"; //有值直接赋值
}
//默认值问题???
//列字段相关,行左端保持必要空格,方便格式对齐
String rowModel = Constant.ROW_PRE + "<<rowItem>> <<dataType>><<(maxLength)>> <<isNeed>>"; //maxlength有括号
if (i != (list.size() - 1)) {
rowModel += rowModelEnd; //最后一行不加
}
rowModel = rowModel.replaceAll("<<rowItem>>", itemNew).
replaceAll("<<dataType>>", typeNew).
replace("<<(maxLength)>>", maxLengthNew).
replaceAll("<<isNeed>>", isNeedNew);
itemContent += rowModel; //列拼接
//注释相关
String rowAnnotation = "\n" +
"comment on column <<tableName>>.<<ITEM_LINE>> is \'<<annotation>>\'\n" +
"/";
rowAnnotation = rowAnnotation.replaceAll("<<tableName>>", tableName).
replaceAll("<<ITEM_LINE>>", ITEM_LINE).
replaceAll("<<annotation>>", annotationNew)
;
annotationContent += rowAnnotation; //注释拼接
}
}
}
//替换一下表字段名和注释部分
sqlStr = sqlStr.replaceAll("<<itemContent>>", itemContent)
.replaceAll("<<tableNameDesc>>", tableNameDesc)
.replaceAll("<<annotationContent>>", annotationContent)
;
sqlStr = sqlStr.replaceAll("<<tableName>>", tableName);
System.out.println("-----------下面是生成表的sql--------------------------------------------------------");
System.out.println(sqlStr);
System.out.println("-----------sql结束----------------------------------------------------------------");
return sqlStr;
}
}测试类:
package com;
import com.utils.ExcelToSql;
public class MainRun {
public static void main(String[] args) throws Exception {
/**
* 本工具主要为EXCEL生成ORACLE数据库建表sql
* 第一步:修改resource文件夹下面Excel.xlsx内容(第一行不要修改),
* item:列字段, annotation:注释,type:数据类型(Number/String/Date 默认String),maxLength:最大长度(字符串默认256),isNeed:是否必须(是/否)
* 第二步:设置 下面的tableName, tableNameDesc,excelPath
* 第三步:运行MainRun.java类下的main方法,控制台输出sqls
* 注意格式:只测试过excel里面(驼峰字段)--->生成表列都是(下划线大写)
*
*/
String tableName = "MY_USER"; //表名,必填
String tableNameDesc = "我的用户表"; //表名,可以为空
String excelPath = "D:\\code\\excelToSql\\src\\main\\resources\\Excel.xlsx"; //excel硬盘上路径
String sql = ExcelToSql.createSql(tableName, tableNameDesc, excelPath);
}
}总结 : 1. Oracle的SQL规范,对开发者来说也是一种比较好的约束和开发工作中需要考虑到的方面.
2.使用一些比较方便的工具对开发者省时省力,避免重复的"CV"工作.
3.本篇中使用的无非是使用sql模板,结合POI工具,对表字段内容进行替换和逻辑判断,十分简单.
4.如果有实现Mysql版本对应的工具,欢迎留言交流分享~~~
GitHub传送门: https://github.com/ColoZhu/excelToSql
