MyBatis 关联查询的实现：使用扩展类、嵌套查询实现一对一（了解）

 

有2个实体：用户、会员卡，一个用户只能办理一张会员卡，即一对一。

 

 

user_tb : 

 需要在一方引入另一方的主键作为外键。

 

 

card_tb:

 

 

---

 

 

使用扩展类

（1）在pojo包下新建user类：

package com.chy.pojo;

public class user {
    private integer id;  //主键
    private string name;  //姓名
    private string tel;  //手机号
    private string address;  //地址

    public integer getid() {
        return id;
    }

    public void setid(integer id) {
        this.id = id;
    }

    public string getname() {
        return name;
    }

    public void setname(string name) {
        this.name = name;
    }

    public string gettel() {
        return tel;
    }

    public void settel(string tel) {
        this.tel = tel;
    }

    public string getaddress() {
        return address;
    }

    public void setaddress(string address) {
        this.address = address;
    }

    @override
    public string tostring() {
        return "user{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", tel='" + tel + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

 

 

（2）在pojo包下新建user的扩展类userext，继承user，并把card的属性添加进来，提供对应的getter、setter方法。

package com.chy.pojo;

public class userext extends user {
    private integer no;
    private float money;

    public integer getno() {
        return no;
    }

    public void setno(integer no) {
        this.no = no;
    }

    public float getmoney() {
        return money;
    }

    public void setmoney(float money) {
        this.money = money;
    }

    @override
    public string tostring() {
        return super.tostring()+",card{" +
                "no=" + no +
                ", money=" + money +
                '}';
    }
}

先alt+insert插入tostring()，再拼接上user的tostring()，然后修改下就ok。

 

 

（3）编写usermapper接口、usermapper.xml

package com.chy.mapper;

import com.chy.pojo.userext;

public interface usermapper {
    public userext queryuserextbyid(integer id);
}

<?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.chy.mapper.usermapper">
    <select id="queryuserextbyid" parametertype="integer" resulttype="userext">
        select user_tb.*,card_tb.* from user_tb,card_tb where user_tb.id=#{id} and user_tb.card_no=card_tb.no
     </select>
</mapper>

 

 

(4)使用

package com.chy.utils;

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 java.io.ioexception;
import java.io.inputstream;

public class mybatisutils {
    private static sqlsessionfactory sqlsessionfactory;

    static {
        try {
            inputstream inputstream = resources.getresourceasstream("mybatis-config.xml");
            sqlsessionfactory = new sqlsessionfactorybuilder().build(inputstream);
        } catch (ioexception e) {
            e.printstacktrace();
        }
    }

    public static sqlsession getsqlsession(){
        return sqlsessionfactory.opensession();
    }
}

 

     sqlsession sqlsession = mybatisutils.getsqlsession();
        usermapper mapper = sqlsession.getmapper(usermapper.class);
        userext userext = mapper.queryuserextbyid(1);
        system.out.println(userext);
        sqlsession.close();

 

 

使用扩展类可以实现一对一的关联查询，但没有体现实体之间的关联关系（一个pojo中包含另一个pojo）。

 

 

 

---

 

 

 

使用嵌套查询

（1）给2个“一”都编写pojo类，需要在一个“一”中关联另一个“一”

package com.chy.pojo;

public class user {
    private integer id;  //主键
    private string name;  //姓名
    private string tel;  //手机号
    private string address;  //地址
    private card card;  //与之关联的card

    public integer getid() {
        return id;
    }

    public void setid(integer id) {
        this.id = id;
    }

    public string getname() {
        return name;
    }

    public void setname(string name) {
        this.name = name;
    }

    public string gettel() {
        return tel;
    }

    public void settel(string tel) {
        this.tel = tel;
    }

    public string getaddress() {
        return address;
    }

    public void setaddress(string address) {
        this.address = address;
    }

    public card getcard() {
        return card;
    }

    public void setcard(card card) {
        this.card = card;
    }

    @override
    public string tostring() {
        return "user{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", tel='" + tel + '\'' +
                ", address='" + address + '\'' +
                ", card=" + card +
                '}';
    }
}

 

package com.chy.pojo;

public class card {
    private integer no;  //会员卡编号
    private float money;  //余额

    public integer getno() {
        return no;
    }

    public void setno(integer no) {
        this.no = no;
    }

    public float getmoney() {
        return money;
    }

    public void setmoney(float money) {
        this.money = money;
    }

    @override
    public string tostring() {
        return "card{" +
                "no=" + no +
                ", money=" + money +
                '}';
    }
}

 外键是用来辅助sql操作的，并不是实体的属性，所以pojo类一般不包含外键字段。

 

 

（2）给这2个pojo类都编写mapper接口、xml映射文件

public interface cardmapper {
    public card querycardbyuserid(integer no);
}

<?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.chy.mapper.cardmapper">
    <select id="querycardbyno" parametertype="integer" resulttype="card">
        select * from card_tb where no=#{no}
     </select>
</mapper>

 

 

package com.chy.mapper;

import com.chy.pojo.user;

public interface usermapper {
    public user queryuserbyid(integer id);
}

<?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.chy.mapper.usermapper">
    <select id="queryuserbyid" parametertype="integer" resultmap="userresultwithcard">
        select * from user_tb where id=#{id}
     </select>
    <resultmap id="userresultwithcard" type="user">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="tel" column="tel"/>
        <result property="address" column="address"/>
        <association property="card" column="card_no" javatype="card" select="com.chy.mapper.cardmapper.querycardbyno"/>
    </resultmap>
</mapper>

sql语句都是查询当前pojo类对应的数据表，但usermapper使用<resultmap>的<association>元素指定了嵌套查询。

• property属性指定当前pojo类中表示另一个“一”的属性名
• column属性指定当前数据表中关联另一个“一”的列（外键）
• javatype属性指定与当前pojo类关联的另一个“一”的数据类型。
• select属性指定要使用的另一个“一”的哪个sql元素关联（namespace+id），执行当前<select>查询时，会自动嵌套另一个“一”的<select>进行查询。

 

 

（3）使用

package com.chy.utils;

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 java.io.ioexception;
import java.io.inputstream;

public class mybatisutils {
    private static sqlsessionfactory sqlsessionfactory;

    static {
        try {
            inputstream inputstream = resources.getresourceasstream("mybatis-config.xml");
            sqlsessionfactory = new sqlsessionfactorybuilder().build(inputstream);
        } catch (ioexception e) {
            e.printstacktrace();
        }
    }

    public static sqlsession getsqlsession(){
        return sqlsessionfactory.opensession();
    }
}

 

package com.chy.test;

import com.chy.mapper.usermapper;
import com.chy.pojo.user;
import com.chy.utils.mybatisutils;
import org.apache.ibatis.session.*;
import java.io.ioexception;

public class test {
    public static void main(string[] args) {
        sqlsession sqlsession = mybatisutils.getsqlsession();
        usermapper mapper = sqlsession.getmapper(usermapper.class);
        user user = mapper.queryuserbyid(1);
        system.out.println(user);
        sqlsession.close();
    }
}

 

使用嵌套查询体现了实体之间的关联关系，但一条查询会触发另一个与之关联的查询，另一个查询如果有与之关联的查询，也会触发.....链式反应，极大地降低了查询效率和数据库的性能，不推荐。