目录
关联映射
关联关系是面向对象分析、面向对象设计最终的思想,Mybatis完全可以理解这种关联关系,如果关系得当,Mybatis的关联映射将可以大大简化持久层数据的访问。关联关系大致可以分为以下情况:
1、一对一,一个人只能有一张身份证,而一张身份证只能属于一个人;
2、一对多,一个客户对应多个订单
3、多对多,一篇新闻对应多种类型,一种类型对应多篇新闻
当然,还有更复杂的关系,同样在购物系统中,一个用户可以有多个订单,而一个订单只能属于一个用户,再加上商品的关系就是一对多夹杂多对多的关系,但是万变都不离其中。
一对一
用户表(tb_user)和身份证表(tb_card)
一个用户对应一张身份证
tb_user:
tb_card:
2、创建相应的实体类对象(用user去关联card)、mapper层接口和mapper文件
实体类对象
public class Card {
private Integer cardId;//主键id
private String cardCode;//身份证号
//省略相应的get/set和构造方法
}
@Data
@ToString
public class User{
private Integer userId;//主键userId
private String userName;//姓名
private String userGender;//性别
private Integer userAge;//年龄
private Card card;//人和身份证是一对一的关系
}
mapper层接口:
@Repository
public interface CardMapper {
Card getCardById(Integer cardId);
}
@Repository
public interface UserMapper {
User getUserById(Integer userId);
}
mapper文件
UserMapper
<resultMap type="com.zepal.mybatis.domain.User" id="userMap">
<id column="user_id" property="userId"/>
<result column="user_name" property="userName"/>
<result column="user_gender" property="userGender"/>
<result column="user_age" property="userAge"/>
<!-- 一对一关联,select属性是通过namespace引入的card查询 -->
<association property="card" column="card_id"
select="com.zepal.mybatis.dao.CardDao.getCardById"
javaType="com.zepal.mybatis.domain.Card">
<id column="card_id" property="cardId"/>
<result column="card_code" property="cardCode"/>
</association>
</resultMap>
<select id="getUserById" parameterType="int" resultMap="userMap">
SELECT * FROM tb_user WHERE user_id = #{userId};
</select>
cardMapper
<resultMap type="com.zepal.mybatis.domain.Card" id="cardMap">
<id column="card_id" property="cardId"/>
<result column="card_code" property="cardCode"/>
</resultMap>
<select id="getCardById" parameterType="int" resultMap="cardMap">
SELECT * FROM tb_card WHERE card_id = #{cardId};
</select>
3、测试代码和结果
package com.zking.ssm.book.service.impl;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring.xml"})
public class BaseTest {
@Autowired
@private UserService userservice;
@Test
public void getUserById(){
User user = userDao.getUserById(userId);
System.out.println(user.toString());
}
}
User [userId=1, userName=张三, userGender=男, userAge=18, card=Card [cardId=2, cardCode=12345619900801123x]]
一对多、多对一
1、按照上举例的客户和订单的关系创建如下两张表t_customer(客户)和t_order(订单)
t_customer:
t_order:
2、创建相应的实体类对象、mapper层接口和mapper文件
实体类对象
/**
* 一对多
*/
public class Customer {
private Integer customerId;
private String customerName;
private List<Order> orders;
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
public Customer(Integer customerId, String customerName) {
this.customerId = customerId;
this.customerName = customerName;
}
public Customer() {
super();
}
public Integer getCustomerId() {
return customerId;
}
public void setCustomerId(Integer customerId) {
this.customerId = customerId;
}
public String getCustomerName() {
return customerName;
}
public void setCustomerName(String customerName) {
this.customerName = customerName;
}
@Override
public String toString() {
return "Customer{" +
"customerId=" + customerId +
", customerName='" + customerName + '\'' +
", orders=" + orders +
'}';
}
}
/**
* 多对一
*/
public class Order {
private Integer orderId;
private String orderNo;
private Integer cid;
private Customer customer;
public Customer getCustomer() {
return customer;
}
public void setCustomer(Customer customer) {
this.customer = customer;
}
public Order(Integer orderId, String orderNo, Integer cid) {
this.orderId = orderId;
this.orderNo = orderNo;
this.cid = cid;
}
public Order() {
super();
}
public Integer getOrderId() {
return orderId;
}
public void setOrderId(Integer orderId) {
this.orderId = orderId;
}
public String getOrderNo() {
return orderNo;
}
public void setOrderNo(String orderNo) {
this.orderNo = orderNo;
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
@Override
public String toString() {
return "Order{" +
"orderId=" + orderId +
", orderNo='" + orderNo + '\'' +
", cid=" + cid +
", customer=" + customer +
'}';
}
}
Mapper层接口:
CustomerMapper
@Repository
public interface CustomerMapper {
List<Customer> queryOrders();
}
OrderMapper
@Repository
public interface OrderMapper {
Order queryOrderId(Integer OrderId);
}
Mapper文件
OrderMapper.xml
<resultMap id="ManyToOne" type="com.zking.ssm.book.model.Order">
<id property="orderId" javaType="java.lang.Integer" column="order_Id" />
<result property="orderNo" javaType="java.lang.String" column="order_No"/>
<result property="orderId" javaType="java.lang.Integer" column="order_Id"/>
<association property="customer" javaType="com.zking.ssm.book.model.Customer">
<id property="customerId" javaType="java.lang.Integer" column="customer_Id" />
<result property="customerName" javaType="java.lang.String" column="customer_Name"/>
</association>
</resultMap>
<select id="queryOrderId" resultMap="ManyToOne">
select * from t_customer c inner join t_order o on c.customer_id=o.cid
where o.order_id=#{value}
</select>
CustomerMapper.xml
<resultMap id="oneToMany" type="com.zking.ssm.book.model.Customer">
<id property="customerId" javaType="java.lang.Integer" column="customer_Id" />
<result property="customerName" javaType="java.lang.String" column="customer_Name"/>
<collection property="orders" ofType="com.zking.ssm.book.model.Order">
<id property="orderId" javaType="java.lang.Integer" column="order_Id" />
<result property="orderNo" javaType="java.lang.String" column="order_No"/>
<result property="orderId" javaType="java.lang.Integer" column="order_Id"/>
</collection>
</resultMap>
<select id="queryOrders" resultMap="oneToMany">
select * from t_customer c left join t_order o on c.customer_id=o.cid
</select>
3、测试代码和结果
多对一的关系
多个订单对应一个客户
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring.xml"})
public class OrderServiceImplTest{
@Autowired
private IOrderService iOrderService;
@Test
public void queryOrderId() {
Order order = iOrderService.queryOrderId(3);
System.out.println(order);
}
}
Order{orderId=3, orderNo='P03', cid=null, customer=Customer{customerId=1, customerName='zs', orders=null}}
一对多的关系
一个客户对应多个订单
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring.xml"})
public class CustomerServiceImplTest{
@Autowired
private ICustomerService iCustomerService;
@Test
public void queryOrders() {
List<Customer> orders = iCustomerService.queryOrders();
orders.forEach(System.out::println);
}
}
Customer{customerId=1, customerName='zs', orders=[Order{orderId=1, orderNo='P01', cid=null, customer=null}, Order{orderId=2, orderNo='P02', cid=null, customer=null}, Order{orderId=3, orderNo='P03', cid=null, customer=null}, Order{orderId=4, orderNo='P04', cid=null, customer=null}, Order{orderId=5, orderNo='P05', cid=null, customer=null}]}
Customer{customerId=2, customerName='ls', orders=[Order{orderId=6, orderNo='P06', cid=null, customer=null}, Order{orderId=7, orderNo='P07', cid=null, customer=null}, Order{orderId=8, orderNo='P08', cid=null, customer=null}]}
Customer{customerId=3, customerName='ww', orders=[Order{orderId=9, orderNo='P09', cid=null, customer=null}, Order{orderId=10, orderNo='P10', cid=null, customer=null}]}
Customer{customerId=4, customerName='xm', orders=[]}
多对多
1、给出三张表 t_news(新闻)t_category(新闻类型) t_news_category(桥接表)
t_news:
t_category:
t_news_category:
2、创建相应的实体类对象、mapper层接口和mapper文件
实体类对象
public class News {
private Integer newsId;
private String title;
private List<Category> categoryList;
public List<Category> getCategoryList() {
return categoryList;
}
public void setCategoryList(List<Category> categoryList) {
this.categoryList = categoryList;
}
public News(Integer newsId, String title) {
this.newsId = newsId;
this.title = title;
}
public News() {
super();
}
public Integer getNewsId() {
return newsId;
}
public void setNewsId(Integer newsId) {
this.newsId = newsId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
@Override
public String toString() {
return "News{" +
"newsId=" + newsId +
", title='" + title + '\'' +
", categoryList=" + categoryList +
'}';
}
}
public class Category {
private Integer categoryId;
private String categoryName;
private List<News> newsList;
public List<News> getNewsList() {
return newsList;
}
public void setNewsList(List<News> newsList) {
this.newsList = newsList;
}
public Category(Integer categoryId, String categoryName) {
this.categoryId = categoryId;
this.categoryName = categoryName;
}
public Category() {
super();
}
public Integer getCategoryId() {
return categoryId;
}
public void setCategoryId(Integer categoryId) {
this.categoryId = categoryId;
}
public String getCategoryName() {
return categoryName;
}
public void setCategoryName(String categoryName) {
this.categoryName = categoryName;
}
@Override
public String toString() {
return "Category{" +
"categoryId=" + categoryId +
", categoryName='" + categoryName + '\'' +
", newsList=" + newsList +
'}';
}
}
public class News_Category {
private Integer id;
private Integer nid;
private Integer cid;
private News news;
private Category category;
public News getNews() {
return news;
}
public void setNews(News news) {
this.news = news;
}
public Category getCategory() {
return category;
}
public void setCategory(Category category) {
this.category = category;
}
public News_Category(Integer id, Integer nid, Integer cid) {
this.id = id;
this.nid = nid;
this.cid = cid;
}
public News_Category() {
super();
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getNid() {
return nid;
}
public void setNid(Integer nid) {
this.nid = nid;
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
@Override
public String toString() {
return "News_Category{" +
"id=" + id +
", nid=" + nid +
", cid=" + cid +
", news=" + news +
", category=" + category +
'}';
}
}
mapper层接口
News_CategoryMapper
@Repository
public interface News_CategoryMapper {
//根据新闻id查询
List<News_Category> queryNewsToMany(Integer newsId);
//根据类型id查询
List<News_Category> queryCategoryToMany(Integer categoryId);
}
mapper文件
News_CategoryMapper.xml
<resultMap id="ManyToMany" type="com.zking.ssm.book.model.News_Category">
<id property="id" javaType="java.lang.Integer" column="id"/>
<result property="nid" javaType="java.lang.Integer" column="nid"/>
<result property="cid" javaType="java.lang.Integer" column="cid"/>
<association property="news" javaType="com.zking.ssm.book.model.News">
<id property="newsId" javaType="java.lang.Integer" column="category_Id"/>
<result property="title" javaType="java.lang.String" column="title"/>
</association>
<association property="category" javaType="com.zking.ssm.book.model.Category">
<id property="categoryId" javaType="java.lang.Integer" column="category_Id"/>
<result property="categoryName" javaType="java.lang.String" column="category_Name"/>
</association>
</resultMap>
<select id="queryNewsToMany" resultMap="ManyToMany">
select
*
from
t_news_category nc left join t_news n on nc.nid=n.news_id
left join t_category c on nc.cid =c.category_id where n.news_id=#{value}
</select>
<select id="queryCategoryToMany" resultMap="ManyToMany">
select
*
from
t_news_category nc left join t_news n on nc.nid=n.news_id
left join t_category c on nc.cid =c.category_id where c.category_id=#{value}
</select>
3、测试代码和结果
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring.xml"})
public class News_CategoryServiceImplTest{
@Autowired
private INews_CategoryService iNews_categoryService;
@Test
public void queryNewsToMany() {
List<News_Category> news = iNews_categoryService.queryNewsToMany(1);
news.forEach(System.out::println);
}
@Test
public void queryCategoryToMany() {
List<News_Category> categorys = iNews_categoryService.queryCategoryToMany(1);
categorys.forEach(System.out::println);
}
}
queryNewsToMany:
News_Category{id=1, nid=1, cid=1, news=News{newsId=1, title='110', categoryList=null}, category=Category{categoryId=1, categoryName='焦点', newsList=[News{newsId=1, title='110', categoryList=null}]}}
News_Category{id=2, nid=1, cid=2, news=News{newsId=2, title='110', categoryList=null}, category=Category{categoryId=2, categoryName='国际', newsList=[News{newsId=2, title='110', categoryList=null}]}}
queryCategoryToMany:
News_Category{id=1, nid=1, cid=1, news=News{newsId=1, title='110', categoryList=null}, category=Category{categoryId=1, categoryName='焦点', newsList=null}}
News_Category{id=3, nid=2, cid=1, news=News{newsId=1, title='111', categoryList=null}, category=Category{categoryId=1, categoryName='焦点', newsList=null}}
News_Category{id=5, nid=3, cid=1, news=News{newsId=1, title='112', categoryList=null}, category=Category{categoryId=1, categoryName='焦点', newsList=null}}
News_Category{id=8, nid=4, cid=1, news=News{newsId=1, title='113', categoryList=null}, category=Category{categoryId=1, categoryName='焦点', newsList=null}}
至此,Mybatis的关联映射介绍完毕,由于作者水平有限难免有疏漏,欢迎留言纠错。