第1集 MyBatis3.X的resultMap 你知道多少
简介:讲解Mybatis的resultMap讲解
Mybatis
的
SQL
语句返回结果有两种
- resultType
- 查询出的字段在相应的pojo中必须有和它相同的字段对应,或者基本数据类型
- 适合简单查询
- resultMap
- 需要⾃定义字段,或者多表查询,⼀对多等关系,⽐resultType更强⼤
- 适合复杂查询
Mapper.xml文件:
<!--定义复杂的resultMap映射-->
<resultMap id="VideoResultMap" type="Video">
<!--
id 指定查询列的唯⼀标示
column 数据库字段的名称
property pojo类的名称
-->
<!--为了测试,我们将title字段名改为video_title-->
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="video_title" property="title" jdbcType="VARCHAR"/>
<result column="summary" property="summary" jdbcType="VARCHAR"/>
<result column="cover_img" property="CoverImg" jdbcType="VARCHAR"/>
</resultMap>
<!--使用复杂类型查询-->
<select id="queryVideoById" resultMap="VideoResultMap" parameterType="Integer">
select id, title as video_title ,summary,cover_img from video
where id = #{id,jdbcType =INTEGER }
</select>
mapper接口:
Video queryVideoById(int id);
@Test
public void queryVideoByIdTest(){
//使用Resources读取配置文件
InputStream is = null;
try {
//读取resources目录下的文件
is = Resources.getResourceAsStream("MybatisConfigure.xml");
} catch (IOException e) {
e.printStackTrace();
}
if (is != null) {
//构建Session工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
VideoMapper mapper = sqlSession.getMapper(VideoMapper.class);
Video video = mapper.queryVideoById(34);
System.out.println(video.toString());
sqlSession.close();
}
}
运行结果:
第2集 ResultMap复杂对象⼀对⼀查询结果映射之association
简介:讲解Mybatis 复杂对象映射配置ResultMap的associatio
- association: 映射到POJO的某个复杂类型属性,⽐如订单order对象⾥⾯包含 user对象
案例:查询user_id为6的用户所有的订单
VideoOrder实体:订单 - 用户属于一对一关系
public class VideoOrder {
private int id;
private String OutTradeNo; //订单唯一标识
private int state; //0表示支付 1表示未支付
private int TotalFee; //支付金额
private int VideoId;
private String VideoTitle;
private String VideoImg;
//一对一 订单和用户关系
private User user;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getOutTradeNo() {
return OutTradeNo;
}
public void setOutTradeNo(String outTradeNo) {
OutTradeNo = outTradeNo;
}
public int getState() {
return state;
}
public void setState(int state) {
this.state = state;
}
public int setTotalFee() {
return TotalFee;
}
public void setTotalFee(int TotalFee) {
this.TotalFee = TotalFee;
}
public int getVideoId() {
return VideoId;
}
public void setVideoId(int videoId) {
VideoId = videoId;
}
public String getVideoTitle() {
return VideoTitle;
}
public void setVideoTitle(String videoTitle) {
VideoTitle = videoTitle;
}
public String getVideoImg() {
return VideoImg;
}
public void setVideoImg(String videoImg) {
VideoImg = videoImg;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "VideoOrder{" +
"id=" + id +
", OutTradeNo='" + OutTradeNo + '\'' +
", state=" + state +
", TotalFee='" + TotalFee + '\'' +
", VideoId=" + VideoId +
", VideoTitle='" + VideoTitle + '\'' +
", VideoImg='" + VideoImg + '\'' +
", user=" + user +
'}';
}
}
VideoOrderMapper.xml文件:
<?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.dtd是mybatis的规范化文件-->
<!--namespace是唯一的,每一个表创建一个mapper-->
<mapper namespace="Xj.com.MybatisDemo.dao.VideoOrderMapper">
<!--association:一对一关系-->
<resultMap id="VideoOrderResultMap" type="VideoOrder">
<id column="id" property="id" jdbcType="INTEGER" ></id>
<result column="out_trade_no" property="OutTradeNo" jdbcType="VARCHAR"/>
<result column="state" property="state" jdbcType="VARCHAR"/>
<result column="create_time" property="CreateTime" jdbcType="VARCHAR"/>
<result column="total_fee" property="TotalFee" jdbcType="INTEGER"/>
<result column="video_id" property="VideoId" jdbcType="INTEGER"/>
<result column="video_title" property="VideoTitle" jdbcType="VARCHAR"/>
<result column="video_img" property="VideoImg" jdbcType="VARCHAR"/>
<!--
association 配置属性⼀对⼀
property 对应videoOrder⾥⾯的user属性名
javaType 这个属性的类型
-->
<association property="user" javaType="User">
<!--可能会报错-->
<id column="user_id" property="id" jdbcType="INTEGER"></id>
<result column="name" property="name" jdbcType="VARCHAR"/>
<result column="head_img" property="HeadImg" jdbcType="VARCHAR"/>
<result column="create_time" property="CreateTime" jdbcType="TIMESTAMP"/>
<result column="phone" property="phone" jdbcType="VARCHAR"/>
</association>
</resultMap>
<!--查询某个用户一共下了多少订单-->
<!--如果不加where说明,查询订单包含所有用户的信息-->
<select id="queryVideoOrderList" resultMap="VideoOrderResultMap" >
select
o.id,
o.out_trade_no,
o.state,
o.create_time,
o.total_fee,
o.video_id,
o.video_title,
o.video_img,
u.id as user_id,
u.name,
u.head_img,
u.create_time,
u.phone
from video_order o LEFT JOIN user u
on o.user_id = u.id
<!--注意 这里用了User.id是因为在VideoOrderMapper接口中使用了@Param接口设置了为User,如果只想用id的话就不需要@Param设置参数,直接在select设置ParameresultType-->
where o.user_id = #{User.id,jdbcType = INTEGER}
</select>
</mapper>
VideoOrderMapper接口:
package Xj.com.MybatisDemo.dao;
import Xj.com.MybatisDemo.domain.User;
import Xj.com.MybatisDemo.domain.VideoOrder;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface VideoOrderMapper {
//查询某个用户一共下了多少订单
List<VideoOrder>queryVideoOrderList(@Param("User")User user);
}
// 测试查询某个用户所下的所有订单
@Test
public void VideoOrderByUserIdTest(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
VideoOrderMapper mapper = sqlSession.getMapper(VideoOrderMapper.class);
User user = new User();
user.setId(6);
//执行mapper
List<VideoOrder> videoOrders = mapper.queryVideoOrderList(user);
//输出用户6所有的下单记录
System.out.println("输出用户6所有的下单记录:"+videoOrders.toString());
}
}
运行结果:
第3集 ResultMap复杂对象⼀对多查询结果映射之collection
简介:讲解Mybatis 复杂对象⼀对多映射配置ResultMap的collection
- collection: ⼀对多查询结果查询映射,⽐如user有多个订单
User实体类:用户 - 视频订单 一对多关系
package Xj.com.MybatisDemo.domain;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String name;
private String pwd;
private String HeadImg; //头像
private String phone;
private Date CreateTime;
private List<VideoOrder> videoOrderList;
public List<VideoOrder> getVideoOrderList() {
return videoOrderList;
}
public void setVideoOrderList(List<VideoOrder> videoOrderList) {
this.videoOrderList = videoOrderList;
}
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 getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getHeadImg() {
return HeadImg;
}
public void setHeadImg(String headImg) {
HeadImg = headImg;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Date getCreateTime() {
return CreateTime;
}
public void setCreateTime(Date createTime) {
CreateTime = createTime;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", pwd='" + pwd + '\'' +
", HeadImg='" + HeadImg + '\'' +
", phone='" + phone + '\'' +
", CreateTime=" + CreateTime +
", videoOrderList=" + videoOrderList +
'}';
}
}
UserMapper.xml文件:
<?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.dtd是mybatis的规范化文件-->
<!--namespace是唯一的,每一个表创建一个mapper-->
<mapper namespace="Xj.com.MybatisDemo.dao.UserMapper">
<resultMap id="UserResultMap" type="User">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
<result column="pwd" property="pwd" jdbcType="VARCHAR"/>
<result column="head_img" property="HeadImg" jdbcType="VARCHAR"/>
<result column="phone" property="phone" jdbcType="VARCHAR"/>
<result column="create_time" property="CreateTime" jdbcType="TIMESTAMP"/>
<!--映射一个一对多-->
<collection property="videoOrderList" ofType="VideoOrder">
<id column="video_order_id" property="id" jdbcType="INTEGER"></id>
<result column="out_trade_no" property="OutTradeNo" jdbcType="VARCHAR"/>
<result column="state" property="state" jdbcType="INTEGER"/>
<result column="total_fee" property="TotalFee" jdbcType="INTEGER"/>
<result column="video_id" property="VideoId" jdbcType="INTEGER"/>
<result column="video_title" property="VideoTitle" jdbcType="VARCHAR"/>
<result column="video_img" property="VideoImg" jdbcType="VARCHAR"/>
<result column="user_id" property="UserId" jdbcType="INTEGER"/>
</collection>
</resultMap>
<select id="queryVideoOrderList" resultMap="UserResultMap">
select
u.id,
u.name,
u.head_img,
u.phone,
u.create_time,
o.id as video_order_id,
o.out_trade_no,
o.state,
o.total_fee,
o.video_id,
o.video_title,
o.video_img,
o.user_id
from user u left join video_order o
on u.id = o.user_id
</select>
</mapper>
UserMapper接口:
public interface UserMapper {
/**
* ⼀对多查询结果查询映射,⽐如user有多个订单
* 查询所有的用户的订单
* @return
*/
List<User> queryVideoOrderList();
}
测试类:
// 测试一个用户有多个订单
@Test
public void queryVideoOrderListTest(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//执行mapper
List<User> videoOrders = mapper.queryVideoOrderList();
//输出用户6所有的下单记录
System.out.println("所有用户的下单记录:"+videoOrders.toString());
}
}
运行结果:
用户6的第一个订单
第二个订单.....
第4集 Mybatis3.X ResultMap复杂对象查询总结
简介:总结ResultMap的复杂对象查询
- association 映射的是⼀个pojo类,处理⼀对⼀的关联关系。
- collection 映射的⼀个集合列表,处理的是⼀对多的关联关系。
- 模板
<!-- column不做限制,可以为任意表的字段,⽽property须为type 定义的pojo属性-->
<resultMap id="唯⼀的标识" type="映射的pojo对象">
<id column="表的主键字段,或查询语句中的别名字段" jdbcType="字段类型" property="映射
pojo对象的主键属性" />
<result column="表的⼀个字段" jdbcType="字段类型" property="映射到pojo对象的⼀个属
性"/>
<association property="pojo的⼀个对象属性" javaType="pojo关联的pojo对象">
<id column="关联pojo对象对应表的主键字段" jdbcType="字段类型" property="关联pojo
对象的属性"/>
<result column="表的字段" jdbcType="字段类型" property="关联pojo对象的属性"/>
</association>
<!-- 集合中的property 需要为oftype定义的pojo对象的属性-->
<collection property="pojo的集合属性名称" ofType="集合中单个的pojo对象类型">
<id column="集合中pojo对象对应在表的主键字段" jdbcType="字段类型" property="集合
中pojo对象的主键属性" />
<result column="任意表的字段" jdbcType="字段类型" property="集合中的pojo对象的属
性" />
</collection>
</resultMap>