mybatis的查询效率问题

mybatis的查询效率问题

mybatis

查询效率

1.配置信息

现在有学生和老师两张表老师的Mapper

1.老师Mapper

---

<?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.abc.mapper.TeacherMapper">
    <resultMap type="com.abc.domian.Teacher" id="supervisorResultMap">
        <id property="id" column="oid"/>
        <result property="name" column="name"/>
        <result property="gender" column="gender"/>
        <result property="researchArea" column="research_area"/>
        <result property="title" column="title"/>
        <!-- <collection property="supStudent" resultMap="com.abc.mapper.StudentMapper.StudentResultMap"></collection> -->
        <collection property="supStudent" column="id" select="com.abc.mapper.StudentMapper.getStudentByTeacherId" resultMap="com.abc.mapper.StudentMapper.StudentResultMap"></collection>
    </resultMap>
    <select id="getById" parameterType="int" resultMap="supervisorResultMap">
        select t.id as oid ,t.name,t.gender,t.research_area,t.title,s.id,s.name as s_name,s.gender,s.major,s.grade,s.supervisor_id from student s , teacher t where s.supervisor_id = t.id
        and t.id=#{id}
    </select>
    <select id="getAllTeacher" parameterType="map" resultMap="supervisorResultMap">
        select * from teacher
    </select>
</mapper>

2. 学生mapper

---

<?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.abc.mapper.StudentMapper">
    <resultMap type="com.abc.domian.Student" id="StudentResultMap">
        <id property="id" column="id"/>
        <result property="name" column="s_name"/>
        <result property="gender" column="gender"/>
        <result property="major" column="major"/>
        <result property="grade" column="grade"/>
        <association property="supervisor" resultMap="com.abc.mapper.TeacherMapper.supervisorResultMap">
        </association>
    </resultMap>
    <select id="getById" parameterType="int" resultMap="StudentResultMap">
        select * from student where id = #{id}
    </select>
    <!-- #name 中那么大小写敏感 useGeneratedKeys设置为true是由数据库自动生成逐渐  keyProperty 指定把获得到的主键值注入
    到Student的id属性-->
    <insert id="add" parameterType="com.abc.domian.Student" useGeneratedKeys="true" keyProperty="id">
        insert into student(name,gender,major,grade) values(#{name},#{gender},#{major},#{grade})
    </insert>
    <update id="update" parameterType="com.abc.domian.Student" >
        update student set gender = #{gender} where id= #{id}
    </update>
    <!-- org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.abc.mapper.StudentMapper.delete
            如果id和Mapper不匹配的话会抛出上述的错误
    -->
    <delete id="delete" parameterType="com.abc.domian.Student">
        delete from student where id = #{id}
    </delete>
    
    <select id="getStudnetAndTeacher" parameterType="int"   resultMap="StudentResultMap">
        select * from student s ,teacher t where s.supervisor_id = t.id and s.id = #{id}
    </select>   
    <insert id="addStudent" parameterType="com.abc.domian.Student" useGeneratedKeys="true" keyProperty="id">
        insert into student(name,gender,major,grade,supervisor_id)
        values(#{name},#{gender},#{major},#{grade},#{supervisor.id})
    </insert>
    <select id="getStudentByTeacherId" parameterType="string" resultMap="StudentResultMap">
        select * from student where supervisor_id = #{id}
    </select>
</mapper>

2. 问题

2.1、关联查询时效率的问题---通过老师查找学生getById的形式

配置文件：

2.1.1老师：

<resultMap type="com.abc.domian.Teacher" id="supervisorResultMap">
        <id property="id" column="oid"/>
        ...
        <collection property="supStudent" column="id" select="com.abc.mapper.StudentMapper.getStudentByTeacherId" resultMap="com.abc.mapper.StudentMapper.StudentResultMap"></collection>
    </resultMap>
    <select id="getById" parameterType="int" resultMap="supervisorResultMap">
        select t.id as oid ,t.name,t.gender,t.research_area,t.title,s.id,s.name as s_name,s.gender,s.major,s.grade,s.supervisor_id from student s , teacher t where s.supervisor_id = t.id
        and t.id=#{id}
    </select>

2.1.2 学生

<resultMap type="com.abc.domian.Student" id="StudentResultMap">
        <id property="id" column="id"/>
        ...
        <association property="supervisor" resultMap="com.abc.mapper.TeacherMapper.supervisorResultMap">
        </association>
    </resultMap>

通过上面的配置我们可以看出来；老师查询学生的时候，使用通过select的配置进行查询的，如果我们通过这种方式来查询学生是会导致sql语句的增多；我们查看控制台后，发现输出的内容为：

select t.id as oid ,t.name,t.gender,t.research_area,t.title,s.id,s.name as s_name,s.gender,s.major,s.grade,s.supervisor_id from student s , teacher t where s.supervisor_id = t.id and t.id=? 
select * from student where supervisor_id = ? 
select * from student where supervisor_id = ? 
select * from student where supervisor_id = ? 
select * from student where supervisor_id = ? 

sql语句的个数明显很多，因此不建议通过该方式进行查询；
解决办法：
如果我们将老师的mapper映射文件：

<collection property="supStudent" column="id" select="com.abc.mapper.StudentMapper.getStudentByTeacherId" resultMap="com.abc.mapper.StudentMapper.StudentResultMap"></collection>

修改为：

<collection property="supStudent" resultMap="com.abc.mapper.StudentMapper.StudentResultMap"></collection> 

在进行查询的时候我们发现会只输出一句话；

select t.id as oid ,t.name,t.gender,t.research_area,t.title,s.id,s.name as s_name,s.gender,s.major,s.grade,s.supervisor_id from student s , teacher t where s.supervisor_id = t.id and t.id=? 

在进行关联映射的时候尽量不要使用collection中的select语句，这样会导致查询语句增多的问题；可以自己书写sql语句，让mybatis进行映射

2.2 关联查询别称起到的作用

根据输出的内容我么还可以发现另外一些问题：
1、通过自定义的sql语句进行查询时管理的查询出的对象没有null
2、通过select的配置查询的记录是由null只出现的；

当我们吧select * from teacher这条语句进行替换成自定义的sql时；

<select id="getAllTeacher" parameterType="map" resultMap="supervisorResultMap">
        select t.id as oid ,t.name,t.gender,t.research_area,t.title,s.id,s.name as s_name,s.gender,s.major,s.grade,s.supervisor_id from student s , teacher t where s.supervisor_id = t.id
    </select>

使用Mybatis时，尽量使用一条sql语句去完成查询

2.3 关联是对参数的赋值问题

---

验证关联查询的时候使用的时sql名称还是属性名称,sql语句查询时参数的赋值依据

参数的赋值结果是由parameterType="map"类型决定的如果为对象，那么从属性中获得，如果为map，参数有map中的键决定

2.4 关联是对参数的赋值问题

如果我想通过老师的专业，来查询学士，注意下面两天sql语句的不同：

SELECT s.id, t.id , s.name , t.gender, t.research_area, t.title, t.name, s.gender, s.major, s.grade, s.supervisor_id FROM student s, teacher t WHERE s.supervisor_id = t.id and t.research_area = '1'  

SELECT t.id , s.name , t.gender, t.research_area, t.title, s.id, t.name, s.gender, s.major, s.grade, s.supervisor_id FROM student s, teacher t WHERE s.supervisor_id = t.id and t.research_area = '1' 

数据库中的sql条数为

使用一条sql语句时，注意要返回的结果是谁，如果返回多的一方，请将多的一方id放置到第一列，如果返回的是少的一方，请将少的一方id放置到第一列，
即：根据 resultMap="StudentResultMap" 对象的id放置第一列

2.5 赋值的参数类型有谁决定

由parameterType="map"去决定值

2.6如果使用了关联查询，并且配置了column，查询时参数的设置

使用column活着，column对应的属性或者该属性对应的列 都可以查询出结果集sql

2.7 实体类有相同的字段，查询数据会被覆盖

在查询时，有学生和老师两个对象，但是学生中的name和老师的那么属性是一致的，使用关联对象进行查询的时候，就会遇到学生的name值被覆盖给了老师如图：

查询到的老师的姓名都是学生的，这个时候有两种解决办法：
a、 通过修改数据库，将学生和老师的两个字段都进行修改；让他们的字段不一样，这样映射也就不会有错误；
b、另外一种方式是不修改数据库的字段，修改映射文件中的result

    <id column="tea_id" property="id" jdbcType="INTEGER" />
   <result column="tea_name" property="name" jdbcType="VARCHAR" />
   <result column="tea_gender" property="gender" jdbcType="VARCHAR" />
   <result column="tea_research_area" property="researchArea" jdbcType="VARCHAR" />
   <result column="tea_title" property="title" jdbcType="VARCHAR" />

以及

 <sql id="Base_Column_List" >
   id as tea_id, name as tea_name, gender as tea_gender, research_area as tea_research_area, title as tea_title
 </sql>

来完成修改，当然查询的sql语句也是需要修改的

  <select id="queryStudentAndTeacher" resultMap="BaseResultMap" useCache="true" >
   SELECT
       s.id,
       s.`name`,
       s.gender,
       s.major,
       s.grade,
       s.supervisor_id ,
       t.id as tea_id,
       t.`name` as tea_name,
       t.gender as tea_gender,
       t.research_area  as tea_research_area,
       t.title  as tea_title
   FROM
       student s
   LEFT JOIN teacher t ON s.supervisor_id = t.id
 </select>

3 资料

/*
Navicat MySQL Data Transfer
Source Server         : localhost_3306
Source Server Version : 50528
Source Host           : localhost:3306
Source Database       : courseman
Target Server Type    : MYSQL
Target Server Version : 50528
File Encoding         : 65001
Date: 2015-04-21 16:54:27
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `major` varchar(255) DEFAULT NULL,
  `grade` varchar(255) DEFAULT NULL,
  `supervisor_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '李林', '男', '计算机科学与技术', '2011', '1');
INSERT INTO `student` VALUES ('2', '陈明', '男', '软件技术', '2012', '1');
INSERT INTO `student` VALUES ('3', '李林1', '男', '计算机科学与技术', '2011', '2');
INSERT INTO `student` VALUES ('4', '陈明2', '男', '软件技术', '2012', '2');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `research_area` varchar(255) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '陈老师', '男', '计算机', '暂无');
INSERT INTO `teacher` VALUES ('2', '郝老师', '男', '无语', '五');