一. if判断语句
用法:可以在SQL语句下加if标签用于判断
xml
<!-- if判断 -->
<select id="selectif" resultMap="BillProviderResult">
SELECT sb.id,sb.billCode,sb.productName,sp.id,sp.proName,sb.totalPrice,sb.isPayment,sb.creationDate
FROM smbms_bill sb,smbms_provider sp WHERE sb.id=sp.id
<if test="id!=null and id !=0">
and sb.id=#{id}
</if>
<if test="isPayment=1 and isPayment=2">
and sb.isPayment=#{isPayment}
</if>
<if test="productName!=null and productName!=''">
and sb.productName Like CONCAT(#{productName},'%')
</if>
</select>
接口
//if查询
List<Bill> selectif(@Param("id")int id,@Param("isPayment")int isPayment,@Param("productName")String productName);
二. if - where
用法:可以在SQL语句下加<where>标签用于添加条件,在里面嵌套if进行判断
xml
<!-- if where -->
<select id="selectifwhere" resultMap="ProviderBillResult">
SELECT id,proCode,proName,proContact,proPhone,proAddress,creationDate FROM
smbms_provider
<where>
<if test="proCode!=null and proCode!=''">
and proCode Like CONCAT(#{proCode},'%')
</if>
<if test="proName!=null and proName!=''">
and proName Like CONCAT(#{proName},'%')
</if>
</where>
</select>
接口
//if where
List<Provider> selectifwhere(@Param("proCode")String proCode,@Param("proName")String proName);
三. if set
用法:可以在SQL修改语句下加<if>标签用于添加条件判断
xml:
<!-- if+set -->
<update id="updateifset" parameterType="com.entity.Provider">
UPDATE smbms_provider
<set>
<if test="proName!=null and proName!=''">
proName =#{proName}
</if>
</set>
WHERE id=#{id}
</update>
接口:
//if set
int updateifset(Provider provider);
四. trim
用法:用于查询或则修改语句,<trim prefix="set">可以设置参数
xml
更改:
<!-- trim -->
<update id="updatetrim" parameterType="com.entity.Provider">
UPDATE smbms_provider
<trim prefix="set">
<if test="proName!=null and proName!=''">
proName =#{proName}
查询:
<!-- if trim -->
<select id="selectIfTrim" resultType="com.entity.Role" parameterType="String">
SELECT * FROM smbms_role
<trim prefix="where" >
<if test="roleName!=null and roleName!=''">
roleName LIKE CONCAT ('%',#{roleName})
</if>
</trim>
LIMIT 0,2
</if>
</trim>
WHERE id=#{id}
</update>
接口
//trim
int updatetrim(Provider provider);
五. forEach
用于:SELECT * FROM smbms_provider WHERE id IN 语句
<!-- forEach 数组-->
<select id="selectForEach" resultMap="ProviderBillResult">
SELECT * FROM smbms_provider WHERE id IN
<foreach collection="array" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</select>
<!-- forEach list集合-->
<select id="selectForEach2" resultMap="ProviderBillResult">
SELECT * FROM smbms_provider WHERE id IN
<foreach collection="list" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</select>
<!-- forEach Map入参-->
<select id="selectForEach3" resultMap="ProviderBillResult">
SELECT * FROM smbms_provider WHERE id IN
<foreach collection="Map" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</select>
//forEach 数组
List<Provider> selectForEach(String[] id);
//forEach 集合
List<Provider> selectForEach2(List<String> list);
//forEach Map
List<Provider> selectForEach3(Map<String,Object> map);
六. Choose 分支
<!-- Choose 分支-->
<select id="selectChoose" resultMap="ProviderBillResult">
SELECT id,proCode,proName,proContact,creationDate FROM smbms_provider WHERE 1=1
<choose>
<when test="proCode!=null and proCode!=''">
and proCode Like CONCAT('%',#{proCode},'%')
</when>
<when test="proName!=null and proName!=''">
and proName Like CONCAT('%',#{proCode},'%')
</when>
<when test="proContact!=null andproContact!=''">
and proContact Like CONCAT('%',#{proContact},'%')
</when>
<otherwise>
creationDate>'2014-00-00'
</otherwise>
</choose>
//Choose
List<Provider> selectChoose(@Param("proCode")String proCode,@Param("proName")String proName,
@Param("proContact")String proContact,@Param("creationDate")Date creationDate);
七. 分页
<!-- 分页 -->
<select id="selectLimit" resultMap="ProviderBillResult">
SELECT * FROM smbms_provider ORDER BY creationDate DESC LIMIT #{page1},#{page2}
</select>
//limit分页
List<Provider> selectLimit(@Param("page1")int page1,@Param("page2")int page2);