pg 数据库 mapper.xml 配置CRUD 相关 sql.xml

Question

<?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">


<!-- 保存职场信息 -->

insert into job_market(id,jm_code,jm_name,jm_url,jm_status,status,create_time,update_time,create_user,update_user)
values(#{id},#{jmCode},#{jmName},#{jmUrl},#{jmStatus},#{status},
to_timestamp(to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')::timestamp without time zone,
to_timestamp(to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')::timestamp without time zone,
#{createUser},#{updateUser})

<!-- 修改职场信息 -->
<update id="updateJobMarket" parameterType="com.paic.xface.dto.JobMarketDTO">
    update job_market set 
        jm_name = #{jmName},
        jm_url = #{jmUrl},
        jm_status = #{jmStatus},
        update_time = to_timestamp(to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')::timestamp without time zone,
        update_user = #{updateUser}
    where id = #{id}
</update>

<!-- 更新职场信息记录状态 -->
<update id="updateJobRecordStatus" parameterType="com.paic.xface.dto.JobMarketDTO">
    update job_market set 
        status = #{status},
        update_time = to_timestamp(to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')::timestamp without time zone,
        update_user = #{updateUser}
    where id = #{id}
</update>

<!-- 保存考勤信息 -->
<insert id="saveAttendanceInfo" parameterType="com.paic.xface.dto.JobMarketDTO">
    insert into attendance_info_rel(id,jm_code,table_name,attendance_time,create_time,update_time,create_user,update_user) 
    values(#{id},#{jmCode},#{tableName},#{attendanceTime},
    to_timestamp(to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')::timestamp without time zone,
    to_timestamp(to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')::timestamp without time zone,
    #{createUser},#{updateUser})
</insert>

<!-- 修改考勤信息 -->
<update id="updateAttendanceInfo" parameterType="com.paic.xface.dto.JobMarketDTO">
    update attendance_info_rel set 
        table_name = #{tableName},
        update_time = to_timestamp(to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')::timestamp without time zone,
        update_user = #{updateUser}
    where id = #{id}
</update>

<!-- 查询职场信息 -->
<select id="searchJobMarket" parameterType="com.paic.xface.dto.JobMarketDTO" resultType="com.paic.xface.dto.JobMarketDTO">
    SELECT
        row_number() OVER (ORDER BY jm.create_time) as rowNum,
        jm.id id, 
        jm.jm_code jmCode,
        jm.jm_name jmName,
        jm.jm_url jmUrl,
        jm.jm_status jmStatus,
        cbdjms.dic_name jmStatusName,
        jm.status status,
        cbds.dic_name statusName,
        jm.create_time createTime,
        jm.create_time updateTime,
        jm.create_user createUser,
        jm.update_user updateUser
    FROM job_market jm
    INNER JOIN cwa_bse_dictionary cbdjms on cbdjms.dic_value = jm.jm_status and cbdjms.dic_type = 'JM_STATUS'
    INNER JOIN cwa_bse_dictionary cbds on cbds.dic_value = jm.status and cbds.dic_type = 'JM_RECORD_STATUS'
    <where>
        <if test="jmCode != null and jmCode != ''">
            and jm.jm_code like '%' || #{jmCode} || '%'
        </if>
        <if test="jmName != null and jmName != ''">
            and jm.jm_name like '%' || #{jmName} || '%'
        </if>
        <if test="jmStatus != null and jmStatus != ''">
            and jm.jm_status = #{jmStatus}
        </if>
    </where>
    limit #{pageSize} offset #{start}
</select>
<select id="searchJobMarketCount" parameterType="com.paic.xface.dto.JobMarketDTO" resultType="java.lang.Integer">
    SELECT count(1) FROM job_market jm
    <where>
        <if test="jmCode != null and jmCode != ''">
            and jm.jm_code like '%' || #{jmCode} || '%'
        </if>
        <if test="jmName != null and jmName != ''">
            and jm.jm_name like '%' || #{jmName} || '%'
        </if>
        <if test="jmStatus != null and jmStatus != ''">
            and jm.jm_status = #{jmStatus}
        </if>
    </where>
</select>

<!-- 查询职场信息和考勤信息 -->
<select id="selectOneJobMarketAttInfo" parameterType="com.paic.xface.dto.JobMarketDTO" resultType="com.paic.xface.dto.JobMarketDTO">
    SELECT
        jm.id id, 
        jm.jm_code jmCode,
        jm.jm_name jmName,
        jm.jm_url jmUrl,
        jm.jm_status jmStatus,
        jm.status status,
        air.id ariId,
        air.jm_code airJmCode,
        air.table_name tableName,
        air.attendance_time attendanceTime
    FROM job_market jm
    INNER JOIN attendance_info_rel air on air.jm_code = jm.jm_code and air.id = jm.id
    WHERE jm.id = #{id}
</select>

<!-- 检查职场编码在职场信息表中是否存在 -->
<select id="isExistJmCode" parameterType="com.paic.xface.dto.JobMarketDTO" resultType="java.lang.Integer">
    select count(1) from job_market jm 
    where jm.jm_code = #{jmCode}
</select>

<!-- 检查职场编码和考勤记录时间是否存在 -->
<select id="isExistJmCodeAttendanceTime" parameterType="com.paic.xface.dto.JobMarketDTO" resultType="java.lang.Integer">
    select count(1) from attendance_info_rel air 
    where air.jm_code = #{jmCode} and air.attendance_time = #{attendanceTime}
</select>

<!-- 根据字典状态查询字典数据 -->
<select id="queryDicDataByDicType" parameterType="java.lang.String" resultType="java.util.Map">
    select dic_name dicName, dic_value dicValue from cwa_bse_dictionary where dic_type = #{dicType}
</select>

Answer 1

这是遇到什么问题了？?