MyBatis基础入门

MyBatis基础入门

软件开发中的框架

  • 框架是可被应用开发者定制的应用骨架
  • 框架是一种规则,保证开发者遵循相同的方式开发程序
  • 框架提倡“不要重复造轮子”,对基础功能进行封装

框架的优点

  • 极大提高了开发效率
  • 统一的编码规则,有利于团队管理
  • 灵活配置的应用,拥有更好的维护性

SSM开发框架

  • S -> Spring
  • S -> Spring MVC
  • M -> MyBatis

什么是MyBatis

  • MyBatis是优秀的持久层框架
  • MyBatis使用XML将SQL与程序解耦,便于维护
  • MyBatis学习简单,执行高效,是JDBC的延展

MyBatis开发流程

  1. 引入MyBatis依赖
  2. 创建核心配置文件
  3. 创建实体(Entity)类
  4. 创建Mapper映射文件
  5. 初始化SessionFactory
  6. 利用SqlSession对象操作数据

MyBatis环境配置

mybatis-config.xml说明

  • MyBatis采用XML格式配置数据库环境信息
  • MyBatis环境配置标签
  • environment包含数据库驱动、URL、用户名于密码

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="dev">
        <!--根据环境,不同的环境不同的id,dev->开发环境 prd->生产环境 -->
        <environment id="dev">
            <!--采用JDBC方式对数据库事务进行commit/rollback-->
            <transactionManager type="JDBC"></transactionManager>
            <!--数据连接方式管理数据库连接-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
        <environment id="prd">
            <!--采用JDBC方式对数据库事务进行commit/rollback-->
            <transactionManager type="JDBC"></transactionManager>
            <!--数据连接方式管理数据库连接-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://192.168.1.1:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

创建SqlSession

SqlSessionFactory

  • SqlSessionFactory是MyBatis的核心对象
  • 用于初始化MyBatis,创建SqlSession对象
  • 保证SqlSessionFactory在应用中全局唯一

SqlSession

  • SqlSession是MyBatis操作数据库的核心对象
  • SqlSession使用JDBC方式与数据库交互
  • SqlSession对象提供了数据表CRUD对应方法

示例代码

package com.imooc.mybatis;

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.Test;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;

/**
 * JUNIT单元测试类
 * @author CubeMonkey
 * @create 2020-10-27 10:21
 */
public class MyBatisTestor {
    @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 conn = sqlSession.getConnection();
            System.out.println(conn);
        }catch (Exception e){
            e.printStackTrace();
        } finally {
            if (sqlSession != null) {
                //如果type="POOLED", 代表使用连接池,close则是将连接回收到连接池中
                //如果type="UNPOOLED",代表直连,close则会调用Connection.close()方法关闭连接
                sqlSession.close();
            }
        }
    }
}

初始化工具类MyBatisUtils

MyBatisUtils代码示例:

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 java.io.IOException;
import java.io.Reader;

/**
 * MyBatisUtils工具类,创建全局唯一的SqlSessionFactory对象
 * @author CubeMonkey
 * @create 2020-10-27 10:51
 */
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();
            //初始化错误时,通过抛出异常ExceptionInitializerError通知调用者
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * openSession 创建一个新的SqlSession对象
     * @return SqlSession对象
     */
    public static SqlSession openSession(){
        return sqlSessionFactory.openSession();
    }

    /**
     * 释放一个有效的SqlSession对象
     * @param sqlSession 准备释放sqlSession对象
     */
    public static void closeSession(SqlSession sqlSession){
        if (sqlSession != null){
            sqlSession.close();
        }
    }

}

MyBatisUtils工具类使用实例:

@Test
public void testMyBatisUtils(){
    SqlSession sqlSession = null;
    try {
        sqlSession = MyBatisUtils.openSession();
        Connection conn = sqlSession.getConnection();
        System.out.println(conn);
    }catch (Exception e){
        e.printStackTrace();
    }finally {
        MyBatisUtils.closeSession(sqlSession);
    }
}

MyBatis数据查询

简单查询

MyBatis数据查询步骤
  • 创建实体类(Entity)
  • 创建Mapper Xml
  • 编写SQL标签
  • 开启驼峰命名映射
  • 新增
  • SqlSession执行select语句
示例:
  1. 创建实体类

    Goods.java

    package com.imooc.mybatis.entity;
    
    /**
     * @author CubeMonkey
     * @create 2020-10-27 11:28
     */
    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;
        }
    }
    
    
  2. 创建Mapper Xml

  3. 编写SQL标签

    resources/mappers/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">
        <!--resultType->返回的数据类型 -->
        <select id="selectAll" resultType="com.imooc.mybatis.entity.Goods">
            select * from t_goods order by goods_id desc limit 10
        </select>
    </mapper>
    
  4. 开启驼峰命名映射

    在mybatis-config.xml中的configuration下添加settings配置

    <settings>
            <!--goods_id ==> goodsId 驼峰命名转化-->
            <setting name="mapUnderscoreToCamelCase" value="true"/>
        </settings>
    
  5. 新增

    在mybatis-config.xml中的configuration下添加mapper配置

    <mappers>
            <!--resource指的是映射文件的目录-->
            <mapper resource="mappers/goods.xml"></mapper>
        </mappers>
    
  6. SqlSession执行select语句

    MyBatisTestor部分测试代码:

    @Test
        public void testSelectAll(){
            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传参

  • 单参数传参

    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>
    

    MyBatisTestor.java部分代码

    @Test
    public void testSelectById(){
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            Goods goods = session.selectOne("goods.selectById", 1603);
            System.out.println(goods.getTitle());
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }
    
  • 多参数传参

    goods.xml

    <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>
    

    MyBatisTestor.java部分代码

    @Test
    public void testSelectByPriceRange(){
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            Map param = new HashMap<String, Integer>();
            param.put("min", 100);
            param.put("max", 500);
            param.put("limit", 10);
            List<Goods> list = session.selectList("goods.selectByPriceRange", param);
            for (Goods goods : list) {
                System.out.println(goods.getTitle());
            }
        }catch (Exception e){
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }
    

多表查询

示例(不用ResultMap结果映射):

goods.xml中添加以下代码

	<!--利用LinkedHashMap保存多表关联结果
        MyBatis会将每一条记录包装为LinkedHashMap对象
        key是字段名 value是字段对应的值,字段类型根据表结构进行自动判断
        优点:易于扩展,易于使用
        缺点:太过灵活,无法进行编译时检查
    -->
    <select id="selectGoodsMap" resultType="java.util.LinkedHashMap">
        select g.*, c.category_name from t_goods g, t_category c
        where g.category_id = c.category_id
    </select>

MyBatisTestor.java部分代码

@Test
public void testSelectGoodsMap(){
    SqlSession sqlSession = null;
    sqlSession = MyBatisUtils.openSession();
    List<Map>list = sqlSession.selectList("goods.selectGoodsMap");
    for (Map map : list) {
        System.out.println(map);
    }
}
ResultMap结果映射
  • ResultMap可以将查询结果映射为复杂类型的Java对象
  • ResultMap使用于Java对象保存多表关联结果
  • ResultMap支持对象关联查询等高级特性
tip:DTO->Data Transfer Object – 数据传输对象
使用ResultMap结果映射

编写GoodsDTO ->

package com.imooc.mybatis.dto;

import com.imooc.mybatis.entity.Category;
import com.imooc.mybatis.entity.Goods;

/**
 * @author CubeMonkey
 * @create 2020-10-27 18:50
 */
//Data Transfer Object --数据传输对象
public class GoodsDTO {
    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.java

package com.imooc.mybatis.entity;

/**
 * @author CubeMonkey
 * @create 2020-10-27 11:28
 */
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;
    }
}

Category.java

package com.imooc.mybatis.entity;

/**
 * @author CubeMonkey
 * @create 2020-10-27 19:12
 */
public class Category {
    private Integer categoryId;
    private String categoryName;
    private Integer parentId;
    private Integer categoryLevel;
    private Integer categoryOrder;

    public Integer getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }

    public String getCategoryName() {
        return categoryName;
    }

    public void setCategoryName(String categoryName) {
        this.categoryName = categoryName;
    }

    public Integer getParentId() {
        return parentId;
    }

    public void setParentId(Integer parentId) {
        this.parentId = parentId;
    }

    public Integer getCategoryLevel() {
        return categoryLevel;
    }

    public void setCategoryLevel(Integer categoryLevel) {
        this.categoryLevel = categoryLevel;
    }

    public Integer getCategoryOrder() {
        return categoryOrder;
    }

    public void setCategoryOrder(Integer categoryOrder) {
        this.categoryOrder = categoryOrder;
    }
}

在goods.xml中添加如下信息

<!--结果映射-->
    <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.categoryId" column="category_id"></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>

MyBatis的写操作

Insert操作

在goods.xml中配置如下信息

<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods">
    insert into t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id)
    values(#{title}, #{subTitle}, #{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})
    <!--更新goods中的id-->
    <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
        select last_insert_id();
    </selectKey>
</insert>

MyBatisTestor.java 测试代码

@Test
    public void testInsert() {
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            Goods goods = new Goods();
            goods.setTitle("测试用例标题");
            goods.setSubTitle("测试用例子标题");
            goods.setOriginalCost(1f);
            goods.setCurrentPrice(100f);
            goods.setDiscount(0.5f);
            goods.setIsFreeDelivery(1);
            goods.setCategoryId(12);
            int num = session.insert("goods.insert", goods);
            session.commit();//这个很重要,不能忘记
            if (num == 1) System.out.println("插入成功");
            else System.out.println("插入失败");
            System.out.println(goods.getGoodsId());
        } catch (Exception e){
            session.rollback();
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }
selectKey与useGeneratedKey区别
selectKey:
<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods">
   	INSERT INTO 语句
    <!--更新goods中的id-->
    <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
        select last_insert_id();
    </selectKey>
</insert>
useGeneratedKey:
<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods"
        useGeneratedKeys="true" keyProperty="goodsId" 	keyColumn="goods_id">
    INSERT INTO 语句
</insert>
二者区别 - 显示与隐式
  • selectKey标签需要明确编写获取最新主键的SQL语句(不同的数据库驱动获取最新主键的SQL语句不同)
  • useGeneratedKeys属性会自动根据驱动生成对应SQL语句
二者区别-应用场景不同
  • selectKey适用于所有的关系型数据库
  • useGeneratedKeys只支持“自增主键”类型的数据库
tip:

​ 不支持自增主键的数据库有:DB2, Oracle

在oracle中selectKey的用法:
<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods">
   	INSERT INTO 语句
    <!--更新goods中的id-->
    <selectKey resultType="Integer" keyProperty="goodsId" order="BEFORE">
       select seq_goods.nextval as id from dual
    </selectKey>
</insert>
总结:
  • selectKey标签是通用方案,使用于所有数据库,但编写麻烦
  • useGeneratedKeys属性只支持“自增主键”数据库,使用简单

update操作

goods.xml:

<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>

MyBatisTestor.java

@Test
    public void testUpdate(){
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            Goods goods = session.selectOne("selectById", 739);
            goods.setTitle("更新测试商品");
            int num = session.update("goods.update", goods);
            if (num == 1) System.out.println("更新成功");
            else System.out.println("更新失败");
            System.out.println(goods.getTitle());
            session.commit();
        } catch (Exception e) {
            session.rollback();
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

delete操作

goods.xml

<delete id="delete" parameterType="Integer">
	delete from t_goods where goods_id = #{value}
</delete>

MyBatisTestor.java

@Test
    public void testDelete(){
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            int num = session.delete("goods.delete", 739);
            if (num == 1) System.out.println("删除成功");
            else System.out.println("删除失败");
            session.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

MyBatis预防SQL注入攻击

SQL注入攻击

  • SQL注入是指攻击者利用SQL漏洞,绕过系统约束,越权获取数据的攻击方式

MyBatis俩种传值方式

  • ${}文本替换,未经任何处理对SQL文本替换(可以用于动态使用SQL语句,使用时绝对不能从外界输入)
  • #{}预编译传值,使用预编译传值可以预防SQL注入

MyBatis工作流程

在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Rex·Lin

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值