多表查询

whyczh

2021-04-15 28e7d24ad81f9afe1037b72d8cfc3c27ba948f28

多表查询

 src/main/java/com/example/lxw/controller/User_infController.java

@@ -33,4 +33,17 @@
        return model;
    }

    @PutMapping("searchByBaojiUsr2")
    @ApiOperation(value = "查询所有用户的包机组信息2")
    public ServiceModel searchByBaojiUsr2(){
        ServiceModel model=user_infService.searchByBaojiUsr2();
        return model;
    }
    @PutMapping("searchByBaojiUsr3")
    @ApiOperation(value = "查询所有用户的包机组信息3")
    public ServiceModel searchByBaojiUsr3(){
        ServiceModel model=user_infService.searchByBaojiUsr3();
        return model;
    }

}

 src/main/java/com/example/lxw/mapper/User_InfMapper.java

@@ -2,11 +2,14 @@

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.lxw.pojo.User_inf;
import com.example.lxw.pojo.vo.UserInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @author lxw
@@ -19,4 +22,15 @@
     * @return
     */
    public List<User_inf> searchByBaojiUsr();

    /**
     * 返回所有的用户
     * @return
     */

    public List<UserInfo> searchByBaojiUsr2();

    @Select("SELECT tb_user_inf.*,tb_user_battgroup_baojigroup_usr.baoji_group_id from tb_user_inf LEFT JOIN tb_user_battgroup_baojigroup_usr ON tb_user_inf.uId=tb_user_battgroup_baojigroup_usr.uId  order by tb_user_inf.uid")
    public List<Map<String,Object>> searchByBaojiUsr3();

}

 src/main/java/com/example/lxw/pojo/vo/UserInfo.java

New file
@@ -0,0 +1,21 @@
package com.example.lxw.pojo.vo;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.example.lxw.pojo.UserBattgroupBaojigroupUsr;
import com.example.lxw.pojo.User_inf;
import lombok.Data;

import java.util.List;

/**
 * @author 张辉
 * @since 2021/4/15
 */
@Data
@TableName(schema = "db_user",value = "tb_user_inf")
public class UserInfo extends User_inf {
    @TableField(exist = false)
    private List<UserBattgroupBaojigroupUsr> usrList;

}

 src/main/java/com/example/lxw/service/User_infService.java

@@ -3,11 +3,13 @@
import com.example.lxw.mapper.User_InfMapper;
import com.example.lxw.pojo.ServiceModel;
import com.example.lxw.pojo.User_inf;
import com.example.lxw.pojo.vo.UserInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

/**
 * @author lxw
@@ -40,5 +42,23 @@
        model.setData(list);
        return model;
    }
    /**
     * 查询所有用户的包机组信息
     * */
    public  ServiceModel searchByBaojiUsr2(){
        List<UserInfo> list=user_infMapper.searchByBaojiUsr2();
        ServiceModel model=new ServiceModel();
        model.setCode(1);
        model.setData(list);
        return model;
    }

    public  ServiceModel searchByBaojiUsr3(){
        List<Map<String,Object>> list=user_infMapper.searchByBaojiUsr3();
        ServiceModel model=new ServiceModel();
        model.setCode(1);
        model.setData(list);
        return model;
    }

}

 src/main/resources/mapper/User_infMapper.xml

@@ -10,10 +10,25 @@
        </collection>
    </resultMap>

    <resultMap id="baojiUserMap2" type="UserInfo">
        <result column="uSnId" property="uSnId" />
        <result column="uName" property="uName" />
        <collection property="usrList" ofType="UserBattgroupBaojigroupUsr">
            <result column="baoji_group_id" property="baojiGroupId" />
        </collection>
    </resultMap>

    <select id="searchByBaojiUsr" resultMap="baojiUserMap">
        SELECT tb_user_inf.*,tb_user_battgroup_baojigroup_usr.baoji_group_id
        from tb_user_inf
        LEFT JOIN tb_user_battgroup_baojigroup_usr ON tb_user_inf.uId=tb_user_battgroup_baojigroup_usr.uId
        order by tb_user_inf.uid
    </select>

    <select id="searchByBaojiUsr2" resultMap="baojiUserMap2">
        SELECT tb_user_inf.*,tb_user_battgroup_baojigroup_usr.baoji_group_id
        from tb_user_inf
                 LEFT JOIN tb_user_battgroup_baojigroup_usr ON tb_user_inf.uId=tb_user_battgroup_baojigroup_usr.uId
        order by tb_user_inf.uid
    </select>
</mapper>