一、背景

想做自定义建表的原因是，公司准备做一个数据接入平台，需要从前端页面建表。以往建大多是后端手动通过图形化界面建表，而现在将这个任务交到了前端页面，于是有了以下的思考。以下探究均基于mysql

1、方法探究

数据库语言分为DQL(数据查询语言)、DML(数据操纵语言)、DDL(数据定义语言)、DCL(数据控制语言)。

建表方式无非是导入sql文件，图形化界面创建，图像化界面执行DDL语句。想了想要实现项目经理的功能，由于是将建表交由前端页面实现，首先排除第一个和第二个。那就只剩下第三个，在数据库写好sql语句，执行建表。

<insert id="testCreate">
CREATE TABLE ......
</insert>

使用sql的标签，但是执行的是DDL语句。项目经理的思路是前端拼接字符串，通过$符号传递参数，但我认为不妥，一是在前端拼接，后端校验一长串的字符串比较困难且$接收参数有sql注入的风险。所以我打算将处理过程放到代码中。通过定义好实体类，再经由一定的规则转换成最终的成品。

2、前期准备

在确定思路的情况下，根据DDL语句规则建立一套配套的支撑

1、数据库准备

1、数据类型表

2、数据索引表

上述两个表为前端页面提供下拉框选项

2、代码准备

1、实体类

    private String columnName;
    private Boolean hasPrimaryKey;
    private Boolean hasKey;
    private String keyType;
    private Boolean hasAutoIncrement;
    private String remark;
    private Boolean hasNull;
    private String columnType;

   private transient String sql;
   private String dbName;
   private String tableName;
   private List<TableColumnsDTO> tableColumns;

   public List<TableColumnsDTO> getTableColumns() {
        return null == tableColumns? Collections.emptyList() : tableColumns;
   }

根据DDL语句特点，建立实体类，前端传输一个集合到后端，后端通过上述表的对应规则，对sql语句进行重新拼接。

2、xml文件

经过测试，DDL语句和sql的标签无关，无论是insert还是select都可以触发DDL语句，再考虑到该语句是为动态扩展的，因此，xml文件中的语句如下：

<insert id="createTable" >
CREATE TABLE #{dbName}.#{tableName}  (
 ${sql}
)

其中${sql}部分为拼接的sql。使用$传参需要防止SQL注入。

3、实践开始

首先是定义一个在conroller中使用的切面，并使用前置通知，在切面中对数据进行整合。切面的意义是将集合对象转成建表语句。切面里只干三件事：1、拼接建表字段语句；2、拼接建表索引语句；3、处理字符串。

1、字段处理

1、列名

为了防止sql注入，所以需要对字段名进行过滤，带有“--”、“；”以及空白字符串均视为非法字符。

   if (StrUtil.isBlank(columnName) || columnName.contains("--") || columnName.contains(";")) {
                //抛异常
                throw new RuntimeException("字段名称不合法，请检查: " + columnName);
            }

2、主键

主键只能由一个，由于是页面操作，难免会有操作失误的地方，例如给了两个主键，所以需要增加判断，第二个及以后被设为主键的，不会触发主键的语句

 //主键
            if (tableColumn.getHasPrimaryKey() && pkeyCount == 0) {
                pkeyCount++;
                keyAble = StrPool.PK + StrPool.SPACE + StrPool.LEFT_BRACKET + StrPool.ITALIC_DOT + columnName + StrPool.ITALIC_DOT + StrPool.RIGHT_BRACKET;
                autoIncrement = tableColumn.getHasAutoIncrement() ? StrPool.AUTO_INCR : StrPool.SPACE;
            }

另外再提一句，字段名尽量不要设置成isxxx，当字段类型为布尔时，多数时候isxxx是个自带的方法，且前端传入的和收到的很可能不一样（当布尔类型为boolean时），布尔类型的字段尽量使用包装类Boolean，以及字段名避免isxxx，可以用hasxxx替代或者其他。

3、字符串常量

由于语句拼接涉及到大量的字符串，而在代码中使用字符串不便于后期维护，且不够美观，因此定义一个字符串常量。字符串常量的作用是将集合元素转换为特定的字符串。字符串常量可以定义为枚举类、常量类、常量接口。我这边选的是常量接口。

public interface StrPool {
    String SPACE=" ";
    String PK="PRIMARY KEY";
    String AUTO_INCR="AUTO_INCREMENT";
    String COMMENT="COMMENT";
    String NULL="NULL";
    String NOT_NULL ="NOT NULL";
    String DEFAULT="DEFAULT";
    String USE_HASH="USING HASH";
    String USE_BTREE="USING BTREE";
    String UNIQUE="UNIQUE INDEX";
    String SPATIAL="SPATIAL INDEX";
    String FULLTEXT="FULLTEXT INDEX";
    String NORMAL="INDEX";
    String ITALIC_DOT="`";
    String ITALIC_DOUBLE="\"";
    String ITALIC_SINGLE="'";
    String LEFT_BRACKET="(";
    String RIGHT_BRACKET=")";
    String SEMICOLON=";";
    String COMMA=",";
    String CREATE_TABLE="CREATE TABLE";
}

2、拼接建表字段语句

观察建表的语句可以发现，建表语句大致包含：字段名+字段类型+是否允许为空+备注。其中主键多了个是否自动递增。

 //字段名+字段类型+是否非空+备注
            String columnType = tableColumn.getColumnType();
            String nullAble = tableColumn.getHasNull() ? StrPool.NULL : StrPool.NOT_NULL;
            String remark = tableColumn.getRemark();
            String keyAble = "";
            String autoIncrement = "";
            String indexAble="";
String res = StrPool.ITALIC_DOT + columnName + StrPool.ITALIC_DOT + StrPool.SPACE
                    + columnType + StrPool.SPACE
                    + nullAble + StrPool.SPACE
                    + autoIncrement + StrPool.SPACE
                    + StrPool.COMMENT + StrPool.SPACE
                    +StrPool.ITALIC_SINGLE+ remark+StrPool.ITALIC_SINGLE+StrPool.COMMA;

3、拼接索引语句

同上，不过需要注意顺序，索引语句是在建表语句之后，因此定义两个stringbulider，最后在组合一下即可。需要注意的是，主键索引只能有一个，所以在循环外面定义一个整型用于记录主键索引出现的次数，或者定义一个布尔类型，出现主键索引将其置为false。

//主键
            if (tableColumn.getHasPrimaryKey() && pkeyCount == 0) {
                pkeyCount++;
                keyAble = StrPool.PK + StrPool.SPACE + StrPool.LEFT_BRACKET + StrPool.ITALIC_DOT + columnName + StrPool.ITALIC_DOT + StrPool.RIGHT_BRACKET;
                autoIncrement = tableColumn.getHasAutoIncrement() ? StrPool.AUTO_INCR : StrPool.SPACE;
            }
            //拼接
            //索引
            if(tableColumn.getHasKey()){
                indexAble=StrPool.COMMA+tableColumn.getKeyType()+StrPool.SPACE+
                        StrPool.LEFT_BRACKET + StrPool.ITALIC_DOT + columnName + StrPool.ITALIC_DOT + StrPool.RIGHT_BRACKET;
            }

4、切面

细节就是上面的那些了，现在来说一下切面本身逻辑。

    @Before("@annotation(com.hwb.customsql.aop.SqlSegment)")
    public void create(JoinPoint joinPoint) {
        for (Object arg : joinPoint.getArgs()) {
            if (arg instanceof CreateTableDTO) {
                CreateTableDTO tableDTO = (CreateTableDTO) arg;
                List<TableColumnsDTO> tableColumns = tableDTO.getTableColumns();
                //拼接列表数据//拼接主键、索引等
                String sql = dealColumnName(tableColumns);
                tableDTO.setSql(sql);
                tableDTO.setDbName(StrPool.ITALIC_DOT+tableDTO.getDbName()+StrPool.ITALIC_DOT);
                tableDTO.setTableName(StrPool.ITALIC_DOT+tableDTO.getTableName()+StrPool.ITALIC_DOT);
            }
        }
    }

对库名和表名的修改，也可以在语句中通过concat等函数进行拼接。

4、总结

自助建表的功能到这一步基本上就算是实现了，后续需要完善的就是判断表是否存在等细节。当然另一个办法就是，前端页面做一个能输入的下拉框，下拉框的内容是选定数据库的表名，输入想要的表名，下拉列表中没出现东西即该表名不存在，可以新建，反之则不允许新建。

下一篇记录自定义查询吧。