用户与数据字典表关联

用户与数据字典表关联

根据主键(userId)查找用户

<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String">
    SELECT
        a.user_id,
        b.dictionary_content user_grade,
        c.dictionary_content user_subject,
        a.user_password,
        a.user_name,
        a.user_phone,
        a.user_address,
        a.user_register_time,
        d.dictionary_content user_sex,
        a.user_sign,
        a.user_icon
    FROM
        teacher_user AS a
        INNER JOIN system_dictionary AS b ON b.dictionary_id = a.user_grade
        INNER JOIN system_dictionary AS c ON c.dictionary_id = a.user_subject
        INNER JOIN system_dictionary AS d ON d.dictionary_id = a.user_sex
    WHERE
        a.is_deleted = "N" AND a.user_id = #{userId,jdbcType=VARCHAR}


        47534bd1bf5a41f7a16c306188af0864
</select>

根据手机号查找老师

<select id="findUserByPhone" resultMap="BaseResultMap" parameterType="java.lang.String">
    SELECT
        a.user_id,
        b.dictionary_content user_grade,
        c.dictionary_content user_subject,
        a.user_password,
        a.user_name,
        a.user_phone,
        a.user_address,
        a.user_register_time,
        d.dictionary_content user_sex,
        a.user_sign,
        a.user_icon
    FROM
        teacher_user AS a
        INNER JOIN system_dictionary AS b ON b.dictionary_id = a.user_grade
        INNER JOIN system_dictionary AS c ON c.dictionary_id = a.user_subject
        INNER JOIN system_dictionary AS d ON d.dictionary_id = a.user_sex
    WHERE
        a.is_deleted = "N" AND a.user_phone = #{userPhone,jdbcType=VARCHAR}
</select>

添加老师用户

<insert id="insertSelective" parameterType="club.prettyedu.api.pojo.TeacherUser">
    insert into teacher_user
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="userId != null">
            user_id,
        </if>
        <if test="userPassword != null">
            user_password,
        </if>
        <if test="userPhone != null">
            user_phone,
        </if>
        <if test="userName != null">
            user_name,
        </if>
        <if test="userSex != null">
            user_sex,
        </if>
        <if test="userAddress != null">
            user_address,
        </if>
        <if test="userGrade != null">
            user_grade,
        </if>
        <if test="userSubject != null">
            user_subject,
        </if>
        <if test="userSign != null">
            user_sign,
        </if>
        <if test="userIcon != null">
            user_icon,
        </if>
        <if test="isDeleted != null">
            is_deleted,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="userId != null">
            #{userId,jdbcType=VARCHAR},
        </if>
        <if test="userPassword != null">
            #{userPassword,jdbcType=VARCHAR},
        </if>
        <if test="userPhone != null">
            #{userPhone,jdbcType=VARCHAR},
        </if>
        <if test="userName != null">
            #{userName,jdbcType=VARCHAR},
        </if>
        <if test="userSex != null">
            (SELECT dictionary_id FROM system_dictionary WHERE dictionary_content = #{userSex,jdbcType=VARCHAR} and is_deleted = 'N'),
        </if>
        <if test="userAddress != null">
            #{userAddress,jdbcType=VARCHAR},
        </if>
        <if test="userGrade != null">
            (SELECT dictionary_id FROM system_dictionary WHERE dictionary_content = #{userGrade,jdbcType=VARCHAR} and is_deleted = 'N'),
        </if>
        <if test="userSubject != null">
            (SELECT dictionary_id FROM system_dictionary WHERE dictionary_content = #{userSubject,jdbcType=VARCHAR} and is_deleted = 'N'),
        </if>
        <if test="userSign != null">
            #{userSign,jdbcType=VARCHAR},
        </if>
        <if test="userIcon != null">
            #{userIcon,jdbcType=VARCHAR},
        </if>
        <if test="isDeleted != null">
            #{isDeleted,jdbcType=VARCHAR},
        </if>
    </trim>
</insert>

更新老师信息

<update id="updateByPrimaryKeySelective" parameterType="club.prettyedu.api.pojo.TeacherUser">
    update teacher_user
    <set>
        <if test="userPhone != null">
            user_phone = #{userPhone,jdbcType=VARCHAR},
        </if>
        <if test="userName != null">
            user_name = #{userName,jdbcType=VARCHAR},
        </if>
        <if test="userSex != null">
            user_sex = (SELECT dictionary_id FROM system_dictionary WHERE dictionary_content =
            #{userSex,jdbcType=VARCHAR}),
        </if>
        <if test="userAddress != null">
            user_address = #{userAddress,jdbcType=VARCHAR},
        </if>
        <if test="userGrade != null">
            user_grade = (SELECT dictionary_id FROM system_dictionary WHERE dictionary_content =
            #{userGrade,jdbcType=VARCHAR}),
        </if>
        <if test="userSubject != null">
            user_subject = (SELECT dictionary_id FROM system_dictionary WHERE dictionary_content =
            #{userSubject,jdbcType=VARCHAR}),
        </if>
        <if test="userSign != null">
            user_sign = #{userSign,jdbcType=VARCHAR},
        </if>
        <if test="userIcon != null">
            user_icon = #{userIcon,jdbcType=VARCHAR},
        </if>
    </set>
    where user_id = #{userId,jdbcType=VARCHAR} AND is_deleted = "N"
</update>

更新老师密码

<update id="updatePassword" parameterType="map">
    update teacher_user
    <set>
        <if test="userPassword != null">
            user_password = #{userPassword,jdbcType=VARCHAR},
        </if>
    </set>
    where user_id = #{userId,jdbcType=VARCHAR} AND is_deleted = "N"
</update>