mybatis_09关联查询_一对一

 

复杂查询时，单表对应的po类已不能满足输出结果集的映射。

所以有些时候就需要关联查询_一对一:通过条件查询结果每个字段都唯一

一对一:模型里面有模型

一对多:模型里面有集合

多对多:集合里面有集合

 

方法一:resultType实现,要根据需求建立一个扩展类来作为resultType的类型。(详细代码)

拓展类OrderEst:

package com.ahd.model;


public class OrderExt extends Orders {
    private String username;
    private String address;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {

        return "OrderExt{" +
                "username='" + username + '\'' +
                ", address='" + address + '\'' +
                '}'+super.toString();
    }
}

 

 

OrderMapper.java

package com.ahd.mapper;

import com.ahd.model.OrderExt;
import com.ahd.model.User;
import com.ahd.vo.UserQueryVO;

import java.util.List;
import java.util.Map;

public interface OrderMapper {
    public OrderExt findOrderExtbyId(int id);
}

OrderMapper.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">

<mapper namespace="com.ahd.mapper.OrderMapper">
   <select id="findOrderExtbyId" parameterType="int" resultType="com.ahd.model.OrderExt">
    select
      o.*,u.username,u.address
    from
      `user` u,orders o
    where u.id=o.user_id
    and u.id=#{id}
   </select>

</mapper>

总配置文件:SQLMapConfig.xml

<?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>

    <!--自定义别名-->
    <typeAliases>
        <package name="com.ahd.model"></package>

    </typeAliases>
    <!-- 配置mybatis的环境信息 -->
    <environments default="development">
        <environment id="development">

            <!-- 配置JDBC事务控制，由mybatis进行管理 -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 配置数据源，采用dbcp连接池 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/ahd/mapper/OrderMapper.xml"></mapper>
    </mappers>
</configuration>

 

 

测试文件Test:

测试文件:Test
package com.ahd.Test;

import com.ahd.mapper.OrderMapper;
import com.ahd.mapper.UserMapper;
import com.ahd.model.OrderExt;
import com.ahd.model.User;
import com.ahd.vo.UserQueryVO;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Demo09 {
    SqlSession sqlSession=null;

    @Before
    public void before() throws IOException {
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(is);
        sqlSession=ssf.openSession();
    }
    /*resultMap*/
    @Test
    public void test() throws IOException {
        OrderMapper mapper=sqlSession.getMapper(OrderMapper.class);

        OrderExt oe=mapper.findOrderExtbyId(1);

        System.out.println(oe);
        sqlSession.commit();
        sqlSession.close();

    }
}

 

方法二:resultMap实现(关键代码)

掌握association的使用

OrderMap.xml:

<resultMap id="orderRslMap" type="orders">
    <id property="id" column="id"></id>
    <result property="number" column="number"></result>
    <result property="createtime" column="createtime"></result>
    <result property="note" column="note"></result>
    <!--
    property:为order类中属性
    javaType:为具体类的类型
    -->
    <!-- 往orders的user匹配数据,模型里有模型，使用association来配置-->
    <association property="user" javaType="user">
        <id property="id" column="user_id"></id>
        <result property="username" column="username"></result>
        <result property="address" column="address"></result>
    </association>
</resultMap>
<select id="findOrderExtbyId2" parameterType="int" resultMap="orderRslMap">
    select
    o.*,u.username,u.address
    from
    `user` u,orders o
    where u.id=o.user_id
    and u.id=#{id}
</select>