MyBatis之多对一关联查询

多对一关联

多张卡（银行卡）对一个用户

表结构（一对多、多对一、一对一的表结构相同）：

-- 用户表 CREATE TABLE `user1`( `id` INT PRIMARY KEY AUTO_INCREMENT, `user_name` VARCHAR(20),-- 用户姓名 `address` VARCHAR(60)-- 联系地址 ) ENGINE INNODB CHARSET utf8; INSERT INTO `user1` VALUES(1,'陈大','深圳市南山区'); INSERT INTO `user1` VALUES(2,'王二','深圳市福田区'); INSERT INTO `user1` VALUES(3,'张三','深圳市龙华新区'); INSERT INTO `user1` VALUES(4,'李四','深圳市龙岗区'); -- 卡表 CREATE TABLE `card1`( `id` INT PRIMARY KEY AUTO_INCREMENT, `card_no` VARCHAR(18), `remark` VARCHAR(100), `user_id` INT-- 用于关联user1的主键id(不设置外键，避免级联问题) ) ENGINE=INNODB CHARSET=utf8; INSERT INTO `card1` VALUES(1,'420001','工资卡',1); INSERT INTO `card1` VALUES(2,'420002','工资卡',2); INSERT INTO `card1` VALUES(3,'420003','工资卡',3); INSERT INTO `card1` VALUES(4,'420004','工资卡',3); -- SELECT * FROM `user1`; -- SELECT * FROM `card1`;实体类：Card1 package com.chensan.m2o.entity; public class Card1 { private int id; private String cardNo; private String remark; private User1 user; public String toString(){ return "[ id = " + id + ", cardNo = " + cardNo + ", remark = " + remark + "]"; } //...省略setter、getter方法 }实体类：User1

package com.chensan.m2o.entity; public class User1 { private int id; private String userName; private String 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.m2o.entity.Card1Mapper"> <resultMap id="card1" type="com.chensan.m2o.entity.Card1"> <id property="id" column="id"/> <result property="cardNo" column="card_no"/> <result property="remark" column="remark"/> <association property="user" column="user_id" javaType="com.chensan.m2o.entity.User1"> <id property="id" column="user_id"/> <result property="userName" column="user_name"/> <result property="address" column="address"/> </association> </resultMap> <!-- 多对一：多张卡（银行卡）对一个用户 --> <select id="query" parameterType="int" resultMap="card1"> <!-- SELECT t1.`id`,t1.`card_no`,t1.`remark`,t2.`id` `user_id`,t2.`user_name`,t2.`address` FROM `card1` t1, `user1` t2 WHERE t1.`user_id`=t2.`id` AND t1.`id`=#{id} --> SELECT t1.`id`,t1.`card_no`,t1.`remark`,t2.`id` `user_id`,t2.`user_name`,t2.`address` FROM `card1` t1, `user1` t2 WHERE t1.`user_id`=t2.`id` AND t1.`user_id`=#{id} </select> </mapper>不须从User1端操作，不用User1Mapper.xml配置文件

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/m2o/mapper/Card1Mapper.xml"/> </mappers> </configuration>测试类

package com.chensan.m2o.test; import java.io.Reader; import java.util.List; 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.m2o.entity.Card1; public class TestM2O { 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(); List<Card1> cards = sqlSession.selectList("com.chensan.m2o.entity.Card1Mapper.query", 3); System.out.println(cards); for(Card1 card : cards){ System.out.println( "[ " + "id = " + card.getId() + ", " + "cardNo = " + card.getCardNo() + ", " + "remark = " + card.getRemark() + ", " + "userId = " + card.getUser().getId() + ", " + "userName = " + card.getUser().getUserName() + ", " + "address = " + card.getUser().getAddress() + " ]" ); } sqlSession.close(); } public static void main(String[] args) { TestM2O testMyBatisManyToOne = new TestM2O(); testMyBatisManyToOne.getUserCard(); } }结果：

[[ id = 3, cardNo = 420003, remark = 工资卡], [ id = 4, cardNo = 420004, remark = 工资卡]] [ id = 3, cardNo = 420003, remark = 工资卡, userId = 3, userName = 张三, address = 深圳市龙华新区 ] [ id = 4, cardNo = 420004, remark = 工资卡, userId = 3, userName = 张三, address = 深圳市龙华新区 ]

如果有列同名，就会造成问题。如当user1和card1都有id列，未对user1的id列取别名user_id，userId列就会有问题（虽然card1有user_id列，但mapper文件 并未使用，所以可以给user1的id取别名user_id），结果如下：

[[ id = 3, cardNo = 420003, remark = 工资卡], [ id = 4, cardNo = 420004, remark = 工资卡]] [ id = 3, cardNo = 420003, remark = 工资卡, userId = 3, userName = 张三, address = 深圳市龙华新区 ] [ id = 4, cardNo = 420004, remark = 工资卡, userId = 4, userName = 张三, address = 深圳市龙华新区 ] 多对一关联应和一对一对着看看区别：在于select语句是否通过关联列来查询；一对一关联见： http://blog.csdn.net/qinshijangshan/article/details/56839950 

SysUser5 [id = 3, userName = 李三, birthday = Thu May 23 00:00:00 CDT 1991, salary = 123326.1, address = 深圳市福田区] SysDept2 [id = 1, deptName = 软件研发部, remark = 负责公司软件产品开发]参考自：http://blog.csdn.net/rain097790/article/details/13615291