一、概念
一对多(多对一)是指一方持有多方的引用。例如:去京东购物，那么一个京东用户可以对应多个购物订单
二、实现思路
- 确定查询的主表： 用户表user
- 确定查询的关联表：订单表orders
- 主表与子表采用外键关联
- 关系模型
- 对象模型
三、实现方式
- 嵌套查询
- 连接查询
四、栗子
SQL
DROP TABLE IF EXISTS `user`;
CREATE TABLE user
(
uid INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64) UNICODE NOT NULL COMMENT '用户名',
password VARCHAR(128) NOT NULL COMMENT '密码',
phone VARCHAR(11) UNIQUE NOT NULL COMMENT '手机号',
email VARCHAR(128) UNIQUE NOT NULL COMMENT '用户邮箱',
join_date TIMESTAMP DEFAULT current_timestamp COMMENT '创建时间',
login_date TIMESTAMP DEFAULT current_timestamp
ON UPDATE current_timestamp COMMENT '最后一次登录时间',
status TINYINT DEFAULT 1 COMMENT '0 表示禁用 1 表示正常'
)
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order`
(
`order_id` INT(11) AUTO_INCREMENT,
`order_code` VARCHAR(128) NOT NULL,
`uid` INT(11) NOT NULL,
`message` VARCHAR(255) NOT NULL,
`create_date` DATETIME(6) DEFAULT NULL,
`pay_date` DATETIME(6) DEFAULT NULL,
`status` INT(11) NOT NULL,
PRIMARY KEY (`order_id`),
CONSTRAINT `fk_order_uid` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`)
)
编写对象
public class User {
private Integer uid;
/**
* 用户名
*/
private String name;
/**
* 密码
*/
private String password;
/**
* 手机号
*/
private String phone;
/**
* 用户邮箱
*/
private String email;
/**
* 创建时间
*/
private Date joinDate;
/**
* 最后一次登录时间
*/
private Date loginDate;
/**
* 0 表示禁用 1 表示正常
*/
private int status;
/**
* 订单信息
*/
private List<Orders> orders;
}
public class Orders {
/**
* 主键订单Id
*/
private Integer ordersId;
/**
* 下单用户id
*/
private Integer userId;
/**
* 订单号
*/
private String number;
/**
* 创建订单时间
*/
private Date createTime;
/**
* 备注
*/
private String note;
编写Mapper.java
public interface UserMapper {
/**
* 获得所有用户的所有订单订单信息
* @return
*/
List<User> getUsersOrders();
/**
* 根据用户id查询所有的订单信息
* @param uid
* @return
*/
public User findOrdersById(@Param("uid") int uid);
/**
* 根据用户id查询所有的订单信息
* @param uid
* @return
*/
public User findOrdersById2(@Param("uid") int uid);
}
public interface OrdersMapper {
public List<Orders> findByUserId(@Param("uid") int uid);
}
编写mapper.xml
<!--在使用MyBatis嵌套查询方式进行关联查询时，使用MyBatis的延迟加载可以在一定程度上提高查询效率-->
<settings>
<!--打开延迟加载的开关-->
<setting name= "lazyLoadingEnabled" value= "true"/>
<!--将积极加载改为按需加载-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.vip.mybatis.mapper.UserMapper">
<sql id="base_column_list">
`uid`, `name`, `password`, phone, email, join_date, login_date, `status`
</sql>
<!-- 一对多 根据uid查询用户及其关联的订单信息：嵌套查询 -->
<resultMap type="User" id="userAndOrders">
<id column="uid" jdbcType="INTEGER" property="uid"/>
<result column="name" jdbcType="VARCHAR" property="name"/>
<result column="password" jdbcType="VARCHAR" property="password"/>
<result column="phone" jdbcType="VARCHAR" property="phone"/>
<result column="email" jdbcType="VARCHAR" property="email"/>
<result column="join_date" jdbcType="TIMESTAMP" property="joinDate"/>
<result column="login_date" jdbcType="TIMESTAMP" property="loginDate"/>
<result column="status" jdbcType="TINYINT" property="status"/>
<!-- 一对多级联查询，ofType表示集合中的元素类型-->
<collection property="orders" ofType="Orders"
column="uid"
select="com.vip.mybatis.mapper.OrdersMapper.findByUserId"/>
</resultMap>
<select id="findOrdersById" resultMap="userAndOrders">
SELECT
<include refid="base_column_list"/>
FROM user
WHERE uid=#{uid}
</select>
<!-- 方式二 -->
<!--对多根据uid查询用户及其关联的订单信息：级联查询的第二种方法（嵌套结果） -->
<resultMap type="User" id="userAndOrders2">
<id column="uid" jdbcType="INTEGER" property="uid"/>
<result column="name" jdbcType="VARCHAR" property="name"/>
<!-- 一对多级联查询，ofType表示集合中的元素类型-->
<collection property="orders" ofType="Orders"
resultMap="com.vip.mybatis.mapper.OrdersMapper.BaseResultMap"/>
</resultMap>
<select id="findOrdersById2" resultMap="userAndOrders2">
SELECT u.uid, u.name, u.password, u.phone, u.email, u.join_date, u.login_date, u.status , o.order_code,o.order_id, o.order_code, o.uid, o.message, o.create_date, o.pay_date, o.status
FROM user u, orders o
WHERE u.uid = o.uid AND u.uid=#{uid}
</select>
</mapper>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.vip.mybatis.mapper.OrdersMapper">
<resultMap id="BaseResultMap" type="com.vip.mybatis.entity.Orders">
<!--@mbg.generated-->
<id column="order_id" jdbcType="INTEGER" property="orderId"/>
<result column="order_code" jdbcType="VARCHAR" property="orderCode"/>
<result column="uid" jdbcType="INTEGER" property="uid"/>
<result column="message" jdbcType="VARCHAR" property="message"/>
<result column="create_date" jdbcType="TIMESTAMP" property="createDate"/>
<result column="pay_date" jdbcType="TIMESTAMP" property="payDate"/>
<result column="status" jdbcType="INTEGER" property="status"/>
</resultMap>
<sql id="Base_Column_List">
<!--@mbg.generated-->
order_id, order_code, `uid`, message, create_date, pay_date, `status`
</sql>
<select id="findByUserId" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM orders
WHERE uid = #{uid}
</select>
</mapper>
测试代码
public static void main(String[] args) {
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(
App.class.getClassLoader().getResourceAsStream("mybatis-config.xml")
);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.findOrdersById(1);
User user1 = mapper.findOrdersById2(1);
System.out.println(user.toString());
System.out.println(user1);
}
网友评论