mybatis xml方式查询升级

查询所有接口

List<User> selectAll();

公用resultMap

<resultMap type="cn.lyhxh.model.User" id="baseResultMap">

<id column="id" property="id"/>

<result column="username" property="username"/>

<result column="password" property="password"/>

<result column="reale_name" property="realeName"/>

<result column="gender" property="gender"/>

<result column="phone_number" property="phoneNumber"/>

<result column="email" property="email"/>

<result column="register_time" property="registerTime"/>

</resultMap>

查询升级

1，初级：单表查询(用户表)

<resultMap type="cn.lyhxh.model.User" id="userResult" extends="baseResultMap"></resultMap>

<select id="selectAll" resultMap="userResult">

SELECT u.id,u.username,u.password,u.reale_name,u.gender,u.phone_number,u.email,u.register_time

FROM USER u

</select>

2，中级：两表关联association使用(用户表和部门表关联)

user对象添加：

private Department department;

<resultMap type="cn.lyhxh.model.User" id="userResult" extends="baseResultMap">

<association property="department" javaType="cn.lyhxh.model.Department">

<id column="department_id" property="id"/>

<result column="name" property="name"/>

<result column="description" property="description"/>

</association>

</resultMap>

<select id="selectAll" resultMap="userResult">

SELECT u.id,u.username,u.password,u.reale_name,u.gender,u.phone_number,u.email,u.register_time,d.id department_id,d.name,d.description

FROM USER u

LEFT JOIN department d ON d.id = u.department_id

</select>

3，中级：三表关联association使用(用户表和部门表关联，部门表自关联）

user对象添加：

private Department department;

department对象添加：

private Department parent;

<resultMap type="cn.lyhxh.model.User" id="userResult" extends="baseResultMap">

<association property="department" javaType="cn.lyhxh.model.Department">

<id column="department_id" property="id"/>

<result column="name" property="name"/>

<result column="description" property="description"/>

<association property="parent" javaType="cn.lyhxh.model.Department">

<id column="parent_id" property="id"/>

<result column="name" property="name"/>

<result column="description" property="description"/>

</association>

</association>

</resultMap>

<select id="selectAll" resultMap="userResult">

SELECT u.id,u.username,u.password,u.reale_name,u.gender,u.phone_number,u.email,u.register_time,d.id department_id,d.name,d.description,

pd.id parent_id,pd.name,pd.description

FROM USER u

LEFT JOIN department d ON d.id = u.department_id

LEFT JOIN department pd ON pd.id = d.parent_id

</select>

4，高级：四表关联association，collection 使用(用户表和部门表关联，部门表自关联，岗位表关联）

user对象添加：

private Department department;

private List<Role> roles;

department对象添加：

private Department parent;

<resultMap type="cn.lyhxh.model.User" id="userResult" extends="baseResultMap">

<association property="department" javaType="cn.lyhxh.model.Department">

<id column="department_id" property="id"/>

<result column="name" property="name"/>

<result column="description" property="description"/>

<association property="parent" javaType="cn.lyhxh.model.Department">

<id column="parent_id" property="id"/>

<result column="name" property="name"/>

<result column="description" property="description"/>

</association>

</association>

<collection property="roles" javaType="list" ofType="cn.lyhxh.model.Role">

<id column="roles_id" property="id"/>

<result column="name" property="name"/>

<result column="description" property="description"/>

</collection>

</resultMap>

<select id="selectAll" resultMap="userResult">

SELECT u.id,u.username,u.password,u.reale_name,u.gender,u.phone_number,u.email,u.register_time,d.id department_id,d.name,d.description,

pd.id parent_id,pd.name,pd.description,r.id role_id,r.name,r.description

FROM USER u

LEFT JOIN department d ON d.id = u.department_id

LEFT JOIN department pd ON pd.id = d.parent_id

LEFT JOIN user_role ur ON ur.user_id = u.id

LEFT JOIN role r ON r.id = ur.role_id

</select>