Mybatis学习总结

Mybatis入门

Mybatis相关jar包

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>x.x.x</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>x.x.x</version>
</dependency>

核心配置文件

<?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>
    <!--外部配置文件引入-->
    <properties resource="JDBC.properties"></properties>
    <!--别名设置-->
    <typeAliases>
        <package name="com.narrat.pojo"/>
    </typeAliases>
  <!--数据连接池配置-->
  <!--environments和environment的id必须一致-->
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
      </dataSource>
    </environment>
  </environments>
  <!--SQL映射路径-->
  <mappers>
    <mapper resource="com/narrat/Mapper/BlogMapper.xml"/>
  </mappers>
</configuration>

Mapper.xml文件SQL语句映射

• namespace：命名空间一定不要忘记

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

<!--BlogMapper接口-->
<mapper namespace="com.narrat.Mapper.BlogMapper">
  <select id="selectBlog" resultType="Blog">
    select * from Blog where id = #{id}
  </select>
</mapper>

 Mapper接口

public interdface Mapper{
    Blog selectBlog(int id);
}

测试运行

• 数据添加/修改/删除时，需要在流关闭前，进行提交事务commit()

public  class test{
    @Test
    public  void test() throws IOException {
        InputStream in = null;
        in = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(in);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = mapper.selectBlog(1);
        System.out.println(blog);

        //sqlSession.commit();

        in.close();
        sqlSession.close();
    }
}

Select

属性：

• id：命名空间中唯一的标识符
• parameterType：传入SQL语句的参数类型
• resultType：SQL语句返回结果类型
• resultMap：SQL语句返回集合结果类型，resultType和resultMap只能同时使用一个
  • id：标记出作为 ID 的结果
  • constructor：有参构造属性注入
  • association：复杂类型的关联
  • collection：复杂类型的集合

<select id="selectPerson" parameterType="int" resultType="hashmap">
  SELECT * FROM Blog WHERE ID = #{id}
</select>

<!--constructor-->
<constructor>
   <idArg column="id" javaType="int" name="id" />
   <arg column="age" javaType="_int" name="age" />
   <arg column="username" javaType="String" name="username" />
</constructor>

<!--association-->
<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <association property="author" javaType="Author">
    <id property="id" column="author_id"/>
    <result property="username" column="author_username"/>
    <result property="password" column="author_password"/>
    <result property="email" column="author_email"/>
    <result property="bio" column="author_bio"/>
  </association>
</resultMap>

<!--collection-->
<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <collection property="posts" ofType="Post">
    <id property="id" column="post_id"/>
    <result property="subject" column="post_subject"/>
    <result property="body" column="post_body"/>
  </collection>
</resultMap>

模糊查询

<select id="selectPerson" parameterType="int" resultType="hashmap">
  SELECT * FROM Blog WHERE name like #{name}
</select>

• 拼接"%"模糊查询，不安全，存在SQL注入问题

<select id="selectPerson" parameterType="int" resultType="hashmap">
  SELECT * FROM Blog WHERE name like "%"#{name}"%"
</select>

分页查询

<select id="selectPerson" parameterType="int" resultType="hashmap">
  SELECT * FROM Blog  limit  #{start}，#{pageSize}
</select>

Insert

<insert id="insertAuthor">
  insert into Blog (id,username,password,email,bio)
  values (#{id},#{username},#{password},#{email},#{bio})
</insert>

Update

属性：

• id
• parameterType

<update id="updateAuthor">
  update Blog set
    username = #{username},
    password = #{password},
    email = #{email},
    bio = #{bio}
  where id = #{id}
</update>

Delete

<delete id="deleteAuthor">
  delete from books where id = #{id}
</delete>

动态SQL

if

<select id="findActiveBlogWithTitleLike"
     resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
</select>

where

搭配if标签使用，当前SQL语句动态条件时使用；若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

choose  when  otherwise

类似Java 中switch循环语句

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

set

set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

foreach

对集合进行遍历（尤其是在构建 IN 条件语句的时候）

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  <where>
    <foreach item="item" index="index" collection="list"
        open="ID in (" separator="," close=")" nullable="true">
          #{item}
    </foreach>
  </where>
</select>

缓存

一级缓存：本地缓存，默认开启

二级缓存

SQL映射文件配置

<cache
  eviction="FIFO"
  flushInterval="60000"
  size="512"
  readOnly="true"/>

• eviction（清除策略）：
  • LRU：最近最少使用：移除最长时间不被使用的对象。（默认）
  • FIFO：先进先出：按对象进入缓存的顺序来移除它们。
  • SOFT：软引用：基于垃圾回收器状态和软引用规则移除对象。
  • WEAK：弱引用：更积极地基于垃圾收集器状态和弱引用规则移除对象。
• lushInterval（刷新间隔）：设置为任意的正整数，以毫秒为单位，默认情况是不设置，也就是没有刷新间隔，缓存仅仅会在调用语句时刷新。
• size（引用数目）：任意正整数，默认值是 1024。
• readOnly（只读）： true 或 false，默认值是 false。

二级缓存是事务性的。当 SqlSession 完成并提交时，或是完成并回滚，但没有执行 flushCache=true 的 insert/delete/update 语句时，缓存会获得更新。

日志

Log4j

<dependency>
  <groupId>log4j</groupId>
  <artifactId>log4j</artifactId>
  <version>1.2.17</version>
</dependency>

log4j.properties配置文件

#将等级为DEBUG的日志信息输出到console和file这两个目的地，console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file
 
#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n
 
#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/kuang.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n
 
#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

Log4j2

<dependency>
  <groupId>org.apache.logging.log4j</groupId>
  <artifactId>log4j-core</artifactId>
  <version>2.x.x</version>
</dependency>

log4j2.xml配置文件

<?xml version="1.0" encoding="UTF-8"?>
<Configuration xmlns="http://logging.apache.org/log4j/2.0/config">

  <Appenders>
    <Console name="stdout" target="SYSTEM_OUT">
      <PatternLayout pattern="%5level [%t] - %msg%n"/>
    </Console>
  </Appenders>

  <Loggers>
    <Logger name="org.mybatis.example.BlogMapper" level="trace"/>
    <Root level="error" >
      <AppenderRef ref="stdout"/>
    </Root>
  </Loggers>

</Configuration>

注解开发

替代Mapper.xml映射文件的配置，直接注解在接口方法上

• @Select
• @Insert
• @Update
• @Delete

@Param

• 方法只有一个普通参数，可以不使用@Param
• 多个参数时，推荐使用@Param
• 实体类对象，不能使用@Param