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
|