MyBatis多对多关系实战

青旅半醒 2021-07-24 17:23 387阅读 0赞

**一 数据表设计**

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','广州');
    CREATE TABLE tb_article(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(18),
    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,'肖文吉老师经典著作');
    
    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);
    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);

**二 领域类**

1 User

package org.fkit.domain;
    
    import java.io.Serializable;
    import java.util.List;
    
    
    public class User implements Serializable{
        
        private static final long serialVersionUID = 1L;
        
        private Integer id;  // 用户id，主键
        private String username;  // 用户名
        private String loginname; // 登录名
        private String password;  // 密码
        private String phone;    // 联系电话
        private String address;  // 收货地址
        
        // 用户和订单是一对多的关系，即一个用户可以有多个订单
        private List<Order> orders;
    
        public User() {
            super();
            // TODO Auto-generated constructor stub
        }
    
        public User(String username, String loginname, String password,
                String phone, String address) {
            super();
            this.username = username;
            this.loginname = loginname;
            this.password = password;
            this.phone = phone;
            this.address = address;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
        public String getLoginname() {
            return loginname;
        }
    
        public void setLoginname(String loginname) {
            this.loginname = loginname;
        }
    
        public String getPassword() {
            return password;
        }
    
        public void setPassword(String password) {
            this.password = password;
        }
    
        public String getPhone() {
            return phone;
        }
    
        public void setPhone(String phone) {
            this.phone = phone;
        }
    
        public String getAddress() {
            return address;
        }
    
        public void setAddress(String address) {
            this.address = address;
        }
    
        public List<Order> getOrders() {
            return orders;
        }
    
        public void setOrders(List<Order> orders) {
            this.orders = orders;
        }
    
        @Override
        public String toString() {
            return "User [id=" + id + ", username=" + username + ", loginname="
                    + loginname + ", password=" + password + ", phone=" + phone
                    + ", address=" + address + "]";
        }
    }

2 Order

package org.fkit.domain;
    
    import java.io.Serializable;
    import java.util.List;
    
    public class Order implements Serializable {
    
        private static final long serialVersionUID = 1L;
        
        private Integer id;  // 订单id，主键
        private String code;  // 订单编号
        private Double total; // 订单总金额
        
        // 订单和用户是多对一的关系，即一个订单只属于一个用户
        private User user;
        
        // 订单和商品是多对多的关系，即一个订单可以包含多种商品
        private List<Article> articles;
    
        public Order() {
            super();
            // TODO Auto-generated constructor stub
        }
    
        public Order(String code, Double total) {
            super();
            this.code = code;
            this.total = total;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getCode() {
            return code;
        }
    
        public void setCode(String code) {
            this.code = code;
        }
    
        public Double getTotal() {
            return total;
        }
    
        public void setTotal(Double total) {
            this.total = total;
        }
    
        public User getUser() {
            return user;
        }
    
        public void setUser(User user) {
            this.user = user;
        }
    
        public List<Article> getArticles() {
            return articles;
        }
    
        public void setArticles(List<Article> articles) {
            this.articles = articles;
        }
    
        @Override
        public String toString() {
            return "Order [id=" + id + ", code=" + code + ", total=" + total + "]";
        }
        
        
    }

3 Article

package org.fkit.domain;
    
    import java.io.Serializable;
    import java.util.List;
    
    public class Article implements Serializable {
        
        private static final long serialVersionUID = 1L;
        
        private Integer id;        // 商品id，主键
        private String name;    // 商品名称
        private Double price;    // 商品价格
        private String remark;    // 商品描述
        
        // 商品和订单是多对多的关系，即一种商品可以包含在多个订单中
        private List<Order> orders;
    
        public Article() {
            super();
            // TODO Auto-generated constructor stub
        }
    
        public Article(String name, Double price, String remark) {
            super();
            this.name = name;
            this.price = price;
            this.remark = remark;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public Double getPrice() {
            return price;
        }
    
        public void setPrice(Double price) {
            this.price = price;
        }
    
        public String getRemark() {
            return remark;
        }
    
        public void setRemark(String remark) {
            this.remark = remark;
        }
    
        public List<Order> getOrders() {
            return orders;
        }
    
        public void setOrders(List<Order> orders) {
            this.orders = orders;
        }
    
        @Override
        public String toString() {
            return "Article [id=" + id + ", name=" + name + ", price=" + price
                    + ", remark=" + remark + "]";
        }
    
    }

**三 会话工厂**

package org.fkit.factory;
    
    import java.io.InputStream;
    
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    
    public class FKSqlSessionFactory {
        
        private static SqlSessionFactory sqlSessionFactory = null;
        
        // 初始化创建SqlSessionFactory对象
        static{
            try (// 读取mybatis-config.xml文件
                    InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
                ){
                sqlSessionFactory = new SqlSessionFactoryBuilder()
                        .build(is);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        // 获取SqlSession对象的静态方法
        public static SqlSession getSqlSession(){
            return sqlSessionFactory.openSession();
        }
    
        // 获取SqlSessionFactory的静态方法
        public static SqlSessionFactory getSqlSessionFactory() {
            return sqlSessionFactory;
        }
    
    }

**四 Mapper**

1 UserMapper

package org.fkit.mapper;
    import org.fkit.domain.User;
    public interface UserMapper {
         
         User selectUserById(int id);
    }

2 selectOrderById

package org.fkit.mapper;
    import org.fkit.domain.Order;
    public interface OrderMapper {
         Order selectOrderById(int id);
         
    }

3 ArticleMapper.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="org.fkit.mapper.ArticleMapper">
      
      <select id="selectArticleByOrderId" parameterType="int"
      resultType="org.fkit.domain.Article">
          SELECT * FROM tb_article WHERE id IN (
               SELECT article_id FROM tb_item WHERE order_id = #{id}
         )
      </select>
      
    </mapper>

4 UserMapper.xml

5 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="org.fkit.mapper.OrderMapper">
         <resultMap type="org.fkit.domain.Order"  id="orderResultMap">
               <id property="id" column="oid"/>
              <result property="code" column="code"/>
              <result property="total" column="total"/>
               
               <association property="user"  javaType="org.fkit.domain.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" javaType="ArrayList"
           column="oid" ofType="org.fkit.domain.Article"
            select="org.fkit.mapper.ArticleMapper.selectArticleByOrderId"
           fetchType="lazy">
              <id property="id" column="id"/>
              <result property="name" column="name"/>
              <result property="price" column="price"/>
              <result property="remark" column="remark"/>
           </collection>
         </resultMap>
         
         
      <select id="selectOrderById" parameterType="int"  resultMap="orderResultMap">
          SELECT u.*,o.id AS oid,CODE,total,user_id
           FROM tb_user u,tb_order o
          WHERE u.id = o.user_id
           AND o.id = #{id}
      </select>
      
      
      <select id="selectOrderByUserId" parameterType="int"  resultType="org.fkit.domain.Order">
          SELECT * FROM tb_order WHERE user_id = #{id}
      </select>
      
    </mapper>

**五 配置文件**

1 db.properties

driver=com.mysql.jdbc.Driver
    url=jdbc:mysql://127.0.0.1:3306/mybatis
    username=root
    password=123456

2 log4j.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">  
    <log4j:configuration  xmlns:log4j="http://jakarta.apache.org/log4j/">  
        <appender name="STDOUT"  class="org.apache.log4j.ConsoleAppender">  
            <layout class="org.apache.log4j.PatternLayout">  
                <param name="ConversionPattern" value="%5p [%t] %m%n"  />  
            </layout>  
        </appender>   
         <logger name="org.fkit.mapper">  
            <level value="DEBUG" />  
        </logger>
        <root>  
            <level value="ERROR" />  
            <appender-ref ref="STDOUT" />  
        </root>  
    </log4j:configuration>

3 mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration
      PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
      "http://mybatis.org/dtd/mybatis-3-config.dtd">
      
    <configuration>
         <properties resource="db.properties"/>
         
         <settings>
               <setting name="logImpl" value="LOG4J"/>
               
               <setting name="lazyLoadingEnabled" value="true"/>
               <setting name="aggressiveLazyLoading" value="false"/>
         </settings>
         <environments default="mysql">
         
        <environment id="mysql">
        
          <transactionManager type="JDBC"/>
          
           <dataSource type="POOLED">
            <property name="driver" value="${driver}"/>
            <property name="url" value="${url}"/>
            <property name="username" value="${username}"/>
            <property name="password" value="${password}"/>
          </dataSource>
        </environment>
      </environments>
      
      <mappers>
          <mapper resource="org/fkit/mapper/UserMapper.xml"/>
          <mapper resource="org/fkit/mapper/OrderMapper.xml"/>
          <mapper resource="org/fkit/mapper/ArticleMapper.xml"/>
      </mappers>
    </configuration>

**六 测试**

package org.fkit.test;
    
    import java.util.List;
    import org.apache.ibatis.session.SqlSession;
    import org.fkit.domain.Article;
    import org.fkit.domain.Order;
    import org.fkit.domain.User;
    import org.fkit.factory.FKSqlSessionFactory;
    import org.fkit.mapper.OrderMapper;
    import org.fkit.mapper.UserMapper;
    
    
    public class ManyToManyTest {
    
        public static void main(String[] args)  {
            // 定义SqlSession变量
            SqlSession sqlSession = null;
            try {
                // 创建SqlSession实例
                sqlSession = FKSqlSessionFactory.getSqlSession();
                ManyToManyTest t = new ManyToManyTest();
                // 根据用户id查询用户，测试一对多
                t.testSelectUserById(sqlSession);
                // 根据订单id查询订单，测试多对多
    //            t.testSelectOrderById(sqlSession);
                // 提交事务
                sqlSession.commit();
            } catch (Exception e) {
                // 回滚事务
                sqlSession.rollback();
                e.printStackTrace();
            }finally {
                // 关闭SqlSession
                if(sqlSession != null)
                    sqlSession.close();
            }
        }
        
        // 测试一对多，查询User（一）的时候级联查询订单Order（多）  
        public void testSelectUserById(SqlSession sqlSession){
            // 获得UserMapper接口的代理对象
            UserMapper um = sqlSession.getMapper(UserMapper.class);
            // 调用selectUserById方法
            User user = um.selectUserById(1);
            // 查看查询到的user对象信息
            System.out.println(user.getId() + " " + user.getUsername());
            // 查看user对象关联的订单信息
            List<Order> orders = user.getOrders();
            orders.forEach(order -> System.out.println(order));
        }
        
        // 测试多对多，查询订单Order（多）的时候级联查询订单的商品Article（多）  
        public void testSelectOrderById(SqlSession sqlSession){
            // 获得OrderMapper接口的代理对象
            OrderMapper om = sqlSession.getMapper(OrderMapper.class);
            // 调用selectOrderById方法
            Order order = om.selectOrderById(2);
            // 查看查询到的order对象信息
            System.out.println(order.getId() + " " + order.getCode() + " " + order.getTotal());
            // 查看order对象关联的用户信息
            User user = order.getUser();
            System.out.println(user);
            // 查看order对象关联的商品信息
            List<Article> articles = order.getArticles();
            articles.forEach(article -> System.out.println(article));
        }
    
    }

1 测试一对多

DEBUG [main] ==>  Preparing: SELECT * FROM tb_user WHERE id = ?
    DEBUG [main] ==> Parameters: 1(Integer)
    DEBUG [main] <==      Total: 1
    1 杰克
    DEBUG [main] ==>  Preparing: SELECT * FROM tb_order WHERE  user_id = ?
    DEBUG [main] ==> Parameters: 1(Integer)
    DEBUG [main] <==      Total: 2
    Order [id=1, code=6aa3fa359ff14619b77fab5990940a2d, total=388.6]
    Order [id=2, code=6aa3fa359ff14619b77fab5990940b3c, total=217.8]

2 测试多对一

DEBUG [main] ==>  Preparing: SELECT u.*,o.id AS  oid,CODE,total,user_id FROM tb_user u,tb_order o WHERE u.id =  o.user_id AND o.id = ?
    DEBUG [main] ==> Parameters: 2(Integer)
    DEBUG [main] <==      Total: 1
    2 6aa3fa359ff14619b77fab5990940b3c 217.8
    User [id=1, username=杰克, loginname=jack, password=123456,  phone=13920001616, address=广州]
    DEBUG [main] ==>  Preparing: SELECT * FROM tb_article WHERE id  IN ( SELECT article_id FROM tb_item WHERE order_id = ? )
    DEBUG [main] ==> Parameters: 2(Integer)
    DEBUG [main] <==      Total: 2
    Article [id=1, name=疯狂Java讲义, price=108.9, remark=李刚老师经典著作]
    Article [id=4, name=MyBatis企业开发, price=69.9, remark=肖文吉老师经典著作]