一、概念
一對(duì)多(多對(duì)一)是指一方持有多方的引用。例如:去京東購(gòu)物，那么一個(gè)京東用戶(hù)可以對(duì)應(yīng)多個(gè)購(gòu)物訂單
二、實(shí)現(xiàn)思路
- 確定查詢(xún)的主表： 用戶(hù)表user
- 確定查詢(xún)的關(guān)聯(lián)表：訂單表orders
- 主表與子表采用外鍵關(guān)聯(lián)
- 關(guān)系模型
- 對(duì)象模型
三、實(shí)現(xiàn)方式
嵌套查詢(xún)
連接查詢(xún)
四、栗子
SQL
DROP TABLE IF EXISTS `user`;
CREATE TABLE user
(
uid INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64) UNICODE NOT NULL COMMENT '用戶(hù)名',
password VARCHAR(128) NOT NULL COMMENT '密碼',
phone VARCHAR(11) UNIQUE NOT NULL COMMENT '手機(jī)號(hào)',
email VARCHAR(128) UNIQUE NOT NULL COMMENT '用戶(hù)郵箱',
join_date TIMESTAMP DEFAULT current_timestamp COMMENT '創(chuàng)建時(shí)間',
login_date TIMESTAMP DEFAULT current_timestamp
ON UPDATE current_timestamp COMMENT '最后一次登錄時(shí)間',
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`)
)
編寫(xiě)對(duì)象
public class User {
private Integer uid;
/**
* 用戶(hù)名
*/
private String name;
/**
* 密碼
*/
private String password;
/**
* 手機(jī)號(hào)
*/
private String phone;
/**
* 用戶(hù)郵箱
*/
private String email;
/**
* 創(chuàng)建時(shí)間
*/
private Date joinDate;
/**
* 最后一次登錄時(shí)間
*/
private Date loginDate;
/**
* 0 表示禁用 1 表示正常
*/
private int status;
/**
* 訂單信息
*/
private List<Orders> orders;
}
public class Orders {
/**
* 主鍵訂單Id
*/
private Integer ordersId;
/**
* 下單用戶(hù)id
*/
private Integer userId;
/**
* 訂單號(hào)
*/
private String number;
/**
* 創(chuàng)建訂單時(shí)間
*/
private Date createTime;
/**
* 備注
*/
private String note;
編寫(xiě)Mapper.java
public interface UserMapper {
/**
* 獲得所有用戶(hù)的所有訂單訂單信息
* @return
*/
List<User> getUsersOrders();
/**
* 根據(jù)用戶(hù)id查詢(xún)所有的訂單信息
* @param uid
* @return
*/
public User findOrdersById(@Param("uid") int uid);
/**
* 根據(jù)用戶(hù)id查詢(xún)所有的訂單信息
* @param uid
* @return
*/
public User findOrdersById2(@Param("uid") int uid);
}
public interface OrdersMapper {
public List<Orders> findByUserId(@Param("uid") int uid);
}
編寫(xiě)mapper.xml
<!--在使用MyBatis嵌套查詢(xún)方式進(jìn)行關(guān)聯(lián)查詢(xún)時(shí)，使用MyBatis的延遲加載可以在一定程度上提高查詢(xún)效率-->
<settings>
<!--打開(kāi)延遲加載的開(kāi)關(guān)-->
<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>
<!-- 一對(duì)多 根據(jù)uid查詢(xún)用戶(hù)及其關(guān)聯(lián)的訂單信息：嵌套查詢(xún) -->
<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"/>
<!-- 一對(duì)多級(jí)聯(lián)查詢(xún)，ofType表示集合中的元素類(lèi)型-->
<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>
<!-- 方式二 -->
<!--對(duì)多根據(jù)uid查詢(xún)用戶(hù)及其關(guān)聯(lián)的訂單信息：級(jí)聯(lián)查詢(xún)的第二種方法（嵌套結(jié)果） -->
<resultMap type="User" id="userAndOrders2">
<id column="uid" jdbcType="INTEGER" property="uid"/>
<result column="name" jdbcType="VARCHAR" property="name"/>
<!-- 一對(duì)多級(jí)聯(lián)查詢(xún)，ofType表示集合中的元素類(lèi)型-->
<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>
測(cè)試代碼
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);
}
