一、背景
想做自定义建表的原因是,公司准备做一个数据接入平台,需要从前端页面建表。以往建大多是后端手动通过图形化界面建表,而现在将这个任务交到了前端页面,于是有了以下的思考。以下探究均基于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、总结
自助建表的功能到这一步基本上就算是实现了,后续需要完善的就是判断表是否存在等细节。当然另一个办法就是,前端页面做一个能输入的下拉框,下拉框的内容是选定数据库的表名,输入想要的表名,下拉列表中没出现东西即该表名不存在,可以新建,反之则不允许新建。
下一篇记录自定义查询吧。