专栏首页后端Coderjava进阶|MyBatis系列文章(七)多表查询操作

java进阶|MyBatis系列文章(七)多表查询操作

一,MyBatis框架介绍

MyBatis是一款支持动态sql的半自动化sql的ORM框架,在企业中应用的很多,相比较于Hibernate,JdbcTemplate这样的框架,它有着自己独特的好处所以这篇还是自己去整理一下关于MyBatis框架在支持多表关联操作的编写。

二,项目依赖的jar包信息

          <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-web</artifactId>        </dependency>        <dependency>            <groupId>org.mybatis.spring.boot</groupId>            <artifactId>mybatis-spring-boot-starter</artifactId>            <version>2.1.2</version>        </dependency>
        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <scope>runtime</scope>        </dependency>        <dependency>            <groupId>org.projectlombok</groupId>            <artifactId>lombok</artifactId>            <optional>true</optional>        </dependency>

以上jar包依赖主要是mysql连接,支持mybatis操作以及简化get/set方法的lombok包。

三,连接数据库配置文件的信息

server:  port: 8080spring:  application:    name: mybatis-assoaction  datasource:    url: jdbc:mysql://localhost:3306/assoaction?serverTimezone=UTC    username: root    password: root    driver-class-name: com.mysql.cj.jdbc.Drivermybatis:  mapper-locations: classpath:/mapper/**.xml

四,便于用postman工具测试的UserController类相关的代码如下,这里进行了分层逻辑的拆分。

package com.wpw.mybatisassoaction.controller;
import com.wpw.mybatisassoaction.entity.User;import com.wpw.mybatisassoaction.service.UserService;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.bind.annotation.RestController;
/** * @author wpw */@RestControllerpublic class UserController {    private final UserService userService;
    public UserController(UserService userService) {        this.userService = userService;    }
    @GetMapping(value = "/get")    public User getUserByUserId(@RequestParam(value = "userId") Integer userId) {        return userService.getUserByUserId(userId);    }
    @GetMapping(value = "/getUserWithCompany")    public User getUserWithCompany(@RequestParam(value = "userId") Integer userId) {        return userService.getUserWithCompany(userId);    }
    @GetMapping(value = "/getUserWithAccountList")    public User getUserWithAccountList(@RequestParam(value = "userId") Integer userId) {        return userService.getUserWithAccountList(userId);    }
    @GetMapping(value = "/optionUser")    public User optionUser(@RequestParam(value = "userId") Integer userId) {        return userService.optionUser(userId);    }
    @GetMapping(value = "/selectOne")    public User selectOne(@RequestParam(value = "userId") Integer userId) {        return userService.selectOne(userId);    }}

五,业务逻辑处理类相关的代码如下,这里都针对方法名做了见名知意的处理,所以本篇文章就不会很多去介绍是如何实现的。

package com.wpw.mybatisassoaction.service;
import com.wpw.mybatisassoaction.entity.User;
/** * @author pc */public interface UserService {    /**     * 根据用户id查询用户信息     *     * @param userId 用户id     * @return 用户信息     */    User getUserByUserId(Integer userId);
    /**     * 根据用户id获取用户信息     *     * @param userId 用户id     * @return 用户信息,不含有用户账户信息     */    User getUserWithCompany(Integer userId);
    /**     * 根据用户id获取用户信息     *     * @param userId 用户id     * @return 用户信息,不含有公司信息     */    User getUserWithAccountList(Integer userId);
    /**     * 根据用户id获取用户信息     * @param userId 用户id     * @return 用户信息     */    User optionUser(Integer userId);
    /**     * 根据用户id获取用户信息     * @param userId 用户id     * @return 用户信息     */    User selectOne(Integer userId);}

实现类的逻辑代码如下

package com.wpw.mybatisassoaction.service.impl;
import com.wpw.mybatisassoaction.entity.User;import com.wpw.mybatisassoaction.mapper.UserMapper;import com.wpw.mybatisassoaction.service.UserService;import org.springframework.stereotype.Service;
/** * @author wpw */@Servicepublic class UserServiceImpl implements UserService {    private final UserMapper userMapper;
    public UserServiceImpl(UserMapper userMapper) {        this.userMapper = userMapper;    }
    @Override    public User getUserByUserId(Integer userId) {        return userMapper.getUserByUserId(userId);    }
    @Override    public User getUserWithCompany(Integer userId) {        return userMapper.getUserWithCompany(userId);    }
    @Override    public User getUserWithAccountList(Integer userId) {        return userMapper.getUserWithAccountList(userId);    }
    @Override    public User optionUser(Integer userId) {        return userMapper.optionUser(userId);    }
    @Override    public User selectOne(Integer userId) {        return  userMapper.selectOne(userId);    }}

六,这里主要是和数据库进行操作的UserMapper接口和UserMapper.xml文件代码的编写。

package com.wpw.mybatisassoaction.mapper;
import com.wpw.mybatisassoaction.entity.Account;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Result;import org.apache.ibatis.annotations.Results;import org.apache.ibatis.annotations.Select;import org.springframework.stereotype.Repository;
import java.util.List;
/** * @author wpw */@Mapper@Repositorypublic interface AccountMapper {
    /**     * 根据用户id查找当前用户下的账户信息     *     * @param userId 用户id     * @return 账户列表信息     */    @Select(value = "select * from account where user_id=#{userId}")    @Results({            @Result(property = "id", column = "id"),            @Result(property = "accountName", column = "name")    })    List<Account> listAccountByUserId(Integer userId);}
package com.wpw.mybatisassoaction.mapper;
import com.wpw.mybatisassoaction.entity.Company;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Result;import org.apache.ibatis.annotations.Results;import org.apache.ibatis.annotations.Select;import org.springframework.stereotype.Repository;
/** * @author wpw */@Mapper@Repositorypublic interface CompanyMapper {    /**     * 根据公司id查询公司信息     *     * @param companyId 公司id     * @return 公司信息     */    @Select(value = "select * from company where id=#{companyId}")    @Results({            @Result(property = "id", column = "id"),            @Result(property = "companyName", column = "name")    })    Company getByCompanyId(Integer companyId);}
package com.wpw.mybatisassoaction.mapper;
import com.wpw.mybatisassoaction.entity.User;import org.apache.ibatis.annotations.*;import org.springframework.stereotype.Repository;
/** * @author wpw */@Mapper@Repositorypublic interface UserMapper {    /**     * 根据用户id获取用户信息     *     * @param userId 用户id     * @return 用户信息     */    @Select(value = "select * from user where id=#{userId}")    @Results({            @Result(property = "id", column = "id"),            @Result(property = "name", column = "name"),            @Result(property = "company", column = "company_id", one = @One(select = "com.wpw.mybatisassoaction.mapper.CompanyMapper.getByCompanyId")),            @Result(property = "accountList", column = "id", many = @Many(select = "com.wpw.mybatisassoaction.mapper.AccountMapper.listAccountByUserId"))    })    User getUserByUserId(Integer userId);
    /**     * 根据用户id获取用户信息     *     * @param userId 用户id     * @return 用户信息,不含有账户信息     */    @Select(value = "select * from user where id=#{userId}")    @Results({            @Result(property = "id", column = "id"),            @Result(property = "name", column = "name"),            @Result(property = "company", column = "company_id", one = @One(select = "com.wpw.mybatisassoaction.mapper.CompanyMapper.getByCompanyId"))    })    User getUserWithCompany(Integer userId);
    /**     * 根据用户id获取用户信息     *     * @param userId 用户id     * @return 用户信息,不含有公司信息     */    @Select(value = "select * from user where id=#{userId}")    @Results({            @Result(property = "id", column = "id"),            @Result(property = "name", column = "name"),            @Result(property = "accountList", column = "id", many = @Many(select = "com.wpw.mybatisassoaction.mapper.AccountMapper.listAccountByUserId"))    })    User getUserWithAccountList(Integer userId);
    /**     * 根据用户id获取用户信息     *     * @param userId 用户id     * @return 用户信息     */    User optionUser(Integer userId);
    /**     * 根据用户id获取用户信息     *     * @param userId 用户id     * @return 用户信息     */    User selectOne(Integer userId);}

UserMapper.xml文件代码是这篇文章主要的内容,相比较于注解的方式,它还是比较好用的,注解也挺好,不过自己慢慢也喜欢了用xml方式去写对应的示例程序。

<?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,namespace的值习惯上设置成包名+sql映射文件名,这样就能够保证namespace的值是唯一的 --><mapper namespace="com.wpw.mybatisassoaction.mapper.UserMapper">    <resultMap id="map" type="com.wpw.mybatisassoaction.entity.User">        <id property="id" column="id"/>        <result property="name" column="name"/>        <association property="company" column="company_id" select="getCompany">        </association>        <collection property="accountList" column="id" select="listAccountByUserId"/>    </resultMap>    <select id="listAccountByUserId" parameterType="int" resultMap="accountMap">        select *        from account        where user_id = #{id}    </select>    <resultMap id="accountMap" type="com.wpw.mybatisassoaction.entity.Account">        <id property="id" column="id"/>        <result property="accountName" column="name"/>    </resultMap>    <select id="optionUser" parameterType="int" resultMap="map">        select *        from user        where id = #{id}    </select>    <resultMap id="companyMap" type="com.wpw.mybatisassoaction.entity.Company">        <id property="id" column="id"/>        <result property="companyName" column="name"/>    </resultMap>    <select id="getCompany" resultMap="companyMap">        select *        from company        where id = #{id}    </select>    <resultMap id="userMap" type="com.wpw.mybatisassoaction.entity.User">        <id property="id" column="id"/>        <result property="name" column="name"/>        <association property="company" column="company_id" javaType="com.wpw.mybatisassoaction.entity.Company">            <id property="id" column="companyId"/>            <result property="companyName" column="companyName"/>        </association>        <collection property="accountList" column="id"  ofType="com.wpw.mybatisassoaction.entity.Account">            <id property="id" column="accountId"/>            <result property="accountName" column="accountName"/>        </collection>    </resultMap>    <select id="selectOne" parameterType="int" resultMap="userMap">        select u.id, u.name, c.id companyId, c.name companyName, a.id accountId, a.name accountName        from user u                 left join company c on u.company_id = c.id                 left join account a on u.id = a.user_id        where u.id = #{id}    </select></mapper>

七,项目需要的测试基础类

package com.wpw.mybatisassoaction.entity;
import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;import lombok.experimental.Accessors;
import java.io.Serializable;
/** * @author wpw */@AllArgsConstructor@NoArgsConstructor@Data@Builder@Accessors(chain = true)public class Account implements Serializable {    private Integer id;    private String accountName;}
package com.wpw.mybatisassoaction.entity;
import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;import lombok.experimental.Accessors;
import java.io.Serializable;/** * @author wpw */@AllArgsConstructor@NoArgsConstructor@Data@Builder@Accessors(chain = true)public class Company implements Serializable {    private Integer id;    private String companyName;}
package com.wpw.mybatisassoaction.entity;
import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;import lombok.experimental.Accessors;
import java.io.Serializable;import java.util.List;
/** * @author wpw */@AllArgsConstructor@NoArgsConstructor@Data@Builder@Accessors(chain = true)public class User implements Serializable {    private Integer id;    private String name;    private Company company;    private List<Account> accountList;}

八,项目依赖的sql文件信息

create table if not exists `account`(    id      int(11)      not null auto_increment comment '主键',    name    varchar(200) not null comment '账户名称',    user_id int(11)      not null comment '用户id',    primary key (`id`)) engine = InnoDB  DEFAULT CHARSET = utf8;
create table if not exists `company`(    id   int(11)      not null auto_increment comment '主键',    name varchar(200) not null comment '公司名称',    primary key (`id`)) ENGINE = InnoDB  DEFAULT CHARSET = utf8;
create table if not exists `user`(    id         int(11)     not null auto_increment comment '主键',    name       varchar(50) not null comment '姓名',    company_id int(11)     not null comment '公司id',    primary key (`id`)) engine = InnoDB  DEFAULT CHARSET = utf8;
insert into user(name,company_id)values("zhangsan",1);insert into user(name,company_id)values("lisi",2);insert into company(name) values("中国移动");insert into company(name) values("中国联通");insert into account(name,user_id) values("中国农业银行",1);insert into account(name,user_id) values("中国工商银行",1);insert into account(name,user_id) values("中国邮政储蓄银行",2);insert into account(name,user_id) values("中国银行",2);

整个项目的搭建和测试流程就结束了,下面是项目的结构图以及项目代码的结构图

gitHub地址:https://github.com/myownmyway/mybatis-assoaction.git

本文分享自微信公众号 - WwpwW(gh_245290c1861a),作者:后端Coder

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-04-18

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • java进阶|Mybatis系列文章(三)表关联查询操作

    整个项目所需要的xml文件进行数据的增删改查操作ClassesMapper.xml文件

    后端Coder
  • java进阶必备知识点

    好了,我们说完了java反射的机制定义之后,接下来我们还是按照文章的风格继续先看我们的示例程序来一步一步分析。

    后端Coder
  • java进阶|JPA系列教程(一)单表操作

    四,项目的处理器controller,主要用户接收前端传入的参数,这里基于postman测试工具进行测试。

    后端Coder
  • R语言 | 根据数据框的顺序进行筛选

    这里有两个数据框,两者有相同的列(ID),这里想把第一个数据框,按照第二个数据框的ID列进行提取,顺序和第二个数据框一致。

    邓飞
  • 无限级子商户的查询优化方法

    A 有 2 个直接下级B、C,    B有2个直接下级D、E,    C有2个直接下级F、G

    宣言言言
  • 一些sql用法例子【Updating】

    1、利用instr连接表做字段查询,group_concat做值的合并: create table ab(product_id int,product_name...

    用户1177713
  • 面试官问:MySQL的自增ID用完了,怎么办?

    可以发现 AUTO_INCREMENT 已经自动变成2,这离用完还有很远,我们可以算下最大当前声明的自增ID最大是多少,由于这里定义的是 intunsigned...

    用户4143945
  • 分表查询统计的一个具体案例

    问题描述 mysql数据库在数据量较大的情况下,对数据表进行水平分表,按照年份,如下:

    后端技术探索
  • 分表查询统计的一个具体案例

    问题描述 mysql数据库在数据量较大的情况下,对数据表进行水平分表,按照年份,如下:

    后端技术探索
  • 面试官问:MySQL的自增ID用完了,怎么办?

    可以发现 AUTO_INCREMENT 已经自动变成2,这离用完还有很远,我们可以算下最大当前声明的自增ID最大是多少,由于这里定义的是 intunsigned...

    xcbeyond

扫码关注云+社区

领取腾讯云代金券