MyBatis配置技巧

Java-ORM

提纲与学习路线

1. mybatis搭建简单入门：http://www.cnblogs.com/xdp-gacl/p/4261895.html
2. 基础crud：http://www.cnblogs.com/xdp-gacl/p/4262895.html
3. resultMap嵌套对象：http://www.cnblogs.com/hamhog/p/3959451.html
4. resultMap与parameterMap相同，如何避免重复编写；答：参数直接使用parameterType指向Class，绑定字段
5. 如何调用存储过程：http://blog.csdn.net/u012948731/article/details/49003361
6. mysql查询分页：http://blog.csdn.net/ls_man/article/details/11783813
7. parameterType类型为List的配置方法：http://blog.csdn.net/lsq_401/article/details/46776223

Conf配置

通过Maven打包时，默认情况下不会打包src目录下的xml文件，因此，如下结构会报错Could not find resource com.tg.mybatis.lotteryMapper.xml

<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <!-- 配置数据库连接信息 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="xxx"/>
                <property name="username" value="u"/>
                <property name="password" value="pwd"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/tg/mybatis/lotteryMapper.xml"/>
    </mappers>
</configuration>

Maven Mapper.xml建议添加到resources目录中，<mappers/>调整：

    <mappers>
        <mapper resource="mapper/lotteryMapper.xml"/>
    </mappers>

Mapper配置

parameterType

http://blog.csdn.net/kingjin_csdn_/article/details/53156732

1. MyBatis的传入参数parameterType类型分两种

   1. 1. 基本数据类型：int,string,long,Date;
   2. 1. 复杂数据类型：类和Map
         注：不同版本的MyBatis对基本类型传递过来的参数名称不能识别，要使用_parameter来代替。
         [html] view plain copy print?
         
         select from win_log where eventId = #{_parameter,jdbcType=BIGINT}
         

2. 如何获取参数中的值:
   2.1 基本数据类型：#{参数} 获取参数中的值
   2.2 复杂数据类型：#{属性名} ，map中则是#{key}

resultType

【foreach】标签

xml语法：

<foreach collection="list" item="item" open="(" close=")" separator="," index="index">
   #{item.xx}, #{item.xx}
</foreach>

> 单参数List的类型：

code:List ids = new ArrayList();List blogs = blogMapper.dynamicForeachTest(ids);

 <select id="dynamicForeachTest" parameterType="java.util.List" resultType="Blog">
           select * from t_blog where id in
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}       
        </foreach>    
    </select>

> 自己把参数封装成Map的类型

注意：上述collection的值为ids，是传入的参数Map的key，对应的Mapper代码：
public List dynamicForeach3Test(Map params);

code:

List ids = new ArrayList();List blogs = blogMapper.dynamicForeachTest(ids);
  final List ids = new ArrayList();
          ids.add(1);
          ids.add(2);
          ids.add(3);
          ids.add(6);
         ids.add(7);
         ids.add(9);
        Map params = new HashMap();
         params.put("ids", ids);
         params.put("title", "中国");
        List blogs = blogMapper.dynamicForeach3Test(params);

 <select id="dynamicForeach3Test" parameterType="java.util.HashMap" resultType="Blog">
         select * from t_blog where title like "%"#{title}"%" and id in
          <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
               #{item}
          </foreach>
 </select>

<select id="findListByNos" parameterType="list" resultMap="BaseResultMap">  
        SELECT   
         <include refid="Base_Column_List"/>  
         FROM INFO  
            WHERE INFO_ID IN  
            <foreach collection="list" index="index" item="item" open="(" close=")" separator=",">  
                    #{item}  
         </foreach>  
            ORDER BY INFO_ID DESC  
    </select>  
  <select id="findById" resultMap="BaseResultMap" parameterType="java.lang.String" >  
    SELECT   
    <include refid="Base_Column_List" />  
     FROM INFO  
     WHERE INFO_ID = #{infoId,jdbcType=VARCHAR}  
  </select>  
<select id="findByEntityWithPagination" resultMap="BaseResultMap" >  
 SELECT  
    <include refid="Base_Column_List" />  
    FROM INFO  
    <trim prefix="WHERE" suffixOverrides="AND" >  
      <if test="param1.infoType != null" >  
        INFO_TYPE = #{param1.infoType,jdbcType=VARCHAR} AND  
      </if>  
      <if test="param1.infoContent != null" >  
        INFO_CONTENT = #{param1.infoContent,jdbcType=LONGVARCHAR} AND  
      </if>    
    </trim>  
     LIMIT #{param2} , #{param3}  
  </select>  
<select id="findByProperties" parameterType="map" resultMap="BaseResultMap" >  
    SELECT   
    <include refid="Base_Column_List" />  
    FROM INFO  
    <trim prefix="WHERE" suffixOverrides="AND" >  
      <if test="_parameter.containsKey('infoType')" >  
        INFO_TYPE = #{infoType,jdbcType=VARCHAR} AND  
      </if>  
      <if test="_parameter.containsKey('infoContent')" >  
        INFO_CONTENT = #{infoContent,jdbcType=LONGVARCHAR} AND  
      </if>  
      <if test="_parameter.containsKey('infoName')" >  
        INFO_NAME = #{infoName,jdbcType=VARCHAR} AND  
      </if>  
    </trim>  
    ORDER BY INFO_ID  
  </select>  
   <select id="findByEntity" resultMap="BaseResultMap" parameterType="com.cn.Info" >  
    SELECT  
    <include refid="Base_Column_List" />  
    FROM INFO  
    <trim prefix="WHERE" suffixOverrides="AND" >  
      <if test="infoType != null" >  
        INFO_TYPE = #{infoType,jdbcType=VARCHAR} AND  
      </if>  
      <if test="infoContent != null" >  
        INFO_CONTENT = #{infoContent,jdbcType=LONGVARCHAR} AND  
      </if>  
    </trim>  
  </select>  

批量插入

//java code
int id = mapper.batchSave(List<User> list)
<!-- 批量保存用户,并返回每个用户插入的ID -->
<insert id="batchSave" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO `test`.`tb_user`(`username`, age)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.username}, #{item.age})
    </foreach>
</insert>

【insert】

insert插入返回主键ID

很多时候，在向数据库插入数据时，需要保留插入数据的id，以便进行后续的update操作或者将id存入其他表作为外键。但是，在默认情况下，insert操作返回的是一个int值，并且不是表示主键id，而是表示当前SQL语句影响的行数。。。

MyBatis在使用MySQL insert插入操作时将返回的id绑定到对象中。

MySQL用法：

<insert id="insert" parameterType="com.test.User"  keyProperty="userId" useGeneratedKeys="true" >

上面配置中，“keyProperty”表示返回的id要保存到对象的那个属性中，“useGeneratedKeys”表示主键id为自增长模式。

【if】标签

    <if test="_parameter != null and _parameter > 0">
        AND id = #{_parameter}
    </if>

    连接符and必须小写，否则报错，限定以下

        <EOF> 
"," ...
"=" ...
"?" ...
"||" ...
"or" ...
"&&" ...
"and" ...
"|" ...
"bor" ...
"^" ...
"xor" ...
"&" ...
"band" ...
"==" ...
"eq" ...
"!=" ...
"neq" ...
"<" ...
"lt" ...
">" ...
"gt" ...
"<=" ...
"lte" ...
">=" ...
"gte" ...
"in" ...
"not" ...
"<<" ...
"shl" ...
">>" ...
"shr" ...
">>>" ...
"ushr" ...
"+" ...
"-" ...
"*" ...
"/" ...
"%" ...
"instanceof" ...
"." ...
"(" ...
"[" ...
<DYNAMIC_SUBSCRIPT> ...