mybatis-调用视图多条件查询

1.前言

在进行用户视图数据查询时需要进行关联查询，并进行多条件筛选。进行视图查询在数据库中可以创建视图来进行。
视图优点：
1.允许简化复杂查询。
2.限制对特定用户的数据访问
3.提供额外的安全层
4.启用计算列
视图缺点：
1.数据查询可能会比较慢
2.表依赖问题，变更关联表就需要修改视图。

2.数据库视图创建

表：

sys_user		//用户表
sys_role		//角色表
sys_organization		//组织表
sys_user_organize_relevance	//用户组织关联表
sys_user_role_relevance			//用户角色关联表

视图sql：

SELECT
	`users`.`user_id` AS `user_id`,
	`users`.`user_number` AS `user_number`,
	`users`.`user_name` AS `user_name`,
	`users`.`user_sex` AS `user_sex`,
	`users`.`user_email` AS `user_email`,
	`users`.`user_phone` AS `user_phone`,
	`users`.`native_place` AS `native_place`,
	`users`.`edu_bg` AS `edu_bg`,
	`users`.`specialty` AS `specialty`,
	`users`.`university` AS `university`,
	`users`.`user_introduce` AS `user_introduce`,
	`users`.`status` AS `status`,
	`users`.`add_time` AS `add_time`,
	`users`.`update_time` AS `update_time`,
	`roles`.`role_name` AS `role_name`,
	`roles`.`role_id` AS `role_id`,
	`organizes`.`organize_name` AS `organize_name`,
	`organizes`.`organize_id` AS `organize_id` 
FROM
	(
	(
	(
	( `sys_user` `users` LEFT JOIN `sys_user_role_relevance` `surr` ON ( ( `users`.`user_id` = `surr`.`user_id` ) ) )
	LEFT JOIN `sys_role` `roles` ON ( ( `roles`.`role_id` = `surr`.`role_id` ) ) 
	)
	LEFT JOIN `sys_user_organize_relevance` `suor` ON ( ( `users`.`user_id` = `suor`.`user_id` ) ) 
	)
	LEFT JOIN `sys_organization` `organizes` ON ( ( `suor`.`organize_id` = `organizes`.`organize_id` ) ) 
	)

3.mybatis引用

1. 创建视图对象

mybatis进行视图调用查询时通过该对象进行输出。

/**
 * 用户视图模型
 */
@Data
public class SysUserVO {

	private Integer id;
	private String number;
	private String name;
	private String email;
	private String phone;
	private Integer status;
	private String department;
	private String role;
	private Date addTime;
	private Date updateTime;
}

2.创建字段映射集合

在对的xml文件中进行创建

<!-- 用户视图映射 -->
    <resultMap id="BaseResultMapVo" type="com.prospect.admin.mbg.vo.sys.SysUserVO">
        <id column="user_id" jdbcType="INTEGER" property="id"/>
        <result column="user_number" jdbcType="VARCHAR" property="number"/>
        <result column="user_name" jdbcType="VARCHAR" property="name"/>
        <result column="user_email" jdbcType="VARCHAR" property="email"/>
        <result column="user_phone" jdbcType="VARCHAR" property="phone"/>
        <result column="status" jdbcType="TINYINT" property="status"/>
        <result column="organize_name" jdbcType="VARCHAR" property="department"/>
        <result column="role_name" jdbcType="VARCHAR" property="role"/>
        <result column="add_time" jdbcType="TIMESTAMP" property="addTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
    </resultMap>

3.创建字段集合列表

该字段集合对应着视图映射字段中拥有的字段

<sql id="Base_Column_List_Vo">
        user_id, user_number, user_name, user_email, user_phone, status, organize_name, role_name, add_time,
        update_time
    </sql>

4.调用视图查询

在进行视图调用进行数据筛选查询时应该有如下注意事项：
筛选字段必须是视图中存在，如果使用不存在的字段将会报出错误。
其余操作和普通的select查询表一样的操作。

<select id="listSelective" resultMap="BaseResultMapVo">
        select
        <include refid="Base_Column_List_Vo"/>
        from sys_user_vo_view
        <where>
            <if test="userInfo!= null and userInfo != ''">
                user_number like concat('%', #{userInfo}, '%')
                OR user_name like concat('%', #{userInfo}, '%')
                OR user_email like concat('%', #{userInfo}, '%')
                OR user_phone like concat('%', #{userInfo}, '%')
            </if>
            <if test="organize != null and organize != ''">
                and organize_id = #{organize, jdbcType=INTEGER}
            </if>
            <if test="role != null and role != ''">
                and role_id = #{role, jdbcType=INTEGER}
            </if>
        </where>
        <choose>
            <when test="sort != null and sort != '' and sort == 'department' ">
                order by organize_id
            </when>
            <when test="sort != null and sort != '' and sort == 'role' ">
                order by role_id
            </when>
            <when test="sort != null and sort != '' and sort == 'addTime' ">
                order by add_time
            </when>
            <when test="sort != null and sort != '' and sort == 'status' ">
                order by status
            </when>
            <otherwise>
                order by add_time
            </otherwise>
        </choose>
        <choose>
            <when test="order != null and order != '' and order == 'asc' ">
                ASC
            </when>
            <otherwise>
                DESC
            </otherwise>
        </choose>
    </select>

4.查询结果