构建一个springboot多表查询的项目 springboot 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: 8080
spring:
  application:
    name: mybatis-assoaction
  datasource:
    url: jdbc:mysql://localhost:3306/assoaction?serverTimezone=UTC
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
  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
 */
@RestController
public 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
 */
@Service
public 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
@Repository
public 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
@Repository
public 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
@Repository
public 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