Mybatis_第五部分：Mybatis复杂映射开发

Mybatis_第五部分：Mybatis复杂映射开发

5.1 ⼀对⼀查询

5.1.1 ⼀对⼀查询的模型

⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户

⼀对⼀查询的需求：查询⼀个订单，与此同时查询出该订单所属的⽤户

 

5.1.2⼀对⼀查询的语句

对应的 sql 语句： select * from orders o,user u where o.uid=u.id;

查询的结果如下：

 

5.1.3 创建Order和User实体

public class Order {

private int id ;

private Date ordertime ;

private double total ;

// 代表当前订单从属于哪⼀个客户

private User user ;

}

public class User {

private int id ;

private String username ;

private String password ;

private Date birthday ;

}

5.1.4 创建OrderMapper接⼝

public interface OrderMapper {

List < Order > findAll ();

}

5.1.5 配置OrderMapper.xml

<mapper namespace = "com.lagou.mapper.OrderMapper" >

<resultMap id = "orderMap" type = "com.lagou.domain.Order" >

<result column = "uid" property = "user.id" ></result>

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

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

<result column = "birthday" property = "user.birthday" ></result>

</resultMap>

<select id = "findAll" resultMap = "orderMap" >

select * from orders o,user u where o.uid=u.id

</select>

</mapper>

其中还可以配置如下：

<resultMap id = "orderMap" type = "com.lagou.domain.Order" >

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

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

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

<association property = "user" javaType = "com.lagou.domain.User" >

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

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

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

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

</association>

</resultMap>

5.1.6 测试结果

OrderMapper mapper = sqlSession . getMapper ( OrderMapper . class );

List < Order > all = mapper . findAll ();

for ( Order order : all ){

System . out . println ( order );

}

 

5.2 ⼀对多查询

5.2.1 ⼀对多查询的模型

⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户

⼀对多查询的需求：查询⼀个⽤户，与此同时查询出该⽤户具有的订单

 

5.2.2 ⼀对多查询的语句

对应的 sql 语句： select *,o.id oid from user u left join orders o on u.id=o.uid;

查询的结果如下：

 

5.2.3 修改User实体

public class Order {

private int id ;

private Date ordertime ;

private double total ;

// 代表当前订单从属于哪⼀个客户

private User user ;

}

public class User {

private int id ;

private String username ;

private String password ;

private Date birthday ;

// 代表当前⽤户具备哪些订单

private List < Order > orderList ;

}

5.2.4 创建UserMapper接⼝

public interface UserMapper {

List < User > findAll ();

}

5.2.5 配置UserMapper.xml

<mapper namespace = "com.lagou.mapper.UserMapper" >

<resultMap id = "userMap" type = "com.lagou.domain.User" >

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

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

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

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

<collection property = "orderList" ofType = "com.lagou.domain.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 *,o.id oid from user u left join orders o on u.id=o.uid

</select>

</mapper>

5.2.6 测试结果

UserMapper mapper = sqlSession . getMapper ( UserMapper . class );

List < User > all = mapper . findAll ();

for ( User user : all ){

System . out . println ( user . getUsername ());

List < Order > orderList = user . getOrderList ();

for ( Order order : orderList ){

System . out . println ( order );

}

System . out . println ( "----------------------------------" );

}

 

5.3 多对多查询

5.3.1 多对多查询的模型

⽤户表和⻆⾊表的关系为，⼀个⽤户有多个⻆⾊，⼀个⻆⾊被多个⽤户使⽤

多对多查询的需求：查询⽤户同时查询出该⽤户的所有⻆⾊

 

5.3.2 多对多查询的语句

对应的 sql 语句： select u. ,r. ,r.id rid from user u left join user_role ur on u.id=ur.user_id

inner join role r on ur.role_id=r.id;

查询的结果如下：

 

5.3.3 创建Role实体，修改User实体

public class User {

private int id ;

private String username ;

private String password ;

private Date birthday ;

// 代表当前⽤户具备哪些订单

private List < Order > orderList ;

// 代表当前⽤户具备哪些⻆⾊

private List < Role > roleList ;

}

public class Role {

private int id ;

private String rolename ;

}

5.3.4 添加UserMapper接⼝⽅法

List<User> findAllUserAndRole();

5.3.5 配置UserMapper.xml

<resultMap id = "userRoleMap" type = "com.lagou.domain.User" >

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

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

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

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

<collection property = "roleList" ofType = "com.lagou.domain.Role" >

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

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

</collection>

</resultMap>

<select id = "findAllUserAndRole" resultMap = "userRoleMap" >

select u.*,r.*,r.id rid from user u left join user_role ur on

u.id=ur.user_id

inner join role r on ur.role_id=r.id

</select>

5.3.6 测试结果

UserMapper mapper = sqlSession . getMapper ( UserMapper . class );

List < User > all = mapper . findAllUserAndRole ();

for ( User user : all ){

System . out . println ( user . getUsername ());

List < Role > roleList = user . getRoleList ();

for ( Role role : roleList ){

System . out . println ( role );

}

System . out . println ( "----------------------------------" );

}

 

5.4 知识⼩结

MyBatis 多表配置⽅式：

⼀对⼀配置：使⽤做配置

⼀对多配置：使⽤ + 做配置

多对多配置：使⽤ + 做配置