package com.changhong.autoform.mapper.table.impl;
|
|
import com.alibaba.fastjson.JSON;
|
import com.changhong.autoform.constant.ConstMap;
|
import com.changhong.autoform.core.exception.RowOverLengthException;
|
import com.changhong.autoform.core.mapper.BaseMapper;
|
import com.changhong.autoform.entity.ColunmsFieldEntity;
|
import com.changhong.autoform.entity.SubTableStructure;
|
import com.changhong.autoform.entity.TableStructure;
|
import com.changhong.autoform.entity.sql.create.CreateTable;
|
import com.changhong.autoform.entity.sql.insert.Insert;
|
import com.changhong.autoform.entity.sql.update.UpdateTable;
|
import com.changhong.autoform.mapper.table.TableMapper;
|
import com.iemsoft.framework.cloud.core.tools.ObjectUtil;
|
import org.springframework.stereotype.Component;
|
import org.springframework.ui.ModelMap;
|
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
@Component("simpleTableMapper")
|
public class TableMapperImpl extends BaseMapper implements TableMapper{
|
|
private static final Map<String, String> DBTYPE = new HashMap<>();
|
|
private static final List<String> FIELDLIST = new ArrayList<>();
|
|
private static final Map<String, Integer> DBTYPELENGTH = new HashMap<>();
|
|
|
static {
|
// 单行文本
|
DBTYPE.put("text", ConstMap.VARCHAR150);
|
// 单选按钮
|
DBTYPE.put("radio", ConstMap.VARCHAR50);
|
// 下拉列表框
|
DBTYPE.put("dropdownlist", ConstMap.LONGTEXT);
|
// 日期控件
|
DBTYPE.put("calendar", ConstMap.VARCHAR30);
|
// 复选框
|
DBTYPE.put("checkbox", ConstMap.LONGTEXT);
|
// 子表单
|
DBTYPE.put("subform", ConstMap.VARCHAR30);
|
// 数字
|
DBTYPE.put("number", ConstMap.VARCHAR30);
|
// 多行文本
|
DBTYPE.put("textarea", ConstMap.LONGTEXT);
|
// HTML
|
DBTYPE.put("htmlediter", ConstMap.LONGTEXT);
|
// 条码
|
DBTYPE.put("barcode", ConstMap.VARCHAR30);
|
// 二维码
|
DBTYPE.put("qrcode", ConstMap.VARCHAR200);
|
// 图片
|
DBTYPE.put("image", ConstMap.LONGTEXT);
|
// 文件上传
|
DBTYPE.put("fileupload", ConstMap.LONGTEXT);
|
// 数型控件
|
DBTYPE.put("tree", ConstMap.LONGTEXT);
|
// 金额组件
|
DBTYPE.put("amount", ConstMap.LONGTEXT);
|
//自定义控件
|
DBTYPE.put("mytext",ConstMap.VARCHAR150);
|
//自动生成编号控件
|
DBTYPE.put("auto",ConstMap.VARCHAR150);
|
|
|
// 单行文本
|
DBTYPELENGTH.put("text", 150);
|
// 单选按钮
|
DBTYPELENGTH.put("radio", 50);
|
// 日期控件
|
DBTYPELENGTH.put("calendar", 30);
|
// 复选框
|
// 子表单
|
DBTYPELENGTH.put("subform", 30);
|
// 数字
|
DBTYPELENGTH.put("number", 30);
|
// 条码
|
DBTYPELENGTH.put("barcode", 30);
|
// 二维码
|
DBTYPELENGTH.put("qrcode", 200);
|
|
// 表单固定字段
|
FIELDLIST.add("DATAROWNUM");
|
FIELDLIST.add("TENANTID");
|
FIELDLIST.add("PARENTDATAROWNUM");
|
FIELDLIST.add("DELETEFLG");
|
FIELDLIST.add("CREATEUSER");
|
FIELDLIST.add("CREATEDATETIME");
|
FIELDLIST.add("UPDATEUSER");
|
FIELDLIST.add("UPDATEDATETIME");
|
FIELDLIST.add("PROCESSINSTID");
|
|
}
|
|
//创建表结构
|
@Override
|
public int createTable(TableStructure tableStructure) {
|
int count = 0;
|
count = update(new CreateTable(String.format(tableStructure.getTableName())
|
, new ModelMap().addAllAttributes(tableStructure.getFields())));
|
//添加记录到formDataManager表中
|
Insert insert = new Insert("FORMDATAMANAGER"
|
,new ModelMap("FORMDATATABLE", tableStructure.getTableName())
|
.addAttribute("FORMID", tableStructure.getFormId())
|
.addAttribute("SUBFORMFLG", 0)
|
.addAttribute("CREATEUSER", tableStructure.getCreatUser())
|
.addAttribute("UPDATEUSER", tableStructure.getCreatUser())
|
.addAttribute("FORMDATANUM", 0)
|
.addAttribute("DELETEFLG", 0)
|
.addAttribute("TENANTID", tableStructure.getTenantId()));
|
execute(insert);
|
List<SubTableStructure> subTableList = tableStructure.getSubTableList();
|
if(subTableList != null && subTableList.size()>0){
|
for (SubTableStructure subTableStructure : subTableList) {
|
String subTableName = subTableStructure.getTableName();
|
Map<String, Integer> subFormNameAndFormDataNum = subTableStructure.getSubFormNameAndFormDataNum();
|
count += update(new CreateTable(subTableName
|
,new ModelMap().addAllAttributes(subTableStructure.getFields())));
|
|
Insert subInsert = new Insert("FORMDATAMANAGER"
|
,new ModelMap("FORMDATATABLE", subTableStructure.getTableName())
|
.addAttribute("FORMID", tableStructure.getFormId())
|
.addAttribute("SUBFORMFLG", 1)
|
.addAttribute("CREATEUSER", subTableStructure.getCreatUser())
|
.addAttribute("UPDATEUSER", subTableStructure.getCreatUser())
|
.addAttribute("FORMDATANUM", subFormNameAndFormDataNum.get(subTableName))
|
.addAttribute("DELETEFLG", 0)
|
.addAttribute("TENANTID", tableStructure.getTenantId()));
|
execute(subInsert);
|
}
|
}
|
return count;
|
}
|
|
//修改表结构
|
@Override
|
public int updateTable(TableStructure tableStructure) {
|
System.out.println("updateTable请求参数:"+JSON.toJSONString(tableStructure));
|
// 获取现有主表字段
|
alterTableCols(tableStructure.getTableName(), tableStructure.getAliasValuesMap());
|
/*
|
处理子表单
|
*/
|
if(ObjectUtil.empty(tableStructure.getSubTableList())){
|
return 1;
|
}
|
tableStructure.getSubTableList().stream().forEach(subTableStructure ->{
|
if(ObjectUtil.notEmpty(subTableStructure.getFields())){
|
update(new CreateTable(subTableStructure.getTableName()
|
,new ModelMap().addAllAttributes(subTableStructure.getFields())));
|
return;
|
}
|
alterTableCols(subTableStructure.getTableName()
|
, Optional.ofNullable(subTableStructure.getAliasSubValuesMap().get(subTableStructure.getSubFormNameAndFormDataNum().values().stream().findFirst().orElse(0)))
|
.orElse(Collections.EMPTY_MAP)
|
);
|
});
|
return 1;
|
}
|
|
protected void alterTableCols(String tableName, Map<String, String> aliasValues){
|
List<ColunmsFieldEntity> mainCols = getColunmsField(tableName);
|
Set<String> cols = mainCols.stream().map(ColunmsFieldEntity::getField).collect(Collectors.toSet());
|
Map<String, Object> alterFields = new HashMap<>();
|
aliasValues.entrySet().stream()
|
.filter(val->!cols.contains(val.getKey()))
|
.forEach(val->alterFields.put(val.getKey()
|
, Optional.ofNullable(DBTYPE.get(aliasValues.get(val.getValue())))
|
.orElse(ConstMap.VARCHAR50)
|
));
|
|
if(ObjectUtil.notEmpty(alterFields)) {
|
// 添加字段
|
update(new UpdateTable(tableName, alterFields));
|
}
|
|
}
|
|
// public static void main(String... args){
|
// String v = "DATAROWNUM\tint(11)\n" +
|
// "TENANTID\tchar(50)\n" +
|
// "PARENTDATAROWNUM\tint(11)\n" +
|
// "journey\tvarchar(30)\n" +
|
// "单行文本56\tvarchar(150)\n" +
|
// "子表单49\tvarchar(30)\n" +
|
// "DELETEFLG\tchar(1)\n" +
|
// "CREATEUSER\tvarchar(50)\n" +
|
// "CREATEDATETIME\ttimestamp\n" +
|
// "UPDATEUSER\tvarchar(50)\n" +
|
// "UPDATEDATETIME\ttimestamp\n" +
|
// "PROCESSINSTID\tdecimal(20,0)\n" +
|
// "orderCode\tvarchar(50)\n" +
|
// "orderStatus\tvarchar(2)\n" +
|
// "processState\tvarchar(2)\n" +
|
// "processLog\tvarchar(255)\n" +
|
// "processTask\tvarchar(255)\n" +
|
// "processUser\tvarchar(20)\n" +
|
// "processFlag\tvarchar(20)\n" +
|
// "solidification\tlongtext\n" +
|
// "voucherCode\tvarchar(20)\n" +
|
//// "C_Type\tlongtext\n" +
|
// "Amount\tvarchar(30)\n" +
|
// "toWriteBudget\tlongtext\n" +
|
// "payinfo\tvarchar(30)\n" +
|
// "budgetStartDate\tvarchar(30)\n" +
|
// "Remark\tvarchar(150)\n" +
|
// "budget_itemBalance\tvarchar(30)\n" +
|
// "reimburseMan\tlongtext\n" +
|
// "expend_department\tlongtext\n" +
|
// "expendUseMoney\tvarchar(30)\n" +
|
// "netMoneyAll\tvarchar(30)\n" +
|
// "debitItem\tvarchar(150)\n" +
|
// "expenDesc\tvarchar(150)\n" +
|
// "currency\tlongtext\n" +
|
// "单行文本38\tvarchar(150)\n" +
|
// "number_business\tvarchar(50)";
|
// List<ColunmsFieldEntity> mainCols = new ArrayList<>();
|
// for(String col : v.split("\n")){
|
// ColunmsFieldEntity c = new ColunmsFieldEntity();
|
// c.setField(col.split("\t")[0]);
|
// c.setType(col.split("\t")[1]);
|
// mainCols.add(c);
|
// }
|
// Set<String> cols = mainCols.stream().map(ColunmsFieldEntity::getField).collect(Collectors.toSet());
|
// Map<String, Object> alterFields = new HashMap<>();
|
// Map<String, Object> aliasValues = JSONTool.toObj("{\n" +
|
// " \"C_Type\":\"tree\",\n" +
|
// " \"journey\":\"subform\",\n" +
|
// " \"toWriteBudget\":\"mytext\",\n" +
|
// " \"payinfo\":\"subform\",\n" +
|
// " \"budgetStartDate\":\"calendar\",\n" +
|
// " \"Remark\":\"text\",\n" +
|
// " \"budget_itemBalance\":\"number\",\n" +
|
// " \"reimburseMan\":\"dropdownlist\",\n" +
|
// " \"expend_department\":\"tree\",\n" +
|
// " \"expendUseMoney\":\"number\",\n" +
|
// " \"netMoneyAll\":\"number\",\n" +
|
// " \"debitItem\":\"text\",\n" +
|
// " \"expenDesc\":\"text\",\n" +
|
// " \"currency\":\"dropdownlist\",\n" +
|
// " \"单行文本38\":\"text\",\n" +
|
// " \"number_business\":\"auto\"\n" +
|
// " }", Map.class);
|
// aliasValues.entrySet().stream()
|
// .filter(val->!cols.contains(val.getKey()))
|
// .forEach(val->alterFields.put(val.getKey()
|
// , Optional.ofNullable(DBTYPE.get(aliasValues.get(val.getValue())))
|
// .orElse(ConstMap.VARCHAR50)
|
// ));
|
// System.out.println(alterFields);
|
// }
|
|
protected List<ColunmsFieldEntity> getColunmsField(String tableName) {
|
return showColumn(" show columns from "+tableName);
|
}
|
|
@SuppressWarnings("unused")
|
private Map<String,Object> alterTable(String tableName,
|
List<String> fieldtypeList, Map<String, String> fieldsLength, List<String> aliasValues,
|
List<String> oldField, Map<String,String> aliasValuesMap) throws RowOverLengthException {
|
|
Map<String, List<String>> optionColumns = genAddAndDelColumns(aliasValues, fieldtypeList, oldField);
|
List<String> deleteList = optionColumns.get("DELETE");
|
List<String> addList = optionColumns.get("ADD");
|
|
int maxLengthDel = 0;
|
for (String delObj : deleteList) {
|
if (fieldsLength.get(delObj) != null) {
|
String length = fieldsLength.get(delObj).replaceAll("[^0-9]", "");
|
if (!fieldsLength.get(delObj).equals("longtext")) {
|
maxLengthDel += Integer.parseInt(length); //数据类型大小150 varchar(150)
|
}
|
}
|
}
|
|
if (!checkRowLength(fieldtypeList, maxLengthDel)) {
|
throw new RowOverLengthException();
|
}
|
Map<String,Object> data = new HashMap<>();
|
|
for (int i = 0; i < addList.size(); i++) {
|
String field = addList.get(i);
|
String type = aliasValuesMap.get(field);
|
if (DBTYPE.containsKey(type)) {
|
data.put(field, DBTYPE.get(type));
|
}
|
}
|
|
// 表结构没有更改
|
if (deleteList.isEmpty() && addList.isEmpty()) {
|
return null;
|
}
|
return data;
|
}
|
|
private Map<String, Object> alterSubTable(String subTableName, List<String> subFormObjcloumn, List<String> fieldtypeList,
|
Map<String, String> fieldsLength, List<String> oldField, Map<String, String> subFormNameAndColumnsMap )
|
throws RowOverLengthException {
|
|
Map<String, List<String>> optionColumns = genAddAndDelColumns(subFormObjcloumn, fieldtypeList, oldField);
|
List<String> deleteList = optionColumns.get("DELETE");
|
List<String> addList = optionColumns.get("ADD");
|
|
int maxLengthDel = 0;
|
for (String delObj : deleteList) {
|
if (fieldsLength.get(delObj) != null) {
|
if (!fieldsLength.get(delObj).equals("longtext")) {
|
Integer length = Integer.parseInt(fieldsLength.get(delObj).replaceAll("[^0-9]", ""));
|
maxLengthDel += length;
|
}
|
}
|
}
|
if (!checkRowLength(fieldtypeList, maxLengthDel)) {
|
throw new RowOverLengthException();
|
}
|
Map<String,Object> data = new HashMap<>();
|
StringBuilder alterTableSql = new StringBuilder(
|
"/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FORMID = '%s' */ alter table ");
|
alterTableSql.append(subTableName);
|
// 表单数据表列增加
|
for (int i = 0; i < addList.size(); i++) {
|
String field = addList.get(i);
|
if("DATAROWNUM".equals(field))
|
continue;
|
String type = subFormNameAndColumnsMap.get(field);
|
if (DBTYPE.containsKey(type)) {
|
alterTableSql.append(" ADD COLUMN ");
|
alterTableSql.append(field);
|
alterTableSql.append(DBTYPE.get(type));
|
data.put(field,DBTYPE.get(type));
|
alterTableSql.append(" NULL AFTER SUBDATAROWNUM");
|
alterTableSql.append(ConstMap.COMMA);
|
}
|
}
|
if (!addList.isEmpty()) {
|
alterTableSql.deleteCharAt(alterTableSql.length() - 1);
|
}
|
// 表结构没有更改
|
if (deleteList.isEmpty() && addList.isEmpty()) {
|
return null;
|
}
|
alterTableSql.append(";");
|
return data;
|
}
|
|
private boolean checkRowLength(List<String> fieldtypeList, Integer oldLength) {
|
int rowLength = 0;
|
for (String type : fieldtypeList) {
|
if (DBTYPELENGTH.containsKey(type)) {
|
rowLength += DBTYPELENGTH.get(type);
|
}
|
}
|
if ((rowLength + oldLength) > ConstMap.MAXROWLENGTH) {
|
return false;
|
}
|
return true;
|
}
|
|
private Map<String, List<String>> genAddAndDelColumns(List<String> aliasValues,
|
List<String> fieldtypeList,List<String> oldField) {
|
|
Map<String, List<String>> map = new HashMap<>();
|
List<String> needToDel = new ArrayList<>();
|
List<String> needToAdd = new ArrayList<>();
|
List<String> needToUpd = new ArrayList<>();
|
int count = 0;
|
for (String alias : aliasValues) {
|
if (DBTYPE.containsKey(fieldtypeList.get(count))) {
|
if (!oldField.contains(alias)) {
|
needToAdd.add(alias);
|
} else {
|
needToUpd.add(alias);
|
}
|
}
|
count++;
|
}
|
for (String old : oldField) {
|
if (!aliasValues.contains(old)) {
|
needToDel.add(old);
|
}
|
}
|
map.put("DELETE", needToDel);
|
map.put("ADD", needToAdd);
|
map.put("UPDATE", needToUpd);
|
return map;
|
}
|
|
}
|
