上次遇到一个问题,提供数字r250-r500,要求在数据库中新增r250到r500的字段,同时这些字段也可能存在于数据库表中,同时使用了 allowMultiQueries=true在配置中,实现了同时多个insert,只需用;隔开即可
这里不想使用手写,于是写了一个不完善的接口
service层
//起始数字,结尾数字,表名
int startNum=250;
int endNum=300;
String str0 = "";
for (int i = 0; i <endNum-startNum ; i++) {
String str1 = "ALTER TABLE aps_ord ADD COLUMN "+"d"+ startNum + " varchar(100) COMMENT 'r48'";
str0 +=str1;
}
String str =str0.toUpperCase();
// 判断:必须是同表的新增字段sql;
// 1.按照 ; 切割,提取成字段名做key,sql 做成value,做成map
// 2.查出该表所有字段,
// 3.剔除有的需要新增的字段(map key)
// 4.剩下的value拼接起来
List<String> lists = Arrays.stream(str.split(";")).collect(Collectors.toList());
Map<String,String> map =new HashMap<>();
for (String i : lists) {
List<String> listi = Arrays.stream(i.split("\\s+")).collect(Collectors.toList());//一条sql按空格切割
String sub =""; //key
int addIndex = listi.indexOf("ADD"); //column可能会省略,用add确定位置
sub = listi.get(addIndex+2);
map.put(sub,i);
}
String tableName =StringUtils.substringBetween(lists.get(0),"TABLE","ADD").trim();//截取字符,去掉空格;
List<String> list =baseMapper.showTable(tableName);
for (String strOne:list) {
strOne.toUpperCase();
}
for (int i = 0; i <list.size(); i++) {
map.remove(list.get(i));
}
String s="";
Collection<String> values = map.values();
for (Object value: values){
s =s+value+";";
}
baseMapper.alterTable(s);
mapper
int alterTable(String sqlString);
List<String> showTable(@Param("tableName") String tableName);
xml
用$是因为,在代码中的判断是
select column_name from information_schema.columns
where table_schema = 'fl' and table_name = 'aps_ord' ;
带有单引号,无法使用#来判断,会报错
<update id="alterTable" >
${sqlString}
</update>
<select id="showTable" resultType="java.lang.String">
select column_name from information_schema.columns where table_schema = 'fl' and table_name = '${tableName}';
</select>