MyBatis——3-阿里云开发者社区

开发者社区> 数据库> 正文

MyBatis——3

简介: MyBatis笔记,参考文档: 多表查询:http://blog.csdn.net/happylee6688/article/details/45967763;

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

方法一:

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

XML转义字符

57cf835068962fecf9ff3cd9e2b4ae574475c246

44ad2dc441b295eb11ace0122720bf26657ebf5d

方法二:

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

600f5a644f9e4d57cbedcafb7f5c5f1294550781


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的实体类代码:

1dee1661abc13e98f2dfe552bfdb292d5f6caa96726eba52d94073bc1e7601a8f0e93e0e96bddf79

在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);
}














版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章