1.BaseResultMap
<resultMap id="BaseResultMap" type="com.stylefeng.guns.common.persistence.model.LoginTest">
<id column="id" property="id" />
<result column="name" property="name" />
<result column="password" property="password" />
</resultMap>
2.SQL
<sql id="Base_Column_List">
id, name, password
</sql>
3.确切的Select
<select id="selectUser" resultMap="BaseResultMap" parameterType="String">
SELECT <include refid="Base_Column_List" /> FROM login_test
<where>
<if test="name != ''">
name=#{name}
</if>
</where>
</select>
4.模糊的Select
<select id="selectUsers" resultMap="BaseResultMap" parameterType="String">
SELECT <include refid="Base_Column_List" /> FROM login_test
<where>
<if test="name != ''">
name like '%#{name}%'
</if>
</where>
</select>
具体可参考:SQL 模糊查询
5.批量的Select(可用于数据库表的批量导出)
<select id="selectBySomeid" parameterType="list" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List" />
FROM login_test WHERE id in
<foreach collection="Idlist" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</select>
6.有选择性的update
<update id="updateByPrimaryKeySelective" parameterType="com.mall.pojo.LoginTest">
update login_test
<set>
<if test="name != null">
username = #{username},
</if>
<if test="password != null">
password = #{password},
</if>
</set>
where id = #{id}
</update>
7.无选择性的uptate
<update id="updateByPrimaryKey" parameterType="com.mall.pojo.LoginTest">
update login_test
set name = #{username},
password = #{password},
where id = #{id}
</update>
8.单个delete
<delete id="deleteByid" parameterType="Integer">
DELETE FROM login_test
WHERE id =#{id}
</delete>
9.批量delete
<delete id="deleteByid" parameterType="list">
DELETE FROM login_test WHERE id in
<foreach collection="Idlist" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
10.有选择性的单个insert
<insert id="insertSelective" parameterType="com.mall.pojo.LoginTest">
insert into login_test
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="username != null">
username,
</if>
<if test="password != null">
password,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id},
</if>
<if test="username != null">
#{name},
</if>
<if test="password != null">
#{password},
</if>
</trim>
</insert>
具体可参考:mybatis之特殊标签的使用
11.无选择性的单个insert
<insert id="insert" parameterType="com.mall.pojo.LoginTest">
insert into mmall_user (id, username, password)
values (#{id}, #{username}, #{password})
</insert>
12.批量插入
<insert id="batchInsert" parameterType="list">
insert into mmall_order_item (id, name, password)
values
<foreach collection="List" item="item" index="index" separator=",">
(
#{item.id},#{item.name},#{item.password} )
</foreach>
</insert>
13.多表更新
<update id="updateObjectVersion" parameterType="com.huhu.Dto">
UPDATE ${dataCode} set OBJECT_VERSION_NUMBER=#{objectVersionNumber}
<where>
<if test="codeId != null">
CODE_ID = #{codeId}
</if>
<if test="codeValueId != null">
AND CODE_VALUE_ID = #{codeValueId}
</if>
<if test="productId != null">
AND PRODUCT_ID = #{productId}
</if>
<if test="propertyId != null">
AND PROPERTY_ID = #{propertyId}
</if>
<if test="cmdId != null">
AND CMD_ID = #{cmdId}
</if>
<if test="paramId != null">
AND PARAM_ID = #{paramId}
</if>
<if test="templateId != null">
AND title = #{templateId}
</if>
</where>
</update>
不要难为自己,常用的就记录下来
-------记录点:白银
----------------------------------------------------------------------------------------------------------------------------------
数据库中类型是datetime,在mybatis中是
自定义插入某个表:
<update id="updateObjectVersion" parameterType="com.xx.xx">
UPDATE ${dataCode} set OBJECT_VERSION_NUMBER=#{objectVersionNumber}
<where>
<if test="codeId != null">
CODE_ID = #{codeId}
</if>
<if test="codeValueId != null">
AND CODE_VALUE_ID = #{codeValueId}
</if>
<if test="productId != null">
AND PRODUCT_ID = #{productId}
</if>
<if test="propertyId != null">
AND PROPERTY_ID = #{propertyId}
</if>
<if test="cmdId != null">
AND CMD_ID = #{cmdId}
</if>
<if test="paramId != null">
AND PARAM_ID = #{paramId}
</if>
<if test="templateId != null">
AND TEMPLATE_ID = #{templateId}
</if>
</where>
</update>