开发学习之路 五 配置说明

原创 于 2023-07-21 21:56:05 发布 · 283 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#学习 #mybatis

一、SysDeptMapper.xml

<?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.ctgu.web.mapper.SysDeptMapper">
    <select id="list" resultType="SysDept">
        select id, name, parent_id, tree_path, sort, status, deleted
        from sys_dept
    </select>

    <select id="listDepartments" resultType="SysDept">
        select id,name,parent_id,tree_path,sort,status,deleted
        from sys_dept
        <where>
            <if test="keywords!=null">
                name like concat(#{keywords},'%')
            </if>
            <if test="status!=null">
                and status=#{keywords}
            </if>
        </where>
    </select>

    <insert id="save">
        insert into sys_dept
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name!=null">name,</if>
            <if test="parentId!=null">parent_id,</if>
            <if test="treePath!=null">tree_path,</if>
            <if test="sort!=null">sort,</if>
            <if test="status!=null">status,</if>
            <if test="deleted!=null">deleted,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name!=null">#{name},</if>
            <if test="parentId!=null">#{parentId},</if>
            <if test="treePath!=null">#{treePath},</if>
            <if test="sort!=null">#{sort},</if>
            <if test="status!=null">#{status},</if>
            <if test="deleted!=null">#{deleted},</if>
        </trim>
    </insert>

    <delete id="remove">
        delete
        from sys_dept
        where id = #{id}
           or find_in_set(id, 'tree_path')
    </delete>

    <update id="updateById">
        update sys_dept
        <set>
            <if test="name!=null">name=#{name},</if>
            <if test="parentId!=null">parent_id=#{parentId},</if>
            <if test="treePath!=null">tree_path=#{treePath},</if>
            <if test="sort!=null">sort=#{sort},</if>
            <if test="status!=null">status=#{status},</if>
            <if test="deleted!=null">deleted=#{deleted},</if>
        </set>
        where id=#{id}
    </update>

    <select id="getOne" resultType="SysDept">
        select id, name, parent_id, tree_path, sort, status, deleted
        from sys_dept
        where id = #{id}
    </select>
</mapper>

二、SysDictMapper.xml

<?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.ctgu.web.mapper.SysDictMapper">

    <insert id="save">
        insert into sys_dict
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="typeCode != null and typeCode !=''">type_code,</if>
            <if test="name != null and name !=''">name,</if>
            <if test="value != null and value !=''">value,</if>
            <if test="sort != null">sort,</if>
            <if test="status != null">status,</if>
            <if test="defaulted != null">defaulted,</if>
            <if test="remark != null and remark !=''">remark,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="typeCode != null and typeCode !=''">#{typeCode},</if>
            <if test="name != null and name !=''">#{name},</if>
            <if test="value != null and value !=''">#{value},</if>
            <if test="sort != null">#{sort},</if>
            <if test="status != null">#{status},</if>
            <if test="defaulted != null">#{defaulted},</if>
            <if test="remark != null and remark !=''">#{remark},</if>
        </trim>
    </insert>

    <delete id="removeByTypeCode">
        delete from sys_dict
        <where>
            <foreach item="item" index="index" collection="list"
                     open="type_code in (" separator="," close=")" nullable="true">
                #{item}
            </foreach>
        </where>
    </delete>

    <select id="page" resultType="SysDict">
        SELECT id,type_code,name,value,sort,status,defaulted,remark
        FROM
        sys_dict
        <where>
            <if test='name!=null and name.trim() neq ""'>
                name LIKE CONCAT('%',#{name},'%')
            </if>
            <if test='typeCode!=null and typeCode.trim() neq ""'>
                and type_code = #{typeCode}
            </if>
        </where>
        order BY id asc
    </select>

    <select id="getOne" resultType="SysDict">
        select id, type_code, name,value,sort,status,defaulted, remark
        from sys_dict
        where id = #{id}
    </select>

    <delete id="removeByIds">
        delete
        from sys_dict
        <where>
            <foreach item="item" index="index" collection="list"
                     open="ID in (" separator="," close=")" nullable="true">
                #{item}
            </foreach>
        </where>
    </delete>

    <update id="updateById">
        update sys_dict
        <set>
            <if test="typeCode != null">type_code=#{typeCode},</if>
            <if test="name != null">name=#{name},</if>
            <if test="value != null">value=#{value},</if>
            <if test="sort != null">sort=#{sort},</if>
            <if test="status != null">status=#{status},</if>
            <if test="defaulted != null">defaulted=#{defaulted},</if>
            <if test="remark != null">remark=#{remark},</if>
        </set>
        where id=#{id}
    </update>
</mapper>

三、SysDictTypeMapper.xml

<?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.ctgu.web.mapper.SysDictTypeMapper">

    <insert id="save">
        insert into sys_dict_type
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null and name != ''">name,</if>
            <if test="code != null and code !=''">code,</if>
            <if test="status != null and status !=''">status,</if>
            <if test="remark != null and remark !=''">remark,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null and name != ''">#{name},</if>
            <if test="code != null and code !=''">#{code},</if>
            <if test="status != null and status !=''">#{status},</if>
            <if test="remark != null and remark !=''">#{remark},</if>
        </trim>
    </insert>

    <delete id="removeByIds">
        delete from sys_dict_type
        <where>
            <foreach item="item" index="index" collection="list"
                     open="id in (" separator="," close=")" nullable="true">
                #{item}
            </foreach>
        </where>
    </delete>

    <select id="list" resultType="string">
        select code from sys_dict_type
        <where>
            <foreach item="item" index="index" collection="list"
                     open="id in (" separator="," close=")" nullable="true">
                #{item}
            </foreach>
        </where>
    </select>

    <select id="page" resultType="SysDictType">
        SELECT id, name, code , status, remark
        FROM sys_dict_type
        GROUP BY id asc
    </select>

    <select id="getOne" resultType="SysDictType">
        SELECT id , name, code, status , remark
        FROM sys_dict_type
        where id = #{id}
    </select>

    <update id="updateById">
        update sys_dict_type
        <set>
            <if test="code != null">code=#{code},</if>
            <if test="name != null">name=#{name},</if>
            <if test="status != null">status=#{status},</if>
            <if test="remark != null">remark=#{remark},</if>
        </set>
        where id=#{id}
    </update>
</mapper>

四、SysMenuMapper.xml

<?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.ctgu.web.mapper.SysMenuMapper">

    <!-- 菜单路由映射 -->
    <resultMap id="RouteMap" type="com.ctgu.web.pojo.bo.RouteBO">
        <id property="id" column="id" jdbcType="BIGINT"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <result property="parentId" column="parent_id" jdbcType="BIGINT"/>
        <result property="path" column="path" jdbcType="VARCHAR"/>
        <result property="component" column="component" jdbcType="VARCHAR"/>
        <result property="redirectUrl" column="redirect_url" jdbcType="VARCHAR"/>
        <result property="icon" column="icon" jdbcType="VARCHAR"/>
        <result property="sort" column="sort" jdbcType="INTEGER"/>
        <result property="visible" column="visible" jdbcType="BOOLEAN"/>
        <result property="type" column="type" jdbcType="INTEGER"
                typeHandler="com.ctgu.web.handler.MenuTypeEnumHandler"/>
        <collection property="roles" ofType="string" javaType="list">
            <result column="code"/>
        </collection>
    </resultMap>

    <resultMap id="listMap" type="SysMenu">
        <id property="id" column="id" jdbcType="BIGINT"/>
        <result property="parentId" column="parent_id" jdbcType="BIGINT"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <result property="type" column="type" javaType="com.ctgu.common.enums.MenuTypeEnum"
                typeHandler="com.ctgu.web.handler.MenuTypeEnumHandler"/>
        <result property="path" column="path" jdbcType="VARCHAR"/>
        <result property="component" column="component" jdbcType="VARCHAR"/>
        <result property="perm" column="perm" jdbcType="VARCHAR"/>
        <result property="visible" column="visible" jdbcType="BOOLEAN"/>
        <result property="sort" column="sort" jdbcType="INTEGER"/>
        <result property="icon" column="icon" jdbcType="VARCHAR"/>
        <result property="redirect" column="redirect_url" jdbcType="VARCHAR"/>
    </resultMap>

    <resultMap id="saveMap" type="SysMenu">
        <result property="parentId" column="parent_id" jdbcType="BIGINT"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <result property="type" column="type" javaType="com.ctgu.common.enums.MenuTypeEnum"
                typeHandler="com.ctgu.web.handler.MenuTypeEnumHandler"/>
        <result property="path" column="path" jdbcType="VARCHAR"/>
        <result property="component" column="component" jdbcType="VARCHAR"/>
        <result property="perm" column="perm" jdbcType="VARCHAR"/>
        <result property="visible" column="visible" jdbcType="BOOLEAN"/>
        <result property="sort" column="sort" jdbcType="INTEGER"/>
        <result property="icon" column="icon" jdbcType="VARCHAR"/>
        <result property="redirect" column="redirect_url" jdbcType="VARCHAR"/>
    </resultMap>

    <insert id="save">
        insert into sys_menu
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="parentId!=null">parent_id,</if>
            <if test="name!=null">name,</if>
            <if test="type!=null">type,</if>
            <if test="path!=null">path,</if>
            <if test="component!=null">component,</if>
            <if test="perm!=null">perm,</if>
            <if test="visible!=null">visible,</if>
            <if test="sort!=null">sort,</if>
            <if test="icon!=null">icon,</if>
            <if test="redirect!=null">redirect,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="parentId!=null">#{parentId},</if>
            <if test="name!=null">#{name},</if>
            <if test="type!=null">#{type,typeHandler=com.ctgu.web.handler.MenuTypeEnumHandler},</if>
            <if test="path!=null">#{path},</if>
            <if test="component!=null">#{component},</if>
            <if test="perm!=null">#{perm},</if>
            <if test="visible!=null">#{visible},</if>
            <if test="sort!=null">#{sort},</if>
            <if test="icon!=null">#{icon},</if>
            <if test="redirect!=null">#{redirect},</if>
        </trim>
    </insert>

    <update id="updateById">
        update sys_menu
        <set>
            <if test="parentId!=null">parent_id=#{parentId},</if>
            <if test="name!=null">name=#{name},</if>
            <if test="type!=null">type=#{type},</if>
            <if test="path!=null">path=#{path},</if>
            <if test="component!=null">component=#{component},</if>
            <if test="perm!=null">perm=#{perm},</if>
            <if test="visible!=null">visible=#{visible},</if>
            <if test="sort!=null">sort=#{sort},</if>
            <if test="icon!=null">icon=#{icon},</if>
            <if test="redirect!=null">redirect=#{redirect},</if>
        </set>
        where id=#{id}
    </update>

    <select id="list" resultMap="listMap">
        select id
             , parent_id
             , name
             , type
             , path
             , component
             , perm
             , visible
             , sort
             , icon
             , redirect_url
             , redirect
        from sys_menu
        group by id asc
        order by sort asc
    </select>

    <select id="getById" resultMap="listMap">
        select id,
               parent_id,
               name,
               type,
               path,
               component,
               perm,
               visible,
               sort,
               icon,
               redirect_url,
               redirect
        from sys_menu
        where id = #{id}
    </select>

    <delete id="remove">
        delete
        from sys_menu
        where id = #{id}
    </delete>

    <select id="listRoutes" resultMap="RouteMap">
        SELECT t1.id,
               t1.name,
               t1.parent_id,
               t1.path,
               t1.component,
               t1.icon,
               t1.sort,
               t1.visible,
               t1.redirect_url,
               t1.type,
               t3.code
        FROM sys_menu t1
                 LEFT JOIN sys_role_menu t2 ON t1.id = t2.menu_id
                 LEFT JOIN sys_role t3 ON t2.role_id = t3.id
        WHERE t1.type != '4'
        ORDER BY t1.sort asc
    </select>

    <select id="listRolePerms" resultType="java.lang.String">
        SELECT
        DISTINCT t1.perm
        FROM
        sys_menu t1
        INNER JOIN sys_role_menu t2
        ON t1.id=t2.menu_id
        INNER JOIN sys_role t3
        ON t2.role_id=t3.id
        WHERE
        t1.type = '${@com.ctgu.common.enums.MenuTypeEnum@BUTTON.getValue()}'
        AND t1.perm IS NOT NULL
        <choose>
            <when test="roles!=null and roles.size()>0">
                AND t3.CODE IN
                <foreach collection="roles" item="role" separator="," open="(" close=")">
                    #{role}
                </foreach>
            </when>
            <otherwise>
                AND t1.id = -1
            </otherwise>
        </choose>
    </select>

</mapper>

五、SysRoleMapper.xml

<?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.ctgu.web.mapper.SysRoleMapper">
    <select id="list" resultType="SysRole">
        select *
        from sys_role
        where code != 'ROOT'
        order by sort asc
    </select>

    <select id="count" resultType="long">
        select count(1)
        from sys_role
        where id = #{id}
    </select>

    <update id="updateById">
        update sys_role
        <set>
            <if test="name != null">name=#{name},</if>
            <if test="code != null">code=#{code},</if>
            <if test="sort != null">sort=#{sort},</if>
            <if test="status != null">status=#{status},</if>
            <if test="dataScope != null">data_scope=#{dataScope},</if>
            <if test="deleted != null">deleted=#{deleted},</if>
        </set>
        where id=#{id}
    </update>

    <select id="page" resultType="SysRole">
        select id,name,code,sort,status,data_scope,deleted,create_time,update_time
        from sys_role
        <where>
            and code !='ROOT'
            <if test="keywords != null">
                and(
                name like CONCAT('%',#{keywords},'%')
                or code like CONCAT('%',#{keywords},'%')
                )
            </if>
        </where>
    </select>

    <delete id="removeByIds">
        delete
        from sys_role
        <where>
            <foreach item="item" index="index" collection="list"
                     open="ID in (" separator="," close=")" nullable="true">
                #{item}
            </foreach>
        </where>
    </delete>

    <insert id="save">
        insert into sys_role
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null and name != ''">
                name,
            </if>
            <if test="code != null and code !=''">
                code,
            </if>
            <if test="sort != null and sort !=''">
                sort,
            </if>
            <if test="status != null and status !=''">
                status,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null and name != ''">
                #{name},
            </if>
            <if test="code != null and code !=''">
                #{code},
            </if>
            <if test="sort != null and sort !=''">
                #{sort},
            </if>
            <if test="status != null and status !=''">
                #{status},
            </if>
        </trim>
    </insert>

    <select id="getById" resultType="SysRole">
        select id, name, code, status
        from sys_role
        where id = #{id}
    </select>
</mapper>

六、SysRoleMenuMapper.xml

<?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.ctgu.web.mapper.SysRoleMenuMapper">

    <!-- 获取角色拥有的菜单ID集合 -->
    <select id="listMenuIdsByRoleId" resultType="java.lang.Long">
        SELECT rm.menu_id
        FROM sys_role_menu rm
                 INNER JOIN sys_menu m ON rm.menu_id = m.id
        WHERE rm.role_id = #{roleId}
    </select>

    <delete id="remove">
        delete
        from sys_role_menu
        where role_id = #{roleId}
    </delete>

    <insert id="saveBatch">
        INSERT INTO sys_role_menu (
        role_id
        ,menu_id
        ) VALUES
        <foreach collection="list" item="item" separator=",">
            (
            #{item.roleId}
            ,#{item.menuId}
            )
        </foreach>
    </insert>

</mapper>

七、SysUserMapper.xml

<?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.ctgu.web.mapper.SysUserMapper">
    <resultMap id="UserFormMap" type="com.ctgu.web.pojo.form.UserForm">
        <id property="id" column="id" jdbcType="BIGINT"/>
        <result property="username" column="username" jdbcType="VARCHAR"/>
        <result property="nickname" column="nickname" jdbcType="VARCHAR"/>
        <result property="mobile" column="mobile" jdbcType="VARCHAR"/>
        <result property="gender" column="gender" jdbcType="TINYINT"/>
        <result property="avatar" column="avatar" jdbcType="VARCHAR"/>
        <result property="email" column="email" jdbcType="VARCHAR"/>
        <result property="status" column="status" jdbcType="BOOLEAN"/>
        <result property="deptId" column="dept_id" jdbcType="BIGINT"></result>
        <collection
                property="roleIds"
                column="id"
                select="com.ctgu.web.mapper.SysUserRoleMapper.listRoleIdsByUserId">
            <result column="role_id"/>
        </collection>
    </resultMap>

    <resultMap id="UserPageMap" type="com.ctgu.web.pojo.vo.UserPageVO">
        <id property="id" column="id" jdbcType="BIGINT"/>
        <result property="username" column="username" jdbcType="VARCHAR"/>
        <result property="nickname" column="nickname" jdbcType="VARCHAR"/>
        <result property="mobile" column="mobile" jdbcType="VARCHAR"/>
        <result property="genderLabel" column="gender" jdbcType="TINYINT" typeHandler="com.ctgu.web.handler.GenderEnumHandler"/>
        <result property="avatar" column="avatar" jdbcType="VARCHAR"/>
        <result property="email" column="email" jdbcType="VARCHAR"/>
        <result property="status" column="status" jdbcType="BOOLEAN"/>
        <result property="deptName" column="dept_name" jdbcType="VARCHAR"></result>
        <result property="roleNames" column="roleNames" jdbcType="VARCHAR"></result>
        <result property="createTime" column="create_time" jdbcType="VARCHAR"></result>
    </resultMap>

    <select id="getUserPage" resultMap="UserPageMap">
        SELECT
        u.id,
        u.username,
        u.nickname,
        u.mobile,
        u.gender,
        u.avatar,
        u.STATUS,
        d.NAME AS dept_name,
        GROUP_CONCAT( r.NAME ) AS roleNames,
        u.create_time
        FROM
        sys_user u
        LEFT JOIN sys_dept d ON u.dept_id = d.id
        LEFT JOIN sys_user_role sur ON u.id = sur.user_id
        LEFT JOIN sys_role r ON sur.role_id = r.id
        <where>
            u.username != 'root'
            <if test='keywords!=null and keywords.trim() neq ""'>
                AND (
                u.username LIKE CONCAT('%',#{keywords},'%')
                OR u.nickname LIKE CONCAT('%',#{keywords},'%')
                OR u.mobile LIKE CONCAT('%',#{keywords},'%')
                )
            </if>
            <if test='status!=null'>
                AND u.status = #{status}
            </if>
            <if test='deptId!=null'>
                AND concat(',',concat(d.tree_path,',',d.id),',') like concat('%,',#{deptId},',%')
            </if>
        </where>
        GROUP BY u.id
    </select>

    <insert id="save" useGeneratedKeys="true" keyProperty="id">
        insert into sys_user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username != null and username != ''">
                username,
            </if>
            <if test="nickname != null and nickname !=''">
                nickname,
            </if>
            <if test="gender != null and gender !=''">
                gender,
            </if>
            <if test="password != null and password !=''">
                password,
            </if>
            <if test="deptId != null and deptId !=''">
                dept_id,
            </if>
            <if test="avatar != null and avatar !=''">
                avatar,
            </if>
            <if test="mobile != null and mobile !=''">
                mobile,
            </if>
            <if test="status != null and status !=''">
                status,
            </if>
            <if test="email != null and email !=''">
                email,
            </if>
            <if test="deleted != null and deleted !=''">
                deleted,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="username != null and username != ''">
                #{username},
            </if>
            <if test="nickname != null and nickname !=''">
                #{nickname},
            </if>
            <if test="gender != null and gender !=''">
                #{gender},
            </if>
            <if test="password != null and password !=''">
                #{password},
            </if>
            <if test="deptId != null and deptId !=''">
                #{deptId},
            </if>
            <if test="avatar != null and avatar !=''">
                #{avatar},
            </if>
            <if test="mobile != null and mobile !=''">
                #{mobile},
            </if>
            <if test="status != null and status !=''">
                #{status},
            </if>
            <if test="email != null and email !=''">
                #{email},
            </if>
            <if test="deleted != null and deleted !=''">
                #{deleted},
            </if>
        </trim>
    </insert>

    <select id="getUserDetail" resultMap="UserFormMap">
        SELECT id,
               username,
               nickname,
               mobile,
               gender,
               avatar,
               email,
               STATUS,
               dept_id
        FROM sys_user
        WHERE id = #{userId}
    </select>

    <update id="updateById">
        update sys_user u
        <set>
            <if test="username != null">username=#{username},</if>
            <if test="nickname != null">nickname=#{nickname},</if>
            <if test="gender != null">gender=#{gender},</if>
            <if test="deptId != null">dept_id=#{deptId},</if>
            <if test="avatar != null">avatar=#{avatar},</if>
            <if test="status != null">status=#{status},</if>
            <if test="email != null">email=#{email},</if>
            <if test="deleted != null">deleted=#{deleted},</if>
        </set>
        where u.id=#{id}
    </update>

    <delete id="removeByIds">
        delete from sys_user
        <where>
            <foreach item="item" index="index" collection="list"
                     open="id in (" separator="," close=")" nullable="true">
                #{item}
            </foreach>
        </where>
    </delete>

    <!-- 用户认证信息映射 -->
    <resultMap id="UserAuthMap" type="com.ctgu.web.pojo.bo.UserAuthInfo">
        <id property="userId" column="userId" jdbcType="BIGINT"/>
        <result property="username" column="username" jdbcType="VARCHAR"/>
        <result property="password" column="password" jdbcType="VARCHAR"/>
        <result property="status" column="status" jdbcType="BOOLEAN"/>
        <result property="deptId" column="dept_id" jdbcType="BIGINT"></result>
        <collection property="roles" column="username" ofType="list"
                    select="com.ctgu.web.mapper.SysUserRoleMapper.getRoleName">
        </collection>
    </resultMap>
    <!-- 根据用户名获取认证信息 -->
    <select id="getUserAuthInfo" resultMap="UserAuthMap">
        SELECT t1.id userId,t1.username,t1.nickname,t1.PASSWORD, t1.STATUS,t1.dept_id
        FROM sys_user t1
        WHERE t1.username = #{username}
    </select>

    <select id="getUserByName" resultType="SysUser">
        SELECT t1.id, t1.username, t1.nickname,t1.password,t1.status,t1.dept_id,t1.avatar
        FROM sys_user t1
        WHERE t1.username = #{username}
    </select>

    <!-- 获取用户导出列表 -->
    <select id="listExportUsers">
        SELECT
        u.username,
        u.nickname,
        u.mobile,
        CASE u.gender
        WHEN 1 THEN '男'
        WHEN 2 THEN '女'
        ELSE '未知'
        END gender,
        d.NAME AS dept_name,
        u.create_time
        FROM
        sys_user u
        LEFT JOIN sys_dept d ON u.dept_id = d.id
        <where>
            u.deleted = 0 AND u.username != 'root'
            <if test='keywords!=null and keywords.trim() neq ""'>
                AND (u.username LIKE CONCAT('%',#{keywords},'%')
                OR u.nickname LIKE CONCAT('%',#{keywords},'%')
                OR u.mobile LIKE CONCAT('%',#{keywords},'%'))
            </if>
            <if test='status!=null'>
                AND u.status = #{status}
            </if>
            <if test='deptId!=null'>
                AND concat(',',concat(d.tree_path,',',d.id),',') like concat('%,',#{deptId},',%')
            </if>
        </where>
        GROUP BY u.id
    </select>

    <update id="updatePwd">
        update sys_user
        set password=#{pwd}
        where id = #{userId}
    </update>
</mapper>

八、SysUserRoleMapper.xml

<?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.ctgu.web.mapper.SysUserRoleMapper">

    <!-- 根据用户ID获取角色ID集合 -->
    <select id="list" resultType="java.lang.Long">
        SELECT role_id
        FROM sys_user_role
        WHERE user_id = #{userId}
    </select>

    <insert id="saveBatch" parameterType="list">
        insert into sys_user_role(user_id,role_id)
        values
        <foreach collection="list" index="index" item="item" separator=",">
            (#{item.userId},#{item.roleId})
        </foreach>
    </insert>

    <delete id="remove" parameterType="list">
        delete
        from sys_user_role
        where user_id=#{userId}
        and role_id in
        <foreach collection="list" open="(" close=")" item="item" separator=",">
            #{item}
        </foreach>
    </delete>

    <select id="listRoleIdsByUserId" resultType="java.lang.Long">
        SELECT role_id
        FROM sys_user_role
        WHERE user_id = #{userId}
    </select>

    <select id="getRoleName" resultType="java.lang.String">
        SELECT t3.CODE
        FROM sys_user t1
                 LEFT JOIN sys_user_role t2 ON t2.user_id = t1.id
                 LEFT JOIN sys_role t3 ON t3.id = t2.role_id
        WHERE t1.username = #{username}
          AND t1.deleted = 0
    </select>

</mapper>

九、application.properties

server.port=20002
spring.application.name=artemis
spring.profiles.active=dev
spring.mvc.pathmatch.matching-strategy=ant_path_matcher

mybatis.configuration.map-underscore-to-camel-case=true
mybatis.mapper-locations=classpath*:mapper/*.xml
mybatis.type-aliases-package=com.ctgu.web.pojo.entity
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

easycaptcha.enable=true
easycaptcha.type=arithmetic
easycaptcha.ttl=120

十、application-dev.properties

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/youlai_boot?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8&serverTimeZone=Asia/Shanghai&autoReconnect=true&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=123456

spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-active=30
spring.datasource.druid.filter.stat.db-type=mysql
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=200

auth.token.secret_key=SecretKey012345678901234567890123456789012345678901234567890123456789
auth.token.ttl=18000

十一、logback-spring.xml

<configuration>
    <appender name="console" class="ch.qos.logback.core.ConsoleAppender">
        <encoder charset="UTF-8">
             <pattern>[%d{yyyy-MM-dd HH:mm:ss.SSS}] %level [%thread] %file:%line - %msg%n</pattern>
            <charset>UTF-8</charset>
        </encoder>
    </appender>

    <appender name="info" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <file>${catalina.base:-.}/logs/artemis_info.log</file>
        <rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
            <fileNamePattern>${catalina.base:-.}/logs/artemis_info.%d{yyyy-MM-dd}.%i.log</fileNamePattern>
            <maxHistory>30</maxHistory>
            <maxFileSize>100MB</maxFileSize>
        </rollingPolicy>
        <filter class="ch.qos.logback.classic.filter.LevelFilter">
            <level>INFO</level>
            <onMatch>ACCEPT</onMatch>
            <onMismatch>DENY</onMismatch>
        </filter>
        <encoder charset="UTF-8">
            <pattern>[%d{yyyy-MM-dd HH:mm:ss.SSS}] %level [%thread] %file:%line - %msg%n</pattern>
            <charset>UTF-8</charset>
        </encoder>
    </appender>

    <appender name="error" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <file>${catalina.base:-.}/logs/artemis_error.log</file>
        <rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
            <fileNamePattern>${catalina.base:-.}/logs/artemis_error.%d{yyyy-MM-dd}.%i.log</fileNamePattern>
            <maxHistory>30</maxHistory>
            <maxFileSize>100MB</maxFileSize>
        </rollingPolicy>
        <filter class="ch.qos.logback.classic.filter.LevelFilter">
            <level>ERROR</level>
            <onMatch>ACCEPT</onMatch>
            <onMismatch>DENY</onMismatch>
        </filter>
        <encoder charset="UTF-8">
            <pattern>[%d{yyyy-MM-dd HH:mm:ss.SSS}] %level [%thread] %file:%line - %msg%n</pattern>
            <charset>UTF-8</charset>
        </encoder>
    </appender>

    <appender name="bigTimeReqLog" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <file>${catalina.base:-.}/logs/bigTimeReq_info.log</file>
        <rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
            <fileNamePattern>${catalina.base:-.}/logs/bigTimeReqLog_info.%d{yyyy-MM-dd}.%i.log</fileNamePattern>
            <maxHistory>30</maxHistory>
            <maxFileSize>100MB</maxFileSize>
        </rollingPolicy>
        <filter class="ch.qos.logback.classic.filter.LevelFilter">
            <level>DEBUG</level>
            <onMatch>ACCEPT</onMatch>
            <onMismatch>DENY</onMismatch>
        </filter>
        <encoder charset="UTF-8">
            <pattern>[%d{yyyy-MM-dd HH:mm:ss.SSS}] %level [%thread] %file:%line - %msg%n</pattern>
            <charset>UTF-8</charset>
        </encoder>
    </appender>

    <logger name="bigTimeReq" level="info">
        <appender-ref ref="bigTimeReqLog"/>
    </logger>

    <root level="info">
        <appender-ref ref="console"/>
        <appender-ref ref="info"/>
        <appender-ref ref="error"/>
    </root>

</configuration>
shine4j
关注
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值