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>