MyBatis的简单多表关联映射

MyBatis的简单多表关联映射

学习要点

依靠外键多表关联映射查询

依靠外键多表关联映射查询

代码举例

1.创建用户表，部门表

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;

2.创建用户实体类(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;

    private Dept dept;


    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 Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

    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 + '\'' +
                ", dept=" + dept +
                '}';
    }
}

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

package com.pkk.entity;

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

    private Integer id;
    private String deptname;

    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 + '\'' +
                '}';
    }
}

4.配置文件的配置(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="userAndDept" type="com.pkk.entity.User">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <association property="dept" column="deptid" javaType="com.pkk.entity.Dept" select="selectDeptById">
            <!--<id column="id" property="id"/>
            <result property="deptname" column="deptname"/>-->
        </association>
    </resultMap>

    <select id="selectDeptById" resultType="com.pkk.entity.Dept">
      SELECT * from dept where id=#{id}
    </select>

    <select id="selectUserAndDeptByUserId" parameterType="int" resultMap="userAndDept">
        SELECT * from USER where id=#{id}
    </select>

</mapper>

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

6.测试类(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 select5() {
        List<User> user1s = session.selectList("com.pkk.test.mappertest.userMapper3.selectUserAndDeptByUserId", 1);
        System.out.println(user1s.get(0).toString());

    }


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

}

7.工具类（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();
        }
    }

}

8.代码解析

针对于association属性讲解：
1.colum为数据库列，从上述可以看出为外键deptid.
2.property：为实体的属性，上述可以看出是设置的Dept类型的属性名，不是类型，是作为User的一个属性dept(全小写)
3.javaType：表示该属性对应的实体类型
4.select:表示执行一条查询语句，将查询的结果封装到property所代表的实体类中。