MyBatis注解之多对多关联实战

小鱼儿 2021-07-24 11:46 366阅读 0赞

**一 领域模型**

1 User

package org.fkit.domain;
    
    import java.io.Serializable;
    import java.util.List;
    import java.util.UUID;
    
    /**
    *
    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);
    
    * */
    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.apache.ibatis.annotations.Select;
    import org.fkit.domain.User;
    
    public interface UserMapper {
    
        @Select("SELECT * FROM TB_USER WHERE ID = #{id} ")
        User selectById(Integer id);
        
    }

2 OrderMapper

package org.fkit.mapper;
    
    
    import org.apache.ibatis.annotations.Many;
    import org.apache.ibatis.annotations.One;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.Select;
    import org.apache.ibatis.mapping.FetchType;
    import org.fkit.domain.Order;
    
    public interface OrderMapper {
    
        @Select("SELECT * FROM TB_ORDER WHERE ID = #{id}")
        @Results({
            @Result(id=true,column="id",property="id"),
            @Result(column="code",property="code"),
            @Result(column="total",property="total"),
            @Result(column="user_id",property="user",
                one=@One(select="org.fkit.mapper.UserMapper.selectById",
            fetchType=FetchType.EAGER)),
            @Result(column="id",property="articles",
                many=@Many(select="org.fkit.mapper.ArticleMapper.selectByOrderId",
            fetchType=FetchType.LAZY))
        })
        Order selectById(Integer id);
        
    }

3 ArticleMapper

package org.fkit.mapper;
    
    import java.util.List;
    
    import org.apache.ibatis.annotations.Select;
    import org.fkit.domain.Article;
    
    public interface ArticleMapper {
    
        @Select("SELECT * FROM tb_article WHERE id IN "
        +"(SELECT article_id FROM tb_item WHERE order_id = #{id} ) ")
        List<Article> selectByOrderId(Integer order_id);
        
    }

**四 配置文件**

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 class="org.fkit.mapper.UserMapper"/>
          <mapper class="org.fkit.mapper.OrderMapper"/>
          <mapper class="org.fkit.mapper.ArticleMapper"/>
      </mappers>
    </configuration>

**五 测试**

package org.fkit.test;
    
    import org.apache.ibatis.session.SqlSession;
    import org.fkit.domain.Order;
    import org.fkit.factory.FKSqlSessionFactory;
    import org.fkit.mapper.OrderMapper;
    public class ManyToManyTest {
    
        public static void main(String[] args) {
            // 定义SqlSession变量
            SqlSession sqlSession = null;
            try {
                // 创建SqlSession实例
                sqlSession = FKSqlSessionFactory.getSqlSession();
                // 获取OrderMapper实例
                OrderMapper om = sqlSession.getMapper(OrderMapper.class);
                // 根据id查询Order对象
                Order order = om.selectById(1);
                // 查看查询到的Order对象
                System.out.println(order.getId() + " " + order.getCode() + " " + order.getTotal());
                // 查看Order关联的User对象
                System.out.println(order.getUser());
                // 查看关联的Article集合，因为配置使用的是LAZY懒加载，所以当使用时才执行SQL语句
                order.getArticles().forEach(article -> System.out.println(article));
                // 提交事务
                sqlSession.commit();
            } catch (Exception e) {
                // 回滚事务
                sqlSession.rollback();
                e.printStackTrace();
            }finally {
                // 关闭SqlSession
                if(sqlSession != null)
                    sqlSession.close();
            }
        }
    }

测试结果

DEBUG [main] ==>  Preparing: SELECT * FROM TB_ORDER WHERE ID = ?
    DEBUG [main] ==> Parameters: 1(Integer)
    DEBUG [main] ====>  Preparing: SELECT * FROM TB_USER WHERE ID =  ?
    DEBUG [main] ====> Parameters: 1(Integer)
    DEBUG [main] <====      Total: 1
    DEBUG [main] <==      Total: 1
    1 6aa3fa359ff14619b77fab5990940a2d 388.6
    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: 1(Integer)
    DEBUG [main] <==      Total: 3
    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=李刚老师经典著作]