Mybatis多表查询

admin • 2024-01-11 20:45 • 5G

一、简介

MyBatis 是一款优秀的 ORM 框架，支持多表查询操作。在实际开发中，经常需要使用多表查询来获取业务数据。

表与表之间存在的三种关系：一对一，一对多，多对多

二、一对一

1.pojo

student（学生类）

import java.util.List;

public class Student {
    private int id;
    private String studentName;
    private String gender;
    private String address;
    private String email;
    private String remark;

    public Student() {
    }

    public Student(String studentName, String gender, String address,String remark) {
        this.studentName = studentName;
        this.gender = gender;
        this.address = address;
        this.remark = remark;
    }

    public int getId() {
        return id;
    }

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

    public String getStudentName() {
        return studentName;
    }

    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }
}

order（订单类）

import java.util.List;

public class Order {
    private int id;
    private String ordername;
    private int ordercount;
    private Student student;

    public int getId() {
        return id;
    }

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

    public String getOrdername() {
        return ordername;
    }

    public void setOrdername(String ordername) {
        this.ordername = ordername;
    }

    public int getOrdercount() {
        return ordercount;
    }

    public void setOrdercount(int ordercount) {
        this.ordercount = ordercount;
    }

    public Student getStudent() {
        return student;
    }

    public void setStudent(Student student) {
        this.student = student;
    }
}

2.queryOrder接口类

public interface QueryOrderMapper {
    public List<Order> findOrderStudentAll();
}

3.OrderMapper.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="com.batis.mapper.QueryOrderMapper">
    <select id="findOrderStudentAll" resultType="com.batis.domain.QueryOrderStudent" >
        SELECT o.ordername,o.ordercount,s.id,s.studentName,s.address FROM orders o,student s WHERE o.id=s.id;
    </select>
    <resultMap id="orderStudent" type="com.batis.domain.Order">
        <result column="ordername" property="ordername"></result>
        <result column="ordercount" property="ordercount"></result>
        <association property="student" javaType="com.batis.domain.Student">
            <id column="student_id" property="id"></id>
            <result column="studentName" property="studentName"></result>
            <result column="address" property="address"></result>
        </association>
    </resultMap>
    <select id="findOrderStudent" resultMap="orderStudent">
        SELECT o.ordername,o.ordercount,s.id,s.studentName,s.address FROM orders o,student s WHERE o.id=s.id;
    </select>
</mapper>

4.test测试

 @Test
    public void testMapper6(){
        SqlSession sqlSession = ssf.openSession();
        QueryOrderMapper mapper = sqlSession.getMapper(QueryOrderMapper.class);
        List<Order> orders = mapper.findOrderStudentAll();
        QueryOrderStudent student = new QueryOrderStudent();
        for (Order order : orders) {
            System.out.println(
                    order.getOrdername()+","+order.getOrdercount()+","+
                    student.getId()+","+student.getStudentName()
                    +","+student.getAddress()
            );
        }
    }

三、一对多

1.pojo

student.java

package com.batis.domain;

        import java.util.List;

public class Student {
    private int id;
    private String studentName;
    private String gender;
    private String address;
    private String email;
    private String remark;
    private List<Order> listOrder;

    public Student() {
    }

    public Student(String studentName, String gender, String address,String remark) {
        this.studentName = studentName;
        this.gender = gender;
        this.address = address;
        this.remark = remark;
    }

    public List<Order> getListOrder() {
        return listOrder;
    }

    public void setListOrder(List<Order> listOrder) {
        this.listOrder = listOrder;
    }

    public int getId() {
        return id;
    }

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

    public String getStudentName() {
        return studentName;
    }

    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }
}

order.java

import java.util.List;

public class Order {
    private int id;
    private String ordername;
    private int ordercount;
    private Student student;

    public int getId() {
        return id;
    }

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

    public String getOrdername() {
        return ordername;
    }

    public void setOrdername(String ordername) {
        this.ordername = ordername;
    }

    public int getOrdercount() {
        return ordercount;
    }

    public void setOrdercount(int ordercount) {
        this.ordercount = ordercount;
    }

    public Student getStudent() {
        return student;
    }

    public void setStudent(Student student) {
        this.student = student;
    }
}

2.StudentMapper接口类

public interface StudentMapper {
    public List<Student> findStudentOrderAll();
}

3.StudentMapper.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="com.batis.mapper.StudentMapper">
    <resultMap id="studentOrder" type="Student">
        <id column="id" property="id"></id>
        <result column="studentName" property="studentName"></result>
        <result column="address" property="address"></result>
        <collection property="listOrder" ofType="com.batis.domain.Order">
            <result column="ordername" property="ordername"></result>
            <result column="ordercount" property="ordercount"></result>
        </collection>
    </resultMap>

    <select id="findStudentOrderAll" resultMap="studentOrder">
        SELECT s.id,s.studentName,s.address,o.ordername,o.ordercount FROM student s LEFT JOIN orders o on s.id=o.id;
    </select>

</mapper>

4.test测试

@Test
    public void testMapper8(){
        SqlSession sqlSession = ssf.openSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = mapper.findStudentOrderAll();
        for (Student student : students) {
            System.out.println(student.getId()+","+student.getStudentName()+","+student.getAddress());
            List<Order> listOrder = student.getListOrder();
            for (Order order : listOrder) {
                System.out.println(order.getOrdername()+","+order.getOrdercount());
            }

        }
    }

四、多对多

1.pojo

items（物品类）

public class Items {
    private int id;
    private String items_name;
    private int items_price;

    public int getId() {
        return id;
    }

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

    public String getItems_name() {
        return items_name;
    }

    public void setItems_name(String items_name) {
        this.items_name = items_name;
    }

    public int getItems_price() {
        return items_price;
    }

    public void setItems_price(int items_price) {
        this.items_price = items_price;
    }
}

order

import java.util.List;

public class Order {
    private int id;
    private String ordername;
    private int ordercount;
    private Student student;
    private List<OrderDetail> orderDetailList;

    public List<OrderDetail> getOrderDetailList() {
        return orderDetailList;
    }

    public void setOrderDetailList(List<OrderDetail> orderDetailList) {
        this.orderDetailList = orderDetailList;
    }

    public int getId() {
        return id;
    }

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

    public String getOrdername() {
        return ordername;
    }

    public void setOrdername(String ordername) {
        this.ordername = ordername;
    }

    public int getOrdercount() {
        return ordercount;
    }

    public void setOrdercount(int ordercount) {
        this.ordercount = ordercount;
    }

    public Student getStudent() {
        return student;
    }

    public void setStudent(Student student) {
        this.student = student;
    }
}

orderdetail（订单详细）

import java.util.List;

public class OrderDetail {
    private int id;
    private int item_count;
    private int item_all_price;
    private Order orderL;
    private List<Items> itemsList;

    public Order getOrderL() {
        return orderL;
    }

    public void setOrderL(Order orderL) {
        this.orderL = orderL;
    }

    public List<Items> getItemsList() {
        return itemsList;
    }

    public void setItemsList(List<Items> itemsList) {
        this.itemsList = itemsList;
    }

    public int getId() {
        return id;
    }

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

    public int getItem_count() {
        return item_count;
    }

    public void setItem_count(int item_count) {
        this.item_count = item_count;
    }

    public int getItem_all_price() {
        return item_all_price;
    }

    public void setItem_all_price(int item_all_price) {
        this.item_all_price = item_all_price;
    }
}

2.queryOrder接口类

import com.batis.domain.Order;

import java.util.List;

public interface QueryOrderMapper {
    public List<Order> findOrderStudentItemsOrderDetail();
}

3.OrderMapper.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="com.batis.mapper.QueryOrderMapper">

    <resultMap id="OrderStudentItemsOrderDetail" type="com.batis.domain.Order">
        <id column="o_id" property="id"></id>
        <result column="ordername" property="ordername"></result>
        <association property="student" javaType="com.batis.domain.Student">
            <id column="student_id" property="id"></id>
            <result column="studentName" property="studentName"></result>
            <result column="gender" property="gender"></result>
        </association>
        <collection property="itemsList" ofType="com.batis.domain.OrderDetail">
            <id column="od_id" property="id"></id>
            <result column="item_count" property="item_count"></result>
            <result column="item_all_price" property="item_all_price"></result>
        </collection>
        <collection property="items" ofType="com.batis.domain.Items">
            <result column="items_name" property="items_name"></result>
            <result column="items_price" property="items_price"></result>
        </collection>
    </resultMap>
    <select id="findOrderStudentItemsOrderDetail" resultMap="OrderStudentItemsOrderDetail">
        SELECT
		  orders.id o_id,
		  orders.ordername,
		  student.studentName,
		  student.gender,
		  orderdetail.id od_id,
		  orderdetail.item_count,
		  orderdetail.item_all_price ,
		  items.items_name,
		  items.items_price
		FROM ORDERs,student,orderdetail,items


		WHERE orders.student_id = student.id
		  AND orders.id = orderdetail.orders_id
		  AND orderdetail.items_id = items.id


    </select>
</mapper>

4.test测试

@Test
    public void testMapper9(){
        SqlSession sqlSession = ssf.openSession();
        QueryOrderMapper mapper = sqlSession.getMapper(QueryOrderMapper.class);
        List<Order> orders = mapper.findOrderStudentItemsOrderDetail();
        for (Order order : orders) {
            System.out.println(order.getId()+","+order.getOrdername()+","+order.getStudent().getStudentName()+","+order.getStudent().getGender());
            List<OrderDetail> orderDetailList = order.getOrderDetailList();
            for (OrderDetail orderDetail : orderDetailList) {
                System.out.println(orderDetail.getId()+","+orderDetail.getItem_count()+","+orderDetail.getItem_all_price());
                List<Items> itemsList = orderDetail.getItemsList();
                for (Items items : itemsList) {
                    System.out.println(items.getItems_name()+","+items.getItems_price());

                }
            }

        }
    }

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。

THE END

idea java mybatis

二维码

mysql和Oracle 查询某个时间之内的数据

< <上一篇

字节跳动机器人研究团队：用大规模视频数据训练GR-1，机器人轻松应对复杂任务

下一篇>>

搜索内容

Mybatis多表查询

一、简介

二、一对一

1.pojo

2.queryOrder接口类

3.OrderMapper.xml

4.test测试

三、一对多

1.pojo

2.StudentMapper接口类

3.StudentMapper.xml

4.test测试

四、多对多

1.pojo

2.queryOrder接口类

3.OrderMapper.xml

4.test测试

最新文章

分类

标签云