mybatis-杂记

 1、mybatis在xml文件中处理大于号小于号的方法

         第一种方法：

              用了转义字符把>和<替换掉，然后就没有问题了。
             SELECT * FROM test WHERE 1 = 1 AND start_date  &lt;= CURRENT_DATE AND end_date &gt;= CURRENT_DATE

    附：XML转义字符

&lt;	<	小于号
&gt;	>	大于号
&amp;	&	和
&apos;	'	单引号
&quot;	"	双引号

         第二种方法： 
             因为这个是xml格式的，所以不允许出现类似“>”这样的字符，但是都可以使用<![CDATA[ ]]>符号进行说明，将此类符号不进行解析 你的可以写成这个，mapper文件示例代码：

 

<![CDATA[ when min(starttime)<='12:00' and max(endtime)<='12:00' ]]>

 

2、SQL语句中“resultMap”和"parameterMap"的设置

     在配置文件先申明resultMap和parameterMap

 <resultMap id="BaseResultMap" type="com.etwin.order.model.UserNew" >
    <id column="ID" property="id" jdbcType="INTEGER" />
    <result column="USER_ID" property="userId" jdbcType="VARCHAR" />
    <result column="Name" property="name" jdbcType="VARCHAR" />
    <result column="PassWord" property="password" jdbcType="VARCHAR" />
    <result column="Sex" property="sex" jdbcType="INTEGER" />
    <result column="Phone" property="phone" jdbcType="VARCHAR" />
    <result column="IDCard" property="idcard" jdbcType="VARCHAR" />
    <result column="Email" property="email" jdbcType="VARCHAR" />
    <result column="Add_Person" property="addPerson" jdbcType="VARCHAR" />
    <result column="Add_Time" property="addTime" jdbcType="TIMESTAMP" />
    <result column="Role_ID" property="roleId" jdbcType="VARCHAR" />
    <result column="status" property="status" jdbcType="INTEGER" />
    <result column="loginTime" property="logintime" jdbcType="TIMESTAMP" />
    <result column="Cash_Password" property="cashPassword" jdbcType="VARCHAR" />
    <result column="Agent_ID" property="agentId" jdbcType="INTEGER" />
    <result column="Is_Enable" property="isEnable" jdbcType="VARCHAR" />
    <result column="Agent_Type" property="agentType" jdbcType="VARCHAR" />
  </resultMap>

 说明：
      resultMap：返回结果集
      parameterMap：查询参数集

      BaseResultMap：可以为返回结果集也可以为查询参数集，jdbcType可写可不写。

    

      SQL查询例子

<select id="selectUserOldJiLian" resultType="com.etwin.order.model.UserOld" parameterType="com.etwin.order.model.UserOld">
	select distinct u.User_ID userId, u.Agent_ID agentId, u.User_Name userName, u.Password Password, u.User_Type userType, u.Create_Date createDate,
	u.Create_By createBy,u.Modify_Date modifyDate, u.Modify_Pass_Date modifyPassDate, u.Modify_PayPass_Date modifyPaypassDate, u.Modify_By modifyBy,
	u.Active_Flag activeFlag,u.Employee_Name employeeName, u.Email email, u.Tel tel, u.Mobile_Tel mobileTel, u.Stop_CMD stopCmd, u.Is_Admin isAdmin,
	u.Pay_Password payPassword, u.Parent_Agent_Id parentAgentId, u.Question question, u.Answer answer, u.User_Security userSecurity, r.Role_ID RoleId
	from  om_user u,om_userrole ur,om_role r
	<where>
		1=1 and u.user_id = ur.User_id and ur.Role_Code = r.Role_Code
		<if test="agentId != null">
			AND Agent_ID = #{agentId}
		</if>
	</where>
</select>

 说明：

      resultType="com.etwin.order.model.UserOld",返回结果为对象，可以换成上面配置好的resultMap="BaseResultMap"

      parameterType="com.etwin.order.model.UserOld",查询差数为对象，可以换成上面配置好的parameterMap="BaseResultMap"

 

3、mybatis批量操作

     xml文件配置      

<!-- 批量插入 -->
<insert id="insertRoleNewList" parameterType="java.util.List">
	insert into sys_role(ID,Role_ID,Name)
		values
	<foreach collection="list" item="item" index="index" separator=",">
		(#{item.id}, #{item.roleId},#{item.name})
	</foreach>
</insert>

 注释：list为道中传入的参数（集合）

    dao调用

int insertRoleNewList(List list);

 注释：list为向xml文件中传递的参数
 

4、迭代写法    

     xml文件写法

<select id="findUserIdRole" resultMap="roleResultMap" parameterType="java.util.Map">
     select * from t_role
     <trim prefix="where" prefixOverrides="and" >
	      <if test="userRoleIdList!=null">
                  and id in
                  <foreach item="item" index="index" collection="userRoleIdList" open="(" separator="," close=")">  
 			#{item}  
		  </foreach>   
	      </if>
     </trim>     
</select>

  注释：collection="userRoleIdList"中userRoleIdList为集合

    调用类写法

package com.gamexun.support.test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.gamexun.support.model.Role;
import com.gamexun.support.service.RoleService;

public class Test {
	public static void main(String[] args) {         
        @SuppressWarnings("resource")
	ApplicationContext context = new ClassPathXmlApplicationContext("gx-support-common-context-test.xml");
        RoleService roleService = (RoleService)context.getBean("roleService");     

        try { 
        	Map<String,Object> params = new HashMap<String,Object>();
        	List userRoleIdList = new ArrayList();
        	userRoleIdList.add(1);
        	userRoleIdList.add(2);
        	userRoleIdList.add(3);
        	params.put("userRoleIdList",userRoleIdList);
        	List<Role> list = roleService.findUserIdRole(params);
        	System.out.println("---------------------------------------------");
		System.out.println(list.size());
	} catch (Exception e) {
		e.printStackTrace();
	}      
    }
}

5、在mybatis中使用模糊查询

<!-- 查询用户 -->
<select id="getUser" resultMap="usertMap" parameterType="java.util.Map">
       select * from user
       <where>
       		1=1
       		<if test="account!=null"><![CDATA[ and account like '${account}%' ]]></if>
       		<if test="name!=null"><![CDATA[ and name like '${name}%' ]]></if>
       		<if test="tel!=null"><![CDATA[ and phone like '${tel}%' ]]></if>
       		<if test="beginNum!=null and count!=null">
       			order by id limit #{beginNum},#{count}
       		</if>
       </where>
</select>

 说明：like后面必须用$;