谈谈IBatis的动态SQL应该改进的架构设计！

foreach很有用,再说一个的另类用法:(用过MybatisGenerator的人就不用看了.)

将传入的条件添加到一个List里面,通过foreach输出,配合<where>标签就可以生成一个针对于此对象的通用条件块.

<sql id="generalCondition">
<if test="parameter.condition.size()!=0">
<foreach....>
<where>......
</foreach>
</if>
</sql>

那么我们可以写出:

<select...>
select * from A
<include id="generalCondition"/>
</select>
<update...>
update ....
<include id="generalCondition"/>
</update>
<delete>........

血的教训
1.不要在sql上部署复杂逻辑
2.用缓存(不要用Mybatis自带的，问题多多)

另外，试试MyBatis提供的自动生成工具，里边的查询方式还是不错的

强类型转换这点 不是很同意你的看法  看了你的代码做出一个判断 我想问前提是不是要判断是否为空？ 有句号说得好一切输入是有害的    你说的ibatis不支持强类型  我想说 判断是条件是传给数据库的做处理的  没必要做一次类型转换 如果你传整型 用$int$ 传到数据库就不会有引号 。之前项目中 用ibatis有sql注入的问题 后面是在java层做了处理解决了 

今天才学习的ibatis,感觉还不错

能提供个Ibatis的OGNL资料吗？官方文档上没找到

现在最新的是mybatis，我参加的上一个项目就是，但是可参考的文档不是很多，有的时候研究一个写法还是比较费劲的，这是我个人的感觉，不过用起来确实很灵活。

我们现在正在用的这个，拿我们项目中的一个xml文件说明

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">


<!-- 不使用namespace的话sql搜索定位会比较方便 -->
<mapper namespace="Company">

	<resultMap id="RM.Company" type="com.bhaman.yiyaosou.model.Company">
        <result property="cmpId" column="cmp_id"/>
        <result property="usrId" column="usr_id"/>
        <result property="cmpName" column="cmp_name"/>
        <result property="cmpIdentity" column="cmp_identity"/>
        <result property="cmpType" column="cmp_type"/>
        <result property="cmpArea" column="cmp_area"/>
        <result property="cmpBusiness" column="cmp_business"/>
        <result property="cmpMainProduct" column="cmp_main_product"/>
        <result property="cmpOperateMode" column="cmp_operate_mode"/>
        <result property="cmpIntro" column="cmp_intro"/>
        <result property="cmpPic" column="cmp_pic"/>
        <result property="cmpUrl" column="cmp_url"/>
        <result property="cmpEmployee" column="cmp_employee"/>
        <result property="cmpAnnualTurnover" column="cmp_annual_turnover"/>
        <result property="cmpMainClient" column="cmp_main_client"/>
        <result property="cmpMainBrand" column="cmp_main_brand"/>
        <result property="cmpRegCapital" column="cmp_reg_capital"/>
        <result property="cmpBankName" column="cmp_bank_name"/>
        <result property="cmpBankAccount" column="cmp_bank_account"/>
        <result property="cmpLicense" column="cmp_license"/>
        <result property="cmpLegalMan" column="cmp_legal_man"/>
        <result property="cmpRspMan" column="cmp_rsp_man"/>
        <result property="cmpQualityMan" column="cmp_quality_man"/>
        <result property="cmpRegAddr" column="cmp_reg_addr"/>
        <result property="cmpPrdAddr" column="cmp_prd_addr"/>
        <result property="cmpDepotAddr" column="cmp_depot_addr"/>
        <result property="cmpDepotAcreage" column="cmp_depot_acreage"/>
        <result property="cmpRegDate" column="cmp_reg_date"/>
        <result property="cmpEndDate" column="cmp_end_date"/>
        <result property="cmpAuthArea" column="cmp_auth_area"/>
        <result property="comContactMan" column="com_contact_man"/>
        <result property="cmpPhone" column="cmp_phone"/>
        <result property="cmpFax" column="cmp_fax"/>
        <result property="cmpEmail" column="cmp_email"/>
        <result property="cmpAddr" column="cmp_addr"/>
        <result property="cmpZip" column="cmp_zip"/>
        <result property="cmpComment" column="cmp_comment"/>
        <result property="cmpAttention" column="cmp_attention"/>
        <result property="cmpStatus" column="cmp_status"/>
        <result property="cmpCreated" column="cmp_created"/>
	</resultMap>

	<!-- 用于select查询公用抽取的列 -->
	<sql id="Company.columns">
	    <![CDATA[
		cmp_id,usr_id,cmp_name,cmp_identity,cmp_type,cmp_area,cmp_business,cmp_main_product,cmp_operate_mode,cmp_intro,cmp_pic,cmp_url,cmp_employee,cmp_annual_turnover,cmp_main_client,cmp_main_brand,cmp_reg_capital,cmp_bank_name,cmp_bank_account,cmp_license,cmp_legal_man,cmp_rsp_man,cmp_quality_man,cmp_reg_addr,cmp_prd_addr,cmp_depot_addr,cmp_depot_acreage,cmp_reg_date,cmp_end_date,cmp_auth_area,com_contact_man,cmp_phone,cmp_fax,cmp_email,cmp_addr,cmp_zip,cmp_comment,cmp_attention,cmp_status,cmp_created
	    ]]>
	</sql>

	<!-- useGeneratedKeys="true" keyProperty="xxx" for sqlserver and mysql -->
	<insert id="Company.insert" useGeneratedKeys="true" keyProperty="cmpId">
    <![CDATA[
        INSERT INTO company (
        	cmp_id ,
        	usr_id ,
        	cmp_name ,
        	cmp_identity ,
        	cmp_type ,
        	cmp_area ,
        	cmp_business ,
        	cmp_main_product ,
        	cmp_operate_mode ,
        	cmp_intro ,
        	cmp_pic ,
        	cmp_url ,
        	cmp_employee ,
        	cmp_annual_turnover ,
        	cmp_main_client ,
        	cmp_main_brand ,
        	cmp_reg_capital ,
        	cmp_bank_name ,
        	cmp_bank_account ,
        	cmp_license ,
        	cmp_legal_man ,
        	cmp_rsp_man ,
        	cmp_quality_man ,
        	cmp_reg_addr ,
        	cmp_prd_addr ,
        	cmp_depot_addr ,
        	cmp_depot_acreage ,
        	cmp_reg_date ,
        	cmp_end_date ,
        	cmp_auth_area ,
        	com_contact_man ,
        	cmp_phone ,
        	cmp_fax ,
        	cmp_email ,
        	cmp_addr ,
        	cmp_zip ,
        	cmp_comment ,
        	cmp_attention ,
        	cmp_status ,
        	cmp_created
        ) VALUES (
        	#{cmpId} ,
        	#{usrId} ,
        	#{cmpName} ,
        	#{cmpIdentity} ,
        	#{cmpType} ,
        	#{cmpArea} ,
        	#{cmpBusiness} ,
        	#{cmpMainProduct} ,
        	#{cmpOperateMode} ,
        	#{cmpIntro} ,
        	#{cmpPic} ,
        	#{cmpUrl} ,
        	#{cmpEmployee} ,
        	#{cmpAnnualTurnover} ,
        	#{cmpMainClient} ,
        	#{cmpMainBrand} ,
        	#{cmpRegCapital} ,
        	#{cmpBankName} ,
        	#{cmpBankAccount} ,
        	#{cmpLicense} ,
        	#{cmpLegalMan} ,
        	#{cmpRspMan} ,
        	#{cmpQualityMan} ,
        	#{cmpRegAddr} ,
        	#{cmpPrdAddr} ,
        	#{cmpDepotAddr} ,
        	#{cmpDepotAcreage} ,
        	#{cmpRegDate} ,
        	#{cmpEndDate} ,
        	#{cmpAuthArea} ,
        	#{comContactMan} ,
        	#{cmpPhone} ,
        	#{cmpFax} ,
        	#{cmpEmail} ,
        	#{cmpAddr} ,
        	#{cmpZip} ,
        	#{cmpComment} ,
        	#{cmpAttention} ,
        	#{cmpStatus} ,
        	#{cmpCreated}
        )
    ]]>
		<!--
			oracle: order="BEFORE" SELECT sequenceName.nextval AS ID FROM DUAL
			DB2: order="BEFORE"" values nextval for sequenceName
		<selectKey resultType="java.lang.Long" order="BEFORE" keyProperty="userId">
			SELECT sequenceName.nextval AS ID FROM DUAL
        </selectKey>
		-->
	</insert>

	<update id="Company.update" >
        UPDATE company SET
	       <if test="@Ognl@isNotEmpty(usrId)">
				 usr_id = #{usrId},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpName)">
				 cmp_name = #{cmpName},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpIdentity)">
				 cmp_identity = #{cmpIdentity},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpArea)">
				 cmp_area = #{cmpArea},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpBusiness)">
				 cmp_business = #{cmpBusiness},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpMainProduct)">
				 cmp_main_product = #{cmpMainProduct},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpOperateMode)">
				 cmp_operate_mode = #{cmpOperateMode},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpIntro)">
				 cmp_intro = #{cmpIntro},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpPic)">
				 cmp_pic = #{cmpPic},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpUrl)">
				 cmp_url = #{cmpUrl},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpEmployee)">
				 cmp_employee = #{cmpEmployee},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpAnnualTurnover)">
				 cmp_annual_turnover = #{cmpAnnualTurnover},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpMainClient)">
				 cmp_main_client = #{cmpMainClient},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpMainBrand)">
				 cmp_main_brand = #{cmpMainBrand},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpRegCapital)">
				 cmp_reg_capital = #{cmpRegCapital},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpBankName)">
				 cmp_bank_name = #{cmpBankName},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpBankAccount)">
				 cmp_bank_account = #{cmpBankAccount},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpLicense)">
				 cmp_license = #{cmpLicense},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpLegalMan)">
				 cmp_legal_man = #{cmpLegalMan},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpRspMan)">
				 cmp_rsp_man = #{cmpRspMan},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpQualityMan)">
				 cmp_quality_man = #{cmpQualityMan},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpRegAddr)">
				AND cmp_reg_addr = #{cmpRegAddr},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpPrdAddr)">
				 cmp_prd_addr = #{cmpPrdAddr},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpDepotAddr)">
				 cmp_depot_addr = #{cmpDepotAddr},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpDepotAcreage)">
				 cmp_depot_acreage = #{cmpDepotAcreage},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpRegDate)">
				 cmp_reg_date = #{cmpRegDate},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpEndDate)">
				 cmp_end_date = #{cmpEndDate},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpAuthArea)">
				 cmp_auth_area = #{cmpAuthArea},
			</if>
	       <if test="@Ognl@isNotEmpty(comContactMan)">
				 com_contact_man = #{comContactMan},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpPhone)">
				 cmp_phone = #{cmpPhone},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpFax)">
				cmp_fax = #{cmpFax},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpEmail)">
				 cmp_email = #{cmpEmail},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpAddr)">
				 cmp_addr = #{cmpAddr},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpZip)">
				 cmp_zip = #{cmpZip},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpComment)">
			 	cmp_comment = #{cmpComment},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpAttention)">
				cmp_attention = #{cmpAttention},
			</if>
	       <if test="@Ognl@isNotEmpty(cmpStatus)">
				 cmp_status = #{cmpStatus},
			</if>


		   cmp_type = #{cmpType}
        WHERE
	        cmp_id = #{cmpId}

	</update>

    <delete id="Company.delete">
    <![CDATA[
        DELETE FROM company WHERE
        cmp_id = #{id}
    ]]>
    </delete>

    <select id="Company.getById" resultMap="RM.Company">
		SELECT <include refid="Company.columns" />
	    <![CDATA[
		    FROM company
	        WHERE
		        cmp_id = #{id}
	    ]]>
	</select>

	<sql id="Company.findPage.where">
		<!-- ognl访问静态方法的表达式 为@class@method(args),以下为调用rapid中的Ognl.isNotEmpty()方法,还有其它方法如isNotBlank()可以使用，具体请查看Ognl类 -->
		<where>
	       <if test="@Ognl@isNotEmpty(cmpId)">
				AND cmp_id = #{cmpId}
			</if>
	       <if test="@Ognl@isNotEmpty(usrId)">
				AND usr_id = #{usrId}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpName)">
				AND cmp_name = #{cmpName}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpIdentity)">
				AND cmp_identity = #{cmpIdentity}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpType)">
				AND cmp_type = #{cmpType}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpArea)">
				AND cmp_area = #{cmpArea}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpBusiness)">
				AND cmp_business = #{cmpBusiness}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpMainProduct)">
				AND cmp_main_product = #{cmpMainProduct}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpOperateMode)">
				AND cmp_operate_mode = #{cmpOperateMode}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpIntro)">
				AND cmp_intro = #{cmpIntro}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpPic)">
				AND cmp_pic = #{cmpPic}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpUrl)">
				AND cmp_url = #{cmpUrl}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpEmployee)">
				AND cmp_employee = #{cmpEmployee}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpAnnualTurnover)">
				AND cmp_annual_turnover = #{cmpAnnualTurnover}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpMainClient)">
				AND cmp_main_client = #{cmpMainClient}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpMainBrand)">
				AND cmp_main_brand = #{cmpMainBrand}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpRegCapital)">
				AND cmp_reg_capital = #{cmpRegCapital}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpBankName)">
				AND cmp_bank_name = #{cmpBankName}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpBankAccount)">
				AND cmp_bank_account = #{cmpBankAccount}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpLicense)">
				AND cmp_license = #{cmpLicense}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpLegalMan)">
				AND cmp_legal_man = #{cmpLegalMan}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpRspMan)">
				AND cmp_rsp_man = #{cmpRspMan}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpQualityMan)">
				AND cmp_quality_man = #{cmpQualityMan}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpRegAddr)">
				AND cmp_reg_addr = #{cmpRegAddr}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpPrdAddr)">
				AND cmp_prd_addr = #{cmpPrdAddr}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpDepotAddr)">
				AND cmp_depot_addr = #{cmpDepotAddr}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpDepotAcreage)">
				AND cmp_depot_acreage = #{cmpDepotAcreage}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpRegDate)">
				AND cmp_reg_date = #{cmpRegDate}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpEndDate)">
				AND cmp_end_date = #{cmpEndDate}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpAuthArea)">
				AND cmp_auth_area = #{cmpAuthArea}
			</if>
	       <if test="@Ognl@isNotEmpty(comContactMan)">
				AND com_contact_man = #{comContactMan}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpPhone)">
				AND cmp_phone = #{cmpPhone}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpFax)">
				AND cmp_fax = #{cmpFax}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpEmail)">
				AND cmp_email = #{cmpEmail}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpAddr)">
				AND cmp_addr = #{cmpAddr}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpZip)">
				AND cmp_zip = #{cmpZip}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpComment)">
				AND cmp_comment = #{cmpComment}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpAttention)">
				AND cmp_attention = #{cmpAttention}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpStatus)">
				AND cmp_status = #{cmpStatus}
			</if>
	       <if test="@Ognl@isNotEmpty(cmpCreatedBegin)">
				AND cmp_created >= #{cmpCreatedBegin}
		   </if>
		   <if test="@Ognl@isNotEmpty(cmpCreatedEnd)">
				AND cmp_created &lt;= #{cmpCreatedEnd}
		   </if>
		</where>
	</sql>

    <select id="Company.findPage.count" resultType="long">
        SELECT count(*) FROM company
		<include refid="Company.findPage.where"/>
    </select>

    <!--
    	分页查询已经使用Dialect进行分页,也可以不使用Dialect直接编写分页
    	因为分页查询将传 offset,pageSize,lastRows 三个参数,不同的数据库可以根于此三个参数属性应用不同的分页实现
    -->
    <select id="Company.findPage" resultMap="RM.Company">
    	SELECT <include refid="Company.columns" />
	    FROM company
		<include refid="Company.findPage.where"/>

		<if test="@Ognl@isNotEmpty(sortColumns)">
			ORDER BY ${sortColumns}
		</if>
    </select>

	<!-- 手动配置 -->
	<select id="queryCmpPicByCmpId" resultType="java.lang.String">
		select cmp_pic from company where cmp_id = #{cmpId}
	</select>

	<select id="queryCmpPicByUserId" resultType="java.lang.String">
		select cmp_pic from company where usr_id = #{usrId}
	</select>

	<select id="queryCmpIDByUserId" resultType="java.lang.Integer">
		select cmp_id from company where usr_id = #{usrId}
	</select>

	<select id="queryCmpStatusById" resultType="java.lang.Integer">
		select cmp_status from company where cmp_id = #{cmpId}
	</select>

	<update id="update_status_by_cmpID">
		update company set cmp_status = #{cmpStatus} where cmp_id = #{cmpId}
	</update>

	<update id="update_cmppic_by_cmpid">
		update company set cmp_pic = #{cmpPic} where cmp_id = #{cmpId}
 </update>

	<select id="query_CompanyInfo_By_UserId" resultMap="RM.Company">
		SELECT
		<include refid="Company.columns" />
	    <![CDATA[
		    FROM company
	        WHERE
		       usr_id = #{usrId}
	    ]]>
	</select>

	<select id="findByRand" resultMap="RM.Company">
		SELECT
		<include refid="Company.columns" />
	    <![CDATA[
		    FROM company
		    ORDER BY rand()
		    limit #{limit}
	    ]]>
	</select>

    <select id="getByCmpName" resultMap="RM.Company">
		SELECT <include refid="Company.columns" />
	    <![CDATA[
		    FROM company
	        WHERE
		        cmp_name = #{cmpName}
	    ]]>
	</select>
</mapper>

上面的@Ognl@isNotEmpty是什么情况？
我一点击跳转到

public class Ognl {
	
	/**
	 * 可以用于判断String,Map,Collection,Array是否为空
	 * @param o
	 * @return
	 */
	public static boolean isEmpty(Object o) throws IllegalArgumentException {
		if(o == null) return true;

		if(o instanceof String) {
			if(((String)o).length() == 0){
				return true;
			}
		} else if(o instanceof Collection) {
			if(((Collection)o).isEmpty()){
				return true;
			}
		} else if(o.getClass().isArray()) {
			if(Array.getLength(o) == 0){
				return true;
			}
		} else if(o instanceof Map) {
			if(((Map)o).isEmpty()){
				return true;
			}
		}else {
			return false;
//			throw new IllegalArgumentException("Illegal argument type,must be : Map,Collection,Array,String. but was:"+o.getClass());
		}

		return false;
	}
	
	/**
	 * 可以用于判断 Map,Collection,String,Array是否不为空
	 * @param c
	 * @return
	 */	
	public static boolean isNotEmpty(Object o) {
		return !isEmpty(o);
	}
	
	public static boolean isNotBlank(Object o) {
		return !isBlank(o);
	}
	
	public static boolean isNumber(Object o) {
		if(o == null) return false;
		if(o instanceof Number) {
			return true;
		}
		if(o instanceof String) {
			String str = (String)o;
			if(str.length() == 0) return false;
			if(str.trim().length() == 0) return false;
			
			try {
				Double.parseDouble(str);
				return true;
			}catch(NumberFormatException e) {
				return false;
			}
		}
		return false;
	}
	
	public static boolean isBlank(Object o) {
		if(o == null)
			return true;
		if(o instanceof String) {
			String str = (String)o;
			return isBlank(str);
		}
		return false;
	}

	public static boolean isBlank(String str) {
		if(str == null || str.length() == 0) {
			return true;
		}
		
		for (int i = 0; i < str.length(); i++) {
			if (!Character.isWhitespace(str.charAt(i))) {
				return false;
			}
		}
		return true;
	}
}

我对xml里面使用这种找相关资料也没找到，官方的MyBatis 3 User Guide Simplified Chinese.pdf，说的太过于简单，这方面根本就没

强类型可以完全避免注入，如对于int，在sql执行时，将会执行：

preparedStatment.setInt(位置, Integer.parseInt(参数的值)) ;

如果传入的内容非法，在转换中就会报错，不是sql拼接的那种。

楼上的楼上 <update id="Company.update" >  这个更新语句不会有问题么 = = 如果更新某部分数据为空时不是不起作用了。

用velocity倒是挺有意思的一个想法。。。