扫一扫下方二维码,关注本站官方公众号
获取永久解锁本站全部文章的验证码
还能不定期领现金红包

Springboot+mybatis+MySQL实现简单的多表查询-Go语言中文社区

Springboot+mybatis+MySQL实现简单的多表查询


Springboot+mybatis+MySQL实现简单的多表查询

直接进入正题
1.首先我们新建一个数据库,再建两个表

tbl_employee表建表SQL语句

CREATE TABLE `tbl_employee` (
  `id` int(200) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `d_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept` (`d_id`),
  CONSTRAINT `fk_emp_dept` FOREIGN KEY (`d_id`) REFERENCES `tbl_dept` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

tbl_dept表建表SQL语句

CREATE TABLE `tbl_dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

表建好之后,我们填入一些数据。如下:
在这里插入图片描述
在这里插入图片描述
图中画红圈的为关联部分,将两个表进行关联。
在这里插入图片描述
2.在平时我们需要分别查询每一个表的信息。
这里我们的需求是,我要查询这个人的名字顺便查到这个人属于哪个部门,这就需要多表联查了。
3.我们还用SpringBoot+mybatis创建一个项目。
4.代码如下:
Controller层

@RestController
public class EmployeeController {
    @Autowired
    private EmployeeService employeeService;
    @RequestMapping(value = "/selectEmployee",method = RequestMethod.POST,headers = "Accept=application/json")
    public HttpResponseEntity selectEmployee(@RequestBody Employee employee){
        HttpResponseEntity httpResponseEntity = new HttpResponseEntity();
        try {
            Employee selectEmployee = employeeService.selectEmployee(employee);
            httpResponseEntity.setCode(Constans.SUCCESS_CODE);
            httpResponseEntity.setMessage(Constans.SELECT_EXIST_SUCCESS);
            httpResponseEntity.setData(selectEmployee);
        }catch (Exception e){
            httpResponseEntity.setCode(Constans.ADD_EXIST_CODE);
            httpResponseEntity.setMessage(Constans.SELECT_EXIST_MESSAGE);
        }
        return httpResponseEntity;
    }

Service层

@Service
public class EmployeeService {
    @Autowired
    private EmployeeMapper employeeMapper;
    @Transactional
    public Employee selectEmployee(Employee employee) {
        Employee selectEmployee = employeeMapper.selectEmployee(employee.getId());
        return selectEmployee;
    }


Mapper

Employee selectEmployee(Integer id);

5.创建两个实体类,代码如下:
Department

package com.springboot.dao.entity;

public class Department {
    private Integer id;
    private String departmentName;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", departmentName='" + departmentName + ''' +
                '}';
    }
}

Employee

package com.springboot.dao.entity;

public class Employee {
    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column tbl_employee.id
     *
     * @mbg.generated
     */
    private Integer id;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column tbl_employee.last_name
     *
     * @mbg.generated
     */
    private String lastName;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column tbl_employee.email
     *
     * @mbg.generated
     */
    private String email;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column tbl_employee.gender
     *
     * @mbg.generated
     */
    private String gender;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column tbl_employee.d_id
     *
     * @mbg.generated
     */
    private Integer dId;

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column tbl_employee.id
     *
     * @return the value of tbl_employee.id
     *
     * @mbg.generated
     */

    private Department dept;

    public Department getDept() {
        return dept;
    }

    public void setDept(Department dept) {
        this.dept = dept;
    }

    public Integer getId() {
        return id;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column tbl_employee.id
     *
     * @param id the value for tbl_employee.id
     *
     * @mbg.generated
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column tbl_employee.last_name
     *
     * @return the value of tbl_employee.last_name
     *
     * @mbg.generated
     */
    public String getLastName() {
        return lastName;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column tbl_employee.last_name
     *
     * @param lastName the value for tbl_employee.last_name
     *
     * @mbg.generated
     */
    public void setLastName(String lastName) {
        this.lastName = lastName == null ? null : lastName.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column tbl_employee.email
     *
     * @return the value of tbl_employee.email
     *
     * @mbg.generated
     */
    public String getEmail() {
        return email;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column tbl_employee.email
     *
     * @param email the value for tbl_employee.email
     *
     * @mbg.generated
     */
    public void setEmail(String email) {
        this.email = email == null ? null : email.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column tbl_employee.gender
     *
     * @return the value of tbl_employee.gender
     *
     * @mbg.generated
     */
    public String getGender() {
        return gender;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column tbl_employee.gender
     *
     * @param gender the value for tbl_employee.gender
     *
     * @mbg.generated
     */
    public void setGender(String gender) {
        this.gender = gender == null ? null : gender.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column tbl_employee.d_id
     *
     * @return the value of tbl_employee.d_id
     *
     * @mbg.generated
     */
    public Integer getdId() {
        return dId;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column tbl_employee.d_id
     *
     * @param dId the value for tbl_employee.d_id
     *
     * @mbg.generated
     */
    public void setdId(Integer dId) {
        this.dId = dId;
    }
}

6.重新再xml配置文件中定义映射

<resultMap id="MyDifEmp" type="com.springboot.dao.entity.Employee">
<id column="id" property="id"/>
  <result column="last_name" property="lastName"/>
  <result column="gender" property="gender"/>
  <result column="d_id"  property="dId" />
  <result column="did" property="dept.id"/>
  <result column="dept_name" property="dept.departmentName"/>
</resultMap>
  <select id="selectEmployee"  resultMap="MyDifEmp">
    SELECT e.id id,e.last_name last_name,e.email email,e.gender gender,e.d_id d_id,
    d.id did,d.dept_name dept_name FROM tbl_employee e,tbl_dept d
    WHERE e.d_id=d.id AND e.id=#{id}
  </select>

代码就写完了,我们启动一下。
这里用postman测试一下:
在这里插入图片描述
大功告成!!!!!!!!!!!!!

版权声明1:本文来源CSDN,感谢博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
原文链接:https://blog.csdn.net/weixin_42370891/article/details/84199894
站方申明:本站部分内容来自社区用户分享,若涉及侵权,请联系站方删除。

0 条评论

请先 登录 后评论

官方社群