SQL插入语句自动封装工具类
有时候需要拼装sql语句,但是有的表动不动就是几十个字段或者上百个字段,自己手写肯定是不太靠谱的,这里提供一个参考工具类。
主要由三部分组成:
1、TRANSCOLUMNTABLE :需要进行字段名替换的表的数组
2、DATADICTIONARY :替换字段对应的规则字典
3、sqlFormat:工具主体
设计思路:
请求发送过来的是一个Map,map的key对应字段名,value就是值。对于map中包含map的情况,需要把子map取出另外执行一次,或者你可以自己改造为递归调用。
定义规范:
1、字段名全部采用小写
2、每个字段都需要前缀修饰,避免重复字段难以区分
3、字段前缀采用一个自定义字符+表名前n个字符定义+下划线。例如user表,自定义字符为p,然后取前3个字符,前缀最后的结果就是puse_
4、子表的前缀长度比父表的前缀长度+1。例如userinfo作为user的子表,前缀就为puser_。
5、一个模块对应一串表前缀,比如一个oto模块,表前缀就可以取名为polar_oto_,相关的表全都用这个前缀,以后看表名也能直接看出属于哪个功能模块。这部分属于自定义,随意,不要就删。
业务场景分析:
1、数据库中字段名为:flag,后端接收到的请求数据是一个map,但是其对应的名为accountFlag,这个时候如果不作处理,拼接出来的语句肯定就不符合我们需要的sql。所以这个地方需要用一个字典,一 一对应用于替换字段名。
2、请求中对应的map1和map2都有相同的key名为accountFlag,table1中有对应的字段为flag,table2中有对应的字段为accountFlag,那么这个时候我们就只需要替换map1中的accountFlag,map2中不需要变化,所以需要加一个数组,把map1对应的table加入到数组中,只有存在于数组中的table对应的map才会进行字段名的替换。
3、参数中的index字段:accountinfo表是account表的子表,account表的主键id命名为pa_id,为了拼接语句的时候出现不必要的麻烦,这里规定子表的字段名前缀长度+1,那么accountinfo的主键id命名就是pac_id。进一步解释:在生成account的语句的时候,id默认拼接为了map名转换为小写然后取前n个字符。如果accountinfo也是取前n个字符作为前缀,那么生成的id字段名就和父表id字段名重复了,插入必然报错,所以我们采用子表+1的规则。
4、参数中的 parentId,就是父表的id,因为我们主要的参数是一个map,在进行子表sql拼接的时候,会取到map里的每一个元素,父表id也在其中,如果不做筛查处理,那么会再一次拼装父id,就变成了pac_pa_id,自然也是不符合我们需求的,方法中这个参数就是为了做筛查处理,对map中的parentId直接进行sql拼接,而不去处理。
代码:
public class SqlUtils{
public final static String[] TRANSCOLUMNTABLE = {"user", "manager", "staff"};
public final static Map<String, String> DATADICTIONARY = new HashMap() {
{
put("deductible", "deduct");
put("publicAccountFlag", "accountflag");
}
};
/**
* @param requestMap 对应表的map
* @param tableName 表名
* @param index 截取字段长度
* @param parentId 父ID
* @param preTableName 表名前缀
* @param preColumnName 字段前缀
* @return 拼装后的字符串
*/
public static String sqlFormat(Map<String, Object> requestMap, String tableName, Integer index, String parentId, String preTableName, String preColumnName) {
if (requestMap.isEmpty()) {
return null;
}
// 用于拼接from前面的column
StringBuffer columnSql = new StringBuffer();
// 用于拼接from后面的value
StringBuffer valueSql = new StringBuffer();
StringBuffer table = new StringBuffer().append(preTableName).append(tableName.toLowerCase());
columnSql.append("INSERT INTO ").append(table).append(" (");
valueSql.append("VALUES ").append("( ");
// 对requestMap的每一个元素进行操作
Iterator<String> iterator = requestMap.keySet().iterator();
while (iterator.hasNext()) {
String obj = iterator.next();
// 该方法只处理类型为String和Integer的数据。如果Map包含Map,需要单独处理子Map
if ((!(requestMap.get(obj) instanceof String)) && (!(requestMap.get(obj) instanceof Integer))) {
continue;
} else {
// 对于子表关联父表的ID,父ID不参与preColumnName的拼接操作
// 比如父表id命名为"pbase_id",存放在requestMap中,如果不摘出来,再一次拼接后就变成了"ppbase_id",不利于后期的查询美观
if ((!Strings.isNullOrEmpty(parentId)) && parentId.equals(obj)) {
columnSql.append(obj).append(", ");
valueSql.append(requestMap.get(obj).toString()).append(", ");
continue;
}
String dictionaryTransformName = null;
StringBuffer columnName;
// 部分表的字段需要做重命名处理
if (Arrays.asList(TRANSCOLUMNTABLE).contains(tableName)) {
Iterator<String> dataDictionaryIt = DATADICTIONARY.keySet().iterator();
while (dataDictionaryIt.hasNext()) {
String key = dataDictionaryIt.next();
if (obj.equals(key)) {
dictionaryTransformName = DATADICTIONARY.get(key);
}
}
}
// 判断数据字典是否需要替换字段名
// 为空,表示不用替换,直接进行追加
if (Strings.isNullOrEmpty(dictionaryTransformName)) {
columnName = new StringBuffer().append(preColumnName).append(tableName.substring(0, index).toLowerCase()).append("_").append(obj.toLowerCase());
} else {
columnName = new StringBuffer().append(preColumnName).append(tableName.substring(0, index).toLowerCase()).append("_")
.append(dictionaryTransformName.toLowerCase());
}
columnSql.append(columnName).append(", ");
valueSql.append("'").append(requestMap.get(obj).toString()).append("', ");
}
}
// 因为上面的操作处理完最后一个字段后依然会加一个逗号和空格,这里需要把这个逗号删掉。然后把前半段和后半段拼一起
columnSql.deleteCharAt(columnSql.length() - 2).append(") ").append(valueSql.deleteCharAt(valueSql.length() - 2)).append(")").append(";");
return columnSql.toString();
}
}
Main方法:
public static void main(String[] args) {
Map<String, Object> requestMap = Maps.newHashMap();
Map<String, Object> requestMap2 = Maps.newHashMap();
requestMap.put("uuid", "1111");
requestMap.put("abc", "1114221");
requestMap.put("des", "333");
requestMap.put("asxx", requestMap2);
requestMap2.put("qqq", "444");
requestMap2.put("wwww", "444");
String s = sqlFormat(requestMap, "test", 3, null, "polar_", "p");
String s2 = sqlFormat(requestMap2, "testchild", 4, null, "polar_", "p");
System.out.println(s);
System.out.println(s2);
}
最后输出结果:
INSERT INTO polar_test (ptes_abc, ptes_des, ptes_uuid ) VALUES ( '1114221', '333', '1111' );
INSERT INTO polar_testchild (ptest_qqq, ptest_wwww ) VALUES ( '444', '444' );