插入
插入数据
<insert id="insertUser2" parameterType="UserEntity">
insert into user values(null,#{userName},#{passWord} ,#{age})
</insert>
批量插入
<insert id="insertForeach" parameterType="java.util.List" >
insert into user values
<foreach item="item" index="index" collection="list" separator="," >
(#{item.id},#{item.userName},#{item.passWord},#{item.age})
</foreach>
</insert>
删除
删除数据
<delete id="delUser" parameterType="int">
DELETE from user where id = #{id};
</delete>
批量删除
<delete id="deleteForeach" parameterType="java.util.List">
delete from user where id in
<foreach item="item" collection="list" open="(" separator="," close=")">#{item}</foreach>
</delete>
更新
更新数据
<update id="updateUser" parameterType="UserEntity">
UPDATE user set username = #{userName},password=#{passWord} where id = #{id}
</update>
//选择局部更新
<update id="updateSet" parameterType="UserEntity" >
update user
<set>
<if test="userName!=null">username=#{userName},</if>
<if test="passWord!=null">password=#{passWord}</if>
</set>
where id=#{id}
</update>
批量更新
<update id="updateForeach" parameterType="java.util.List">
<foreach collection="list" item = "item" separator=";">
update user
<set>
<if test="item.userName !=null">username=#{item.userName},</if>
<if test="item.passWord !=null">password=#{item.passWord}</if>
</set>
where id=#{item.id}
</foreach>
</update>
查询
查询全部,模糊查询,相应变量查询
<select id="selectAll" resultType="com.cc.bean.UserEntity">
select <include refid="userSqlAll"></include> from user
</select>
<select id="selectLike" parameterType="string" resultType="com.cc.bean.UserEntity">
select <include refid="userSqlAll"></include> from user where username like concat("%",#{string},"%");
</select>
<update id="updateUser" parameterType="UserEntity">
UPDATE user set username = #{userName},password=#{passWord} where id = #{id}
</update>
可变变量查询
<select id="selectWhere" parameterType="UserEntity" resultMap="mapUser">
select <include refid="userSqlAll"></include> from user
<where>
<if test="id>0">and id = #{id}</if>
<if test="userName1=null">and username=#{userName}</if>
<if test="passWord!=null">and password=#{passWord}</if>
</where>
</select>
<select id="selectTrim" parameterType="UserEntity" resultMap="mapUser">
select <include refid="userSqlAll"></include> from user
<trim prefix="where" prefixOverrides="and|or">
<if test="id>0">and id = #{id}</if>
<if test="userName1=null">and username=#{userName}</if>
<if test="passWord!=null">and password=#{passWord}</if>
</trim>
</select>
<select id="selectIf" parameterType="UserEntity" resultMap="mapUser">
select <include refid="userSqlAll"></include> from user where 1 = 1
<if test="userName!=null">and username=#{userName}</if>
<if test="passWord!=null">and password=#{passWord}</if>
</select>
<select id="selectChoose" parameterType="UserEntity" resultMap="mapUser">
select <include refid="userSqlAll"></include> from user where 1 = 1
<choose>
<when test="userName!=null">and username=#{userName}</when>
<when test="passWord!=null">and password=#{passWord}</when>
<otherwise>and 1 = 1</otherwise>
</choose>
</select>
批量查询
<select id="selectForeachIn" parameterType="java.util.List" resultMap="mapUser">
select <include refid="userSqlAll"></include> from user where id in
<foreach item="item" collection="list" open="(" separator="," close=")">#{item}</foreach>
</select>
注意事项
resultMap进行实体类和数据库表名映射
<resultMap type="UserEntity" id="mapUser">
<id property="id" column="id"></id>
<!-- 非主键 -->
<result property="userName" column="username"></result>
<result property="passWord" column="password"></result>
<result property="age" column="age"></result>
</resultMap>
sql标签和include标签简写sql
<sql id="userSqlAll">id,username,password,age</sql>
<include refid="userSqlAll"></include>
每一个标签的id值对应一个dao层的接口方法