JAVAWEB（三）mybatis实现多对一等关联查询

一.在数据库中建立相应的表

use mybatis ;
-- 删除表
drop table if exists  items ;
drop table if exists  orders ;
drop table if exists  users ;

-- 创建表
create table users(id int primary key auto_increment , name           
varchar(20) , age int);
create table orders(id int primary key auto_increment ,       
orderno varchar(20) , uid int);
create table items(id int primary key auto_increment ,     
itemname varchar(20) , oid int);

-- 插入用户
insert into users(name,age) values('tom',12);
insert into users(name,age) values('tomas',13);

-- 插入订单
insert into orders(orderno,uid) values('No001',1);
insert into orders(orderno,uid) values('No002',1);
insert into orders(orderno,uid) values('No003',2);
insert into orders(orderno,uid) values('No004',2);

-- 插入订单项
insert into items(itemname,oid) values('item001',1);
insert into items(itemname,oid) values('item002',1);
insert into items(itemname,oid) values('item003',2);
insert into items(itemname,oid) values('item004',2);
insert into items(itemname,oid) values('item005',3);
insert into items(itemname,oid) values('item006',3);
insert into items(itemname,oid) values('item007',4);
insert into items(itemname,oid) values('item008',5);


select * from users ;
select * from orders ;
select * from items ;

二.建立实体类，用于去映射数据库中的表

• 建立类

  [Order.java]
   public class Order {
       private Integer id ;
       private String orderNo ;
       //简历关联关系
       private User user ;
  
       //get/set
   }
  
   [Item.java]
   public class Item {
       private Integer id;
       private String itemName;
       //订单项和订单之间的关联关系
       private Order order;
       //get/set
   }
  

• 引入映射文件

  <mappers>
        <mapper resource="UserMapper.xml"/>
       <mapper resource="OrderMapper.xml"/>
  </mappers>
  

• 测试类

  @Test
        public void selectOne() throws Exception {
  String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession s = sf.openSession();
            Order order = s.selectOne("orders.selectOne",1);
            System.out.println(order.getOrderNo());
            s.commit();
            s.close();
        }
  
        @Test
        public void selectAll() throws Exception {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession s = sf.openSession();
            List<Order> list = s.selectList("orders.selectAll");
            for(Order o : list){
                System.out.println(o.getOrderNo() + " : " + o.getUser().getName());
            }
            s.commit();
            s.close();
        }
  

三.配置一对多的应用

• 1.在User中增加orders集合。

        public class User {
        ...
            private List<Order> orders ;
        //get/set
    }
  

  • 2.改造UserMapper.xml

<!-- selectOne -->
<select id="selectOne" parameterType="int" resultMap="RM_User">
  select
    u.id uid ,
    u.name uname ,
    u.age uage ,
    o.id oid ,
    o.orderno oorderno
  from users u
    left outer join orders o on u.id = o.uid
  where u.id = #{id}
</select>
<resultMap id="RM_User" type="_User">
    <id property="id" column="uid" />
    <result property="name" column="uname" />
    <result property="age" column="uage" />
    <!-- 映射一对多关联关系 -->
    <collection property="orders" ofType="_Order">
        <id property="id" column="oid" />
        <result property="orderNo" column="oorderno" />
    </collection>
</resultMap>

<!-- selectAll -->
<select id="selectAll" resultMap="RM_User">
     select
    u.id uid ,
    u.name uname ,
    u.age uage ,
    o.id oid ,
    o.orderno oorderno
  from users u
    left outer join orders o on u.id = o.uid
</select>

四.组合多对一和一对多关联关系到一个实体(Order)中

• 1.关系

  Order() -> (1)User
  Order(1) -> ()Item

• 2.Order.java

 class Order{
        ...
        List<Item> items ;
        //get/set   
    }
2'.修改配置文件增加别名
    [resources/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>
        <typeAliases>
            <typeAlias type="com.it18zhang.mybatisdemo.domain.User" alias="_User"/>
            <typeAlias type="com.it18zhang.mybatisdemo.domain.Order" alias="_Order"/>
            <typeAlias type="com.it18zhang.mybatisdemo.domain.Item" alias="_Item"/>
        </typeAliases>
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                    <property name="username" value="root"/>
                    <property name="password" value="root"/>
                </dataSource>
            </environment>
        </environments>
        <!-- 引入映射文件 -->
        <mappers>
            <mapper resource="UserMapper.xml"/>
            <mapper resource="OrderMapper.xml"/>
        </mappers>
    </configuration>

• 3.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="orders">
       <insert id="insert">
         insert into orders(orderno,uid) values(#{orderNo},#{user.id})
       </insert>
  
       <!-- findById -->
       <select id="selectOne" parameterType="int" resultMap="RM_Order">
         select
           o.id      oid ,
           o.orderno oorderno ,
           o.uid     uid ,
           u.name    uname ,
           u.age     uage ,
           i.id      iid,
           i.itemname iitemname
         from orders o
           left outer join users u on o.uid = u.id
           left outer join items i on o.id = i.oid
         where o.id = #{id}
       </select>
       <!-- findAll -->
       <select id="selectAll" resultMap="RM_Order">
         select
           o.id      oid ,
           o.orderno oorderno ,
           o.uid     uid ,
           u.name    uname ,
           u.age     uage ,
           i.id      iid,
           i.itemname iitemname
         from orders o
           left outer join users u on o.uid = u.id
           left outer join items i on o.id = i.oid
       </select>
       <!-- 自定义结果映射 -->
       <resultMap id="RM_Order" type="com.it18zhang.mybatisdemo.domain.Order">
           <id property="id" column="oid"/>
           <result property="orderNo" column="oorderno"/>
           <association property="user" javaType="com.it18zhang.mybatisdemo.domain.User">
               <id property="id" column="uid" />
               <result property="name" column="uname" />
               <result property="age" column="uage" />
           </association>
           <collection property="items" ofType="_Item">
               <id property="id" column="iid" />
               <result property="itemName" column="iitemname" />
           </collection>
       </resultMap>
   </mapper>
  

• 4.测试

    @Test
    public void selectOne() throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession s = sf.openSession();
        Order order = s.selectOne("orders.selectOne",1);
        System.out.println(order.getOrderNo() + order.getUser().getName());
        for(Item i : order.getItems()){
            System.out.println(i.getId() + ":" + i.getItemName());
        }
        s.commit();
        s.close();
    }