Spring boot2基于Mybatis实现多表关联查询
程序员文章站
2022-03-26 08:06:03
模拟业务关系:一个用户user有对应的一个公司company,每个用户有多个账户account。spring boot 2的环境搭建见上文:spring boot 2整合mybatis一、mysql创...
模拟业务关系:
一个用户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, '中行', 2);
二、创建实体
public class user { private integer id; private string name; private company company; private list<account> 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<account> 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<user> getall(); }
方法二:使用xml
参考上文spring boot 2整合mybatis配置application.properties和mybatis-config.xml等后,
以上面的getall()方法为例,usermapper.xml配置如下:
<?xml version="1.0" encoding="utf-8" ?> <!doctype mapper public "-//mybatis.org//dtd mapper 3.0//en" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.example.demo.mapper.usermapper" > <resultmap id="usermap" type="com.example.demo.entity.user"> <id column="id" jdbctype="integer" property="id" /> <result property="name" column="name" jdbctype="varchar" /> <!--封装映射company表数据,user表与company表1对1关系,配置1对1的映射 association:用于配置1对1的映射 属性property:company对象在user对象中的属性名 属性javatype:company属性的java对象 类型 属性column:user表中的外键引用company表 --> <association property="company" javatype="com.example.demo.entity.company" column="company_id"> <id property="id" column="companyid"></id> <result property="companyname" column="companyname"></result> </association> <!--配置1对多关系映射 property:在user里面的list<account>的属性名 oftype:当前account表的java类型 column:外键 --> <collection property="accounts" oftype="com.example.demo.entity.account" column="user_id"> <id property="id" column="accountid"></id> <result property="accountname" column="accountname"></result> </collection> </resultmap> <select id="getall" resultmap="usermap" > 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 </select> </mapper>
四、控制层
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<user> getusers() { list<user> users=usermapper.getall(); return users; } }
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。
上一篇: Java 8 Stream操作类型及peek示例解析
下一篇: ASP注入详细命令40条第1/2页
推荐阅读
-
序列化表单为json对象,datagrid带额外参提交一次查询 后台用Spring data JPA 实现带条件的分页查询 多表关联查询
-
Spring boot2基于Mybatis实现多表关联查询
-
Mybatis基于注解实现多表查询功能
-
Spring Boot2(十一):Mybatis使用总结(自增长、多条件、批量操作、多表查询等等)
-
Spring boot2基于Mybatis实现多表关联查询
-
Java Maven:spring boot + Mybatis连接MySQL,通用mapper的增删改查,映射实现多表查询
-
Spring Boot2(十一):Mybatis使用总结(自增长、多条件、批量操作、多表查询等等)