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
动态 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();
}
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](http://img4.tbcdn.cn/L1/461/1/t_1523594034.png)
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;
}
![alt](http://img4.tbcdn.cn/L1/461/1/t_1525362352.png)
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注入风险。