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>