MyBatis多表联查

时间:2022-09-28 blanceage 人气:0

一、通过映射配置文件实现多表联查

首先，使用Mysql数据库，创建两个表，分别为学生表Student表和班级表Class表，在Student表中添加列classid参照主表的列id的外键约束。

学生表Student表：

班级表Class表：

现在去写Dao层和实体类

Student实体类：

package com.ape.bean;
 
import java.util.Date;
 
public class Student {
    private Integer sid;
    private String sname;
    private Date birthday;
    private String ssex;
    private int classid;
/*
    一对一
*/
    private Classs banji;
 
    public Student() {
        super();
        // TODO Auto-generated constructor stub
    }
 
    public Student(Integer sid, String sname, Date birthday, String ssex, int classid, Classs banji) {
        this.sid = sid;
        this.sname = sname;
        this.birthday = birthday;
        this.ssex = ssex;
        this.classid = classid;
        this.banji = banji;
    }
 
    public Integer getSid() {
        return sid;
    }
 
    public void setSid(Integer sid) {
        this.sid = sid;
    }
 
    public String getSname() {
        return sname;
    }
 
    public void setSname(String sname) {
        this.sname = sname;
    }
 
    public Date getBirthday() {
        return birthday;
    }
 
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
 
    public String getSsex() {
        return ssex;
    }
 
    public void setSsex(String ssex) {
        this.ssex = ssex;
    }
 
    public int getClassid() {
        return classid;
    }
 
    public void setClassid(int classid) {
        this.classid = classid;
    }
 
    public Classs getBanji() {
        return banji;
    }
 
    public void setBanji(Classs banji) {
        this.banji = banji;
    }
 
    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", sname='" + sname + '\'' +
                ", birthday=" + birthday +
                ", ssex='" + ssex + '\'' +
                ", classid=" + classid +
                ", banji=" + banji +
                '}';
    }
}

Class实体类：

package com.ape.bean;
 
import java.util.List;
 
public class Classs {
    private int classid;
    private String classname;
/*
    一对多
*/
    private List<Student> xuesheng;
 
    public Classs() {
    }
 
    public Classs(int classid, String classname, List<Student> xuesheng) {
        this.classid = classid;
        this.classname = classname;
        this.xuesheng = xuesheng;
    }
 
    public int getClassid() {
        return classid;
    }
 
    public void setClassid(int classid) {
        this.classid = classid;
    }
 
    public String getClassname() {
        return classname;
    }
 
    public void setClassname(String classname) {
        this.classname = classname;
    }
 
    public List<Student> getXuesheng() {
        return xuesheng;
    }
 
    public void setXuesheng(List<Student> xuesheng) {
        this.xuesheng = xuesheng;
    }
 
    @Override
    public String toString() {
        return "Classs{" +
                "classid=" + classid +
                ", classname='" + classname + '\'' +
                ", xuesheng=" + xuesheng +
                '}';
    }
}

Mapper接口：

public interface ClassMapper {
    
    public List<Class> findClass();
}

public interface StudentMapper {
    public List<Student> findStudent();
/*
    一对多
*/
    public List<Student> findduobiao();
}

一对一关系的xml文件配置：

实现一对一的关系查询，即一条student信息对应一条class信息

<resultMap id="stu_class_Map" type="Student">
		<result column="sid" property="sid" />
		<result column="sname" property="sname"/>
		<result column="birthday" property="birthday"/>
		<result column="ssex" property="ssex"/>
		<result column="classid" property="classid"/>
        <!-- 一对一的关系映射，配置封装student的内容 -->
		<association property="banji">
			<result column="classid" property="classid"/>
			<result column="classname" property="classname"/>
		</association>
	</resultMap>
	<select id="findduobiao" resultMap="stu_class_Map">
		select * from student inner join class on student.classid = class.classid;
	</select>

这里主要配置的就是resultMap了，配置javabean类中属性与数据库列名的对应关系，association是用来指定从表方的引用实体属性的。
注意最后写的findstudent方法中，是使用到resultMap作为接收结果值返回，与上文配置的resultMap相对应。

一对多查询xml文件配置：

<resultMap id="class_stu_Map" type="Classs">
		<result column="classid" property="classid"/>
		<result column="classname" property="classname"/>
        <!-- Class中的集合映射 -->
		<collection property="xuesheng">
			<result column="sid" property="sid" />
			<result column="sname" property="sname"/>
			<result column="birthday" property="birthday"/>
			<result column="ssex" property="ssex"/>
			<result column="classid" property="classid"/>
		</collection>
	</resultMap>
	<select id="yiduiduo" resultMap="class_stu_Map">
		select * from class left join student on class.classid = student.classid order by class.classid;
	</select>

虽然知道使用这条sql语句查询后的结果会有重复项，但是不用担心，应为mybatis会自动识别到重复的内容，只保留一个。

二、使用注解的方式

还是同样的我们需要再建实体类，跟上面的一样；其次Mapper接口中的方法需要加注解。

一对一：

public interface AccountDao {
  @Select("select  * from account")
  @Results(id="accountMap",value = {
      @Result(id = true,column = "id",property = "id"),
      @Result(column = "uid",property = "uid"),
      @Result(column = "money",property = "money"),
      @Result(property = "user",column = "uid",one =
      @One(select="mediacomm.dao.UserDao.findUserById",fetchType= FetchType.DEFAULT))
  })
  List<Account> findAccountWithUser();

public interface UserDao {
@Select("select * from user where id=#{id}")
  User findUserById(int id);

一对多：

public interface UserDao {
  @Select(value = "select * from user")
  @Results(id = "userMap",value = {
      @Result(id = true,column = "id",property = "id"),
      @Result(column = "username",property = "username"),
      @Result(column = "password",property = "password"),
      @Result(column = "address",property = "address"),
      @Result(property = "accounts",column = "id",
          many = @Many(select = "mediacomm.dao.AccountDao.findAccountByUid",fetchType = FetchType.DEFAULT))
  })
  List<User> findAllUser();

public interface AccountDao {
 @Select("select * from account where uid=#{uid}")
  List<Account> findAccountByUid(int uid);

加载全部内容