Mybatis之一对多和多对一查询

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

public class Card1 {  
    private int id;  
    private String cardNo;  
    private String remark;  

    //...省略setter、getter方法  
}  

实体类：User1

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方法  
} 

实体类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.o2m.entity.User1Mapper">  
  <resultMap id="user1" type="com.chensan.o2m.entity.User1">  
    <id property="id" column="user_id"/>  
    <result property="userName" column="user_name"/>  
    <result property="address" column="address"/>  

    <collection property="cards" column="user_id" ofType="com.chensan.o2m.entity.Card1">  
        <id property="id" column="id"/>  
        <result property="cardNo" column="card_no"/>  
        <result property="remark" column="remark"/>  
    </collection>  
  </resultMap>  

  <select id="query" parameterType="int" resultMap="user1">  
    SELECT t1.`id` `user_id`,t1.`user_name`,t1.`address`,t2.`id`,t2.`card_no`,t2.`remark`   
    FROM `user1` t1,`card1` t2   
    WHERE t1.`id`=t2.`user_id` AND t1.`id`=#{id}  
  </select>  
</mapper>

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/o2m/mapper/User1Mapper.xml"/>  
  </mappers>  
</configuration>  

测试类

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.o2m.entity.Card1;  
import com.chensan.o2m.entity.User1;  

public class TestO2M {  
    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.o2m.entity.User1Mapper.query", 3);  
        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 static void main(String[] args) {  
        TestO2M testMyBatisOneToMany = new TestO2M();  
        testMyBatisOneToMany.getUserCard();  
    }  
}  

结果

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

user1和card1的id列同名，如果不对其中一列取别名，则user只关联一个card对象，只有一条记录：

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