Mybatis复杂集合对象的嵌套查询以及一些动态查询

1.动态查询条件。

select count(*)
from sp_account
<where>
    <if test="createUserId != null">
        and create_user_id = #{createUserId,jdbcType=INTEGER}
    </if>
    <if test="createTime != null">
        and create_time = #{createTime,jdbcType=TIMESTAMP}
    </if>
    <if test="updateTime != null">
        and update_time = #{updateTime,jdbcType=TIMESTAMP}
    </if>
    <if test="administrator != null and administrator != ''">
        and administrator = #{administrator,jdbcType=CHAR}
    </if>
</where>

不用担心会有多余的and 或者 Or ，where标签会处理掉的，所以不需要像以前那样先where 1=1 然后在写各种动态if标签。

 

2.批量插入sql

<insert id="batchCreate" parameterType="java.util.List" keyColumn="id" keyProperty="id" useGeneratedKeys="true">
    INSERT INTO sp_agency_school (mch_name,mch_id,agency_id,agency_level,
    school_name,school_id,school_address,status,op_user_id,create_time, update_time)
    values
    <foreach collection="list" item="emp" separator=",">
        (#{emp.mchName,jdbcType=VARCHAR},#{emp.mchId,jdbcType=VARCHAR},#{emp.agencyId,jdbcType=INTEGER},
        #{emp.agencyLevel,jdbcType=INTEGER},#{emp.schoolName,jdbcType=VARCHAR},#{emp.schoolId,jdbcType=BIGINT},
        #{emp.schoolAddress,jdbcType=VARCHAR},#{emp.status,jdbcType=CHAR},#{emp.opUserId,jdbcType=INTEGER},
        #{emp.createTime,jdbcType=TIMESTAMP},#{emp.updateTime,jdbcType=TIMESTAMP})
    </foreach>
</insert>

需要注意foreach标签里的属性要是item里的emp.xxx才行！！

 

3.循环查找

<select id="selectBySchoolIds" parameterType="java.util.List" resultMap="BaseResultMap">
    SELECT
    <include refid="Base_Column_List"/>
    FROM
    sp_agency_school
    where
    school_id IN
    <foreach collection="schoolIds" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
    and status = '1'
</select>

这里的collection="schoolIds",在Mapper接口通过@Param("schoolIds")定义好的

4.多表关联及映射成java复杂集合对象。

<!--这里注意column是sql语句后的column，所以可以自定义防止主键id等重复，一般创建扩展类进行类型接收-->
<resultMap type="com.az.xpay.domain.spbase.entity.AccountExt" id="AccountMap">
    <result column="accountId" jdbcType="INTEGER" property="accountId"/>
    <result column="phone" jdbcType="VARCHAR" property="phone"/>
    <result column="username" jdbcType="VARCHAR" property="username"/>
    <result column="status" jdbcType="CHAR" property="status"/>
    <result column="type" jdbcType="CHAR" property="type"/>
    <result column="address" jdbcType="VARCHAR" property="address"/>
    <result column="idcard" jdbcType="VARCHAR" property="idcard"/>
    <result column="password" jdbcType="VARCHAR" property="password"/>
    <result column="opUserId" jdbcType="INTEGER" property="opUserId"/>
    <result column="createUserId" jdbcType="INTEGER" property="createUserId"/>
    <result column="administrator" jdbcType="CHAR" property="administrator"/>
    <result column="createTime" jdbcType="TIMESTAMP" property="createTime"/>
    <result column="updateTime" jdbcType="TIMESTAMP" property="updateTime"/>
    <!-- 机构信息-->
    <result column="agencyId" jdbcType="INTEGER" property="agencyId"/>
    <result column="agencyName" jdbcType="VARCHAR" property="agencyName"/>
    <result column="companyName" jdbcType="VARCHAR" property="companyName"/>
    <result column="companyId" jdbcType="INTEGER" property="companyId"/>
    <result column="agencyLevel" jdbcType="INTEGER" property="agencyLevel"/>
    <collection property="roles" resultMap="RoleMap">
    </collection>
</resultMap>


<!--角色信息-->
<resultMap id="RoleMap" type="com.az.xpay.domain.spbase.entity.RoleExt">
    <result property="roleId" column="roleId"/>
    <result column="roleName" jdbcType="VARCHAR" property="roleName"/>
    <result column="roleDescription" jdbcType="VARCHAR" property="roleDescription"/>
    <!--权限信息-->
    <collection property="permissions" javaType="ArrayList" column="perId"
                ofType="com.az.xpay.domain.spbase.entity.PermissionExt">
        <result column="url" jdbcType="VARCHAR" property="url"/>
        <result column="perId" jdbcType="INTEGER" property="perId"/>
        <result column="perDescription" jdbcType="VARCHAR" property="perDescription"/>
    </collection>
</resultMap>

<select id="selectByPhone" resultType="java.lang.String" resultMap="AccountMap">
     SELECT
        ac.id as accountId,
     ac.phone as phone,
     ac.name as username,
     ac.status as status,
     ac.type as type,
     ac.address as address,
        ac.idcard as idcard,
        ac.password as password,
        ac.op_user_id as opUserId,
        ac.create_user_id as createUserId,
        ac.administrator as administrator,
        ac.create_time as createTime,
        ac.update_time as updateTime,
     ag.id as agencyId,
     ag.name as agencyName,
     ag.level as agencyLevel,
     ag.company_name as companyName,
     ag.company_id as companyId,
     r.id as roleId,
     r.name as roleName,
     r.description as roleDescription,
     p.id as perId,
     p.url as url,
     p.description as perDescription
    FROM
        sp_account ac
    LEFT JOIN sp_agency ag on ac.agency_id = ag.id AND ag.`status` = 1
    LEFT JOIN sp_account_role acr ON ac.id = acr.account_id AND acr.`status` = 1
    LEFT JOIN sp_role r ON acr.role_id = r.id AND r.`status` = 1
    LEFT JOIN sp_role_permission rp ON r.id = rp.role_id AND rp.`status` = 1
    LEFT JOIN sp_permission p ON rp.permission_id = p.id AND p.`status` = 1
    WHERE
     ac.phone = #{phone}
</select>

这里包含了好几层对象。账号对应多个角色，角色对应多个权限。模拟代码结构的话应该是这样的：

Class Account {

private List<Role> roles;

}

Class Role{

private List<Permission> permissions;

}

多层级的管理关系。包含集合对象的话。要使用collection标签，然后result标签里的 column不是数据的栏位名称而是可以自定义 as 后的字段。 property是对应的java类的属性名

 

模糊查询设置

<select id="selectBySelective" parameterType="com.az.xpay.domain.spbase.entity.Agency" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from sp_agency
    <where>
    <if test="companyName != null and companyName != ''">
        <bind name="pattern_companyName" value="'%' + companyName + '%'"/>
        and company_name like #{pattern_companyName,jdbcType=VARCHAR}
    </if>
    <if test="opUserId != null">
        and op_user_id = #{opUserId,jdbcType=INTEGER}
    </if>
    <if test="name != null and name != ''">
        <bind name="pattern_name" value="'%' + name + '%'"/>
        and `name` like #{pattern_name,jdbcType=VARCHAR}
    </if>
   
   
   
    </where>
</select>

主要是 <if test="companyName != null and companyName != ''"> <bind name="pattern_companyName" value="'%' + companyName + '%'"/> and company_name like #{pattern_companyName,jdbcType=VARCHAR} </if>

bind标签也可以放在一开始的select标签外面，但是那样如果不传值的话，mybatis会报错。所以这里是放在if标签下的，还有注意拼接的value值中一定是对象中的属性名而不是数据库列名