MyBatis 多表联合查询及优化

序

这篇文章我打算来简单的谈谈 mybatis 的多表联合查询。起初是觉得挺简单的，没必要拿出来写，毕竟 mybatis 这东西现在是个开发的都会用，而且网上的文章也是一搜罗一大堆，根本就用不着我来重复。但是吧，就我前几天在做一个多表联合查询的时候，竟然出了很多意想不到的问题，而且这些问题的出现，并不是对 mybatis 不了解，而是在用的过程中会或多或少的忽略一些东西，导致提示各种错误。

背景

老规矩，开始之前，还是要先说说这件事的背景。也就是最近几天，公司要做一个后台的管理平台，由于之前的一些限制，这次要做成单独的项目进行部署，因此就要重新考虑很多东西。索性这几天有时间，就做了一个小 Demo ，实现 mybatis 的多表联合查询的，由于之前用的是 Hibernate 做的联合查询，众所周知，Hibernate 是全自动的数据库持久层框架，它可以通过实体来映射数据库，通过设置一对多、多对一、一对一、多对多的关联来实现联合查询。

正文

下面就来说一下 mybatis 是通过什么来实现多表联合查询的。首先看一下表关系，如图：

这里，我已经搭好了开发的环境，用到的是 SpringMVC + Spring + MyBatis，当然，为了简单期间，你可以不用搭前端的框架，只使用 Spring + MyBatis 就可以，外加 junit 测试即可。环境我就不带大家搭了，这里只说涉及到联合查询的操作。

设计好表之后，我用到了 mybatis 的自动生成工具 mybatis generator 生成的实体类、mapper 接口、以及 mapper xml 文件。由于是测试多表联合查询，因此需要自己稍加改动。

下面是 User 和 Role 的实体类代码：

User

[java] view plain copy

1. <span style="font-family:Comic Sans MS;font-size:12px;">package com.sica.domain;  
2.   
3. import java.io.Serializable;  
4. import java.util.List;  
5.   
6. public class User implements Serializable {  
7.     private String id;  
8.   
9.     private String username;  
10.   
11.     private String password;  
12.   
13.     private List<Role> roles;  
14.   
15.     private static final long serialVersionUID = 1L;  
16.   
17.     public String getId() {  
18.         return id;  
19.     }  
20.   
21.     public void setId(String id) {  
22.         this.id = id == null ? null : id.trim();  
23.     }  
24.   
25.     public String getUsername() {  
26.         return username;  
27.     }  
28.   
29.     public void setUsername(String username) {  
30.         this.username = username == null ? null : username.trim();  
31.     }  
32.   
33.     public String getPassword() {  
34.         return password;  
35.     }  
36.   
37.     public void setPassword(String password) {  
38.         this.password = password == null ? null : password.trim();  
39.     }  
40.   
41.     public List<Role> getRoles() {  
42.         return roles;  
43.     }  
44.   
45.     public void setRoles(List<Role> roles) {  
46.         this.roles = roles;  
47.     }  
48.   
49.     @Override  
50.     public boolean equals(Object that) {  
51.         if (this == that) {  
52.             return true;  
53.         }  
54.         if (that == null) {  
55.             return false;  
56.         }  
57.         if (getClass() != that.getClass()) {  
58.             return false;  
59.         }  
60.         User other = (User) that;  
61.         return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId()))  
62.             && (this.getUsername() == null ? other.getUsername() == null : this.getUsername().equals(other.getUsername()))  
63.             && (this.getPassword() == null ? other.getPassword() == null : this.getPassword().equals(other.getPassword()));  
64.     }  
65.   
66.     @Override  
67.     public int hashCode() {  
68.         final int prime = 31;  
69.         int result = 1;  
70.         result = prime * result + ((getId() == null) ? 0 : getId().hashCode());  
71.         result = prime * result + ((getUsername() == null) ? 0 : getUsername().hashCode());  
72.         result = prime * result + ((getPassword() == null) ? 0 : getPassword().hashCode());  
73.         return result;  
74.     }  
75. }</span>  

Role

[java] view plain copy

1. <span style="font-family:Comic Sans MS;font-size:12px;">package com.sica.domain;  
2.   
3. import java.io.Serializable;  
4.   
5. public class Role implements Serializable {  
6.     private String id;  
7.   
8.     private String name;  
9.   
10.     private String jsms;  
11.   
12.     private String bz;  
13.   
14.     private Integer jlzt;  
15.   
16.     private String glbm;  
17.   
18.     private String userid;  
19.   
20.     private static final long serialVersionUID = 1L;  
21.   
22.     public String getId() {  
23.         return id;  
24.     }  
25.   
26.     public void setId(String id) {  
27.         this.id = id == null ? null : id.trim();  
28.     }  
29.   
30.     public String getName() {  
31.         return name;  
32.     }  
33.   
34.     public void setName(String name) {  
35.         this.name = name == null ? null : name.trim();  
36.     }  
37.   
38.     public String getJsms() {  
39.         return jsms;  
40.     }  
41.   
42.     public void setJsms(String jsms) {  
43.         this.jsms = jsms == null ? null : jsms.trim();  
44.     }  
45.   
46.     public String getBz() {  
47.         return bz;  
48.     }  
49.   
50.     public void setBz(String bz) {  
51.         this.bz = bz == null ? null : bz.trim();  
52.     }  
53.   
54.     public Integer getJlzt() {  
55.         return jlzt;  
56.     }  
57.   
58.     public void setJlzt(Integer jlzt) {  
59.         this.jlzt = jlzt;  
60.     }  
61.   
62.     public String getGlbm() {  
63.         return glbm;  
64.     }  
65.   
66.     public void setGlbm(String glbm) {  
67.         this.glbm = glbm == null ? null : glbm.trim();  
68.     }  
69.   
70.     public String getUserid() {  
71.         return userid;  
72.     }  
73.   
74.     public void setUserid(String userid) {  
75.         this.userid = userid == null ? null : userid.trim();  
76.     }  
77.   
78.     @Override  
79.     public boolean equals(Object that) {  
80.         if (this == that) {  
81.             return true;  
82.         }  
83.         if (that == null) {  
84.             return false;  
85.         }  
86.         if (getClass() != that.getClass()) {  
87.             return false;  
88.         }  
89.         Role other = (Role) that;  
90.         return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId()))  
91.             && (this.getName() == null ? other.getName() == null : this.getName().equals(other.getName()))  
92.             && (this.getJsms() == null ? other.getJsms() == null : this.getJsms().equals(other.getJsms()))  
93.             && (this.getBz() == null ? other.getBz() == null : this.getBz().equals(other.getBz()))  
94.             && (this.getJlzt() == null ? other.getJlzt() == null : this.getJlzt().equals(other.getJlzt()))  
95.             && (this.getGlbm() == null ? other.getGlbm() == null : this.getGlbm().equals(other.getGlbm()))  
96.             && (this.getUserid() == null ? other.getUserid() == null : this.getUserid().equals(other.getUserid()));  
97.     }  
98.   
99.     @Override  
100.     public int hashCode() {  
101.         final int prime = 31;  
102.         int result = 1;  
103.         result = prime * result + ((getId() == null) ? 0 : getId().hashCode());  
104.         result = prime * result + ((getName() == null) ? 0 : getName().hashCode());  
105.         result = prime * result + ((getJsms() == null) ? 0 : getJsms().hashCode());  
106.         result = prime * result + ((getBz() == null) ? 0 : getBz().hashCode());  
107.         result = prime * result + ((getJlzt() == null) ? 0 : getJlzt().hashCode());  
108.         result = prime * result + ((getGlbm() == null) ? 0 : getGlbm().hashCode());  
109.         result = prime * result + ((getUserid() == null) ? 0 : getUserid().hashCode());  
110.         return result;  
111.     }  
112. }</span>  

首先讲一下业务，这里用到的 User 、Role 的对应关系是，一个用户有多个角色，也就是 User ： Role 是 1 ： n 的关系。因此，在 User 的实体中加入一个 Role 的属性，对应一对多的关系。

然后就是 mapper 接口和 xml 文件了：

mapper接口

UserMapper

[java] view plain copy

1. <span style="font-family:Comic Sans MS;font-size:12px;">package com.sica.mapper;  
2.   
3. import com.sica.domain.User;  
4.   
5. import java.util.List;  
6.   
7. public interface UserMapper {  
8.     int deleteByPrimaryKey(String id);  
9.   
10.     int insert(User record);  
11.   
12.     int insertSelective(User record);  
13.   
14.     User selectByPrimaryKey(String id);  
15.   
16.     int updateByPrimaryKeySelective(User record);  
17.   
18.     int updateByPrimaryKey(User record);  
19.   
20.     List<User> queryForList();  
21. }</span>  

mapper xml文件

UserMapper

[html] view plain copy

1. <span style="font-family:Comic Sans MS;font-size:12px;"><?xml version="1.0" encoding="UTF-8" ?>  
2. <!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "http:///dtd/mybatis-3-mapper.dtd" >  
3. <mapper namespace="com.sica.mapper.UserMapper">  
4.     <resultMap id="BaseResultMap" type="com.sica.domain.User">  
5.         <id column="id" property="id" jdbcType="VARCHAR"/>  
6.         <result column="username" property="username" jdbcType="VARCHAR"/>  
7.         <result column="password" property="password" jdbcType="VARCHAR"/>  
8.     </resultMap>  
9.   
10.     <resultMap id="queryForListMap" type="com.sica.domain.User">  
11.         <id column="id" property="id" jdbcType="VARCHAR"/>  
12.         <result column="username" property="username" jdbcType="VARCHAR"/>  
13.         <result column="password" property="password" jdbcType="VARCHAR"/>  
14.         <collection property="roles" javaType="java.util.List" ofType="com.sica.domain.Role">  
15.             <id column="r_id" property="id" jdbcType="VARCHAR" />  
16.             <result column="r_name" property="name" jdbcType="VARCHAR" />  
17.             <result column="r_jsms" property="jsms" jdbcType="VARCHAR" />  
18.             <result column="r_bz" property="bz" jdbcType="VARCHAR" />  
19.             <result column="r_jlzt" property="jlzt" jdbcType="INTEGER" />  
20.             <result column="r_glbm" property="glbm" jdbcType="VARCHAR" />  
21.         </collection>  
22.     </resultMap>  
23.     <select id="queryForList" resultMap="queryForListMap">  
24.         SELECT  
25.           u.id,  
26.           u.username,  
27.           u.password,  
28.           r.id r_id,  
29.           r.name r_name,  
30.           r.jsms r_jsms,  
31.           r.bz r_bz,  
32.           r.jlzt r_jlzt,  
33.           r.glbm r_glbm  
34.         FROM  
35.           user u  
36.         LEFT JOIN  
37.           role r  
38.         ON  
39.           u.id = r.userid  
40.     </select>  
41.     <sql id="Base_Column_List">  
42.       id, username, password  
43.     </sql>  
44.     <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String">  
45.         select  
46.         <include refid="Base_Column_List"/>  
47.         from user  
48.         where id = #{id,jdbcType=VARCHAR}  
49.     </select>  
50.     <delete id="deleteByPrimaryKey" parameterType="java.lang.String">  
51.     delete from user  
52.     where id = #{id,jdbcType=VARCHAR}  
53.     </delete>  
54.     <insert id="insert" parameterType="com.sica.domain.User">  
55.     insert into user (id, username, password  
56.       )  
57.     values (#{id,jdbcType=VARCHAR}, #{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}  
58.       )  
59.     </insert>  
60.     <insert id="insertSelective" parameterType="com.sica.domain.User">  
61.         insert into user  
62.         <trim prefix="(" suffix=")" suffixOverrides=",">  
63.             <if test="id != null">  
64.                 id,  
65.             </if>  
66.             <if test="username != null">  
67.                 username,  
68.             </if>  
69.             <if test="password != null">  
70.                 password,  
71.             </if>  
72.         </trim>  
73.         <trim prefix="values (" suffix=")" suffixOverrides=",">  
74.             <if test="id != null">  
75.                 #{id,jdbcType=VARCHAR},  
76.             </if>  
77.             <if test="username != null">  
78.                 #{username,jdbcType=VARCHAR},  
79.             </if>  
80.             <if test="password != null">  
81.                 #{password,jdbcType=VARCHAR},  
82.             </if>  
83.         </trim>  
84.     </insert>  
85.     <update id="updateByPrimaryKeySelective" parameterType="com.sica.domain.User">  
86.         update user  
87.         <set>  
88.             <if test="username != null">  
89.                 username = #{username,jdbcType=VARCHAR},  
90.             </if>  
91.             <if test="password != null">  
92.                 password = #{password,jdbcType=VARCHAR},  
93.             </if>  
94.         </set>  
95.         where id = #{id,jdbcType=VARCHAR}  
96.     </update>  
97.     <update id="updateByPrimaryKey" parameterType="com.sica.domain.User">  
98.     update user  
99.     set username = #{username,jdbcType=VARCHAR},  
100.       password = #{password,jdbcType=VARCHAR}  
101.     where id = #{id,jdbcType=VARCHAR}  
102.   </update>  
103. </mapper></span>  

之后，我扩展了一个 Dao 接口，当然，你也可以直接使用 mapper 接口，都是一样的。

Dao 接口

IUserDao

[java] view plain copy

1. <span style="font-family:Comic Sans MS;font-size:12px;">package com.sica.dao;  
2.   
3. import com.sica.mapper.UserMapper;  
4.   
5. /** 
6.  * Created by IntelliJ IDEA. 
7.  * Package: com.sica.dao 
8.  * Name: IUserDao 
9.  * User: xiang.li 
10.  * Date: 2015/5/22 
11.  * Time: 15:25 
12.  * Desc: To change this template use File | Settings | File Templates. 
13.  */  
14. public interface IUserDao extends UserMapper {  
15.   
16. }</span>  

下面就是 service 和实现层的代码了。

IUserService

[java] view plain copy

1. <span style="font-family:Comic Sans MS;font-size:12px;">package com.sica.service;  
2.   
3. import com.sica.domain.User;  
4.   
5. import java.util.List;  
6.   
7. /** 
8.  * Created by xiang.li on 2015/1/31. 
9.  */  
10. public interface IUserService {  
11.   
12.     /** 
13.      * 根据Id查询用户对象 
14.      * @param id 编号 
15.      * @return 用户对象 
16.      */  
17.     User getUserById(String id);  
18.   
19.     /** 
20.      * 根据用户名查询用户对象 
21.      * @return  List 
22.      */  
23.     List<User> queryUserList();  
24. }</span>  

UserServiceImpl

[java] view plain copy

1. <span style="font-family:Comic Sans MS;font-size:12px;">package com.sica.service.impl;  
2.   
3. import com.sica.dao.IUserDao;  
4. import com.sica.domain.User;  
5. import com.sica.service.IUserService;  
6. import org.springframework.stereotype.Service;  
7.   
8. import javax.annotation.Resource;  
9. import java.util.List;  
10.   
11. /** 
12.  * Created by xiang.li on 2015/1/31. 
13.  */  
14. @Service("userService")  
15. public class UserServiceImpl implements IUserService {  
16.   
17.     @Resource  
18.     public IUserDao userDao;  
19.   
20.     @Override  
21.     public User getUserById(String id) {  
22.         return this.userDao.selectByPrimaryKey(id);  
23.     }  
24.   
25.     @Override  
26.     public List<User> queryUserList() {  
27.         return userDao.queryForList();  
28.     }  
29.   
30. }</span>  

当然，还有所谓的 applicationContext.xml 配置，不过，我这里叫 spring-mybatis.xml。

[html] view plain copy

1. <span style="font-family:Comic Sans MS;font-size:12px;"><?xml version="1.0" encoding="UTF-8"?>  
2. <beans xmlns="http://www./schema/beans"  
3.        xmlns:xsi="http://www./2001/XMLSchema-instance"  
4.        xmlns:p="http://www./schema/p"  
5.        xmlns:context="http://www./schema/context"  
6.        xmlns:mvc="http://www./schema/mvc"  
7.        xsi:schemaLocation="http://www./schema/beans  
8.        http://www./schema/beans/spring-beans.xsd  
9.         http://www./schema/context  
10.         http://www./schema/context/spring-context.xsd  
11.         http://www./schema/mvc  
12.         http://www./schema/mvc/spring-mvc.xsd">  
13.   
14.     <!-- 自动扫描 -->  
15.     <context:component-scan base-package="com.sica"/>  
16.     <!-- 引入配置文件 -->  
17.     <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"  
18.           p:location="classpath:jdbc.properties"  
19.           />  
20.   
21.     <!-- 配置数据库连接池 -->  
22.     <!-- 初始化连接大小 -->  
23.     <!-- 连接池最大数量 -->  
24.     <!-- 连接池最大空闲 -->  
25.     <!-- 连接池最小空闲 -->  
26.     <!-- 获取连接最大等待时间 -->  
27.     <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"  
28.           p:driverClassName="${jdbc.driver}"  
29.           p:url="${jdbc.url}"  
30.           p:username="${jdbc.username}"  
31.           p:password="${jdbc.password}"  
32.           p:initialSize="${jdbc.initialSize}"  
33.           p:maxActive="${jdbc.maxActive}"  
34.           p:maxIdle="${jdbc.maxIdle}"  
35.           p:minIdle="${jdbc.minIdle}"  
36.           p:maxWait="${jdbc.maxWait}"  
37.           />  
38.   
39.     <!-- spring和MyBatis完美整合，不需要mybatis的配置映射文件 -->  
40.     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean" lazy-init="default"  
41.           p:dataSource-ref="dataSource"  
42.           p:mapperLocations="classpath:com/sica/mapping/*.xml"  
43.           />  
44.   
45.     <!-- DAO接口所在包名，Spring会自动查找其下的类 -->  
46.     <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"  
47.           p:basePackage="com.sica.dao"  
48.           p:sqlSessionFactoryBeanName="sqlSessionFactory"  
49.           />  
50.   
51.     <!-- (事务管理)transaction manager, use JtaTransactionManager for global tx -->  
52.     <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"  
53.           p:dataSource-ref="dataSource"  
54.           />  
55. </beans></span>  

最后，我用到的是 junit 进行的测试，测试代码如下。

GetUserTest

[java] view plain copy

1. <span style="font-family:Comic Sans MS;font-size:12px;">package com.sica.user;  
2.   
3. import com.alibaba.fastjson.JSON;  
4. import com.sica.domain.User;  
5. import com.sica.service.IUserService;  
6. import org.junit.Test;  
7. import org.junit.runner.RunWith;  
8. import org.slf4j.Logger;  
9. import org.slf4j.LoggerFactory;  
10. import org.springframework.test.context.ContextConfiguration;  
11. import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;  
12.   
13. import javax.annotation.Resource;  
14. import java.util.List;  
15.   
16. /** 
17.  * Created by xiang.li on 2015/2/1. 
18.  */  
19. @RunWith(SpringJUnit4ClassRunner.class)  
20. @ContextConfiguration(locations = "classpath:spring-mybatis.xml")  
21. public class GetUserTest {  
22.   
23.     private static String UUID = "3";  
24.     @Resource  
25.     private IUserService userService;  
26.     private static Logger logger = LoggerFactory.getLogger(GetUserTest.class);  
27.   
28.     @Test  
29.     public void test() {  
30.         User user = userService.getUserById(UUID);  
31.         logger.info(JSON.toJSONString(user));  
32.     }  
33.   
34.     /** 
35.      * 测试联合查询 
36.      */  
37.     @Test  
38.     public void test2() {  
39.         List<User> users = userService.queryUserList();  
40.         logger.info(JSON.toJSONString(users));  
41.     }  
42. }</span>  

测试结果

可以看到，所有的用户和用户对应的角色都全部查出来了，这说明，这次的测试很成功。

关于优化

对于优化嘛，我这里简单的提几点，大家可以考虑一下。首先，就是对表的设计，在设计表初期，不仅仅要考虑到数据库的规范性，还好考虑到所谓的业务，以及对性能的影响，比如，如果从规范性角度考虑的话，可能就会分多个表，但是如果从性能角度来考虑的话，庞大的数据量在多表联合查询的时候，相对于单表来说，就会慢很多，这时，如果字段不是很多的话，可以考虑冗余几个字段采用单表的设计。

其次嘛，就是在 sql 上下功夫了，对于联合查询，sql 的优化是很有必要的，到底是采用 INNER JOIN，还是采用 LEFT JOIN 亦或是 RIGHT JOIN 、OUTTER JOIN 等，都是要在满足业务需求之后，通过测试性能得出的结论。

再次嘛，就是在程序中调用的时候了，是采用懒加载，还是采用非懒加载的方式，这也算是一个因素吧，具体的还是要考虑业务的需要。

最后嘛，就要用到数据库的缓存了，或者在数据库与程序的中间再加一层缓存。不过，还是建议用好数据库本身自带的缓存功能。

结束语

最后的最后，还是要提醒大家，不要因为觉得简单就引不起足够的重视，否则你会由于一点点小的失误，而浪费大把大把的时间的。我就在这上边体会过，所以，这里奉劝大家，还是小心为妙啊@！