为了试验Mybatis多对一的查询，我们先在数据库中建两个表，一个城市表，一个市区表，一个城市有多个区是一个一对多的关系；多个区对应一个城市是一个多对一的关系。建表SQL及数据如下：本文地址:http://www.04007.cn/article/895.html,未经许可,不得转载.
DROP TABLE IF EXISTS `citys`;
CREATE TABLE `citys` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `citys` VALUES ('1', '北京');
INSERT INTO `citys` VALUES ('2', '上海');
DROP TABLE IF EXISTS `areas`;
CREATE TABLE `areas` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`cityid` int(20) unsigned NOT NULL,
`area` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=42 DEFAULT CHARSET=utf8;
INSERT INTO `areas` VALUES ('1', '1', '海淀区');
INSERT INTO `areas` VALUES ('35', '1', '东城区');
INSERT INTO `areas` VALUES ('18', '1', '西城区');
INSERT INTO `areas` VALUES ('37', '1', '朝阳区');
INSERT INTO `areas` VALUES ('39', '2', '黄浦区');
INSERT INTO `areas` VALUES ('40', '2', '闵行区');
INSERT INTO `areas` VALUES ('41', '2', '徐汇区');
本文地址:http://www.04007.cn/article/895.html,未经许可,不得转载.接下来我们开始新建项目及module，相关的项目依赖及配置可参见文章：http://www.04007.cn/article/894.html ，我们建立两个pojo类及两个mapper类，此时的Area pojo类中的city不再是一个字符串，而是关联city的pojo类。代码如下：本文地址:http://www.04007.cn/article/895.html,未经许可,不得转载.
//city pojo类
package com.kermit.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class City {
private int id;
private String cityname;
}
//area pojo类
package com.kermit.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Area {
private int id;
private City city;
private String areaname;
}
//AreaMapper类
package com.kermit.dao;
import com.kermit.pojo.Area;
import java.util.List;
public interface AreaMapper {
//取得市区列表
public List<Area> getArea();
}
本文地址:http://www.04007.cn/article/895.html,未经许可,不得转载.接下来我们要来编辑mybatis的mapper.xml配置文件，我们这里只是试验多对一，只需要填写AreaMapper.xml文件，这里我们使用了两种方式来实现多对一的查询，一是在association中用子查询去取得城市数据数据，另一种是是使用显示联表SQL查询后通过resultMap对应到city对象中。配置如下：本文地址:http://www.04007.cn/article/895.html,未经许可,不得转载.
<?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">
<!--绑定DAO接口-->
<mapper namespace="com.kermit.dao.AreaMapper">
<!--使用association用子查询去取得城市数据数据-->
<select id="getArea" resultMap="AreaCity">
select * from areas
</select>
<resultMap id="AreaCity" type="com.kermit.pojo.Area">
<association property="city" javaType="com.kermit.pojo.City" select="getCity" column="cityid"/>
</resultMap>
<select id="getCity" resultType="com.kermit.pojo.City">
select * from citys where id = #{cid};
</select>
<!--使用association:使用显示联表SQL查询后通过resultMap对应到city对象中-->
<select id="getArea2" resultMap="AreaCity2">
select areas.cityid,cityname,areas.id,areas.areaname from areas,citys where areas.cityid = citys.id
</select>
<resultMap id="AreaCity2" type="com.kermit.pojo.Area">
<result property="id" column="id" />
<result property="areaname" column="areaname" />
<association property="city" javaType="com.kermit.pojo.City" >
<result property="id" column="cityid" />
<result property="cityname" column="cityname" />
</association>
</resultMap>
</mapper>
本文地址:http://www.04007.cn/article/895.html,未经许可,不得转载.接下来我们去编辑测试类TestMybatis代码如下：本文地址:http://www.04007.cn/article/895.html,未经许可,不得转载.
import com.kermit.dao.AreaMapper;
import com.kermit.pojo.Area;
import com.kermit.utils.MybatisConn;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.io.IOException;
import java.util.List;
public class TestMybatis {
private static SqlSession sqlSession;
private static AreaMapper mapper;
static{
try {
sqlSession = MybatisConn.getSqlsession();
} catch (IOException e) {
e.printStackTrace();
}
mapper = sqlSession.getMapper(AreaMapper.class);
}
@Test
public void areaToCity1() {
//多对一的情况：取出区域的同时取得城市数据
List<Area> areaList = mapper.getArea();
for (Area area : areaList) {
System.out.println(area);
}
sqlSession.close();
}
@Test
public void areaToCity2(){
//多对一的查询，通过sql查询再映射实现
List<Area> areaList = mapper.getArea2();
for (Area area : areaList) {
System.out.println(area);
}
sqlSession.close();
}
}
本文地址:http://www.04007.cn/article/895.html,未经许可,不得转载.我们尝试运行两种查询，得到的结果分别如下：本文地址:http://www.04007.cn/article/895.html,未经许可,不得转载.
#areaToCity1()结果如下： Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter. PooledDataSource forcefully closed/removed all connections. PooledDataSource forcefully closed/removed all connections. PooledDataSource forcefully closed/removed all connections. PooledDataSource forcefully closed/removed all connections. Opening JDBC Connection Created connection 1293241549. Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d154ccd] ==> Preparing: select * from areas ==> Parameters: <== Columns: id, cityid, areaname <== Row: 1, 1, 海淀区 ====> Preparing: select * from citys where id = ?; ====> Parameters: 1(Long) <==== Columns: id, cityname <==== Row: 1, 北京 <==== Total: 1 <== Row: 35, 1, 东城区 <== Row: 18, 1, 西城区 <== Row: 37, 1, 朝阳区 <== Row: 39, 2, 黄浦区 ====> Preparing: select * from citys where id = ?; ====> Parameters: 2(Long) <==== Columns: id, cityname <==== Row: 2, 上海 <==== Total: 1 <== Row: 40, 2, 闵行区 <== Row: 41, 2, 徐汇区 <== Total: 7 Area(id=1, city=City(id=1, cityname=北京), areaname=海淀区) Area(id=35, city=City(id=1, cityname=北京), areaname=东城区) Area(id=18, city=City(id=1, cityname=北京), areaname=西城区) Area(id=37, city=City(id=1, cityname=北京), areaname=朝阳区) Area(id=39, city=City(id=2, cityname=上海), areaname=黄浦区) Area(id=40, city=City(id=2, cityname=上海), areaname=闵行区) Area(id=41, city=City(id=2, cityname=上海), areaname=徐汇区) Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d154ccd] Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d154ccd] Returned connection 1293241549 to pool. Process finished with exit code 0 #areaToCity2()结果如下： Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter. PooledDataSource forcefully closed/removed all connections. PooledDataSource forcefully closed/removed all connections. PooledDataSource forcefully closed/removed all connections. PooledDataSource forcefully closed/removed all connections. Opening JDBC Connection Created connection 1400856767. Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@537f60bf] ==> Preparing: select areas.cityid,cityname,areas.id,areas.areaname from areas,citys where areas.cityid = citys.id ==> Parameters: <== Columns: cityid, cityname, id, areaname <== Row: 1, 北京, 1, 海淀区 <== Row: 1, 北京, 35, 东城区 <== Row: 1, 北京, 18, 西城区 <== Row: 1, 北京, 37, 朝阳区 <== Row: 2, 上海, 39, 黄浦区 <== Row: 2, 上海, 40, 闵行区 <== Row: 2, 上海, 41, 徐汇区 <== Total: 7 Area(id=1, city=City(id=1, cityname=北京), areaname=海淀区) Area(id=35, city=City(id=1, cityname=北京), areaname=东城区) Area(id=18, city=City(id=1, cityname=北京), areaname=西城区) Area(id=37, city=City(id=1, cityname=北京), areaname=朝阳区) Area(id=39, city=City(id=2, cityname=上海), areaname=黄浦区) Area(id=40, city=City(id=2, cityname=上海), areaname=闵行区) Area(id=41, city=City(id=2, cityname=上海), areaname=徐汇区) Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@537f60bf] Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@537f60bf] Returned connection 1400856767 to pool. Process finished with exit code 0本文地址:http://www.04007.cn/article/895.html,未经许可,不得转载.
可以看到两种方式均能实现我们要的结果，但也有所不同。通过日志记录我们可以发现第一种方式areaToCity1()执行后，实际执行了3次SQL查询。而第二种方式areaToCity2()只进行了一次SQL查询操作，从性能来讲肯定是第二种更优越。本文地址:http://www.04007.cn/article/895.html,未经许可,不得转载.
本文地址:http://www.04007.cn/article/895.html 未经许可,不得转载. 手机访问本页请扫描右下方二维码.
|
|
手机扫码直接打开本页面 |
