mybatis3学习笔记

API:

http://ibatisnet.sourceforge.net/DevGuide.html

1、配置数据源时可以通过properties文件来获取，具体如下来配置 

 <properties resource="db.properties">

<!-- <property name="driver" value="oracle.driver"/>

  <property name="url" value="oracle.url"/>

  <property name="username" value="oracle.username"/>

  <property name="password" value="oracle.password"/> -->

 </properties>

 

 <environments default="development">

   <environment id="development">

    <transactionManager type="JDBC"/>

    <dataSource type="UNPOOLED">

     <property name="driver" value="${oracle.driver}" />

     <property name="url" value="${oracle.url}" />

     <property name="username" value="${oracle.username}" />

     <property name="password" value="${oracle.password}" />

    </dataSource>

   </environment>

   

 </environments>

2.参数传递List以及返回List

 public List<String> queryExistVipMsg(List<SmsInfo> smsInfos) {

  return this.getSqlSession().selectList("com.deppon.ump.module.Interface.shared.domain.SmsInfo.queryExistVipMsg", smsInfos);

 }

 <!-- 查询走VIP通道的业务类型 -->

 <select id="queryExistVipMsg" resultType="String"

  parameterType="java.util.List">

  SELECT T.CONTENT||T.TELEPHONE||T.SERVICE_TYPE

  FROM

  T_UMP_SOURCE_VIP_TEMP T

  WHERE

  <foreach collection=" list" item="item" open="(" separator="or" close=")">

  T.CONTENT = #{item.msgContent}

  and T.TELEPHONE = #{item.mobile}

  and T.SERVICE_TYPE = #{item.serviceType}

     </foreach>

  and t.insert_time > (sysdate - 1/1440)

 </select>

-- 上面foreach中collection必须为list而不是smsInfos，另外 separator可以为or也可以为逗号

3.IBATIS javaType

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "   http://mybatis.org/dtd/mybatis-3-mapper.dtd" >  
<mapper namespace="student">  

<select id="select" resultType="student" resultMap="studentMap">  

select  
stu.*,g.fname g_name, t.fname t_name  
from t_hi_student stu  
left join t_hi_grades g on  
g.fid = stu.fgradeid  
left join t_hi_term t on  
t.fid = stu.ftermid  
<where>  
<if test="number != null and number != '' ">  
and stu.fnum = #{number}  
</if>  

<if test="number == null or number == ''">  

<!-- 其次是根据姓名以及其他条件查询-->
<if test="name != null and name != ''">  
and stu.fname = #{name}  
</if>  
<if test="queryType != null and queryType != '' ">  
<!--查询该月新进学员 -->  
<if test="queryType == 'new' ">  
<![CDATA[  
and stu.fcreateTime >= #{beginTime}  
and stu.fcreateTime < #{endTime}  
and stu.fiseffective = 1  
and stu.fgradeid = #{grade.id}  
]]>  
</if>  

<!--查询该月退学学员 -->  
<if test="queryType == 'out' ">  
<![CDATA[  
and stu.flastUpdateTime >= #{beginTime}  
and stu.flastUpdateTime < #{endTime}  
and stu.fiseffective = 0  
and stu.fgradeid = #{grade.id}  
]]>  
</if>  

<!--查询该月在校学员 -->  
<if test="queryType == 'in' ">  
<![CDATA[  
and stu.fcreateTime >= t.fbegintime  
and stu.fcreateTime < #{endTime}  
and t.fid = #{term.id}  
and stu.fiseffective = 1  
and stu.fgradeid = #{grade.id}  
]]>  
</if>  
</if>  

</if>  

</where>  

</select>  

<insert id="insert" parameterType="student">  
insert into t_hi_student(fid,  
fname, fnum, fcreateTime, fdescribe, fiseffective,  
faddress,  
fcellphone, fgenearchName, frelation, fage, fgender,  
fintime, fgradeid, ftermid)  
values  
(  
uuid(), #{name}, #{number}, now(), #{describe}, 1,  
#{address}, #{cellphone}, #{genearchName}, #{relation}, #{age},  
#{gender},  
#{intime},#{grade.id}, #{term.id}  
)  
</insert>  

<!-- 作废-->  
<update id="repeal" parameterType="student">  
update t_hi_student t set t.fiseffective = 0 where t.fid = #{id}  
</update>  

<update id="update" parameterType="student">  
<![CDATA[  
update t_hi_student t  
]]>  
<set>  
t.fname = #{name},  
t.flastUpdateTime = now(),  
t.fiseffective =  
#{iseffective},  
t.fage = #{age},  
t.fgender = #{gender},  
t.fintime =  
#{intime},  
t.fgradeid = #{grade.id},  
t.fcellphone = #{cellphone},  
t.faddress = #{address},  
t.fgenearchName = #{genearchName},  
t.frelation = #{relation},  

<if test="describe != null and describe != '' ">  
t.fdescribe = #{describe},  
</if>  
</set>  
<where>  
t.fnum = #{number} and t.fiseffective = 1  
</where>  
</update>  

<!-- 数据库字段和Bean对象字段映射 -->  
<resultMap id="studentMap" type="student">  
<result property="id" column="fid" javaType="String" />  
<result property="name" column="fname" javaType="String" />  
<result property="number" column="fnum" />  
<result property="createTime" column="fcreateTime" javaType="Date" />  
<result property="lastUpdateTime" column="flastUpdateTime" javaType="Date"/>  

<result property="describe" column="fdescribe" javaType="String" />  
<result property="iseffective" column="fiseffective" />  
<result property="address" column="faddress" javaType="String" />  
<result property="cellphone" column="fcellphone" javaType="String" />  

<result property="genearchName" column="fgenearchName"  
javaType="String" />  
<result property="relation" column="frelation" javaType="String" />  
<result property="age" column="fage" javaType="int" />  
<result property="gender" column="fgender" javaType="int" />  
<result property="intime" column="fintime" javaType="Date" />  

<association property="grade" column="fgradeid"  
javaType="com.student.domain.Grades" columnPrefix="g_">  
<id property="id" column="id" />  
<result property="name" column="name" />  
</association>  

<association property="term" column="ftermid"  
javaType="com.student.domain.Term" columnPrefix="t_">  
<id property="id" column="id" />  
<result property="name" column="name" />  
</association>  
</resultMap>  
</mapper>  

4.IBATIS jdbcType

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"  
" http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">  
<mapper namespace="children">  

<!-- 数据库字段和Bean对象字段映射 -->  
<resultMap id="childrenMap" type="com.student.domain.Children">  
<result property="id" column="fid"/>  
<result property="number" column="fnumber"/>  
<result property="creataTime" column="fcreataTime"/>  
<result property="lastUpdateTime" column="flastUpdateTime"/>  
<result property="describe" column="fdescribe"/>  

<result property="childName" column="fchildName"/>  
<result property="childGender" column="fchildGender"/>  
<result property="childAge" column="fchildAge"/>  
<result property="belongClass" column="fbelongClass"/>  
<result property="beginTime" column="fbeginTime"/>  
<result property="parentName" column="fparentName"/>  
<result property="cellphone" column="fcellphone"/>  
<result property="telphone" column="ftelphone"/>  
<result property="address" column="faddress"/>  
<result property="leaved" column="fleaved"/>  
</resultMap>  

<sql id="childrenColumn">  
fid,  
fdescribe,  
flastupdatetime,  
fnumber,  
fcreatetime,  

fchildName,  
fchildGender,  
fchildAge,  
fbelongClass,  
fbeginTime,  
fparentName,  
fcellphone,  
ftelphone,  
faddress,  
fleaved  
</sql>  

<!-- 增加 -->  
<insert id="insert" parameterType="com.student.domain.Children">  
insert into t_hi_children  
(<include refid="childrenColumn"/>)  
values  
(  
uuid(),  
#{describe jdbcType=VARCHAR},  
#{lastUpdateTime jdbcType=DATE},  
#{number jdbcType=VARCHAR},  
#{creataTime jdbcType=DATE}  

#{childName jdbcType=VARCHAR},  
#{childGender jdbcType=INTEGER},  
#{childAge jdbcType=INTEGER},  
#{belongClass jdbcType=VARCHAR},  
#{parentName jdbcType=VARCHAR},  
#{cellphone jdbcType=VARCHAR},  
#{telphone jdbcType=VARCHAR},  
#{address jdbcType=VARCHAR},  
#{leaved jdbcType=INTEGER}  
)  
</insert>  

<!-- 修改 -->  
<update id="update" parameterType="com.student.domain.Children">  
update t_hi_children t  
<set>  
<if test="childName !=null and childName != '' ">  
FchildName = #{childName},  
</if>  
<if test="number !=null and number != '' ">  
fnumber = #{number},  
</if>  
<if test="describe !=null and describe != '' ">  
fdescribe = #{describe},  
</if>  
<if test="childGender !=null and childGender != '' ">  
FchildGender = #{childGender},  
</if>  
<if test="childAge !=null and childAge != '' ">  
FchildAge = #{childAge},  
</if>  
<if test="belongClass !=null and belongClass != '' ">  
FbelongClass = #{belongClass},  
</if>  
<if test="parentName !=null and parentName != '' ">  
FparentName = #{parentName},  
</if>  
<if test="cellphone !=null and cellphone != '' ">  
Fcellphone = #{cellphone},  
</if>  
<if test="telphone !=null and telphone != '' ">  
Ftelphone = #{telphone},  
</if>  
<if test="address !=null and address != '' ">  
<!-- Faddress = #{address},-->  
</if>  
<if test="leaved !=null and leaved != '' ">  
Fleaved = #{leaved},  
</if>  
flastupdatetime = now()  
</set>  

<where>  
fid = #{id}  
</where>  
</update>  

<!-- 查询 可能返回多个结果-->  
<select id="selectAll" resultType="java.util.List" resultMap="childrenMap">  
select *  
from t_hi_children  
</select>  

<!-- 删除 -->  
<delete id="delete">  
delete  
from t_hi_children where fid = #{id}  
</delete>  

</mapper>