有勇气的牛排博客
☰

javaWeb MyBatis（七）一对一查询

有勇气的牛排 996 Java 2021-10-26 00:18:42

1 数据库

mysql> select * from user;
+----+----------+------------+-----+----------+
| id | username | birthday   | sex | address  |
+----+----------+------------+-----+----------+
|  1 | 王五     | NULL       | 2   | 仙女系   |
| 10 | Charles  | 2021-10-21 | 1   | 银河系   |
| 16 | 张小明   | NULL       | 1   | 河南郑州 |
| 22 | 陈小明   | NULL       | 1   | 河南郑州 |
| 24 | 张三丰   | NULL       | 1   | 河南郑州 |
| 25 | 陈小明   | NULL       | 1   | 河南郑州 |
| 26 | 李白2    | 2021-10-22 | 2   | 666      |
| 28 | 李白     | 2021-10-22 | 2   | 666      |
+----+----------+------------+-----+----------+

mysql> select * from orders;
+----+---------+---------+---------------------+------+
| id | user_id | number  | createtime          | note |
+----+---------+---------+---------------------+------+
|  3 |       1 | 1000010 | 2015-02-04 13:22:35 | NULL |
|  4 |       1 | 1000011 | 2015-02-03 13:22:41 | NULL |
|  5 |      10 | 1000012 | 2015-02-12 16:13:23 | NULL |
+----+---------+---------+---------------------+------+

2 一对一查询

2.1 方法一 resultType

sql

select orders.*, user.username,user.address
from orders,user
where orders.user_id=user.id;

Orders.java

import java.util.Date;
import java.util.List;

public class Orders {
    private Integer id;
    private Integer userId;
    private String number;
    private Date createtime;
    private String note;

    get set...
    toString()

}

OrdersCustom.java 继承 Orders.java

package net920vip.bean;

public class OrdersCustom extends Orders {
    private String username;
    private String adress;

    get set...
    toString()
}

UserMapper.xml

<!-- 一对一查询，resultType -->
<select id="findOrdersList" resultType="OrdersCustom">
    select orders.*, user.username,user.address
    from orders,user
    where orders.user_id=user.id;
</select>

接口：UserMapper.java

package net920vip.mapper;

import net920vip.bean.Orders;
import net920vip.bean.UserBean;

import java.util.List;

/**
 * 接口文件 对应UserMapper.xml文件
 * 用户管理mapper
 * */
public interface UserMapper {

    // 一对一查询：查询所有orders，关联其下单用户 resultTypt方式
    public List<OrdersCustom> findOrdersList() throws Exception;

}

Test.java

// 一对一查询 orders关联user：resultType方式
List<OrdersCustom> list = userMapper.findOrdersList();
for (OrdersCustom ordersCustom : list) {
    System.out.println(ordersCustom.getNumber());
    System.out.println(ordersCustom.getUsername());
}

2.2 方法二 resultMap方式

Orders.java

package net920vip.bean;
package net920vip.bean;

import java.util.Date;
import java.util.List;

public class Orders {
    private Integer id;
    private Integer userId;
    private String number;
    private Date createtime;
    private String note;

    // 因为是一对一关系，所以在此处加入user属性
    private UserBean userBean;

    get set...
    toString()

}

接口UserMapper.java

package net920vip.mapper;

import net920vip.bean.Orders;
import net920vip.bean.UserBean;

import java.util.List;

/**
 * 接口文件 对应UserMapper.xml文件
 * 用户管理mapper
 * */
public interface UserMapper {

    // 一对一查询：查询所有orders，关联其下单用户 resultMap方式
    public List<Orders> findOrdersListMap() throws Exception;

}

Test.java

// 一对一查询 orders关联user：resultMap方式
List<Orders> list = userMapper.findOrdersListMap();
System.out.println(list);
System.out.println(list.get(0).getUserBean().getUsername());

for (Orders i:list){
    System.out.println(i);
}

留言

专栏
文章
加入群聊
程序员兼职接单交流
编程交流