https://www.huangdf.xyz/categories/study-notes
南风
南风
发布于 2024-05-15 / 41 阅读
1
0

Java学习之自定义建表

一、背景

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

1、方法探究

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

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

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

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

2、前期准备

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

1、数据库准备

1、数据类型表

id

type

1

int

...

......

2、数据索引表

id

state

1

NORMAL

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、总结

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

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


评论