MyBatis学习（七）MyBatis关联映射之多对多映射
对于数据库中的多对多关系建议使用一个中间表来维护关系。
1.创建四张表，分别为用户表，商品表，订单表，中间表。
1 DROP TABLE IF EXISTS `t_user`;
2 CREATE TABLE `t_user` (
3 `id` int(11) NOT NULL AUTO_INCREMENT,
4 `username` varchar(18) NOT NULL,
5 `sex` char(2) NOT NULL,
6 `age` int(11) NOT NULL,
7 PRIMARY KEY (`id`)
8 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
9
10 -- ----------------------------
11 -- Records of t_user
12 -- ----------------------------
13 INSERT INTO `t_user` VALUES ('1', '张三', '男', '12');
14 INSERT INTO `t_user` VALUES ('2', '王五', '男', '12');
1 -- ----------------------------
2 -- Table structure for `t_article`
3 -- ----------------------------
4 DROP TABLE IF EXISTS `t_article`;
5 CREATE TABLE `t_article` (
6 `id` int(11) NOT NULL AUTO_INCREMENT,
7 `name` varchar(255) NOT NULL,
8 `price` int(11) NOT NULL,
9 `remark` varchar(255) NOT NULL,
10 PRIMARY KEY (`id`)
11 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
12
13 -- ----------------------------
14 -- Records of t_article
15 -- ----------------------------
16 INSERT INTO `t_article` VALUES ('1', 'java', '100', 'java');
17 INSERT INTO `t_article` VALUES ('2', 'spring', '100', '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 ('1', '1', '100');
INSERT INTO `t_order` VALUES ('2', '1', '200');
-- ----------------------------
-- 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 ('1', '1');
INSERT INTO `t_item` VALUES ('2', '1');
INSERT INTO `t_item` VALUES ('1', '2');
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语句结果如下图
然后将查询到的数据中的用户信息装载到Order对象的user属性中，将查询到column属性的oid的值作为参数，执行ArticleMapper.xml里面的selectArticleByOrderId查询订单中的商品，将查询到的商品数据封装到Order对象的articles对象当中。
结果如图所示：
测试成功！
