Mybatis数据库多对多查询

1，进行多对多查询商品与订单之间的关联关系，首先准备两个mysql数据表

#创建goods表用来储存商品信息

CREATE TABLE `goods`(
`id` INT(10) PRIMARY KEY AUTO_INCREMENT,
`gsName` VARCHAR(100),
`gsPrice` DOUBLE(15,2),
`gsNum` INT(60),
`gsInformation` VARCHAR(255)
);
#插入数据
INSERT INTO goods VALUES(1,'Java程序设计',39.50,342,'一门计算机程序设计类语言');
INSERT INTO goods VALUES(2,'JavaEE企业级应用',59.80,645,'Java三大框架Mybatis，Spring，Spring MVC');
INSERT INTO goods VALUES(3,'笔记本电脑',4999.90,342,'办公室笔记本电脑，游戏本电脑');
INSERT INTO goods VALUES(4,'VIVO手机',2999.50,1120,'一款移动5G手机');
INSERT INTO goods VALUES(5,'牛肉干',15.9,637,'一种学生党热爱的小零食');
INSERT INTO goods VALUES(3,'七波辉皮鞋',69.80,2362,'一种耐磨的皮鞋');
INSERT INTO goods VALUES(6,'外套',129.45,453,'阿迪达斯品牌的衣服');

#创建tb_orders表用来储存订单信息

CREATE TABLE `tb_orders`(
`id` INT(32) PRIMARY KEY AUTO_INCREMENT,
`number` VARCHAR(32) NOT NULL,
`tb_user_id` INT(32) NOT NULL,
FOREIGN KEY (tb_user_id) REFERENCES `tb_user` (id)
);
#插入数据
INSERT INTO tb_orders VALUES(1,'1000011',1);
INSERT INTO tb_orders VALUES(2,'1000012',1);
INSERT INTO tb_orders VALUES(3,'1000013',2);
INSERT INTO tb_orders VALUES(4,'1000013',4);
INSERT INTO tb_orders VALUES(5,'1000014',5);
INSERT INTO tb_orders VALUES(6,'1000015',5);
INSERT INTO tb_orders VALUES(7,'1000016',6);
INSERT INTO tb_orders VALUES(8,'1000017',4);
INSERT INTO tb_orders VALUES(9,'1000018',1);
INSERT INTO tb_orders VALUES(10,'1000019',2);
INSERT INTO tb_orders VALUES(11,'1000020',3);

2，在IDEA创建maven项目并命名JavaEE

在项目src/main/java下创建com.three.pojo包

#在pojo包下创建Goods 类封装属性值及返回成员变量：

package com.three.pojo.orders;

import java.util.List;

public class Goods {
    private int id;
    private String gsName;
    private Double gsPrice;
    private int gsNum;
    private String gsInformation;
    private List<Orders> orders;

    public int getId() {
        return id;
    }

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

    public String getGsName() {
        return gsName;
    }

    public void setGsName(String gsName) {
        this.gsName = gsName;
    }

    public Double getGsPrice() {
        return gsPrice;
    }

    public void setGsPrice(Double gsPrice) {
        this.gsPrice = gsPrice;
    }

    public int getGsNum() {
        return gsNum;
    }

    public void setGsNum(int gsNum) {
        this.gsNum = gsNum;
    }

    public String getGsInformation() {
        return gsInformation;
    }

    public void setGsInformation(String gsInformation) {
        this.gsInformation = gsInformation;
    }

    public List<Orders> getOrders() {
        return orders;
    }

    public void setOrders(List<Orders> orders) {
        this.orders = orders;
    }

    @Override
    public String toString() {
        return "Goods{" +
                "id=" + id +
                ", gsName='" + gsName + '\'' +
                ", gsPrice=" + gsPrice +
                ", gsNum=" + gsNum +
                ", gsInformation='" + gsInformation + '\'' +
                ", orders=" + orders +
                '}';
    }
}

#在pojo包下创建Orders类封装属性值及返回成员变量：

package com.three.pojo.orders;

import java.util.List;

public class Orders {
    private int id;
    private int number;
    private List<Goods> goods;

    public int getId() {
        return id;
    }

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

    public int getNumber() {
        return number;
    }

    public void setNumber(int number) {
        this.number = number;
    }

    public List<Goods> getGoods() {
        return goods;
    }

    public void setGoods(List<Goods> goods) {
        this.goods = goods;
    }

    @Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", number=" + number +
                ", goods=" + goods +
                '}';
    }
}

在项目com.three下创建dao包并在当前位置创建接口类InformationMapper

package com.three.dao;

import com.three.pojo.person.PersonInformation;
import com.three.pojo.reader.Readers;

import java.util.List;

public interface InformationMapper {
    public List<Orders> selectMany_for_many(Integer id);
}

在com.three下创建包untils包存放工具类Mybatis

package com.three.utils;

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 java.io.IOException;
import java.io.Reader;

public class Mybatis {
    private static SqlSessionFactory sqlSessionFactory=null;
    static {
        try {
            Reader reader= Resources.getResourceAsReader("mybatis-config.xml");
            SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
            sqlSessionFactory=builder.build(reader);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession(true);
    }
}

#为了美观查询界面，为此准备了一个Swing窗口JFrame，JTable的TableMany_for_many

package com.three.utils;

import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.awt.*;

public class TableMany_for_many extends JFrame {
    private static JLabel jLabel1,jLabel2;
    private static DefaultTableModel dTableModel1,dTableModel2;
    private static JTable table1,table2;
    private static JScrollPane jScrollPane1,jScrollPane2;
    private static JPanel jPanelA,jPanelB,jPanel1,jPanel2;
    private static Object[] columns1 = {"订单ID","订单号"};
    private static Object[] columns2 = {"商品ID", "名称","商品价格", "商品数量", "商品信息"};

    private static Object[][] data = null;

    {
        jScrollPane1=new JScrollPane();
        jPanel1=new JPanel();
        jPanel1.setBackground(Color.magenta);
        dTableModel1=new DefaultTableModel(data,columns1);
        dTableModel1.setRowCount(0);
        table1=new JTable(dTableModel1);
        table1.setBackground(Color.CYAN);
        jScrollPane1.setViewportView(table1);

        jScrollPane2=new JScrollPane();
        jPanel2=new JPanel();
        jPanel2.setBackground(Color.blue);
        dTableModel2=new DefaultTableModel(data,columns2);
        dTableModel2.setRowCount(0);
        table2=new JTable(dTableModel2);
        table2.setBackground(Color.yellow);
        jScrollPane2.setViewportView(table2);

        jPanel1.add(jScrollPane1);;
        jPanel2.add(jScrollPane2);

        this.setTitle("订单与商品信息：(多对多查询)");
        this.setBounds(100,0,1000,500);
        this.setBackground(Color.PINK);
        this.setLayout(new FlowLayout(FlowLayout.CENTER));
        this.add(jPanel1);
        this.add(jPanel2);
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        this.setVisible(true);
    }
    public DefaultTableModel addTable1(){
        return dTableModel1;
    }
    public DefaultTableModel addTable2(){
        return dTableModel2;
    }
}

在当前dao包下创建impl包并在此包下实现类InformationMapperImpl

package com.three.dao.impl;

import com.three.dao.InformationMapper;
import com.three.pojo.person.PersonInformation;
import com.three.pojo.reader.Readers;
import com.two.utils.Mybatis;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class InformationMapperImpl implements InformationMapper {
    @Override
    public List<Orders> selectMany_for_many(Integer id) {
        SqlSession session=Mybatis.getSqlSession();
        return session.selectList("selectMany_for_many",id);
    }

}

在resource下创建文件夹mappers，并在文件夹下创建InformationMapper.xml编写SQL语句

<?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" >
<!--namespace的值通常为接口的全限定名-->
<mapper namespace="com.three.dao.InformationMapper">

<!--    多对多查询订单与商品信息。-->
    <select id="selectMany_for_many" parameterType="Integer" resultMap="GoodsInformation">
        SELECT o.*,g.id AS gsID,g.gsName,g.gsPrice,g.gsNum,g.gsInformation
        FROM tb_orders o,goods g,mid_table m
        WHERE m.oders_id=o.id
        AND m.goods_id=g.id
        AND o.id=#{id};
    </select>
    <resultMap id="GoodsInformation" type="Orders">
        <id property="id" column="id"/>
        <result property="number" column="number"/>
        <collection property="goods" ofType="Goods">
            <id property="id" column="gsID"/>
            <result property="gsName" column="gsName"/>
            <result property="gsPrice" column="gsPrice"/>
            <result property="gsNum" column="gsNum"/>
            <result property="gsInformation" column="gsInformation"/>
        </collection>
    </resultMap>
</mapper>

在测试包下编写测试类Three

import com.three.dao.InformationMapper;
import com.three.dao.impl.InformationMapperImpl;
import com.three.pojo.person.Person;
import com.three.pojo.person.PersonInformation;
import com.three.pojo.reader.Lend_Books;
import com.three.pojo.reader.Readers;
import com.three.utils.TableOne_for_many;
import com.three.utils.TableOne_for_one;

import java.util.List;

public class Three {
    public static void main(String[] args) {
        Three three=new Three();
        three.many_for_many();
    }
    public void many_for_many(){
        InformationMapper informationMapper=new InformationMapperImpl();
        List<Orders> informations=informationMapper.selectMany_for_many(1);
        TableMany_for_many table=new TableMany_for_many();
        for (Orders orders:informations){
            String[] arr=new String[2];
            arr[0]= String.valueOf(orders.getId());
            arr[1]= String.valueOf(orders.getNumber());;
            table.addTable1().addRow(arr);

            List<Goods> goodsList=orders.getGoods();
            for (Goods goods:goodsList) {
                String[] brr = new String[5];
                brr[0] = String.valueOf(goods.getId());
                brr[1] = goods.getGsName();
                brr[2] = String.valueOf(goods.getGsPrice());
                brr[3] = String.valueOf(goods.getGsNum());
                brr[4] = goods.getGsInformation();
                table.addTable2().addRow(brr);
            }
        }
        System.out.println(informations);
    }
   
}

测试结果如下