MyBatis一对多关联查询（级联查询）

程序员文章站 2024-03-23 08:57:52

...

一对多级联查询

创建表及准备数据：

CREATE TABLE `user` (
  `uid` tinyint(2) NOT NULL,
  `uname` varchar(20) DEFAULT NULL,
  `usex` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `orders` (
		`id` tinyint(2) PRIMARY KEY AUTO_INCREMENT,
		`ordersn` varchar(10) DEFAULT NULL,
		`user_id` tinyint(2) DEFAULT NULL,
		CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES user(uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MyBatis一对多关联查询（级联查询）

创建工程并导入相关JAR包

创建持久化类MyUser、Orders：

package pers.zhang.po;

import java.util.List;

/**
* 数据库中user表的持久类
*/
public class MyUser {
    private Integer uid; // 主键
    private String uname;
    private String usex;

    private List<Orders> ordersList; //一对多关系表达
    
    public Integer getUid() {
		return uid;
	}


	public void setUid(Integer uid) {
		this.uid = uid;
	}


	public String getUname() {
		return uname;
	}


	public void setUname(String uname) {
		this.uname = uname;
	}


	public String getUsex() {
		return usex;
	}


	public void setUsex(String usex) {
		this.usex = usex;
	}

	

	public List<Orders> getOrdersList() {
		return ordersList;
	}


	public void setOrdersList(List<Orders> ordersList) {
		this.ordersList = ordersList;
	}


	@Override
	public String toString() {
		return "MyUser [uid=" + uid + ", uname=" + uname + ", usex=" + usex + ", ordersList=" + ordersList + "]";
	}
}

package pers.zhang.po;

public class Orders {
    private Integer id;
    private String ordersn;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getOrdersn() {
        return ordersn;
    }

    public void setOrdersn(String ordersn) {
        this.ordersn = ordersn;
    }

    @Override
    public String toString() {
        return "Orders[id=" + id + ",ordersn=" + ordersn + "]";
    }
}

创建映射文件：
UserMapper.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="pers.zhang.mapper.UserMapper">
    <!-- 一对多 根据uid查询用户及其关联的订单信息：级联查询的第一种方法（嵌套查询） -->
    <resultMap type="pers.zhang.po.MyUser" id="userAndOrders1">
        <id property="uid" column="uid" />
        <result property="uname" column="uname" />
        <result property="usex" column="usex" />
        <!-- 一对多级联查询，ofType表示集合中的元素类型，将uid传递给selectOrdersByld -->
        <collection property="ordersList" ofType="pers.zhang.po.Orders"
            column="uid" select="pers.zhang.mapper.OrdersMapper.selectOrdersById" />
    </resultMap>
    <select id="selectUserOrdersById1" parameterType="Integer"
        resultMap="userAndOrders1">
        select * from user where uid = #{id}
    </select>
    
    
    <!--对多根据uid查询用户及其关联的订单信息：级联查询的第二种方法（嵌套结果） -->
    <resultMap type="pers.zhang.po.MyUser" id="userAndOrders2">
        <id property="uid" column="uid" />
        <result property="uname" column="uname" />
        <result property="usex" column="usex" />
        <!-- 对多级联查询，ofType表示集合中的元素类型 -->
        <collection property="ordersList" ofType="pers.zhang.po.Orders">
            <id property="id" column="id" />
            <result property="ordersn" column="ordersn" />
        </collection>
    </resultMap>
    <select id="selectUserOrdersById2" parameterType="Integer"
        resultMap="userAndOrders2">
        select u.*,o.id, o.ordersn from user u, orders o where u.uid
        = o.user_id and
        u.uid=#{id}
    </select>
    
    
    <!-- 一对多 根据uid查询用户及其关联的订单信息：连接查询（使用POJO存储结果） -->
    <select id="selectUserOrdersById3" parameterType="Integer"
        resultType="pers.zhang.po.SelectUserOrdersById">
        select u.*, o.id, o.ordersn from user u, orders o where
        u.uid = o.user_id
        and u.uid=#{id}
    </select>
</mapper>

OrdersMapper.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="pers.zhang.mapper.OrdersMapper">
    <!-- 根据用户uid查询订单信息 -->
    <select id="selectOrdersById" resultType="pers.zhang.po.Orders"
        parameterType="Integer">
        select * from orders where user_id=#{id}
    </select>
</mapper>

创建 POJO 类:

package pers.zhang.po;

public class SelectUserOrdersById {
    private Integer uid;
    private String uname;
    private String usex;
    private Integer id;
    private String ordersn;
    
    public Integer getUid() {
		return uid;
	}

	public void setUid(Integer uid) {
		this.uid = uid;
	}

	public String getUname() {
		return uname;
	}
	
	public void setUname(String uname) {
		this.uname = uname;
	}

	public String getUsex() {
		return usex;
	}

	public void setUsex(String usex) {
		this.usex = usex;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getOrdersn() {
		return ordersn;
	}

	public void setOrdersn(String ordersn) {
		this.ordersn = ordersn;
	}

	@Override
    public String toString() { // 为了方便查看结果，重写了toString方法
        return "User[uid=" + uid + ",uname=" + uname + ",usex=" + usex
                + ",oid=" + id + ",ordersn=" + ordersn + "]";
    }
}

创建Mapper接口：

package pers.zhang.mapper;

import java.util.List;

import pers.zhang.po.MyUser;
import pers.zhang.po.SelectUserOrdersById;

public interface UserMapper {
	
	public MyUser selectUserOrdersById1(Integer uid);

    public MyUser selectUserOrdersById2(Integer uid);

    public List<SelectUserOrdersById> selectUserOrdersById3(Integer uid);
}

package pers.zhang.mapper;

import java.util.List;

import pers.zhang.po.Orders;

public interface OrdersMapper {

	public List<Orders> selectOrdersById(Integer uid);
}

测试

一对多第一种方式：嵌套查询

@Test
public void test1() throws Exception{
	//加载核心配置文件
	String resource = "sqlMapConfig.xml";
	InputStream in = Resources.getResourceAsStream(resource);
	//创建SqlSessionFactory
	SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
	//获得sqlSession
	SqlSession sqlSession = sqlSessionFactory.openSession();
	//获得Mapper
	UserMapper mapper = sqlSession.getMapper(UserMapper.class);
	Integer id = 3;
	MyUser user = mapper.selectUserOrdersById1(id);
	System.out.println(user);
}

控制台打印输出：

DEBUG [main] - ==>  Preparing: select * from user where uid = ? 
DEBUG [main] - ==> Parameters: 3(Integer)
DEBUG [main] - <==      Total: 1
DEBUG [main] - ==>  Preparing: select * from orders where user_id=? 
DEBUG [main] - ==> Parameters: 3(Integer)
DEBUG [main] - <==      Total: 1
MyUser [uid=3, uname=Lily, usex=女, ordersList=[Orders[id=6,ordersn=3331]]]

一对多第二种方式：连接查询，映射到持久化类中

@Test
public void test2() throws Exception{
	//加载核心配置文件
	String resource = "sqlMapConfig.xml";
	InputStream in = Resources.getResourceAsStream(resource);
	//创建SqlSessionFactory
	SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
	//获得sqlSession
	SqlSession sqlSession = sqlSessionFactory.openSession();
	//获得Mapper
	UserMapper mapper = sqlSession.getMapper(UserMapper.class);
	Integer id = 2;
	MyUser user = mapper.selectUserOrdersById2(id);
	System.out.println(user);
}

控制台打印：

DEBUG [main] - ==>  Preparing: select u.*,o.id, o.ordersn from user u, orders o where u.uid = o.user_id and u.uid=? 
DEBUG [main] - ==> Parameters: 2(Integer)
DEBUG [main] - <==      Total: 2
MyUser [uid=2, uname=Jerry, usex=男, ordersList=[Orders[id=4,ordersn=2221], Orders[id=5,ordersn=2222]]]

一对多第三种方式：连接查询，映射到POJO中

@Test
	public void test3() throws Exception{
		//加载核心配置文件
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		//创建SqlSessionFactory
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		//获得sqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		//获得Mapper
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		Integer id = 2;
		List<SelectUserOrdersById> list = mapper.selectUserOrdersById3(id);
		System.out.println(list);
	}

控制台打印：

DEBUG [main] - ==>  Preparing: select u.*, o.id, o.ordersn from user u, orders o where u.uid = o.user_id and u.uid=? 
DEBUG [main] - ==> Parameters: 2(Integer)
DEBUG [main] - <==      Total: 2
[User[uid=2,uname=Jerry,usex=男,oid=4,ordersn=2221], User[uid=2,uname=Jerry,usex=男,oid=5,ordersn=2222]]

上一篇： swift NSLayoutConstraint 简单使用

MyBatis一对多关联查询（级联查询）

一对多级联查询

测试

MyBatis一对多关联查询（级联查询）

tp5 一对多关联查询

Mybatis 一对多查询返回结果只有一条：解决思路

解决mybatis一对多查询问题时的只显示一条数据的问题（查询部门的同时把所属部门的员工信息查出来）关联查询

Mybatis06----查询多对一，一对多的处理

SpringBoot +Mybatis 一对多查询

Mybatis一对一、一对多、多对多查询。+MYSQL

MyBatis中多对多关系的映射和查询

mybatis一对一查询功能

mybatis一对多查询功能