MyBatis中映射文件和注解的关系查询（一对一，一对多，多对多）

最新推荐文章于 2024-06-19 13:51:28 发布

csdn_phy

最新推荐文章于 2024-06-19 13:51:28 发布

阅读量1.6k

点赞数

分类专栏： MyBatis 文章标签： java 框架 mybatis

本文链接：https://blog.youkuaiyun.com/phy1997/article/details/79045105

版权

MyBatis 专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍了在MyBatis中如何进行一对一、一对多和多对多的关系查询。通过案例展示了User与Card的一对一查询，Locations与Citys的一对多查询，以及Tb_user与Tb_jues的多对多查询。在一对一查询中，涉及到User和Card实体类，以及UserDao和User.xml映射文件的配置。一对多查询中，讨论了Locations和Citys实体类的映射。多对多查询部分提到了Tb_user和Tb_jues实体类，以及如何通过注解方式简化映射文件的使用。最后，阐述了如何在mybatis-config.xml中配置注解，并在测试类中进行验证。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

映射关系的查询

一对一查询：（案例--人和卡）

User实体类

package com.entity;

public class User {

    private int uid;
    private String uname;
    private  String upass;

    //一对一关系  
    private Card card;

    public Card getCard() {
        return card;
    }

    public void setCard(Card card) {
        this.card = card;
    }
    
    public User() {
    }

    public User(int uid, String uname, String upass) {
        this.uid = uid;
        this.uname = uname;
        this.upass = upass;
    }

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getUname() {
        return uname;
    }

    public void setUname(String uname) {
        this.uname = uname;
    }

    public String getUpass() {
        return upass;
    }

    public void setUpass(String upass) {
        this.upass = upass;
    }

    @Override
    public String toString() {
        return "User{" +
                "uid=" + uid +
                ", uname='" + uname + '\'' +
                ", upass='" + upass + '\'' +
                '}';
    }
}

Card实体类

package com.entity;

public class Card {

    private  int cid;
    private  String cnum;
    
    public Card() {
    }

    public Card(int cid, String cnum) {
        this.cid = cid;
        this.cnum = cnum;
    }

    public int getCid() {
        return cid;
    }

    public void setCid(int cid) {
        this.cid = cid;
    }

    public String getCnum() {
        return cnum;
    }

    public void setCnum(String cnum) {
        this.cnum = cnum;
    }

    @Override
    public String toString() {
        return "Card{" +
                "cid=" + cid +
                ", cnum='" + cnum + '\'' +
                '}';
    }
}

mybatis-config.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.entity"></package>
    </typeAliases>


    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test"/>
                <property name="username" value="root"/>
                <property name="password" value="123"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/entity/User.xml"/>
        <!-- 注册UserMapper映射接口-->
       
    </mappers>

</configuration>

UserDao--接口

public User getMyUserAndCardById(int uid);

User.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.dao.UserDao">
    
    <!--一对一关系-->
     <resultMap id="myUserMap" type="com.entity.User">
         <id property="uid" column="uid"></id>
         <result property="uname" column="uname"></result>
         <result property="upass" column="upass"></result>
         <association property="card" javaType="com.entity.Card">  //对象标签association
          <result property="cid" column="cid"></result>
          <result property="cnum" column="cnum"></result>
         </association>
     </resultMap>

    <!--一对一的查询-->
    <select id="getMyUserAndCardById" parameterType="int" resultMap="myUserMap">
        select u.*,c.cnum from user u,card c where u.uid=c.cid and u.uid=#{uid};
    </select>

</mapper>

一对一测试类：

//加载配置文件并得到SQLsessionfactory
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
//得到sqlsession
SqlSession sqlSession=sqlSessionFactory.openSession();

//得到dao的接口类
UserDao userDao=sqlSession.getMapper(UserDao.class);

//查询一对一的关系
User user=userDao.getMyUserAndCardById(1);
System.out.println(user);
System.out.println(user.getCard().getCnum());

sqlSession.close();

一对多查询：（案例--省和城市）

Locations实体类：

package com.entity;

import java.util.Set;

public class Locations {

    private int loc_id;
    private String loc_name;

    //一对多关系
    private Set<Citys> citys;

    public Set<Citys> getCitys() {
        return citys;
    }

    public void setCitys(Set<Citys> citys) {
        this.citys = citys;
    }

    public Locations() {
    }

    public Locations(int loc_id, String loc_name) {
        this.loc_id = loc_id;
        this.loc_name = loc_name;
    }

    public int getLoc_id() {
        return loc_id;
    }

    public void setLoc_id(int loc_id) {
        this.loc_id = loc_id;
    }

    public String getLoc_name() {
        return loc_name;
    }

    public void setLoc_name(String loc_name) {
        this.loc_name = loc_name;
    }

    @Override
    public String toString() {
        return "Locations{" +
                "loc_id=" + loc_id +
                ", loc_name='" + loc_name + '\'' +
                '}';
    }
}

Citys实体类：

package com.entity;

public class Citys {

    private int cit_id;
    private String cit_name;
    private  int loc_id;


    public Citys() {
    }

    public Citys(int cit_id, String cit_name, int loc_id) {
        this.cit_id = cit_id;
        this.cit_name = cit_name;
        this.loc_id = loc_id;
    }

    public int getCit_id() {
        return cit_id;
    }

    public void setCit_id(int cit_id) {
        this.cit_id = cit_id;
    }

    public String getCit_name() {
        return cit_name;
    }

    public void setCit_name(String cit_name) {
        this.cit_name = cit_name;
    }

    public int getLoc_id() {
        return loc_id;
    }

    public void setLoc_id(int loc_id) {
        this.loc_id = loc_id;
    }

    @Override
    public String toString() {
        return "Citys{" +
                "cit_id=" + cit_id +
                ", cit_name='" + cit_name + '\'' +
                ", loc_id=" + loc_id +
                '}';
    }
}

Dao方法:

//通过省份得到城市
 public Locations getCitysById(int loc_id);

User.xml中配置:

<resultMap id="myLocationsMap" type="com.entity.Locations">
   <id property="loc_id" column="loc_id"></id>
   <result property="loc_name" column="loc_name"></result>
    <collection property="citys" ofType="com.entity.Citys">
        <id property="cit_id" column="cit_id"></id>
        <result property="cit_name" column="cit_name"></result>
    </collection>
</resultMap>

<!--一对多的查询-->
 <select id="getCitysById" parameterType="int" resultMap="myLocationsMap">
    select l.*,c.* from locations l,citys c  where l.loc_id=c.loc_id and l.loc_id=#{loc_id};
 </select>

测试类：

//查询一对多的关系
Locations locations=userDao.getCitysById(2);
for (Citys citys : locations.getCitys()) {
    System.out.println(citys);
}

多对多查询：（案例--用户和角色）

Tb_user实体类

package com.entity;

import java.util.Set;

public class Tb_user {

    private int u_id;
    private String u_name;

     private Set<Tb_jues> tb_jues;

    public Set<Tb_jues> getTb_jues() {
        return tb_jues;
    }


    public void setTb_jues(Set<Tb_jues> tb_jues) {
        this.tb_jues = tb_jues;
    }

    public Tb_user() {
    }

    public Tb_user(int u_id, String u_name) {
        this.u_id = u_id;
        this.u_name = u_name;
    }

    public int getU_id() {
        return u_id;
    }

    public void setU_id(int u_id) {
        this.u_id = u_id;
    }

    public String getU_name() {
        return u_name;
    }

    public void setU_name(String u_name) {
        this.u_name = u_name;
    }

    @Override
    public String toString() {
        return "Tb_user{" +
                "u_id=" + u_id +
                ", u_name='" + u_name + '\'' +
                '}';
    }
}

Tb_jues实体类:

package com.entity;

import java.util.Set;

public class Tb_jues {

    private int j_id;
    private String j_name;

    private Set<Tb_user> tb_users;

    public Set<Tb_user> getTb_users() {
        return tb_users;
    }

    public void setTb_users(Set<Tb_user> tb_users) {
        this.tb_users = tb_users;
    }

    public Tb_jues() {
    }

    public Tb_jues(int j_id, String j_name) {
        this.j_id = j_id;
        this.j_name = j_name;
    }

    public int getJ_id() {
        return j_id;
    }

    public void setJ_id(int j_id) {
        this.j_id = j_id;
    }

    public String getJ_name() {
        return j_name;
    }

    public void setJ_name(String j_name) {
        this.j_name = j_name;
    }

    @Override
    public String toString() {
        return "Tb_jues{" +
                "j_id=" + j_id +
                ", j_name='" + j_name + '\'' +
                '}';
    }
}

Dao方法：

  //通过用户编号得到有关的所有角色
    public Tb_user getALLj(int u_id);

User.xml映射文件的配置：

<!--查询所有的用户-->
   <resultMap id="getall" type="com.entity.Tb_user">
       <id property="u_id" column="u_id"></id>
       <result property="u_name" column="u_name"></result>
       <collection property="tb_jues" ofType="com.entity.Tb_jues">
           <id property="j_id" column="j_id"></id>
           <result property="j_name" column="j_name"></result>

       </collection>
   </resultMap>
   

   <select id="getALLj" parameterType="int" resultMap="getall">
    select u.*,j.j_name from tb_user u,tb_jues j,u_j uj where uj.u_id=j.j_id and u.u_id=#{u_id};
</select>

测试类：

Tb_user tb_user=userDao.getALLj(1);
for (Tb_jues tb_jues : tb_user.getTb_jues()) {
    System.out.println("::"+tb_jues.getJ_name());
}

通过注解实现关系查询：

不需要映射文件了

UserDao的接口方法：

 //通过注解方式实现一对一关系查询
 //查询卡
 @Select("select * from card where cid=#{uid}")
 public Card getCardById(int uid);

 //通过用户id得到用户信息和卡号
 @Select("select * from user where uid=#{uid}")
 @Results({
         @Result(id=true, column="uid", property="uid"),
         @Result(column="uname", property="uname"),
         @Result(property="card", column="uid",  //column为主表的id
                 one=@One(select="com.dao.UserDao.getCardById"))  //调用查询卡的方法
 })
 public User getMyUserAndCardById_zhujie(int uid);




//通过注解实现一对多的关系
 //查询省份下面的所有城市
 //先查找城市
 @Select("select * from citys where loc_id=#{loc_id}")
 @Results({
         @Result(id=true, column="cit_id", property="cit_id"),
         @Result(column="cit_name", property="cit_name")
 })
 public List<Citys> getAllCitysZ(int loc_id);


  //查找省份
  @Select("select * from locations where loc_id=#{loc_id}")
  @Results({
         @Result(id=true, column="loc_id", property="loc_id"),
         @Result(column="loc_name", property="loc_name"),
         @Result(property="citys", column="loc_id", //主表id
                  many=@Many(select="com.dao.UserDao.getAllCitysZ"))  //调用得到城市的方法
        })
 public Locations getLocationsZhujie(int loc_id);



  //通过注解实现多对多关系查询
  //先查找角色
  @Select("select * from tb_jues where j_id in (select j_id from u_j where u_id = #{u_id})")  //u_j为关系表

  @Results({
         @Result(id=true, column="j_id", property="j_id"),
         @Result(column="j_name", property="j_name")
 })
 public List<Tb_jues> gettbjues(int u_id);


 //查找用户
 @Select("select * from tb_user where u_id=#{u_id}")
 @Results({
         @Result(id=true,column="u_id",property="u_id"),
         @Result(column="u_name",property="u_name"),
         @Result(column="u_id",property="tb_jues",many=@Many(select="com.dao.UserDao.gettbjues",fetchType=FetchType.LAZY))  //调用查询角色的方法
 })
 public Tb_user getAllTbUSER(int u_id);

然后在mybatis-config.xml文件<mappers>标签中加入

<mappers>
    <!--<mapper resource="com/entity/User.xml"/>-->

    <!-- 注册UserMapper映射接口-->
    <mapper class="com.dao.UserDao"/>
</mappers>

测试类中在调用方法测试就可以了！

//注解方法 一对一
User user=userDao.getMyUserAndCardById_zhujie(1);
System.out.println(user);
System.out.println(user.getCard().getCnum());


//注解方法 一对多
Locations locations=userDao.getLocationsZhujie(2);
System.out.println(locations);
for (Citys citys : locations.getCitys()) {
    System.out.println(citys);
}


//注解方法 多对多
Tb_user tb_user=userDao.getAllTbUSER(1);
System.out.println(tb_user);
for (Tb_jues tb_jues : tb_user.getTb_jues()) {
    System.out.println(tb_jues);
}