文章目录
一、MyBatis是什么？
二、单元测试和JUnit4
二、MyBatis环境配置
<?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.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="org/mybatis/example/BlogMapper.xml"/>
</mappers>
</configuration>
三、SqlSessionFactory
四、MyBatis数据查询步骤
public void testSelectAll(){
SqlSession session = null;
try {
session=MyBatisUtils.openSession();
List<Goods> list = session.selectList("goods.selectAll");
for(Goods p:list){
System.out.println(p);
}
}catch (Exception e){
e.printStackTrace();
}
}
goods.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 namespace="goods">
<select id="selectAll" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods order by goods_id desc limit 10
</select>
</mapper>
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>
<!-- 开启驼峰命名映射-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<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/babytun?useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mappers/goods.xml"/>
</mappers>
</configuration>
1.SQL传参
<?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="goods">
<select id="selectAll" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods order by goods_id desc limit 10
</select>
<select id="selectById" parameterType="Integer" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods where goods_id=#{value}
</select>
<select id="selectByPriceRange" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods
where
current_price between #{min} and #{max}
order by current_price
limit 0,#{limit}
</select>
</mapper>
@Test
public void testSelectById(){
SqlSession session = null;
try {
session=MyBatisUtils.openSession();
Goods goods = session.selectOne("goods.selectById", 1602);
System.out.println(goods.getTitle());
}catch (Exception e){
e.printStackTrace();
}
}
@Test
public void testSelectByPriceRange(){
SqlSession session = null;
try {
session=MyBatisUtils.openSession();
Map param = new HashMap();
param.put("min",100);
param.put("max",500);
param.put("limit",10);
List<Goods> list = session.selectList("selectByPriceRange", param);
for (Goods p:list){
System.out.println(p.getCurrentPrice());
}
}catch (Exception e){
e.printStackTrace();
}
}
2.多表关联查询
<mapper namespace="goods">
<select id="selectAll" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods order by goods_id desc limit 10
</select>
<select id="selectById" parameterType="Integer" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods where goods_id=#{value}
</select>
<select id="selectByPriceRange" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods
where
current_price between #{min} and #{max}
order by current_price
limit 0,#{limit}
</select>
<select id="selectGoodsMap" resultType="java.util.LinkedHashMap">
select g.*,c.category_name,'1' as test from t_goods g,t_category c
where g.category_id = c.category_id
</select>
</mapper>
@Test
public void testSelectGoodsMap(){
SqlSession session = null;
try {
session=MyBatisUtils.openSession();
List<Map> goods = session.selectList("goods.selectGoodsMap");
for(Map map:goods){
System.out.println(map);
}
}catch (Exception e){
e.printStackTrace();
}
}
3.ResultMap结果映射
<resultMap id="rmGoods" type="com.imooc.mybatis.dto.GoodsDTO">
<!--设置主键字段与属性映射-->
<id property="goods.goodsId" column="goods_id"></id>
<!--设置非主键字段与属性映射 -->
<result property="goods.title" column="title"></result>
<result property="goods.originalCost" column="original_cost"></result>
<result property="goods.currentPrice" column="current_price"></result>
<result property="goods.discount" column="discount"></result>
<result property="goods.isFreeDelivery" column="is_free_delivery"></result>
<result property="goods.categoryId" column="category_id"></result>
<result property="category.categoryId" column="category_id"></result>
<result property="category.categoryName" column="category_name"></result>
<result property="category.parentId" column="parent_id"></result>
<result property="category.categoryLevel" column="category_level"></result>
<result property="category.categoryOrder" column="category_order"></result>
<result property="test" column="test"></result>
</resultMap>
<select id="selectGoodsDTO" resultMap="rmGoods">
select g.*,c.*,'1' as test from t_goods g,t_category c
where g.category_id = c.category_id
</select>
@Test
public void testSelectGoodsDTO(){
SqlSession session = null;
try {
session=MyBatisUtils.openSession();
List<GoodsDTO> goods = session.selectList("goods.selectGoodsDTO");
for(GoodsDTO p:goods){
System.out.println(p.getGoods().getTitle());
}
}catch (Exception e){
e.printStackTrace();
}
}
4.MyBatis数据写入
1.insert 新增
@Test
public void testInsert(){
SqlSession session = null;
try {
session=MyBatisUtils.openSession();
Goods goods = new Goods();
goods.setTitle("测试商品");
goods.setSubTitle("测试子标题");
goods.setOriginalCost(200f);
goods.setCurrentPrice(100f);
goods.setDiscount(0.5f);
goods.setIsFreeDelivery(1);
goods.setCategoryId(43);
int insert = session.insert("goods.insert", goods);
System.out.println(1);
session.commit();
System.out.println(goods.getGoodsId());
}catch (Exception e){
if(session!=null){
session.rollback();
}
e.printStackTrace();
}
}
<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods">
INSERT INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery)
VALUE (#{title},#{subTitle},#{originalCost},#{currentPrice},#{discount},#{isFreeDelivery})
<selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
select last_insert_id()
</selectKey>
</insert>
2.useGeneratedKeys属性
<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods" useGeneratedKeys="true" keyProperty="goodsId" keyColumn="goods_id" >
INSERT INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery)
VALUE (#{title},#{subTitle},#{originalCost},#{currentPrice},#{discount},#{isFreeDelivery})
<!--<selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
select last_insert_id()
</selectKey>-->
</insert>
3.更新和删除操作
更新
删除
@Test
public void testUpdate(){
SqlSession session = null;
try {
session=MyBatisUtils.openSession();
Goods goods = session.selectOne("goods.selectById", 739);
goods.setTitle("更新测试商品");
int num = session.update("goods.update", goods);
session.commit();
}catch (Exception e){
e.printStackTrace();
}
}
@Test
public void testDelete(){
SqlSession session = null;
try {
session=MyBatisUtils.openSession();
int num = session.delete("goods.delete", 739);
System.out.println(num);
session.commit();
}catch (Exception e){
e.printStackTrace();
}
}
<update id="update" parameterType="com.imooc.mybatis.entity.Goods">
UPDATE t_goods
SET
title=#{title},
sub_title=#{subTitle},
original_cost = #{originalCost},
current_price = #{currentPrice},
discount = #{discount},
is_free_delivery = #{isFreeDelivery},
category_id = #{categoryId}
WHERE
goods_id = #{goodsId}
</update>
<delete id="delete" parameterType="Integer">
delete from t_goods where goods_id=#{value}
</delete>
4.SQL注入攻击
5.MyBatis工作流程
五、MyBatis高级特性
1.日志管理
2.动态SQL
<select id="dynamicSQL" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods
<where>
<if test="categoryId != null">
and category_id = #{categoryId}
</if>
<if test="currentPrice != null">
and current_price < #{currentPrice}
</if>
</where>
</select>
@Test
public void testDynamicSQL(){
SqlSession session = null;
try {
session=MyBatisUtils.openSession();
Map param = new HashMap();
param.put("categoryId", 44);
param.put("currentPrice",100);
List<Goods> list = session.selectList("goods.dynamicSQL", param);
for(Goods p:list){
System.out.println(p.getCurrentPrice());
}
}catch (Exception e){
e.printStackTrace();
}
}
3.MyBatis二级缓存
<!--开启了二级缓存-->
<!--开启了二级缓存
eviction是缓存的清除策略,当缓存对象数量达到上限后,自动触发对应算法对缓存对象清除
1.LRU – 最近最久未使用:移除最长时间不被使用的对象。
O1 O2 O3 O4 .. O512
14 99 83 1 893
2.FIFO – 先进先出:按对象进入缓存的顺序来移除它们。
3.SOFT – 软引用:移除基于垃圾收集器状态和软引用规则的对象。
4.WEAK – 弱引用:更积极的移除基于垃圾收集器状态和弱引用规则的对象。
flushInterval 代表间隔多长时间自动清空缓存，单位毫秒，600000毫秒 = 10分钟
size 缓存存储上限，用于保存对象或者集合（1个集合算1个对象）的数量上限
readOnly 设置为true，代表返回只读缓存，每次从缓存取出的是缓存对象本身，这种执行效率较高
设置为false，代表每次取出的是缓存对象的“副本”，每一次取出的对象都是不同的，这种安全性较高
-->
<cache eviction="LRU" flushInterval="600000" size="512" readOnly="true"></cache>
4.多表级联查询
1.OneToMany
goods.xml
<!--
resultMap可用于说明一对多或者多对一的映射逻辑
id 是resultMap属性引用的标志
type 指向One的实体(Goods)
-->
<resultMap id="rmGoods1" type="com.imooc.mybatis.entity.Goods">
<!-- 映射goods对象的主键到goods_id字段 -->
<id column="goods_id" property="goodsId"></id>
<!--
collection的含义是,在
select * from t_goods limit 0,1 得到结果后,对所有Goods对象遍历得到goods_id字段值,
并代入到goodsDetail命名空间的selectByGoodsId的SQL中执行查询,
将得到的"商品详情"集合赋值给goodsDetails List对象.
-->
<collection property="goodsDetailList" select="goodsDetail.selectByGoodsId" column="goods_id"></collection>
</resultMap>
<select id="selectOneToMany" resultMap="rmGoods1">
select * from t_goods limit 0,1;
</select>
goods_detail.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 namespace="goodsDetail">
<select id="selectByGoodsId" parameterType="Integer" resultType="com.imooc.mybatis.entity.GoodsDetail">
select * from t_goods_detail where goods_id = #{value}
</select>
</mapper>
@Test
public void testOneToMany(){
SqlSession session = null;
try {
session=MyBatisUtils.openSession();
List<Goods> list = session.selectList("goods.selectOneToMany");
for(Goods goods:list){
System.out.println(goods.getTitle()+":"+goods.getGoodsDetailList().size());
}
}catch (Exception e){
e.printStackTrace();
}
}
5.分页插件PageHelper
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>
<!-- 开启驼峰命名映射-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!-- pagehelper配置 -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<property name="helperDialect" value="mysql"/>
<property name="reasonable" value="true"/>
</plugin>
</plugins>
<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/babytun?useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mappers/goods.xml"/>
<mapper resource="mappers/goods_detail.xml"/>
</mappers>
</configuration>
@Test
public void testSelectPage(){
SqlSession session = null;
try {
session=MyBatisUtils.openSession();
PageHelper.startPage(2,10);
Page<Goods> page = (Page) session.selectList("goods.selectPage");
System.out.println(page.getPages());
List<Goods> result = page.getResult();
for(Goods p:result){
System.out.println(p.getTitle());
}
}catch (Exception e){
e.printStackTrace();
}
}