目录
基础
<?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="com.xusy.dao.UserMapper">
<!--
eviction LRU
flushInterval缓存时间,以毫秒为单位
size缓存大小
readOnly如果为false的话,缓存对象必须是可序列化的-->
<cache eviction="LRU"
type="com.xusy.util.RedisCache"
flushInterval="120000"
size="1024"
readOnly="true"/>
<resultMap type="com.xusy.po.User" id="userMap">
<id column="id" property="id" />
<result column="user_name" property="userName" />
<result column="pass_word" property="passWord" />
<result column="create_time" property="createTime" />
</resultMap>
<select id="findById" resultType="com.xusy.po.User"
resultMap="userMap" useCache="true">
select * from user where id=#{id}
</select>
<select id="update" parameterType="com.xusy.po.User">
update users set
user_name=#{userName},pass_word=#{passWord},create_time=#{createTime}
where
id=#{id}
</select>
<select id="del" parameterType="java.lang.Integer">
delete from users where id=#{id}
</select>
<select id="save" parameterType="com.shyroke.bean.UserBean">
insert into users(user_name,pass_word,create_time) values(#{userName},#{passWord},#{createTime})
</select>
</mapper>
查询
分页查询
<select id="selectAll" parameterType="int" resultMap="resultMap">
<![CDATA[
select *from t_ampa_appdeploydt limit #{num},10;
]]>
</select>
.查询表t_ampa_appdeploydt 中从num开始的后10条数据,上例中,#{num}为传入的变量,中的 parameterType属性指定该变量的数据类型,resultMap属性就是映射之前配置好的,并以其配置好的格式进行输出。(注:因select *from t_ampa_appdeploydt limit #{num},10中涉及数据变量#{num},最好加上![CDATA[]将该句子包裹,否则可能会造成无法识别为变量)
对应的查询方法为: selectList(“selectAll”,int类型的变量); 其中selectAll对应配置文件中相应查询语句的id。
多条件查询
<select id="selectBySclAndTypeAndName" parameterType="java.util.Map" resultMap="resultMap">
<![CDATA[
select *from t_ampa_appdeploydt where 1=1]]>
<if test="schoolid!='全部'">
<![CDATA[
and schoolid=#{schoolid}
]]>
</if>
<if test="apptype!='全部'">
<![CDATA[
and apptype=#{apptype}
]]>
</if>
<if test="appname!=''">
<![CDATA[
and appname like CONCAT('%',#{appname},'%')
]]>
</if>
<![CDATA[
limit #{num},10;
]]>
</select>
上例中,标签的parameterType属性指定为java.util.Map,那么传入的值就是Map类型,其中可包含多个键值对,引用时只需要写上对应的键名即可。带条件的查询语句写法如上,若schoolid、apptype、appname的值不满足条件,不会被带入条件查询当中,而最后都会把limit #{num},10带入进行分页查询。
对应的查询方法为:selectList(“selectBySclAndTypeAndName”,Map类型的变量);
查询单条数据
<select id="selectAllCount" parameterType="int" resultType="java.lang.Integer">
<![CDATA[
select count(*)from t_ampa_appdeploydt;
]]>
</select>
对应的查询方法为:selectOne(“selectAllCount”);
查询like
<select id="getNews" resultMap="news">
select * from new
where name like CONCAT('%','${key}','%')
or content like CONCAT('%','${key}','%')
or keyword like CONCAT('%','${key}','%') order by time DESC
</select>
插入
普通插入
<insert id="insertStatement" parameterType="demo.entity.User">
<![CDATA[
insert into user(username,password) values(#{username},#{password});
]]>
</insert>
批量插入
<insert id="insertMoreStatement" parameterType="java.util.List">
<![CDATA[
insert into user(id,username,password) values]]>
<foreach collection="list" item="item" index="index" separator=",">
<![CDATA[
(#{item.username},#{item.password})
]]>
</foreach>
</insert>
删除
普通删除
<delete id="deleteStatement" parameterType="demo.entity.User">
<![CDATA[
delete from user where id=#{id};
]]>
</delete>
批量删除
<delete id="deleteMoreStatement" parameterType="java.util.List">
<![CDATA[
delete from user where id in]]>
<foreach collection="list" item="item" open="(" separator="," close=")">
<![CDATA[
#{item.id}
]]>
</foreach>
</delete>
更新
更新语句
<update id="updataStatement" parameterType="demo.entity.User">
<![CDATA[
update user set username=#{username} where id=#{id};
]]>
</update>
批量更新
<update id="updataMoreStatement" parameterType="java.util.List">
<![CDATA[
update user
]]>
<set>
<![CDATA[
username=
]]>
<![CDATA[
#{item.username}
]]>
</set>
<![CDATA[
where id in
]]>
<foreach collection="list" item="item" open="(" close=")" separator=",">
<![CDATA[
#{item.id}
]]>
</foreach>
</update>