Mybatis多对多操作-查询

目录结构

数据库

1、product_表

插入数据：

建表sql文：

use mybatistest1;

create table product_(
id int NOT NULL AUTO_INCREMENT,
name varchar(30)  DEFAULT NULL,
price float  DEFAULT 0,
cid int ,
PRIMARY KEY (id)
)AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

3、创建order_表


建表sql：

create table order_(
	id int(11) NOT NULL AUTO_INCREMENT,
	code VARCHAR(32) DEFAULT NULL,
	PRIMARY KEY(id)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 

4、order_item表


建表sql：

CREATE table order_item_(
	id int(11) not null auto_increment,
	oid int,
	pid int,
	number int,
	PRIMARY KEY(id)
)AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

代码

pojo实体类Order.java

package com.mybatis.pojo;

import java.util.List;
 
//系统订单由无数个，但是有些有没有关联到特定的产品
public class Order {
    private int id;
    private String code;
     
    List<OrderItem> orderItems;
     
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCode() {
        return code;
    }
    public void setCode(String code) {
        this.code = code;
    }
    public List<OrderItem> getOrderItems() {
        return orderItems;
    }
    public void setOrderItems(List<OrderItem> orderItems) {
        this.orderItems = orderItems;
    }
 
}

pojo实体类Product.java

package com.mybatis.pojo;

public class Product {
    private int id;
    private String name;
    private float price;
    private Category category;     //多个产品对应一个类别
    public Category getCategory() {
		return category;
	}
	public void setCategory(Category category) {
		this.category = category;
	}
	public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public float getPrice() {
        return price;
    }
    public void setPrice(float price) {
        this.price = price;
    }
    @Override
    public String toString() {
        return "Product [id=" + id + ", name=" + name + ", price=" + price + "]";
    }
 
}

Order_item_类

package com.mybatis.pojo;

//表示系统内其中一对关联，该关联表示多个订单关联到多个产品，需要查找的特定订单和产品
public class OrderItem {
    private int id;
    private int number;
    private Order order;
    private Product product;
    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 Order getOrder() {
        return order;
    }
    public void setOrder(Order order) {
        this.order = order;
    }
    public Product getProduct() {
        return product;
    }
    public void setProduct(Product product) {
        this.product = product;
    }
     
}

Order.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.mybatis.pojo">
    	<!-- 多个订单的其中一个订单（Order）关联到多个产品（Product）的对象  -->
    	<resultMap type="Order" id="orderBean">
    		<id column="oid" property="id"/>
    		<result column="ocode" property="code"/>
    		<collection property="orderItems" ofType="OrderItem">
    			<id column="oiid" property="id"/>
    			<result column="oinumber" property="number"/>
    			<association property="product" javaType="Product">
    				<id column="pid" property="id"/>
    				<result column="pname" property="name"/>
    				<result column="pprice" property="price"/>
    			</association>
    		</collection>
    	</resultMap>
    	
    	<!-- 返回值为数据库字段名通过orderBean解析到Order类属性里 -->
    	<select id="listOrder" resultMap="orderBean">  
    		select o.*,p.*,o.id 'oid',o.code 'ocode',p.id 'pid',p.name 'pname',p.price 'pprice',oi.id 'oiid',oi.oid 'oioid',oi.pid 'oipid',oi.number 'oinumber'
    		from order_ o
    		left join order_item_ oi
    		on o.id=oi.oid
    		left join product_ p
    		on oi.pid=p.id
    	</select>
    	
    	<!-- 多对多查询只需要传入一个参数就能查询到join的所有表，所以不需要parametertype -->
    	<select id="getOrder" resultMap="orderBean">
    		select o.*,p.*,o.id 'oid',o.code 'ocode',p.id 'pid',p.name 'pname',p.price 'pprice',oi.id 'oiid',oi.oid 'oioid',oi.pid 'oipid',oi.number 'oinumber'
    		from order_ o
    		left join order_item_ oi
    		on o.id=oi.oid
    		left join product_ p
    		on oi.pid=p.id
    		where o.id=#{id}
    	</select>
    </mapper>
    
    

testMybatis.java测试查询订单Order

package com.mybatis;

import java.io.IOException;
import java.io.InputStream;
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.mybatis.pojo.*;

public class testMybatis {
	public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml"; //获取全局配置信息
        InputStream inputStream = Resources.getResourceAsStream(resource); //全局配置信息装入输入流
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //建立sql会话工厂，将输入流装入
        SqlSession session=sqlSessionFactory.openSession(); //开启一个会话
        
        //Category c=new Category();
     
        listAll(session);  //打印出session的Category
        
        session.commit();  //更新到数据库
        session.close();
        
    
    }
	
	private static void listAll(SqlSession session) {
		List<Order> os=session.selectList("listOrder");
		for(Order o : os) {
			System.out.println(o.getCode());
			List<OrderItem> ois=o.getOrderItems();
			for(OrderItem oi:ois) {
				System.out.format("\t%s\t%f\t%d%n", oi.getProduct().getName(),oi.getProduct().getPrice(),oi.getNumber());
			}
		}
		
	}
}

结果

过程分析总结

一个订单有多个产品
所以建立一个OrderItem类，存放多对多关系
一个Order对象有多个OrderItem对象集合，表示Order对象可以有多个订单和产品的关系（一对多）
一个OrderItem对象有一个Product对象（多对一）
一对多对一 = 多对多