话说那是一个愉快的周五的下午，刚经历双11双黑五12大促连环迎战，一周的工作也接近结束，任务也都要走向提测的节点了，心里美滋滋，可以早点回家啦～
巴特，popo弹出一份：xxx master 的单元测试静态代码检查失败，单元测试失败用例x个。请在1小时内修复并重新执行，否则可能会打回提测，单测失败详情xxx。
点开详情看看失败原因吧~
org.springframework.jdbc.UncategorizedSQLException: ### Error updating database. Cause: java.sql.SQLException: It is not allowed to execute a(n) DELETE without where condition, sql:delete from mt_flash_sale_nav ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: delete from mt_flash_sale_nav ### Cause: java.sql.SQLException: It is not allowed to execute a(n) DELETE without where condition, sql:delete from mt_flash_sale_nav ; uncategorized SQLException for SQL []; SQL state [HY000]; error code [0]; It is not allowed to execute a(n) DELETE without where condition, sql:delete from mt_flash_sale_nav; nested exception is java.sql.SQLException: It is not allowed to execute a(n) DELETE without where condition, sql:delete from mt_flash_sale_nav瓦特，怎么可能，之前还是好好的呀～ 我没动过这块代码呀～一定是加班太多晃了我的狗眼～本地跑一次还是这样～！GG
赶紧联系DBAbaba们，原来是测试环境qs升级啦！xxx
好吧，扯犊子就到这里了
前段时间测试环境ddb开始限制不带where条件的update/delete的sql语句的执行，单测各种失败，且后续还会在生产环境也会这样，于是开始在工程中各种搜索，人工处理难免有遗漏的可能，怎么地也要用程序全部扫描下才放心呀！
那mybatis是如何解析xml和生成sql的呢，比如这样的sql是如何解析的呢？
<select id="selectByCond" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from test_db <where> <if test="a != null"> and `a` = #{a} </if> <if test="list != null"> and b in <foreach collection="list" open="(" close=")" item="item" separator=","> #{item} </foreach> </if> order by db_update_time </where> </select>通过分析mybatis的初始化SqlSessionFactoryBean过程，可以一探究竟。
/** * Build a {@code SqlSessionFactory} instance. * * The default implementation uses the standard MyBatis {@code XMLConfigBuilder} API to build a * {@code SqlSessionFactory} instance based on an Reader. * Since 1.3.0, it can be specified a {@link Configuration} instance directly(without config file). * * @return SqlSessionFactory * @throws IOException if loading the config file failed */ protected SqlSessionFactory buildSqlSessionFactory() throws IOException { // ... if (!isEmpty(this.mapperLocations)) { for (Resource mapperLocation : this.mapperLocations) { if (mapperLocation == null) { continue; } try { XMLMapperBuilder xmlMapperBuilder = new XMLMapperBuilder(mapperLocation.getInputStream(), configuration, mapperLocation.toString(), configuration.getSqlFragments()); // 这里解析xml xmlMapperBuilder.parse(); } catch (Exception e) { throw new NestedIOException("Failed to parse mapping resource: '" + mapperLocation + "'", e); } finally { ErrorContext.instance().reset(); } if (LOGGER.isDebugEnabled()) { LOGGER.debug("Parsed mapper file: '" + mapperLocation + "'"); } } } else { if (LOGGER.isDebugEnabled()) { LOGGER.debug("Property 'mapperLocations' was not specified or no matching resources found"); } } return this.sqlSessionFactoryBuilder.build(configuration); }而sql最终如何生成呢？主要靠SqlSource Sql源接口，代表从xml文件或注解映射的sql内容，主要就是用于创建BoundSql，有实现类DynamicSqlSource(动态Sql源)，StaticSqlSource(静态Sql源)等：
/** * Represents the content of a mapped statement read from an XML file or an annotation. * It creates the SQL that will be passed to the database out of the input parameter received from the user. * * @author Clinton Begin */ public interface SqlSource { BoundSql getBoundSql(Object parameterObject); }如此，想要打印工程项目中所有sql并判断是否带有where条件就比较明晰了，直接上代码：
@Resource private SqlSessionFactory sqlSessionFactory; @Test public void test_check() { Configuration configuration = sqlSessionFactory.getConfiguration(); System.out.println("#sql.size#" + configuration.getMappedStatements().size()); Set<String> errors = Sets.newHashSet(); int i = 1; for (Object obj : configuration.getMappedStatements()) { if (obj instanceof MappedStatement) { MappedStatement mappedStatement = (MappedStatement) obj; String sql = mappedStatement.getSqlSource().getBoundSql(new SqlParamMap()).getSql(); sql = sql.replaceAll("\n", ""); sql = sql.replaceAll("\\s+", " "); System.out.println(String.format("#sql,#d #%s #%s", i++, mappedStatement.getSqlCommandType(), sql)); if (!sql.toLowerCase().startsWith("insert") && !sql.toLowerCase().startsWith("select") && !sql.toLowerCase().startsWith("replace")) { if (!sql.toLowerCase().contains("where")) { errors.add(sql); } } } } System.err.println("#error#" + errors.size()); for (String errorSql : errors) { System.err.println(errorSql); } } // 这里为了方便生成sql时，解析入参对象的 public static class SqlParamMap extends AbstractMap<String, Object> implements Map<String, Object> { @Override public Set<Entry<String, Object>> entrySet() { return Collections.emptySet(); } @Override public Object get(Object key) { return new Object[] {1, 2}; } }如此便可打印出不符合条件的sql语句了。比如在我们haitao-matter工程里搜索出：
#error#21 delete from mt_baby_article_config delete from mt_coupon_center_nav update mt_auction_goods set price_check_status = 4 delete from mt_spring_label delete from mt_scene_brand delete FROM mt_newgoods_content_configupdate mt_auction_goods_edit set price_check_status = 4 DELETE from mt_coupon_center_coupon_info delete from mt_spring_label_goods delete from mt_goods_stock_rel delete from mt_flash_sale_nav delete from mt_auction_homeshow_inferior delete from matter_switcher_param_center delete from mt_mission_award delete from `mt_newgoods_category_tab_config` delete from mt_goods_stock_rel_edit delete from mt_album_label delete from matter_app_channel_relations delete from element_user_baby_coupon_info_log delete from mt_album_label_category此外还能打印出工程所有sql出来，比如：
#sql,#1974 #DELETE #delete from mt_auction_homeshow_inferior_edit where id=? #sql,#1975 #SELECT #select id,title,begin_time,end_time,type,status,db_update_time,content from matter_common_schedule WHERE type =? and begin_time <=? and end_time >=? and type = ? order by begin_time limit ?,? #sql,#1976 #UPDATE #update element_user_baby_coupon_info_log SET award_info=?, create_time=?, update_time=? where id=? #sql,#1977 #SELECT #SELECT id from mt_auction_goods where show_status!=2 #sql,#1978 #INSERT #insert into TB_ACTIVITY_SHOW_DETAIL_EDIT (id, zone_id, activity_show_id, related_type, related_id, image_url, image_link, sort_order, config,ui_data ) values #sql,#1979 #SELECT #select id, related_id, goods_id, goods_title, category_id, category_name, advance_price, question_mark_pos, config, sort_order, db_create_time, db_update_time from mt_advance_price_goods_edit where id = ? #sql,#1980 #SELECT #select id, apply_category_id, apply_brand_id, import_type, db_create_time,db_update_time, status, goods_qa_scheme_edit_id from goods_qa_category_scope_edit where apply_category_id = ? and apply_brand_id = ? and import_type = ? and goods_qa_scheme_edit_id <> ? #sql,#1981 #SELECT #select id, skin_scheme_id, skin_order, skin_name, skin_introduce, skin_img_config, skin_gif_config, skin_status, operator, db_create_time, db_update_time from mt_private_custom_skin_config_edit where skin_status = ? order by skin_order asc如此，算是放心不会又遗漏了。整个思路简单直接，其中涉及到mybatis解析xml和生产动态sql的原理和过程的东西有待分析，这里先留个坑，日后来填。
Reference：
http://www.mybatis.org/mybatis-3/dynamic-sql.html
https://www.cnblogs.com/fangjian0423/p/mybaits-dynamic-sql-analysis.html
作者：网易工程师-李云鹏
www.toutiao.com/i6874790802107826700
精彩推荐SpringBoot内容聚合IntelliJ IDEA内容聚合Mybatis内容聚合 Java笔记虾 认证博客专家 Spring MySQL Spring Boot 欢迎微信搜索【Java笔记虾】关注我的公众号，号内回复“后端面试”，送你一份精心准备的Java面试题（提纲+解析），后端技术精选每天定时推送优质Java技术博客，可以琐碎时间学点儿东西