MP多表关联查询

我们关联user表和product表，两个表如下：

• user表

  

• product表

  

现在我们要关联两个表查询出product的全部信息已经对应的用户名字

先写sql语句

然后创建vo

package com.hyn.mybatisplus.entity;

import lombok.Data;

@Data
public class ProductVo {
    private Integer category;
    private Integer count;
    private String description;
    private Integer userId;
    private String userName;
}

usermapper创建新方法和JPA类似

package com.hyn.mybatisplus.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hyn.mybatisplus.entity.ProductVo;
import com.hyn.mybatisplus.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface UserMapper extends BaseMapper<User> {
    @Select("select p.*,u.name userName from user u ,product p where u.id=p.user_id and u.id = #{id}")
    List<ProductVo> productList(Integer id);
}

最后测试一下

@Test
void product(){
    mapper.productList(13).forEach(System.out::println);
}

​ 可见关联表查询以及封装数据成功，如果vo和表里的字段不对应可以在sql语句中改别名。

版权声明：请勿用于任何商业用途的文章转载，转载请说明出处！

1.dao层接口使用Select注解写SQL：

重点：@Param(“ew”) Wrapper参数是必须，因为${ew.customSqlSegment} 底层其实就是where 条件，所以为了保证Wrapper不为空，service层代码中的Wrapper至少需要有一个条件：1 = 1

@Override
    @Select("select a.code as code , b.name as name , b.barcode as barcode ,  a.ware_code as wareCode , c.name as wareName , a.qty as qty , a.oprice as oprice , a.total as total , " +
            " a.id as id , a.create_by as createBy , a.create_date as createDate , a.update_by as updateBy , a.update_date as updateDate , a.status as status , a.remarks as remarks  " +
            "from sku_stock a , goods b , warehouse c " +
            "${ew.customSqlSegment} and a.code = b.code and a.ware_code = c.code")
    IPage<SkuStock> selectPage(IPage<SkuStock> page, @Param("ew")Wrapper<SkuStock> queryWrapper);

2.service层代码示例：

service父类封装的findPage方法：

/**
     * 封装findPage
     * @param entity
     * @param search  Map中的key：";"为保留关键字，拆分数组，仅支持最大长度2的数组，
     *                下标0：QueryWrapper查询条件中的列名(支持多表关联查询的表别名 + 列名方式，需要dao层接口支持)
     *                下标1： QueryWrapper中不同的查询条件，eq：等于，ge：大于等..... todo：请自行完善Mybatis eq、ne、gt、lt、ge、le等
     *                Map中的value：QueryWrapper需要查询的值
     * @param args  QueryWrapper中order by 排序数组
     * @return
     */
    public IPage<T> findPage(T entity , Map<String , Object> search , String... args){
        long current = 1L;
        long size = 10L;
        if (EmptyUtil.isNoEmpty(ReflexUtil.getFieldValue(entity , "page")) && (long) ReflexUtil.getFieldValue(entity , "page") != 0){
            current = (long) ReflexUtil.getFieldValue(entity , "page");
        }
        if (EmptyUtil.isNoEmpty(ReflexUtil.getFieldValue(entity , "limit")) && (long) ReflexUtil.getFieldValue(entity , "limit") != 0){
            size = (long) ReflexUtil.getFieldValue(entity , "limit");
        }

        QueryWrapper<T> queryWrapper;
        if (EmptyUtil.isNoEmpty(search)){
            queryWrapper = new QueryWrapper<>();
            for (Map.Entry<String , Object> entry:search.entrySet()
                 ) {
                String[] key = entry.getKey().split(";");
                if (key.length > 1){
                    if (key[1].equals("eq")){
                        queryWrapper.eq(key[0] , entry.getValue());
                    }else if (key[1].equals("ge")){
                        queryWrapper.ge(key[0] , entry.getValue());
                    }else if (key[1].equals("lt")){
                        queryWrapper.lt(key[0] , entry.getValue());
                    }
                }else {
                    queryWrapper.like(entry.getKey() , entry.getValue());
                }
            }
        }else {
            queryWrapper = new QueryWrapper<>(entity);
        }
        queryWrapper.orderByAsc(args);
        return super.page(new Page<T>(current , size) , queryWrapper);
    }

service实现类方法：

public IPage<SkuStock> findPage(SkuStock entity, String... args) {
        Map<String , Object> search = null;
        search = new HashedMap();
        search.put("1;eq" , "1");
        if (EmptyUtil.isNoEmpty(entity.getCode())
                || EmptyUtil.isNoEmpty(entity.getWareCode())
        ){
            if (EmptyUtil.isNoEmpty(entity.getCode())){
                search.put("code" , entity.getCode());
            }
            if (EmptyUtil.isNoEmpty(entity.getWareCode())){
                search.put("ware_code" , entity.getWareCode());
            }
        }else {
            long limit = entity.getLimit();
            long page = entity.getPage();
            entity = new SkuStock();
            entity.setLimit(limit);
            entity.setPage(page);
        }
        return super.findPage(entity , search , args);
    }

3.反射工具类：

package org.bluedream.comm.utils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @ClassName ReflexUtil
 * @Description TODO
 * @Author foxsand
 * @Data 2021-06-09 15:17
 * @Version
 */
public class ReflexUtil {

    /**
     * 返回 entity 对象的所有属性，包含父类
     * @param obj
     * @return
     */
    public static List<Field> getObjectFields(Object obj){
        Class clazz = obj.getClass();
        List<Field> fieldList = new ArrayList<>() ;
        while (clazz != null) {//当父类为null的时候说明到达了最上层的父类(Object类).
            fieldList.addAll(Arrays.asList(clazz .getDeclaredFields()));
            clazz = clazz.getSuperclass(); //得到父类,然后赋给自己
        }
        return fieldList;
    }

    public static List<Field> getObjectFields(Class<?> clazz){
        List<Field> fieldList = new ArrayList<>() ;
        while (clazz != null){
            fieldList.addAll(Arrays.asList(clazz .getDeclaredFields()));
            clazz = clazz.getSuperclass(); //得到父类,然后赋给自己
        }
        return fieldList;
    }

    /**
     * 判断 Class entity 是否存在名称为 fieldName 的属性
     * @param fieldName
     * @param entity
     * @return
     */
    public static Boolean isField(String fieldName , Object entity){
        List<Field> fieldList = getObjectFields(entity);
        for (Field f1:fieldList
        ) {
            if (fieldName.equals(f1.getName()))
                return true;
        }
        return false;
    }

    /**
     * 返回 entity 对象中的所有方法，包含父类
     * @param entity
     * @return
     */
    public static List<Method> getObjectMethods(Object entity){
        Class<?> clazz = entity.getClass();
        List<Method> methods = new ArrayList<>();
        while (clazz != null && clazz != Object.class) {//当父类为null的时候说明到达了最上层的父类(Object类).
            methods.addAll(Arrays.asList(clazz .getDeclaredMethods()));
            clazz = clazz.getSuperclass(); //得到父类,然后赋给自己
        }
        return methods;
    }

    public static List<Method> getObjectMethods(Class<?> clazz){
        List<Method> methods = new ArrayList<>();
        while (clazz != null && clazz != Object.class) {//当父类为null的时候说明到达了最上层的父类(Object类).
            methods.addAll(Arrays.asList(clazz .getDeclaredMethods()));
            clazz = clazz.getSuperclass(); //得到父类,然后赋给自己
        }
        return methods;
    }

    /**
     * 判断 Class entity 是否存在名称为 methodName 的方法
     * @param methodName
     * @param entity
     * @return
     */
    public static Boolean isMethod(String methodName , Object entity){
        List<Method> methods = getObjectMethods(entity);
        for (Method m1:methods
        ) {
            if (methodName.equals(m1.getName()))
                return true;
        }
        return false;
    }

    /**
     * 循环向上转型, 获取对象的 DeclaredMethod
     * @param obj
     * @param methodName
     * @param parameterTypes  方法参数类型
     * @return
     */
    public static Method getDeclaredMethod(Object obj , String methodName , Class<?>...parameterTypes) {
        for (Class<?> clazz = obj.getClass(); clazz != Object.class && clazz != null; clazz = clazz.getSuperclass()) {
            try {
                return clazz.getDeclaredMethod(methodName, parameterTypes);
            } catch (Exception e) {
                // 这里甚么都不要做！并且这里的异常必须这样写，不能抛出去。
                // 如果这里的异常打印或者往外抛，则就不会执行clazz=clazz.getSuperclass(),最后就不会进入到父类中了
            }
        }
        return null;
    }

    public static Object invoke(Object object, String methodName, Class<?>[] parameterTypes,
                                Object[] parameters){
        Method method = getDeclaredMethod(object, methodName, parameterTypes);
        try {
            if (method != null){
                method.setAccessible(true);
                // 调用object 的 method 所代表的方法，其方法的参数是 parameters
                return method.invoke(object, parameters);
            }
        }catch (Exception e1){
            e1.printStackTrace();
        }
        return null;
    }

    /**
     * 循环向上转型, 获取对象的 DeclaredField
     *
     * @param object
     *            : 子类对象
     * @param fieldName
     *            : 父类中的属性名
     * @return 父类中的属性对象
     */

    public static Field getDeclaredField(Object object, String fieldName) {
        Field field = null;

        Class<?> clazz = object.getClass();

        for (; clazz != Object.class && clazz != null; clazz = clazz.getSuperclass()) {
            try {
                field = clazz.getDeclaredField(fieldName);
                return field;
            } catch (Exception e) {
                // 这里甚么都不要做！并且这里的异常必须这样写，不能抛出去。
                // 如果这里的异常打印或者往外抛，则就不会执行clazz = clazz.getSuperclass(),最后就不会进入到父类中了

            }
        }

        return null;
    }

    /**
     * 直接设置对象属性值, 忽略 private/protected 修饰符, 也不经过 setter
     *
     * @param object
     *            : 子类对象
     * @param fieldName
     *            : 父类中的属性名
     * @param value
     *            : 将要设置的值
     */

    public static void setFieldValue(Object object, String fieldName, Object value) {

        // 根据 对象和属性名通过反射 调用上面的方法获取 Field对象
        Field field = getDeclaredField(object, fieldName);

        if (field != null){
            // 抑制Java对其的检查
            field.setAccessible(true);
            try {
                // 将 object 中 field 所代表的值 设置为 value
                field.set(object, value);
            } catch (IllegalArgumentException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 直接读取对象的属性值, 忽略 private/protected 修饰符, 也不经过 getter
     *
     * @param object
     *            : 子类对象
     * @param fieldName
     *            : 父类中的属性名
     * @return : 父类中的属性值
     */

    public static Object getFieldValue(Object object, String fieldName) {
        // 根据 对象和属性名通过反射 调用上面的方法获取 Field对象
        Field field = getDeclaredField(object, fieldName);

        if (field != null){
            // 抑制Java对其的检查
            field.setAccessible(true);
            try {
                // 获取 object 中 field 所代表的属性值
                return field.get(object);

            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }


}

4.判空工具类：

package org.bluedream.comm.utils;

import java.util.Collection;
import java.util.Map;

public class EmptyUtil {
    //Suppress default constructor for noninstantiability
    private EmptyUtil(){
        throw new AssertionError();
    }

    public static boolean isEmpty(Object object){
        if (object == null){
            return true;
        }
        if (object instanceof int[]){
            return ((int[]) object).length == 0;
        }
        if (object instanceof double[]){
            return ((double[]) object).length == 0;
        }
        if (object instanceof long[]){
            return ((long[]) object).length == 0;
        }
        if (object instanceof byte[]){
            return ((byte[]) object).length == 0;
        }
        if (object instanceof short[]){
            return ((short[]) object).length == 0;
        }
        if (object instanceof float[]){
            return ((float[]) object).length == 0;
        }
        if (object instanceof char[]){
            return ((char[]) object).length == 0;
        }
        if (object instanceof Object[]){
            return ((Object[]) object).length == 0;
        }
        if (object instanceof CharSequence) {
            return ((CharSequence) object).length() == 0;
        }
        if (object instanceof Collection ){
            return ((Collection) object).isEmpty();
        }
        if (object instanceof Map){
            return ((Map) object).isEmpty();
        }
        return false;
    }

    public static boolean isNoEmpty(Object object){
        return !isEmpty(object);
    }

}

demo：https://download.csdn.net/download/caidingnu/12064636

1、目录结构

2、Controller

package com.example.mybatisplustest.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.mybatisplustest.entity.UniData;
import com.example.mybatisplustest.entity.User;
import com.example.mybatisplustest.mapper.PageDataMapper;
import com.example.mybatisplustest.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.HashMap;
import java.util.Map;

/**
 * desc： plus 分页方式
 * author CDN
 * create 2019-12-27 21:37
 * version 1.0.0
 */
@RestController
public class PageDataController {
    @Autowired
    PageDataMapper pageDataMapper;

    @Autowired
    UserMapper userMapper;

    /**
     * desc:  注解方式（关联分页查询）
     * param:
     * return:
     * author: CDN
     * date: 2019/12/27
     */
    @RequestMapping("/annotationsPage")
    public Object annotationsPage() {
        IPage<UniData> page=new Page<>(1, 2);
        Map map=new HashMap();
        map.put("userId", 1);
        IPage<UniData> all = pageDataMapper.annotationsPage(page,map);
        return all;
    }


    /**
     * desc:  provider （关联分页查询）
     * param:
     * return:
     * author: CDN
     * date: 2019/12/27
     */
    @RequestMapping("/providerPage")
    public Object providerPage() {
        IPage<UniData> page=new Page<>(1, 2);
        Map map=new HashMap();
        map.put("userId", 1);
        IPage<UniData> all = pageDataMapper.providerPage(page,map);
        return all;
    }


    /**
     * desc:  xml方式（关联分页查询）
     * param:
     * return:
     * author: CDN
     * date: 2019/12/27
     */
    @RequestMapping("/xmlPage")
    public Object xmlPage() {
        IPage<UniData> page=new Page<>(1, 2);
        Map map=new HashMap();
        map.put("userId", 1);
        IPage<UniData> all = pageDataMapper.xmlPage(page,map);
        return all;
    }


    /**
     * desc:  常规方式(单表 user表)
     * param:
     * return:
     * author: CDN
     * date: 2019/12/27
     */
    @RequestMapping("/commonPage")
    public Object commonPage() {
        IPage<User> page=new Page<>(1, 2);
        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.eq("user_id", 1);
        IPage<User> all = userMapper.selectPage(page,queryWrapper);
        return all;
    }


}

3、UniData 实体

package com.example.mybatisplustest.entity;

/**
 * desc：
 * author CDN
 * create 2019-12-27 21:10
 * version 1.0.0
 */
public class UniData {

    private String name;

    private String password;

    private String nickName;

    private Integer age;

    private Integer roleId;

    private String role;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getNickName() {
        return nickName;
    }

    public void setNickName(String nickName) {
        this.nickName = nickName;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getRoleId() {
        return roleId;
    }

    public void setRoleId(Integer roleId) {
        this.roleId = roleId;
    }

    public String getRole() {
        return role;
    }

    public void setRole(String role) {
        this.role = role;
    }
}

4、Mapper

package com.example.mybatisplustest.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.mybatisplustest.entity.UniData;
import com.example.mybatisplustest.mapper.provider.UniDataProvider;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.Map;

/**
 * desc:
 * author caidingnu
 * create 2019/12/27
 * version 1.0.0
 */
public interface PageDataMapper {

    /**
     * 注解方式分页
     *
     * @param page
     * @param map
     * @return
     */
    @Select("SELECT user.name,user.password,USER.nick_name as nickName,USER.age,role.role_id as roleId,role.role " +
            "FROM user INNER JOIN role ON `user`.user_id = role.role_id where user.user_id = #{map.userId}")
    IPage<UniData> annotationsPage(IPage<UniData> page, @Param("map") Map map);

    /**
     * provider 方式分页
     *   ☆☆☆☆☆☆☆ 重点： page必须放在第一个参数，否则无法分页或者报错 ☆☆☆☆☆☆☆☆
     * @param page
     * @param map
     * @return
     */
    @SelectProvider(type = UniDataProvider.class, method = "providerPage")
    IPage<UniData> providerPage(IPage<UniData> page, @Param("map") Map map);

    /**
     *  xml 方式分页
     * @param page
     * @param map
     * @return
     */
    IPage<UniData> xmlPage(IPage<UniData> page, @Param("map") Map map);
}

5、UniDataProvider

package com.example.mybatisplustest.mapper.provider;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.example.mybatisplustest.entity.UniData;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;

import java.util.Map;

/**
 * desc：
 * author CDN
 * create 2019-12-27 22:20
 * version 1.0.0
 */
public class UniDataProvider {


    /**
     * desc:
     * param:
     * return:
     * author: CDN
     * date: 2019/12/27
     */
    public String providerPage(Map map) {
        SQL sql = new SQL();
        sql.SELECT("user.name,user.password,USER.nick_name as nickName,USER.age,role.role_id as roleId,role.role " +
                "FROM user INNER JOIN role ON `user`.user_id = role.role_id where user.user_id = #{map.userId}");
        return sql.toString();
    }
}

6、PageDataMapper

<?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.example.mybatisplustest.mapper.PageDataMapper">

   <select id="xmlPage" resultType="com.example.mybatisplustest.entity.UniData">
       SELECT user.name,user.password,USER.nick_name as nickName,USER.age,role.role_id as roleId,role.role " +
            "FROM user INNER JOIN role ON `user`.user_id = role.role_id where user.user_id = #{map.userId}
   </select>

<!--  实体类传参也要精确到属性   user.name   -->

</mapper>