规范底层xml文件
列如:构建一个班级用户表
首先配置resulMap进行实体类与字段之间进行映射
<resultMap id="BaseResultMap" type="educhainx.hardware.network.parent.model.ClazzUser">
<result column="id" property="id"/>
<result column="clazz_id" property="clazzId"/>
<result column="user_id" property="userId"/>
<result column="create_time" property="createTime"/>
<result column="update_time" property="updateTime"/>
</resultMap>
构建sql查询列表
<sql id="Base_Column_List">
id,clazz_id,user_id,create_time,update_time
</sql>
构建sql条件列表
<sql id="Example_Where_Clause">
where 1=1
<trim suffixOverrides=",">
<if test="clazzId != null">
and clazz_id = #{clazzId}
</if>
<if test="userId != null">
and user_id = #{userId}
</if>
<if test="clazzIdList!=null and clazzIdList.size()>0">
and clazz_id in
<foreach item="clazzId" index="index" collection="clazzIdList" open="(" separator="," close=")">
#{clazzId}
</foreach>
</if>
<if test="userIdList!=null and userIdList.size()>0">
and user_id in
<foreach item="userId" index="index" collection="userIdList" open="(" separator="," close=")">
#{userId}
</foreach>
</if>
</trim>
</sql>
创建基本底层业务
每个业务都会存在几个基本的接口,如基本的表格维护接口(表的增删改查)以及条件接口(根据条件查询多条记录、根据条件查询单条记录、根据id查询多条记录、根据id查询多条记录、根据id删除记录、根据条件删除多条记录等等)
表的插入数据
<insert id="insertClazzUser" parameterType="educhainx.hardware.network.parent.model.ClazzUser"
useGeneratedKeys="true" keyProperty="id">
insert into sy_clazz_user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="userId != null">
user_id,
</if>
<if test="clazzId != null">
clazz_id,
</if>
create_time,update_time
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="userId != null">
#{userId},
</if>
<if test="clazzId != null">
#{clazzId},
</if>
now(),now()
</trim>
</insert>
注意:这个地方有几个需要注意的地方
插入都是按条件进行插入需要用到<trim>进行拼接条件标签
useGeneratedKeys="true" 开启自增id返回
keyProperty="id" 将返回的自增id映射到实体中的id上
表更新数据
<update id="updateClazzUser" parameterType="educhainx.hardware.network.parent.model.ClazzUser">
update sy_clazz_user
<set>
<if test="clazzId != null">
clazz_id = #{clazzId},
</if>
<if test="userId != null">
user_id = #{userId},
</if>
update_time = now(),
</set>
where id=#{id}
</update>
注意:
按是否有字段进行更新
根据id进行更新
根据id获取单条数据
<select id="getUserClazzById" resultMap="BaseResultMap" parameterType="java.lang.Integer">
select
<include refid="Base_Column_List"/>
from sy_clazz_user
where id = #{id}
</select>
注意:
使用resultMap映射进行构建查询字段,会增加查询效率
根据id进行删除
<delete id="deleteClazzUserById" parameterType="java.lang.Integer">
delete
from sy_clazz_user
where id = #{id}
</delete>
根据条件查询单条记录
<select id="getUserClazzByParams" resultMap="BaseResultMap" parameterType="java.util.HashMap">
select
<include refid="Base_Column_List"/>
from sy_clazz_user
<include refid="Example_Where_Clause"/>
limit 1
</select>
注意:
limit 1:限制查询出来的记录只取第一条
根据条件查询多条数据
<select id="findUserClazzByParams" resultMap="BaseResultMap" parameterType="java.util.HashMap">
select
<include refid="Base_Column_List"/>
from sy_clazz_user
<include refid="Example_Where_Clause"/>
</select>