xml配置文件实现 数据表的一对一 一对多 多对多查询

一、一对一查询

  使用<resultMap>实现，使用一个订单Order对应一个用户User

(1)建立orders表与user表

   orders表：

   user表:

(2)OrderMapper接口

public interface OrderMapper {
    public List<Order> findAll();
}

(3)OrderMapper.xml文件配置

  property:所在类的属性名
  column:数据表中属性所对应的字段名

<mapper namespace="mapper.OrderMapper">
<!--    一对多-->
    <resultMap id="orderMap" type="order">
        <!--手动指定字段与实体属性之间的映射关系
            column:数据表字段名
            property:实体属性名
        -->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <association property="user" javaType="user">
            <id column="uid" property="id"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
            <result column="birthday" property="birthday"></result>
        </association>
        <!--
             property:当前实体(order)中的属性名称
             javaType:当前实体(order)中属性的类型(User)
        -->
    </resultMap>
    <select id="findAll" resultMap="orderMap">
		select * from orders o,user u where o.uid=u.id
	</select>
</mapper>

(4)测试结果

二、一对多查询

  一个User对应多个Order,使用<resultMap>与<collection>配置

(1)建表

  与一对一查询表一样

(2)User类与UserMapper接口

  public class User{
   		private int id;
    	private String username;
    	private String password;
    	//当前用户有哪些订单
    	private List<Order> orderList;
    }

public interface UserMapper {
    public List<User> findAll();
}

(3)UserMapper.xml文件配置

  ofType:一对多中,collection映射的list是何种类型

<mapper namespace="mapper.UserMapper">
    <resultMap id="userMap" type="user">
        <result column="id" property="id"></result>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <collection property="orderList" ofType="order">
            <result column="oid" property="id"></result>
            <result column="ordertime" property="ordertime"></result>
            <result column="total" property="total"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="userMap">
        SELECT *
        FROM USER u,
             orders o
        WHERE u.`id` = o.`uid`;
    </select>
</mapper>

(4)测试结果

三、多对多查询

  使用<resultMap>与<collection>配置

(1)建表

  user表同上
  sys_role表:

  sys_user_role表:(中间表)

(2)User实体与UserMapper接口

public class User {

    private int id;
    private String username;
    private String password;
    //当前用户有哪些订单
    //private List<Order> orderList;
    //当前用户有哪些角色
    private List<Role> roleList;

    public List<Role> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public List<Role> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", roleList=" + roleList +
                '}';
    }
}

    public List<User> findRoleAll();

(3)UserMapper.xml接口配置

    <resultMap id="userRoleMap" type="user">
        <!--        user信息-->
        <id column="userId" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <!--        user内部的roleList信息-->
        <collection property="roleList" ofType="role">
            <id column="roleId" property="id"></id>
            <result column="roleName" property="roleName"></result>
            <result column="roleDesc" property="roleDesc"></result>
        </collection>
    </resultMap>
    <select id="findRoleAll" resultMap="userRoleMap">
        --         等值连接 没有role的user不会显示
        SELECT *
        FROM USER u,
             sys_user_role ur,
             sys_role r
        WHERE u.`id` = ur.`userId`
        AND ur.`roleId` = r.`id`;
        --         左外连接 显示所有user
        --         SELECT *
        --         FROM USER u
        --         LEFT OUTER JOIN sys_user_role ur
        --         ON u.`id` = ur.`userId`
        --         LEFT JOIN sys_role r
        --         ON ur.`roleId` = r.`id`;
    </select>

(4)测试结果

  一个user可以有多个role,一个role可以被不同的user充当