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.代码如下：
mysql语句多表查询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.Id());
return selectEmployee;
}
Mapper
Employee selectEmployee(Integer id);
5.创建两个实体类，代码如下：
Department
package com.ity;
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 + '\'' +
'}';
servlet容器是什么意思
}
}
Employee
package com.ity;
public class Employee {
/**
*
* This field was generated by MyBatis Generator.
* This field corresponds to the database column tbl_employee.id
*
怎么在button中加上小图标* @ated
*/
private Integer id;
/**
*
* This field was generated by MyBatis Generator.
* This field corresponds to the database column tbl_employee.last_name *
* @ated
*/
private String lastName;
/**
*
* This field was generated by MyBatis Generator.
* This field corresponds to the database column ail
*
* @ated
*/
linux系统发布应用private String email;
*
* This field was generated by MyBatis Generator.
* This field corresponds to the database column der
*
* @ated
*/
private String gender;
/**
*
* This field was generated by MyBatis Generator.
* This field corresponds to the database column tbl_employee.d_id
*
* @ated
*/
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
*
* @ated
*/
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
*
* @ated
*/我的世界null图片
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
*
* @ated
*/
public String getLastName() {
return lastName;
}
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论