程序博客网 > 英语四级考试准备知乎

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

来源：互联网 发布：英语四级考试准备知乎 编辑：程序博客网 时间：2024/05/22 06:42
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 NULLCOMMENT 'email',
`qq` bigint(20)DEFAULT NULLCOMMENT 'QQ号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6DEFAULT CHARSET=utf8 COMMENT='用户扩展信息（一个用户只能有一条扩展记录）'
CREATE TABLE `address` (
`id` bigint(20)NOT NULL AUTO_INCREMENT COMMENT 'id',
`personid` bigint(20) NOTNULL COMMENT '用户id',
`addr` varchar(128)DEFAULT NULLCOMMENT '地址',
`zipcode` varchar(8) DEFAULTNULL COMMENT '邮编',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10DEFAULT CHARSET=utf8 COMMENT='地址（一个用户可以有多个地址）'
CREATE TABLE `orders` (
`id` bigint(20)NOT NULL AUTO_INCREMENT COMMENT 'ID',
`personid` bigint(20) NOTNULL COMMENT '用户ID',
`product` varchar(128) NOTNULL COMMENT '产品',
`num` int(11)NOT NULL COMMENT '数量',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9DEFAULT 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 implementsSerializable {
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 implementsSerializable {
private long id;
private long personid;          //用户id
privateString email;          //email
privatelong qq;          //QQ号
public class Address implementsSerializable {
privatelong id;
privatelong personid;         //用户id
privateString addr;          //地址
privateString zipcode;          //邮编
public class Orders implementsSerializable {
privatelong id;
privatelong personid;         //用户ID
privateString product;          //产品
privateint 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
<?xmlversion="1.0"encoding="UTF-8"?>
<!DOCTYPE configuration  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<propertiesresource="config/jdbc.properties"/>
<typeAliases>
<packagename="mypkg.entity"/>
</typeAliases>
<environmentsdefault="development">
<environmentid="development">
<transactionManagertype="JDBC"/>
<dataSourcetype="POOLED">
<propertyname="driver"value="${driver}"/>
<propertyname="url"value="${url}"/>
<propertyname="username"value="${username}"/>
<propertyname="password"value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapperresource="mypkg/entity/Person.xml"/>
<mapperresource="mypkg/entity/Extinfo.xml"/>
<mapperresource="mypkg/entity/Address.xml"/>
<mapperresource="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
<?xmlversion="1.0"encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mappernamespace="Person">
<resultMapid="rs_base"type="Person">
<idcolumn="id"property="id"/>
<resultproperty="name"column="name"/>
<resultproperty="pswd"column="pswd"/>
</resultMap>
<!-- 一对一，两个一对多，存在N+1问题，不推荐使用 -->
<resultMapid="rs1"type="Person">
<idproperty="id"column="id"/>
<resultproperty="name"column="name"/>
<resultproperty="pswd"column="pswd"/>
<associationproperty="extinfo"column="id"select="Extinfo.queryByPersonid"/>
<collectionproperty="addressList"column="id"select="Address.queryByPersonid"/>
<collectionproperty="ordersList"column="id"select="Orders.queryByPersonid"/>
</resultMap>
<selectid="find1"parameterType="string"resultType="Person"resultMap="rs1">
select * from person where name like "%"#{value}"%"
</select>
<!-- 一对一，没有N+1问题 -->
<resultMapid="rs2"type="Person">
<idproperty="id"column="p_id"/>
<resultproperty="name"column="name"/>
<resultproperty="pswd"column="pswd"/>
<associationproperty="extinfo"column="id"javaType="extinfo">
<idproperty="id"column="e_id"/>
<resultproperty="personid"column="personid"/>
<resultproperty="email"column="email"/>
<resultproperty="qq"column="qq"/>
</association>
</resultMap>
<selectid="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问题 -->
<resultMapid="rs3"type="Person">
<idproperty="id"column="p_id"/>
<resultproperty="name"column="name"/>
<resultproperty="pswd"column="pswd"/>
<collectionproperty="addressList"column="id"javaType="Address">
<idproperty="id"column="a_id"/>
<resultproperty="personid"column="personid"/>
<resultproperty="addr"column="addr"/>
<resultproperty="zipcode"column="zipcode"/>
</collection>
</resultMap>
<selectid="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问题 -->
<resultMapid="rs4"type="Person"autoMapping="true">
<idproperty="id"column="p_id"/>
<resultproperty="name"column="name"/>
<resultproperty="pswd"column="pswd"/>
<associationproperty="extinfo"column="id"javaType="extinfo">
<idproperty="id"column="e_id"/>
<resultproperty="personid"column="e_pid"/>
<resultproperty="email"column="email"/>
<resultproperty="qq"column="qq"/>
</association>
<collectionproperty="addressList"column="id"javaType="Address">
<idproperty="id"column="a_id"/>
<resultproperty="personid"column="a_pid"/>
<resultproperty="addr"column="addr"/>
<resultproperty="zipcode"column="zipcode"/>
</collection>
</resultMap>
<selectid="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问题 -->
<resultMapid="rs5"type="Person"autoMapping="true">
<idproperty="id"column="p_id"/>
<resultproperty="name"column="name"/>
<resultproperty="pswd"column="pswd"/>
<associationproperty="extinfo"column="id"javaType="extinfo">
<idproperty="id"column="e_id"/>
<resultproperty="personid"column="e_pid"/>
<resultproperty="email"column="email"/>
<resultproperty="qq"column="qq"/>
</association>
<collectionproperty="addressList"column="id"ofType="Address">
<idproperty="id"column="a_id"/>
<resultproperty="personid"column="a_pid"/>
<resultproperty="addr"column="addr"/>
<resultproperty="zipcode"column="zipcode"/>
</collection>
<collectionproperty="ordersList"column="id"ofType="Orders">
<idproperty="id"column="o_id"/>
<resultproperty="personid"column="o_pid"/>
<resultproperty="product"column="product"/>
<resultproperty="num"column="num"/>
</collection>
</resultMap>
<selectid="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>
<insertid="insert"parameterType="Person"useGeneratedKeys="true"keyProperty="id">
insert into person(name,pswd) values(#{name},#{pswd})
</insert>
<updateid="update"parameterType="Person">
update person set name=#{name},pswd=#{pswd} where id=#{id}
</update>
<selectid="load"parameterType="long"resultType="Person"resultMap="rs_base">
select * from person where id = #{value}
</select>
<deleteid="delete"parameterType="long">
delete from person where id = #{value}
</delete>
<!-- 查询结果集为Map -->
<selectid="load4Map"resultType="map">
select * from person
</select>
</mapper>

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?xmlversion="1.0"encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mappernamespace="Extinfo">
<resultMapid="rs_base"type="Extinfo">
<idcolumn="id"property="id"/>
<resultproperty="personid"column="personid"/>
<resultproperty="email"column="email"/>
<resultproperty="qq"column="qq"/>
</resultMap>
<selectid="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
<?xmlversion="1.0"encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mappernamespace="Address">
<resultMapid="rs_base"type="Address">
<idcolumn="id"property="id"/>
<resultproperty="personid"column="personid"/>
<resultproperty="addr"column="addr"/>
<resultproperty="zipcode"column="zipcode"/>
</resultMap>
<selectid="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
<?xmlversion="1.0"encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mappernamespace="Orders">
<resultMapid="rs_base"type="Orders">
<idcolumn="id"property="id"/>
<resultproperty="personid"column="personid"/>
<resultproperty="product"column="product"/>
<resultproperty="num"column="num"/>
</resultMap>
<selectid="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-2718:11:13org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:139)  ==>  Preparing: select *,p.idas p_id, e.id as e_id, a.id asa_id,e.personid ase_pid ,a.personid asa_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-2718:11:13org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:139)  ==> Parameters:
2014-03-2718:11:13org.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
0 0
英语四级考试准备知乎 英语四级考试准备知乎
原创粉丝点击
热门IT博客
软件水平考试 几点考软件水平考试 几点考
阿里巴巴和阿里云阿里巴巴和阿里云
r软件绘制世界地图
北京程序员聚集地
java string写入文件
a标签js跳转 target
递归算法的总结
淘宝女装模特图片
淘宝搜索引擎营销案例
lol解封软件下载
网络大电影广告植入
ug8.5车削加工编程
初页软件注册
乐视事件始末知乎
淘宝api调用实例
淘宝购物车满了图片
阳西网络问政平台
淘宝助理5.8.5.0版本
掏耳 知乎
mac的airdrop用不了
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 书虫是什么 书虫网 英语书虫 书虫图片 书虫入门级 蛀书虫 老书虫书吧 书虫 入门级 老书虫书店 小书虫作文 书虫旧书网 书虫小公主 书虫之家 书虫读后感 无极书虫作品 书虫在清朝的米虫生活 书虫系列英语读物 书虫mp3免费下载 英语书虫在线阅读 英语书虫系列 书虫在线阅读 书虫系列英语读物mp3 书虫是什么虫 小书虫读书卡 小书虫手抄报 我们班的小书虫 威廉莎士比亚书虫 苏格兰玛丽女王书虫 书虱 书虱图片 虫斑皮蠹 蠹 蠹怎么读 户枢不蠹 蠹鱼 五蠹 黑皮蠹 韩非子五蠹 户枢不蠹怎么读 蠹政病民 窃蠹科

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里