ibatis中sql xml的基础写法

ibatis中sql xml的基础写法

在工作中整理的ibatis sql配置文件的一些基础写法。

ibatis sql xml文件基本构成

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="USER">
</sqlMap>

ibatis sql 语句类型

分为select，insert，update，delete
通过namespace与id定位具体sql

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="USER">
	<select id="query" parameterClass="java.util.HashMap" resultClass="java.util.HashMap">
	</select>
	<insert id="insert">
	</insert>
	<update id="update">
	</update>
	<delete id="delete">
	</delete>
</sqlMap>

ibatis sql 语句入参类型设置

通过标签parameterClass设置sql入参类型
可以设置string，int，long等类型，一般项目中参数大多为多个，传入类型大多设置为map传入。故大部分情况下，入参设置为parameterClass=“java.util.HashMap”

ibatis sql 语句出参类型设置

用过标签resultClass设置出参
可以设置为String，int等多种参数，一般项目中，出参为多个。设置类型分为以下几种
一、设置为resultClass=“java.util.HashMap”

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="USER">
	<select id="query" parameterClass="java.util.HashMap" resultClass="java.util.HashMap">
		select USER_ID AS "userId",
				USER_NAME AS "userName",
				USER_AGE AS "userAge"
		from USER_INFO
	</select>
</sqlMap>

二、设置为固定pojo类返回，resultClass=“com.demo.domain.User”

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="USER">
	<select id="query" parameterClass="java.util.HashMap" resultClass="com.demo.domain.User">
		select USER_ID AS "userId",
				USER_NAME AS "userName",
				USER_AGE AS "userAge"
		from USER_INFO
	</select>
</sqlMap>

三、为固定pojo类设置别名，返回值可以设置为该别名

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="USER">

	<typeAlias type="com.demo.domain.User" alias="User" /> 
	
	<select id="query" parameterClass="java.util.HashMap" resultClass="User">
		select USER_ID AS "userId",
				USER_NAME AS "userName",
				USER_AGE AS "userAge"
		from USER_INFO
	</select>
</sqlMap>

四、显示映射设置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="USER">

	<resultMap id="User" class="com.demo.domain.User"> 
              <result column="USER_ID" property="userId" /> 
              <result column="USER_NAME" property="userName" /> 
              <result column="USER_AGE" property="userAge" /> 
       </resultMap> 
	
	<select id="query" parameterClass="java.util.HashMap" resultClass="User">
		select USER_ID AS "userId",
				USER_NAME AS "userName",
				USER_AGE AS "userAge"
		from USER_INFO
	</select>
</sqlMap>

ibatis sql 接收参数

ibatis sql 参数接收分为，## 和 $$两种
##会进行类型匹配，$$不会进行类型匹配
例：

<select id="query" parameterClass="java.util.HashMap" resultClass="java.util.HashMap">
		select USER_ID AS "userId",
				USER_NAME AS "userName",
				USER_AGE AS "userAge"
		from USER_INFO
		where 
			USER_ID = #userId#
</select>

<select id="query" parameterClass="java.util.HashMap" resultClass="java.util.HashMap">
		select USER_ID AS "userId",
				USER_NAME AS "userName",
				USER_AGE AS "userAge"
		from USER_INFO
		where 
			USER_ID = '$userId$'
</select>

ibatis其他常用标签

isNull判断传入值是否为null
例：

<select id="query" parameterClass="java.util.HashMap" resultClass="java.util.HashMap">
		select USER_ID AS "userId",
				USER_NAME AS "userName",
				USER_AGE AS "userAge"
		from USER_INFO
		where 
			USER_ID = '$userId$'
			<isNull prepend="and" property="userName">
				USER_NAME = #userName#
			</isNull> 
</select>

isEmpty判断传入值是否为空
例：

<select id="query" parameterClass="java.util.HashMap" resultClass="java.util.HashMap">
		select USER_ID AS "userId",
				USER_NAME AS "userName",
				USER_AGE AS "userAge"
		from USER_INFO
		where 
			USER_ID = '$userId$'
			<isEmpty prepend="and" property="userName">
				USER_NAME = #userName#
			</isEmpty> 
</select>

isEqual比较传入值
例：

<select id="query" parameterClass="java.util.HashMap" resultClass="java.util.HashMap">
		select USER_ID AS "userId",
				USER_NAME AS "userName",
				USER_AGE AS "userAge"
		from USER_INFO
		where 
			USER_ID = '$userId$'
			<isEqual isEqual property="state" compareValue="00">
				USER_AGE = #userAge#
			</isEqual > 
</select>

Iterate循环
例：

<select id="query" parameterClass="java.util.HashMap" resultClass="java.util.HashMap">
		select USER_ID AS "userId",
				USER_NAME AS "userName",
				USER_AGE AS "userAge"
		from USER_INFO
		where 
			USER_ID = '$userId$'
			<iterate prepend="AND" property="userNameList" open="(" close=")" conjunction="OR">   
    			USER_NAME = #userNameList[]#   
			</iterate> 
</select>

dynamic删除代码块中的第一个配置的prepend值
例：

<select id="query" parameterClass="java.util.HashMap" resultClass="java.util.HashMap">
		select USER_ID AS "userId",
				USER_NAME AS "userName",
				USER_AGE AS "userAge"
		from USER_INFO
		where 
		<dynamic prepend="and">
			<isEmpty prepend="and" property="userName">
				USER_ID = #userId#
			</isEmpty> 
			<isEmpty prepend="and" property="userName">
				USER_NAME = #userName#
			</isEmpty> 
		</dynamic>
</select>