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', '郝老师', '男', '无语', '五');