<?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.cmcc.vrp.chongqing.dao.AdministerMapper" >
<resultMap id="BaseResultMap" type="com.cmcc.vrp.chongqing.model.Administer" >
<id column="id" property="id" jdbcType="BIGINT" />
<result column="user_name" property="userName" jdbcType="VARCHAR" />
<result column="password" property="password" jdbcType="VARCHAR" />
<result column="mobile_phone" property="mobilePhone" jdbcType="VARCHAR" />
<result column="creator_id" property="creatorId" jdbcType="BIGINT" />
<result column="pic_url" property="picUrl" jdbcType="VARCHAR" />
<result column="email" property="email" jdbcType="VARCHAR" />
<result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
<result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
<result column="delete_flag" property="deleteFlag" jdbcType="INTEGER" />
<result column="citys" property="citys" jdbcType="VARCHAR" />
<result column="role_id" property="roleId" jdbcType="BIGINT" />
</resultMap>
<resultMap id="ExtendsResultMap" extends="BaseResultMap" type="com.cmcc.vrp.chongqing.model.Administer" >
<result column="roleName" property="roleName" jdbcType="VARCHAR" />
</resultMap>
<resultMap id="ResultMapAuthority" type="com.cmcc.vrp.chongqing.model.Authority" >
<result column="AUTHORITY_NAME" property="authorityName" jdbcType="VARCHAR" />
</resultMap>
<sql id="Base_Column_List" >
id, user_name, password, mobile_phone, creator_id, pic_url, email, create_time, update_time,
delete_flag, citys
</sql>
<sql id="WhereClase">
<where>
<if test="userName != null">
AND T.user_name LIKE '%${userName}%'
</if>
<if test="password != null">
AND T.password = #{password,jdbcType=VARCHAR}
</if>
<if test="mobilePhone != null">
AND T.mobile_phone = #{mobilePhone,jdbcType=VARCHAR}
</if>
<if test="picUrl != null">
AND T.pic_url = #{picUrl,jdbcType=VARCHAR}
</if>
<if test="email != null">
AND T.email = #{email,jdbcType=VARCHAR}
</if>
<if test="createTime != null">
AND T.create_time = #{createTime,jdbcType=TIMESTAMP}
</if>
<if test="updateTime != null">
AND T.update_time = #{updateTime,jdbcType=TIMESTAMP}
</if>
<if test="1==1">
AND T.delete_flag = 0
</if>
</where>
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
select
id, user_name, password, mobile_phone, creator_id, pic_url, email, create_time, update_time,
delete_flag, citys,b.role_id
from chongqing_administer a left join chongqing_admin_role b
on a.id=b.admin_id
where a.id = #{id,jdbcType=BIGINT}
</select>
<!-- 根据手机号码查询用户对象 -->
<select id="selectByMobilePhone" resultMap="BaseResultMap" parameterType="java.lang.String" >
select
<include refid="Base_Column_List" />
from chongqing_administer
where
mobile_phone = #{phone,jdbcType=VARCHAR} and
delete_flag = 0
</select>
<select id="selectByUserName" resultMap="BaseResultMap"
parameterType="java.lang.String">
select
<include refid="Base_Column_List" />
from chongqing_administer
where
user_name = #{userName,jdbcType=VARCHAR} and
delete_flag = 0
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
delete from chongqing_administer
where id = #{id,jdbcType=BIGINT}
</delete>
<insert id="insert" parameterType="com.cmcc.vrp.chongqing.model.Administer" useGeneratedKeys="true" keyProperty="id">
insert into chongqing_administer (id, user_name, password,
mobile_phone, creator_id, pic_url,
email, create_time, update_time,
delete_flag, citys)
values (#{id,jdbcType=BIGINT}, #{userName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR},
#{mobilePhone,jdbcType=VARCHAR}, #{creatorId,jdbcType=BIGINT}, #{picUrl,jdbcType=VARCHAR},
#{email,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP},
#{deleteFlag,jdbcType=INTEGER}, #{citys,jdbcType=VARCHAR})
</insert>
<insert id="insertSelective" parameterType="com.cmcc.vrp.chongqing.model.Administer" useGeneratedKeys="true" keyProperty="id">
insert into chongqing_administer
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
id,
</if>
<if test="userName != null" >
user_name,
</if>
<if test="password != null" >
password,
</if>
<if test="mobilePhone != null" >
mobile_phone,
</if>
<if test="creatorId != null" >
creator_id,
</if>
<if test="picUrl != null" >
pic_url,
</if>
<if test="email != null" >
email,
</if>
<if test="createTime != null" >
create_time,
</if>
<if test="updateTime != null" >
update_time,
</if>
<if test="deleteFlag != null" >
delete_flag,
</if>
<if test="citys != null" >
citys,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=BIGINT},
</if>
<if test="userName != null" >
#{userName,jdbcType=VARCHAR},
</if>
<if test="password != null" >
#{password,jdbcType=VARCHAR},
</if>
<if test="mobilePhone != null" >
#{mobilePhone,jdbcType=VARCHAR},
</if>
<if test="creatorId != null" >
#{creatorId,jdbcType=BIGINT},
</if>
<if test="picUrl != null" >
#{picUrl,jdbcType=VARCHAR},
</if>
<if test="email != null" >
#{email,jdbcType=VARCHAR},
</if>
<if test="createTime != null" >
#{createTime,jdbcType=TIMESTAMP},
</if>
<if test="updateTime != null" >
#{updateTime,jdbcType=TIMESTAMP},
</if>
<if test="deleteFlag != null" >
#{deleteFlag,jdbcType=INTEGER},
</if>
<if test="citys != null" >
#{citys,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.cmcc.vrp.chongqing.model.Administer" >
update chongqing_administer
<set >
<if test="userName != null" >
user_name = #{userName,jdbcType=VARCHAR},
</if>
<if test="password != null" >
password = #{password,jdbcType=VARCHAR},
</if>
<if test="mobilePhone != null" >
mobile_phone = #{mobilePhone,jdbcType=VARCHAR},
</if>
<if test="creatorId != null" >
creator_id = #{creatorId,jdbcType=BIGINT},
</if>
<if test="picUrl != null" >
pic_url = #{picUrl,jdbcType=VARCHAR},
</if>
<if test="email != null" >
email = #{email,jdbcType=VARCHAR},
</if>
<if test="createTime != null" >
create_time = #{createTime,jdbcType=TIMESTAMP},
</if>
<if test="updateTime != null" >
update_time = #{updateTime,jdbcType=TIMESTAMP},
</if>
<if test="deleteFlag != null" >
delete_flag = #{deleteFlag,jdbcType=INTEGER},
</if>
<if test="citys != null" >
citys = #{citys,jdbcType=VARCHAR},
</if>
</set>
where id = #{id,jdbcType=BIGINT}
</update>
<update id="updateByPrimaryKey" parameterType="com.cmcc.vrp.chongqing.model.Administer" >
update chongqing_administer
set user_name = #{userName,jdbcType=VARCHAR},
password = #{password,jdbcType=VARCHAR},
mobile_phone = #{mobilePhone,jdbcType=VARCHAR},
creator_id = #{creatorId,jdbcType=BIGINT},
pic_url = #{picUrl,jdbcType=VARCHAR},
email = #{email,jdbcType=VARCHAR},
create_time = #{createTime,jdbcType=TIMESTAMP},
update_time = #{updateTime,jdbcType=TIMESTAMP},
delete_flag = #{deleteFlag,jdbcType=INTEGER},
citys = #{citys,jdbcType=VARCHAR}
where id = #{id,jdbcType=BIGINT}
</update>
<update id="updatePasswordByKey" parameterType="java.util.Map">
update
chongqing_administer
set
password=#{password,jdbcType=VARCHAR}
where
id = #{id,jdbcType=BIGINT}
</update>
<update id="updatePasswordByMobile" parameterType="java.util.Map">
update
chongqing_administer
set
password=#{password,jdbcType=VARCHAR}
where
mobile_phone = #{mobilePhone,jdbcType=VARCHAR}
</update>
<select id="selectAllAdministers" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List" />
from
chongqing_administer T
where
T.delete_flag = 0
</select>
<!-- 用户管理使用分页类 -->
<select id="queryPaginationAdminCount" parameterType="java.util.Map"
resultType="java.lang.Integer">
SELECT
count(0)
FROM chongqing_administer t1 LEFT JOIN chongqing_admin_role t3
ON t1.id =
t3.admin_id LEFT
JOIN chongqing_role t2 ON t2.role_id =
t3.role_id
WHERE
t1.delete_flag = 0
<if test="userName != null and userName != ''">
AND t1.user_name like CONCAT('%',CONCAT(#{userName},'%'))
</if>
</select>
<!-- 用户管理使用分页类 -->
<select id="queryPaginationAdminList" parameterType="java.util.Map"
resultMap="ExtendsResultMap">
SELECT
t1.id,t1.user_name,t1.mobile_phone,t1.creator_id,t1.email,t1.create_time,t1.update_time,
t2.name as roleName ,t1.delete_flag
FROM chongqing_administer t1 LEFT JOIN
chongqing_admin_role t3 ON t1.id = t3.admin_id LEFT
JOIN chongqing_role t2 ON t2.role_id = t3.role_id
WHERE
t1.delete_flag = 0
<if test="userName != null and userName != ''">
AND t1.user_name like CONCAT('%',CONCAT(#{userName},'%'))
</if>
<if test="mobilePhone != null and mobilePhone != ''">
AND t1.mobile_phone like CONCAT('%',CONCAT(#{mobilePhone},'%'))
</if>
ORDER BY
t1.update_time DESC,t1.create_time DESC
LIMIT
#{pageNum},
#{pageSize}
</select>
<!-- 根据手机号来检索数据库,如果有ID,则把这个ID的记录排除在外(用于手机号唯一性验证) -->
<select id="selectAdministerCountByPhoneExcludeId"
parameterType="java.util.Map" resultType="java.lang.Integer">
select
count(*)
from
chongqing_administer T
where
T.delete_flag = 0 and
mobile_phone = #{mobilePhone,jdbcType=VARCHAR}
<if test="id != null and id != ''" >
and T.id != #{id,jdbcType=INTEGER}
</if>
</select>
<select id="queryUserAuthories" parameterType="java.lang.String" resultMap="ResultMapAuthority">
SELECT distinct authority.AUTHORITY_NAME
FROM
chongqing_administer admin,
chongqing_admin_role adminRole,
chongqing_role role,
chongqing_role_authority roleAuthority,
chongqing_authority authority
WHERE
admin.id=adminRole.admin_id and
adminRole.role_id=role.role_id and
role.role_id=roleAuthority.role_id and
roleAuthority.authority_id=authority.AUTHORITY_ID and
admin.user_name=#{userName}
</select>
<select id="queryUserAuthoriesByMobile" parameterType="java.lang.String" resultMap="ResultMapAuthority">
SELECT distinct authority.AUTHORITY_NAME
FROM
chongqing_administer admin,
chongqing_admin_role adminRole,
chongqing_role role,
chongqing_role_authority roleAuthority,
chongqing_authority authority
WHERE
admin.id=adminRole.admin_id and
adminRole.role_id=role.role_id and
role.role_id=roleAuthority.role_id and
roleAuthority.authority_id=authority.AUTHORITY_ID and
admin.mobile_phone=#{mobilePhone}
</select>
<select id="selectByRoleCode" resultMap="BaseResultMap" parameterType="java.lang.Long">
SELECT ADMIN.*
FROM chongqing_administer ADMIN
LEFT JOIN chongqing_admin_role AR ON AR.admin_id=ADMIN.id
LEFT JOIN chongqing_role ROLE ON AR.role_id=ROLE.role_id
WHERE ROLE.`code` = #{roleCode}
</select>
<select id="selectUnUsedCardMarker" resultMap="BaseResultMap" parameterType="java.lang.Long">
SELECT ADMIN.*
FROM chongqing_administer ADMIN
LEFT JOIN chongqing_admin_role AR ON AR.admin_id=ADMIN.id
LEFT JOIN chongqing_role ROLE ON AR.role_id=ROLE.role_id
WHERE ROLE.`code` = #{roleCode} and ADMIN.id not in
(SELECT operator_id from chongqing_mdrc_cardmaker where delete_flag=0)
</select>
</mapper>
MyBatis框架DAO文件的配置
最新推荐文章于 2024-03-01 14:15:30 发布