Mybatis之多表查询

原创

爱撒谎的男孩

修改于 2018-05-10 18:36:56

2.4K3

修改于 2018-05-10 18:36:56

文章被收录于专栏：码猿技术专栏码猿技术专栏

Mybatis之多表查询

本人独立博客https://chenjiabing666.github.io/

`一对一`的查询(`<assocation>`)

创建Java类

创建User类(用户类)

public class User implements Serializable {
    private static final long serialVersionUID = 6716332190979093860L;
    private Integer id;
    private String username;
    private String password;
    private Integer age;
    private Department department;   //Department对象
    

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((age == null) ? 0 : age.hashCode());
        result = prime * result
                + ((department == null) ? 0 : department.hashCode());
        result = prime * result + ((id == null) ? 0 : id.hashCode());
        result = prime * result
                + ((password == null) ? 0 : password.hashCode());
        result = prime * result
                + ((username == null) ? 0 : username.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        User other = (User) obj;
        if (age == null) {
            if (other.age != null)
                return false;
        } else if (!age.equals(other.age))
            return false;
        if (department == null) {
            if (other.department != null)
                return false;
        } else if (!department.equals(other.department))
            return false;
        if (id == null) {
            if (other.id != null)
                return false;
        } else if (!id.equals(other.id))
            return false;
        if (password == null) {
            if (other.password != null)
                return false;
        } else if (!password.equals(other.password))
            return false;
        if (username == null) {
            if (other.username != null)
                return false;
        } else if (!username.equals(other.username))
            return false;
        return true;
    }

    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", password="
                + password + ", age=" + age + ", department=" + department
                + "]";
    }
}

创建Department类(部门类)

public class Department {
    private Integer id;
    private String name;

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

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((id == null) ? 0 : id.hashCode());
        result = prime * result + ((name == null) ? 0 : name.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Department other = (Department) obj;
        if (id == null) {
            if (other.id != null)
                return false;
        } else if (!id.equals(other.id))
            return false;
        if (name == null) {
            if (other.name != null)
                return false;
        } else if (!name.equals(other.name))
            return false;
        return true;
    }

    @Override
    public String toString() {
        return "Department [id=" + id + ", name=" + name + "]";
    }
}

分析

我们知道一个用户只能属于一个部门，因此这里在User类中只是使用了Department对象，而不是个集合
那么我们想要查询所有的用户信息和其所在的部门信息，此时的sql语句为:select * from user u left join department d on u.department_id=d.id;。但是我们在mybaits中如果使用这条语句查询，那么返回的结果类型是什么呢？如果是User类型的，那么查询结果返回的还有Department类型的数据，那么肯定会对应不上的。
我们可以使用resultMap解决对应问题

实现

使用resultMap解决查询结果的对应问题
- 这里一定要在resultMap将每一个字段和查询结果返回的字段对应上，否则此时的结果就为null

<!-- 定义resultMap -->
<resultMap type="cn.tedu.spring.entity.User" id="UserDepartment">
    <!-- 配置id的对应 -->
    <id column="id" property="id"/>
    
    <!-- 配置其他字段的对应关系
        column: 查询结果中的列名字，如果没有起别名，那么就是表中的字段名
        property: java类中的属性名称
     -->
    <result column="password" property="password"/>
    <result column="age" property="age"/>
    <result column="username" property="username"/>
    
    <!-- 配置对1的数据类型，即User类中的Department对象是单一的具体类型
        property: 这个是在User类中的字段名称
        javaType: 这个是java类的全名，是Department类的全名
     -->
    <association property="department" javaType="cn.tedu.spring.entity.Department">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
    </association>
    
</resultMap>

<!-- 
    User findUserAndDepartment();
    resultMap: 指定上面resultMap的id的值
 -->
 <select id="findUserAndDepartment" resultMap="UserDepartment">
    select *  from user u left join department d on u.department_id=d.id
 </select>

测试方法

@Test
    public void testFindUserAndDepartment() {
        //加载Spring的配置文件
        AbstractApplicationContext ac
            = new ClassPathXmlApplicationContext(
                "spring-mvc.xml",
                "spring-dao.xml");
        
        //获取UserMapper的bean，这个是spring通过扫描mapper.xml文件自动为mybatis自动创建的，首字母小写
        UserMapper userMapper
            = ac.getBean(
                "userMapper", UserMapper.class);
        List<User> users=userMapper.findUserAndDepartment();
        for (User user : users) {
            System.out.println(user);
        }
        ac.close();
    }

`一对多`的查询(`<collection>`)

一个宿舍可以住多个学生，那么我们通过宿舍查询学生，那么就是一对多查询

创建Java

创建Student类

public class Student implements Serializable {
    private static final long serialVersionUID = 8673238196042278929L;
    private Integer id;
    private String name;
    private Integer age;
    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 Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((age == null) ? 0 : age.hashCode());
        result = prime * result + ((id == null) ? 0 : id.hashCode());
        result = prime * result + ((name == null) ? 0 : name.hashCode());
        return result;
    }
    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Student other = (Student) obj;
        if (age == null) {
            if (other.age != null)
                return false;
        } else if (!age.equals(other.age))
            return false;
        if (id == null) {
            if (other.id != null)
                return false;
        } else if (!id.equals(other.id))
            return false;
        if (name == null) {
            if (other.name != null)
                return false;
        } else if (!name.equals(other.name))
            return false;
        return true;
    }
    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
    }
    
}

创建Dormitory类
- 其中有一个Set<Student>集合用来存储学生对象

public class Dormitory implements Serializable{
    private static final long serialVersionUID = 1359749532219773083L;
    private Integer id;
    private String number;  //编号
    private Set<Student> students;  //学生集合，一个宿舍可以住多个学生
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getNumber() {
        return number;
    }
    public void setNumber(String number) {
        this.number = number;
    }
    public Set<Student> getStudents() {
        return students;
    }
    public void setStudents(Set<Student> students) {
        this.students = students;
    }
    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((id == null) ? 0 : id.hashCode());
        result = prime * result + ((number == null) ? 0 : number.hashCode());
        result = prime * result
                + ((students == null) ? 0 : students.hashCode());
        return result;
    }
    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Dormitory other = (Dormitory) obj;
        if (id == null) {
            if (other.id != null)
                return false;
        } else if (!id.equals(other.id))
            return false;
        if (number == null) {
            if (other.number != null)
                return false;
        } else if (!number.equals(other.number))
            return false;
        if (students == null) {
            if (other.students != null)
                return false;
        } else if (!students.equals(other.students))
            return false;
        return true;
    }
    @Override
    public String toString() {
        return "Dormitory [id=" + id + ", number=" + number + ", students="
                + students + "]";
    }
}

创建表

student

create table student(id int primary key auto_increment,name varchar(10) not null unique,age int,dormitory_id int);

dormitory

create table dormitory(id int primary key auto_increment,number varchar(20) not null unique);

创建DormitoryMapper.java(接口)

public interface DormitoryMapper {
    //查询所有的宿舍信息
    List<Dormitory> findDormitories();
    //根据id查询宿舍信息
    Dormitory findDormitory(Integer id);
}

创建DormitoryMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"      
 "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<!-- MyBatis的接口映射文件，根节点是mapper -->
<!-- 接口映射文件是与Java接口文件(interface)相对应的 -->
<!-- 根节点的namespace属性用于指定Java接口文件 -->
<mapper namespace="cn.tedu.spring.mapper.DormitoryMapper">
    
    <!-- 定义resultMap
     -->
    <resultMap type="cn.tedu.spring.entity.Dormitory" id="DormitoryStudentRs">
        <id column="id" property="id"/>
        <result column="number" property="number"/>
        
        <!-- 因为Dormitoy中的Student使用set集合存储的，因此这里使用collection标签
            property ： Java类中的集合对象
            ofType： 集合对象的泛型类型
         -->
        <collection property="students" ofType="cn.tedu.spring.entity.Student">
            
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="age" property="age"/>
        </collection>
    </resultMap>
    
    
    <!--    List<Dormitory> findDormitories();
            查询所有的宿舍信息
            resultMap： 指定前面定义的resultMap
     -->
     
     <select id="findDormitories" resultMap="DormitoryStudentRs">
        select * from student s left join dormitory d on s.dormitory_id=d.id
     </select>
     
     <!-- 
        Dormitory findDormitory(Integer id);
      -->
      <select id="findDormitory" resultType="cn.tedu.spring.entity.Dormitory">
        select * from dormitory where id=#{id}
      </select>
</mapper>

在`spring`配置文件中添加`DormitoryMapper.xml`文件

    <!-- 配置SqlSessionFactoryBean -->
    <bean class="org.mybatis.spring.SqlSessionFactoryBean">
        <!-- 配置数据源：如何连接数据库等 -->
        <property name="dataSource"
            ref="dataSource" />
        <!-- 配置XML文件的位置,其中的值是一个数组 -->
        <property name="mapperLocations">
                <array> 
                    <value>classpath:mappers/UserMapper.xml</value>
                    <value>classpath:mappers/DormitoryMapper.xml</value>
                </array>
            </property>
    </bean>

测试

    @Test
    public void testFindDormitory() {
        //加载Spring的配置文件
        AbstractApplicationContext ac
            = new ClassPathXmlApplicationContext(
                "spring-mvc.xml",
                "spring-dao.xml");
        //获取DormitoryMapper对象
        DormitoryMapper dormitoryMapper=ac.getBean("dormitoryMapper",DormitoryMapper.class);
        
        //执行查询方法
        List<Dormitory> dormitories=dormitoryMapper.findDormitories();
        for (Dormitory dormitory : dormitories) {
            System.out.println(dormitory);
        }
        
        ac.close();
    }

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mybatis

github

数据库

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mybatis

github

数据库

登录后参与评论

0 条评论

热度

Mybatis之多表查询

Mybatis之多表查询

Mybatis之多表查询

`一对一`的查询(`<assocation>`)

创建Java类

分析

实现

`一对多`的查询(`<collection>`)

创建Java

创建表

创建DormitoryMapper.java(接口)

创建DormitoryMapper.xml

在`spring`配置文件中添加`DormitoryMapper.xml`文件

测试

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

Mybatis之多表查询

Mybatis之多表查询

Mybatis之多表查询

一对一的查询(<assocation>)

创建Java类

分析

实现

一对多的查询(<collection>)

创建Java

创建表

创建DormitoryMapper.java(接口)

创建DormitoryMapper.xml

在spring配置文件中添加DormitoryMapper.xml文件

测试

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

`一对一`的查询(`<assocation>`)

`一对多`的查询(`<collection>`)

在`spring`配置文件中添加`DormitoryMapper.xml`文件