mybatis分页oracle查询

  1 <?xml version="1.0" encoding="UTF-8"?>
  2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
  3 <mapper namespace="com.cqfczc.server.dao.IRepairDao">
  4     <resultMap type="com.cqfczc.server.entity.RepairInfo" id="BaseResultMap">
  5         <id column="equipmentsId" property="equipmentsId" jdbcType="INTEGER"/>
  6         <result column="projectId" property="projectId" jdbcType="INTEGER"/>
  7         <result column="projectName" property="projectName" jdbcType="VARCHAR"/>
  8         <result column="equipmentName" property="equipmentName" jdbcType="VARCHAR"/>
  9         <result column="equipmentNo" property="equipmentNo" jdbcType="VARCHAR"/>
 10         <result column="equipmentModel" property="equipmentModel" jdbcType="VARCHAR"/>
 11         <result column="repairBudget" property="repairBudget" jdbcType="VARCHAR"/>
 12         <result column="repairMoney" property="repairMoney" jdbcType="VARCHAR"/>
 13         <result column="repairBuilding" property="repairBuilding" jdbcType="VARCHAR"/>
 14         <result column="repairRoom" property="repairRoom" jdbcType="VARCHAR"/>
 15         <result column="faultDescription" property="faultDescription" jdbcType="VARCHAR"/>
 16         <result column="riskInformed" property="riskInformed" jdbcType="VARCHAR"/>
 17         <result column="remarks" property="remarks" jdbcType="VARCHAR"/>
 18         <result column="isValidity" property="isValidity" jdbcType="INTEGER" />
 19         <result column="createUserId" property="createUserId" jdbcType="INTEGER" />
 20         <result column="createDate" property="createDate"/>
 21         <result column="repairUnit" property="repairUnit" jdbcType="VARCHAR"/>
 22     </resultMap>
 23     <resultMap type="com.cqfczc.server.entity.EquipmentInfo" id="BaseResultMap3">
 24         <id column="equipmentId" property="equipmentId" jdbcType="INTEGER"/>
 25         <result column="equipmentType" property="equipmentType" jdbcType="VARCHAR"/>
 26         <result column="equipmentName" property="equipmentName" jdbcType="VARCHAR"/>
 27         <result column="equipmentRoom" property="equipmentRoom" jdbcType="VARCHAR"/>
 28         <result column="equipmentBuilding" property="equipmentBuilding" jdbcType="VARCHAR"/>
 29         <result column="equipmentFloor" property="equipmentFloor" jdbcType="VARCHAR"/>
 30         <result column="equipmentUnit" property="equipmentUnit" jdbcType="VARCHAR"/>
 31         <result column="equipmentModel" property="equipmentModel" jdbcType="VARCHAR"/>
 32         <result column="makeUnit" property="makeUnit" jdbcType="VARCHAR"/>
 33         <result column="useDate" property="useDate" jdbcType="VARCHAR"/>
 34         <result column="maintainDate" property="maintainDate" jdbcType="VARCHAR"/>
 35         <result column="equipmentNo" property="equipmentNo" jdbcType="VARCHAR"/>
 36         <result column="remarks" property="remarks" jdbcType="VARCHAR"/>
 37         <result column="isValidity" property="isValidity" jdbcType="INTEGER" />
 38         <result column="createUserId" property="createUserId" jdbcType="INTEGER" />
 39         <result column="createDate" property="createDate"/>
 40     </resultMap>
 41     <sql id="Base_Column_List">
 42         equipmentsId,projectId,projectName,equipmentName,equipmentNo,equipmentModel,repairBudget,repairMoney,repairBuilding,repairRoom,faultDescription,riskInformed,remarks,repairUnit
 43     </sql>
 44             <sql id="Base_Column_List1">
 45          projectId,projectName,equipmentName,equipmentNo, equipmentModel, repairBudget, repairMoney, repairBuilding, repairRoom,faultDescription, 
 46         riskInformed,remarks,isValidity,createUserId,createDate,repairUnit
 47     </sql>
 48     
 49     <sql id="Base_Column_Repair_List">
 50         a.equipmentsId,a.projectId,a.projectName,a.equipmentName,a.equipmentNo,a.equipmentModel,a.repairBudget,a.repairMoney,(select buildingName from cq_building b where a.repairBuilding=b.buildingCode) as repairBuilding,a.repairRoom,
 51         a.faultDescription,a.riskInformed,a.remarks,a.repairUnit
 52     </sql>
 53         <sql id="Base_Column_Repair_List1">
 54          a.projectId,a.projectName,a.equipmentName, a.equipmentNo, a.equipmentModel, a.repairBudget, a.repairMoney, 
 55         a.repairBuilding, a.repairRoom,a.faultDescription,a.riskInformed,a.remarks,a.isValidity,a.createUserId,a.createDate,a.repairUnit
 56     </sql>
 57     
 58     <!-- 添加维修信息 -->
 59     <insert id="addRepairInfo" parameterType="com.cqfczc.server.entity.RepairInfo" >
 60         insert into cq_repair(projectId, projectName, equipmentName, equipmentNo,equipmentModel,repairBudget, repairMoney, repairBuilding,repairRoom,faultDescription,riskInformed,remarks,isValidity,createUserId,createDate,repairUnit)
 61         values (#{projectId,jdbcType=INTEGER}, #{projectName,jdbcType=VARCHAR},#{equipmentName,jdbcType=VARCHAR},#{equipmentNo,jdbcType=VARCHAR},#{equipmentModel,jdbcType=VARCHAR},
 62         #{repairBudget,jdbcType=VARCHAR},#{repairMoney,jdbcType=VARCHAR},#{repairBuilding,jdbcType=VARCHAR},#{repairRoom,jdbcType=VARCHAR},
 63         #{faultDescription,jdbcType=VARCHAR},#{riskInformed,jdbcType=VARCHAR},
 64       #{remarks,jdbcType=VARCHAR },1,#{createUserId,jdbcType=INTEGER},#{createDate},#{repairUnit})
 65     </insert>
 66     <!-- 添加维修信息 -->
 67     <update id="updateRepairInfo" parameterType="com.cqfczc.server.entity.RepairInfo" >
 68     update cq_repair set projectId=#{projectId,jdbcType=INTEGER},projectName=#{projectName,jdbcType=VARCHAR},equipmentName=#{equipmentName,jdbcType=VARCHAR},
 69     equipmentNo=#{equipmentNo,jdbcType=VARCHAR},equipmentModel=#{equipmentModel,jdbcType=VARCHAR},repairBudget=#{repairBudget,jdbcType=VARCHAR},
 70     repairMoney=#{repairMoney,jdbcType=VARCHAR},repairBuilding=#{repairBuilding,jdbcType=VARCHAR},repairRoom=#{repairRoom,jdbcType=VARCHAR},
 71     faultDescription= #{faultDescription,jdbcType=VARCHAR},riskInformed=#{riskInformed,jdbcType=VARCHAR},remarks=#{remarks,jdbcType=VARCHAR },
 72     isValidity=1,createUserId=#{createUserId,jdbcType=INTEGER},createDate=#{createDate},repairUnit=#{repairUnit} where equipmentsId=#{equipmentsId,jdbcType=INTEGER}
 73     </update>
 74     
 75     <!-- 查询维修信息总数 -->
 76     <select id="selectTotalCount" resultType="int" parameterType="com.cqfczc.server.entity.RepairInfo">
 77         select count(*) from cq_repair
 78         <if test="(null != equipmentName) &amp;&amp; (!('').equals(equipmentName))">
 79             where equipmentName like '%' || #{equipmentName,jdbcType=VARCHAR} || '%'
 80         </if>
 81     </select>
 82     <select id="selectTotalCountByUserId" resultType="int" parameterType="com.cqfczc.server.entity.RepairInfo">
 83         select count(*) from cq_repair where 1=1 and createUserId=#{createUserId,jdbcType=INTEGER}
 84         <if test="(null != equipmentName) &amp;&amp; (!('').equals(equipmentName))">
 85             and equipmentName like '%' || #{equipmentName,jdbcType=VARCHAR} || '%'
 86         </if>
 87     </select>
 88     <!-- 查询维修信息总数-->
 89     
 90     <!-- 查询所有 -->
 91     <select id="selectRepairList" resultMap="BaseResultMap" parameterType="map">
 92     select <include refid="Base_Column_Repair_List" /> from (
 93         select ROWNUM AS RN,<include refid="Base_Column_List" /> from (select * from cq_repair order by createDate Desc) where 1=1  
 94         <if test="(null != equipmentName) &amp;&amp; (!('').equals(equipmentName))">
 95             and equipmentName like '%' || #{equipmentName,jdbcType=VARCHAR} || '%'
 96         </if>
 97         <if test="(null != equipmentsId) &amp;&amp; (!('').equals(equipmentsId))">
 98             and equipmentsId=#{equipmentsId,jdbcType=INTEGER}
 99         </if>
100         ) a
101         where
102         RN &gt;(#{pageIndex,jdbcType=INTEGER}-1)*${@com.cqfczc.util.Constant@PAGE_SERVER_SIZE} and RN &lt;= (#{pageIndex,jdbcType=INTEGER})*${@com.cqfczc.util.Constant@PAGE_SERVER_SIZE}
103     </select>
104         <select id="selectRepairListByUserId" resultMap="BaseResultMap" parameterType="map">
105     select <include refid="Base_Column_Repair_List" /> from (
106         select ROWNUM AS RN,<include refid="Base_Column_List" /> from (select * from cq_repair order by createDate Desc) where 1=1 and createUserId=#{createUserId,jdbcType=INTEGER}
107         <if test="(null != equipmentName) &amp;&amp; (!('').equals(equipmentName))">
108             and equipmentName like '%' || #{equipmentName,jdbcType=VARCHAR} || '%'
109         </if>
110         <if test="(null != equipmentsId) &amp;&amp; (!('').equals(equipmentsId))">
111             and equipmentsId=#{equipmentsId,jdbcType=INTEGER}
112         </if>
113         ) a
114         where
115         RN &gt;(#{pageIndex,jdbcType=INTEGER}-1)*${@com.cqfczc.util.Constant@PAGE_SERVER_SIZE} and RN &lt;= (#{pageIndex,jdbcType=INTEGER})*${@com.cqfczc.util.Constant@PAGE_SERVER_SIZE}
116     </select>
117     <!-- 查询所有 -->
118          <!--  批量 list  -->
119     <insert id="addRepairBatch" parameterType="java.util.List">
120       insert into cq_repair(<include refid="Base_Column_List1" />)
121       select <include refid="Base_Column_Repair_List1" />
122       from(
123      <foreach collection="list" item="item" index="index" separator="union all">
124        select #{item.projectId,jdbcType=INTEGER} as projectId, #{item.projectName,jdbcType=VARCHAR} as projectName,
125         #{item.equipmentName,jdbcType=VARCHAR} as equipmentName,#{item.equipmentNo,jdbcType=VARCHAR} as equipmentNo,
126         #{item.equipmentModel,jdbcType=VARCHAR} as equipmentModel,#{item.repairBudget,jdbcType=VARCHAR} as repairBudget,
127         #{item.repairMoney,jdbcType=VARCHAR} as repairMoney,#{item.repairBuilding,jdbcType=VARCHAR} as repairBuilding,#{item.repairRoom,jdbcType=VARCHAR} as repairRoom,
128         #{item.faultDescription,jdbcType=VARCHAR} as faultDescription,#{item.riskInformed,jdbcType=VARCHAR} as riskInformed,#{item.remarks,jdbcType=VARCHAR} as remarks,
129         1 as isValidity,#{item.createUserId,jdbcType=INTEGER } as createUserId,#{item.createDate} as createDate
130         FROM DUAL
131       </foreach>
132          ) A
133     </insert>
134     <!--查询房间内设备维修次数总数 -->
135         <select id="selectRepairTotalCount" resultType="int" parameterType="map">
136         select count(*) from cq_repair where repairBuilding=#{buildingId,jdbcType=VARCHAR} and repairRoom=#{equipmentRoom,jdbcType=VARCHAR} and equipmentNo=#{equipmentNo,jdbcType=VARCHAR}    
137         <if test="(null != equipmentName) &amp;&amp; (!('').equals(equipmentName))">
138             and equipmentName like '%' || #{equipmentName,jdbcType=VARCHAR} || '%'
139         </if>
140     </select>
141         <select id="selectRepairMessageList" resultMap="BaseResultMap" parameterType="map">
142     select <include refid="Base_Column_Repair_List" /> from (
143         select ROWNUM AS RN,<include refid="Base_Column_List" /> from (select * from cq_repair order by createDate Desc) where repairBuilding=#{buildingId,jdbcType=VARCHAR} and repairRoom=#{equipmentRoom,jdbcType=VARCHAR} and equipmentNo=#{equipmentNo,jdbcType=VARCHAR}
144         <if test="(null != equipmentName) &amp;&amp; (!('').equals(equipmentName))">
145             and equipmentName like '%' || #{equipmentName,jdbcType=VARCHAR} || '%'
146         </if>
147         ) a
148         where
149         RN &gt;(#{pageIndex,jdbcType=INTEGER}-1)*${@com.cqfczc.util.Constant@PAGE_SERVER_SIZE} and RN &lt;= (#{pageIndex,jdbcType=INTEGER})*${@com.cqfczc.util.Constant@PAGE_SERVER_SIZE}
150     </select>
151 <select id="selectProjectName" resultType="String" parameterType="int">
152 select projectName from cq_project where projectId=#{projectId,jdbcType=INTEGER}
153 </select>
154 </mapper>
posted on 2016-08-17 13:38 Sharpest 阅读(2100) 评论(0) 编辑收藏举报