mybatis使用心得总结

首先下面的总结是基于mysql数据库的其他数据库是不是一样不知道

1.模糊查询

使用concat()函数。CONCAT(str1,str2,...)

SELECT * from t_cbo_location_apply
        <where>
            <if test="identityType!=null">and identity_type = #{identityType,jdbcType=INTEGER}</if>
            <if test="addressStructure!=null and addressStructure!=''">and address_structure like
                concat("%",#{addressStructure,jdbcType=VARCHAR},"%")
            </if>
        </where>

2.大于号小于号

使用<![CDATA[  ]]> 标记将大于号和小于号圈起来

SELECT t1.*,t2.name,t2.id as orgId,1 as cond FROM t_pb_conference t1
                INNER JOIN t_pb_organization t2
                on t1.pb_id = t2.id
                <where>
                    <if test="status != null">
                        <if test="status == 1">and <![CDATA[ t1.start_time >  #{nowDate,jdbcType=TIMESTAMP} and t1.status < 4 and t1.status >= 1]]></if>
                        <if test="status == 2">and <![CDATA[ t1.start_time <=  #{nowDate,jdbcType=TIMESTAMP} and t1.end_time >= #{nowDate,jdbcType=TIMESTAMP} and t1.status < 4 and t1.status >= 1]]></if>
                        <if test="status == 3">and <![CDATA[ t1.end_time < #{nowDate,jdbcType=TIMESTAMP} and t1.status < 4 and t1.status >= 1]]></if>
                        <if test="status == 4">and t1.status = 4</if>
                    </if>
                    <if test="conferenceType !=null">and t1.conference_type = #{conferenceType,jdbcType=INTEGER} </if>
                    <if test="theme !=null and theme !=''">and t1.theme like concat("%",#{theme,jdbcType=VARCHAR},"%") </if>
                    <if test="beginTime !=null and beginTime !='' and endTime !=null and endTime !=''">
                        and t1.start_time between #{beginTime,jdbcType=VARCHAR} and #{endTime,jdbcType=VARCHAR}
                    </if>
                    <if test="noidList!=null and noidList.size()>0 ">and t1.pb_id in
                        <foreach collection="noidList" item="id" index="index" open="(" close=")" separator=",">
                            #{id,jdbcType=BIGINT}
                        </foreach>
                    </if>
                    and t1.status > 0
                </where>
                order by t1.release_time desc

3.批量更新数组（不是按照id批量设置某一个属性的值，而是更新多行数据）

<update id="batchUpdateConferenceStatus" parameterType="list">
        UPDATE t_pb_conference
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="status = case" suffix="end,">
                <foreach collection="updateList" item="item" index="index" >
                    <if test="item.status!=null">
                        WHEN id = #{item.id,jdbcType=BIGINT} then #{item.status,jdbcType=INTEGER}
                    </if>
                </foreach>
            </trim>
            <trim prefix="version = case" suffix="end,">
                <foreach collection="updateList" item="item" index="index" >
                    <if test="item.version!=null">
                        WHEN id = #{item.id,jdbcType=BIGINT} then #{item.version,jdbcType=INTEGER} + 1
                    </if>
                </foreach>
            </trim>
        </trim>
        <where>
            <foreach collection="updateList" item="item" index="index" separator=") or (" close=")" open="(">
                id = #{item.id,jdbcType=BIGINT} and version = #{item.version,jdbcType=INTEGER}
            </foreach>

        </where>
    </update>

这个sql需要传进来一个list ，updateList中是要更新的数据库队列的值。这里更新的条件是按照id和锁version同时满足。至于要更新的行的属性，只需要将item.xxx进行 替换就行

4.mybatis批量查询 结果集按照传入参数的顺序输出

这里我自己的例子找不到了 找了个网上的例子

<select id="getStudentListByIdList" parameterType="java.util.List" resultMap="studentResultMap">

select id,name,gender,age

from t_student

where id in

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

#{id}

</foreach>

order by field(

id,

<foreach collection="list" separator="," item="id">

#{id}

</foreach>

)

</select>

5.mybatis 的insert动态语句

不要将字段和值写死，要改成动态的。虽然这样会多写很多代码。ε=(´ο｀*)))唉。

个人不太喜欢用代码生成器，生成了方法名字也得改。

<insert id="addRecord" parameterType="com.bit.module.cbo.bean.LocationApply" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO t_cbo_location_apply
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="residentId!=null">
                resident_id,
            </if>
            <if test="communityId!=null">
                community_id,
            </if>
            <if test="communityName!=null and communityName!=''">
                community_name,
            </if>
            <if test="orgId!=null">
                org_id,
            </if>
            <if test="orgName!=null and orgName!=''">
                org_name,
            </if>
            <if test="addressId!=null">
                address_id,
            </if>
            <if test="addressStructure!=null">
                address_structure,
            </if>
            <if test="createTime!=null">
                create_time,
            </if>
            <if test="updateTime!=null">
                update_time,
            </if>
            <if test="updateUserId!=null">
                update_user_id,
            </if>
            <if test="identityType!=null">
                identity_type,
            </if>
            <if test="applyStatus!=null">
                apply_status,
            </if>
            <if test="enable!=null">
                enable,
            </if>
            <if test="version!=null">
                version,
            </if>
            <if test="comment!=null">
                comment,
            </if>
            <if test="cardType!=null">
                card_type,
            </if>
            <if test="cardNum!=null">
                card_num,
            </if>
            <if test="credentialsPhotoIds!=null">
                credentials_photo_ids,
            </if>
            <if test="residentName!=null">
                resident_name,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="residentId!=null">
                #{residentId,jdbcType=BIGINT},
            </if>
            <if test="communityId!=null">
                #{communityId,jdbcType=BIGINT},
            </if>
            <if test="communityName!=null and communityName!=''">
                #{communityName,jdbcType=VARCHAR},
            </if>
            <if test="orgId!=null">
                #{orgId,jdbcType=BIGINT},
            </if>
            <if test="orgName!=null and orgName!=''">
                #{orgName,jdbcType=VARCHAR},
            </if>
            <if test="addressId!=null">
                #{addressId,jdbcType=BIGINT},
            </if>
            <if test="addressStructure!=null ">
                #{addressStructure,jdbcType=VARCHAR},
            </if>
            <if test="createTime!=null">
                #{createTime,jdbcType=TIMESTAMP},
            </if>
            <if test="updateTime!=null">
                #{updateTime,jdbcType=TIMESTAMP},
            </if>
            <if test="updateUserId!=null">
                #{updateUserId,jdbcType=BIGINT},
            </if>
            <if test="identityType!=null">
                #{identityType,jdbcType=INTEGER},
            </if>
            <if test="applyStatus!=null">
                #{applyStatus,jdbcType=INTEGER},
            </if>
            <if test="enable!=null">
                #{enable,jdbcType=INTEGER},
            </if>
            <if test="version!=null">
                #{version,jdbcType=INTEGER},
            </if>
            <if test="comment!=null">
                #{comment,jdbcType=INTEGER},
            </if>
            <if test="cardType!=null">
                #{cardType,jdbcType=INTEGER},
            </if>
            <if test="cardNum!=null and cardNum!=''">
                #{cardNum,jdbcType=VARCHAR},
            </if>
            <if test="credentialsPhotoIds!=null">
                #{credentialsPhotoIds,jdbcType=VARCHAR},
            </if>
            <if test="residentName!=null and residentName!=''">
                #{residentName,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>

6.GROUP_CONCAT()函数

写项目的时候碰上个需求，要将一个关联关系表的值展示成一个字段。网上找了一下用这个函数就行

       SELECT a.*,b.extend_type FROM
        (
        SELECT DISTINCT t1.id,
        t1.real_name,
        t1.sex,
        t1.birthday,
        t1.card_num,
        t1.mobile,
        t1.`status`,
        t2.identity_type,
        t2.address_structure
        from t_cbo_resident t1
        INNER JOIN
        (
        SELECT * from t_cbo_location_apply
        <where>
            and apply_status = 1
            <if test="identityType!=null">and identity_type = #{identityType,jdbcType=INTEGER}</if>
            <if test="addressStructure!=null and addressStructure!=''">and address_structure like concat("%",#{addressStructure,jdbcType=VARCHAR},"%") </if>
        </where>
        LIMIT 1

        )t2 on t1.id = t2.resident_id) a
        Left JOIN (

            SELECT x.id,GROUP_CONCAT(y.extend_type) as extend_type  from t_cbo_resident x
            INNER JOIN t_cbo_resident_extend y on x.id = y.resident_id
            <where>
                <if test="orgId!=null">
                    and y.org_id = #{orgId,jdbcType=BIGINT}
                </if>
            </where>
            GROUP BY x.id
        ) b
        ON a.id = b.id
        <where>
            <if test="realName!=null">and a.real_name like concat("%",#{realName,jdbcType=VARCHAR},"%")</if>
            <if test="cardNum!=null">and a.card_num like concat("%",#{cardNum,jdbcType=VARCHAR},"%") </if>
            <if test="mobile!=null">and a.mobile like concat("%",#{mobile,jdbcType=VARCHAR},"%") </if>
            <if test="extendType!=null">and b.type like concat("%",#{extendType,jdbcType=VARCHAR},"%") </if>
        </where>

    </select>

注意这里使用了这个函数将关联关系表的值查询出来 按照居民的id分类，最后的结果当做一个表去和别的表join链接

SELECT x.id,GROUP_CONCAT(y.extend_type) as extend_type  from t_cbo_resident x
            INNER JOIN t_cbo_resident_extend y on x.id = y.resident_id
            <where>
                <if test="orgId!=null">
                    and y.org_id = #{orgId,jdbcType=BIGINT}
                </if>
            </where>
            GROUP BY x.id