欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MyBatis 的关联映射

程序员文章站 2022-04-03 08:53:04
...

一、一对一

示例以个人和身份证之间的一对一关联关系。

1.创建数据表:

create table tb_idcard( id int  primary key auto_increment, code varchar(18)); 

create table tb_person( id int  primary key auto_increment, name varchar(32),age int,sex varchar(8),card_id int unique,foreign key(card_id) references tb_idcard(id)); 

insert into tb_idcard(code) values("110238733008844444");     
insert into tb_idcard(code) values("110238733008855555");    

insert into tb_person(name,age,sex,card_id) values("kangxg",29,"男",1);
insert into tb_person(name,age,sex,card_id) values("kangxy",27,"女",2);


2.在com.kangxg.po包下创建持久化类 IdCard 和 Person

package com.kangxg.po;

public class IdCard {
  private Integer id;
  private String code;
  
  public Integer getId()
  {
      return this.id;
  }
  
  public void setId(Integer id)
  {
      this.id = id;
  }
  
  public String getCode()
  {
      return this.code;
  }
  
  public void setCode(String code)
  {
      this.code = code;
  }
  
  @Override
  public String toString()
  {
      return "Customer [id =" + id +"," +"code =" +code  +"]";
  }
}

package com.kangxg.po;

public class Person {
      private Integer id;
      private String  name;
      private Integer age;
      private String  sex;
      
      private IdCard card;
      
      public Integer getId()
      {
          return this.id;
      }
      
      public void setId(Integer id)
      {
          this.id = id;
      }
      
      public Integer getAge()
      {
          return this.age;
      }
      
      public void setAge(Integer age)
      {
          this.age = age;
      }
      
      
      public String getName()
      {
          return this.name;
      }
      
      public void setName(String name)
      {
          this.name = name;
      }
      
      public String getSex()
      {
          return this.sex;
      }
      
      public void setSex(String sex)
      {
          this.sex = sex;
      }
      
      public IdCard getCard()
      {
          return this.card;
      }
      
      public void setCard(IdCard card)
      {
          this.card = card;
      }
      
      @Override
      public String toString()
      {
          return "Customer [id =" + id +"," +"name =" +name +", age =" +age +", sex =" +sex +", card =" +card +"]";
      }
}


3.在com.kangxg.mapper包中创建映射文件IdCardMapper.xml 和PersonMapper.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">
<!-- namespace 表示命名空间  -->
<mapper namespace="com.kangxg.mapper.IdCardMapper">

  <!-- 根据id 查询证件信息 -->
  <select id="findCodeById" parameterType = "Integer" resultType="IdCard">
     select * from tb_idcard where id = #{id}
  </select>

</mapper>
<?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">
<!-- namespace 表示命名空间  -->
<mapper namespace="com.kangxg.mapper.PersonMapper">

  <!-- 根据id 查询证件信息 -->
  <select id="findPersonById" parameterType = "Integer" resultMap="IdCardWithPersonResult">
     select * from tb_person where id = #{id}
  </select>
  
  <resultMap type="Person" id="IdCardWithPersonResult">
    <id property = "id" column = "id"/>
    <result property = "name" column = "name"/>
    <result property = "age" column = "age"/>
    <result property = "sex" column = "sex"/>
    
    <association property = "card" column = "card_id" javaType = "IdCard"
       select = "com.kangxg.mapper.IdCardMapper.findCodeById"/>
  </resultMap>

</mapper>


4.配置核心文件,引入Mapper映射文件并设置别名

<?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"/>
  <!-- 使用扫描的形式定义别名 -->
  <typeAliases>
     <package name = "com.kangxg.po"/>
  </typeAliases>
  
  <!-- 1.配置环境,默认环境id 为 mysql -->
  <environments default="mysql">
    <!-- 1.2.配置id 为 mysql 的数据库环境-->
    <environment id="mysql">
      <!-- JDBC 事务管理-->
      <transactionManager type="JDBC"/>
       <!-- 数据库连接池-->
      <dataSource type="POOLED">
         <!-- 数据库驱动 -->
         <property name = "driver" value = "${jdbc.driver}" />
         <!-- 连接数据库URL-->
         <property name = "url" value = "${jdbc.url}" />
         <!-- 连接数据库的用户名-->
         <property name = "username" value = "${jdbc.username}" />
         <!-- 连接数据库的密码-->
         <property name = "password" value = "${jdbc.password}" />
      </dataSource>
    </environment>
  </environments>
  <!-- 2.配置Mapper的位置-->
  <mappers>
    <mapper resource="com/kangxg/mapper/IdCardMapper.xml"/>
    <mapper resource="com/kangxg/mapper/PersonMapper.xml"/>
  </mappers>
</configuration>


5.在测试文件中添加测试方法

    @Test
    public void findPersonByIdTest()throws Exception
    {
        SqlSession sqlSession =  MybatisUtils.getSession();
        
        Person person = sqlSession.selectOne("com.kangxg.mapper"+".PersonMapper.findPersonById",1 );

        System.out.println(person);
        
        sqlSession.close();
    }


6.debug 运行程序

MyBatis 的关联映射

从上图中可以看出来执行了多条查询,对于大型数据结合和列表展示会导致成百上千条关联的SQL语句执行,从而极大地消耗数据库性能并且会降低查询效率。为此做如下修改:

  <select id="findPersonById2" parameterType = "Integer" resultMap="IdCardWithPersonResult2">
     select p.*,idcard.code
     from tb_person p,tb_idcard idcard
     where p.card_id = idcard.id and p.id = #{id}
  </select>
  
  <resultMap type="Person" id="IdCardWithPersonResult2">
    <id property = "id" column = "id"/>
    <result property = "name" column = "name"/>
    <result property = "age" column = "age"/>
    <result property = "sex" column = "sex"/>
    
    <association property = "card"  javaType = "IdCard">
       <id property ="id" column ="card_id"/>
       <result property ="code" column = "code"/>
    </association>
  
  </resultMap>
    @Test
    public void findPersonByIdTest2()throws Exception
    {
        SqlSession sqlSession =  MybatisUtils.getSession();
        
        
        Person person = sqlSession.selectOne("com.kangxg.mapper"+".PersonMapper.findPersonById2",1 );

        System.out.println(person);
        
        
        sqlSession.close();
    }
运行结果:

MyBatis 的关联映射

多学一招:MyBatis延迟加载的设置

在使用MyBatis嵌套查询方式进行MyBatis关联查询映射时,使用MyBatis的延迟加载在一定程度上可以降低运行消耗并提高查询效率。在MyBatis默认没有开启延迟加载,需要在核心配置文件中的<setting>元素内进行配置

  <properties resource = "db.properties"/>
  <settings>
     <!-- 打开延迟加载开关 -->
     <setting name="lazyLoadingEnabled" value="True"/>
     <!--将积极加载改为消息加载,即按需加载 -->
     <setting name="aggressiveLazyLoading" value="false"/>
 </settings>
  <!-- 使用扫描的形式定义别名 -->
  <typeAliases>
     <package name = "com.kangxg.po"/>
  </typeAliases>
在映射文件中,MyBatis关联映射的<association>元素和<collection>元素中都已经默认配置了延迟加载属性,即默认属性 fetch ="lazy"(fetch="eager"表示立即加载),所以配置文件中开启延迟加载后,无须在映射文件中再进行配置。


二、一对多

1.创建两个数据表,并插入测试数据

create table tb_user( id int(32)  primary key auto_increment, username varchar(32),address varchar(256));

create table tb_orders( id int(32)  primary key auto_increment, number varchar(32) NOT NULL,user_id int(32) NOT NULL,foreign key(user_id) references tb_user(id));


insert into tb_user values('1','蔡英文','台北');   
insert into tb_user values('2','马英九','*');
insert into tb_user values('3','陈水扁','*');

insert into tb_orders values('1','1000011','1');
insert into tb_orders values('2','1000012','1');
insert into tb_orders values('3','1000013','2');


2.在com.kangxg.po包中创建持久化类Orders 和 User

package com.kangxg.po;

public class Orders {
      private Integer id;
      private String  number;
      
      public Integer getId()
      {
          return this.id;
      }
      
      public void setId(Integer id)
      {
          this.id = id;
      }
      
      public String getNumber()
      {
          return this.number;
      }
      
      public void setNumber(String number)
      {
          this.number = number;
      }
      
      @Override
      public String toString()
      {
          return "Orders [id =" + id +"," +"number =" +number  +"]";
      }
}

 
package com.kangxg.po;

import java.util.List;

public class User {
      private Integer id;
      private String  username;
      private String  address;
      private List<Orders> ordersList;
      
    
      public Integer getId()
      {
          return this.id;
      }
      
      public void setId(Integer id)
      {
          this.id = id;
      }
      
      public String getUsername()
      {
          return this.username;
      }
      
      public void setUsername(String username)
      {
          this.username = username;
      }
      
      public String getAddress()
      {
          return this.address;
      }
      
      public void setAddress(String address)
      {
          this.address = address;
      }
      
      public List<Orders> getOrders()
      {
          return this.ordersList;
      }
      
      public void setOrders(List<Orders> ordersList)
      {
          this.ordersList=  ordersList;
      }
      
      @Override
      public String toString()
      {
          return "User [id =" + id +"," +"username =" +username +", address =" +address +", ordersList =" +ordersList +"]";
      }
}


3.在 com.kangxg.mapper 包中创建UserMapper.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">
<!-- namespace 表示命名空间  -->
<mapper namespace="com.kangxg.mapper.UserMapper">

  <!-- 根据id 查询证件信息 -->
  <select id="findUserWithOrders" parameterType = "Integer" resultMap="UserWithOrdersResult">
     select u.*,o.id as orders_id,o.number
     from tb_user u,tb_orders o
     where u.id = o.user_id
     and u.id = #{id}
  </select>
  
  
  <resultMap type="User" id="UserWithOrdersResult">
    <id property = "id" column = "id"/>
    <result property = "username" column = "username"/>
    <result property = "address" column = "address"/>
  
    
    <collection property = "ordersList"  ofType = "Orders">
       <id property ="id" column ="orders_id"/>
       <result property ="number" column = "number"/>
    </collection>
  
  </resultMap>

</mapper>

4.测试类中创建 测试方法

    @Test
    public void findUserTest()throws Exception
    {
        SqlSession sqlSession =  MybatisUtils.getSession();
        
        
        User user = sqlSession.selectOne("com.kangxg.mapper"+".UserMapper.findUserWithOrders",1 );

        System.out.println(user);
        
        
        sqlSession.close();
    }


5. debug 运行程序

MyBatis 的关联映射


三、多对多

以订单和商品为例,一个订单可以包含多种商品,而一种商品又可以属于多个订单,订单和商品就属于多对多的关联关系。

1.创建两个数据表,并插入测试数据

create table tb_product( id int  primary key auto_increment, name varchar(32),price double); 


create table tb_ordersitem( id int (32) primary key auto_increment, orders_id  int(32),product_id int(32),foreign key(orders_id) references tb_orders(id),foreign key(product_id) references tb_product(id)); 


insert into tb_product values('1','java基础教程','44.5');   
insert into tb_product values('2','iOS开发秘籍','78');
insert into tb_product values('3','C现代编程','48');
insert into tb_product values('4','Android开发实战','50');

insert into tb_ordersitem values('1','1','1');
insert into tb_ordersitem values('2','2','2');
insert into tb_ordersitem values('3','3','3');

2.在com.kangxg.po包中创建持久化类Product

package com.kangxg.po;

import java.util.List;

public class Product {
      private Integer id;
      private String  name;
      private Double  price;
    
      
      private List<Orders> orders;
      
      public Integer getId()
      {
          return this.id;
      }
      
      public void setId(Integer id)
      {
          this.id = id;
      }
      
      public String getName()
      {
          return this.name;
      }
      
      public void setName(String name)
      {
          this.name = name;
      }
      
      public Double getPrice()
      {
          return this.price;
      }
      
      public void setPrice(Double price)
      {
          this.price = price;
      }
      
      
      
      public List<Orders> getOrders()
      {
          return this.orders;
      }
      
      public void setOrders(List<Orders> orders)
      {
          this.orders=  orders;
      }
      
      @Override
      public String toString()
      {
          return "Product [id =" + id +"," +"name =" +name +", price =" +price +"]";
      }
}


在Orders类中进行修改

      private List<Product> productList;

      public List<Product> getProducList()
      {
          return this.productList;
      }
      
      public void setProductList(List<Product> productList)
      {
          this.productList =  productList;
      }
      @Override
      public String toString()
      {
          return "Orders [id =" + id +"," +"number =" +number+"," +"productList =" +productList+"]";
      }


3.在com.kangxg.mapper包中创建OrdersMapper.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">
<!-- namespace 表示命名空间  -->
<mapper namespace="com.kangxg.mapper.OrdersMapper">

  <select id="findOrdersWithProduct" parameterType = "Integer" resultMap="OrdersWithProductResult">
     select o.*,p.id as pid,p.name,p.price
     from tb_orders o,tb_product p,tb_ordersitem oi
     where oi.orders_id = o.id
     and oi.product_id=p.id
     and o.id = #{id}
  </select>
  
  <resultMap type="Orders" id="OrdersWithProductResult">
    <id property = "id" column = "id"/>
    <result property = "number" column = "number"/>
    
    
    <collection property = "productList"    ofType = "Product">
             <id property = "id" column = "pid"/>
             <result property = "name" column = "name"/>
             <result property = "price" column = "price"/>
    </collection>
  
  </resultMap>

</mapper>


4.在核心配置文件中增加配置

 <mapper resource="com/kangxg/mapper/OrdersMapper.xml"/>


5.在测试类中增加测试方法

    @Test
    public void findOrdersTest()throws Exception
    {
        SqlSession sqlSession =  MybatisUtils.getSession();
        
        
        Orders orders = sqlSession.selectOne("com.kangxg.mapper."+"OrdersMapper.findOrdersWithProduct",1 );

        System.out.println(orders);
        
        
        sqlSession.close();
    }


6.debug运行程序

DEBUG [main] - ==>  Preparing: select o.*,p.id as pid,p.name,p.price from tb_orders o,tb_product p,tb_ordersitem oi where oi.orders_id = o.id and oi.product_id=p.id and o.id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
Orders [id =1,number =1000011,productList =[Product [id =1,name =java基础教程, price =44.5]]]
相关标签: MyBatis关联映射