phoenix hbase 联表查询 example联表查询

之前的数据库操作都是基于一张表进行操作的，若一次查询涉及到多张表，那该如何进行操作呢？

首先明确联表查询的几个关系，大体可以分为一对一和一对多这两种情况，接下来对这两种情况进行分析：

一、建立表、添加数据、反向生成

建立teacher、classes、student三张表，并添加几条数据用于后续操作

进行反向生成

二、一对一联表查询

一个班级（classes）只有一个班主任（teacher），即一对一的关系

仅仅通过反向生成的文件是无法进行联表查询的，因此需要进行一些改动

package com.pojo;

public class Teacher {
    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 == null ? null : name.trim();
    }
    
    @Override
    public String toString() {
        return "Teacher [id=" + id + ", name=" + name + "]";
    }
}

Teacher类

在Classes类中添加一个teacher的属性，删除多余的teacher_id：

package com.pojo;

public class Classes {
    private Integer classId;
    private String className;
    private Teacher teacher;    //一对一对应的teacher

    public Integer getClassId() {
        return classId;
    }
    public void setClassId(Integer classId) {
        this.classId = classId;
    }
    public String getClassName() {
        return className;
    }
    public void setClassName(String className) {
        this.className = className;
    }
    public Teacher getTeacher() {
        return teacher;
    }
    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }
    
    @Override
    public String toString() {
        return "Classes [classId=" + classId + ", className=" + className
                + ", teacher=" + teacher + "]";
    }
}

Classes类

修改接口及mapper映射文件：此处有2种方法进行查询

在resultMap节点下添加一个association节点，其配置与resultMap类似，该节点可以用于处理“一对一“的类型关系

package com.mapper;

import com.pojo.Teacher;

public interface TeacherMapper {
    
    Teacher selectTeacherById(Integer id);
    
}

TeacherMapper.java

<?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.mapper.TeacherMapper" >
  <resultMap id="BaseResultMap" type="com.pojo.Teacher" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="name" property="name" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, name
  </sql>
  <select id="selectTeacherById" parameterType="java.lang.Integer" resultMap="BaseResultMap">
      select
      <include refid="Base_Column_List"/>
      from teacher where id=#{id}
  </select>
</mapper>

TeacherMapper.xml

package com.mapper;

import com.pojo.Classes;

public interface ClassesMapper {
    //方法一
    Classes selectByClassId1(Integer classId);
    //方法二
    Classes selectByClassId2(Integer classId);
    
}

ClassesMapper.java

<?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.mapper.ClassesMapper" >

  <sql id="Base_Column_List">
      class_id,class_name,teacher_id
  </sql>
  <sql id="classesAndTeacher">
      classes.class_id,classes.class_name,teacher.id,teacher.name
  </sql>
  <!-- 
      方法一：直接嵌套查询
  -->
  <resultMap id="ClassesMap1" type="com.pojo.Classes" >
    <id column="class_id" property="classId" jdbcType="INTEGER" />
    <result column="class_name" property="className" jdbcType="VARCHAR" />
    <association property="teacher" javaType="com.pojo.Teacher">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
    </association>
  </resultMap>
  <select id="selectByClassId1" resultMap="ClassesMap1" parameterType="java.lang.Integer" >
    select
    <include refid="classesAndTeacher"/>
    from classes LEFT JOIN teacher ON classes.teacher_id=teacher.id where classes.class_id=#{classId}
  </select>
  
  <!-- 
      方法二：进行2次查询（可使用懒加载）
  -->
  <resultMap id="ClassesMap2" type="com.pojo.Classes" >
    <id column="class_id" property="classId" jdbcType="INTEGER" />
    <result column="class_name" property="className" jdbcType="VARCHAR" />
    <association property="teacher" javaType="com.pojo.Teacher" column="teacher_id" select="com.mapper.TeacherMapper.selectTeacherById"/>
    <!-- 
        若需设置懒加载，需在mybatis-config.xml中添加下列节点（开启懒加载，关闭急加载）
        <settings>
            开启懒加载
            <setting name="lazyLoadingEnabled" value="true"/>
            关闭急加载
              <setting name="aggressiveLazyLoading" value="false"/>
          </settings>
        并在association节点中添加fetchType属性并设置为lazy，如下 
    -->
    <!-- <association property="teacher" javaType="com.pojo.Teacher" column="teacher_id" select="com.mapper.TeacherMapper.selectTeacherById" fetchType="lazy"/> -->
  </resultMap>
  <select id="selectByClassId2" resultMap="ClassesMap2" parameterType="java.lang.Integer">
      select
      <include refid="Base_Column_List"/>
      from classes where class_id=#{id}
  </select>
</mapper>

ClassesMapper.xml

编写测试类

package com.test;

import java.io.InputStream;

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

import com.mapper.ClassesMapper;
import com.pojo.Classes;

public class TestHasOne {
    
    public static void main(String[] args) throws Exception {
        //mybatis的核心配置文件
        String resource = "mybatis-config.xml";
        //使用MyBatis提供的Resources类加载mybatis的配置文件
        InputStream  is = Resources.getResourceAsStream(resource);
        //构建SqlSession的工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        //开启SqlSession
        SqlSession session = factory.openSession();
        //通过映射接口执行操作
        ClassesMapper mapper = session.getMapper(ClassesMapper.class);
        Classes clazz1 = mapper.selectByClassId1(1);
        System.out.println("方法一：" + clazz1);
        Classes clazz2 = mapper.selectByClassId2(1);
        System.out.println("方法二：" + clazz2);
        session.close();
    }

}

TestHasOne

输出结果如下：

可从日志输出中看出，方法一仅执行了1条查询语句，方法二执行了2条查询语句

三、一对多联表查询

一个班级（classes）中有许多学生（student），即一对多的关系

修改反向生成的文件

删除Student类中多余的cId属性

package com.pojo;

public class Student {
    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 == null ? null : name.trim();
    }
    
    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + "]";
    }
}

Student

在Classes类中添加一个List<Student>类型的students集合

package com.pojo;

import java.util.List;

public class Classes {
    private Integer classId;
    private String className;
    private Teacher teacher;    //一对一对应的teacher
    private List<Student> students;    //一对多对应的students

    public Integer getClassId() {
        return classId;
    }
    public void setClassId(Integer classId) {
        this.classId = classId;
    }
    public String getClassName() {
        return className;
    }
    public void setClassName(String className) {
        this.className = className;
    }
    public Teacher getTeacher() {
        return teacher;
    }
    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }
    public List<Student> getStudents() {
        return students;
    }
    public void setStudents(List<Student> students) {
        this.students = students;
    }
    
    @Override
    public String toString() {
        return "Classes [classId=" + classId + ", className=" + className
                + ", teacher=" + teacher + ", students=" + students + "]";
    }
}

Classes

修改接口及mapper映射文件：

在resultMap节点下添加一个association节点，其配置与resultMap类似，该节点可以用于处理“一对多“的类型关系

package com.mapper;

import com.pojo.Student;

public interface StudentMapper {
    
    Student selectStudentById(Integer cId);
    
}

StudentMapper.java

<?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.mapper.StudentMapper" >
  <resultMap id="BaseResultMap" type="com.pojo.Student" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="name" property="name" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, name
  </sql>
  <select id="selectStudentById" resultMap="BaseResultMap" parameterType="java.lang.Integer">
      select
      <include refid="Base_Column_List"/>
      from student where c_id=#{cId}
  </select>
</mapper>

StudentMapper.xml

package com.mapper;

import com.pojo.Classes;

public interface ClassesMapper {
    //方法一
    Classes selectByClassId1(Integer classId);
    //方法二
    Classes selectByClassId2(Integer classId);
    //一对多查询
    Classes selectByClassId3(Integer classId);
}

ClassesMapper.java

<?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.mapper.ClassesMapper" >

  <sql id="Base_Column_List">
      class_id,class_name,teacher_id
  </sql>
  <sql id="classesAndTeacher">
      classes.class_id,classes.class_name,teacher.id,teacher.name
  </sql>
  <sql id="classesAndStudent">
      classes.class_id,classes.class_name,student.id,student.name
  </sql>
  <!-- 
      方法一：直接嵌套查询
  -->
  <resultMap id="ClassesMap1" type="com.pojo.Classes" >
    <id column="class_id" property="classId" jdbcType="INTEGER" />
    <result column="class_name" property="className" jdbcType="VARCHAR" />
    <association property="teacher" javaType="com.pojo.Teacher">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
    </association>
  </resultMap>
  <select id="selectByClassId1" resultMap="ClassesMap1" parameterType="java.lang.Integer" >
    select
    <include refid="classesAndTeacher"/>
    from classes LEFT JOIN teacher ON classes.teacher_id=teacher.id where classes.class_id=#{classId}
  </select>
  
  <!-- 
      方法二：进行2次查询（可使用懒加载）
  -->
  <resultMap id="ClassesMap2" type="com.pojo.Classes" >
    <id column="class_id" property="classId" jdbcType="INTEGER" />
    <result column="class_name" property="className" jdbcType="VARCHAR" />
    <association property="teacher" javaType="com.pojo.Teacher" column="teacher_id" select="com.mapper.TeacherMapper.selectTeacherById"/>
    <!-- 
        若需设置懒加载，需在mybatis-config.xml中添加下列节点（开启懒加载，关闭急加载）
        <settings>
            开启懒加载
            <setting name="lazyLoadingEnabled" value="true"/>
            关闭急加载
              <setting name="aggressiveLazyLoading" value="false"/>
          </settings>
        并在association节点中添加fetchType属性并设置为lazy，如下 
    -->
    <!-- <association property="teacher" javaType="com.pojo.Teacher" column="teacher_id" select="com.mapper.TeacherMapper.selectTeacherById" fetchType="lazy"/> -->
  </resultMap>
  <select id="selectByClassId2" resultMap="ClassesMap2" parameterType="java.lang.Integer">
      select
      <include refid="Base_Column_List"/>
      from classes where class_id=#{id}
  </select>
  
  <!-- 一对多查询 -->
  <resultMap id="ClassesMap3" type="com.pojo.Classes" >
    <id column="class_id" property="classId" jdbcType="INTEGER" />
    <result column="class_name" property="className" jdbcType="VARCHAR" />
    <association property="teacher" javaType="com.pojo.Teacher" column="teacher_id" select="com.mapper.TeacherMapper.selectTeacherById"/>
    <collection property="students" column="class_id" ofType="com.pojo.Student" select="com.mapper.StudentMapper.selectStudentById"/>
  </resultMap>
  <select id="selectByClassId3" resultMap="ClassesMap3" parameterType="java.lang.Integer">
      select
      <include refid="Base_Column_List"/>
      from classes where class_id=#{id}
  </select>
</mapper>

ClassesMapper.xml

编写测试类

package com.test;

import java.io.InputStream;

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

import com.mapper.ClassesMapper;
import com.pojo.Classes;

public class TestHasMany {
    
    public static void main(String[] args) throws Exception{
        //mybatis的核心配置文件
        String resource = "mybatis-config.xml";
        //使用MyBatis提供的Resources类加载mybatis的配置文件
        InputStream  is = Resources.getResourceAsStream(resource);
        //构建SqlSession的工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        //开启SqlSession
        SqlSession session = factory.openSession();
        //通过映射接口执行操作
        ClassesMapper mapper = session.getMapper(ClassesMapper.class);
        Classes clazz = mapper.selectByClassId3(1);
        System.out.println(clazz);
    }

}

TestHasMany

输出结果如下

Classes [classId=1, className=一年1班, teacher=Teacher [id=2, name=教师B], students=[Student [id=1, name=张三], Student [id=2, name=李四], Student [id=3, name=王五]]]