mybatis

自定义sql片段

<sql id = "update_Column" >    ID, Name, SerialNumber, Address, Phone, Province, City, District, ProvinceID, CityID, DistrictID, Remark, State</sql>

通过id引入sql片段

<include refid = "update_Column" ></ include >

条件判断（string类型）

<if test="keyword != null and keyword != ''">and Name like '%${keyword}%' or SerialNumber like '%${keyword}%'</if>

条件判断（int类型）

<if test="warehouseid!=null">and warehouseID =#{warehouseid,jdbcType=INTEGER}</if>

条件判断（时间类型）

<if test="startTime!=null and startTime!=''">and<![CDATA[collectionTime >=#{startTime}]]></if>

查询

<select id = "selectArticleByUserId" parameterType="java.lang.String" resultMap="ArticleResultMap" >    select * from tb_article where     userId=#{userId} </select>

更新

<update id = "update" parameterType="com.mms.pojo.Device">update device    set    typeID = #{typeID,jdbcType=VARCHAR},serialNumber = #{serialNumber,jdbcType=VARCHAR},name = #{name,jdbcType=VARCHAR},spec = #{spec,jdbcType=VARCHAR},departmentID = #{departmentID,jdbcType=INTEGER},warehouseID = #{warehouseID,jdbcType=INTEGER},Site = #{site,jdbcType=VARCHAR},State = #{state,jdbcType=INTEGER}where id = #{id,jdbcType=INTEGER}</update>

删除

<delete id = "delete" parameterType="java.lang.Integer">delete from department    where id= #{id,jdbcType=INTEGER}</delete>

插入

<insert id = "insert" parameterType="com.mms.pojo.Members">insert into members(        ULoginName,        ULoginMoblie,        UPswd,        UName,        RegDate,        usex,        faceimg,        ismanage,        DepartmentID    )    values(        #{uloginname,jdbcType=VARCHAR},        #{uloginmoblie,jdbcType=VARCHAR},        #{upswd,jdbcType=VARCHAR},        #{uname,jdbcType=VARCHAR},        #{regdate,jdbcType=TIMESTAMP},        #{usex},        #{faceimg},        #{ismanage},        #{departmentID}    )</insert>

关键字查询

<if test="keyword != null and keyword != ''">and Name like '%${keyword}%' or SerialNumber like'%${keyword}%'</if><if test="departmentid!=null">and DepartmentID=#{departmentid,jdbcType=INTEGER}</if><if test="warehouseid!=null">and warehouseID=#{warehouseid,jdbcType=INTEGER}</if><if test="typeid!=null and typeid!=''">and typeID=#{typeid,jdbcType=VARCHAR}</if><if test="start!=null and length!=null">limit #{start,jdbcType=INTEGER},#{length,jdbcType=INTEGER}</if>

trim的用法

< trim prefix = "set" suffixoverride="," suffix=" where id = #{id} ">　　<if test="name != null and name.length()>0"> name=#{name},</if>　　<if test="gender != null and gender.length()>0"> gender=#{gender} ,</if></trim>注解：prefix = "set" :前面加"set" suffixoverride = "," :去除最后一个"，" suffix=" where id = #{id} " :在尾部加 "where id = #{id}"

一对一与一对多collection和association的使用

一、一对一

Article类（文章类）

private int id;//主键private String articleTitle;//文章标题private String articleContent;//文章内容

User类（用户类）

private int id;//主键private String userName;//用户姓名private int Article_id;//外键（文章id）private Article article;//额外增加的的文章属性（数据库没有这一列）

Article Mapping(xml文件)

<select id="selectArticleById" parameterType="java.lang.Integer" resultMap="ArticleResultMap" >    select * from    Article where id = #{id} </select>

注：这个方法必须有

User Mapping(xml文件)

<resultMap id="userResultMap" type="test.mybatis.entity.User">   <id column="id" property="id"/>   <result column="userName" property="userName"/>   <result column="Article_id" property="Article_id"/>   //这里把外键（Article_id）传过去   <association property="article" column="Article_id" select="test.mybatis.dao.articleMapper.selectArticleById" />//test.mybatis.dao.articleMapper为命名空间</resultMap>

二、一对多，collection，理解了一对一，一对多容易理解。

Article类（文章类）

private int id;//主键private String articleTitle;//文章标题private String articleContent;//文章内容

User类（用户类）

private int id;//主键private String userName;//用户姓名private int Article_id;//外键（文章id）private List<Article> articleList;//额外增加的的文章属性（数据库没有这一列）

Article Mapping(xml文件)

<select id = "selectArticleById" parameterType="java.lang.Integer" resultMap="ArticleResultMap" >    select* from    Article where id = #{id} </select>

User Mapping(xml文件)

<resultMap id = "userResultMap" type="test.mybatis.entity.User">   <id column = "id" property="id"/>   <result column = "userName" property="userName"/>   <result column = "Article_id" property="Article_id"/>   //这里把外键（Article_id）传过去   <collection property = "articleList" column="Article_id" select="test.mybatis.dao.articleMapper.selectArticleById" />//test.mybatis.dao.articleMapper为命名空间</resultMap>