备案控制台
开发者社区 数据库 文章 正文

MyBaits关联查询一对一、一对多

2017-11-22 938
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

MyBaits 3.2.6

MySQL 5.5.12

JDK1.6

 

模型：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE  TABLE  `person` (
`id`  bigint (20)  NOT  NULL  AUTO_INCREMENT COMMENT  'id' ,
` name `  varchar (24)  NOT  NULL  COMMENT  '用户名' ,
`pswd`  varchar (16)  NOT  NULL  COMMENT  '密码' ,
PRIMARY  KEY  (`id`)
) ENGINE=InnoDB  DEFAULT  CHARSET=utf8 COMMENT= '用户'
CREATE  TABLE  `extinfo` (
`id`  bigint (20)  NOT  NULL  AUTO_INCREMENT COMMENT  'id' ,
`personid`  bigint (20)  NOT  NULL  COMMENT  '用户id' ,
`email`  varchar (32)  DEFAULT  NULL  COMMENT  'email' ,
`qq`  bigint (20)  DEFAULT  NULL  COMMENT  'QQ号' ,
PRIMARY  KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6  DEFAULT  CHARSET=utf8 COMMENT= '用户扩展信息（一个用户只能有一条扩展记录）'
CREATE  TABLE  `address` (
`id`  bigint (20)  NOT  NULL  AUTO_INCREMENT COMMENT  'id' ,
`personid`  bigint (20)  NOT  NULL  COMMENT  '用户id' ,
`addr`  varchar (128)  DEFAULT  NULL  COMMENT  '地址' ,
`zipcode`  varchar (8)  DEFAULT  NULL  COMMENT  '邮编' ,
PRIMARY  KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10  DEFAULT  CHARSET=utf8 COMMENT= '地址（一个用户可以有多个地址）'
CREATE  TABLE  `orders` (
`id`  bigint (20)  NOT  NULL  AUTO_INCREMENT COMMENT  'ID' ,
`personid`  bigint (20)  NOT  NULL  COMMENT  '用户ID' ,
`product`  varchar (128)  NOT  NULL  COMMENT  '产品' ,
`num`  int (11)  NOT  NULL  COMMENT  '数量' ,
PRIMARY  KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9  DEFAULT  CHARSET=utf8

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public  class  Person  implements  Serializable {
private  long  id;
private  String name;           //用户名
private  String pswd;           //密码
private  Extinfo extinfo;
private  List<Address> addressList= new  ArrayList<Address>( 0 );
private  List<Orders> ordersList= new  ArrayList<Orders>( 0 );
public  class  Extinfo  implements  Serializable {
private  long  id;
private  long  personid;           //用户id
private  String email;           //email
private  long  qq;           //QQ号
public  class  Address  implements  Serializable {
private  long  id;
private  long  personid;           //用户id
private  String addr;           //地址
private  String zipcode;           //邮编
public  class  Orders  implements  Serializable {
private  long  id;
private  long  personid;           //用户ID
private  String product;           //产品
private  int  num;           //数量

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<? xml  version = "1.0"  encoding = "UTF-8"  ?>
<!DOCTYPE configuration  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
< configuration >
< properties  resource = "config/jdbc.properties" />
< typeAliases >
< package  name = "mypkg.entity" />
</ typeAliases >
< environments  default = "development" >
< environment  id = "development" >
< transactionManager  type = "JDBC" />
< dataSource  type = "POOLED" >
< property  name = "driver"  value = "${driver}" />
< property  name = "url"  value = "${url}" />
< property  name = "username"  value = "${username}" />
< property  name = "password"  value = "${password}" />
</ dataSource >
</ environment >
</ environments >
< mappers >
< mapper  resource = "mypkg/entity/Person.xml" />
< mapper  resource = "mypkg/entity/Extinfo.xml" />
< mapper  resource = "mypkg/entity/Address.xml" />
< mapper  resource = "mypkg/entity/Orders.xml" />
</ mappers >
</ configuration >

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
<? 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 = "Person" >
< resultMap  id = "rs_base"  type = "Person" >
< id  column = "id"  property = "id" />
< result  property = "name"  column = "name" />
< result  property = "pswd"  column = "pswd" />
</ resultMap >
<!-- 一对一，两个一对多，存在N+1问题，不推荐使用 -->
< resultMap  id = "rs1"  type = "Person" >
< id  property = "id"  column = "id" />
< result  property = "name"  column = "name" />
< result  property = "pswd"  column = "pswd" />
< association  property = "extinfo"  column = "id"  select = "Extinfo.queryByPersonid" />
< collection  property = "addressList"  column = "id"  select = "Address.queryByPersonid" />
< collection  property = "ordersList"  column = "id"  select = "Orders.queryByPersonid" />
</ resultMap >
< select  id = "find1"  parameterType = "string"  resultType = "Person"  resultMap = "rs1" >
select * from person where name like "%"#{value}"%"
</ select >
<!-- 一对一，没有N+1问题 -->
< resultMap  id = "rs2"  type = "Person" >
< id  property = "id"  column = "p_id" />
< result  property = "name"  column = "name" />
< result  property = "pswd"  column = "pswd" />
< association  property = "extinfo"  column = "id"  javaType = "extinfo" >
< id  property = "id"  column = "e_id" />
< result  property = "personid"  column = "personid" />
< result  property = "email"  column = "email" />
< result  property = "qq"  column = "qq" />
</ association >
</ resultMap >
< select  id = "find2"  parameterType = "string"  resultType = "Person"  resultMap = "rs2" >
select *,p.id as p_id, e.id as e_id from person p left join extinfo e on p.id=e.personid
where p.name like "%"#{value}"%"
</ select >
<!-- 一对多，没有N+1问题 -->
< resultMap  id = "rs3"  type = "Person" >
< id  property = "id"  column = "p_id" />
< result  property = "name"  column = "name" />
< result  property = "pswd"  column = "pswd" />
< collection  property = "addressList"  column = "id"  javaType = "Address" >
< id  property = "id"  column = "a_id" />
< result  property = "personid"  column = "personid" />
< result  property = "addr"  column = "addr" />
< result  property = "zipcode"  column = "zipcode" />
</ collection >
</ resultMap >
< select  id = "find3"  parameterType = "string"  resultType = "Person"  resultMap = "rs3" >
select *,p.id as p_id, a.id as a_id from person p left join address a on p.id=a.personid
where p.name like "%"#{value}"%"
</ select >
<!-- 一对一和一对多，没有N+1问题 -->
< resultMap  id = "rs4"  type = "Person"  autoMapping = "true" >
< id  property = "id"  column = "p_id" />
< result  property = "name"  column = "name" />
< result  property = "pswd"  column = "pswd" />
< association  property = "extinfo"  column = "id"  javaType = "extinfo" >
< id  property = "id"  column = "e_id" />
< result  property = "personid"  column = "e_pid" />
< result  property = "email"  column = "email" />
< result  property = "qq"  column = "qq" />
</ association >
< collection  property = "addressList"  column = "id"  javaType = "Address" >
< id  property = "id"  column = "a_id" />
< result  property = "personid"  column = "a_pid" />
< result  property = "addr"  column = "addr" />
< result  property = "zipcode"  column = "zipcode" />
</ collection >
</ resultMap >
< select  id = "find4"  parameterType = "string"  resultType = "Person"  resultMap = "rs4" >
select *,p.id as p_id, e.id as e_id, a.id as a_id,e.personid as e_pid ,a.personid as a_pid
from person p left join address a on p.id=a.personid
left join extinfo e on p.id = e.personid
where p.name like '%b%'
</ select >
<!-- 一对多，2个一对多，没有N+1问题 -->
< resultMap  id = "rs5"  type = "Person"  autoMapping = "true" >
< id  property = "id"  column = "p_id" />
< result  property = "name"  column = "name" />
< result  property = "pswd"  column = "pswd" />
< association  property = "extinfo"  column = "id"  javaType = "extinfo" >
< id  property = "id"  column = "e_id" />
< result  property = "personid"  column = "e_pid" />
< result  property = "email"  column = "email" />
< result  property = "qq"  column = "qq" />
</ association >
< collection  property = "addressList"  column = "id"  ofType = "Address" >
< id  property = "id"  column = "a_id" />
< result  property = "personid"  column = "a_pid" />
< result  property = "addr"  column = "addr" />
< result  property = "zipcode"  column = "zipcode" />
</ collection >
< collection  property = "ordersList"  column = "id"  ofType = "Orders" >
< id  property = "id"  column = "o_id" />
< result  property = "personid"  column = "o_pid" />
< result  property = "product"  column = "product" />
< result  property = "num"  column = "num" />
</ collection >
</ resultMap >
< select  id = "find5"  parameterType = "string"  resultType = "Person"  resultMap = "rs5" >
select *,p.id as p_id, e.id as e_id, a.id as a_id,e.personid as e_pid ,a.personid as a_pid,o.id as o_id,o.personid as o_pid
from person p left join address a on p.id=a.personid
left join extinfo e on p.id = e.personid
left join orders o on p.id = o.personid
where p.name like '%b%'
</ select >
< insert  id = "insert"  parameterType = "Person"  useGeneratedKeys = "true"  keyProperty = "id" >
insert into person(name,pswd) values(#{name},#{pswd})
</ insert >
< update  id = "update"  parameterType = "Person" >
update person set name=#{name},pswd=#{pswd} where id=#{id}
</ update >
< select  id = "load"  parameterType = "long"  resultType = "Person"  resultMap = "rs_base" >
select * from person where id = #{value}
</ select >
< delete  id = "delete"  parameterType = "long" >
delete from person where id = #{value}
</ delete >
<!-- 查询结果集为Map -->
< select  id = "load4Map"  resultType = "map" >
select * from person
</ select >
</ mapper >

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<? 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 = "Extinfo" >
< resultMap  id = "rs_base"  type = "Extinfo" >
< id  column = "id"  property = "id" />
< result  property = "personid"  column = "personid" />
< result  property = "email"  column = "email" />
< result  property = "qq"  column = "qq" />
</ resultMap >
< select  id = "queryByPersonid"  parameterType = "long"  resultType = "Extinfo"  resultMap = "rs_base" >
select * from extinfo where personid=#{value}
</ select >
</ mapper >
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<? 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 = "Address" >
< resultMap  id = "rs_base"  type = "Address" >
< id  column = "id"  property = "id" />
< result  property = "personid"  column = "personid" />
< result  property = "addr"  column = "addr" />
< result  property = "zipcode"  column = "zipcode" />
</ resultMap >
< select  id = "queryByPersonid"  parameterType = "long"  resultType = "Address"  resultMap = "rs_base" >
select * from address where personid=#{value}
</ select >
</ mapper >
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<? 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 = "Orders" >
< resultMap  id = "rs_base"  type = "Orders" >
< id  column = "id"  property = "id" />
< result  property = "personid"  column = "personid" />
< result  property = "product"  column = "product" />
< result  property = "num"  column = "num" />
</ resultMap >
< select  id = "queryByPersonid"  parameterType = "long"  resultType = "Orders"  resultMap = "rs_base" >
select * from orders where personid=#{value}
</ select >
</ mapper >
1
2
3
4
5
6
7
8
9
10
11
12
13
#全局日志配置
log4j.rootLogger=debug, stdout
#包下所有类的日志级别
log4j.logger.org.apache.ibatis=debug
log4j.logger.java.sql. Connection =info, stdout
log4j.logger.java.sql.Statement=debug, stdout
log4j.logger.java.sql.PreparedStatement=debug, stdout
#日志输出到控制台
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %l  %m%n
#关闭Spring日志
log4j.category.org.springframework =  OFF

 

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
D:\jdk1. 6 .0_45\bin\java -Didea.launcher.port= 7533  -Didea.launcher.bin.path=C:\IDEA. 13.0 . 1 \bin -Dfile.encoding=UTF- 8  -classpath D:\jdk1. 6 .0_45\jre\lib\charsets.jar;D:\jdk1. 6 .0_45\jre\lib\deploy.jar;D:\jdk1. 6 .0_45\jre\lib\javaws.jar;D:\jdk1. 6 .0_45\jre\lib\jce.jar;D:\jdk1. 6 .0_45\jre\lib\jsse.jar;D:\jdk1. 6 .0_45\jre\lib\management-agent.jar;D:\jdk1. 6 .0_45\jre\lib\plugin.jar;D:\jdk1. 6 .0_45\jre\lib\resources.jar;D:\jdk1. 6 .0_45\jre\lib\rt.jar;D:\jdk1. 6 .0_45\jre\lib\ext\dnsns.jar;D:\jdk1. 6 .0_45\jre\lib\ext\localedata.jar;D:\jdk1. 6 .0_45\jre\lib\ext\sunjce_provider.jar;D:\jdk1. 6 .0_45\jre\lib\ext\sunmscapi.jar;D:\jdk1. 6 .0_45\jre\lib\ext\sunpkcs11.jar;D:\IdeaProjects\mybaitsdemo2\out\production\mybaitsdemo2;D:\IdeaProjects\mybaitsdemo2\lib\asm- 3.3 . 1 .jar;D:\IdeaProjects\mybaitsdemo2\lib\junit- 4.0 .jar;D:\IdeaProjects\mybaitsdemo2\lib\cglib- 2.2 . 2 .jar;D:\IdeaProjects\mybaitsdemo2\lib\log4j- 1.2 . 16 .jar;D:\IdeaProjects\mybaitsdemo2\lib\mybatis- 3.2 . 6 .jar;D:\IdeaProjects\mybaitsdemo2\lib\slf4j-api- 1.7 . 5 .jar;D:\IdeaProjects\mybaitsdemo2\lib\javassist- 3.17 . 1 -GA.jar;D:\IdeaProjects\mybaitsdemo2\lib\slf4j-log4j12- 1.7 . 5 .jar;D:\IdeaProjects\mybaitsdemo2\lib\commons-logging- 1.1 . 1 .jar;D:\IdeaProjects\mybaitsdemo2\lib\mysql-connector-java- 5.1 . 17 -bin.jar;C:\IDEA. 13.0 . 1 \lib\idea_rt.jar com.intellij.rt.execution.application.AppMain mypkg.dao.PersonDAO
2014 - 03 - 27  18 : 11 : 13  org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java: 139 )  ==>  Preparing: select *,p.id  as  p_id, e.id  as  e_id, a.id  as  a_id,e.personid  as  e_pid ,a.personid  as  a_pid,o.id  as  o_id,o.personid  as  o_pid from person p left join address a on p.id=a.personid left join extinfo e on p.id = e.personid left join orders o on p.id = o.personid where p.name like  '%b%'
2014 - 03 - 27  18 : 11 : 13  org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java: 139 )  ==> Parameters:
2014 - 03 - 27  18 : 11 : 13  org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java: 139 )  <==      Total:  15
Person{id= 1 , name= '张三b' , pswd= '111' }
Extinfo{id= 1 , personid= 1 , email= 'adf@as.com' , qq= 1231412341 }
Address{id= 1 , personid= 1 , addr= 'adsaaaa' , zipcode= '2342342' }
Address{id= 2 , personid= 1 , addr= 'werwqfqw' , zipcode= '2234234' }
Address{id= 3 , personid= 1 , addr= 'qwefaz' , zipcode= '2342342' }
Orders{id= 1 , personid= 1 , product= 'aaa' , num= 2 }
Person{id= 2 , name= '李四b' , pswd= '222' }
Extinfo{id= 2 , personid= 2 , email= 'jkks@sa.com' , qq= 827238782 }
Address{id= 4 , personid= 2 , addr= 'vzczsd' , zipcode= '13234234' }
Address{id= 5 , personid= 2 , addr= 'aaaaaaaaa' , zipcode= '2342356' }
Address{id= 6 , personid= 2 , addr= 'asawsd' , zipcode= '4564565' }
Orders{id= 2 , personid= 2 , product= 'bbb' , num= 3 }
Orders{id= 3 , personid= 2 , product= 'ccc' , num= 1 }
Person{id= 3 , name= '王五b' , pswd= '111' }
Extinfo{id= 3 , personid= 3 , email= '8238@aa.com' , qq= 234253234 }
Address{id= 7 , personid= 3 , addr= 'jkhkky' , zipcode= '2342342' }
Orders{id= 4 , personid= 3 , product= 'ddd' , num= 3 }
Orders{id= 5 , personid= 3 , product= 'asdf' , num= 21 }
Person{id= 5 , name= 'ggggb' , pswd= 'password' }
Extinfo{id= 5 , personid= 5 , email= '2323423W@asd.com' , qq= 8992837422 }
Orders{id= 7 , personid= 5 , product= 'zzdfa' , num= 232 }
Orders{id= 8 , personid= 5 , product= 'ggg' , num= 66 }
Person{id= 15 , name= 'testnameb' , pswd= 'password' }
Person{id= 19 , name= 'testnameb' , pswd= 'password' }
Process finished  with  exit code  0

 

 

 本文转自 leizhimin 51CTO博客，原文链接：http://blog.51cto.com/lavasoft/1385502，如需转载请自行联系原作者

文章标签：
日志服务
Java
关系型数据库
相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
技术小阿哥
目录
相关文章
阿甘兄
|
7月前
|
SQL Java 数据库连接
33MyBatis - 关联查询（一对多查询）
33MyBatis - 关联查询（一对多查询）
阿甘兄
36 0
一个风轻云淡
|
10月前
|
SQL Java 关系型数据库
mybatis多对一和一对多查询数据处理解读（下）
mybatis多对一和一对多查询数据处理解读（下）
一个风轻云淡
95 1
i46usca74mzt4
|
7月前
|
XML Java 数据库连接
“MyBatis中的关联关系配置与多表查询“
“MyBatis中的关联关系配置与多表查询“
i46usca74mzt4
28 0
IT012201
|
8月前
|
SQL Java 数据库连接
Mybatis多对一，一对多
Mybatis多对一，一对多
IT012201
39 0
一个风轻云淡
|
10月前
|
SQL Java 关系型数据库
mybatis多对一和一对多查询数据处理解读（上）
mybatis多对一和一对多查询数据处理解读
一个风轻云淡
73 0
全村第二帅
|
11月前
|
SQL Java 数据库连接
Mybatis多表查询之一对多、多对一
Mybatis多表查询之一对多、多对一
全村第二帅
107 0
请你喝杯Java
|
12月前
|
Java 数据库连接 mybatis
Mybatis实现一对一、一对多关联查询
Mybatis实现一对一、一对多关联查询
请你喝杯Java
98 0
萌狼蓝天
|
Java 数据库连接 mybatis
【MyBatis】学习笔记15：通过分步查询解决一对多或多对多问题
【MyBatis】学习笔记15：通过分步查询解决一对多或多对多问题
萌狼蓝天
211 0
【MyBatis】学习笔记15：通过分步查询解决一对多或多对多问题
星繁
|
Java 关系型数据库 MySQL
mybatis一对一，一对多，多对多的关联查询
mybatis一对一，一对多，多对多的关联查询
星繁
100 0
前端歌谣
|
Java 数据库连接 mybatis
mybatis学习(49):嵌套查询--一对多
mybatis学习(49):嵌套查询--一对多
前端歌谣
45 0
mybatis学习(49):嵌套查询--一对多