关键点: 用连接查询, 而不是用mybatis提供的属性select配置

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
CREATETABLE`person` (
`id` bigint(20) NOTNULLAUTO_INCREMENT COMMENT 'id',
`name` varchar(24) NOTNULLCOMMENT '用户名',
`pswd` varchar(16) NOTNULLCOMMENT '密码',
PRIMARYKEY(`id`)
) ENGINE=InnoDB DEFAULTCHARSET=utf8 COMMENT='用户'
CREATETABLE`extinfo` (
`id` bigint(20) NOTNULLAUTO_INCREMENT COMMENT 'id',
`personid` bigint(20) NOTNULLCOMMENT '用户id',
`email` varchar(32) DEFAULTNULLCOMMENT 'email',
`qq` bigint(20) DEFAULTNULLCOMMENT 'QQ号',
PRIMARYKEY(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULTCHARSET=utf8 COMMENT='用户扩展信息（一个用户只能有一条扩展记录）'
CREATETABLE`address` (
`id` bigint(20) NOTNULLAUTO_INCREMENT COMMENT 'id',
`personid` bigint(20) NOTNULLCOMMENT '用户id',
`addr` varchar(128) DEFAULTNULLCOMMENT '地址',
`zipcode` varchar(8) DEFAULTNULLCOMMENT '邮编',
PRIMARYKEY(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULTCHARSET=utf8 COMMENT='地址（一个用户可以有多个地址）'
CREATETABLE`orders` (
`id` bigint(20) NOTNULLAUTO_INCREMENT COMMENT 'ID',
`personid` bigint(20) NOTNULLCOMMENT '用户ID',
`product` varchar(128) NOTNULLCOMMENT '产品',
`num` int(11) NOTNULLCOMMENT '数量',
PRIMARYKEY(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULTCHARSET=utf8


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
publicclassPerson implementsSerializable {
privatelongid;
privateString name;          //用户名
privateString pswd;          //密码
privateExtinfo extinfo;
privateList<Address> addressList=newArrayList<Address>(0);
privateList<Orders> ordersList=newArrayList<Orders>(0);
publicclassExtinfo implementsSerializable {
privatelongid;
privatelongpersonid;          //用户id
privateString email;          //email
privatelongqq;          //QQ号
publicclassAddress implementsSerializable {
privatelongid;
privatelongpersonid;          //用户id
privateString addr;          //地址
privateString zipcode;          //邮编
publicclassOrders implementsSerializable {
privatelongid;
privatelongpersonid;          //用户ID
privateString product;          //产品
privateintnum;          //数量


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.id asp_id, e.id ase_id, a.id asa_id,e.personid ase_pid ,a.personid asa_pid,o.id aso_id,o.personid aso_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 withexit code 0


本文出自 “熔 岩” 博客，请务必保留此出处http://lavasoft.blog.51cto.com/62575/1385502