MyBatis学习(七)MyBatis关联映射之多对多映射

对于数据库中的多对多关系建议使用一个中间表来维护关系。

1.创建四张表,分别为用户表,商品表,订单表,中间表。

 DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(18) NOT NULL,
`sex` char(2) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES ('', '张三', '男', '');
INSERT INTO `t_user` VALUES ('', '王五', '男', '');

user表

 -- ----------------------------
-- Table structure for `t_article`
-- ----------------------------
DROP TABLE IF EXISTS `t_article`;
CREATE TABLE `t_article` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`price` int(11) NOT NULL,
`remark` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- ----------------------------
-- Records of t_article
-- ----------------------------
INSERT INTO `t_article` VALUES ('', 'java', '', 'java');
INSERT INTO `t_article` VALUES ('', 'spring', '', 'spring');

商品表

-- ----------------------------
-- Table structure for `t_order`
-- ----------------------------
DROP TABLE IF EXISTS `t_order`;
CREATE TABLE `t_order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`total` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `uid` FOREIGN KEY (`uid`) REFERENCES `t_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- ----------------------------
-- Records of t_order
-- ----------------------------
INSERT INTO `t_order` VALUES ('', '', '');
INSERT INTO `t_order` VALUES ('', '', '');

订单表

-- ----------------------------
-- Table structure for `t_item`
-- ----------------------------
DROP TABLE IF EXISTS `t_item`;
CREATE TABLE `t_item` (
`order_id` int(11) NOT NULL,
`article_id` int(11) NOT NULL,
PRIMARY KEY (`order_id`,`article_id`),
KEY `article_id` (`article_id`),
CONSTRAINT `article_id` FOREIGN KEY (`article_id`) REFERENCES `t_article` (`id`),
CONSTRAINT `order_id` FOREIGN KEY (`order_id`) REFERENCES `t_order` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ----------------------------
-- Records of t_item
-- ----------------------------
INSERT INTO `t_item` VALUES ('', '');
INSERT INTO `t_item` VALUES ('', '');
INSERT INTO `t_item` VALUES ('', '');

中间表

2.创建一个项目,导入所需的jar包,导入db.perproties、log4j.properties属性文件

3.编写对应的实体类

User.java

public class User {

    private Integer id;
private String username;
private String sex;
private Integer age;
//一个用户可以有多个订单
private List<Order> orders;
//省略set、get、toString方法
}

Order.java

public class Order {

    private Integer id;
private Double total;
//一个订单对应一个用户
private User user;
//一个订单可以有多个商品
private List<Article> articles;
//省略set、get、toString方法
}

Article.java

public class Article {

    private Integer id;
private String name;
private Double price;
private String remark;
//一种商品可以出现在多个订单中
private List<Order> orders;
//省略get、set、toString方法
}

4.编写SQL映射文件

OrderMapper.xml

<?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.dj.mapper.OrderMapper">
<resultMap type="com.dj.domain.Order" id="orderResultMapper">
<id property="id" column="oid"/>
<result property="total" column="total"/>
<!-- 多对一关联映射 -->
<association property="user" javaType="com.dj.domain.User">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
</association>
<!-- 多对多映射 -->
<!-- 使用查询到的column属性oid的值作为参数执行selectArticleByOrderId -->
<collection property="articles" column="oid" fetchType="lazy"
javaType="ArrayList" ofType="com.dj.domain.Article"
select="com.dj.mapper.ArticleMapper.selectArticleByOrderId">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="price" column="price"/>
<result property="remark" column="remark"/>
</collection>
</resultMap>
<!-- 查询结果有两个id列,所以把order的id取一个别名oid -->
<select id="selectOrderById" parameterType="int" resultMap="orderResultMapper">
select u.*,o.id as oid ,o.total
from t_user u,t_order o
where u.id=o.uid and o.id=#{id}
</select>
</mapper>

ArticleMapper.xml

<?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.dj.mapper.ArticleMapper">
<select id="selectArticleByOrderId" parameterType="int"
resultType="com.dj.domain.Article">
select * from t_article where id in(
select article_id from t_item where order_id=#{id}
)
</select>
</mapper>

5.mapper接口对象

public interface OrderMapper {
Order selectOrderById(int id);
}

6.测试类

public class SelectOrderTest {
public static void main(String[] args) throws Exception {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = factory.openSession();
//获得接口的代理对象
OrderMapper om = session.getMapper(OrderMapper.class);
Order order = om.selectOrderById(1);
System.out.println(order);
User user = order.getUser();
System.out.println(user);
List<Article> articles = order.getArticles();
for (Article article : articles) {
System.out.println(article);
}
}
}

  流程:

  得到一个OrderMapper接口的代理对象,调用里面的selectOrderById(int id)方法,找到OrderMapper.xml里面 id为selectOrderById的select属性,返回的是一个resultMap,找到这个resultMap,这个resultMap返回的是一个Order类型。

  执行id为selecOrderById的select属性里面的SQL语句结果如下图

  MyBatis学习(七)MyBatis关联映射之多对多映射

  然后将查询到的数据中的用户信息装载到Order对象的user属性中,将查询到column属性的oid的值作为参数,执行ArticleMapper.xml里面的selectArticleByOrderId查询订单中的商品,将查询到的商品数据封装到Order对象的articles对象当中。

  结果如图所示:

MyBatis学习(七)MyBatis关联映射之多对多映射

测试成功!

上一篇:[bzoj 2151]种树(贪心)


下一篇:SQL SERVER 2000/2005/2008数据库数据迁移到Oracle 10G细述