0051 MyBatis关联映射--多对多关系

用户与订单时一对多关系，再加上商品信息的话，订单与商品之间就是多对多关系了

DROP DATABASE IF EXISTS testdb;

USE testdb;

/*用户表，记录用户信息；用户与订单为一对多关系：一个用户可拥有多个订单*/

DROP TABLE IF EXISTS `tb_user`;

CREATE TABLE tb_user(

    id INT PRIMARY KEY AUTO_INCREMENT,

    username VARCHAR(18),

    loginname VARCHAR(18),

    PASSWORD VARCHAR(18),

    phone VARCHAR(18),

    address VARCHAR(18)

);

INSERT INTO tb_user(username,loginname,PASSWORD,phone,address) VALUES('杰克','jack','123456','13920001616','广州');

/*商品信息表；商品与订单为多对多关系：一个订单可有多个商品，一个商品也可出现在多个订单中*/

DROP TABLE IF EXISTS `tb_article`;

CREATE TABLE tb_article(

    id INT PRIMARY KEY AUTO_INCREMENT,

    NAME VARCHAR(32),

    price DOUBLE,

    remark VARCHAR(18)

);

INSERT INTO tb_article(NAME,price,remark) VALUES('疯狂Java讲义',108.9,'李刚老师经典着作');

INSERT INTO tb_article(NAME,price,remark) VALUES('疯狂Android讲义',99.9,'李刚老师经典着作');

INSERT INTO tb_article(NAME,price,remark) VALUES('疯狂iOS讲义',89.9,'李刚老师经典着作');

INSERT INTO tb_article(NAME,price,remark) VALUES('SpringMVC+MyBatis企业开发',69.9,'肖文吉老师经典着作');

/*订单表，记录订单编号，总金额，所属用户的id；订单与用户为多对一关系，与商品为多对多关系*/

DROP TABLE IF EXISTS `tb_order`;

CREATE TABLE tb_order(

    id INT PRIMARY KEY AUTO_INCREMENT,

    CODE VARCHAR(32),

    total DOUBLE,

    user_id INT,

    FOREIGN KEY (user_id) REFERENCES tb_user(id)

);

INSERT INTO tb_order(CODE,total,user_id) VALUES('6aa3fa359ff14619b77fab5990940a2d',388.6,1);

INSERT INTO tb_order(CODE,total,user_id) VALUES('6aa3fa359ff14619b77fab5990940b3c',217.8,1);

/*中间表，用于记录订单对应的商品id*/

DROP TABLE IF EXISTS `tb_item`;

CREATE TABLE tb_item(

    order_id INT,

    article_id INT,

    amount INT,

    PRIMARY KEY(order_id,article_id),

    FOREIGN KEY (order_id) REFERENCES tb_order(id),

    FOREIGN KEY (article_id) REFERENCES tb_article(id)

);

INSERT INTO tb_item(order_id,article_id,amount) VALUES(1,1,1);

INSERT INTO tb_item(order_id,article_id,amount) VALUES(1,2,1);

INSERT INTO tb_item(order_id,article_id,amount) VALUES(1,3,2);

INSERT INTO tb_item(order_id,article_id,amount) VALUES(2,4,2);

INSERT INTO tb_item(order_id,article_id,amount) VALUES(2,1,1);

实体类User

package net.sonng.manytomany;

import java.util.List;

public class User {

    private int id;

    private String username;

    private String loginname;

    private String password;

    private String phone;

    private String address;

    private List<Order> orders; //用户与订单：一对多关系，一个用户可有多个订单

    //......

}

实体类Order

package net.sonng.manytomany;

import java.util.List;

public class Order {

    private int id;

    private String code;

    private double total;

    private int user_id;

    private User user;              //一个订单只属于一个用户

    private List<Article> articles; //订单与商品：多对多关系，一个订单可包含多个商品，一个商品也可以出现在多个订单中

    //.....

}

实体类Article

package net.sonng.manytomany;

import java.util.List;

public class Article {

    private int id;

    private String name;

    private double price;

    private String remark;

    private List<Order> orders;  //商品与订单：多对多关系

    //.....

}

根据用户id查询用户信息，并将其所有订单信息一并查询出来

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTDMapper 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<mapper namespace="net.sonng.manytomany.UserDao">

    <select id="selectUserById" parameterType="int" resultMap="userMap">

        SELECT * FROM tb_user WHERE id=#{user_id}

    </select>

    <resultMap id="userMap" type="net.sonng.manytomany.User">

        <id property="id" column="id" />

        <result property="username" column="username" />

        <result property="loginname" column="loginname" />

        <result property="password" column="password" />

        <result property="phone" column="phone" />

        <result property="address" column="address" />

        <!-- 用户与订单一对多关系，用collection -->

        <collection property="orders" column="id" javaType="ArrayList" ofType="net.sonng.manytomany.Order" select="net.sonng.manytomany.OrderDao.selectOrderByUserId" >

            <id property="id" column="id" />

            <result property="code" column="code" />

            <result property="total" column="total" />

        </collection>

    </resultMap>

</mapper>

根据订单id查询订单信息，一并将其所属的用户信息和包含的商品信息查询出来

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTDMapper 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<mapper namespace="net.sonng.manytomany.OrderDao">

    <select id="selectOrderByUserId" parameterType="int" resultType="net.sonng.manytomany.Order" >

        SELECT * FROM tb_order WHERE user_id=#{user_id}

    </select>

    <select id="selectOrderById" parameterType="int" resultMap="orderMap">

        SELECT u.*,o.id AS oid,code,total,user_id FROM tb_order o,tb_user u WHERE o.user_id=u.id AND o.id=#{order_id}

    </select>

    <resultMap type="net.sonng.manytomany.Order" id="orderMap" >

        <id property="id" column="id" />

        <result property="code" column="code" />

        <result property="total" column="total" />

        <association property="user" javaType="net.sonng.manytomany.User">

            <id property="id" column="id" />

            <result property="username" column="username" />

            <result property="loginname" column="loginname" />

            <result property="password" column="password" />

            <result property="phone" column="phone" />

            <result property="address" column="address" />

        </association>

        <collection property="articles" column="id" javaType="ArrayList" ofType="net.sonng.manytomany.Article" select="net.sonng.manytomany.ArticleDao.selectArticleByOrderId" >

            <id property="id" column="id" />

            <result property="name" column="name" />

            <result property="price" column="price" />

            <result property="remark" column="remark" />

        </collection>

    </resultMap>

</mapper>

根据订单id，先从中间表item中查询其包含的商品id，再查询这些商品的信息

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTDMapper 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<mapper namespace="net.sonng.manytomany.ArticleDao">

    <select id="selectArticleByOrderId" parameterType="int" resultType="net.sonng.manytomany.Article">

        SELECT * FROM tb_article WHERE id IN (SELECT article_id FROM tb_item WHERE order_id=#{order_id})

    </select>

</mapper>

测试类

package net.sonng.test;

import java.util.List;

import net.sonng.manytomany.Article;

import net.sonng.manytomany.ArticleDao;

import net.sonng.manytomany.Order;

import net.sonng.manytomany.OrderDao;

import net.sonng.manytomany.User;

import net.sonng.manytomany.UserDao;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Test {

    public static void main(String[] args){

        ApplicationContext ac=new ClassPathXmlApplicationContext("ac.xml");

        System.out.println("测试用户查询：根据用户id查询用户信息及其订单信息");

        UserDao userDao=ac.getBean("userDao",UserDao.class);

        User user=userDao.selectUserById(1);

        System.out.println("------用户信息------");

        System.out.println(user);

        List<Order> orders=user.getOrders();

        System.out.println("------该用户下的订单信息------");

        for (Order order:orders){

            System.out.println(order);

        }

        System.out.println("测试订单查询：根据订单id查询订单信息，所属用户信息，订单包含的商品信息");

        OrderDao orderDao=ac.getBean("orderDao",OrderDao.class);

        Order order=orderDao.selectOrderById(2);

        System.out.println("------订单信息------");

        System.out.println(order);

        System.out.println("------订单所属用户信息------");

        System.out.println(order.getUser());

        List<Article> articles=order.getArticles();

        System.out.println("------订单包含的商品信息------");

        for (Article article:articles) {

            System.out.println(article);

        }

    }

}

输出：

测试用户查询：根据用户id查询用户信息及其订单信息

------用户信息------

User [id=1, username=杰克, loginname=jack, password=123456, phone=13920001616, address=广州]

------该用户下的订单信息------

Order [id=1, code=6aa3fa359ff14619b77fab5990940a2d, total=388.6, user_id=1]

Order [id=2, code=6aa3fa359ff14619b77fab5990940b3c, total=217.8, user_id=1]

测试订单查询：根据订单id查询订单信息，所属用户信息，订单包含的商品信息

------订单信息------

Order [id=1, code=6aa3fa359ff14619b77fab5990940b3c, total=217.8, user_id=0]

------订单所属用户信息------

User [id=1, username=杰克, loginname=jack, password=123456, phone=13920001616, address=广州]

------订单包含的商品信息------

Article [id=1, name=疯狂Java讲义, price=108.9, remark=李刚老师经典着作]

Article [id=2, name=疯狂Android讲义, price=99.9, remark=李刚老师经典着作]

Article [id=3, name=疯狂iOS讲义, price=89.9, remark=李刚老师经典着作]

巴特西

0051 MyBatis关联映射--多对多关系

最新文章

热门文章