mybatis核心流程
- 每一个基于mybatis的应用都是建议一个sqlSessionFactory的实例为核心
- sqlSessionFactory的实例可以通过sqlSessionFactoryBuilder获得
- sqlSessionFactoryBuilder可以通过xml配置文件或预先配置好的configuration实例来构建出sqlSessionFactory实例
- 工厂设计模式里面需要获取sqlSession里面提供了在数据库执行sql命令所需的所有方法
使用流程
- 创建mybatis-config.xml配置文件
- 创建xxxMapper.xml配置文件
- 创建sqlSessionFactory创建sqlSession对象
- 用sqlSession执行crud
例子:
1.创建mybatis-config.xml配置文件
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="123"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/VideoMapper.xml"/>
</mappers>
</configuration>
2.创建mapper
<mapper namespace="net.xdclass.online_class.dao.VideoMapper">
</mapper>
3.通过创建sqlSessionFactory来创建sqlSession对象从而操作写在mapper方法
String resource = "config/mybatis-config.xml";
// 读取配置文件
InputStream inputStream = Resources.getResourceAsStream(resource);
// 构建Session工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取sqlSession
try(SqlSession sqlSession = sqlSessionFactory.openSession()) {
VideoMapper videoMapper = sqlSession.getMapper(Video.Class);
// mapper.xml中的sql方法
Video video = videoMapper.selectById();
}
1.简单查询
1.1 Mapper接口
// @Param(“别名”) mapper.xml中用
Video selectById(@Param("video_id") int videoId);
1.2 video.xml
<!--
namespace:命名空间,一般需要保持全局唯一,最好是和dao的java的接口的相对位置保持一致,可以映射sql语句 到对应的方法名称和参数,返回类型。
mybatis是使用接口动态代理
-->
<mapper namespace ="xx.xx.dao.Mapper">
<!--
statement sql
id:当前mapper下需要唯一,直接为方法名 selectById,
resultType sql查询结果集的封装 即表映射的实体类
-->
<select id ="selectById" resultType="xx.xx.xx.po"
select * from video where id = #{video_id}
</select>
</mapper>
1.3 mybatis配置驼峰字段映射java对象和数据库字段
- 利用别名
- mybatis自带配置(mybatis-config.xml顶部,有顺序性 properties在第一个)
<! -- 下划线自动映射驼峰字段 -->
<settings>
<setting name ="mapUnderscoreToCamelCase" value="true"/>
</setting>
2 Mybatis3.X复杂Sql查询
Mybatis3.X的sql语句返回结果有两种
- resultType:查询出的字段在对应的po中必须和它有相同的字段对应,或者基本数据类型,适合简单查询
- resultMap:需要自定义字段或者多表查询,一对多等关系,比resultType更强大,适合复杂查询
2.1 单表简单一对一resultMap
<resultMap id ="VideoOrderResultMap" type ="Video">
<!--
id 指定查询列的唯一标识
column 数据库字段的名称
property pojo类的名称
-->
<id column = "id" property = "id" jdbcType ="INTERGER"/>
<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 = "selectBaseFieldByIdWithResultMap" resultMap ="VideoResultMap">
select id,title as video_title ,summary,cover_id from video where id = #{vodeo_id}
</select>
2.2 单表复杂对象一对一resultMap结果映射之association(1对1连表)
- association:映射到POJO的某个复杂类型属性,比如订单order包含user对象
<resultMap id="VideoOrderResultMap" type="VideoOrder">
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="out_trade_no" property="outTradeNo"/>
<result column="create_time" property="createTime"/>
<result column="state" property="state"/>
<result column="total_fee" property="totalFee"/>
<result column="video_id" property="videoId"/>
<result column="video_title" property="videoTitle"/>
<result column="video_img" property="videoImg"/>
<!--
association 配置属性一对一
property 对应videoOrder里面的user属性名
javaType 这个属性的类型
-->
<association property="user" javaType="User">
<id property="id" column="user_id"/>
<result property="name" column="name"/>
<result property="headImg" column="head_img"/>
<result property="createTime" column="create_time"/>
<result property="phone" column="phone"/>
</association>
</resultMap>
<!--一对一管理查询订单, 订单内部包含用户属性-->
<select id="queryVideoOrderList" resultMap="VideoOrderResultMap">
select
o.id id,
o.user_id ,
o.out_trade_no,
o.create_time,
o.state,
o.total_fee,
o.video_id,
o.video_title,
o.video_img,
u.name,
u.head_img,
u.create_time,
u.phone
from video_order o left join user u on o.user_id = u.id
</select>
代码:
// resultmap association关联查询
VideoOrderMapper videoOrderMapper = sqlSession.getMapper(VideoOrderMapper.class);
List<VideoOrder> videoOrderList = videoOrderMapper.queryVideoOrderList();
System.out.println(videoOrderList.toString());
2.3 ResultMap复杂对象一对多查询结果映射之collection
<resultMap id="UserOrderResultMap" type="User">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="headImg" column="head_img"/>
<result property="createTime" column="create_time"/>
<result property="phone" column="phone"/>
<!--
property 填写pojo类中集合类属性的名称
ofType 集合里面的pojo对象
-->
<collection property="videoOrderList" ofType="VideoOrder">
<!--配置主键,管理order的唯一标识-->
<id column="order_id" property="id"/>
<result column="user_id" property="userId"/>
<result column="out_trade_no" property="outTradeNo"/>
<result column="create_time" property="createTime"/>
<result column="state" property="state"/>
<result column="total_fee" property="totalFee"/>
<result column="video_id" property="videoId"/>
<result column="video_title" property="videoTitle"/>
<result column="video_img" property="videoImg"/>
</collection>
</resultMap>
<select id="queryUserOrder" resultMap="UserOrderResultMap">
select
u.id,
u.name,
u.head_img,
u.create_time,
u.phone,
o.id order_id,
o.out_trade_no,
o.user_id,
o.create_time,
o.state,
o.total_fee,
o.video_id,
o.video_title,
o.video_img
from user u left join video_order o on u.id = o.user_id
</select>
// resultmap association关联查询
VideoOrderMapper videoOrderMapper = sqlSession.getMapper(VideoOrderMapper.class);
//resultmap collection测试
List<User> userList = videoOrderMapper.queryUserOrder();
System.out.println(userList.toString());
2.4 复杂ResultMap复杂对象查询总结
<!-- 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>
2.5 例子:三表联查
<resultMap id="VideoDetailResultMap" type="Video">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="title" jdbcType="VARCHAR" property="title"/>
<result column="summary" jdbcType="VARCHAR" property="summary"/>
<result column="cover_img" jdbcType="VARCHAR" property="coverImg"/>
<result column="price" jdbcType="INTEGER" property="price"/>
<result column="point" jdbcType="DOUBLE" property="point"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<collection property="chapterList" ofType="Chapter">
<id column="chapter_id" jdbcType="INTEGER" property="id"/>
<result column="chapter_title" jdbcType="VARCHAR" property="title"/>
<result column="ordered" jdbcType="INTEGER" property="ordered"/>
<result column="chapter_create_time" jdbcType="TIMESTAMP" property="createTime"/>
<collection property="episodeList" ofType="Episode">
<id column="episode_id" jdbcType="INTEGER" property="id"/>
<result column="num" jdbcType="INTEGER" property="num"/>
<result column="episode_title" jdbcType="VARCHAR" property="title"/>
<result column="episode_ordered" jdbcType="INTEGER" property="ordered"/>
<result column="play_url" jdbcType="VARCHAR" property="playUrl"/>
<result column="free" jdbcType="INTEGER" property="free"/>
<result column="episode_create_time" jdbcType="TIMESTAMP" property="createTime"/>
</collection>
</collection>
</resultMap>
<select id="findDetailById" resultMap="VideoDetailResultMap">
select
v.id, v.title,v.summary,v.cover_img,v.price,v.point,v.create_time,
c.id as chapter_id, c.title as chapter_title, c.ordered,c.create_time as chapter_create_time,
e.id as episode_id,e.num, e.title as episode_title,e.ordered as episode_ordered,e.play_url,e.free,e.create_time as episode_create_time
from video v
left join chapter c on v.id=c.video_id
left join episode e on c.id= e.chapter_id
where v.id = #{video_id}
order by c.ordered,e.num asc
</select>
接口:
Video findDetailById(@Param("video_id" int videoId);
3mybatis的缓存和懒加载
1.一级缓存是sqlSession级别,同一个sqlSession中执行相同的sql第一次回查数据库,并写入缓存,第二次直接从缓存中取,两此查询中间发生了增加,删除,修改,则缓存失效。
2.二级缓存是namespace级别,第一次调用某个namespace下的sql查询数据,查询数据回放在该mapper对应的二级缓存中,第二次调用同一个namespace的mapper映射文件,会去对应的二级缓存中取结果。失效策略是执行同个namespace下的mapper映射文件时,出现crud时,并执行了commit操作胡,会清空该二级缓存,实现二级缓存的时候,,po是可序列化的,建议实现serializabale.默认是最近最少实现lru算法。
懒加载是先从单表查询,需要时再从关联表中查询,resultMap的association和collection有延迟加载的功能