已知带有特殊标记的sql语句和前端传过来的参数集合,用参数替换sql的特殊标记,如果标记对应的参数存在,则直接替换,如果不存在,要把对应的一段条件去掉,最后得出一个能用于查询的sql语句。要求给出完整代码,代码加上必要的注释。例如:已知 1)带有特殊标记的sql语句 select * from T1 where 1=1 and a = :aa and (b =:bb or b =:cc) and e like :ee 2)前端传过来的参数有 aa= "111" , bb= "222", cc= "333",dd = "444" 得到 select * from T1 where 1=1 and a = 111 and (b = 222 or b = 333) /** * 用参数替换sql的特殊标记,如果标记对应的参数存在,则直接替换,如果不存在,要把对应的一段条件去掉,最后得出一个能用于查询的sql语句,返回能用于查询的sql语句。 * @param labelSql 带有标记的sql语句, 如select * from T1 where 1=1 and a = :aa and (b =:bb or b =:cc) and e like :ee * @param param 参数集合,如aa= "111" , bb= "222", cc= "333",dd = "444" * @return 能用于查询的sql,如select * from T1 where 1=1 and a = 111 and (b = 222 or b = 333) */ public static String getRunnableSql(String labelSql,Map<String,Object> param){ //write your code here; }完成了,请用如下情况去测试
1) sql: select * from T1 where 1=1 and (((a = :AA or B like :BB or c in :CC) and d in :DD ) or e = :EE) and f like :FF
2)参数:GG = "1223" , aa = "first"最终得到 select * from T1 where 1=1
也测一下:有些标记有参数,有些标记没有参数,看最终结果是否正确我在解这个字符串的时候,"select * from T1 where 1=1 and (((a=:AA or B like :BB or c in :CC) and d in :DD) or e=:EE) and f like :FF"
得到结果select * from T1 where 1=1 and d in 1234 or e=1234 and f like 1234,语意错误。求大神们的解题思路,谢谢。
1) sql: select * from T1 where 1=1 and (((a = :AA or B like :BB or c in :CC) and d in :DD ) or e = :EE) and f like :FF
2)参数:GG = "1223" , aa = "first"最终得到 select * from T1 where 1=1
也测一下:有些标记有参数,有些标记没有参数,看最终结果是否正确我在解这个字符串的时候,"select * from T1 where 1=1 and (((a=:AA or B like :BB or c in :CC) and d in :DD) or e=:EE) and f like :FF"
得到结果select * from T1 where 1=1 and d in 1234 or e=1234 and f like 1234,语意错误。求大神们的解题思路,谢谢。
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;public class TTeeest {
public static void main(String[] args) { Map<String, Object> map = new HashMap<String, Object>();
map.put("aa", "123"); getRunnableSql("select * from T1 where 1=1 and a = :aa and (b=:bb or b =:cc) and e like :ee", map);
} /**
*
* 用参数替换sql的特殊标记,如果标记对应的参数存在,则直接替换,如果不存在,要把对应的一段条件去掉,最后得出一个能用于查询的sql语句,返回能用于查询的sql语句。
*
* @param labelSql
* 带有标记的sql语句, 如select * from T1 where 1=1 and a = :aa and (b
* =:bb or b =:cc) and e like :ee
*
* @param param
* 参数集合,如aa= "111" , bb= "222", cc= "333",dd = "444"
*
* @return 能用于查询的sql,如select * from T1 where 1=1 and a = 111 and (b = 222 or
* b = 333)
*
*/ public static String getRunnableSql(String labelSql, Map<String, Object> param) { String regex1 = "(or|and)?\\s?[\\(?\\w?\\s?]\\s?(=|like)?\\s?:\\w+"; String regex2 = "(?<=:)\\w+"; Pattern pattern1 = Pattern.compile(regex1); Pattern pattern2 = Pattern.compile(regex2); Matcher matcher = pattern1.matcher(labelSql); // 匹配找出以下
// and a = :aa
// b=:bb
// or b =:cc
// and e like :ee
while (matcher.find()) {
// 用于aa bb这些
String paramXX = null;
// 提取到要替换的字符串
String paramReplace = matcher.group(); Matcher matcher2 = pattern2.matcher(paramReplace);
// 再找出:aa中的aa
if (matcher2.find()) {
paramXX = matcher2.group(); }
// 找出map中的参数
Object mapParam = param.get(paramXX); // 判断是否为null,为null则删除
if (mapParam == null) { labelSql = labelSql.replaceAll(paramReplace, " ");
} else {
// 替换
labelSql = labelSql.replaceAll(":" + paramXX, mapParam.toString());
} }
System.out.println(labelSql); return null; }
}
// 去年and () or()这种
labelSql = labelSql.replaceAll("(and|or)\\s?\\(\\s*+\\)\\s", "");
这个思路很好,但是还是要求语意正确
select * from T1 where 1=1 and (((a=:AA or B like :BB or c in :CC) and d in :DD) or e=:EE) and f like :FF
map.put("bb", "1234");
map.put("DD", "1234");
map.put("EE", "1234");
map.put("FF", "1234");
select * from T1
<where>
<if test="aa!= null>
and a= #{aa}
</if>
<if test="bb!= null or cc != null >
and b= #{bb} or c = #{cc}
</if>
<if test="dd!= null >
and d= #{dd}
</if>
and 1=1
</where>
这是mybatis里的吧,我说的是最近的面试题啦
是的,我也是这样的思路,但是像这条语句:select * from T1 where 1=1 and (((a=:AA or B like :BB or c in :CC) and d in :DD) or e=:EE) and f like :FF
存在参数bb:1234 DD:1234 EE:1234 FF:1234
这是我的结果:select * from T1 where 1=1 and d in 1234 or e=1234 and f like 1234
就出现了语意不明的情况
是的,我也是这样的思路,但是像这条语句:select * from T1 where 1=1 and (((a=:AA or B like :BB or c in :CC) and d in :DD) or e=:EE) and f like :FF
存在参数bb:1234 DD:1234 EE:1234 FF:1234
这是我的结果:select * from T1 where 1=1 and d in 1234 or e=1234 and f like 1234
就出现了语意不明的情况
那是你判断的有问题吧,按思路select * from T1 where 1=1 and (((a=:AA or B like :BB or c in :CC) and d in :DD) or e=:EE) and f like :FF这个第一步处理完应该是这样的select * from T1 where 1=1 and ((() and d in 1234 ) or e=1234 ) and f like 1234
是的,我也是这样的思路,但是像这条语句:select * from T1 where 1=1 and (((a=:AA or B like :BB or c in :CC) and d in :DD) or e=:EE) and f like :FF
存在参数bb:1234 DD:1234 EE:1234 FF:1234
这是我的结果:select * from T1 where 1=1 and d in 1234 or e=1234 and f like 1234
就出现了语意不明的情况
那是你判断的有问题吧,按思路select * from T1 where 1=1 and (((a=:AA or B like :BB or c in :CC) and d in :DD) or e=:EE) and f like :FF这个第一步处理完应该是这样的select * from T1 where 1=1 and ((() and d in 1234 ) or e=1234 ) and f like 1234确实是这样,我做了些更改,因为这样也是语意不明了吧?
这是mybatis里的吧,我说的是最近的面试题啦
这个方法也可以在java中这样逻辑判断使用的撒