mybati之day02

今天开始讲解mybatis的第二天内容

 一,拼接sql

在mapper.xml中,会多次使用到同一条sql片段,这时为了简便书写,将其定义出来

<sql id="base_sql">

 goods_name,

good_no,

goods_price,

goods_desc

</sql>

 

 

<select id="queryGoodsByGoodsName" resultMape="GoodsMap">

select <include refid="base_sql" /> from goods g where g.goods_name='${goodsName}'

</select>

 

 

  二,动态SQL

MyBatis 的一个强大的特性之一通常是它的动态 SQL 能力。提供了OGNL表达式动态生成SQL的功能。动态SQL有：

1、if

2、choose, when, otherwise

3、where, set

4、foreach

下面解释其用法:

if:

<if test="goodsNo!= null and goodsNo!= '' ">
     and end_time <![CDATA[ <= ]]> #{goodNo}
</if>

 

2、choose, when, otherwise

 

 

<select id="queryUserLikeNameOrAge" resultType="User">
      SELECT * FROM tb_user WHERE sex = 1 
    <choose>
       　 <when test="name != null and name !=''">
                AND name like '%${name}%'
           </when>
           <when test="age !=null and age != ''">
              AND age > #{age} 
           </when>
           <otherwise>
              AND created is not null
           </otherwise>
   </choose>
</select>

 

 

相当于java中 if  elseif 逻辑；

 

 

<select id="queryUserByIds" resultType="User">
  　　　　SELECT * FROM tb_user WHERE id IN 
　　　　　　 <foreach collection="ids" item="id" open="(" close=")" separator=",">
　　　　　　　　　　　　#{id}
　　　　　　 </foreach>
</select>

 

 

 

String orderId = request.getParameter("orderId");
String tcms = request.getParameter("tcms");
if (tcms != null && !"".equals(tcms)) {
EcTcMessage ecTcMessage = new EcTcMessage();
Integer userId = Integer.parseInt(request.getSession().getAttribute("userId").toString());
ecTcMessage.setEditUserId(userId);
ecTcMessage.setIds(tcms.split(","));
ecTcMessage.setCallBackUserId(userId.toString());
ecTcMessage.setCallBackTime(DateUtils.getCurrentDateString());
ecTcMessage.setIsAdvisory("Y");
//    ecTcMessage.setOrderId(orderId);
orderService.updateTcMessageIsBuy(ecTcMessage);
}
return "ok";

 

//ibatis  批量更新

传递参数是一个Model对象

<update id="update" parameterClass="ecTcMessage">
update ec_tc_message set
IS_ADVISORY =
'Y',CALLBACK_TIME=to_char(sysdate,'YYYY-MM-DD
HH24:mi:ss'),CALLBACK_USERID=#callBackUserId#,EDIT_USER_ID=#editUserId#,EDIT_TIME=sysdate
where TC_MESSAGE_ID in
<iterate property="ids" conjunction="," open="(" close=")">
#ids[]#
</iterate>
</update>

 

//批量更新

 <update id="update" parameterClass="PharmacistComment">
update PHARMACIST_COMMENT
<dynamic prepend="set">

<isNotEmpty prepend="," property="goodsNo">
          <![CDATA[ GOODS_NO=#goodsNo# ]]>
</isNotEmpty>

<isNotEmpty prepend="," property="isDelete">
          <![CDATA[ IS_DELETE=#isDelete# ]]>
</isNotEmpty>

<dynamic>
where PHARMACIST_COMMENT_ID=#pharmacistCommentId#
</update>

 

S

tring ids = null;
if (request.getParameter("ids") != null) {
ids = request.getParameter("ids").trim();

String[] idArr = ids.split(",");
for (int i = 0; i < idArr.length; i++) {
PharmacistComment bean = new PharmacistComment();
bean.setPharmacistCommentId(Long.parseLong(idArr[i]));
bean.setIsDelete("Y");
pharmacistCommentService.update(bean);

}
}

 

//注意:

这两个功能都是根据id批量更新,不同的是参数封装的不一样,

第一个id在java里是一个数组,是在sql里面进行遍历,

第二个是在java代码里遍历后就直接先设置为'Y'了,

但是两个最后的功能都是一样的