MyBatis :多表连接

用到的表 : Hdept ( id , deptname) Hrole (id, rolename)

1, 一对一 : 使用ResultMap + Association 标签

HdeptMapper.xml文件中

<mapper namespace="com.sc.mapper.HdeptMapper">
  <resultMap id="BaseResultMap" type="com.sc.pojo.Hdept">
    <id column="ID" jdbcType="DECIMAL" property="id" />
    <result column="DEPTNAME" jdbcType="VARCHAR" property="deptname" />
    <!--propety必须和Hdept实体类中定义的Hrole类型变量名一致-->
    <association property="hrole" javaType="com.sc.pojo.Hrole">
      <id column="ID" property="id"></id>
      <result column="ROLENAME" property="rolename"></result>
    </association>
    
  </resultMap>
<!--id必须和Mapper中的接口的方法名一直-->
 <select id="queryHdeptAndHrole" resultMap="BaseResultMap">
    select d.id,d.deptname,r.id,r.rolename from Hdept d left join Hrole r on d.id=u.id
  </select>
  
</mapper>

实体类Hdept

package com.sc.pojo;

import java.io.Serializable;
import java.util.List;

public class Hdept implements Serializable {
    private Integer id;

    private String deptname;


    private Hrole hrole;

    
    public Hrole getHrole() {
        return hrole;
    }

    public void setHrole(Hrole hrole) {
        this.hrole = hrole;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDeptname() {
        return deptname;
    }

    public void setDeptname(String deptname) {
        this.deptname = deptname == null ? null : deptname.trim();
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getSimpleName());
        sb.append(" [");
        sb.append("Hash = ").append(hashCode());
        sb.append(", id=").append(id);
        sb.append(", deptname=").append(deptname);
        sb.append("]");
        return sb.toString();
    }
}

实体类Hrole

package com.sc.pojo;

import java.io.Serializable;

public class Hrole implements Serializable {
    private Integer id;

    private String rolename;


    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getRolename() {
        return rolename;
    }

    public void setRolename(String rolename) {
        this.rolename = rolename == null ? null : rolename.trim();
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getSimpleName());
        sb.append(" [");
        sb.append("Hash = ").append(hashCode());
        sb.append(", id=").append(id);
        sb.append(", rolename=").append(rolename);
        sb.append("]");
        return sb.toString();
    }
}

HdeptMapper接口

package com.sc.mapper;

import java.util.List;


public interface HdeptMapper {

    List<Hdept> queryHdeptAndHrole();
}

测试方法

    @Test
    public void testQuery1() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        sf = new SqlSessionFactoryBuilder().build(inputStream);
        session = sf.openSession();
        HdeptMapper hdeptMapper = session.getMapper(HdeptMapper.class);
        List<Hdept> list = hdeptMapper.queryHdeptAndHrole();
        for (Hdept d:list
             ) {
            System.out.println(d);
            System.out.println(d.getHrole());
        }
    }

运行结果

"C:\Program Files\Java\jdk1.8.0_102\bin\java.exe" -agentlib:jdwp=transport=dt_socket,address=127.0.0.1:62876,suspend=y,server=n -ea -Didea.test.cyclic.buffer.size=1048576 -javaagent:C:\Users\Bervin\.IntelliJIdea2018.2\system\captureAgent\debugger-agent.jar=file:/C:/Users/Bervin/AppData/Local/Temp/capture.props -Dfile.encoding=UTF-8 -classpath "C:\Program Files\JetBrains\IntelliJ IDEA 2018.2.2\lib\idea_rt.jar;C:\Program Files\JetBrains\IntelliJ IDEA 2018.2.2\plugins\junit\lib\junit-rt.jar;C:\Program Files\JetBrains\IntelliJ IDEA 2018.2.2\plugins\junit\lib\junit5-rt.jar;C:\Users\Bervin\.m2\repository\org\junit\platform\junit-platform-launcher\1.7.0-M1\junit-platform-launcher-1.7.0-M1.jar;C:\Users\Bervin\.m2\repository\org\apiguardian\apiguardian-api\1.1.0\apiguardian-api-1.1.0.jar;C:\Users\Bervin\.m2\repository\org\junit\platform\junit-platform-engine\1.7.0-M1\junit-platform-engine-1.7.0-M1.jar;C:\Users\Bervin\.m2\repository\org\opentest4j\opentest4j\1.2.0\opentest4j-1.2.0.jar;C:\Users\Bervin\.m2\repository\org\junit\platform\junit-platform-commons\1.7.0-M1\junit-platform-commons-1.7.0-M1.jar;C:\Users\Bervin\.m2\repository\org\junit\jupiter\junit-jupiter-engine\5.7.0-M1\junit-jupiter-engine-5.7.0-M1.jar;C:\Users\Bervin\.m2\repository\org\junit\jupiter\junit-jupiter-api\5.7.0-M1\junit-jupiter-api-5.7.0-M1.jar;C:\Users\Bervin\.m2\repository\org\junit\vintage\junit-vintage-engine\5.7.0-M1\junit-vintage-engine-5.7.0-M1.jar;C:\Users\Bervin\.m2\repository\junit\junit\4.13\junit-4.13.jar;C:\Users\Bervin\.m2\repository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\rt.jar;C:\Program Files\Java\jdk1.8.0_102\jre\lib\ojdbc6-11.2.0.3.jar;C:\sc2020\mybaties2020\target\test-classes;C:\sc2020\mybaties2020\target\classes;C:\repository\junit\junit\4.12\junit-4.12.jar;C:\repository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar;C:\repository\com\oracle\ojdbc6\11.2.0.3.0\ojdbc6-11.2.0.3.0.jar;C:\repository\org\mybatis\mybatis\3.4.5\mybatis-3.4.5.jar;C:\repository\org\mybatis\mybatis-ehcache\1.0.0\mybatis-ehcache-1.0.0.jar;C:\repository\net\sf\ehcache\ehcache-core\2.0.0\ehcache-core-2.0.0.jar;C:\repository\net\sf\ehcache\ehcache\2.10.4\ehcache-2.10.4.jar;C:\repository\org\slf4j\slf4j-api\1.7.7\slf4j-api-1.7.7.jar;C:\repository\org\apache\maven\maven-plugin-api\3.5.0\maven-plugin-api-3.5.0.jar;C:\repository\org\apache\maven\maven-model\3.5.0\maven-model-3.5.0.jar;C:\repository\org\codehaus\plexus\plexus-utils\3.0.24\plexus-utils-3.0.24.jar;C:\repository\org\apache\commons\commons-lang3\3.5\commons-lang3-3.5.jar;C:\repository\org\apache\maven\maven-artifact\3.5.0\maven-artifact-3.5.0.jar;C:\repository\org\eclipse\sisu\org.eclipse.sisu.plexus\0.3.3\org.eclipse.sisu.plexus-0.3.3.jar;C:\repository\javax\enterprise\cdi-api\1.0\cdi-api-1.0.jar;C:\repository\javax\annotation\jsr250-api\1.0\jsr250-api-1.0.jar;C:\repository\javax\inject\javax.inject\1\javax.inject-1.jar;C:\repository\org\eclipse\sisu\org.eclipse.sisu.inject\0.3.3\org.eclipse.sisu.inject-0.3.3.jar;C:\repository\org\codehaus\plexus\plexus-component-annotations\1.5.5\plexus-component-annotations-1.5.5.jar;C:\repository\org\codehaus\plexus\plexus-classworlds\2.5.2\plexus-classworlds-2.5.2.jar;C:\repository\org\mybatis\generator\mybatis-generator-core\1.3.5\mybatis-generator-core-1.3.5.jar;C:\repository\org\junit\jupiter\junit-jupiter-api\5.7.0-M1\junit-jupiter-api-5.7.0-M1.jar;C:\repository\org\apiguardian\apiguardian-api\1.1.0\apiguardian-api-1.1.0.jar;C:\repository\org\opentest4j\opentest4j\1.2.0\opentest4j-1.2.0.jar;C:\repository\org\junit\platform\junit-platform-commons\1.7.0-M1\junit-platform-commons-1.7.0-M1.jar;C:\repository\org\slf4j\slf4j-simple\1.7.25\slf4j-simple-1.7.25.jar" com.intellij.rt.execution.junit.JUnitStarter -ideVersion5 -junit5 TestMybatis,testQuery1
Connected to the target VM, address: '127.0.0.1:62876', transport: 'socket'
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1718906711.
Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@66746f57]
==>  Preparing: select d.id,d.deptname,r.id,r.rolename from Hdept d left join Hrole r on d.id=r.id 
==> Parameters: 
<==    Columns: ID, DEPTNAME, ID, ROLENAME
<==        Row: 1, admin1, 1, 普通会员
<==        Row: 2, admin2, 2, 管理员
<==        Row: 3, admin3, null, null
<==      Total: 3
==>  Preparing: select ID, USERNAME, PASSWORD, CREATETIME, DID from HUSER where DID=? 
==> Parameters: 1(BigDecimal)
<==      Total: 0
Hdept_$$_jvste6_0 [Hash = 2017860548, id=1, deptname=admin1]
Hrole [Hash = 1413886135, id=1, rolename=普通会员]
==>  Preparing: select ID, USERNAME, PASSWORD, CREATETIME, DID from HUSER where DID=? 
==> Parameters: 2(BigDecimal)
<==      Total: 0
Hdept_$$_jvste6_0 [Hash = 1693997031, id=2, deptname=admin2]
Hrole [Hash = 278262050, id=2, rolename=管理员]
==>  Preparing: select ID, USERNAME, PASSWORD, CREATETIME, DID from HUSER where DID=? 
==> Parameters: 3(BigDecimal)
<==      Total: 0
Hdept_$$_jvste6_0 [Hash = 1489322191, id=3, deptname=admin3]
Hrole [Hash = 1029898472, id=3, rolename=null]

2,一对多

一对多就是把association标签换成collection标签,把Hdept.java中的Hrole hrole变量换成List<Hrole> hroles,测试类遍历hroles,其他的不用变