MyBatis使用与分析
MyBatis的使用
1:MyBatis初始化对象 configuration
configuration全局性配置
<!-- 独立使用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关键类结构层次图
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的处理流程:
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注入风险。