resultMap的n+1方式实现多表查询(多对一)
实体类
创建班级类(Clazz)和学生类(Student),并在Student中添加一个Clazz类型的属性,用于表示学生的班级信息。
mapper层
提供studentMapper和ClazzMapper,studentMapper查询所有学生的信息,ClazzMapper根据 编号查询班级信息。再StudentMapper中使用<association>配置装配。
<association> 用于关联一个对象
- property:用于关联一个对象。
- select:设定要继续引用的查询,namespace+id
- column:查询时需要传递的列
service层
由于装配已经完成,serivce层只需调用mapper即可,不需要再进行装配了。
测试代码
多表关联查询_resultMap_单个对象_N+1方式实现
package com.bjsxt.mapper; import com.bjsxt.pojo.Clazz; public interface ClazzMapper { Clazz selById(int id);
}
package com.bjsxt.mapper; import java.util.List; import com.bjsxt.pojo.Student; public interface StudentMapper {
List<Student> selAll();
}
<?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.bjsxt.mapper.ClazzMapper">
<select id="selById" resultType="clazz" parameterType="int">
select * from t_class where id=#{0}
</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.bjsxt.mapper.StudentMapper">
<resultMap type="student" id="smap">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="gender" column="gender"/>
<result property="cid" column="cid"/>
<!-- 用于关联一个对象 -->
<association property="clazz" select="com.bjsxt.mapper.ClazzMapper.selById" column="cid"></association>
</resultMap>
<select id="selAll" resultMap="smap">
select * from t_student
</select> </mapper>
package com.bjsxt.pojo; import java.io.Serializable; public class Clazz implements Serializable{
private int id;
private String name;
private String room; public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + id;
result = prime * result + ((name == null) ? 0 : name.hashCode());
result = prime * result + ((room == null) ? 0 : room.hashCode());
return result;
}
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Clazz other = (Clazz) obj;
if (id != other.id)
return false;
if (name == null) {
if (other.name != null)
return false;
} else if (!name.equals(other.name))
return false;
if (room == null) {
if (other.room != null)
return false;
} else if (!room.equals(other.room))
return false;
return true;
}
public String toString() {
return "Clazz [id=" + id + ", name=" + name + ", room=" + room + "]";
}
public Clazz() {
super();
// TODO Auto-generated constructor stub
}
public Clazz(int id, String name, String room) {
super();
this.id = id;
this.name = name;
this.room = room;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getRoom() {
return room;
}
public void setRoom(String room) {
this.room = room;
} }
package com.bjsxt.pojo; import java.io.Serializable; public class Student implements Serializable{
private int id;
private String name;
private int age;
private String gender;
private int cid;
private Clazz clazz;//用于封装班级信息
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + age;
result = prime * result + cid;
result = prime * result + ((clazz == null) ? 0 : clazz.hashCode());
result = prime * result + ((gender == null) ? 0 : gender.hashCode());
result = prime * result + id;
result = prime * result + ((name == null) ? 0 : name.hashCode());
return result;
}
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Student other = (Student) obj;
if (age != other.age)
return false;
if (cid != other.cid)
return false;
if (clazz == null) {
if (other.clazz != null)
return false;
} else if (!clazz.equals(other.clazz))
return false;
if (gender == null) {
if (other.gender != null)
return false;
} else if (!gender.equals(other.gender))
return false;
if (id != other.id)
return false;
if (name == null) {
if (other.name != null)
return false;
} else if (!name.equals(other.name))
return false;
return true;
}
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age
+ ", gender=" + gender + ", cid=" + cid + ", clazz=" + clazz + "]";
}
public Student(int id, String name, int age, String gender, int cid, Clazz clazz) {
super();
this.id = id;
this.name = name;
this.age = age;
this.gender = gender;
this.cid = cid;
this.clazz = clazz;
}
public Student() {
super();
// TODO Auto-generated constructor stub
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public int getCid() {
return cid;
}
public void setCid(int cid) {
this.cid = cid;
}
public Clazz getClazz() {
return clazz;
}
public void setClazz(Clazz clazz) {
this.clazz = clazz;
} }
package com.bjsxt.service; import java.util.List; import com.bjsxt.pojo.Student; public interface StudentService {
List<Student> selAll(); }
package com.bjsxt.service.impl; import java.util.List; import org.apache.ibatis.session.SqlSession; import com.bjsxt.mapper.ClazzMapper;
import com.bjsxt.mapper.StudentMapper;
import com.bjsxt.pojo.Clazz;
import com.bjsxt.pojo.Student;
import com.bjsxt.service.StudentService;
import com.bjsxt.util.MyBatisUtil; public class StudentServiceImpl implements StudentService{ @Override
public List<Student> selAll() {
SqlSession session =MyBatisUtil.getSession(); StudentMapper stuMapper = session.getMapper(StudentMapper.class);
List<Student> list = stuMapper.selAll(); session.close();
return list;
} }
package com.bjsxt.test; import java.util.List; import com.bjsxt.pojo.Student;
import com.bjsxt.service.StudentService;
import com.bjsxt.service.impl.StudentServiceImpl; public class TestQuery { public static void main(String[] args) {
StudentService ss=new StudentServiceImpl();
List<Student> list = ss.selAll();
for (Student student : list) {
System.out.println(student);
} } }
package com.bjsxt.util; import java.io.IOException;
import java.io.InputStream; import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder; public class MyBatisUtil { private static SqlSessionFactory factory=null;
static{ try {
InputStream is=Resources.getResourceAsStream("mybatis.xml");
factory=new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} public static SqlSession getSession(){
SqlSession session =null;
if(factory!=null){
//true表示开启
session= factory.openSession(true);
}
return session;
}
}
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/java505?useSSL=true&characterEncoding=utf8&useSSL=true
jdbc.username=root
jdbc.password=root
# Set root category priority to INFO and its only appender to CONSOLE.
log4j.rootCategory=ERROR, CONSOLE
# log4j.rootCategory=DEBUG, CONSOLE, LOGFILE # 单独设置SQL语句的输出级别为DEBUG级别
log4j.logger.com.bjsxt.mapper=DEBUG # CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=- %m%n # LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.File=d:/test.log
log4j.appender.LOGFILE.Append=true
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=- %m %l%n
<?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="db.properties"/>
<settings>
<!-- 设置MyBatis使用log4j日志支持 -->
<setting name="logImpl" value="LOG4J"/>
</settings>
<!-- typeAliases给类型取别名 -->
<typeAliases>
<!-- 给user类取别名 -->
<!-- <typeAlias type="com.bjsxt.pojo.User" alias="u"/> -->
<!-- 别名都是类的名字 -->
<package name="com.bjsxt.pojo"/>
</typeAliases>
<!-- 用于指定使用哪个开发
用于指定使用的环境id
-->
<environments default="dev">
<!-- 用于配置开发环境
id:环境的唯一识别码
-->
<environment id="dev">
<!-- 事务管理器
type:用于设定mybatis采用什么方式管理事务
JDBC表示和JDBC一样事务的管理方式
-->
<transactionManager type="JDBC"/>
<!-- 数据源/连接池
用于配置链接池和数据库链接的参数
type:用于设置mybatis是否采用链接池技术
连接池:用来存数据库链接的,减少数据库的频繁开关
POOLED表示mybatis采用连接池技术
-->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!-- 扫描mapper文件 -->
<!-- 文件的全限制路径要用/ -->
<mappers>
<!-- <mapper class="com.bjsxt.mapper.UserMapper"/> -->
<package name="com.bjsxt.mapper"/> </mappers>
</configuration>
多表关联查询_resultMap_单个对象_关联方式实现
package com.bjsxt.mapper; import java.util.List; import com.bjsxt.pojo.Student; public interface StudentMapper { List<Student> selAll();
}
<?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.bjsxt.mapper.StudentMapper">
<resultMap type="student" id="smap">
<id property="id" column="sid" />
<result property="name" column="sname" />
<result property="age" column="age" />
<result property="gender" column="gender" />
<result property="cid" column="cid" />
<association property="clazz" javaType="clazz">
<id property="id" column="cid" />
<result property="name" column="cname" />
<result property="room" column="room" />
</association>
</resultMap>
<select id="selAll" resultMap="smap">
select s.id sid, s.name sname, s.age, s.gender, c.id cid, c.name cname, c.room
from t_student s
left join t_class c
on s.cid=c.id
</select>
</mapper>
package com.bjsxt.pojo; import java.io.Serializable; public class Clazz implements Serializable { private int id;
private String name;
private String room; public Clazz() {
super();
} public int getId() {
return id;
} public void setId(int id) {
this.id = id;
} public String getName() {
return name;
} public void setName(String name) {
this.name = name;
} public String getRoom() {
return room;
} public void setRoom(String room) {
this.room = room;
} @Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + id;
result = prime * result + ((name == null) ? 0 : name.hashCode());
result = prime * result + ((room == null) ? 0 : room.hashCode());
return result;
} @Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Clazz other = (Clazz) obj;
if (id != other.id)
return false;
if (name == null) {
if (other.name != null)
return false;
} else if (!name.equals(other.name))
return false;
if (room == null) {
if (other.room != null)
return false;
} else if (!room.equals(other.room))
return false;
return true;
} @Override
public String toString() {
return "Clazz [id=" + id + ", name=" + name + ", room=" + room + "]";
}
}
package com.bjsxt.pojo; import java.io.Serializable; public class Student implements Serializable { private int id;
private String name;
private int age;
private String gender;
private int cid;
private Clazz clazz;// 用于封装班级信息 public Student() {
super();
} public int getId() {
return id;
} public void setId(int id) {
this.id = id;
} public String getName() {
return name;
} public void setName(String name) {
this.name = name;
} public int getAge() {
return age;
} public void setAge(int age) {
this.age = age;
} public String getGender() {
return gender;
} public void setGender(String gender) {
this.gender = gender;
} public int getCid() {
return cid;
} public void setCid(int cid) {
this.cid = cid;
} public Clazz getClazz() {
return clazz;
} public void setClazz(Clazz clazz) {
this.clazz = clazz;
} @Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + age;
result = prime * result + cid;
result = prime * result + ((clazz == null) ? 0 : clazz.hashCode());
result = prime * result + ((gender == null) ? 0 : gender.hashCode());
result = prime * result + id;
result = prime * result + ((name == null) ? 0 : name.hashCode());
return result;
} @Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Student other = (Student) obj;
if (age != other.age)
return false;
if (cid != other.cid)
return false;
if (clazz == null) {
if (other.clazz != null)
return false;
} else if (!clazz.equals(other.clazz))
return false;
if (gender == null) {
if (other.gender != null)
return false;
} else if (!gender.equals(other.gender))
return false;
if (id != other.id)
return false;
if (name == null) {
if (other.name != null)
return false;
} else if (!name.equals(other.name))
return false;
return true;
} @Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age + ", gender=" + gender + ", cid=" + cid
+ ", clazz=" + clazz + "]";
}
}
package com.bjsxt.service; import java.util.List; import com.bjsxt.pojo.Student; public interface StudentService { List<Student> selAll();
}
package com.bjsxt.service.impl; import java.util.List; import org.apache.ibatis.session.SqlSession; import com.bjsxt.mapper.StudentMapper;
import com.bjsxt.pojo.Student;
import com.bjsxt.service.StudentService;
import com.bjsxt.util.MyBatisUtil; public class StudentServiceImpl implements StudentService { public List<Student> selAll() {
SqlSession session = MyBatisUtil.getSession(); // 学生mapper
StudentMapper stuMapper = session.getMapper(StudentMapper.class); List<Student> list = stuMapper.selAll(); session.close();
return list;
} }
package com.bjsxt.test; import java.util.List; import com.bjsxt.pojo.Student;
import com.bjsxt.service.StudentService;
import com.bjsxt.service.impl.StudentServiceImpl; public class TestQuery { public static void main(String[] args) {
StudentService ss = new StudentServiceImpl();
List<Student> list = ss.selAll();
for (Student student : list) {
System.out.println(student);
}
} }
package com.bjsxt.util; import java.io.IOException;
import java.io.InputStream; import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder; public class MyBatisUtil { private static SqlSessionFactory factory = null; static {
try {
InputStream is = Resources.getResourceAsStream("mybatis-cfg.xml");
factory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
e.printStackTrace();
}
} public static SqlSession getSession() {
SqlSession session = null;
if (factory != null) {
// true表示开启自动提交
// session = factory.openSession(true);
session = factory.openSession();
}
return session;
}
}
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/java505
jdbc.username=root
jdbc.password=root
# Set root category priority to INFO and its only appender to CONSOLE.
log4j.rootCategory=ERROR, CONSOLE
# log4j.rootCategory=DEBUG, CONSOLE, LOGFILE # 单独设置SQL语句的输出级别为DEBUG级别
log4j.logger.com.bjsxt.mapper=DEBUG # CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=- %m%n # LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.File=d:/test.log
log4j.appender.LOGFILE.Append=true
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=- %m %l%n
<?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加载外部文件 -->
<properties resource="db.properties" />
<!-- settings标签 -->
<settings>
<!-- 设置MyBatis使用log4j日志支持 -->
<setting name="logImpl" value="LOG4J"/>
</settings>
<!-- typeAliases给类型起别名 -->
<typeAliases>
<package name="com.bjsxt.pojo" />
</typeAliases>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.bjsxt.mapper" />
</mappers>
</configuration>
mybatis多表关联查询之resultMap单个对象的更多相关文章
-
三、mybatis多表关联查询和分布查询
前言 mybatis多表关联查询和懒查询,这篇文章通过一对一和一对多的实例来展示多表查询.不过需要掌握数据输出的这方面的知识.之前整理过了mybatis入门案例和mybatis数据输出,多表查询是在前 ...
-
JAVA入门[9]-mybatis多表关联查询
概要 本节要实现的是多表关联查询的简单demo.场景是根据id查询某商品分类信息,并展示该分类下的商品列表. 一.Mysql测试数据 新建表Category(商品分类)和Product(商品),并插入 ...
-
MyBatis 多表关联查询
多表关联查询 一对多 单条SQL实现. //根据部门编号查询出部门和部门成员姓名public dept selectAll() thorws Excatipon; //接口的抽象方法 下面是对应接口的 ...
-
Mybatis多表关联查询字段值覆盖问题
一.错误展示 1.首先向大家展示多表关联查询的返回结果集 <resultMap id="specialdayAndWorktimeMap type="com.hierway. ...
-
5.mybatis一对一表关联查询
方式一:嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集,封装联表查询的数据(去除重复的数据) SELECT * FROM class c,teacher t WHERE c.tid = t.t ...
-
spring boot 2使用Mybatis多表关联查询
模拟业务关系:一个用户user有对应的一个公司company,每个用户有多个账户account. spring boot 2的环境搭建见上文:spring boot 2整合mybatis 一.mysq ...
-
MyBatis学习总结(三)——多表关联查询与动态SQL
在上一章中我们学习了<MyBatis学习总结(二)——MyBatis核心配置文件与输入输出映射>,这一章主要是介绍一对一关联查询.一对多关联查询与动态SQL等内容. 一.多表关联查询 表与 ...
-
Spring Boot入门系列(十七)整合Mybatis,创建自定义mapper 实现多表关联查询!
之前讲了Springboot整合Mybatis,介绍了如何自动生成pojo实体类.mapper类和对应的mapper.xml 文件,并实现最基本的增删改查功能.mybatis 插件自动生成的mappe ...
-
MyBatis 中两表关联查询MYSQL (14)
MyBatis 中两表关联查询MYSQL 1.创建数据库表语句 2.插入测试数据 3.pom文件内容 <?xml version="1.0" encoding="U ...
随机推荐
-
Web API接口之FileReader
Web API接口之FileReader *:first-child { margin-top: 0 !important; } body>*:last-child { margin-botto ...
-
Microsoft Visual Studio 2012 文档 下载地址 vs2012 中文帮助文档
https://www.microsoft.com/zh-cn/download/confirmation.aspx?id=34794 下载地址: http://download.microsoft. ...
-
Oracle DataGuard 物理Standby 搭建(上)
物理standby database 环境搭建 Arch asysnc Oracle Dataguard host IP Oracle_sid DB_unique_name FAL_server FA ...
-
sessionapplicationStruts2中访问web元素
本文是一篇关于sessionapplication的帖子 取得Map类型request,session,application,实在类型 HttpServletRequest, HttpSession ...
-
R语言语法笔记
## 1. 数据输入 ## a$b # 数据框中的变量 a = 15 # 赋值 a <- 15 # 赋值 a = c(1,2,3,4,5) # 数组(向量) b = a[1] # 数组下标,从1 ...
-
php模板引擎技术简单实现
用了smarty,tp过后,也想了解了解其模板技术是怎么实现,于是写一个简单的模板类,大致就是读取模板文件->替换模板文件的内容->保存或者静态化 tpl.class.php主要解析 as ...
-
IOS获取来电去电来电归属系统通知达到效果(一)
这里的原理是获得到呼叫系统通知,然后根据本地呼叫电话号码,相应的电话联系. 一世. 来电显示是不是在地址簿中的联系人, 这是当第一个加入一个临时的联系人在您的电话簿(它是创建一个新的接触,并保存相 ...
-
Java利用jcifs集成AD域用户认证
近期一段时间发现AD这东西老火了,尤其是涉及到安全这一方面的,所以AD域用户认证成了如今网络安全方面的产品必备!这里就简单的分享一下,Java通过jcifs集成AD域用户实现认证,以实现网络安全! 我 ...
-
[C++项目]2048控制台游戏
#include <iostream> #include <windows.h> #include <ctime> using namespace std; ; ; ...
-
【转】安全加密(二):BLE安全攻击反制措施
本文导读 近年来出现了越来越多的低功耗蓝牙应用,即BLE(Bluetooth Low Energy),比如说智能手环.防丢器等,对蓝牙的安全要求也越来越高.这篇文章将深入浅出说明BLE4.0~4.2中 ...