Mybatis关联查询

数据库结构：

有2种方法：

实现功能，均为根据actId关联查询account表和user表的信息

方法1：

mapper：
AccountPO selectUserAndActs(Integer actId);

mapper.xml：

<resultMap id="accountPO" type="com.zhou.mybatis.dao.AccountPO">
    <id property="id" column="id"/>
    <result property="name" column="name_act"/>
    <result property="num" column="num"/>
    <association property="user" javaType="com.zhou.mybatis.dao.UserPO">
        <id property="id" column="id"/>
        <result property="name" column="name_user"/>
        <result property="age" column="age"/>
        <result property="address" column="address"/>
    </association>
</resultMap>

<select id="selectUserAndActs" resultMap="accountPO">
    select a.name name_act,u.name name_user ,a.*,u.* from user u, account a where u.id = a.user_id and a.id = #{id}
</select>

Test：

@Test
public void selectUserAndActsTest() throws IOException{
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    AccountPO userPO = mapper.selectUserAndActs(2);
    System.out.println(userPO.toString());
}

得出结果：

AccountPO{id=2, name='储蓄卡', num='4181818181811', user=UserPO{id='2', name='小周', age='18', address='深圳', crt_date=null}}

 

注：因为account表和user表中均有name属性，为了结果集中区分，采用了别名。才疏学浅，如有更好的方法处理，可以私信我

 

方法2：

mapper：

AccountPO selectUserAndActs1(Integer actId);

mapper.xml：

<resultMap id="accountPO1" type="com.zhou.mybatis.dao.AccountPO">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="num" column="num"/>
    <association property="user" javaType="com.zhou.mybatis.dao.UserPO" select="selectUser" column="user_id">
    </association>
</resultMap>

<select id="selectUser" resultType="com.zhou.mybatis.dao.UserPO">
    select * from user where id = #{id}
</select>

<select id="selectUserAndActs1" resultMap="accountPO1">
    select * from account where id = #{id}
</select>

Test

@Test
public void selectUserAndActs1Test() throws IOException{
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    AccountPO userPO = mapper.selectUserAndActs1(2);
    System.out.println(userPO.toString());
}

 

得出结果：

AccountPO{id=2, name='储蓄卡', num='4181818181811', user=UserPO{id='1', name='小周', age='18', address='深圳', crt_date=1581696000000}}
 

注：在关联selectUser方法时，column="user_id"的意思是根据account表中user_id栏位的值赋予selectUser方法查询条件