【mybatis】mybatis 使用association关联查询的几种方式

转自：

=====================================

这里以一对一单向关联为例。对使用或不使用association的配置进行举例。

实体类：

@Data
@ToString
@NoArgsConstructor
public class IdCard {
    private Integer id;
    private String number;
    private Date expiredTime;

    public IdCard(Integer id) {
        this.id = id;
    }

}


@Data
@ToString
@NoArgsConstructor
public class Person {
    protected Integer id;
    protected String name;
    protected IdCard idCard;

    public Person(Integer id) {
        this.id = id;
    }
}

----------------------------------------------

对于关联属性的配置，有5种方式：

---------------------------------------------

方式零：使用内连接+级联属性：

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

方式一：使用内连接+扩展类：

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



    
        
        
        
        
        <result property="cardId" column="cid" />
        <result property="cardNumber" column="number" />
        <result property="cardExpiredTime" column="expired_time" />

扩展类：

package com.sunwii.mybatis.beanresult;

import java.util.Date;

import com.sunwii.mybatis.bean.IdCard;
import com.sunwii.mybatis.bean.Person;

@SuppressWarnings("unused")
public class PersonResult extends Person{
    private Integer cardId;
    private String cardNumber;
    private Date cardExpiredTime;

    public PersonResult() {
        super();
        //即时实例化关联对象
        super.setIdCard(new IdCard());
    }

    public void setCardId(Integer cardId) {
        this.cardId = cardId;
        //设置
        super.getIdCard().setId(cardId);
    }

    public void setCardNumber(String cardNumber) {
        this.cardNumber = cardNumber;
        //设置
        super.getIdCard().setNumber(cardNumber);
    }
    public void setCardExpiredTime(Date cardExpiredTime) {
        this.cardExpiredTime = cardExpiredTime;
        //设置
        super.getIdCard().setExpiredTime(cardExpiredTime);
    }

    
}

方式二：使用内连接+association内联result设置：

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



    
        
        

        
        <association property="idCard" column="idcard_id" javaType="IdCard">
            <id column="cid" property="id" />
            <result column="number" property="number" />
            <result column="expired_time" property="expiredTime" />
        association>

方式三：使用内连接+association引用resultMap

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



    
        
        

        
        <association property="idCard" column="cid"
            resultMap="com.sunwii.mybatis.mapper.IdCardMapper.IdCardMap" />

注意：对于IdCardMapper，为配合方式三需要修改查询到的id属性为cid（即指定column="cid"）：

方式四：使用单表查询+association引用select方式，不用inner查询（以避免再次查询），可以利用延迟加载，配置：

一个一对一单向关联使用注解的例子：(采用方式四非join方式，延迟加载）

Mapper接口类：

@Mapper
public interface IdCardMapper {
    @Select("select id,number,expired_time from t_idcard where id=#{id}")
    @Results(id="IdCardMap", value = {
            @Result(property = "id", column = "id"),
            @Result(property = "number", column = "number"),
            @Result(property = "expiredTime", column = "expired_time"), 
    })
    public IdCard selectById(Integer id);

    @Insert("insert into t_idcard(number,expired_time) values(#{number},#{expiredTime}")
    @Options(keyColumn = "id",keyProperty = "id",useGeneratedKeys = true)
    public int insertIdCard(IdCard idCard);

    @Update("update t_idcard set number=#{number},expired_time=#{expiredTime}")
    public int updateIdCard(IdCard idCard);

    @Delete("delete from t_idcard where id=#{id}")
    public int deleteIdCard(IdCard idCard);
}

@Mapper
public interface PersonMapper {

    @Select("select id,name,idcard_id from t_person where id=#{id}")
    @Results(id="PersonMap",value = {
            @Result(property = "id",column = "id"),
            @Result(property = "name",column = "name"),
            @Result(property = "idCard",column = "idcard_id", 
            one=@One(select = "com.sunwii.mybatis.mapper.IdCardMapper.selectById",fetchType = FetchType.LAZY))
    })
    public Person selectById(Integer id);
    
    @Insert("insert into t_person(name,idcard_id) values(#{name},#{idCard.id})")
    @Options(keyColumn = "id",keyProperty = "id",useGeneratedKeys = true)
    public int insertPerson(Person person);
    
    @UpdateProvider(type = com.sunwii.mybatis.provider.PersonDynamicSqlProvider.class, method = "update")
    public int updatePerson(Person person);
    
    @Delete("delete from t_person where id=#{id}")
    public int deletePerson(Person person);
    
}

动态SQL支持：

public class PersonDynamicSqlProvider {
    public String update(Person person) {
        return new SQL() {
            {
                UPDATE("t_person");
                SET("name=#{name}");
                
                if(person.getIdCard()!=null) {
                    SET("idcard_id=#{idCard.id}");
                }
                
                WHERE("id=#{id}");
            }
        }.toString();
    }
}

业务层：

@Service
public class IdCardServiceImpl implements IdCardService{
    @Autowired
    private IdCardMapper idCardMapper;

    @Override
    public IdCard getIdCard(Integer id) {
        return idCardMapper.selectById(id);
    }

    @Override
    @Transactional
    public void updateIdCard(IdCard idCard) {
        idCardMapper.updateIdCard(idCard);
    }

    @Override
    @Transactional
    public void insertIdCard(IdCard idCard) {
        idCardMapper.insertIdCard(idCard);
    }
    
}

@Service
public class PersonServiceImpl implements PersonService {
    @Autowired
    private PersonMapper personMapper;

    @Autowired
    private IdCardMapper idCardMapper;

    @Override
    public Person getPerson(Integer id) {
        return personMapper.selectById(id);
    }

    @Override
    @Transactional
    public void insertPersion(Person person) {
        // 一对于单向关联：执行主表的插入前，先执行从被关联表的插入并获取其最新插入的主健
        // 由于插件后会自动更新关联实体的ID，所以这里不需要进行设置
        IdCard idCard = person.getIdCard();

        // 这种方式将对Person的属性设置时不进行setIdCart()，会将IdCard的插入延迟，可在后续进行补充的添加（不要求一定要有IdCard）。
        // 必须配合来操作
        if (idCard != null) {
            idCardMapper.insertIdCard(idCard);
        }

        personMapper.insertPerson(person);

    }

    @Override
    @Transactional
    public void updatePersion(Person person) {
        // 每次更新都要先更新被关联表，这样不行，必须独立到从表的更新去=>idCardService.updateIdCard(idCard)
        // IdCard idCard = person.getIdCard();
        // if (idCard != null && idCard.getId() != null) {
        // idCardMapper.updateIdCard(idCard);
        // }

        personMapper.updatePerson(person);

    }

    @Override
    @Transactional
    public void deletePersion(Person person) {
        // 一对于单向关联：执行主表的删除后，再执行被关联表的删除
        // 由于插件后会自动更新关联实体的ID，所以这里不需要进行设置

        personMapper.deletePerson(person);

        IdCard idCard = person.getIdCard();

        // 有IdCard则删除
        if (idCard != null) {
            idCardMapper.deleteIdCard(idCard);
        }
    }

}

SpringUtil工具类：

public class SpringUtil {
    private static ApplicationContext context = null; 
    static {
        context = new ClassPathXmlApplicationContext("applicationContext.xml");
    }
    public static ApplicationContext getContext() {
        return context;
    }
}

Mybatis配置文件：

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

Spring配置文件：

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

    xmlns:mybatis="http://mybatis.org/schema/mybatis-spring"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
    http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
    http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd
    http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd">

    
    

    
    
        
        
        
        
        
        
    

    
    
        
        
        
    

    
    <mybatis:scan base-package="com.sunwii.mybatis.mapper" />

测试类：

public class TestOne2OnePerson {
    private ApplicationContext context = SpringUtil.getContext();
    private PersonService personService = (PersonService) context.getBean(PersonService.class);
    private IdCardService idCardService = (IdCardService) context.getBean(IdCardService.class);

    @Test
    public void testPersonInsert() {
        // 一对一单向：添加。
        Person person = new Person();
        person.setName("person-1");

        IdCard idCard = new IdCard();
        idCard.setNumber(UUID.randomUUID().toString());
        idCard.setExpiredTime(CurrentUtil.currentDate());
        
        person.setIdCard(idCard);

        personService.insertPersion(person);
    }
    

    @Test
    public void testPersonInsert2() {
        // 一对一单向：添加。
        Person person = new Person();
        person.setName("person-8");
        personService.insertPersion(person);
        
        //后续的操作，添加idCard并更新Person
        IdCard idCard = new IdCard();
        idCard.setNumber(UUID.randomUUID().toString());
        idCard.setExpiredTime(CurrentUtil.currentDate());
        idCardService.insertIdCard(idCard);
        person.setIdCard(idCard);
        personService.updatePersion(person);
        
        
    }

    @Test
    public void testPersonSelect() {
        // 一对一单向：查询。
        int id = 6;
        Person person = personService.getPerson(id);
        System.out.println(person.toLasyString());
        System.out.println(person.toString());
    }
    


    @Test
    public void testPersonUpdate() {
        // 一对一单向：更新。
        int id = 6;
        Person person = personService.getPerson(id);
        person.setName("person-1-update");
        personService.updatePersion(person);
        
        System.out.println(person);
    }
    

    @Test
    public void testIdCardUpdate() {
        // 一对一单向：更新。
        int id = 3;
        Person person = personService.getPerson(id);
    
        IdCard idCard = person.getIdCard();
        idCard.setNumber(UUID.randomUUID().toString());
        
        idCardService.updateIdCard(idCard);
        
        System.out.println(person);
    }
    

    @Test
    public void testPersonDelete() {
        // 一对一单向：删除。
        int id = 3;
        Person person = personService.getPerson(id);
        personService.deletePersion(person);
        
    }
}

以上为注解版的一对一的使用示例，也可以使用非注解版（XML版本），需要增加Mapper映射文件。

Mapper映射文件：

PersonMapper.xml:

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



    
        
        
      
        
        

    
    

    
        insert into t_person(name,idcard_id)
        values(#{name},#{idCard.id})
    


    
        update t_person set name=#{name}
        
            ,idcard_id=#{idCard.id}
        
        where id=#{id}
    

    
        delete from t_person
        where id=#{id}

IdCardMapper.xml:

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



    
        
        
        
    
     
    
    
    

    
        insert into
        t_idcard(number,expired_time) values(#{number},#{expiredTime})
    


    
        update t_idcard set
        number=#{number},expired_time=#{expiredTime} where id=#{id}
    

    
        delete from t_idcard
        where id=#{id}

一对多(以及多对一）的示例（含XML版本和注解版本），链接：

多对多(以及多对一）的示例（含XML版本和注解版本），链接：

自关联示例（含XML版本和注解版本），链接：

===============end of <<Mybatis中使用association进行关联的几种方式>>=====================

mybatis

【mybatis】mybatis 使用association关联查询的几种方式

相关

mybatis的一级缓存和二级缓存

Springboot配置tk-mybatis

jdbc,jpa,springjdbc，springdatajpa,mybatis之间的区别

Mybatis：简介

SpringBoot学习- 3、整合MyBatis

spring-boot mybatis配置

SpringBoot+Mybatis加载Mapper.xml文件的两种方式

mybatis-plus 多租户屏蔽某个特定mapper的方法@InterceptorIgnore

[Mybatis]2.增删改

MyBatis学习总结（二）——MyBatis核心配置文件与输入输出映射

MyBatis学习笔记

mybatis的Could not set property ‘id’ of ‘com.*.sys.entity’ with value ‘****

标签