MyBatis入门 - 掘金

开发流程

引入MyBatis依赖
创建核心配置文件
创建实体（Entity）类
创建Mapper映射文件
初始化SessionFactory
利用SqlSession对象操作数据

单元测试

单元测试是指对软件中的最小可测试单元进行检查和验证
测试用例是指编写一段代码对已有功能（方法）进行校验
JUnit 4是Java中最著名的单元测试工具，主流IDE内置支持

JUnit 4使用方法

引入JUnit jar包或增加Maven依赖
编写测试用例验证目标方法是否正确运行
在测试用例上增加@Test注解开始单元测试

使用Maven 新建项目选择Maven 下一步在pom.xml中添加依赖

 <?xml version="1.0" encoding="UTF-8"?>
 <project xmlns="http://maven.apache.org/POM/4.0.0"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
   <modelVersion>4.0.0</modelVersion>
 
   <groupId>org.example</groupId>
   <artifactId>meven-junit4</artifactId>
   <version>1.0-SNAPSHOT</version>
   //指定下载服务器
   <repositories>
     <repository>
       <id>aliyun</id>
       <name>aliyun</name>
       <url>https://maven.aliyun.com/repository/public</url>
     </repository>
   </repositories>
   //依赖
   <dependencies>
     <dependency>
       <groupId>junit</groupId>
       <artifactId>junit</artifactId>
       <version>4.12</version>
     </dependency>
   </dependencies>
 </project>

左侧出现对应包表示下载成功 引入JUnit jar包 新建Java项目新建文件夹lib将jar包导入将lib目录进入项目库中 生成测试用例类 先写一个加减乘除类

 public class Calculator {
     //加法运算
     public int add(int a , int b){
         return a + b;
     }
     //减法运算
     public int subtract(int a , int b){
         return a - b;
     }
     //乘法运算
     public int multiply(int a , int b){
         return a * b;
     }
     //除法运算
     public float divide(int a,int b){
         if(b==0){
             throw new ArithmeticException("除数不能为0");
         }
         return (a*1f) / b;
     }
 }

鼠标右键选择test 生成测试代码添加测试内容

 public class CalculatorTest {
     private Calculator cal = new Calculator();
     @Test
     public void add() {
         int result = cal.add(1, 2);
         System.out.println(result);
     }
 
     @Test
     public void subtract() {
         int result = cal.subtract(1, 2);
         System.out.println(result);
     }
 
     @Test
     public void multiply() {
         int result = cal.multiply(1, 2);
         System.out.println(result);
     }
 
     @Test
     public void divide() {
         float result = cal.divide(1, 2);
         System.out.println(result);
     }
 }

运行

MyBatis基本使用

配置文件：mybatis-config.xml MyBatis采用XML格式配置数据库环境信息 MyBatis环境配置标签 environment包含数据驱动、URL、用户名与密码 使用Maven创建新项目 添加依赖 pom.xml

 <?xml version="1.0" encoding="UTF-8"?>
 <project xmlns="http://maven.apache.org/POM/4.0.0"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
     <modelVersion>4.0.0</modelVersion>
 
     <groupId>org.example</groupId>
     <artifactId>mybatis</artifactId>
     <version>1.0-SNAPSHOT</version>
     <repositories>
         <repository>
             <id>aliyun</id>
             <name>aliyun</name>
             <url>https://maven.aliyun.com/repository/public</url>
         </repository>
     </repositories>
     <dependencies>
         <dependency>
             <groupId>org.mybatis</groupId>
             <artifactId>mybatis</artifactId>
             <version>3.5.1</version>
         </dependency>
         <dependency>
             <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
             <version>5.1.47</version>
         </dependency>
     </dependencies>
     <properties>
         <maven.compiler.source>8</maven.compiler.source>
         <maven.compiler.target>8</maven.compiler.target>
     </properties>
 
 </project>

使用IDE创数据库填写mysql信息点击Test Connection进行测试通过后点击ok 导入数据库脚本等待运行成功配置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="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&amp;characterEncoding=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://192.168.1.1:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>
                 <property name="username" value="root"/>
                 <property name="password" value="123456"/>
             </dataSource>
         </environment>
     </environments>
 </configuration>

SqlSessionFactory

SqlSessionFactory是MyBatis的核心对象用于初始化MyBatis,创建SqlSession对象保证SqlSessionFactory在应用中全局唯一 SqlSession SqlSession是MyBatis操作数据库的核心对象 SqlSession使用JDBC方式与数据库交互 SqlSession对象提供了数据表CRUD对应方法引入单元测试依赖新建包创建单元测试类编写测试代码

 package com.mybatis;
 
 import org.apache.ibatis.session.SqlSession;
 import org.apache.ibatis.session.SqlSessionFactory;
 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 import org.junit.Test;
 
 import org.apache.ibatis.io.Resources;
 
 import java.io.IOException;
 import java.io.Reader;
 import java.sql.Connection;
 
 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);
         System.out.println("sqlSessionFactory加载成功");
         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){
                 //如果type="POOLED",代表使用连接池,close则是将连接回收到连接池中
                 //如果type="UNPOOLED",代表直连,close则会调用Connection.close()方法关闭连接
                 sqlSession.close();
             }
         }
     }
 }

初始化工具类MyBatisUtils

新建工具类代码

 package com.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对象
  */
 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();
             //初始化错误时,通过抛出异常ExceptionInInitializerError通知调用者
             throw new ExceptionInInitializerError(e);
         }
     }
     /**
      * openSession 创建一个新的SqlSession对象
      * @return SqlSession对象
      */
     public static SqlSession openSession(){
         return sqlSessionFactory.openSession();
     }
 
     /**
      * 释放一个有效的SqlSession对象
      * @param session 准备释放SqlSession对象
      */
     public static void closeSession(SqlSession session){
         if(session != null){
             session.close();
         }
 
     }
 }

测试工具类 MybatisTestor.java

     /**
      * MyBatisUtils使用指南
      **/
     @Test
     public void testMyBatisUtils(){
         SqlSession sqlSession = null;
         try{
             sqlSession = MyBatisUtils.openSession();
             Connection connection =sqlSession.getConnection();
             System.out.println(connection);
         }catch (Exception e){
             throw e;
         }finally {
             MyBatisUtils.closeSession(sqlSession);
         }
     }

MyBatis数据查询

MyBatis数据查询步骤

创建实体类(Entity)
创建Mapper XML
编写 select SQL标签
开启驼峰命名映射
新增 mapper
SqlSession执行select语句

创建实体类

package com.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;
    }


}

创建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.mybatis.entity.Goods">
    select * from t_goods order by goods_id desc limit 10
  </select>
</mapper>

在mybatis-config.xml中进行声明测试 MybatisTestor.java中

@Test
    public void testSelectAll(){
    SqlSession session = null;
    try{
        session = MyBatisUtils.openSession();
        List<Goods> list= session.selectList("goods.selectAll");
        for(Goods g:list){
            System.out.println(g.getTitle());
        }
    }catch (Exception e){
        throw e;
    }finally {
        MyBatisUtils.closeSession(session);
    }
}

解决驼峰命名和分段命名不一样问题 mybatis-config.xml中添加

<settings>
  <!-- goods_id ==> goodsId 驼峰命名转换 -->
  <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

测试结果

SQL传参

查询 select 在goods..xml中添加一个select

<select id="selectById" parameterType="Integer" resultType="com.mybatis.entity.Goods">
    select * from t_goods where goods_id = #{value}
</select>

测试 MybatisTestor.java中

@Test
    public void testSelectById() throws Exception{
    SqlSession session = null;
try{
    session = MyBatisUtils.openSession();
    Goods goods= session.selectOne("goods.selectById",1602);
    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.mybatis.entity.Goods">
        select * from t_goods
        where
            current_price between #{min} and #{max}
        order by current_price
        limit 0,#{limt}
</select>

测试 MybatisTestor.java中

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

获取多表关联查询结果

利用LinkedHashMap保存多表关联结果 MyBatis会将每一条记录包装为LinkedHashMap对象 key是字段名 value是字段对应的值 , 字段类型根据表结构进行自动判断优点: 易于扩展,易于使用缺点: 太过灵活,无法进行编译时检查 goods.xml中

<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() throws Exception{
    SqlSession session = null;
try{
    session = MyBatisUtils.openSession();
    List<Map> list= session.selectList("goods.selectGoodsMap");
    for(Map g:list){
        System.out.println(g);
    }
}catch (Exception e){
    throw e;
}finally {
    MyBatisUtils.closeSession(session);
}
}

ResultMap结果映射

ResultMap可以将查询结果映射为复杂类型的Java对象
ResultMap适用于Java对象保存多表关联结果
ResultMap支持对象关联查询等高级特性

创建Category类

package com.mybatis.entity;

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

}

创建GoodsDTO类

package com.mybatis.dto;

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

public class GoodsDTO {
    private Goods goods = new Goods();
    private Category category = new Category();
    

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

goods.xml中

<!--结果映射-->
<resultMap id="rmGoods" type="com.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>
</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>

测试： MybatisTestor.java中

@Test
    public void testSelectGoodsDTO() throws Exception{
    SqlSession session = null;
try{
    session = MyBatisUtils.openSession();
    List<GoodsDTO> list= session.selectList("goods.selectGoodsDTO");
    for(GoodsDTO g:list){
        System.out.println(g.getGoods().getTitle());
    }
}catch (Exception e){
    throw e;
}finally {
    MyBatisUtils.closeSession(session);
}
}

MyBatis数据写入

数据库事务 数据库事务是保证数据操作完整性的基础数据先写入日志中后同时写入数据表如果有数据执行不成功则发生回滚要么全部成功要么全部回滚

MyBatis写操作包含三种

插入-
更新-
删除-

插入-

<insert id="insert"parameterType="com.mybatis.entity.Goods">
  INSERT INTO 'babytun'.'t goods'( 'title', 'sub_title', 'original_cost' , 'current price')
  VALUES ( #{title}, #{subTitle}, #{originalCost}, #{currentPrice})
  <selectKey resultType="int" keyProperty="goodsld" order="AFTER">
    <!-- 当前连接中最后产生的id号 -->
  select last insert id()
</selectKey>
</insert>

插入数据 goods.xml

<insert id="insert" parameterType="com.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})
  <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
    select last_insert_id()
  </selectKey>
</insert>

MybatisTestor.java

@Test
    public void testInsert() throws Exception{
    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);
    //insert()方法返回值代表本次成功插入的记录总数
    int num = session.insert("goods.insert", goods);
    session.commit();//提交事务数据
}catch (Exception e){
    if(session!=null)
        session.rollback();//回滚事务
    throw e;
}finally {
    MyBatisUtils.closeSession(session);
}
}

测试

selectKey与useGeneratedKeys的区别

selectKey标签用法

<insert id="insert" parameterType="com.mybatis.entity.Goods">
insert into sql语句
<!-- resultType主键类型  keyProperty主键属性   order在insert之前或之后执行 -->
  <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
    select last_insert_id()
  </selectKey>
</insert>

useGeneratedKeys属性用法

<insert id="insert
  parameterType="com.mybatis.entity.Goods"
  useGeneratedKeys="true"
  keyProperty="goodsId"
  keyColumn="goods_id">
  INSERT INTO SQL语句 
  </insert>

二者区别-显示与隐示

selectKey标签需要明确编写获取最新主键的SQL语句
useGeneratedKeys属性会自动根据驱动生成对应SQL语句
selectKey适用与所有的关系型数据库
useGeneratedKeys只支持"自增主键"类型的数据库

selectKey标签是通用方案，适用与所有数据库，但编写麻烦 useGeneratedKeys属性只支持“自增主键”数据库，使用简单

更新与删除操作

更新-

<update id="update" parameterType="com.mybatis.entity.Goods">
	UPDATE t_goods
  SET 'title' = #{title}
  WHERE 'goods_id'=#{goodsId}
</update>

删除-

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

更新操作 goods.xml中

<update id="update" parameterType="com.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() throws Exception{
    SqlSession session = null;
try{
    session = MyBatisUtils.openSession();
    Goods goods = session.selectOne("goods.selectById", 500);
    goods.setTitle("更新测试");
    int num=session.update("goods.update",goods);
    session.commit();//提交事务数据
}catch (Exception e){
    if(session!=null)
        session.rollback();//回滚事务
    throw e;
}finally {
    MyBatisUtils.closeSession(session);
}
}

测试结果 删除操作 goods.xml

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

MybatisTestor.java中

@Test
    public void testDelete() throws Exception{
    SqlSession session = null;
try{
    session = MyBatisUtils.openSession();
    int num=session.delete("goods.delete",739);
    session.commit();//提交事务数据
}catch (Exception e){
    if(session!=null)
        session.rollback();//回滚事务
    throw e;
}finally {
    MyBatisUtils.closeSession(session);
}
}

测试结果 ##