关联查询

在关系型数据库中，我们经常要处理一对多，多对一和多对多的关系。

   （1）案例3:关联映射一对多

           以国家和省份对应的一对多关系举例。

       Student表

       

       Teacher表

       

 

       创建Student.java类如下:

                 private int stuid;
                 private String stuname;
                 private String stuaddress;

}

   创建Teacher类如下:

public class Teacher {
    private int tid;
    private String tname;

   创建StuT.java接口如下:

 private Integer stuid;

 private Integer tid;

  创建IStudentDao.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.wdkseft.dao.ISmbmsRoleDao">
    <!--<resultMap id="roleAndUserMapper" type="SmbmsRole">
        <id column="rid" property="rid"></id>
        <result column="roleName" property="roleName"></result>
        <collection property="userList" ofType="SmbmsUser">
            <id column="id" property="id"></id>
            <result column="userName" property="userName"></result>
        </collection>
    </resultMap>

    <select id="getRoleAndUser" resultMap="roleAndUserMapper">
        select u.id,u.userName,u.userCode,r.rid,r.roleName  from smbms.smbms_role as r,smbms.smbms_user as u
        where u.userRole=r.rid and r.rid=#{id}
    </select>-->



    <!--<resultMap id="roleAndUserMapper" type="SmbmsRole">
        &lt;!&ndash;<id column="rid" property="rid"></id>
        <result column="roleName" property="roleName"></result>&ndash;&gt;
        <collection property="userList" ofType="SmbmsUser" select="getRoleAndUserMutilSQL" column="rid">
            &lt;!&ndash;<id column="id" property="id"></id>
            <result column="userName" property="userName"></result>&ndash;&gt;
        </collection>
    </resultMap>

    <select id="getRoleAndUser" resultMap="roleAndUserMapper">
        select * from smbms.smbms_role where rid=#{id}
    </select>
    <select id="getRoleAndUserMutilSQL" resultType="SmbmsUser">
        select * from smbms.smbms_user where userRole=#{rid}
    </select>-->

    <!--<resultMap id="roleAndUserMapper" type="SmbmsUser">
        <id column="id" property="id"></id>
        <result column="userName" property="userName"></result>
        <collection property="role" ofType="SmbmsRole">
            <id column="rid" property="rid"></id>
            <result column="roleName" property="roleName"></result>
        </collection>
    </resultMap>

    <select id="getUserList" resultMap="roleAndUserMapper">
        select u.id,u.userName,u.userCode,r.rid,r.roleName  from smbms.smbms_role as r,smbms.smbms_user as u
        where u.userRole=r.rid
    </select>-->

    <resultMap id="roleAndUserMapper" type="SmbmsUser">
        <id column="id" property="id"></id>
        <result column="userName" property="userName"></result>
        <association property="role" select="getRole" column="userRole">
            <id column="rid" property="rid"></id>
            <result column="roleName" property="roleName"></result>
        </association>
    </resultMap>

    <select id="getUserList" resultMap="roleAndUserMapper">
        select * from smbms.smbms_user
    </select>
    <select id="getRole" resultType="SmbmsRole">
        select * from smbms.smbms_role where rid=#{userRole}
    </select>

</mapper>

编写app测试程序:

package com.wdkseft.test;

import com.wdkseft.dao.Address_Sum;
import com.wdkseft.dao.ISmbmsRoleDao;
import com.wdkseft.dao.IStudentDao;
import com.wdkseft.entity.*;
import com.wdkseft.util.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class Tester {
    SqlSession session = SqlSessionUtils.getSession();
    @Test
    public void getAllPro(){

        Address_Sum mapper = session.getMapper(Address_Sum.class);
        List<ceishi> allPro = mapper.getAllPro("1", "2");
        for (ceishi cs:allPro) {
            System.out.println(cs.getName());
        }
    }

    @Test
    public void getProInData(){
        Address_Sum mapper = session.getMapper(Address_Sum.class);
        List<ceishi> proInData = mapper.getProInData(new String[]{"4", "6"});
        for (ceishi cs:proInData) {
            System.out.println(cs.getName());
        }
    }

    @Test
    public void AddallPro(){
        Address_Sum mapper = session.getMapper(Address_Sum.class);
        //添加
        ceishi cs = new ceishi();
        cs.setName("8");
        cs.setPhone("1475");
        cs.setEmail("114@ii.com");
        mapper.addAddressById(cs);
        session.commit();
    }

    @Test
    public void getRoleAndUser(){
        ISmbmsRoleDao mapper = session.getMapper(ISmbmsRoleDao.class);
        SmbmsRole role = mapper.getRoleAndUser(3);

        System.out.println("角色:"+role.getRoleName());
        for (SmbmsUser user:role.getUserList()) {
            System.out.print(" 用户:"+user.getUserName());
        }
    }

    @Test
    public void getUserList(){
        ISmbmsRoleDao mapper = session.getMapper(ISmbmsRoleDao.class);
        List<SmbmsUser> userList = mapper.getUserList();
        for (SmbmsUser user:userList) {
            System.out.println("用户:"+user.getUserName()+" 角色:"+user.getRole().getRoleName());
        }
    }

    @Test
    public void getStudentInfo(){
        IStudentDao mapper = session.getMapper(IStudentDao.class);
        List<Student> studentInfo = mapper.getStudentInfo();
        for (Student stu:studentInfo){
            System.out.println("学员:"+stu.getStuname());
            for (Teacher teacher:stu.getTeachers()){
                System.out.print(" 教员:"+teacher.getTname());
            }
            System.out.println();
        }
    }
}