MyBatis使用与分析

MyBatis的使用

1:MyBatis初始化对象 configuration

configuration全局性配置

具体属性说明见官方文档:http://mybatis.github.com/mybatis-3/zh/configuration.html

<!-- 独立使用MyBatis配置 -->
<configuration>
  <!-- 支持多套环境配置 -->
  <environments default="development">
    <environment id="development">
    <!-- 两种事务管理类型：JDBC(手动),MANAGED(Spring或JAVAEE服务器托管) -->
    <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>
    <environment id="development2">
      ……
    </environment>
  </environments>
 
 
  <settings cacheModelsEnabled="true" lazyLoadingEnabled="true" enhancementEnabled="true" 
        errorTracingEnabled="true" maxSessions="1024" maxTransactions="512" maxRequests="2048" 
        useStatementNamespaces="true" />
 
  <!-- 维护映射文件 -->
  <mappers>
      <package name="org.xiuyuan.mybatis.demo.mapper"/>
  </mappers>
</configuration>
 
<!-- 与Spring结合配置 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <!-- 配置扫描Domain的包路径 -->
    <property name="typeAliasesPackage" value="org.xiuyuan.mybatis.demo.domain"/>
    <!-- 配置扫描Mapper XML的位置 -->
    <property name="mapperLocations" value="classpath*:org/xiuyuan/mybatis/demo/mapper/*.xml"/>
    <!-- 配置mybatis配置文件的位置 -->
    <property name="configLocation" value="classpath:mybatis-config.xml"/>
    <!-- 注册类型转换器 -->
    <property name="typeHandlers">
        <list>
            <ref bean="dateIntTypeHandler"></ref>
        </list>
    </property>
</bean>

2: StatementMapper配置与使用

MyBatis 真正的着力点是在映射语句中,与JDBC相比将节省95%的代码量，对于使用StatemetMapper接口的DAO层实现，比hibernate代码还要少.

(1):SQL 映射文件结构：

cache - 配置给定命名空间的缓存。
cache-ref – 从其他命名空间引用缓存配置。
resultMap – 最复杂标签，用来描述如何从数据库结果集中来加载对象。
sql – 可以重用的 SQL 块，也可以被其他语句引用。
insert – 映射插入语句
update – 映射更新语句
delete – 映射删除语句
select – 映射查询语句

MyBatis映射SQL的3种方式

//第一种使用xml和完全限定名调用映射
//这里注意小心namespace ,parameterType ,resultType这几项配置
//sql列别名对应pojo属性名，自动映射
<mapper namespace="org.xiuyuan.mybatis.demo.mapper.BranchStatusMapper">
    <select id="getBranchStatusListById" parameterType="int" resultType="Blog">
        select
            branch_id "branchId",is_signed "isSigned",is_online "isOnline",type,
            cooperate_times "cooperateTimes",created_time "createdTime",last_modified "lastModified"
        from branch_status where branch_id = #{id}
    </select>
</mapper>
BranchStatusMapper branchStatus = (branchStatus) session.selectOne(this.getClass.getName()+".getBranchStatusListById", 101);
 
 
//第二种采用接口调用注解映射语句
public interface BranchStatusMapper {
 
    @SelectProvider(type = BrnchStatusSqlProvider.class, method = "getBrnchStatusById")  
    //或者直接写sql
    @Select("select branch_id "branchId",is_signed "isSigned",cooperate_times "cooperateTimes" from branch_status where branch_id = #{id}")
    @Options(useCache = true, flushCache = false, timeout = 10000)
    List<BranchStatus> getBranchStatusListById(Integer id);
 
} 
 
//第三种采用SQL提供类

public class BranchStatusSqlProvider {
    public String getBrnchStatusById(Map<String, Object> parameters) {
        BEGIN();
        SELECT("branch_id "branchId",is_signed "isSigned",is_online "isOnline",type,cooperate_times "cooperateTimes",created_time "createdTime",last_modified "lastModified"");
        FROM("branch_status");
        WHERE("branch_id = #{id}");
        return SQL();
    }
}

BranchStatusMapper mapper = session.getMapper(BranchStatusMapper.class);
BranchStatus branchStatus = mapper.getBranchStatusListById(101);
 
//第三种采用接口调用xml映射语句(推荐这种方式,需要spring-mybatis)
<?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="com.sankuai.meituan.crm.dao.mapper.BranchStatusMapper">
    <sql id="branch_status_column_property">
        <![CDATA[
          branch_id "branchId",is_signed "isSigned",is_online "isOnline",type,cooperate_times "cooperateTimes",created_time "createdTime",last_modified "lastModified"
        ]]>
    </sql>
</mapper>
// 接口定义
public interface BranchStatusMapper {
  
    List<BranchStatus> getBranchStatusListById(Integer id);
 
} 
<!-- 配置扫描Mapper接口的包路径 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="com.sankuai.meituan.crm.dao.mapper"/>
</bean>
//可直接在service注入,mybatis动态生成接口实现类
@Resource
private BranchStatusMapper branchStatusMapper;

XML映射细节

我推荐使用xml的方式，注解方式不便于SQL语句的修改和优化

Insert映射

//SQL片段 (抽取出公共部分供多出调用)
<sql id="partner_table_columns">
        <![CDATA[
            name, city_id, district_id, qualification_id, qualification_code, qualification_type, creator_id, create_time, modifier_id, modify_time, status, integrity, expire_date, lock_version
        ]]>
</sql>
<sql id="partner_java_property">
    <![CDATA[
        #{name}, #{cityId}, #{districtId}, #{qualificationId}, #{qualificationCode}, #{qualificationType}, #{creatorId}, #{createTime}, #{modifierId}, #{modifyTime}, #{status}, #{integrity}, #{expireDate}, #{lockVersion}
        ]]>
</sql>
 
 
//主键策略：
//如果使用的数据库支持自动生成主键，那么就可以设置 useGeneratedKeys= ”true” ，把keyProperty 设成对应的列。
<insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="Partner">
    insert into
        partner(<include refid="partner_table_columns"/> )
    values
        (<include refid="partner_java_property"/>)
</insert>

Update和delete映射

<update id="update" parameterType="BranchStatus">
    update branch_status
    <set>
        <if test="isSigned != null">is_signed=#{isSigned},</if>
        <if test="isOnline != null">is_online=#{isOnline},</if>
        <if test="type != null">type=#{type},</if>
        <if test="cooperateTimes != null">cooperate_times=#{cooperateTimes},</if>
        last_modified = unix_timestamp()
    </set>
    where
    branch_id = #{branchId}
</update>
 
<delete id="deleteBranchStatus” parameterType="int">
    delete from branch_user where branch_id = #{id}
</delete>

Select和动态sql

查询映射是使用 MyBatis 时最常用的元素,也是最复杂的映射

动态 SQL标签
if
choose(when,otherwise)
trim(where,set)
foreach

<!-- 动态映射 -->
<!-- if, where ,foreach标签,简化动态sql和格式控制成本 -->
<select id="getBranchIdsByParams" parameterType="map" resultType="int">
    select
      bs.branch_id
    from branch_status bs
    <if test="(statusList != null and statusList.size > 0) or expireTime !=null">
    inner join branch_user bu on  bu.branch_id = bs.branch_id
    </if>
    <where>
        <if test="isSigned != null">
            bs.is_signed = #{isSigned}
        </if>
        <if test="isOnline != null">
            and bs.is_online = #{isOnline}
        </if>
        <if test="statusList != null and statusList.size > 0">
             and bu.status in
            <foreach collection="statusList" index="index" item="status" open="(" separator="," close=")">
                #{status}
            </foreach>
        </if>
        <if test="expireTime != null">
            <![CDATA[
            and bu.expire_time > 0 and bu.expire_time < #{expireTime}
            ]]>
        </if>
    </where>
</select>

ResoutMap映射，处理一对一，一对多，多对多等复杂情况,

不建议大范围使用，建议把连接拆分大联接

<!-- 注意resultMap的命名 -->
<select id="selectBlogDetails" parameterType="int" resultMap="detailedBlogResultMap">
  select
       B.id as blog_id,
       B.title as blog_title,
       B.author_id as blog_author_id,
       A.id as author_id,
       A.username as author_username,
       A.password as author_password,
       A.email as author_email,
       A.bio as author_bio,
       A.favourite_section as author_favourite_section,
       P.id as post_id,
       P.blog_id as post_blog_id,
       P.author_id as post_author_id,
       P.created_on as post_created_on,
       P.section as post_section,
       P.subject as post_subject,
       P.draft as draft,
       P.body as post_body,
       C.id as comment_id,
       C.post_id as comment_post_id,
       C.name as comment_name,
       C.comment as comment_text,
       T.id as tag_id,
       T.name as tag_name
  from Blog B
       left outer join Author A on B.author_id = A.id
       left outer join Post P on B.id = P.blog_id
       left outer join Comment C on P.id = C.post_id
       left outer join Post_Tag PT on PT.post_id = P.id
       left outer join Tag T on PT.tag_id = T.id
  where B.id = #{id}
</select>
<!-- resultMap映射 -->
<resultMap id="detailedBlogResultMap" type="Blog">
  <constructor>
    <idArg column="blog_id" javaType="int"/>
  </constructor>
  <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"/>
    <result property="favouriteSection" column="author_favourite_section"/>
  </association>
  <collection property="posts" ofType="Post">
    <id property="id" column="post_id"/>
    <result property="subject" column="post_subject"/>
    <association property="author" javaType="Author"/>
    <collection property="comments" ofType=" Comment">
      <id property="id" column="comment_id"/>
    </collection>
    <collection property="tags" ofType=" Tag" >
      <id property="id" column="tag_id"/>
    </collection>
    <discriminator javaType="int" column="draft">
      <case value="1" resultType="DraftPost"/>
    </discriminator>
  </collection>
</resultMap>

MyBatis关键组件分析

1:MyBatis关键类结构层次图

alt

2:根据最简实例分析MyBatis源码:

准备工作

Mybatis完成SQL查询需要的最简代码如下：

String resource = "mybatis.cfg.xml";
Reader reader = Resources.getResourceAsReader(resource);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = ssf.openSession();
try {
    BranchUser user = (BranchUser) session.selectOne("BranchUserDao.getBranchUserById", "1");
    System.out.println(user);
} finally {
    session.close();
}

打开一个session，分析内部操作

SqlSession session = ssf.openSession();
//DefaultSqlSessionFactory的 openSession()方法内容如下：
public SqlSession openSession() {
    return openSessionFromDataSource(configuration.getDefaultExecutorType(), null, false);
}
 
//openSessionFromDataSource内部
private SqlSession openSessionFromDataSource(ExecutorType execType, TransactionIsolationLevel level, boolean autoCommit) {
    Transaction tx = null;
    try {
      //获取加载配置文件的环境信息
      final Environment environment = configuration.getEnvironment();
      //设置连接的事务信息（是否自动提交、事务级别），从配置环境中获取事务工厂，事务工厂获取一个新的事务。
      final TransactionFactory transactionFactory = getTransactionFactoryFromEnvironment(environment);
      tx = transactionFactory.newTransaction(environment.getDataSource(), level, autoCommit);
      //传入事务对象获取一个新的执行器，并传入执行器、配置信息等获取一个执行会话对象。
      final Executor executor = configuration.newExecutor(tx, execType, autoCommit);
      return new DefaultSqlSession(configuration, executor);
    } catch (Exception e) {
      closeTransaction(tx); // may have fetched a connection so lets call close()
      throw ExceptionFactory.wrapException("Error opening session.  Cause: " + e, e);
    } finally {
      ErrorContext.instance().reset();
    }
}
// 在openSessionFromDataSource代码中重点在newExecutor和DefaultSqlSession   
//newExecutor到底做了什么？
public Executor newExecutor (Transaction transaction, ExecutorType executorType){
    //判断执行器类型，如果配置文件中没有配置执行器类型，则采用默认执行类型ExecutorType.SIMPLE。
    executorType = executorType == null ? defaultExecutorType : executorType;
    executorType = executorType == null ? ExecutorType.SIMPLE : executorType;
 // 根据执行器类型返回不同类型的执行器（执行器有三种，分别是 BatchExecutor、SimpleExecutor和CachingExecutor）
    Executor executor;
    if (ExecutorType.BATCH == executorType) {
        executor = new BatchExecutor(this, transaction);
    } else if (ExecutorType.REUSE == executorType) {
        executor = new ReuseExecutor(this, transaction);
    } else {
        executor = new SimpleExecutor(this, transaction);
    }
    if (cacheEnabled) {
        executor = new CachingExecutor(executor);
    }
    //执行器绑定拦截器插件
    executor = (Executor) interceptorChain.pluginAll(executor);
    return executor;
}
//DefaultSqlSession 是做什么的？
//DefaultSqlSession实现了SqlSession接口，里面有各种各样的SQL执行方法，主要用于SQL操作的对外接口，它会的调用执行器来执行实际的SQL语句。   
 
session.selectOne("BranchUserDao.getBranchUserById", "1");
 
//selectOne方法实现
public Object selectOne (String statement, Object parameter){
  // Popular vote was to return null on 0 results and throw exception on too many.
  List list = selectList(statement, parameter);
  if (list.size() == 1) {
      return list.get(0);
  } else if (list.size() > 1) {
      throw new TooManyResultsException("Expected one result (or null) to be returned by selectOne(), but found: " + list.size());
  } else {
      return null;
  }
}
//本质上都是调用selectList实现
public List selectList (String statement, Object parameter){
    return selectList(statement, parameter, RowBounds.DEFAULT);
}
 
public List selectList (String statement, Object parameter, RowBounds rowBounds){
    try {
        //根据SQL的ID到配置信息中找对应的MappedStatement，初始化时MyBatis会将SQL块解析并放入Map<String, MappedStatement> mappedStatements 中
        //并将MappedStatement对象放到一个Map里面进行存放，Map的key值是该SQL块的ID。
        MappedStatement ms = configuration.getMappedStatement(statement);
        //调用执行器的query方法，传入MappedStatement对象、SQL参数对象、范围对象和结果处理方式。
        return executor.query(ms, wrapCollection(parameter), rowBounds, Executor.NO_RESULT_HANDLER);
    } catch (Exception e) {
        throw ExceptionFactory.wrapException("Error querying database.  Cause: " + e, e);
    } finally {
        ErrorContext.instance().reset();
    }
}
//执行器(SimpleExecutor)执行sql代码分析
public <E> List<E> doQuery(MappedStatement ms, Object parameter, RowBounds rowBounds, 
                            ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
  Statement stmt = null;
  try {
    //获取配置信息对象。
    Configuration configuration = ms.getConfiguration();
    //通过配置对象获取一个新的StatementHandler，生成结果处理对象(见下文)。
    StatementHandler handler = configuration.newStatementHandler(this, ms, parameter, rowBounds, resultHandler, boundSql);
    //预处理StatementHandler对象，得到Statement对象。
    stmt = prepareStatement(handler, ms.getStatementLog());
    //传入Statement和结果处理对象，通过StatementHandler的query方法来执行SQL，并对执行结果进行处理。
    return handler.<E>query(stmt, resultHandler);
  } finally {
    closeStatement(stmt);
  }
}
 
//newStatementHandler方法分析
public StatementHandler newStatementHandler(Executor executor, MappedStatement mappedStatement, Object parameterObject, 
                                            RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
    //根据相关的参数获取对应的StatementHandler对象。
    StatementHandler statementHandler = new RoutingStatementHandler(executor, mappedStatement, parameterObject, rowBounds, resultHandler, boundSql);
    //为StatementHandler对象绑定拦截器插件。
    statementHandler = (StatementHandler) interceptorChain.pluginAll(statementHandler);
    return statementHandler;
}
//RoutingStatementHandler构造方法分析
//根据 MappedStatement对象的StatementType来创建不同的StatementHandler，这个跟前面执行器代码类似
//StatementType有STATEMENT、PREPARED和CALLABLE三种类型，跟JDBC的Statement类型对应
public RoutingStatementHandler(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
  switch (ms.getStatementType()) {
    case STATEMENT:
      delegate = new SimpleStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql);
      break;
    case PREPARED:
      delegate = new PreparedStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql);
      break;
    case CALLABLE:
      delegate = new CallableStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql);
      break;
    default:
      throw new ExecutorException("Unknown statement type: " + ms.getStatementType());
  }
}
//最后执行prepareStatement方法，执行SQL获取结果.
private Statement prepareStatement (StatementHandler handler)throws SQLException {
    Statement stmt;
    Connection connection = transaction.getConnection();
    //从连接中获取Statement对象
    stmt = handler.prepare(connection);
    //处理预编译的传入参数
    handler.parameterize(stmt);
    return stmt;
}

MyBatis分析之缓存

1 理解MyBatis缓存

MyBatis和Hibernte一样,都提供提供了一级缓存和二级缓存

一级缓存基于 PerpetualCache 的 HashMap 本地缓存，其存储作用域为 Session，当 Session flush 或 close 之后，该Session中的所有 Cache 就将清空。

二级缓存与一级缓存其机制相同，默认也是采用 PerpetualCache，HashMap存储，但是它的存储作用域为 Mapper(Namespace)，并且可自定义存储源，如 Ehcache、Memcached等。

对于缓存数据更新机制，当某一个作用域(一级缓存Session/二级缓存Namespaces)的进行了 insert/update/delete 操作后，默认该作用域下所有 select 中的缓存将被clear。

MyBatis 的缓存采用了delegate机制及装饰器模式设计，当put、get、remove时，其中会经过多层 delegate cache 处理，其Cache类别有：BaseCache(基础缓存)、EvictionCache(排除算法缓存) 、DecoratorCache(装饰器缓存)：

MyBatis 对于其缓存Key 的生成采取规则为：[hashcode : checksum : mappedStementId : offset : limit : executeSql : queryParams] 见代码:BaseExecutor.createCacheKey

myBatis对Cache的处理流程:

alt

Executor执行器接口类结构图

BaseExecutor 执行器抽象类。实现一些通用方法，如createCacheKey 之类。采用模板模式将具体的数据库操作逻辑(doUpdate、doQuery)交由子类实现。类内部变量PerpetualCache localCache;在该类采用 PerpetualCache 实现基于 Map 存储的一级缓存，其 query 方法如下：

public List query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler) throws SQLException {
    ErrorContext.instance().resource(ms.getResource()).activity("executing a query").object(ms.getId());
    // 执行器已关闭
    if (closed) throw new ExecutorException("Executor was closed.");
    List list;
    try {
      queryStack++;
      // 创建缓存Key
      CacheKey key = createCacheKey(ms, parameter, rowBounds);
      // 从本地缓存在中获取该 key 所对应的结果集
      final List cachedList = (List) localCache.getObject(key);
      // 在缓存中找到数据
      if (cachedList != null) {
        list = cachedList;
      } else { // 未从本地缓存中找到数据，开始调用数据库查询
        //为该 key 添加一个占位标记
        localCache.putObject(key, EXECUTION_PLACEHOLDER);
        try {
          // 执行子类所实现的数据库查询 操作
          list = doQuery(ms, parameter, rowBounds, resultHandler);
        } finally {
          // 删除该 key 的占位标记
          localCache.removeObject(key);
        }
        // 将db中的数据添加至本地缓存中
        localCache.putObject(key, list);
      }
    } finally {
      queryStack--;
    }
    // 刷新当前队列中的所有 DeferredLoad实例，更新 MateObject
    if (queryStack == 0) {
      for (DeferredLoad deferredLoad : deferredLoads) {
        deferredLoad.load();
      }
    }
    return list;
  }

CachingExecutor 二级缓存执行器。使用 delegate机制。其委托执行的类是 BaseExcutor。当无法从二级缓存获取数据时，需要通过BaseExcutor中进行查询,再缓存.流程图为:

程为：从二级缓存中进行查询 -> 如果缓存中没有，交给给 BaseExecutor -> 进入一级缓存中查询 -> 如果也没有 -> 则执行 JDBC 查询，代码如下：

public List query(MappedStatement ms, Object parameterObject, RowBounds rowBounds, ResultHandler resultHandler) throws SQLException {
    if (ms != null) {
      // 获取二级缓存实例
      Cache cache = ms.getCache();
      if (cache != null) {
        flushCacheIfRequired(ms);
        // 获取 读锁( Read锁可由多个Read线程同时保持)
        cache.getReadWriteLock().readLock().lock();
        try {
          // 当前 Statement 是否启用了二级缓存
          if (ms.isUseCache()) {
            // 将创建 cache key 委托给 BaseExecutor 创建
            CacheKey key = createCacheKey(ms, parameterObject, rowBounds);
            final List cachedList = (List) cache.getObject(key);
            // 从二级缓存中找到缓存数据
            if (cachedList != null) {
              return cachedList;
            } else {
              // 未找到缓存，很委托给 BaseExecutor 执行查询
              List list = delegate.query(ms, parameterObject, rowBounds, resultHandler);
              tcm.putObject(cache, key, list);
              return list;
            }
          } else { // 没有启动用二级缓存，直接委托给 BaseExecutor 执行查询
            return delegate.query(ms, parameterObject, rowBounds, resultHandler);
          }
        } finally {
          // 当前线程释放 Read 锁
          cache.getReadWriteLock().readLock().unlock();
        }
      }
    }
    return delegate.query(ms, parameterObject, rowBounds, resultHandler);
}

MyBatis小知识

1：编码的方式提取SQL语句

Configuration configuration = sqlSession.getConfiguration();
MappedStatement ms = configuration.getMappedStatement(sqlStatementId);
BoundSql boundSql = ms.getBoundSql(param);
String sql = boundSql.getSql();

2：实现自定义类型转换(int 转Date)

//1：实现 TypeHandler转换接口
/**
 * @author zhangyijun
 * java中的Date和jdbc中的int转换
 */
//注解生命需要转换的类型
@MappedTypes(value = Date.class)
@MappedJdbcTypes(value = JdbcType.NUMERIC)
public class DateIntTypeHandler implements TypeHandler<Date> {
    @Override
    public Date getResult(CallableStatement cs, int columnIndex) throws SQLException {
        String value = cs.getString(columnIndex);
        if (StringUtil.isBlank(value)) {
            Integer time = Integer.valueOf(value);
            return DateUtil.fromUnixTime(time);
        }
        return null;
    }
    @Override
    public void setParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType) throws SQLException {
        if (parameter != null) {
            Integer second = DateUtil.date2Unixtime(parameter);
            ps.setInt(i, second);
        }
    }
    @Override
    public Date getResult(ResultSet rs, String columnName) throws SQLException {
        String value = rs.getString(columnName);
        if (StringUtil.isBlank(value)) {
            Integer time = Integer.valueOf(value);
            return DateUtil.fromUnixTime(time);
        }
        return null;
    }
    @Override
    public Date getResult(ResultSet rs, int columnIndex) throws SQLException {
        String value = rs.getString(columnIndex);
        if (StringUtil.isBlank(value)) {
            Integer time = Integer.valueOf(value);
            return DateUtil.fromUnixTime(time);
        }
        return null;
    }
}
//2:注册类型转换器
<!-- 类型转换器 -->
<bean id="dateIntTypeHandler" class="org.xiuyuan.mybatis.demo.dao.handler.DateIntTypeHandler"></bean>
<!-- 注册类型转换器 -->
<property name="typeHandlers">
    <list>
        <ref bean="dateIntTypeHandler"></ref>
        </list>
</property>
//3:声明需要类型转换的字段
#{createdTime,javaType=Date,jdbcType=NUMERIC}

3:通过Mysql数据字典生成sql_xml片段，防止写错

-- 查询branch_status所有列
select
    group_concat(column_name)
from
    information_schema.columns
where
    table_schema = 'you_database' and table_name = 'you_table'
 
-- 生成列对应的java属性
select
    group_concat(case when locate('_',column_name)>0 then concat(replace(column_name , concat('_',@shortChar:=substring(column_name,locate('_',column_name)+1,1)),upper(@shortChar))) else column_name end)
from
    information_schema.columns
where
    table_schema = 'you_database' and table_name = 'branch_status'
district_id "districtId"
 
-- 生成列+别名(属性名 如:is_online "isOnline")
select
    group_concat(case when locate('_',column_name)>0 then concat(column_name,' "',replace(column_name , concat('_',@shortChar:=substring(column_name,locate('_',column_name)+1,1)),upper(@shortChar)),'"') else column_name end)
from
    information_schema.columns
where
    table_schema = 'you_database' and table_name = 'branch_status'
-- 生成直接提取ognl表达式(如:#{branchId})
 
select
    group_concat(concat('#{',replace(column_name , concat('_',@shortChar:=substring(column_name,locate('_',column_name)+1,1)), upper(@shortChar) ),'}'))
from
    information_schema.columns
where
    table_schema = 'you_database' and table_name = 'branch_status'
 
-- 生成加入用于判空表达式,用于update(如:<if test = "name != null">name=#{name},</if>)
select
    group_concat(concat('<if test = "' , @java_column_name:=replace(column_name , concat('_',@shortChar:=substring(column_name,locate('_',column_name)+1,1)), upper(@shortChar) ) , ' != null">', column_name , '=#{',@java_column_name,'},</if>') SEPARATOR '')
from
    information_schema.columns
where
    table_schema = 'you_database' and table_name = 'branch_status'
<if test = "name != null"> and name=#{name}</if>
 
-- 生成加入用于判空表达式,用于where条件(如:<if test = "isSigned != null"> and is_signed=#{isSigned},)
select
    group_concat(concat('<if test = "' , @java_column_name:=replace(column_name , concat('_',@shortChar:=substring(column_name,locate('_',column_name)+1,1)), upper(@shortChar) ) , ' != null"> and ', column_name , '=#{', @java_column_name,'},</if>') SEPARATOR '')
from
    information_schema.columns
where
    table_schema = 'you_database' and table_name = 'branch_status'

3: 使用autoMappingBehavior参数基于OGNL映射字段:

autoMappingBehavior属性官方解释:

MyBatis 自动映射列到字段/ 属性方式。PARTIAL 只会自动映射简单, 没有嵌套的结果。FULL 会自动映射任意复杂的结果(嵌套的或其他情况)

-- 配置全局属性 autoMappingBehavior=FULL
-- 小心性能开销 TODO 测试
select u.branch_id "branchId" , s.type "branchStatus.type" ...
from branch_user u, branc_status s
where s.branch_id = u.branch_id

4:使用mapUnderscoreToCamelCase完成下划线命名到驼峰命名

-- mapUnderscoreToCamelCase =true //默认false
-- 自动映射,无需再通过列别名指定属性名
select branch_id,is_signed,is_online,type,cooperate_times,created_time,last_modified
from branch_status
where branch_id = #{id}

5: sql映射中的 # , $ 区分

使用#{}格式 MyBatis 创建预处理语句属设置安全的值(?) 。

使用${}格式直接在 SQL 语句中插入一个不改变的字符串,有sql注入风险。

posted @ 2019-04-09 16:53 TesterMa 阅读(380) 评论(0) 编辑收藏举报

刷新页面返回顶部

TesterMa

MyBatis使用与分析

MyBatis使用与分析

1:MyBatis初始化对象 configuration

2: StatementMapper配置与使用

(1):SQL 映射文件结构：

MyBatis映射SQL的3种方式

Insert映射

Update和delete映射

Select和动态sql

动态 SQL标签if choose(when,otherwise) trim(where,set) foreach

MyBatis关键组件分析

1:MyBatis关键类结构层次图

2:根据最简实例分析MyBatis源码:

MyBatis分析之缓存

MyBatis和Hibernte一样,都提供提供了一级缓存和二级缓存

MyBatis小知识

1：编码的方式提取SQL语句

2：实现自定义类型转换(int 转Date)

3:通过Mysql数据字典生成sql_xml片段，防止写错

3: 使用autoMappingBehavior参数基于OGNL映射字段:

autoMappingBehavior属性官方解释:

MyBatis 自动映射列到字段/ 属性方式。PARTIAL 只会自动映射简单, 没有嵌套的结果。FULL 会自动映射任意复杂的结果(嵌套的或其他情况)

4:使用mapUnderscoreToCamelCase完成下划线命名到驼峰命名

5: sql映射中的 # , $ 区分

公告

动态 SQL标签
if
choose(when,otherwise)
trim(where,set)
foreach