ibatis使用in查询加引号

最近在工作中碰到了一个问题： 同样的查询语句，使用in进行查询时，参数加引号和不加引号效率相差很多。现在记录一下解决过程。

查询语句为：

select s.id            as id,
       s.skuid         as skuId,
       s.product_no    as productNo,
       s.storage       as storage,
       s.available_qty as availableQty,
       s.freeze_qty    as freezeQty,
       s.update_time   as updateTime
  from store_bad_stock s
 where s.product_no in
       (0009724043,
        0009724054,
        0009724065, ... )

这是不加引号的查询。 product_no是varchar类型。如果加上引号，查询时会使用到这一列上的索引，查询效率会高很多。下面是使用“explain”命令分析后的结果对比图：

可以看到不实用引号时，会全表检索，rows是整个表的记录数，当然慢了。

那在ibatis的配置文件中，该如何设置呢？

配置文件保持不变：

<select id="queryBadStockByProduct" parameterClass="badStock" resultClass="badStock">
		select <include refid="badStockFields"/> from store_bad_stock s
		<dynamic prepend="where">
			<isNotEmpty property="productNo" prepend="and">
				s.product_no = #productNo#
			</isNotEmpty>
			<isNotEmpty property="productNos" prepend="and">
				s.product_no in ($productNos$)
			</isNotEmpty>
			<isNotEmpty property="storage" prepend="and">
				s.storage = #storage#
			</isNotEmpty>
			<isNotEmpty property="skuIds" prepend="and">
                s.skuid in ($skuIds$)
            </isNotEmpty>
		</dynamic>
	</select>

在调用的地方，需要转一下参数格式：

    @Override
	public List<BadStock> queryBadStockByProduct(BadStock badStock) {
		badStock.setProductNos(QueryUtil.convertToVarcharFormat(badStock.getProductNos()));
		
		return (List<BadStock>) this.getSqlMapClientTemplate().queryForList("badstock.queryBadStockByProduct", badStock);
	}

QueryUtil.convertToVarcharFormat 方法：

	/**
	 * 将 [1,2,3] 格式数据 转变为 ['1','2','3']格式，便于sql中varchar类型字段查询时使用索引
	 * 
	 * @param strWithoutQuote
	 * @return strWithQuoteStr
	 */
	public static String convertToVarcharFormat(String strWithoutQuote) {
		if (strWithoutQuote == null || "".equals(strWithoutQuote)) {
			return "";
		}

		String[] productNoArray = strWithoutQuote.split(",");
		StringBuffer newStrWithQuoteStringBuffer = new StringBuffer();

		for (String str : productNoArray) {
			newStrWithQuoteStringBuffer.append("'").append(str).append("',");
		}

		String strWithQuote = newStrWithQuoteStringBuffer.toString();
		// 截掉最后的一个“,”
		strWithQuote = strWithQuote.substring(0, strWithQuote.length() - 1);
		return strWithQuote;
	}

打印出来ibatis的查询sql，是这样的：

附：

让ibatis打印执行sql的log4j配置

log4j.rootCategory = debug,stdout 
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d %p [%c] - %m%n


log4j.logger.com.ibatis=debug
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=debug
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=debug
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=debug
log4j.logger.java.sql.Connection=debug
log4j.logger.java.sql.Statement=debug
log4j.logger.java.sql.PreparedStatement=debug,stdout