MyBatis之多对一关联查询

最新推荐文章于 2023-05-10 21:58:05 发布

陈_三

最新推荐文章于 2023-05-10 21:58:05 发布

阅读量514

点赞数 1

分类专栏： MyBatis 文章标签： MyBatis多对一关联

本文链接：https://blog.csdn.net/qinshijangshan/article/details/60955104

版权

MyBatis 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

多对一关联

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

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

-- 用户表
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();
    }
}

结果：

如果有列同名，就会造成问题。如当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