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

2023-07-26 19:28:04 164

模拟业务关系：

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

springboot2的环境搭建见上文：springboot2整合mybatis

一、mysql创表和模拟数据sql

CREATETABLEIFNOTEXISTS`user`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`name`varchar(50)NOTNULL,
`company_id`int(11)NOTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;

CREATETABLEIFNOTEXISTS`company`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`name`varchar(200)NOTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;

CREATETABLEIFNOTEXISTS`account`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`name`varchar(200)NOTNULL,
`user_id`int(11)NOTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;


INSERTINTO
`user`
VALUES
(1,'aa',1),
(2,'bb',2);

INSERTINTO
`company`
VALUES
(1,'xx公司'),
(2,'yy公司');

INSERTINTO
`account`
VALUES
(1,'中行',1),
(2,'工行',1),
(3,'中行',2);

二、创建实体

publicclassUser{
privateIntegerid;
privateStringname;
privateCompanycompany;
privateListaccounts;
//getter/setter这里省略...
}

publicclassCompany{
privateIntegerid;
privateStringcompanyName;
//getter/setter这里省略...
}

publicclassAccount{
privateIntegerid;
privateStringaccountName;
//getter/setter这里省略...

}

三、开发Mapper

方法一：使用注解

1、AccountMapper.java

packagecom.example.demo.mapper;

importjava.util.List;

importorg.apache.ibatis.annotations.Result;
importorg.apache.ibatis.annotations.Results;
importorg.apache.ibatis.annotations.Select;
importcom.example.demo.entity.Account;
publicinterfaceAccountMapper{
/*
*根据用户id查询账户信息
*/
@Select("SELECT*FROM`account`WHEREuser_id=#{userId}")
@Results({
@Result(property="accountName",column="name")
})
ListgetAccountByUserId(LonguserId);
}

2、CompanyMapper.java

packagecom.example.demo.mapper;

importorg.apache.ibatis.annotations.Result;
importorg.apache.ibatis.annotations.Results;
importorg.apache.ibatis.annotations.Select;

importcom.example.demo.entity.Company;

publicinterfaceCompanyMapper{
/*
*根据公司id查询公司信息
*/
@Select("SELECT*FROMcompanyWHEREid=#{id}")
@Results({
@Result(property="companyName",column="name")
})
CompanygetCompanyById(Longid);
}

3、UserMapper.java

packagecom.example.demo.mapper;

importorg.apache.ibatis.annotations.Result;
importorg.apache.ibatis.annotations.Results;
importorg.apache.ibatis.annotations.Select;
importorg.apache.ibatis.annotations.One;
importorg.apache.ibatis.annotations.Many;

importcom.example.demo.entity.User;

publicinterfaceUserMapper{

/*
*一对一查询
*property：查询结果赋值给此实体属性
*column：对应数据库的表字段，做为下面@One(select方法的查询参数
*one：一对一的查询
*@One(select=方法全路径)：调用的方法
*/
@Select("SELECT*FROMuserWHEREid=#{id}")
@Results({
@Result(property="company",column="company_id",one=@One(select="com.example.demo.mapper.CompanyMapper.getCompanyById"))
})
UsergetUserWithCompany(Longid);

/*
*一对多查询
*property：查询结果赋值给此实体属性
*column：对应数据库的表字段，可做为下面@One(select方法)的查询参数
*many：一对多的查询
*@Many(select=方法全路径)：调用的方法
*/
@Select("SELECT*FROMuserWHEREid=#{id}")
@Results({
@Result(property="id",column="id"),//加此行，否则id值为空
@Result(property="accounts",column="id",many=@Many(select="com.example.demo.mapper.AccountMapper.getAccountByUserId"))
})
UsergetUserWithAccount(Longid);

/*
*同时用一对一、一对多查询
*/
@Select("SELECT*FROMuser")
@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"))
})
ListgetAll();
}

方法二：使用XML

参考上文springboot2整合mybatis配置application.properties和mybatis-config.xml等后，
以上面的getAll()方法为例，UserMapper.xml配置如下：




















SELECT
u.id,u.name,c.idcompanyid,c.namecompanyname,a.idaccountid,a.nameaccountname
FROMuseru
LEFTJOINcompanyconu.company_id=c.id
LEFTJOINaccountaonu.id=a.user_id

四、控制层

packagecom.example.demo.web;

importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.web.bind.annotation.PathVariable;
importorg.springframework.web.bind.annotation.RequestMapping;
importorg.springframework.web.bind.annotation.RestController;

importcom.example.demo.entity.User;
importcom.example.demo.mapper.UserMapper;

@RestController
publicclassUserController{
@Autowired
privateUserMapperuserMapper;

//请求例子：http://localhost:9001/getUserWithCompany/1
/*请求结果：{"id":1,"name":"aa","company":{"id":1,"companyName":"xx公司"},"accounts":null}*/
@RequestMapping("/getUserWithCompany/{id}")
publicUsergetUserWithCompany(@PathVariable("id")Longid){
Useruser=userMapper.getUserWithCompany(id);
returnuser;
}


//请求例子：http://localhost:9001/getUserWithAccount/1
/*请求结果：{"id":1,"name":"aa","company":null,"accounts":[{"id":1,"accountName":"中行"},{"id":2,"accountName":"工行"}]}*/
@RequestMapping("/getUserWithAccount/{id}")
publicUsergetUserWithAccount(@PathVariable("id")Longid){
Useruser=userMapper.getUserWithAccount(id);
returnuser;
}


//请求例子：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")
publicListgetUsers(){
Listusers=userMapper.getAll();
returnusers;
}
}

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。

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

热门推荐

随机推荐