MyBatis
时间:2024-05-24 星期五
学习内容
MyBatis入门使用
-
MyBatis框架介绍,介绍框架与MyBatis的概念
-
MyBatis开发流程,详细讲解MyBatis六步开发流程
-
MyBatis使用细则,讲解MyBatis开发中的各种细节
-
MyBatis工作流程,讲解MyBatis的内容执行过程
MyBatis高级特性
-
MyBatis日志管理,MyBatis日志管理配置
-
动态SQL处理,多条件下SQL的动态执行过程
-
MyBatis缓存机制,介绍MyBatis一二级缓存作用与配置
-
多表级联查询,配置MyBatis多表级联查询
-
PageHelper插件,MyBatis分页插件的使用办法
MyBatis入门
软件开发中框架
-
框架是可被应用开发者定制的应用骨架
-
框架是一种规则,保证开发者遵循相同的方式开发程序
-
框架提倡“不要重复造轮子”,对基础功能进行封装
框架的优点:
-
极大提高了开发效率
-
统一的编码规则,利于团队管理
-
灵活配置的应用,拥有更好的维护性
MyBatis框架
MyBatis是优秀的持久层框架;MyBatis使用XML将SQL与程序解耦,便于维护;MyBatis学习简单,执行高效,是JDBC的延伸
开发流程
-
引入MyBatis依赖
-
创建核心配置文件
-
创建实体(Entity)类
-
创建Mapper映射文件
-
初始化SessionFactory
-
利用SqlSession对象操作数据
MyBatis环境配置
配置文件
mybatis-config.xml文件:
-
MyBatis采用XML格式配置数据库环境信息
-
MyBatis环境配置标签<environment>
-
environment包含数据库驱动、URL、用户名与密码
-
<?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="dev"> <!-- 配置环境,不同的环境不同的id名字--> <environment id="dev"> <!-- 采用JDBC方式对数据库事务进行commit/rollback/--> <transactionManager type="JDBC"></transactionManager> <!-- 采用连接池方式管理数据库连接--> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&characterCoding=UTF-8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> <environment id="prd"> <!-- 采用JDBC方式对数据库事务进行commit/rollback/--> <transactionManager type="JDBC"></transactionManager> <!-- 采用连接池方式管理数据库连接--> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://127.0.2:3306/babytun?useUnicode=true&characterCoding=UTF-8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> </configuration>
SessionFactory和SqlSession
-
SqlSessionFactory
-
SqlSessionFactory是MyBatis的核心对象;用于初始化MyBatis,创建SqlSession对象;保证SqlSessionFactory在应用中全局唯一
-
-
SqlSession
-
SqlSession是MyBatis操作数据库的核心对象;使用JDBC方式与数据库交互;该对象提供了数据表CRUD对应方法
-
-
@Test public void testSqlSessionFactory() throws IOException { //利用Reader加载classpath下的mybatis-config.xml核心配置文件 Reader reader = Resources.getResourceAsReader("mybatis-config.xml"); //初始化SqlSessionFactory对象,同时解析mybatis-config.xml文件 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); SqlSession sqlSession = null; try { //创建sqlSession对象,SqlSession是JDBC的扩展类,用于与数据库交互 sqlSession = sqlSessionFactory.openSession(); Connection connection = sqlSession.getConnection(); System.out.println(connection); }catch (Exception e){ e.printStackTrace(); }finally { if (sqlSession != null){ //如果mybatis-config.xml配置文件中 //type="POOLED",代表使用连接池,close则是将连接回收到连接池 //type="UNPOOLED",代表直连,close则会调用Connection.close()方法关闭连接 sqlSession.close(); } }
封装Mybatis工具类
package com.imooc.mybatis.utils; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.runners.model.InitializationError; import java.io.IOException; import java.io.Reader; /** * MyBatisUtils工具类,创建全局唯一的SqlSessionFactory对象 */ public class MybatisUtils { //利用static(静态)属于类不属于对象,且全局唯一 private static SqlSessionFactory sqlSessionFactory = null;//类属性 //利用静态块在初始化类时实例化SqlSessionFactory static { Reader reader = null; try { reader = Resources.getResourceAsReader("mybatis-config.xml"); sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); } catch (IOException e) { e.printStackTrace(); //初始化错误是,抛出初始异常通知调用者 throw new ExceptionInInitializerError(e); } } //开启会话,连接数据库 public static SqlSession openSession(){ return sqlSessionFactory.openSession(); } //关闭会话,断开数据库 public static void closeSession(SqlSession session){ if (session != null){ session.close(); } } }
MyBatis数据查询
实现步骤
-
创建实体类(Entity),该类属性对应数据库表中的字段名
-
package com.imooc.mybatis.entity; //商品实体类 public class Goods { private Integer goodsId;//商品编号 private String title;//标题 private String subTitle;//子标题 private Float originalCost;//原价 private Float currentPrice;//当前价格 private Float discount;//折扣率 private Integer isFreeDelivery;//是否包邮,1-包邮 0-不包邮 private Integer categoryId;//分类编号 public Integer getGoodsId() { return goodsId; } public void setGoodsId(Integer goodsId) { this.goodsId = goodsId; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String getSubTitle() { return subTitle; } public void setSubTitle(String subTitle) { this.subTitle = subTitle; } public Float getOriginalCost() { return originalCost; } public void setOriginalCost(Float originalCost) { this.originalCost = originalCost; } public Float getCurrentPrice() { return currentPrice; } public void setCurrentPrice(Float currentPrice) { this.currentPrice = currentPrice; } public Float getDiscount() { return discount; } public void setDiscount(Float discount) { this.discount = discount; } public Integer getIsFreeDelivery() { return isFreeDelivery; } public void setIsFreeDelivery(Integer isFreeDelivery) { this.isFreeDelivery = isFreeDelivery; } public Integer getCategoryId() { return categoryId; } public void setCategoryId(Integer categoryId) { this.categoryId = categoryId; } }
-
-
创建Mapper XML,在Maven工程下的的resources包目录下新建mapper文件夹用于存放goods.xml映射文件
-
编写<select> SQL标签,在.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"> <!--对商品表数据进行操作,所以将命名空间为goods,为之后在java 程序中 sqlSession.selectList("goods.selectAll")查找sqlId为“selectAll”设置命名空间,确保能准确找到目标语句--> <mapper namespace="goods"> <!-- <select>SQL标签名为selectAll,返回的数据结果为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文件中开启驼峰映射,保证如数据库中的goods_id这样的字段名与实体(Entity)类中的goodsId这样的属性进行映射对应。
-
在mybatis-config.xml文件中,新增<mapper>
-
<?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> <!-- goods_id ==> goodsId 驼峰命名转换,使数据库字段与实体类属性进行映射 --> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> <!-- 省略mybatis环境配置内容 --> ....... <!-- 新增mapper --> <mappers> <mapper resource="mapper/goods.xml"/> </mappers> </configuration>
-
-
SqlSession执行session语句
-
//测试mybatis查询数据库 @Test public void testSelectAll() throws Exception { SqlSession session = null; try { session = MybatisUtils.openSession(); List<Goods> list = session.selectList("goods.selectAll"); for (Goods goods : list){ System.out.println(goods.getTitle()); } }catch (Exception e){ throw e; }finally { MybatisUtils.closeSession(session); } }
-
SQL传参
将mapper映射文件中的语句中的值进行参数传递,使我们能够动态的使用sql语句。
MyBatis中有两种传值方式
-
${}文本替换,未经任何处理对SQL文本替换
-
#{}预编译传值,使用预编译传值可以预防SQL注入
步骤:
-
在goods.xml文件中新增内容
-
<!-- 单参数传递时,使用parameterType指定参数的数据类型即可,SQL中#{value}提取参数 --> <select id="selectById" parameterType="Integer" resultType="com.imooc.mybatis.entity.Goods"> select * from t_goods where goods_id = #{value} </select> <!-- 多参数传递时,使用parameterType指定Map接口,SQL中#{value}提取参数 --> <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>
-
-
测试脚本
-
//测试单参数SQL传递 @Test public void testSelectById() throws Exception { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.openSession(); Goods goods = sqlSession.selectOne("goods.selectById", 868); System.out.println(goods.getGoodsId() + goods.getTitle()); }catch (Exception e){ throw e; }finally { if (sqlSession != null){ MybatisUtils.closeSession(sqlSession); } } } //多参数传递 @Test public void testselectByPriceRange() throws Exception { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.openSession(); Map map = new HashMap(); map.put("min",100); map.put("max",150); map.put("limit",10); List<Goods> goodsList = sqlSession.selectList("goods.selectByPriceRange", map); for (Goods goods: goodsList) { System.out.println(goods.getGoodsId() +" "+ goods.getTitle() +" "+ goods.getCurrentPrice()); } }catch (Exception e){ throw e; }finally { if (sqlSession != null){ MybatisUtils.closeSession(sqlSession); } } }
-
多表联合查询
步骤:
-
goods.xml文件中,新增查询多表查询SqlId和语句,返回结果类型为LinkedHashMap,以链表的方式进行存储Map。
-
<!-- 多表联合查询 --> <!-- 使用LinkedHashMap保存多表关联结果 MyBatis会将每一条纪律包装为LinkedHashMap对象 key是字段名,value时字段对应的值,字段类型根据表结构进行自动判断 优点:易于扩展,易于使用 缺点:太过灵活,无法进行编译时检查 --> <select id="selectMap" 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>
-
-
测试脚本
-
//测试多表联合查询 @Test public void testSelectMap() throws Exception { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.openSession(); List<Map> maps = sqlSession.selectList("goods.selectMap"); for (Map map: maps) { System.out.println(map); } }catch (Exception e){ throw e; }finally { if (sqlSession != null){ MybatisUtils.closeSession(sqlSession); } } }
-
ResultMap结果映射
-
ResultMap可以将查询结果映射为复杂类型的Java对象
-
适用于Java队形保存多表关联结果
-
支持对象关联查询等高级特性
步骤:
-
在java目录下新建包dto(Data Transport Object)用于存放数据传输对象类
-
package com.imooc.mybatis.dto; import com.imooc.mybatis.entity.Category; import com.imooc.mybatis.entity.Goods; public class GoodDTO { private Goods goods = new Goods();//商品实体类 private Category category = new Category();//分类实体类 private String test;//自定义属性类 public Goods getGoods() { return goods; } public void setGoods(Goods goods) { this.goods = goods; } public Category getCategory() { return category; } public void setCategory(Category category) { this.category = category; } public String getTest() { return test; } public void setTest(String test) { this.test = test; } }
-
-
在goods.xml中新增查询语句和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.subTitle" column="sub_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> <!-- 返回数据类型为GoodsDTO --> <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>
-
-
测试
-
//测试使用ResultMap映射得到的结果集 @Test public void testSelectResultMap() throws Exception { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.openSession(); List<GoodsDTO> list = sqlSession.selectList("goods.selectGoodsDTO"); for (GoodsDTO g: list) { System.out.println(g.getGoods().getTitle()); } }catch (Exception e){ throw e; }finally { if (sqlSession != null){ MybatisUtils.closeSession(sqlSession); } } }
-
MyBatis数据插入
步骤:
-
goods.xml中新增插入标签<Insert>
-
<!-- 新增数据--> <insert id="insert" parameterType="com.imooc.mybatis.entity.Goods"> insert into `babytun`.`t_goods`(`title`,`sub_title`,`original_cost`,`current_price`,`discount`,`is_free_delivery`, `category_id`) values (#{title},#{subTitle},#{originalCost},#{currentPrice},#{discount},#{isFreeDelivery},#{categoryId}) <!-- 在插入语句之后,执行下面的语句,获取最后插入的id,由于商品表的主键是自动生成的,所以需要生成后获取,以实例化对象--> <selectKey resultType="int" keyProperty="goodsId" order="AFTER"> select last_insert_id() </selectKey> </insert>
-
-
测试脚本
-
//测试insert,新增数据 @Test public void testInsert() throws Exception { Goods goods = new Goods(); goods.setTitle("测试Title"); goods.setSubTitle("测试subTitle"); goods.setOriginalCost(1000f); goods.setCurrentPrice(1200f); goods.setDiscount(0f); goods.setIsFreeDelivery(1); goods.setCategoryId(2001); SqlSession sqlSession = null; try { sqlSession = MybatisUtils.openSession(); int insertNo = sqlSession.insert("goods.insert", goods); sqlSession.commit();//提交事务 System.out.println(goods.getGoodsId()); }catch (Exception e){ sqlSession.rollback();//回滚 throw e; }finally { sqlSession.close(); } }
插入时,如果有数据库自动生成的字段的话,可以使用两种方式进行提取字段值,分别是useGeneratedKeys属性和select标签方法
<!-- 新增数据--> <!-- 在inset标签中使用useGeneratedKeys属性 --> <insert id="insertUseTwoStyle" parameterType="com.imooc.mybatis.entity.Goods" useGeneratedKeys="true" keyProperty="goodsId" keyColumn="goods_id"> insert into `babytun`.`t_goods`(`title`,`sub_title`,`original_cost`,`current_price`,`discount`,`is_free_delivery`, `category_id`) values (#{title},#{subTitle},#{originalCost},#{currentPrice},#{discount},#{isFreeDelivery},#{categoryId}) <!-- <!– 在插入语句之后,执行下面的语句,获取最后插入的id,由于商品表的主键是自动生成的,所以需要生成后获取,以实例化对象–>--> <!-- <selectKey resultType="int" keyProperty="goodsId" order="AFTER">--> <!-- select last_insert_id()--> <!-- </selectKey>--> </insert>
-
MyBatis数据删除/更新
步骤:
-
添加<delete>和<update> SQL标签语句在resources/mapper/goods.xml中
-
<!-- 删除数据 --> <delete id="delete" parameterType="Integer"> delete from t_goods where goods_id = #{value} </delete> <!-- 更新数据 --> <update id="update" parameterType="com.imooc.mybatis.entity.Goods"> update `babytun`.`t_goods` set `title` = #{title}, `sub_title` = #{subTitle} ,`category_id` = #{categoryId} ,`original_cost` = #{originalCost} ,`current_price` = #{currentPrice} ,`is_free_delivery` = #{isFreeDelivery} ,`category_id` = #{categoryId} where `goods_id` = #{goodsId} </update>
-
-
测试脚本
-
//测试mybatis删除数据操作 @Test public void testDelete() throws Exception { SqlSession sqlSession = null; try { sqlSession = MybatisUtils.openSession(); int deleteNo = sqlSession.delete("goods.delete",2676); sqlSession.commit(); System.out.println("成功删除"+deleteNo+"条数据"); }catch (Exception e){ sqlSession.rollback(); throw e; }finally { MybatisUtils.closeSession(sqlSession); } } //测试mybatis更新数据操作 @Test public void testUpdate() throws Exception { SqlSession sqlSession = null; try { Goods goods = new Goods(); goods.setCategoryId(2677); goods.setDiscount(0.1f); goods.setIsFreeDelivery(1); goods.setCurrentPrice(1000f); goods.setOriginalCost(1500f); goods.setTitle("update标题"); goods.setSubTitle("update子标题"); goods.setGoodsId(2677); sqlSession = MybatisUtils.openSession(); int updateNo = sqlSession.delete("goods.update",goods); sqlSession.commit(); System.out.println("成功更新"+updateNo+"条数据"); }catch (Exception e){ sqlSession.rollback(); throw e; }finally { MybatisUtils.closeSession(sqlSession); } }
-