1、mybatis在xml文件中处理大于号小于号的方法
第一种方法:
用了转义字符把>和<替换掉,然后就没有问题了。
SELECT * FROM test WHERE 1 = 1 AND start_date <= CURRENT_DATE AND end_date >= CURRENT_DATE
附:XML转义字符
< | < | 小于号 |
> | > | 大于号 |
& | & | 和 |
' | ' | 单引号 |
" | " | 双引号 |
第二种方法:
因为这个是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为道中传入的参数(集合)
int insertRoleNewList(List list);
注释:list为向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>
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();
}
}
}
<!-- 查询用户 --> <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后面必须用$;