MyBatis之多对多关联查询

多对多关联

myBatis的多对多关联，是通过两个一对多间接实现的

-- 用户表 CREATE TABLE `user2`( `id` INT PRIMARY KEY AUTO_INCREMENT, `user_name` VARCHAR(20),-- 用户姓名 `address` VARCHAR(60)-- 联系地址 ) ENGINE INNODB CHARSET utf8; INSERT INTO `user2` VALUES(1,'陈大','深圳市南山区'); INSERT INTO `user2` VALUES(2,'王二','深圳市福田区'); INSERT INTO `user2` VALUES(3,'张三','深圳市龙华新区'); INSERT INTO `user2` VALUES(4,'李四','深圳市龙岗区'); -- 卡表 CREATE TABLE `card2`( `id` INT PRIMARY KEY AUTO_INCREMENT, `card_no` VARCHAR(18), `remark` VARCHAR(100) ) ENGINE=INNODB CHARSET=utf8; INSERT INTO `card2` VALUES(1,'420001','会员卡'); INSERT INTO `card2` VALUES(2,'420002','会员卡'); INSERT INTO `card2` VALUES(3,'420003','会员卡'); INSERT INTO `card2` VALUES(4,'420004','会员卡'); -- 用户、卡的中间表 CREATE TABLE `card2_user2`( `id` INT PRIMARY KEY AUTO_INCREMENT, `user_id` INT, `card_id` INT, `remark` VARCHAR(100) ); INSERT INTO `card2_user2` VALUES(1,1,1,''); INSERT INTO `card2_user2` VALUES(2,1,2,''); INSERT INTO `card2_user2` VALUES(3,2,1,''); INSERT INTO `card2_user2` VALUES(4,2,2,''); INSERT INTO `card2_user2` VALUES(5,3,1,''); INSERT INTO `card2_user2` VALUES(6,4,2,''); -- SELECT * FROM `user2`; -- SELECT * FROM `card2`; -- SELECT * FROM `card2_user2`; SELECT t1.`id`, t2.`id` `user_id`,t2.`user_name`,t2.`address`, t3.`id` `card_id`,t3.`card_no`,t3.`remark` `card_info`, t1.`remark` FROM `card2_user2` t1,`user2` t2,`card2` t3 WHERE t1.`user_id`=t2.`id` AND t1.`card_id`=t3.`id`;实体类：Card1

package com.chensan.m2m.entity; import java.util.List; public class Card1 { private int id; private String cardNo; private String remark; private List<User1> users; public String toString(){ return "[ id = " + id + ", cardNo = " + cardNo + ", remark = " + remark + "]"; } //...省略setter、getter方法 }实体类：User1

package com.chensan.m2m.entity; import java.util.List; public class User1 { private int id; private String userName; private String address; private List<Card1> cards; public String toString(){ return "[ id = " + id + ", userName = " + userName + ", address = " + address + "]"; } //...省略setter、getter方法 }实体类Card1映射文件

<?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.chensan.m2m.entity.Card1Mapper"> <resultMap id="card1Map" type="com.chensan.m2m.entity.Card1"> <id property="id" column="card_id"/> <result property="cardNo" column="card_no"/> <result property="remark" column="card_info"/> </resultMap> <resultMap id="cardUserMap" type="com.chensan.m2m.entity.Card1" extends="card1Map"> <collection property="users" ofType="com.chensan.m2m.entity.User1"> <id property="id" column="user_id"/> <result property="userName" column="user_name"/> <result property="address" column="address"/> </collection> </resultMap> <select id="query" parameterType="int" resultMap="cardUserMap"> SELECT t1.`id`, t2.`id` `card_id`,t2.`card_no`,t2.`remark` `card_info`, t3.`id` `user_id`,t3.`user_name`,t3.`address`, t1.`remark` FROM `card2_user2` t1,`card2` t2,`user2` t3 WHERE t1.`user_id`=t2.`id` AND t1.`card_id`=t3.`id` AND t2.`id`=#{id} </select> </mapper>

实体类User1映射文件

<?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.chensan.m2m.entity.User1Mapper"> <resultMap id="user1Map" type="com.chensan.m2m.entity.User1"> <id property="id" column="user_id"/> <result property="userName" column="user_name"/> <result property="address" column="address"/> </resultMap> <resultMap id="userCardMap" type="com.chensan.m2m.entity.User1" extends="user1Map"> <collection property="cards" ofType="com.chensan.m2m.entity.Card1"> <id property="id" column="card_id"/> <result property="cardNo" column="card_no"/> <result property="remark" column="card_info"/> </collection> </resultMap> <select id="query" parameterType="int" resultMap="userCardMap"> SELECT t1.`id`, t2.`id` `user_id`,t2.`user_name`,t2.`address`, t3.`id` `card_id`,t3.`card_no`,t3.`remark` `card_info`, t1.`remark` FROM `card2_user2` t1,`user2` t2,`card2` t3 WHERE t1.`user_id`=t2.`id` AND t1.`card_id`=t3.`id` AND t2.`id`=#{id} </select> </mapper>

使用extends实体类的方法是为了保留和扩展实体的操作；

myBatis配置文件

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 和spring整合后 environments配置将废除--> <environments default="development"> <environment id="development"> <!-- 使用jdbc事务管理 或者JTA事务管理--> <transactionManager type="JDBC" /> <!-- 数据库连接池 第三方组件：c3p0--> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis01"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> <!-- 加载实体类的映射文件 --> <mappers> <mapper resource="com/chensan/m2m/mapper/User1Mapper.xml"/> <mapper resource="com/chensan/m2m/mapper/Card1Mapper.xml"/> </mappers> </configuration>测试类

package com.chensan.m2m.test; import java.io.Reader; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import com.chensan.m2m.entity.Card1; import com.chensan.m2m.entity.User1; public class TestM2M { private static SqlSessionFactory sqlSessionFactory; private static Reader resource; //创建会话工厂 static{ try{ resource = Resources.getResourceAsReader("myBatisConfig.xml"); sqlSessionFactory = new SqlSessionFactoryBuilder().build(resource); }catch(Exception e){ e.printStackTrace(); } } public static SqlSessionFactory getSession(){ return sqlSessionFactory; } //多对多：查询用户对应卡（会员卡） public void getUserCard(){ SqlSession sqlSession = sqlSessionFactory.openSession(); User1 user = sqlSession.selectOne("com.chensan.m2m.entity.User1Mapper.query", 1); System.out.println(user); for(Card1 card : user.getCards()){ System.out.println( "[ " + "userId = " + user.getId() + ", " + "userName = " + user.getUserName() + ", " + "address = " + user.getAddress() + ", " + "cardId = " + card.getId() + ", " + "cardNo = " + card.getCardNo() + ", " + "remark = " + card.getRemark() + " ]" ); } sqlSession.close(); } //多对多：查询卡（会员卡）对应用户 public void getCardUser(){ SqlSession sqlSession = sqlSessionFactory.openSession(); Card1 card = sqlSession.selectOne("com.chensan.m2m.entity.Card1Mapper.query", 1); System.out.println(card); for(User1 user : card.getUsers()){ System.out.println( "[ " + "cardId = " + card.getId() + ", " + "cardNo = " + card.getCardNo() + ", " + "remark = " + card.getRemark() + ", " + "userId = " + user.getId() + ", " + "userName = " + user.getUserName() + ", " + "address = " + user.getAddress() + " ]" ); } sqlSession.close(); } public static void main(String[] args) { TestM2M testMyBatisManyToMany = new TestM2M(); testMyBatisManyToMany.getUserCard(); testMyBatisManyToMany.getCardUser(); } }结果如下：

[ id = 1, userName = 陈大, address = 深圳市南山区] [ userId = 1, userName = 陈大, address = 深圳市南山区, cardId = 1, cardNo = 420001, remark = 会员卡 ] [ userId = 1, userName = 陈大, address = 深圳市南山区, cardId = 2, cardNo = 420002, remark = 会员卡 ] [ id = 1, cardNo = 420001, remark = 会员卡] [ cardId = 1, cardNo = 420001, remark = 会员卡, userId = 1, userName = 陈大, address = 深圳市南山区 ] [ cardId = 1, cardNo = 420001, remark = 会员卡, userId = 2, userName = 王二, address = 深圳市福田区 ]

请给同名列取别名