MyBatis——3

mybatis再xml文件中处理大于号小于号的方法

方法一：

用转义字符把>和<替换掉

XML转义字符

使用<![CDATA[ ]]>符号进行说明，将此类符号不进行解析

MyBatis错误：Parameter 'xxx' not found.Available parameters are [1,0,param1,param2]

原本用的是名称匹配发现不了，换成0,1,2这样的方式序列匹配；如果还是解决不了的话可能是参数类型不一致或者其中参数为NULL。

MyBatis多表联合查询及优化

表关系

user(id,username,password,gmt_create,gmt_modify)

role(id,name,userid)

user,role：一对多

User和Role的实体类代码：

在User的实体中加入一个Role的属性，对应一对多的关系。

mapper xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="src.bonc.lbs.wt.dao.business.UserDao" > 
<!-- Result Map-->
<resultMap id="BaseResultMap" type="src.bonc.lbs.wt.entity.business.User" >
	<result column="id" property="id"/>
	<result column="username" property="username"/>
	<result column="password" property="password"/>
	<result column="gmt_create" property="gmt_create"/>
	<result column="gmt_modify" property="gmt_modify"/>
</resultMap>

<!-- Result Map -->
<resultMap type="src.bonc.lbs.wt.entity.business.User" id="queryForListMap">
	<id column="id" property="id"/>
	<result column="username" property="username"/>
	<result column="password" property="password"/>
	<collection property="roles" javaType="java.util.List" ofType="src.bonc.lbs.wt.entity.business.Role">
		<id column="r_id" property="id" jdbcType="VARCHAR" />  
        <result column="r_name" property="name" jdbcType="VARCHAR" /> 
	</collection>
</resultMap>
       
<!-- user table all fields -->
<sql id="Base_Column_List" >
	 id,username,password,gmt_create,gmt_modify
</sql>
   
   
<!-- 查询条件 -->
<sql id="Example_Where_Clause">
where 1=1
<trim  suffixOverrides="," >
	<if test="id != null and id != ''" >
	    and id =  #{id}
	</if>
	<if test="username != null and username != ''" >
	    and username =  #{username}
	</if>
	<if test="password != null and password != ''" >
	    and password =  #{password}
	</if>
</trim>
</sql>
   

<!-- 插入记录 -->
<insert id="add" parameterType="Object" >
  insert into user(id,username,password)
 values(#{id},#{username},#{password})
</insert>

<!-- 根据id，修改记录-->  
 <update id="update" parameterType="Object" >
  update user set username=#{username},password=#{password} where id=#{id}
 </update>
 
 <!-- 修改记录，只修改只不为空的字段 -->
<update id="updateBySelective" parameterType="Object" >
	update user set 
	<trim  suffixOverrides="," >
	<if test="username != null  ">
		username=#{username},
	</if>
	<if test="password != null  ">
		password=#{password},
	</if>
	<if test="gmt_create != null  ">
		gmt_create=#{gmt_create},
	</if>
	<if test="gmt_modify != null  ">
		gmt_modify=#{gmt_modify},
	</if>
	</trim> where id=#{id}
</update>

<!-- 删除记录 -->
<delete id="delete" parameterType="Object">
	delete 	 from user where id = #{id}
</delete>
 
<!-- 根据id查询 用户 -->
<select id="queryById"  resultMap="BaseResultMap" parameterType="Object">
	select <include refid="Base_Column_List" /> 
	 from user where id = #{id}
</select>

<!-- 用户 列表总数-->
<select id="queryByCount" resultType="java.lang.Integer"  parameterType="Object">
	select count(1) from user 
	<include refid="Example_Where_Clause"/>
</select>
  	
<!-- 查询用户列表 -->
<select id="queryByList" resultMap="BaseResultMap"  parameterType="Object">
	select 
	<include refid="Base_Column_List"/>
	from user 
	<include refid="Example_Where_Clause"/>
	<if test="pager.orderCondition != null and pager.orderCondition != ''" >
      ${pager.orderCondition}
    </if>
    <if test="pager.mysqlQueryCondition != null and pager.mysqlQueryCondition != ''" >
       ${pager.mysqlQueryCondition}
    </if>
</select>

<select id="queryForList" resultMap="queryForListMap">
	SELECT u.id,u.username,u.password,r.id r_id,r.name r_name FROM user u LEFT JOIN role r ON u.id = r.userid
</select>

<select id="queryForListByCT" resultMap="queryForListMap">
	SELECT u.id,u.username,u.password,r.id r_id,r.name r_name FROM user u LEFT JOIN role r ON u.id = r.userid
	WHERE u.gmt_create > #{0} AND u.gmt_create < #{1} 
</select>
  	
</mapper>   

mapper接口

package src.bonc.lbs.wt.dao.business;


import java.util.List;

import src.bonc.com.base.dao.BaseDao;
import src.bonc.lbs.wt.entity.business.User;
/**
 * 
 * <br>
 * <b>功能：</b>UserDao<br>
 * <b>作者：</b>bonc<br>
 * <b>日期：</b> Feb 2, 2017 <br>
 * <b>版权所有：<b>版权所有(C) 2017，bonc.com.cn<br>
 */
public interface UserDao<T> extends BaseDao<T> {
	
	List<User> queryForList();
	
	List<User> queryForListByCT(String startTime, String endTime);
}