resultMap

  1. <resultMap type="com.pb.party.entity.PbPartyDepartment" id="PbPartyDepartmentMap">
  2.     <result property="id" column="id" jdbcType="INTEGER"/>
  3.     <result property="name" column="name" jdbcType="VARCHAR"/>
  4.     <result property="creationTime" column="creation_time" jdbcType="TIMESTAMP"/>
  5.     <result property="introduction" column="introduction" jdbcType="LONGVARCHAR"/>
  6.     <result property="xCoordinate" column="x_coordinate" jdbcType="DOUBLE"/>
  7.     <result property="yCoordinate" column="y_coordinate" jdbcType="DOUBLE"/>
  8.     <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
  9.     <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
  10.     <result property="delFlag" column="del_flag" jdbcType="INTEGER"/>
  11.     <association property="project" javaType="com.pb.party.entity.PbProject" column="{id=project_id}" select="com.pb.party.mapper.PbProjectMapper.selectOneById"></association>
  12.     <association property="parent" javaType="com.pb.party.entity.PbPartyDepartment" column="{id=parent_id}" select="com.pb.party.mapper.PbPartyDepartmentMapper.selectOneById1"></association>
  13.     <collection property="files" javaType="java.util.ArrayList" column="{ids=file_id}" ofType="com.pb.party.entity.PbFile" select="com.pb.party.mapper.PbFileMapper.selectListId"></collection>
  16. <collection property="files" javaType="java.util.List" column="{ids=file_id}"
  17.             ofType="com.pb.projects.entity.PbFile" select="com.pb.projects.mapper.PbFileMapper.selectListId"></collection>
  18. <association property="file" javaType="com.pb.projects.entity.PbFile" column="{id=file_id}" select="com.pb.projects.mapper.PbFileMapper.selectOneById"></association>
  19. </resultMap>
  1. <resultMap type="com.pb.projects.vo.line.ResearchAchievementVo" id="researchAchievementMap">
  2.     <result property="ids" column="ids" jdbcType="VARCHAR"/>
  3.     <result property="type" column="type" jdbcType="VARCHAR"/>
  4.     <collection property="researchAchievementSecVos" javaType="java.util.List" column="{ids=ids}" ofType="com.pb.projects.vo.line.ResearchAchievementSecVo" select="com.pb.projects.mapper.PbProSubResearchMapper.selectResearchAchievementSec"></collection>
  5. </resultMap>
  1. <resultMap type="com.pb.projects.vo.line.ResearchAchievementSecVo" id="ResearchAchievementSecVo">
  2.     <result property="ResearchAchievementId" column="ResearchAchievementId" jdbcType="VARCHAR"/>
  3.     <result property="ResearchAchievementName" column="ResearchAchievementName" jdbcType="VARCHAR"/>
  4.     <collection property="files" javaType="java.util.List" column="{ids=file_id}"
  5.                 ofType="com.pb.projects.entity.PbFile" select="com.pb.projects.mapper.PbFileMapper.selectListId"></collection>
  6. </resultMap>

select

  1. <select id="selectPageById" resultType="com.pb.party.entity.PbPartyDepartment">
  2.         SELECT
  3.         *
  4.         from
  5.         pb_party_department
  6.         WHERE
  7.         pb_party_department.project_id=#{projectId}
  8.         AND pb_party_department.del_flag= 0
  9. </select>
  10. <select id="selectAllByPage" resultType="com.pb.party.entity.PbPartyDepartment">
  11.         SELECT
  12.         *
  13.         from
  14.         pb_party_department
  15.         WHERE
  16.         pb_party_department.del_flag= 0
  17. </select>
  1. <select id="problemPinNumber" resultType="com.pb.projects.entity.PbProProblem">
  2.         SELECT
  3.         pb_pro_sub.alias,
  4.         pb_pro_sub.`name`,
  5.         pb_pro_problem.*
  6.     FROM
  7.         pb_pro_problem
  8.         LEFT JOIN pb_pro_sub ON pb_pro_sub.id = pb_pro_problem.pro_sub_id
  9.     WHERE
  10.         pb_pro_sub.id = #{proSubId}
  11. </select>
  1. <!-- 换流站 待建/在建-换流容量 -->
  2. <select id="selectSubCommutationCapacity" resultType="java.util.Map">
  3.     SELECT
  4.     IFNULL(SUM(pro.capacity),0) AS 'capacity'
  5.     FROM
  6.     pb_pro pro
  7.     LEFT JOIN pb_pro_sub sub ON sub.parent_code = pro.`code`
  8.     <include refid="whereSql"></include>
  9. </select>
  1. <select id="selectAll" resultMap="PbOrganizationalLifeMap">
  2.     SELECT
  3.         life.*
  4.     FROM
  5.         pb_organizational_life life
  6.     <where>
  7.         life.del_flag = 0
  8.         <if test="partyId != null">
  9.             AND life.party_department_id = #{partyId}
  10.         </if>
  12.         <if test="title!='' and title!=null">
  13.             AND life.title LIKE CONCAT('%',#{title},'%')
  14.         </if>
  15.         <if test="title!='' and title!=null">
  16.             OR life.content LIKE CONCAT('%',#{title},'%')
  17.         </if>
  18.     </where>
  19. </select>
  1. <!--综合管理-科研管理列表-->
  2. <select id="scientificResearchManagement" resultType="com.pb.projects.vo.integratedmanagement.PbProResearchVo">
  3.     SELECT
  4.         pro.type,
  5.         pro.`code`,
  6.         pro.`name`,
  7.         pro.alias,
  8.         IFNULL( COUNT( DISTINCT research.id ), 0 ) AS 'total',
  9.         IFNULL( SUM( research.expenditure ), 0 ) AS 'money'
  10.     FROM
  11.         pb_pro pro
  12.         LEFT JOIN pb_pro_sub sub ON sub.parent_code = pro.`code`
  13.         LEFT JOIN pb_pro_sub_research research ON research.pro_sub_id = sub.id
  14.     GROUP BY
  15.         pro.id
  16. </select>
  1. <!--综合管理-科研管理资金累计-->
  2. <select id="selectAccumulation" resultType="java.util.Map">
  3.     SELECT
  4.         IFNULL( COUNT( DISTINCT research.id ), 0 ) AS counts,
  5.         IFNULL( SUM( research.expenditure ), 0 ) AS accumulation
  6.     FROM
  7.         `pb_pro_sub_research` research
  8.         LEFT JOIN pb_pro_sub sub ON sub.id = research.pro_sub_id
  9.         LEFT JOIN pb_pro pro ON pro.`code` = sub.parent_code
  10. </select>
  1. <!--根据ids查询vo列表-换流站变电站-工程列表的容量和投资-->
  2. <select id="selectCapacityAndInvestment" resultType="com.pb.projects.vo.substation.PbProSubListVo">
  3.     SELECT
  4.         pro.type,
  5.         sub.id,
  6.         sub.`name`,
  7.         sub.alias,
  8.         pro.capacity,
  9.         IFNULL( SUM( research.expenditure ), 0 ) AS expenditure
  10.     FROM
  11.         pb_pro_sub_research research
  12.         LEFT JOIN pb_pro_sub sub ON sub.id = research.pro_sub_id
  13.         LEFT JOIN pb_pro pro ON pro.`code` = sub.parent_code
  14.     WHERE
  15.         research.pro_sub_id = #{proSubId}
  16.     GROUP BY
  17.         pro.id,
  18.         research.pro_sub_id
  19. </select>
  1. <!--线路工程-设计进度-->
  2. <select id="statisticsOfDesign" resultMap="PbProDesignMap">
  3.     SELECT
  4.     pro.id AS proId,
  5.     sub.id AS proSubId,
  6.     pro.type AS proType,
  7.     pro.`name` AS proName,
  8.     pro.alias AS proAlias,
  9.     sub.`name` AS proSubName,
  10.     sub.alias AS proSubAlias,
  11.     GROUP_CONCAT( pb_pro_sub_stage.`name` ) AS stage,
  12.     GROUP_CONCAT( pb_pro_sub_stage.`status` ) AS statuses,
  13.     pb_pro_sub_pos_info.*
  14.     FROM
  15.     pb_pro_sub_stage
  16.     LEFT JOIN pb_pro_sub sub ON sub.id = pb_pro_sub_stage.pro_sub_id
  17.     LEFT JOIN pb_pro pro ON pro.`code` = sub.parent_code
  18.     LEFT JOIN pb_pro_sub_pos_info ON pb_pro_sub_pos_info.pro_sub_id = pb_pro_sub_stage.pro_sub_id
  19.     WHERE
  20.     sub.state = #{subState} AND sub.type = #{subType}
  21.     <if test='proType != "q"'>
  22.         AND pro.type = #{proType}
  23.     </if>
  24.     <if test="proSubId != null and proSubId != ''">
  25.         AND sub.id = #{proSubId}
  26.     </if>
  27.     AND pb_pro_sub_stage.`name` IN ('可研','初设','施工图')
  28.     GROUP BY
  29.     pro.id,sub.id,pb_pro_sub_pos_info.id
  30. </select>
  1. <!--线路工程-建设进度-->
  2. <select id="statisticsOfBuild" resultMap="PbProBuildMap">
  3.     SELECT
  4.     pro.id AS proId,
  5.     sub.id AS proSubId,
  6.     pro.type AS proType,
  7.     pro.`name` AS proName,
  8.     pro.alias AS proAlias,
  9.     sub.`name` AS proSubName,
  10.     sub.alias AS proSubAlias,
  11.     GROUP_CONCAT( pb_pro_sub_stage.`name` ) AS stage,
  12.     GROUP_CONCAT( pb_stage_rate.rate ) AS statuses,
  13.     pb_pro_sub_pos_info.*
  14.     FROM
  15.     pb_stage_rate
  16.     LEFT JOIN pb_pro_sub_stage ON pb_pro_sub_stage.id = pb_stage_rate.stage_id
  17.     LEFT JOIN pb_pro_sub sub ON sub.id = pb_pro_sub_stage.pro_sub_id
  18.     LEFT JOIN pb_pro pro ON pro.`code` = sub.parent_code
  19.     LEFT JOIN pb_pro_sub_pos_info ON pb_pro_sub_pos_info.pro_sub_id = pb_pro_sub_stage.pro_sub_id
  20.     WHERE
  21.     sub.state = #{subState} AND sub.type = #{subType}
  22.     <if test='proType != "q"'>
  23.         AND pro.type = #{proType}
  24.     </if>
  25.     <if test="proSubId != null and proSubId != ''">
  26.         AND sub.id = #{proSubId}
  27.     </if>
  28.     AND pb_pro_sub_stage.`name` IN ('具备进场条件','已进场','开挖','浇筑','组塔','架线')
  29.     GROUP BY
  30.     pro.id,sub.id,pb_pro_sub_pos_info.id
  31. </select>
  1. <select id="getPbLineDrawingProcessList" resultMap="PbLineDrawingProcessListMap">
  2.     SELECT
  3.         COUNT(draw.id) volumeCount,
  4.         draw.volume volume
  5.     FROM
  6.         pb_line_drawing_process draw
  7.     <where>
  8.         <if test="drawIds != null and drawIds != ''">
  9.             draw.id IN
  10.             <foreach item="item" index="index" collection="drawIds.split(',')" open="(" separator="," close=")">#{item}</foreach>
  11.         </if>
  12.     </where>
  13.     GROUP BY
  14.         draw.volume
  1. <select id="selectOneById" resultMap="PbPartyDepartmentMap">
  2.         SELECT
  3.             party.*
  4.         FROM
  5.             pb_party_department party
  6.         WHERE
  7.             party.del_flag = 0 AND party.id =#{id}
  8. </select>
  1. <select id="selectSubLineResearchAchievement" resultMap="researchAchievementMap">
  2.         SELECT
  3.             GROUP_CONCAT( ach.id ) ids,
  4.             ach.type type
  5.         FROM
  6.             `pb_pro_sub_research_achievement` ach
  7.         WHERE
  8.      ach.research_id=#{researchId}
  9.         GROUP BY
  10.             ach.type
  11. </select>
  1. <select id="selectResearchAchievementSec" resultMap="researchAchievementSecMap">
  2.         SELECT
  3.             ach.id researchAchievementId,ach.name researchAchievementName,ach.file_id
  4.         FROM
  5.             `pb_pro_sub_research_achievement` ach
  6.         WHERE
  7.             ach.id IN
  8.         <foreach item="item" index="index" collection="ids.split(',')" open="(" separator="," close=")">#{item}</foreach>
  9. </select>
  1. DATE_FORMAT(production_time, '%Y-%m-%d %H:%i:%S') pdtime,
  2. DATE_FORMAT(planning_time, '%Y-%m-%d %H:%i:%S') plantime,
  3. DATE_FORMAT(plan_cmpl_time, '%Y-%m-%d %H:%i:%S') plancltime,
  4. DATE_FORMAT(cst_cmpl_time, '%Y-%m-%d %H:%i:%S') cstctime
  6. AND DATE_FORMAT( t.ACTUALSTARTTIME, '%Y-%m-%d' ) = #{riskDate}
  1. <!--党员统计弹窗-党内人员分页-->
  2. <select id="selectPartyMemberPageByIds" resultType="com.pb.party.vo.PartyMember.PartyMemberVo">
  3.     SELECT
  4.         me.*
  5.     FROM
  6.         party_member me
  7.     WHERE
  8.         me.del_flag = 0
  9.     <if test="ids != null and ids.length != 0 ">
  10.         AND me.id IN
  11.         <foreach item="id" collection="ids" separator="," open="(" close=")" index="">
  12.             #{id}
  13.         </foreach>
  14.     </if>
  15. </select>
  1. SELECT
  2.     me.id,
  3.     me.`name`,
  4.     me.party_duty
  5. FROM
  6.     `party_member` me
  7. WHERE
  8.     me.party_department_id = #{partyId}
  9.     AND me.party_duty NOT IN ${partyDuty}
  10. ORDER BY
  11.  field( me.party_duty, '支部副书记', '组织委员', '宣传委员', '纪检委员', '群(青)工委员', '党小组长' )
  1. <!--首页-在建工程党组织列表/中间地图坐标点-->
  2. <select id="selectList" resultType="com.pb.party.entity.PartyDepartment">
  3.     SELECT
  4.         party.*
  5.     FROM
  6.         party_department party
  7.         LEFT JOIN core_project pro ON pro.id = party.project_id
  8.     WHERE
  9.         party.del_flag = 0
  10.         AND pro.`code` LIKE '${projectUnicode}%'
  11. </select>
  1. <select id="getDeviceList" resultType="com.xqny.model.DeviceVo">
  2.     SELECT
  3.         sbdybm id,
  4.         sbmc deviceName
  5.     FROM
  6.         `device_list_collect`
  7.     WHERE
  8.         sbmc LIKE '%电抗器'
  9.       AND sblxmc LIKE '%电抗器'
  10. </select>
  1. <select id="getTemperatureAndHumidity" resultType="com.xqny.model.TemperatureAndHumidity">
  2.   select
  3.          id id,
  4.          temperature  temperature,
  5.          humidity humidity
  6.   from local_weather_data where id =  (select max(id) from local_weather_data)
  7. </select>

insert

  1. <!-- mybatis获取刚刚插入到数据库的数据的id -->
  2. <insert id="insertOne" parameterType="com.xqny.logtest.entity.User" useGeneratedKeys="true" keyProperty="uid">
  4.     INSERT INTO user ( user_name ) VALUES ( "rrrrrr" )
  5. </insert>
  1. <insert id="addBatchCpsEquipment" parameterType="java.util.List">
  2.     INSERT INTO cps_equipment
  3.     (project_id,node_num,lifetime,min_load,max_load,installation_cost,electrical_efficiency,
  4.     heat_efficiency,cop,max_contribute,compress,operating_cost,capacity,maintenance_cost_factor,tyo,dt)
  5.     VALUES
  6.     <foreach collection ="cpsEquipmentList" item="cpsEquipment" separator =",">
  7.         (#{cpsEquipment.projectId}, #{cpsEquipment.nodeNum}, #{cpsEquipment.lifetime},
  8.         #{cpsEquipment.minLoad}, #{cpsEquipment.maxLoad}, #{cpsEquipment.installationCost},
  9.         #{cpsEquipment.electricalEfficiency}, #{cpsEquipment.heatEfficiency},
  10.         #{cpsEquipment.cop},#{cpsEquipment.maxContribute},#{cpsEquipment.compress},
  11.         #{cpsEquipment.operatingCost},#{cpsEquipment.capacity},
  12.         #{cpsEquipment.maintenanceCostFactor},#{cpsEquipment.tyo},#{cpsEquipment.dt})
  13.     </foreach >
  15. </insert>

update

  1. <!-- 批量更新 -- >
  2. <update id="batchUpdate">
  3.     <foreach collection="studentArrayList" item="item" separator=";"  index="index">
  4.         update student
  5.     <trim prefix="set " suffixOverrides=",">
  6.         <if test="item.getName() != null">
  7.             name = #{item.name},
  8.         </if>
  9.         <if test="item.getAge() != null">
  10.             age = #{item.age},
  11.         </if>
  12.       </trim>
  13.         where id = #{item.id}
  15.     </foreach>
  17. </update>
  1. <update id="batchUpdatePowerSimulation">
  2.     <foreach collection="powerSimulationResults" item="item" separator=";" index="index">
  3.         UPDATE cps_power_simulation_results SET 
  4.         power_purchase_price = #{item.powerPurchasePrice},
  5.         electricity_selling_price = #{item.electricitySellingPrice},
  6.         gas_turbine_output = #{item.gasTurbineOutput},
  7.         electric_energy_storage_discharge = #{item.electricEnergyStorageDischarge},
  8.         electric_vehicle_discharge = #{item.electricVehicleDischarge},
  9.         electric_load = #{item.electricLoad},
  10.         electric_vehicle_charging = #{item.electricVehicleCharging},
  11.         heat_pump_consumption = #{item.heatPumpConsumption},
  12.         consumption_of_electric_refrigerator = #{item.consumptionOfElectricRefrigerator},
  13.         energy_storage_and_charging = #{item.energyStorageAndCharging},
  14.         selling_electricity = #{item.sellingElectricity}
  15.         WHERE
  16.         project_id = #{item.projectId}
  17.         AND type=#{item.type}
  18.     </foreach>
  19. </update>

whereSQL

  1. <sql id="whereSql">
  2.     <where>
  3.         <if test="projectName != null and projectName != ''">
  4.             AND pm.project_name = #{projectName}
  5.         </if>
  6.         <if test="type != null and type != ''">
  7.             AND pm.type = #{type}
  8.         </if>
  9.         <if test="projectId =='qwer'">
  10.             AND (pm.project_id is not null OR pm.project_id !='')
  11.         </if>
  12.         <if test="projectId == null or projectId == ''">
  13.             AND (pm.project_id is null OR pm.project_id='')
  14.         </if>
  15. <if test="date != null and date != ''">
  16.             AND DATE_FORMAT(life.active_time,'%Y') = #{date}
  17. </if>
  18.     </where>
  19. </sql>
  1. <sql id="whereSql">
  2.     <where>
  3.         <if test="proType != null and proType != ''">
  4.             AND pro.type = #{proType}
  5.         </if>
  6.         <if test="subType != null">
  7.             AND sub.type = #{subType}
  8.         </if>
  9.         <if test="subStage != null">
  10.             AND sub.state = #{subStage}
  11.         </if>
  12.         <if test="proSubId != null and proSubId != ''">da
  13.             AND sub.id = #{proSubId}
  14.         </if>
  15.     </where>
  16. </sql>

if分支

  1. <if test="obj.searchType!=null and obj.searchType !=''">
  2. <choose>
  3.     <when test="obj.searchType ='pwNum'">
  4.         AND `pw_num` like CONCAT('%',#{obj.searchValue},'%')
  5.     </when>
  6.     <when test="obj.searchType ='pwDesc'">
  7.         AND `pw_desc` like CONCAT('%',#{obj.searchValue},'%')
  8.     </when>
  9.     <otherwise>
  10.         AND `pw_name` like CONCAT('%',#{obj.searchValue},'%')
  11.     </otherwise>
  12. </choose>
  13. </if>