mybitis数据库操作

<?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.mml.crm2.user.mapper.UserMapper">
<!-- Result Map -->
<resultMap id="BaseResultMap" type="com.mml.crm2.user.model.UserModel">
    <result column="id" property="id" />
    <result column="parent_id" property="parent_id" />
    <result column="parent_name" property="parent_name" />
    <result column="user_code" property="user_code" />
    <result column="password" property="password" />
    <result column="name" property="name" />
    <result column="phone" property="phone" />
    <result column="salary" property="salary" />
    <result column="birthday" property="birthday" />
    <result column="state" property="state" />
    <result column="sex" property="sex" />
    <result column="create_time" property="create_time" />
    <result column="leave_time" property="leave_time" />
    <result column="image" property="image" />
    <result column="achievement" property="achievement" />
    <result column="partition_id" property="partition_id" />
    <result column="partition_name" property="partition_name" />
    <association property="storeModel" javaType="com.mml.crm2.store.model.StoreModel">
        <result column="stores_id" property="id" />
        <result column="store_name" property="name" />
    </association>
    <association property="roleModel" javaType="com.mml.crm2.sys.model.RoleModel">
        <result column="role_id" property="id" />
        <result column="role_name" property="name" />
    </association>
</resultMap>

<!-- table all fields - sql片段 -->
<sql id="Base_Column_List">
    user_code,name,password,birthday,phone,
    sex,image,achievement,
    state,create_time,
    parent_id,role_id        
    <if test="storeModel.id != null">
        ,stores_id
    </if>
    <if test="partition_id != null">
        ,partition_id
    </if>
</sql>
<!-- 查询条件 -->
<sql id="Example_Where_Clause">
    where 1=1
    <if test="model.user_code != null and model.user_code != ''">
        and user_code like #{model.user_code}
    </if>
    <if test="model.name != null and model.name != ''">
        and name like #{model.name}
    </if>
    <if test="model.parent_id != null">
        and parent_id = #{model.parent_id}
    </if>
    <if test="model.id != null">
        and id != #{model.id}
    </if>
</sql>
<sql id="Example_Order_Clause">
    <if test="pager.orderby !=null and pager.orderby !=''">
        order by #{pager.orderby}
    </if>
    <if test="pager.pageOn">
        limit #{pager.rowStart},#{pager.pageLimit}
    </if>
</sql>

<!-- 插入记录 -->
<insert id="insert" parameterType="com.mml.crm2.user.model.UserModel">
    <!--selectKey 会将 SELECT LAST_INSERT_ID()的结果放入到传入的model的主键里面， keyProperty 
        对应的model中的主键的属性名，这里是 user 中的id，因为它跟数据库的主键对应 order AFTER 表示 SELECT LAST_INSERT_ID() 
        在insert执行之后执行,多用与自增主键， BEFORE 表示 SELECT LAST_INSERT_ID() 在insert执行之前执行，这样的话就拿不到主键了， 
        这种适合那种主键不是自增的类型 resultType 主键类型 -->
    <selectKey resultType="java.lang.Integer" order="AFTER"
        keyProperty="id">
        SELECT LAST_INSERT_ID()
    </selectKey>
    insert into realtymanage.user(
    <include refid="Base_Column_List" />
    ) values(#{user_code},#{name},#{password},#{birthday},#{phone},
    #{sex},#{image},#{achievement},#{state},
    #{create_time},#{parent_id},
    #{roleModel.id}    
    <if test="storeModel.id != null">
        ,#{storeModel.id}
    </if>
    <if test="partition_id != null">
        ,#{partition_id}
    </if>
    )
</insert>
<!-- 删除记录 -->
<delete id="delete" parameterType="Object">
    delete from realtymanage.user where
    id=#{id}
</delete>

<delete id="deleteModel" parameterType="Object">
    delete
    from realtymanage.user where
    user_code=#{user_code}
</delete>
<sql id="update_condition">
    name=#{name},birthday=#{birthday},phone=#{phone},
    salary=#{salary},sex=#{sex},achievement=#{achievement},
    state=#{state},create_time=#{create_time},leave_time=#{leave_time},
    parent_id=#{parent_id},role_id=#{roleModel.id},stores_id=#{storeModel.id}
</sql>
<!-- 根据，修改记录 -->
<update id="update" parameterType="Object">
    update realtymanage.user set 
    <include refid="update_condition" />
    where id = #{id}
</update>

<!-- 修改记录，只修改只不为空的字段 -->
<update id="updateActive" parameterType="Object">
    <trim suffixOverrides=",">
        update realtymanage.user set
        id=id,
        <if test="achievement != null">
            achievement = #{achievement},
        </if>
        <if test="leave_time != null and leave_time != '' ">
            leave_time = #{leave_time},
        </if>
        <if test="parent_id != null  ">
            parent_id = #{parent_id},
        </if>
        <if test="roleModel.id != null ">
            role_id = #{roleModel.id},
        </if>
        <if test="password != null and password != '' ">
            password = #{password},
        </if>
        <if test="storeModel.id != null ">
            stores_id = #{storeModel.id},
        </if>
        <if test="phone != null and phone != '' ">
            phone = #{phone},
        </if>
        <if test="name != null and name != '' ">
            name = #{name},
        </if>
        <if test="salary != null ">
            salary = #{salary},
        </if>
        <if test="create_time != null and create_time != '' ">
            create_time = #{create_time},
        </if>
        <if test="birthday != null and birthday != '' ">
            birthday = #{birthday},
        </if>
        <if test="sex != null and sex != '' ">
            sex = #{sex},
        </if>
        <if test="state != null and state != '' ">
            state = #{state},
        </if>
    </trim>
    where id=#{id}
</update>
<!-- 系统用户 列表总数 -->
<select id="selectCount" resultType="java.lang.Integer"
    parameterType="Object">
    SELECT count(1) FROM realtymanage.user_view
    <include refid="Example_Where_Clause" />
</select>

<!-- 查询系统用户列表 -->
<select id="select" resultMap="BaseResultMap" parameterType="Object">
    SELECT * FROM realtymanage.user_view where id = #{id}
</select>
<select id="selectModel" resultMap="BaseResultMap" parameterType="Object">
    SELECT * FROM realtymanage.user_view where user_code = #{user_code}
</select>

<!-- 查询系统用户列表 -->
<select id="selectBlurModelList" resultMap="BaseResultMap">
    select *from realtymanage.user_view
    <include refid="Example_Where_Clause" />
    <include refid="Example_Order_Clause" />
    <!-- <if test="pager.orderCondition != null and pager.orderCondition != 
        ''"> -->
    <!-- ${pager.orderCondition} -->
    <!-- </if> -->
</select>
<select id="selectModelList" resultMap="BaseResultMap">
    select *from realtymanage.user_view
    <include refid="Where_Clause" />
    <include refid="Example_Order_Clause" />
    <!-- <if test="pager.orderCondition != null and pager.orderCondition != 
        ''"> -->
    <!-- ${pager.orderCondition} -->
    <!-- </if> -->
</select>
<sql id="Where_Clause">
    where 1=1
    <if test="model.parent_id != null">
        and parent_id = #{model.parent_id}
    </if>
    <if test="model.roleModel.id != null">
        and role_id = #{model.roleModel.id}
    </if>
    <if test="model.state != null and model.state != ''">
        and state = #{model.state}
    </if>
</sql>
</mapper>