22 SSM框架-Mybatis关联查询一对一和一对多的实现(4)
本文主要讲了使用Mybatis实现关联查询,分为一对一和一对多两种情况,最后并对ResultMap进行一个简要说明。
一、创建表、分析
下面是两表,一个是顾客表,一个是车票表。一个顾客可以对应多张车票,但是一张车票只能对应一个顾客
t_customer:顾客表,一个顾客可以对应多张车票
t_ticket:车票表,一张车票只能对应一个顾客
1、创建数据表及插入初始数据
创建数据表
- 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;
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;
插入数据:
- 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);
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);
传统的联合查询的方法
- select c.*,t.* from t_customer c JOIN t_ticket t ON (c.customerId=t.ticketCId) where c.customerName ='小王';
select c.*,t.* from t_customer c JOIN t_ticket t ON (c.customerId=t.ticketCId) where c.customerName ='小王';
结果如下:
二、工程创建
1、新建java工程,导入需要的包,最后整个工程目录 如下:
2、创建表对应的类:
Customer.java:
- 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+"]";
- }
- }
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.java:
- 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 + "]";
- }
- }
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 + "]"; }}
注意Customer.java:中有个list,list来存放车票,Ticket.java中有一个 customer。3、定义sql映射文件
(1)首先是一对多关联:
MyBatis中使用collection标签来解决一对一的关联查询,collection标签可用的属性如下:
- property:指的是集合属性的值
- ofType:指的是集合中元素的类型
- column:所对应的外键字段名称
- select:使用另一个查询封装的结果
- <?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="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>
<?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="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>
(2)接着是一对一关联:
MyBatis中使用association标签来解决一对一的关联查询,association标签可用的属性如下:
- property:对象属性的名称
- javaType:对象属性的类型
- column:所对应的外键字段名称
- select:使用另一个查询封装的结果
- <?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="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>
<?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="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、总配置文件
- <?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>
- <!-- 设置别名 -->
- <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="url" value="jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8"/>
- <property name="username" value="root"/>
- <property name="password" value="[email protected]"/>
- </dataSource>
- </environment>
- </environments>
- <!-- 列出映射文件 -->
- <mappers>
- <mapper resource="com/mucfc/model/CustomerMapper.xml" />
- <mapper resource="com/mucfc/model/TicketMapper.xml" />
- </mappers>
- </configuration>
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd"><!-- 这是根标签 --><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="url" value="jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8"/> <property name="username" value="root"/> <property name="password" value="[email protected]"/> </dataSource> </environment> </environments> <!-- 列出映射文件 --> <mappers> <mapper resource="com/mucfc/model/CustomerMapper.xml" /> <mapper resource="com/mucfc/model/TicketMapper.xml" /> </mappers></configuration>
5、测试
- 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("小王");
- }
- }
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("小王"); }}
结果:
结果显示,查询正确。
三、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中声明:
- <!-- 列出映射文件 -->
- <mappers>
- <mapper resource="com/mucfc/model/CustomerMapper.xml" />
- <mapper resource="com/mucfc/model/TicketMapper.xml" />
- </mappers>
<!-- 列出映射文件 --> <mappers> <mapper resource="com/mucfc/model/CustomerMapper.xml" /> <mapper resource="com/mucfc/model/TicketMapper.xml" /> </mappers>
SQL 映射XML 文件一些初级的元素:
- 1. cache – 配置给定模式的缓存
- 2. cache-ref – 从别的模式中引用一个缓存
- 3. resultMap – 这是最复杂而却强大的一个元素了,它描述如何从结果集中加载对象
- 4. sql – 一个可以被其他语句复用的SQL 块
- 5. insert – 映射INSERT 语句
- 6. update – 映射UPDATE 语句
- 7. delete – 映射DELEETE 语句
- 8. select - 映射SELECT语句
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. 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。
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 types6. nested result mappings – resultMap 的集合,也可以引用到一个其它上7. discriminator – 使用一个结果值以决定使用哪个resultMapa) case – 基本一些值的结果映射的case 情形i. nested result mappings –一个case 情形本身就是一个结果映射,因此也可以包括一些相同的元素,也可以引用一个外部resultMap。
本文工程免费下载