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>