mybatis 使用经验小结

一、多数据源问题

主要思路是把dataSource、sqlSesstionFactory、MapperScannerConfigurer在配置中区分开,各Mapper对应的包名、类名区分开

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <beans xmlns="http://www.springframework.org/schema/beans"
 3        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
 4        xmlns:tx="http://www.springframework.org/schema/tx" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
 5        xmlns:context="http://www.springframework.org/schema/context"
 6        xsi:schemaLocation="
 7      http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
 8      http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
 9      http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
10      http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
11      http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd"
12        default-autowire="byName">
13 
14     <bean id="dataSource1" class="org.h2.jdbcx.JdbcConnectionPool"
15           destroy-method="dispose">
16         <constructor-arg>
17             <bean class="org.h2.jdbcx.JdbcDataSource">
18                 <property name="URL" value="jdbc:h2:r:/h2db/awbprint/a"/>
19                 <property name="user" value="sa"/>
20                 <property name="password" value="sa"/>
21             </bean>
22         </constructor-arg>
23     </bean>
24 
25 
26     <bean id="dataSource2" class="org.h2.jdbcx.JdbcConnectionPool"
27           destroy-method="dispose">
28         <constructor-arg>
29             <bean class="org.h2.jdbcx.JdbcDataSource">
30                 <property name="URL" value="jdbc:h2:r:/h2db/awbprint/b"/>
31                 <property name="user" value="sa"/>
32                 <property name="password" value="sa"/>
33             </bean>
34         </constructor-arg>
35     </bean>
36 
37     <bean id="sqlSessionFactory1" class="org.mybatis.spring.SqlSessionFactoryBean">
38         <property name="dataSource" ref="dataSource1"/>
39         <property name="configLocation" value="classpath:mybatis-config.xml"></property>
40         <property name="typeAliasesPackage" value="awbprint.mybatis.entity"></property>
41         <property name="mapperLocations" value="classpath:mybatis/a/**/*.xml"></property>
42     </bean>
43 
44     <bean id="sqlSessionFactory2" class="org.mybatis.spring.SqlSessionFactoryBean">
45         <property name="dataSource" ref="dataSource2"/>
46         <property name="configLocation" value="classpath:mybatis-config.xml"></property>
47         <property name="typeAliasesPackage" value="awbprint.mybatis.entity"></property>
48         <property name="mapperLocations" value="classpath:mybatis/b/**/*.xml"></property>
49     </bean>
50 
51     <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
52         <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory1"></property>
53         <property name="basePackage" value="awbprint.mybatis.mapper.a"/>
54     </bean>
55 
56     <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
57         <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory2"></property>
58         <property name="basePackage" value="awbprint.mybatis.mapper.b"/>
59     </bean>
60 
61 
62 </beans>

上面的配置,一个连h2的a数据库,一个连h2的b数据库,至于事务管理器,大家可参考这个思路,建二个,各管各的。

项目中mapper接口及映射文件均用包名区分开,如下图:

二、如何使用Map做为参数及动态条件生成

 1     <resultMap id="BaseResultMap" type="awbprint.mybatis.entity.PrintLayout">
 2         <id column="D_RECID" property="recid" jdbcType="DECIMAL"/>
 3         <result column="D_USER_NAME" property="userName" jdbcType="VARCHAR"/>
 4         <result column="D_NAME" property="name" jdbcType="VARCHAR"/>
 5         <result column="D_TYPE" property="type" jdbcType="VARCHAR"/>
 6         ...
 7        
 8     </resultMap>
 9 
10    <sql id="Base_Column_List">
11         D_RECID, D_USER_NAME, D_NAME, D_TYPE, ...
12     </sql>
13 
14     <select id="select" resultMap="BaseResultMap"
15             parameterType="java.util.Map">
16         select
17         <include refid="Base_Column_List"/>
18         from T_PRINT_LAYOUT
19         where D_USER_NAME = #{userName,jdbcType=VARCHAR} and D_TYPE = #{awbType,jdbcType=VARCHAR}
20         <if test="recId != null">
21             and D_RECID = #{recId,jdbcType=DECIMAL}
22         </if>
23         <if test="ids != null">
24             or D_RECID in
25             <foreach item="item" index="index" collection="ids"
26                      open="(" separator="," close=")">
27                 #{item}
28             </foreach>
29         </if>
30 
31     </select>

14-31演示了如何使用Map做为参数,动态传入查询条件,及List参数生成in(...)条件

java端代码示例:

 1         PrintLayoutMapper mapper = context.getBean(PrintLayoutMapper.class);
 2         
 3         Map<String, Object> map = new HashMap<String, Object>();
 4         map.put("userName", "ADMIN");
 5         map.put("awbType", "CARGOLABEL_MU");
 6         map.put("recId", 1);
 7         
 8         List<Integer> ids = new ArrayList<Integer>();
 9         ids.add(0, 1);
10         ids.add(0, 2);
11         ids.add(0, 3);
12         
13         map.put("ids", ids);
14 
15         List<?> list = mapper.select(map);

其实PrintLayoutMapper接口的定义为:

1 public interface PrintLayoutMapper {    
2     ...
3     
4     List<PrintLayout> select(Map<String, Object> map);
5 }

最终生成的SQL语句为:

1 select D_RECID, D_USER_NAME, D_NAME, D_TYPE, ... from T_PRINT_LAYOUT where D_USER_NAME = ? and D_TYPE = ? and D_RECID = ? or D_RECID in ( ? , ? , ? )

三、兼容不同的数据库

 1 <insert id="insert">
 2  <selectKey keyProperty="id" resultType="int" order="BEFORE">
 3  <if test="_databaseId == 'oracle'">
 4  select seq_users.nextval from dual
 5  </if>
 6  <if test="_databaseId == 'db2'">
 7  select nextval for seq_users from sysibm.sysdummy1"
 8  </if>
 9  </selectKey>
10  insert into users values (#{id}, #{name})
11 </insert>

这是官方文档上的示例,演示了如何兼容oracle与db2这二种不同的数据库,来获取序列的下一个值

四、加强版的分支、选择判断

 1 <select id="findActiveBlogLike"
 2  resultType="Blog">
 3  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
 4  <choose>
 5  <when test="title != null">
 6  AND title like #{title}
 7  </when>
 8  <when test="author != null and author.name != null">
 9  AND author_name like #{author.name}
10  </when>
11  <otherwise>
12  AND featured = 1
13  </otherwise>
14  </choose>
15 </select>

这也是官方文档上的示例,因为<if>...</if>并没对应的<else>标签,所以要达到<if>...<else>...</else> </if>的效果,得借助<choose>、<when>、<otherwise>组合使用。

五、避免Where 空条件的尴尬

1 <select id="findActiveBlogLike" resultType="Blog">
2  SELECT * FROM BLOG
3  WHERE
4  <if test="state != null">
5  state = #{state}
6  </if> 
7 </select>

如果state参数为空时,最终生成SQL语句为 

1  SELECT * FROM BLOG
2  WHERE

执行会出错,当然,你可以在where 后加一个1=1,改成

1 <select id="findActiveBlogLike" resultType="Blog">
2  SELECT * FROM BLOG
3  WHERE 1=1
4  <if test="state != null">
5  and state = #{state}
6  </if> 
7 </select>

但是这个做法不太“环保”(毕竟引入了一个垃圾条件),其实只要改成<where>...</where>即可

1 <select id="findActiveBlogLike" resultType="Blog">
2  SELECT * FROM BLOG
3  <where>
4      <if test="state != null">
5          and state = #{state}
6      </if>
7  </where> 
8 </select>

六、$与#的区别

1  select * from T_PRINT_LAYOUT where  D_RECID = ${recId}

最后生成的SQL为:

1 select * from T_PRINT_LAYOUT where  D_RECID = 1

即:直接将参数值替换到了原来${recId}的位置,相当于硬拼SQL

1  select * from T_PRINT_LAYOUT where  D_RECID = #{recid,jdbcType=DECIMAL}

最后生成的SQL为:

1 select * from T_PRINT_LAYOUT where  D_RECID = ?

即:#{...}被识别为一个SQL参数

七、大量数据的批量insert

大量数据(条数>10000)做insert时,如果按常规方式,每条insert into table(...) values(...);来提交,速度巨慢。改善性能的思路是多条insert批量提交。

oracle环境中,有一种批量insert的小技巧,原理是 insert into ... select from ...,套在mybatis上,变形为:

 1 INSERT INTO T_TEST
 2   (ID, COL_A, COL_B)
 3   SELECT SEQ_TEST.NEXTVAL, A.*
 4     FROM (
 5               SELECT 'A1', 'B1' FROM DUAL
 6     UNION ALL SELECT 'A2', 'B2' FROM DUAL
 7     UNION ALL SELECT 'A3', 'B3' FROM DUAL
 8     UNION ALL SELECT 'A4', 'B4' FROM DUAL
 9     UNION ALL SELECT 'A5', 'B5' FROM DUAL
10     UNION ALL SELECT 'A6', 'B6' FROM DUAL
11     ) A

中间的部分非常有规律,可以用foreach标签生成,参考下面的片段:

 1 <insert id="insertBatch2" parameterType="ctas.entity.SharkFlt">
 2     <selectKey keyProperty="recId" order="BEFORE" resultType="Long">
 3       select SEQ_CTAS_SHARK_FLT.nextval as recId from dual
 4     </selectKey>
 5     insert into CTAS_SHARK_FLT (<include refid="Base_Column_List"/>) SELECT SEQ_TEST.NEXTVAL, A.*
 6     FROM (
 7     <foreach collection="list" item="item" index="index" open="" close="" separator="union all">
 8       select #{item.awbType,jdbcType=VARCHAR}, #{item.awbPre,jdbcType=VARCHAR},... from dual
 9     </foreach>
10     ) A
11   </insert>

即使这样,也不能直接run,oracle中一次执行的sql语句长度是有限制的,如果最后拼出来的sql字符串过长,会导致执行失败,所以java端还要做一个分段处理,参考下面的处理:

 1         List<SharkFlt> data = new ArrayList<SharkFlt>();
 2         for (TSharkFlt f : sharkFlts) {
 3             data.add(getSharkFlt(f));
 4         }
 5 
 6         System.out.println(data.size());
 7 
 8         long beginTime = System.currentTimeMillis();
 9         System.out.println("开始插入...");
10         SqlSessionFactory sqlSessionFactory = ctx.getBean(SqlSessionFactory.class);
11         SqlSession session = null;
12         try {
13             session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
14             int a = 2000;//每次提交2000条
15             int loop = (int) Math.ceil(data.size() / (double) a);
16 
17             List<SharkFlt> tempList = new ArrayList<SharkFlt>(a);
18             int start, stop;
19             for (int i = 0; i < loop; i++) {
20                 tempList.clear();
21                 start = i * a;
22                 stop = Math.min(i * a + a - 1, data.size() - 1);
23                 System.out.println("range:" + start + " - " + stop);
24                 for (int j = start; j <= stop; j++) {
25                     tempList.add(data.get(j));
26                 }
27                 session.insert("ctas.importer.writer.mybatis.mappper.SharkFltMapper.insertBatch2", tempList);
28                 session.commit();
29                 session.clearCache();
30                 System.out.println("已经插入" + (stop + 1) + " 条");
31             }
32         } catch (Exception e) {
33             e.printStackTrace();
34             session.rollback();
35         } finally {
36             if (session != null) {
37                 session.close();
38             }
39         }
40         long endTime = System.currentTimeMillis();
41         System.out.println("插入完成,耗时 " + (endTime - beginTime) + " 毫秒!");

13,27-29这几行是关键,这一段逻辑会经常使用,为了重用,可以封装一下:

 1     /**
 2      * 批量提交数据
 3      * @param sqlSessionFactory
 4      * @param mybatisSQLId SQL语句在Mapper XML文件中的ID
 5      * @param commitCountEveryTime 每次提交的记录数
 6      * @param list 要提交的数据列表
 7      * @param logger 日志记录器
 8      */
 9     private <T> void batchCommit(SqlSessionFactory sqlSessionFactory, String mybatisSQLId, int commitCountEveryTime, List<T> list, Logger logger) {
10         SqlSession session = null;
11         try {
12             session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
13             int commitCount = (int) Math.ceil(list.size() / (double) commitCountEveryTime);
14             List<T> tempList = new ArrayList<T>(commitCountEveryTime);
15             int start, stop;
16             Long startTime = System.currentTimeMillis();
17             for (int i = 0; i < commitCount; i++) {
18                 tempList.clear();
19                 start = i * commitCountEveryTime;
20                 stop = Math.min(i * commitCountEveryTime + commitCountEveryTime - 1, list.size() - 1);
21                 for (int j = start; j <= stop; j++) {
22                     tempList.add(list.get(j));
23                 }
24                 session.insert(mybatisSQLId, tempList);
25                 session.commit();
26                 session.clearCache();
27             }
28             Long endTime = System.currentTimeMillis();
29             logger.debug("batchCommit耗时:" + (endTime - startTime) + "毫秒");
30         } catch (Exception e) {
31             logger.error("batchCommit error!", e);
32             e.printStackTrace();
33             session.rollback();
34         } finally {
35             if (session != null) {
36                 session.close();
37             }
38         }
39     }

对应的,如果是批量update,也是类似的思路,只不过要注意一点:oracle环境中,多条语句提交的sql语句为

begin

  update xxx set xxx =xxx ;

  update xxx set xxx =xxx;

end;

用mytais拼的时候,参考下面的写法:

1   <update id="updateBatch" parameterType="java.util.List">
2     <foreach collection="list" item="item" index="index" open="begin" close="end;" >
3       update xxx set x=#{item.x,jdbcType=VARCHAR} where x =#{item.x,jdbcType=VARCHAR};
4     </foreach>
5   </update>

关于批量提交的性能,Oracle环境下,我大概测试了一下:

insert into ... select xxx

  union all select yyy

  union all select zzz;

最快,其次是

begin

  insert into ... values  ...;

  insert into ... values ...;

end;

当然最慢是逐条insert提交,最后谈下Spring与mybatis集成后,AOP事务管理 对 批量提交的影响 ,通常情况下,我们会这样配置AOP事务管理:

 1     <tx:advice id="txAdvice" transaction-manager="transactionManager">
 2         <tx:attributes>
 3             <tx:method name="do*" read-only="false" rollback-for="java.lang.Exception"/>
 4             <tx:method name="*" propagation="SUPPORTS" read-only="true"/>
 5         </tx:attributes>
 6     </tx:advice>
 7 
 8     <aop:config>
 9         <aop:pointcut id="pc" expression="execution(* ctas.service.*.*(..))"/>
10         <aop:advisor pointcut-ref="pc" advice-ref="txAdvice"/>
11     </aop:config>

这样,ctas.service(及子包)下的所有方法都被拦截,而且只有do开头的方法,具有可写的事务(即:能insert/update/delete记录),而其它方法是只读事务(即:只能select数据),但是我们前面谈到的批量提交操作,都是写代码手动提交的,不需要spring管理,所以配置中需要将某些方法排除,可以约定self开头的方法,由开发者自己管理事务,不需要spring代为管理,上面的配置要改成:

1     <aop:config>
2         <aop:pointcut id="pc" expression="execution(* ctas.service.*.*(..)) and !execution(* ctas.service.*.self*(..)))"/>
3         <aop:advisor pointcut-ref="pc" advice-ref="txAdvice"/>
4     </aop:config>

 通过 and !execution(...) 将self开头的方法排除就可以了,前面的批量操作代码写到selfXXX方法中。

关于批量提交,还有一种情况:父子表的批量插入。思路还是一样的,但是SQL的写法有点区别,原理参考下面的语句(Oracle环境)

 1 DECLARE
 2   BASE_ID   INTEGER;
 3   DETAIL_ID INTEGER;
 4 BEGIN
 5   --第1组记录
 6   SELECT SEQ_T_BASE.NEXTVAL INTO BASE_ID FROM DUAL;
 7   INSERT INTO T_BASE (ID, FEE) VALUES (BASE_ID, ?);
 8   
 9   SELECT SEQ_T_DETAIL.NEXTVAL INTO DETAIL_ID FROM DUAL;
10   INSERT INTO T_DETAIL (ID, BASE_ID, FEE) VALUES (DETAIL_ID, BASE_ID, ?);
11   SELECT SEQ_T_DETAIL.NEXTVAL INTO DETAIL_ID FROM DUAL;
12   INSERT INTO T_DETAIL (ID, BASE_ID, FEE) VALUES (DETAIL_ID, BASE_ID, ?);
13   
14   --第2组记录
15   SELECT SEQ_T_BASE.NEXTVAL INTO BASE_ID FROM DUAL;
16   INSERT INTO T_BASE (ID, FEE) VALUES (BASE_ID, ?);
17   
18   SELECT SEQ_T_DETAIL.NEXTVAL INTO DETAIL_ID FROM DUAL;
19   INSERT INTO T_DETAIL (ID, BASE_ID, FEE) VALUES (DETAIL_ID, BASE_ID, ?);
20   SELECT SEQ_T_DETAIL.NEXTVAL INTO DETAIL_ID FROM DUAL;
21   INSERT INTO T_DETAIL (ID, BASE_ID, FEE) VALUES (DETAIL_ID, BASE_ID, ?);
22   
23   --...
24 END;

xml映射文件中的写法:

 1     <insert id="insertBatch" parameterType="java.util.List">
 2         DECLARE
 3         base_id INTEGER ;
 4         detail_id INTEGER ;
 5         <foreach collection="list" item="item" index="index" open="begin" close="end;">
 6             select seq_t_base.nextval into base_id from dual;
 7             insert into t_base(id, fee) values(base_id, #{item.baseEntity.fee,jdbcType=DECIMAL});
 8             <foreach collection="item.details" item="detail" index="index">
 9                 select seq_t_detail.nextval into detail_id from dual;
10                 insert into t_detail(id, base_id, fee) values(detail_id,base_id,#{detail.fee,jdbcType=DECIMAL});
11             </foreach>
12         </foreach>
13     </insert>

 List中的Dto定义

 1 public class BaseDetailDto {
 2 
 3     private TBase baseEntity;
 4 
 5     private List<TDetail> details;
 6 
 7     public TBase getBaseEntity() {
 8         return baseEntity;
 9     }
10 
11     public void setBaseEntity(TBase baseEntity) {
12         this.baseEntity = baseEntity;
13     }
14 
15 
16     public List<TDetail> getDetails() {
17         return details;
18     }
19 
20     public void setDetails(List<TDetail> details) {
21         this.details = details;
22     }
23 }

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏「3306 Pai」社区

《那些年,我在乙方的日子 -- 神谕篇NO1》

某个夏日的午后,窗外知了在大声鸣叫。而我却在睡梦中跟基友一起吃鸡,正准备抢空投时 。手机突然铃声响起,惊醒后一看是领导电话,一下子回到了现实中。心想又得去公司吃...

1502
来自专栏张善友的专栏

Sql Server 2005 ROW_NUMBER 函数实现分页

过去用SQL Server 2000分页的,大多都用到了临时表。SQL Server 2005 ROW_NUMBER 函数支持分页,性能据说也非常不错。 Pag...

2116
来自专栏乐沙弥的世界

SQL*Plus copy 命令处理大批量数据复制

    对于数据库表级上的数据复制,我们最常用的是CREATE TABLE AS(CTAS)..方式。其实在SQL*Plus下面copy命令可以完成同样的工作,...

951
来自专栏恰同学骚年

《T-SQL查询》读书笔记Part 2.执行计划

  在SSMS(SQL Server Management Studio)中,有两种图形化的执行计划可供选择。一种是估计执行计划(Ctrl+L键),另一种是实际...

663
来自专栏数据小魔方

时间&日期函数

今天要跟大家简要介绍一下excel中经常会用到的日期与时间函数! 日期与时间类的函数虽然算所有函数中最难掌握的,但是因为格式众多,形式多样,而且作为其他高级函数...

3297
来自专栏乐沙弥的世界

FORALL 之 SAVE EXCEPTIONS 子句应用一例

     对于大批量的DML操作中出现的错误,除了使用DML error logging特性来记录在DML期间出现的错误之外,使用批量SQL语句FORALL的S...

661
来自专栏杨建荣的学习笔记

merge语句导致的CPU使用率过高的优化(r7笔记第4天)

今天有一个数据库有点反常,早上的时候报出了CPU使用率的警告。 警告内容如下: ZABBIX-监控系统: -----------------------...

3255
来自专栏杨建荣的学习笔记

简单实用的sql小技巧(第一篇) (r3笔记第36天)

今天和大家简单分享几个实用的sql小技巧。还有一些还在整理中,会不断的分享出来。 有些其实也不算是sql的技巧,可能大家在写sql语句的时候没有意识到我们可以通...

2993
来自专栏恰同学骚年

《T-SQL查询》读书笔记Part 1.逻辑查询处理知多少

  T-SQL是ANSI和ISO SQL标准的MS SQL扩展,其正式名称为Transact-SQL,但一般程序员都称其为T-SQL。

914
来自专栏恰同学骚年

轻量级ORM框架初探-Dapper与PetaPoco的基本使用

  EF是传统的ORM框架,也是一个比较重量级的ORM框架。这里仍然使用EF的原因在于为了突出轻量级ORM框架的性能,所谓有对比才有更优的选择。

2333

扫码关注云+社区

领取腾讯云代金券