MyBatis 多表联合查询及优化以及自定义返回结果集

2023-07-19 14:34:46

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

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

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

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

<span style="font-family:Comic Sans MS;font-size:12px;">package com.sica.domain;

import java.io.Serializable;

import java.util.List;

public class User implements Serializable {

    private String id;

    private String username;

    private String password;

    private List<Role> roles;

    private static final long serialVersionUID = 1L;

    public String getId() {

        return id;

    }

    public void setId(String id) {

        this.id = id == null ? null : id.trim();

    }

    public String getUsername() {

        return username;

    }

    public void setUsername(String username) {

        this.username = username == null ? null : username.trim();

    }

    public String getPassword() {

        return password;

    }

    public void setPassword(String password) {

        this.password = password == null ? null : password.trim();

    }

    public List<Role> getRoles() {

        return roles;

    }

    public void setRoles(List<Role> roles) {

        this.roles = roles;

    }

    @Override

    public boolean equals(Object that) {

        if (this == that) {

            return true;

        }

        if (that == null) {

            return false;

        }

        if (getClass() != that.getClass()) {

            return false;

        }

        User other = (User) that;

        return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId()))

            && (this.getUsername() == null ? other.getUsername() == null : this.getUsername().equals(other.getUsername()))

            && (this.getPassword() == null ? other.getPassword() == null : this.getPassword().equals(other.getPassword()));

    }

    @Override

    public int hashCode() {

        final int prime = 31;

        int result = 1;

        result = prime * result + ((getId() == null) ? 0 : getId().hashCode());

        result = prime * result + ((getUsername() == null) ? 0 : getUsername().hashCode());

        result = prime * result + ((getPassword() == null) ? 0 : getPassword().hashCode());

        return result;

    }

}</span>

Role

<span style="font-family:Comic Sans MS;font-size:12px;">package com.sica.domain;

import java.io.Serializable;

public class Role implements Serializable {

    private String id;

    private String name;

    private String jsms;

    private String bz;

    private Integer jlzt;

    private String glbm;

    private String userid;

    private static final long serialVersionUID = 1L;

    public String getId() {

        return id;

    }

    public void setId(String id) {

        this.id = id == null ? null : id.trim();

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name == null ? null : name.trim();

    }

    public String getJsms() {

        return jsms;

    }

    public void setJsms(String jsms) {

        this.jsms = jsms == null ? null : jsms.trim();

    }

    public String getBz() {

        return bz;

    }

    public void setBz(String bz) {

        this.bz = bz == null ? null : bz.trim();

    }

    public Integer getJlzt() {

        return jlzt;

    }

    public void setJlzt(Integer jlzt) {

        this.jlzt = jlzt;

    }

    public String getGlbm() {

        return glbm;

    }

    public void setGlbm(String glbm) {

        this.glbm = glbm == null ? null : glbm.trim();

    }

    public String getUserid() {

        return userid;

    }

    public void setUserid(String userid) {

        this.userid = userid == null ? null : userid.trim();

    }

    @Override

    public boolean equals(Object that) {

        if (this == that) {

            return true;

        }

        if (that == null) {

            return false;

        }

        if (getClass() != that.getClass()) {

            return false;

        }

        Role other = (Role) that;

        return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId()))

            && (this.getName() == null ? other.getName() == null : this.getName().equals(other.getName()))

            && (this.getJsms() == null ? other.getJsms() == null : this.getJsms().equals(other.getJsms()))

            && (this.getBz() == null ? other.getBz() == null : this.getBz().equals(other.getBz()))

            && (this.getJlzt() == null ? other.getJlzt() == null : this.getJlzt().equals(other.getJlzt()))

            && (this.getGlbm() == null ? other.getGlbm() == null : this.getGlbm().equals(other.getGlbm()))

            && (this.getUserid() == null ? other.getUserid() == null : this.getUserid().equals(other.getUserid()));

    }

    @Override

    public int hashCode() {

        final int prime = 31;

        int result = 1;

        result = prime * result + ((getId() == null) ? 0 : getId().hashCode());

        result = prime * result + ((getName() == null) ? 0 : getName().hashCode());

        result = prime * result + ((getJsms() == null) ? 0 : getJsms().hashCode());

        result = prime * result + ((getBz() == null) ? 0 : getBz().hashCode());

        result = prime * result + ((getJlzt() == null) ? 0 : getJlzt().hashCode());

        result = prime * result + ((getGlbm() == null) ? 0 : getGlbm().hashCode());

        result = prime * result + ((getUserid() == null) ? 0 : getUserid().hashCode());

        return result;

    }

}</span>

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

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

mapper接口

UserMapper

<span style="font-family:Comic Sans MS;font-size:12px;">package com.sica.mapper;

import com.sica.domain.User;

import java.util.List;

public interface UserMapper {

    int deleteByPrimaryKey(String id);

    int insert(User record);

    int insertSelective(User record);

    User selectByPrimaryKey(String id);

    int updateByPrimaryKeySelective(User record);

    int updateByPrimaryKey(User record);

    List<User> queryForList();

}</span>

mapper xml文件

UserMapper

<span style="font-family:Comic Sans MS;font-size:12px;"><?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.sica.mapper.UserMapper">

    <resultMap id="BaseResultMap" type="com.sica.domain.User">

        <id column="id" property="id" jdbcType="VARCHAR"/>

        <result column="username" property="username" jdbcType="VARCHAR"/>

        <result column="password" property="password" jdbcType="VARCHAR"/>

    </resultMap>

    <resultMap id="queryForListMap" type="com.sica.domain.User">

        <id column="id" property="id" jdbcType="VARCHAR"/>

        <result column="username" property="username" jdbcType="VARCHAR"/>

        <result column="password" property="password" jdbcType="VARCHAR"/>

        <collection property="roles" javaType="java.util.List" ofType="com.sica.domain.Role">

            <id column="r_id" property="id" jdbcType="VARCHAR" />

            <result column="r_name" property="name" jdbcType="VARCHAR" />

            <result column="r_jsms" property="jsms" jdbcType="VARCHAR" />

            <result column="r_bz" property="bz" jdbcType="VARCHAR" />

            <result column="r_jlzt" property="jlzt" jdbcType="INTEGER" />

            <result column="r_glbm" property="glbm" jdbcType="VARCHAR" />

        </collection>

    </resultMap>

    <select id="queryForList" resultMap="queryForListMap">

        SELECT

          u.id,

          u.username,

          u.password,

          r.id r_id,

          r.name r_name,

          r.jsms r_jsms,

          r.bz r_bz,

          r.jlzt r_jlzt,

          r.glbm r_glbm

        FROM

          user u

        LEFT JOIN

          role r

        ON

          u.id = r.userid

    </select>

    <sql id="Base_Column_List">

      id, username, password

    </sql>

    <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String">

        select

        <include refid="Base_Column_List"/>

        from user

        where id = #{id,jdbcType=VARCHAR}

    </select>

    <delete id="deleteByPrimaryKey" parameterType="java.lang.String">

    delete from user

    where id = #{id,jdbcType=VARCHAR}

    </delete>

    <insert id="insert" parameterType="com.sica.domain.User">

    insert into user (id, username, password

      )

    values (#{id,jdbcType=VARCHAR}, #{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}

      )

    </insert>

    <insert id="insertSelective" parameterType="com.sica.domain.User">

        insert into user

        <trim prefix="(" suffix=")" suffixOverrides=",">

            <if test="id != null">

                id,

            </if>

            <if test="username != null">

                username,

            </if>

            <if test="password != null">

                password,

            </if>

        </trim>

        <trim prefix="values (" suffix=")" suffixOverrides=",">

            <if test="id != null">

                #{id,jdbcType=VARCHAR},

            </if>

            <if test="username != null">

                #{username,jdbcType=VARCHAR},

            </if>

            <if test="password != null">

                #{password,jdbcType=VARCHAR},

            </if>

        </trim>

    </insert>

    <update id="updateByPrimaryKeySelective" parameterType="com.sica.domain.User">

        update user

        <set>

            <if test="username != null">

                username = #{username,jdbcType=VARCHAR},

            </if>

            <if test="password != null">

                password = #{password,jdbcType=VARCHAR},

            </if>

        </set>

        where id = #{id,jdbcType=VARCHAR}

    </update>

    <update id="updateByPrimaryKey" parameterType="com.sica.domain.User">

    update user

    set username = #{username,jdbcType=VARCHAR},

      password = #{password,jdbcType=VARCHAR}

    where id = #{id,jdbcType=VARCHAR}

  </update>

</mapper></span>

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

Dao 接口

IUserDao

<span style="font-family:Comic Sans MS;font-size:12px;">package com.sica.dao;

import com.sica.mapper.UserMapper;

/**

 * Created by IntelliJ IDEA.

 * Package: com.sica.dao

 * Name: IUserDao

 * User: xiang.li

 * Date: 2015/5/22

 * Time: 15:25

 * Desc: To change this template use File | Settings | File Templates.

 */

public interface IUserDao extends UserMapper {

}</span>

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

IUserService

<span style="font-family:Comic Sans MS;font-size:12px;">package com.sica.service;

import com.sica.domain.User;

import java.util.List;

/**

 * Created by xiang.li on 2015/1/31.

 */

public interface IUserService {

    /**

     * 根据Id查询用户对象

     * @param id 编号

     * @return 用户对象

     */

    User getUserById(String id);

    /**

     * 根据用户名查询用户对象

     * @return  List

     */

    List<User> queryUserList();

}</span>

UserServiceImpl

<span style="font-family:Comic Sans MS;font-size:12px;">package com.sica.service.impl;

import com.sica.dao.IUserDao;

import com.sica.domain.User;

import com.sica.service.IUserService;

import org.springframework.stereotype.Service;

import javax.annotation.Resource;

import java.util.List;

/**

 * Created by xiang.li on 2015/1/31.

 */

@Service("userService")

public class UserServiceImpl implements IUserService {

    @Resource

    public IUserDao userDao;

    @Override

    public User getUserById(String id) {

        return this.userDao.selectByPrimaryKey(id);

    }

    @Override

    public List<User> queryUserList() {

        return userDao.queryForList();

    }

}</span>

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

<span style="font-family:Comic Sans MS;font-size:12px;"><?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

       xmlns:p="http://www.springframework.org/schema/p"

       xmlns:context="http://www.springframework.org/schema/context"

       xmlns:mvc="http://www.springframework.org/schema/mvc"

       xsi:schemaLocation="http://www.springframework.org/schema/beans

       http://www.springframework.org/schema/beans/spring-beans.xsd

        http://www.springframework.org/schema/context

        http://www.springframework.org/schema/context/spring-context.xsd

        http://www.springframework.org/schema/mvc

        http://www.springframework.org/schema/mvc/spring-mvc.xsd">

    <!-- 自动扫描 -->

    <context:component-scan base-package="com.sica"/>

    <!-- 引入配置文件 -->

    <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"

          p:location="classpath:jdbc.properties"

          />

    <!-- 配置数据库连接池 -->

    <!-- 初始化连接大小 -->

    <!-- 连接池最大数量 -->

    <!-- 连接池最大空闲 -->

    <!-- 连接池最小空闲 -->

    <!-- 获取连接最大等待时间 -->

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"

          p:driverClassName="${jdbc.driver}"

          p:url="${jdbc.url}"

          p:username="${jdbc.username}"

          p:password="${jdbc.password}"

          p:initialSize="${jdbc.initialSize}"

          p:maxActive="${jdbc.maxActive}"

          p:maxIdle="${jdbc.maxIdle}"

          p:minIdle="${jdbc.minIdle}"

          p:maxWait="${jdbc.maxWait}"

          />

    <!-- spring和MyBatis完美整合，不需要mybatis的配置映射文件 -->

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean" lazy-init="default"

          p:dataSource-ref="dataSource"

          p:mapperLocations="classpath:com/sica/mapping/*.xml"

          />

    <!-- DAO接口所在包名，Spring会自动查找其下的类 -->

    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"

          p:basePackage="com.sica.dao"

          p:sqlSessionFactoryBeanName="sqlSessionFactory"

          />

    <!-- (事务管理)transaction manager, use JtaTransactionManager for global tx -->

    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"

          p:dataSource-ref="dataSource"

          />

</beans></span>

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

GetUserTest

<span style="font-family:Comic Sans MS;font-size:12px;">package com.sica.user;

import com.alibaba.fastjson.JSON;

import com.sica.domain.User;

import com.sica.service.IUserService;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.test.context.ContextConfiguration;

import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;

import java.util.List;

/**

 * Created by xiang.li on 2015/2/1.

 */

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration(locations = "classpath:spring-mybatis.xml")

public class GetUserTest {

    private static String UUID = "3";

    @Resource

    private IUserService userService;

    private static Logger logger = LoggerFactory.getLogger(GetUserTest.class);

    @Test

    public void test() {

        User user = userService.getUserById(UUID);

        logger.info(JSON.toJSONString(user));

    }

    /**

     * 测试联合查询

     */

    @Test

    public void test2() {

        List<User> users = userService.queryUserList();

        logger.info(JSON.toJSONString(users));

    }

}</span>

测试结果

MyBatis 多表联合查询及优化以及自定义返回结果集

出自：http://blog.csdn.net/happylee6688/article/details/45967763

码农公寓

测试结果

相关文章