Mybatis基本使用入门

Mybatis入门案例
1、首先导入依赖

2、核心配置文件

<?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>
    <!--引用jdbc配置文件-->
    <properties resource="jdbc.properties"/>
    <!--实体类的别名-->
    <typeAliases>
        <!--单个类的别名-->
        <!--<typeAlias type="cn.yinsh.hello.domain.Product" alias="product"/>-->
        <!--给包的所有类设置别名:会消耗性能，但是很方便-->
        <package name="cn.yinsh.hello.domain"/>
    </typeAliases>
    <!--连接数据库-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
    	<!--注册SQL映射文件-->
        <mapper resource="cn/yinsh/hello/productMapper.xml"/>
    </mappers>
</configuration>

创建jdbc.properties配置文件,并引入

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///mybatis
jdbc.username=root
jdbc.password=123456

3、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="cn.yinsh.hello.mapper.ProductMapper">

</mapper>

4、创建实体类
5、dao层：CRUD
实现类
①未抽取工具类并没有创建mapper映射器时写法

@Override
public Product selectById(Long id) {
    Product product = null;
     try {
        //获取配置文件
        String resource = "mybatis-config.xml";
        //加载配置文件
        Reader reader = Resources.getResourceAsReader(resource);
        //创建sqlSessionFactory工厂
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //创建sqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //获取到sql
        String sql = "cn.yinsh.hello.productMapper.selectById";
        //执行sql语句
        product = sqlSession.selectOne(sql,id);
    } catch (IOException e) {
        e.printStackTrace();
    }
    return product;
}

@Override
public List<Product> selectAll() {
    List<Product> list = null;
    try {
        //获取配置文件
        String resource = "mybatis-config.xml";
        //加载配置文件
        Reader reader = Resources.getResourceAsReader(resource);
        //创建sqlSessionFactory工厂
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //创建sqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //获取到sql
        String sql = "cn.yinsh.hello.productMapper.selectAll";
        //执行sql语句
        list = sqlSession.selectList(sql);
    } catch (IOException e) {
        e.printStackTrace();
    }
    return list;

②抽取工具类后

@Override
public void insert(Product product) {
    session.insert("cn.yinsh.hello.productMapper.insert",product);
    logger.debug("productID = " + product.getId());
}

@Override
public void update(Product product) {
    session.update("cn.yinsh.hello.productMapper.update",product);
}

@Override
public void delete(Long id) {
    session.delete("cn.yinsh.hello.productMapper.delete",id);
}

@Override
public Product selectById(Long id) {
    return session.selectOne("cn.yinsh.hello.productMapper.selectById",id);
}

@Override
public List<Product> selectAll() {
    return session.selectList("cn.yinsh.hello.productMapper.selectAll");
}

③使用工具类并创建mapper映射器

@Override
public void insert(Product product) {
    SqlSession session = null;
    try {
        //获取sqlsession对象
        session = MybatisUtil.getSession();
        //获取mapper
        ProductMapper mapper = session.getMapper(ProductMapper.class);
        mapper.insert(product);
        session.commit();
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        session.close();
    }
}

@Override
public void batchInsert(List<Product> products) {
    SqlSession session = null;
    try {
        //获取sqlsession对象
        session = MybatisUtil.getSession();
        //获取mapper
        ProductMapper mapper = session.getMapper(ProductMapper.class);
        mapper.batchInsert(products);
        session.commit();
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        session.close();
    }
}

@Override
public void update(Product product) {
    SqlSession session = null;
    try {
        session = MybatisUtil.getSession();
        ProductMapper mapper = session.getMapper(ProductMapper.class);
        mapper.update(product);
        session.commit();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        session.close();
    }
}

@Override
public void delete(Long id) {
    SqlSession session = null;
    try {
        session = MybatisUtil.getSession();
        ProductMapper mapper = session.getMapper(ProductMapper.class);
        mapper.delete(id);
        session.commit();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        session.close();
    }
}

@Override
public void batchDelete(List<Long> ids) {
    SqlSession session = null;
    try {
        session =  MybatisUtil.getSession();
        ProductMapper mapper = session.getMapper(ProductMapper.class);
        mapper.batchDelete(ids);
        session.commit();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        session.close();
    }
}

@Override
public Product selectById(Long id) {
    //通过工具类获取sqlsession对象
    SqlSession session = MybatisUtil.getSession();
    //获取mapper映射器
    ProductMapper mapper = session.getMapper(ProductMapper.class);

    return mapper.selectById(id);
}

@Override
public List<Product> selectAll() {
    SqlSession session = MybatisUtil.getSession();
    ProductMapper mapper = session.getMapper(ProductMapper.class);
    return mapper.selectAll();
}

@Override
public List<Product> selectForQuery(ProductQuery productQuery) {
    SqlSession session = MybatisUtil.getSession();
    ProductMapper mapper = session.getMapper(ProductMapper.class);
    return mapper.selectForQuery(productQuery);
}

6、mapper映射器

public interface ProductMapper {
   //增
   void insert(Product product);
   //修改
   void update(Product product);
   //删
   void delete(Long id);
   void batchDelete(List<Long> ids);
   //根据Id查询
   Product selectById(Long id);
   //查询所有
   List<Product> selectAll();
   //按条件查询
   List<Product> selectForQuery(ProductQuery productQuery);
   //批量插入
   void batchInsert(List<Product> products);
}

7、Mybatis获取sqlsession工具类

public class MybatisUtil {
   private static SqlSessionFactory sqlSessionFactory = null;
   static {
       try {
           //获取配置文件
           String resource = "mybatis-config.xml";
           //加载配置文件
           Reader reader = Resources.getResourceAsReader(resource);
           //创建sqlSessionFactory工厂
           sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
       } catch (IOException e) {
           e.printStackTrace();
       }
   }
   //创建sqlSession对象
   public static SqlSession getSession(){
       return sqlSessionFactory.openSession();
   }
}

8、新增自动返回主键
① 增删改默认是有返回值的，默认返回int：当前sql影响的数据条数
② useGeneratedKeys ：自动返回主键
③ keyColumn ： 数据库中的主键列
④ keyProperty：对象中的主键属性

<insert id="insert" parameterType="product" useGeneratedKeys="true" keyColumn="id" keyProperty="id"><insert>

9、设置别名

<!--实体类的别名-->
<typeAliases>
    <!--单个类的别名-->
    <!--<typeAlias type="cn.yinsh.hello.domain.Product" alias="product"/>-->
    <!--给包的所有类设置别名:会消耗性能，但是很方便-->
    <package name="cn.yinsh.hello.domain"/>
</typeAliases>

11、结果集映射

<!--集合映射-->
<!--type="product"：可以直接使用别名，但会消耗性能-->
<resultMap id="baseResultMap" type="cn.yinsh.hello.domain.Product">
    <id column="id" property="id"/>
    <result column="productName" property="productName"/>
    <result column="dir_id" property="dir_id"/>
    <result column="salePrice" property="salePrice"/>
    <result column="supplier" property="supplier"/>
    <result column="brand" property="brand"/>
    <result column="cutoff" property="cutoff"/>
    <result column="costPrice" property="costPrice"/>
</resultMap>

<!--查询所有-->
<select id="selectAll"  resultMap="baseResultMap">
    select
      <include refid="baseColumns"/>
    from product
</select>

12、动态SQL：if&Concat&抽取公共sql&WHERE
可以在query实体类中判断productName不等于空字符串’ ',在某些版本中mybatis会把空字符串 ’ ’ 识别为数字类型，导致类型转换异常

<!--抽取公共sql-->
<sql id="baseWhere">
    <where>
        <if test="productName!=null">
            AND productName LIKE CONCAT("%",#{productName},"%")
        </if>
        <if test="brand != null">
            AND brand=#{brand}
        </if>
        <if test="salePriceMin != null">
            AND salePrice &gt;= #{salePriceMin}
        </if>
        <if test="salePriceMax != null">
            AND salePrice &lt;= #{salePriceMax}
        </if>
    </where>
</sql>

13、动态SQL：SET

<!--修改-->
<update id="update" parameterType="product">
   UPDATE product
   <set>
     <if test="productName != null">
         productName=#{productName},
     </if>
     <if test="dir_id != null">
         dir_id=#{dir_id},
     </if>
     <if test="salePrice != null">
         salePrice=#{salePrice},
     </if>
     <if test="supplier != null">
         supplier=#{supplier},
     </if>
     <if test="brand != null">
         brand=#{brand},
     </if>
     <if test="cutoff != null">
         cutoff=#{cutoff},
     </if>
     <if test="costPrice != null">
         costPrice=#{costPrice}
     </if>
   </set>
   WHERE id = #{id}
</update>

14、动态SQL：foreach：批量删除

<!--批量删除-->
<delete id="batchDelete" parameterType="long">
   DELETE FROM product WHERE id IN
     <foreach collection="list" open="(" item="id" close=")" separator=",">
         #{id}
     </foreach>
</delete>

• collection 指的是集合或者数组，这里接受两种值，如果是集合就写 “list”，如果是数组就写“array”
• open 开始元素，我们需要使用“（ ” 作为开始
• item 循环的每一个元素，这里的每一个元素就是list中的每一个id
• separator 分隔符，我们拼接后的sql需要使用“,”来分割多个ID
• close 结束元素，我们需要使用“ ）”作为结束
• #{id} 循环的内容，这里是把id的值取出来了，比如循环三次就如同 : (#{id}，#{id}，#{id})
  循环会在最前面加上 “(” , 后面加上“)” , 然后取出每个item的值即ID ，然后使用分隔符“,”进行分割，最终形成 (1,2) 这种效果。

15、动态SQL：foreach：批量插入

<!--批量新增-->
<insert id="batchInsert">
    INSERT INTO product(
    productName,
    dir_id,
    salePrice,
    supplier,
    brand,
    cutoff,
    costPrice
    )VALUES
    /*
      collection="products"
    */
    <foreach collection="list" item="product" separator=",">
        (
        #{product.productName},
        #{product.dir_id},
        #{product.salePrice},
        #{product.supplier},
        #{product.brand},
        #{product.cutoff},
        #{product.costPrice}
        )
    </foreach>
</insert>

void batchInsert(@Param("products") List<Product> products);

在接口中添加@Param后xml中的collection就可以写为

<foreach collection="products" item="product" separator=",">

16、特殊符号 “>” , "<"处理

<sql id="baseWhere">
    <where>
        <if test="productName!=null">
            AND productName LIKE CONCAT("%",#{productName},"%")
        </if>
        <if test="brand != null">
            AND brand=#{brand}
        </if>
        <if test="salePriceMin != null">
            AND salePrice &gt;= #{salePriceMin}
        </if>
        <if test="salePriceMax != null">
            AND salePrice &lt;= #{salePriceMax}
        </if>
    </where>
</sql>

17、取值表达式 #{}和KaTeX parse error: Expected 'EOF', got '#' at position 6: {}区别 #̲{}的底层就是使用的 “?”占…{}使用的是直接把值拼接到SQL中

18、sql映射文件的CRUD

<?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="cn.yinsh.hello.mapper.ProductMapper">
    
    <!--集合映射-->
    <resultMap id="baseResultMap" type="cn.yinsh.hello.domain.Product">
        <id column="id" property="id"/>
        <result column="productName" property="productName"/>
        <result column="dir_id" property="dir_id"/>
        <result column="salePrice" property="salePrice"/>
        <result column="supplier" property="supplier"/>
        <result column="brand" property="brand"/>
        <result column="cutoff" property="cutoff"/>
        <result column="costPrice" property="costPrice"/>
    </resultMap>

    <!--查询所有-->
    <select id="selectAll"  resultMap="baseResultMap">
        select
          <include refid="baseColumns"/>
        from product
    </select>

    <!--条件查询-->
    <select id="selectForQuery"  resultMap="baseResultMap" >
        select
          <include refid="baseColumns"/>
        from product
          <include refid="baseWhere"/>
    </select>

    <!--抽取字段-->
    <sql id="baseColumns">
        id,productName,dir_id,salePrice,supplier,brand,cutoff,costPrice
    </sql>

    <!--抽取公共sql:在query实体类中判断productName不等于空字符串-->
    <sql id="baseWhere">
        <where>
            <if test="productName!=null">
                AND productName LIKE CONCAT("%",#{productName},"%")
            </if>
            <if test="brand != null">
                AND brand=#{brand}
            </if>
            <if test="salePriceMin != null">
                AND salePrice &gt;= #{salePriceMin}
            </if>
            <if test="salePriceMax != null">
                AND salePrice &lt;= #{salePriceMax}
            </if>
        </where>
    </sql>

    <!--通过ID查询-->
    <select id="selectById" parameterType="long" resultMap="baseResultMap">
        select
          <include refid="baseColumns"/>
        from product WHERE id = #{id}
    </select>
    
    <!--添加-->
    <insert id="insert" parameterType="product"
        useGeneratedKeys="true" keyColumn="id" keyProperty="id">
        INSERT INTO product(
            productName,
            dir_id,
            salePrice,
            supplier,
            brand,
            cutoff,
            costPrice
        )VALUES (
            #{productName},
            #{dir_id},
            #{salePrice},
            #{supplier},
            #{brand},
            #{cutoff},
            #{costPrice}
      )
    </insert>
    <!--批量新增-->
    <insert id="batchInsert">
        INSERT INTO product(
        productName,
        dir_id,
        salePrice,
        supplier,
        brand,
        cutoff,
        costPrice
        )VALUES
        /*
          collection="products"
        */
        <foreach collection="list" item="product" separator=",">
            (

            #{product.productName},
            #{product.dir_id},
            #{product.salePrice},
            #{product.supplier},
            #{product.brand},
            #{product.cutoff},
            #{product.costPrice}

            )
        </foreach>
    </insert>

    <!--删除-->
    <delete id="delete" parameterType="long">
        DELETE FROM product WHERE id = #{id}
    </delete>

    <!--批量删除-->
    <delete id="batchDelete" parameterType="long">
        DELETE FROM product WHERE id IN
          <foreach collection="list" open="(" item="id" close=")" separator=",">
              #{id}
          </foreach>
    </delete>

    <!--修改-->
    <update id="update" parameterType="product">
        UPDATE product
        <set>
          <if test="productName != null">
              productName=#{productName},
          </if>
          <if test="dir_id != null">
              dir_id=#{dir_id},
          </if>
          <if test="salePrice != null">
              salePrice=#{salePrice},
          </if>
          <if test="supplier != null">
              supplier=#{supplier},
          </if>
          <if test="brand != null">
              brand=#{brand},
          </if>
          <if test="cutoff != null">
              cutoff=#{cutoff},
          </if>
          <if test="costPrice != null">
              costPrice=#{costPrice}
          </if>
        </set>
        WHERE id = #{id}
    </update>
</mapper>