Mybatis进阶学习笔记——关系查询—

Mybatis进阶学习笔记——关系查询——一对一查询

用户和订单的需求

通过查询订单，查询用户，就是一对一查询

（1）自定义JavaBean（常用，推荐使用）

1     <select id="queryOrderUser" resultType="OrderUser">
2         SELECT o.id,o.user_id,u.name,o.productname
3         FROM t_order o LEFT JOIN t_user u ON o.user_id= u.id
4     </select>

OrderUser.java：

 1 package cn.sm1234.domain;
 2 
 3 public class OrderUser extends Order {
 4 
 5     //Order中没有name
 6     private String name;
 7 
 8     public String getName() {
 9         return name;
10     }
11 
12     public void setName(String name) {
13         this.name = name;
14     }
15     
16     
17 }

 1     @Test
 2     public void test1() {
 3         SqlSession sqlSession = SessionUtils.getSession();
 4         // getMapper(): 返回指定接口的动态代理的实现类对象
 5         OrderDao dao = sqlSession.getMapper(OrderDao.class);
 6         List<OrderUser> list = dao.queryOrderUser();
 7         for (OrderUser orderUser : list) {
 8             System.out.println(orderUser.getName()+"-"+orderUser.getProductname());
 9         }
10         sqlSession.commit();
11         sqlSession.close();
12     }

OrderDao.java：

1 public interface OrderDao {
2 
3     /**
4      * 查询订单，并且关联查询用户信息
5      */
6     public List<OrderUser> queryOrderUser();
7 }

（2）ResultMap封装

 1     <!-- type="实体类" -->
 2     <resultMap type="Order" id="OrderUserResultMap">
 3         <id column="id" property="id"/>
 4         <result column="productname" property="productname"/>
 5         <result column="orderno" property="orderno"/>
 6         <!-- user_id封装到User中 -->
 7         <!-- 关联属性 
 8             property：关联属性名称（Order类中的user）
 9             javaType：类型（User （即User user））
10         -->
11         <association property="user" javaType="User">
12             <!-- column中的字段名称应该为当前查询结果的字段名称 -->
13             <id column="user_id" property="id"/>
14             <result column="name" property="name"/>
15             <result column="password" property="password"/>
16         </association>
17         
18     </resultMap>
19 
20     <select id="queryOrderUserResultMap" resultMap="OrderUserResultMap">
21         SELECT o.id,o.user_id,u.name,o.productname
22         FROM t_order o LEFT JOIN t_user u ON o.user_id= u.id
23     </select>

Order.java：

 1 package cn.sm1234.domain;
 2 
 3 public class Order {
 4 
 5     private Integer id;
 6     private String productname;
 7     private String orderno;
 8     private Integer userId;
 9     
10     private User user;
11     
12     public Integer getId() {
13         return id;
14     }
15     public void setId(Integer id) {
16         this.id = id;
17     }
18     public String getProductname() {
19         return productname;
20     }
21     public void setProductname(String productname) {
22         this.productname = productname;
23     }
24     public String getOrderno() {
25         return orderno;
26     }
27     public void setOrderno(String orderno) {
28         this.orderno = orderno;
29     }
30     public Integer getUserId() {
31         return userId;
32     }
33     public void setUserId(Integer userId) {
34         this.userId = userId;
35     }
36     public User getUser() {
37         return user;
38     }
39     public void setUser(User user) {
40         this.user = user;
41     }
42     
43     
44 }

1     /**
2      * 查询订单，查询用户（ResultMap）
3      * @return
4      */
5     public List<Order> queryOrderUserResultMap();

 1     @Test
 2     public void test2() {
 3         SqlSession sqlSession = SessionUtils.getSession();
 4         // getMapper(): 返回指定接口的动态代理的实现类对象
 5         OrderDao dao = sqlSession.getMapper(OrderDao.class);
 6         List<Order> list = dao.queryOrderUserResultMap();
 7         for (Order order : list) {
 8             System.out.println(order.getProductname()+"-"+order.getUser().getName());
 9         }
10         sqlSession.commit();
11         sqlSession.close();
12     }