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();
    }
}

orderest

 

 

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>

sqlmapconfig.xml

 

 

测试文件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>