ibatis中动态SQL_insert

最新推荐文章于 2025-07-16 12:59:41 发布

原创最新推荐文章于 2025-07-16 12:59:41 发布 · 1k 阅读

6 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

ibaits2.X 高级应用

本文介绍了ibatis中两种动态SQL的使用方法，一种是适用于所有字段的通用方式，另一种则是针对部分必填字段的特殊处理。通过具体示例展示了如何根据不同场景灵活运用动态SQL来优化数据库操作。

ibatis的动态SQL非常强大，记录一些细节的东西。

1.正常的动态INSERT，即全部遍历一次，这种是通用的,如下：

	<insert id="addUser" parameterClass="com.mmblue.domain.User">
		INSERT INTO USER (
		<dynamic prepend=" ">
					<isNotEmpty property="realName" prepend=",">REALNAME</isNotEmpty>
					<isNotEmpty property="password" prepend=",">PASSWORD</isNotEmpty>
					<isNotEmpty property="projectId" prepend=",">PROJECT_ID</isNotEmpty>
					<isNotEmpty property="isEable" prepend=",">ISENABLE</isNotEmpty>
					<isNotEmpty property="createTime" prepend=",">CREATETIME</isNotEmpty>
					<isNotEmpty property="name" prepend=",">USERNAME</isNotEmpty>
					<isNotEmpty property="id" prepend=",">USERID</isNotEmpty>
					<isNotEmpty property="isFirstLogin" prepend=",">ISFIRSTLOGIN</isNotEmpty>
		</dynamic>
		<![CDATA[
			) VALUES (
		]]>
		<dynamic prepend=" ">
					<isNotEmpty property="realName" prepend=",">#realName#</isNotEmpty>
					<isNotEmpty property="password" prepend=",">#password#</isNotEmpty>
					<isNotEmpty property="projectId" prepend=",">#projectId#</isNotEmpty>
					<isNotEmpty property="isEable" prepend=",">#isEable#</isNotEmpty>
					<isNotEmpty property="createTime" prepend=",">#createTime#</isNotEmpty>
					<isNotEmpty property="name" prepend=",">#name#</isNotEmpty>
					<isNotEmpty property="id" prepend=",">#id#</isNotEmpty>
					<isNotEmpty property="isFirstLogin" prepend=",">#isFirstLogin#</isNotEmpty>
		</dynamic>
		<![CDATA[
	    	)
	    ]]>
	</insert>

2.有的字段必须插入，不是采用这种全部判断的方式

	<insert id="addUser" parameterClass="com.mmblue.entity.system.User">
		<selectKey keyProperty="id" resultClass="long">
             select S_MYIVR_TUSER.NEXTVAL as id from DUAL
         </selectKey>
         INSERT INTO MYIVR_TUSER (
         	USERID
			<isNotEmpty property="name">,USERNAME</isNotEmpty>
			<isNotEmpty property="realName">,REALNAME</isNotEmpty>
			<isNotEmpty property="password">,PASSWORD</isNotEmpty>
			<isEqual property="enable" compareValue="1">,ISENABLE</isEqual>
			<isNotEmpty property="createTime">,CREATETIME</isNotEmpty>
		<![CDATA[
			) VALUES (
		]]>
			#id#
			<isNotEmpty property="name">,#name#</isNotEmpty>
			<isNotEmpty property="realName">,#realName#</isNotEmpty>
			<isNotEmpty property="password">,#password#</isNotEmpty>
			<isEqual property="enable" compareValue="1">,#enable#</isEqual>
			<isNotEmpty property="createTime">,#createTime#</isNotEmpty>
		<![CDATA[
	    	)
	    ]]>
	</insert>

注意 ,USERNAME 里面的','，和最后一个是不带' ,' 这样的话就算只有1个字段有值都可以插入进去。

如果和第一种方式一样的话就会出现INSERT INTO MYIVR_TUSER(USERID,,USERNAME) values(id,,name)这样的情况

标签

#iBATIS #SQL