MyBatis的查询关联映射-根据一查询多

MyBatis的查询关联映射-根据一查询多

学习要点

一对多，根据一查询多，然后进行关联映射

一对多，根据一查询多，然后进行关联映射

场景说明：根据一个部门查询出一个部门中的所有员工信息。

代码举例：
1.项目基本机构

2.创建用户表，部门表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) DEFAULT NULL COMMENT '用户名',
  `password` varchar(32) DEFAULT NULL COMMENT '用户密码',
  `deptid` int(11) DEFAULT NULL COMMENT '部门id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门id',
  `deptname` varchar(50) DEFAULT NULL COMMENT '部门名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

3.创建用户实体类(User)

package com.pkk.entity;

/**
 * @author peikunkun
 * @version V1.0
 * @Title: MyBatisProject
 * @Package com.pkk.entity
 * @Description: <User>
 * @date 2017/12/31 19:31
 */
public class User {

    private Integer id;
    private String username;
    private String password;

    public User() {
    }

    public User(String username, String password) {
        this.username = username;
        this.password = password;
    }

    public User(Integer id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

    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 String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

4.创建部门实体类(Dept )

package com.pkk.entity;

import java.util.List;

/**
 * Created by peikunkun on 2018/1/24 0024.
 * 《部门表》
 */
public class Dept {

    private Integer id;
    private String deptname;

    /*因为一个部门对应多个员工，所以用集合来接收多个用户*/
    private List<User> users;

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }

    public Integer getId() {
        return id;
    }

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

    public String getDeptname() {
        return deptname;
    }

    public void setDeptname(String deptname) {
        this.deptname = deptname;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "id=" + id +
                ", deptname='" + deptname + '\'' +
                ", users=" + users +
                '}';
    }
}

5.配置文件的配置(userMapper3.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.pkk.test.mappertest.userMapper3">


    <resultMap id="deptAndUser" type="com.pkk.entity.Dept">
        <id column="id" property="id"/>
        <result column="deptname" property="deptname"/>
        <!--property表示部门实体中的user集合属性名，column数据库的部门id（后续将依据此进行查询用户部门信息为此id的信息，ofType:集合的实体类型）-->
        <!--javaType概属性返回类型，select调用哪个查询-->
        <collection property="users" column="id" ofType="com.pkk.entity.User" javaType="ArrayList"
                    select="selectUserByDeptId">
            <!--<id column="id" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>-->
        </collection>
    </resultMap>


    <select id="selectUserByDeptId" resultType="com.pkk.entity.User">
        SELECT * from USER where deptid=#{id}
    </select>

    <select id="selectDeptAndUserByDeptId" parameterType="int" resultMap="deptAndUser">
        SELECT * from dept where id=#{id}
    </select>

</mapper>

6.主配置文件的配置（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>


    <!--指定MyBatis所用的日志具体实现-->
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>


    <!--起别名，别名的作用就是用简单明代替全限定类名-->
    <typeAliases>
        <!--
        通过package, 可以直接指定package的名字， mybatis会自动扫描你指定包下面的javabean,
        并且默认设置一个别名，默认的名字为： javabean 的首字母小写的非限定类名来作为它的别名。
        也可在javabean 加上注解@Alias 来自定义别名， 例如： @Alias(user)
        <package name="com.pkk.entity"/>
         -->
        <typeAlias alias="user" type="com.pkk.entity.User"/>
    </typeAliases>


    <!--环境配置，即连接的数据库-->
    <environments default="mysql">
        <environment id="mysql">
            <!--使用jdbc的事务提交与回滚设置-->
            <transactionManager type="JDBC"/>
            <!--配置数据源，POOLED是JDBC连接对象的数据源连接池的实现-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="username" value="***"/>
                <property name="password" value="***"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis"/>
            </dataSource>
        </environment>
    </environments>
    <!--mapper告诉了MyBatis去哪里找持久化的映射文件-->
    <mappers>
        <!--多表关联查询-->
        <mapper resource="com.pkk.test/mappertest/userMapper3.xml"></mapper>

    </mappers>

</configuration>

7.测试类(select)

package com.pkk.test.mappertest;

import com.alibaba.fastjson.JSONObject;
import com.pkk.entity.User;
import com.pkk.entity.User1;
import com.pkk.test.TestUtil;

import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by peikunkun on 2018/1/21 0021.
 */
public class select {
    private SqlSession session = null;

    public static void main(String[] args) {
        System.out.println("欢迎使用MyBatis的查询操作");
        JSONObject jsonObject = new JSONObject();
        String s = "{}";
        System.out.println(jsonObject.toString().equals(s));

    }

    @Before
    public void before() {
        session = TestUtil.getSqlSession();
    }
    /**
     * @param
     * @return void
     * @Description: <查询多个表----关联查询>
     * @author peikunkun
     * @date 2017年2018/1/22 17:32
     * @version V1.0
     */
    @Test
    public void select6() {
        List<Dept> depts = session.selectList("com.pkk.test.mappertest.userMapper3.selectDeptAndUserByDeptId", 1);
        System.out.println(depts);

    }


    @After
    public void end() {
        TestUtil.commitAndCloseSession(session);
    }

}

8.工具类（TestUtil）

package com.pkk.test;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

/**
 * Created by peikunkun on 2018/1/21 0021.
 */
public class TestUtil {


    /**
     * 获取连接
     *
     * @return
     */
    public static SqlSession getSqlSession() {
       /*获取当前mybatis配置文件*/
        InputStream inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("mybatis-config.xml");
        /*创建sqlSessionFactory对象*/
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        /*创建SQLSession对象操作持久层对象*/
        SqlSession session = sqlSessionFactory.openSession();

        return session;
    }

    /**
     * 关闭连接和进行提交
     *
     * @return
     */
    public static void commitAndCloseSession(SqlSession session) {
        if (session != null) {
            session.commit();
            session.close();
        }
    }

}

9.代码解析

针对collection进行讲解：
1.property表示部门实体中的user集合属性名
2.column数据库的部门id（后续将依据此进行查询用户部门信息为此id的信息）
3.ofType:集合的实体类型
4.javaType概属性返回类型
5.select调用哪个查询