推荐这个非常清楚详细的实例,原文:http://www.2cto.com/database/201505/399017.html
一、创建表、分析
下面是两表,一个是顾客表,一个是车票表。一个顾客可以对应多张车票,但是一张车票只能对应一个顾客
t_customer:顾客表,一个顾客可以对应多张车票
t_ticket:车票表,一张车票只能对应一个顾客
1、创建数据表及插入初始数据
创建数据表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
use test;
DROP
TABLE
IF EXISTS t_customer;
CREATE
TABLE
t_customer(
customerId
INT
PRIMARY
KEY
AUTO_INCREMENT,
customerName
VARCHAR
(20)
NOT
NULL
,
customerTel
INT
NOT
NULL
)ENGINE=InnoDB
DEFAULT
CHARSET=utf8;
DROP
TABLE
IF EXISTS t_ticket;
CREATE
TABLE
t_ticket(
ticketId
INT
PRIMARY
KEY
AUTO_INCREMENT,
ticketAddress
VARCHAR
(50)
NOT
NULL
,
ticketPrice
INT
NOT
NULL
,
ticketCId
INT
NOT
NULL
)ENGINE=InnoDB
DEFAULT
CHARSET=utf8;
|
插入数据:
1
2
3
4
5
6
7
8
9
|
use test;
insert
into
t_customer
values
(1,
'小王'
,1888327654);
insert
into
t_customer
values
(2,
'天天'
,3456546354);
insert
into
t_customer
values
(3,
'阿大'
,123345566);
insert
into
t_ticket
values
(1,
'武汉到重庆'
,100,1);
insert
into
t_ticket
values
(2,
'北京到上海'
,200,1);
insert
into
t_ticket
values
(3,
'深圳到广州'
,50,1);
|
传统的联合查询的方法
1
|
select
c.*,t.*
from
t_customer c
JOIN
t_ticket t
ON
(c.customerId=t.ticketCId)
where
c.customerName =
'小王'
;
|
结果如下:
二、工程创建
1、新建java工程,导入需要的包,最后整个工程目录 如下:
2、创建表对应的类:
Customer.java:
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
|
package com.mucfc.model;
import java.util.List;
/**
*顾客信息类
*@author linbingwen
*@2015年5月13日8:30:12
*/
public
class Customer {
private
Integer
customerId;
private String customerName;
private
Integer
customerTel;
private List<ticket> tickets;//使用一个List来表示车票
public
List<ticket> getTickets() {
return
tickets;
}
public
void setTickets(List<ticket> tickets) {
this.tickets = tickets;
}
public
Integer
getCustomerId() {
return
customerId;
}
public
void setCustomerId(
Integer
customerId) {
this.customerId = customerId;
}
public
String getCustomerName() {
return
customerName;
}
public
void setCustomerName(String customerName) {
this.customerName = customerName;
}
public
Integer
getCustomerTel() {
return
customerTel;
}
public
void setCustomerTel(
Integer
customerTel) {
this.customerTel = customerTel;
}
@Override
public
String toString() {
return
"Customer [customerId="
+ customerId +
", customerName="
+ customerName +
", customerTel="
+ customerTel+
"]"
;
}
}
</ticket></ticket></ticket>
|
Ticket.java:
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
|
package com.mucfc.model;
/**
*车票信息类
*@author linbingwen
*@2015年5月13日8:30:12
*/
public
class Ticket {
private
Integer
ticketId;
private String ticketAddress;
private
Integer
ticketPrice;
private
Integer
ticketCId;
private Customer customer;//使用一个customer来表示顾客
public
Customer getCustomer() {
return
customer;
}
public
void setCustomer(Customer customer) {
this.customer = customer;
}
public
Integer
getTicketId() {
return
ticketId;
}
public
void setTicketId(
Integer
ticketId) {
this.ticketId = ticketId;
}
public
String getTicketAddress() {
return
ticketAddress;
}
public
void setTicketAddress(String ticketAddress) {
this.ticketAddress = ticketAddress;
}
public
Integer
getTicketPrice() {
return
ticketPrice;
}
public
void setTicketPrice(
Integer
ticketPrice) {
this.ticketPrice = ticketPrice;
}
public
Integer
getTicketCId() {
return
ticketCId;
}
public
void setTicketCId(
Integer
ticketCId) {
this.ticketCId = ticketCId;
}
@Override
public
String toString() {
return
"Ticket [ticketId="
+ ticketId +
", ticketAddress="
+ ticketAddress +
", ticketPrice="
+ ticketPrice
+
", ticketCId="
+ ticketCId +
"]"
;
}
}
|
3、定义sql映射文件
(1)首先是一对多关联:
MyBatis中使用collection标签来解决一对一的关联查询,collection标签可用的属性如下:property:指的是集合属性的值ofType:指的是集合中元素的类型column:所对应的外键字段名称select:使用另一个查询封装的结果
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
|
<?xml version=
"1.0"
encoding=
"UTF-8"
?>
<!DOCTYPE mapper PUBLIC
"-//mybatis.org//DTD Mapper 3.0//EN"
<mapper namespace=
"com.mucfc.model.CustomerMapper"
>
<!-- 定义数据库字段与实体对象的映射关系 -->
<resultMap type=
"Customer"
id=
"customerBean"
>
<id column=
"customerId"
property=
"customerId"
/>
<result column=
"customerName"
property=
"customerName"
/>
<result column=
"customerTel"
property=
"customerTel"
/>
<!-- 一对多的关系 -->
<!-- property: 指的是集合属性的值, ofType:指的是集合中元素的类型 -->
<collection property=
"tickets"
ofType=
"Ticket"
>
<id column=
"ticketId"
property=
"ticketId"
/>
<result column=
"ticketAddress"
property=
"ticketAddress"
/>
<result column=
"ticketPrice"
property=
"ticketPrice"
/>
<result column=
"ticketCId"
property=
"ticketCId"
/>
</collection>
</resultMap>
<!-- 根据id查询Person, 关联将Orders查询出来 -->
<select id=
"selectCustomerByName"
parameterType=
"string"
resultMap=
"customerBean"
>
select c.*,t.* from t_customer c,t_ticket t where c.customerId=t.ticketCId and c.customerName =#{customerName};
</select>
</mapper>
|
1
|
<mapper namespace=
"com.mucfc.model.CustomerMapper"
></mapper>
|
(2)接着是一对一关联:
MyBatis中使用association标签来解决一对一的关联查询,association标签可用的属性如下:property:对象属性的名称javaType:对象属性的类型column:所对应的外键字段名称select:使用另一个查询封装的结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
<?xml version=
"1.0"
encoding=
"UTF-8"
?>
<!DOCTYPE mapper PUBLIC
"-//mybatis.org//DTD Mapper 3.0//EN"
<mapper namespace=
"com.mucfc.model.TicketMapper"
>
<!-- 定义数据库字段与实体对象的映射关系 -->
<resultMap type=
"Ticket"
id=
"ticketBean"
>
<id column=
"ticketId"
property=
"ticketId"
/>
<result column=
"ticketAddress"
property=
"ticketAddress"
/>
<result column=
"ticketPrice"
property=
"ticketPrice"
/>
<result column=
"ticketCId"
property=
"ticketCId"
/>
<!-- 一对一的关系 -->
<!-- property: 指的是属性的值, javaType:指的是元素的类型 -->
<association property=
"customer"
javaType=
"Customer"
>
<id column=
"customerId"
property=
"customerId"
/>
<result column=
"customerName"
property=
"customerName"
/>
<result column=
"customerTel"
property=
"customerTel"
/>
</association>
</resultMap>
<!-- 根据id查询ticket, 关联将Customer查询出来 -->
<select id=
"selectTicketById"
parameterType=
"int"
resultMap=
"ticketBean"
>
select c.*,t.* from t_customer c,t_ticket t where
c.customerId=t.ticketCId and t.ticketId =#{ticketId}
</select>
</mapper>
|
4、总配置文件
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
|
<?xml version=
"1.0"
encoding=
"UTF-8"
?>
<!DOCTYPE configuration
PUBLIC
"-//mybatis.org//DTD Config 3.0//EN"
<!-- 这是根标签 -->
<configuration>
<!-- 设置别名 -->
<typeAliases>
<typeAlias alias=
"Customer"
type=
"com.mucfc.model.Customer"
/>
<typeAlias alias=
"Ticket"
type=
"com.mucfc.model.Ticket"
/>
</typeAliases>
<!-- 配置数据源相关的信息 -->
<environments
default
=
"development"
>
<environment id=
"development"
>
<transactionManager type=
"JDBC"
/>
<dataSource type=
"POOLED"
>
<property name=
"driver"
value=
"com.mysql.jdbc.Driver"
/>
<property name=
"username"
value=
"root"
/>
<property name=
"password"
value=
"christmas258@"
/>
</dataSource>
</environment>
</environments>
<!-- 列出映射文件 -->
<mappers>
<mapper resource=
"com/mucfc/model/CustomerMapper.xml"
/>
<mapper resource=
"com/mucfc/model/TicketMapper.xml"
/>
</mappers>
</configuration>
|
5、测试
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
|
package com.mucfc.test;
import java.io.Reader;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.mucfc.model.Customer;
import com.mucfc.model.Ticket;
public
class Test {
private
static
SqlSessionFactory sqlSessionFactory;
private
static
Reader reader;
static
{
try {
reader = Resources.getResourceAsReader(
"mybatis-config.xml"
);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
e.printStackTrace();
}
}
/*
* 一对一关联查询
*/
public
static
void selectTicketById(
int
id) {
SqlSession session =
null
;
try {
session = sqlSessionFactory.openSession();
Ticket ticket = (Ticket) session.selectOne(
"com.mucfc.model.TicketMapper.selectTicketById"
, id);
if (ticket ==
null
)
System.
out
.println(
"null"
);
else
{
System.
out
.println(ticket);
System.
out
.println(ticket.getCustomer());
}
} finally {
session.
close
();
}
}
/*
* 一对多关联查询
*/
public
static
void selectCustomerByName(String string) {
SqlSession session =
null
;
try {
session = sqlSessionFactory.openSession();
Customer customer = (Customer) session
.selectOne(
"com.mucfc.model.CustomerMapper.selectCustomerByName"
,
string);
if (customer ==
null
)
System.
out
.println(
"null"
);
else
{
System.
out
.println(customer);
List<ticket> tickets = customer.getTickets();
for
(Ticket ticket : tickets) {
System.
out
.println(ticket);
}
}
} finally {
session.
close
();
}
}
public
static
void main(String[] args) {
System.
out
.println(
"==============一对一查询,根据车票来查顾客==============="
);
selectTicketById(1);
System.
out
.println(
"==============多对一查询,根据顾客来查车票==============="
);
selectCustomerByName(
"小王"
);
}
}
</ticket>
|
结果:
结果显示,查询正确。
三、ResultMap标签
MyBatis中在查询进行select映射的时候,返回类型可以用resultType,也可以用resultMap,resultType是直接表示返回类型的,而resultMap则是对外部ResultMap的引用,但是resultType跟resultMap不能同时存在。在MyBatis进行查询映射的时候,其实查询出来的每一个属性都是放在一个对应的Map里面的,其中键是属性名,值则是其对应的值。当提供的返回类型属性是resultType的时候,MyBatis会将Map里面的键值对取出赋给resultType所指定的对象对应的属性。所以其实MyBatis的每一个查询映射的返回类型都是ResultMap,只是当我们提供的返回类型属性是resultType的时候,MyBatis对自动的给我们把对应的值赋给resultType所指定对象的属性,而当我们提供的返回类型是resultMap的时候,因为Map不能很好表示领域模型,我们就需要自己再进一步的把它转化为对应的对象,这常常在复杂查询中很有作用。
当Java接口与XML文件在一个相对路径下时,可以不在myBatis配置文件的mappers中声明:
1
2
3
4
5
|
<!-- 列出映射文件 -->
<mappers>
<mapper resource=
"com/mucfc/model/CustomerMapper.xml"
/>
<mapper resource=
"com/mucfc/model/TicketMapper.xml"
/>
</mappers>
|
1
|
<mappers><mapper resource=
"com/mucfc/model/CustomerMapper.xml"
><mapper resource=
"com/mucfc/model/TicketMapper.xml"
></mapper></mapper></mappers>
|
SQL 映射XML 文件一些初级的元素:
1
2
3
4
5
6
7
8
|
1. cache – 配置给定模式的缓存
2. cache-ref – 从别的模式中引用一个缓存
3. resultMap – 这是最复杂而却强大的一个元素了,它描述如何从结果集中加载对象
4. sql – 一个可以被其他语句复用的SQL 块
5.
insert
– 映射
INSERT
语句
6.
update
– 映射
UPDATE
语句
7.
delete
– 映射DELEETE 语句
8.
select
- 映射
SELECT
语句
|
resultMap 是MyBatis 中最重要最强大的元素了。你可以让你比使用JDBC 调用结果集省掉90%的代码,也可以让你做许多JDBC 不支持的事。现实上,要写一个等同类似于交互的映射这样的复杂语句,可能要上千行的代码。ResultMaps 的目的,就是这样简单的语句而不需要多余的结果映射,更多复杂的语句,除了只要一些绝对必须的语句描述关系以外,再也不需要其它的。
resultMap属性:type为java实体类;id为此resultMap的标识。
resultMap可以设置的映射:
1
2
3
4
5
6
7
8
9
10
11
12
|
1. constructor – 用来将结果反射给一个实例化好的类的构造器
a) idArg – ID 参数;将结果集标记为ID,以方便全局调用
b) arg –反射到构造器的通常结果
2. id – ID 结果,将结果集标记为ID,以方便全局调用
3. result – 反射到JavaBean 属性的普通结果
4. association – 复杂类型的结合;多个结果合成的类型
a) nested result mappings – 几resultMap 自身嵌套关联,也可以引用到一个其它上
5. collection –复杂类型集合a collection
of
complex types
6. nested result mappings – resultMap 的集合,也可以引用到一个其它上
7. discriminator – 使用一个结果值以决定使用哪个resultMap
a)
case
– 基本一些值的结果映射的
case
情形
i. nested result mappings –一个
case
情形本身就是一个结果映射,因此也可以包括一些相同的元素,也可以引用一个外部resultMap。
|