首页 > 代码库 > mybatis 使用经验小结
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.xsd10 http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd11 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 select17 <include refid="Base_Column_List"/>18 from T_PRINT_LAYOUT19 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 in25 <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 = 113 </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 BLOG3 WHERE4 <if test="state != null">5 state = #{state}6 </if> 7 </select>
如果state参数为空时，最终生成SQL语句为
1 SELECT * FROM BLOG2 WHERE
执行会出错，当然，你可以在where 后加一个1=1，改成
1 <select id="findActiveBlogLike" resultType="Blog">2 SELECT * FROM BLOG3 WHERE 1=14 <if test="state != null">5 and state = #{state}6 </if> 7 </select>
但是这个做法不太“环保”（毕竟引入了一个垃圾条件），其实只要改成<where>...</where>即可
1 <select id="findActiveBlogLike" resultType="Blog">2 SELECT * FROM BLOG3 <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参数
mybatis 使用经验小结
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。