Spring boot2基于Mybatis实现多表关联查询

Spring boot2基于Mybatis实现多表关联查询

模拟业务关系：

一个用户user有对应的一个公司company，每个用户有多个账户account。

spring boot 2的环境搭建见上文：spring boot 2整合mybatis

一、mysql创表和模拟数据sql

CREATE TABLE IF NOT EXISTS `user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(50) NOT NULL,

`company_id` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `company` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(200) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `account` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(200) NOT NULL,

`user_id` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO

`user`

VALUES

(1, 'aa', 1),

(2, 'bb', 2);

INSERT INTO

`company`

VALUES

(1, 'xx公司'),

(2, 'yy公司');

INSERT INTO

`account`

VALUES

(1, '中行', 1),

(2, '工行', 1),

(3, '中行'http://, 2);

二、创建实体

public class User {

private Integer id;

private String name;

private Company company;

private List accounts;

//getter/setter 这里省略...

}

public class Company {

private Integer id;

private String companyName;

//getter/setter 这里省略...

}

public class Account {

private Integer id;

private String accountName;

//getter/setter 这里省略...

}

三、开发Mapper

方法一：使用注解

1、AccountMapper.java

package com.example.demo.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Result;

import org.apache.ibatis.annotations.Results;

import org.apache.ibatis.annotations.Select;

import com.example.demo.entity.Account;

public interface AccountMapper {

/*

* 根据用户id查询账户信息

*/

@Select("SELECT * FROM `account` WHERE user_id = #{userId}")

@Results({

@Result(property = "accountName", column = "name")

})

List getAccountByUserId(Long userId);

}

2、CompanyMapper.java

package com.example.demo.mapper;

import org.apache.ibatis.annotations.Result;

import org.apache.ibatis.annotations.Results;

import org.apache.ibatis.annotations.Select;

import com.example.demo.entity.Company;

public interface CompanyMapper {

/*

* 根据公司id查询公司信息

*/

@Select("SELECT * FROM company WHERE id = #{id}")

@Results({

@Result(property = "companyName", column = "name")

})

Company getCompanyById(Long id);

}

3、UserMapper.java

package com.example.demo.mapper;

import org.apache.ibatis.annotations.Result;

import org.apache.ibatis.annotations.Results;

import org.apache.ibatis.annotations.Select;

import org.apache.ibatis.annotations.One;

import org.apache.ibatis.annotations.Many;

import com.example.demo.entity.User;

public interface UserMapper {

/*

* 一对一查询

* property：查询结果赋值给此实体属性

* column：对应数据库的表字段，做为下面@One(select方法的查询参数

* one：一对一的查询

* @One(select = 方法全路径) ：调用的方法

*/

@Select("SELECT * FROM user WHERE id = #{id}")

@Results({

@Result(property = "company", column = "company_id", one = @One(select = "com.example.demo.mapper.CompanyMapper.getCompanyById"))

})

User getUserWithCompany(Long id);

/*

* 一对多查询

* property：查询结果赋值给此实体属性

* column：对应数据库的表字段，可做为下面@One(select方法)的查询参数

* many：一对多的查询

* @Many(select = 方法全路径) ：调用的方法

*/

@Select("SELECT * FROM user WHERE id = #{id}")

@Results({

@Result(property = "id", column = "id"),//加此行，否则id值为空

@Result(property = "accounts", column = "id", many = @Many(select = "com.example.demo.mapper.AccountMapper.getAccountByUserId"))

})

User getUserWithAccount(Long id);

/*

* 同时用一对一、一对多查询

*/

@Select("SELECT * FROM user")

@Results({

@Result(property = "id", column = "id"),

@Result(property = "company", column = "company_id", one = @One(select = "com.example.demo.mapper.CompanyMapper.getCompanyById")),

@Result(property = "accounts", column = "id", many = @Many(select = "com.example.demo.mapper.AccountMapper.getAccountByUserId"))

})

List getAll();

}

方法二：使用XML

参考上文spring boot 2整合mybatis配置application.properties和mybatis-config.xml等后，

以上面的getAll()方法为例，UserMapper.xml配置如下：

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

四、控制层

package com.example.demo.web;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.PathVariable;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import com.example.demo.entity.User;

import com.example.demo.mapper.UserMapper;

@RestController

public class UserController {

@Autowired

private UserMapper userMapper;

//请求例子：http://localhost:9001/getUserWithCompany/1

/*请求结果：{"id":1,"name":"aa","company":{"id":1,"companyName":"xx公司"},"accounts":null}*/

@RequestMapping("/getUserWithCompany/{id}")

public User getUserWithCompany(@PathVariable("id") Long id) {

User user = userMapper.getUserWithCompany(id);

return user;

}

//请求例子：http://localhost:9001/getUserWithAccount/1

/*请求结果：{"id":1,"name":"aa","company":null,"accounts":[{"id":1,"accountName":"中行"},{"id":2,"accountName":"工行"}]}*/

@RequestMapping("/getUserWithAccount/{id}")

public User getUserWithAccount(@PathVariable("id") Long id) {

User user = userMapper.getUserWithAccount(id);

return user;

}

//请求例子：http://localhost:9001/getUserWithAccount/1

/*请求结果：[{"id":1,"name":"aa","company":{"id":1,"companyName":"xx公司"},"accounts":[{"id":1,"accountName":"中行"},

{"id":2,"accountName":"工行"}]},{"id":2,"name":"bb","company":{"id":2,"companyName":"yy公司"},"accounts":[{"id":3,"accountName":"中行"}]}]*/

@RequestMapping("/getUsers")

public List getUsers() {

List users=userMapper.getAll();

return users;

}

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。