ipms的sql语句

projetMapper.xml

<mapper namespace="com.ipms.dao.ProjetMapper"><resultMap id="BaseResultMap" type="com.ipms.model.Projet"><id column="IDS" jdbcType="DECIMAL" property="ids"/><result column="PNAME" jdbcType="VARCHAR" property="pname"/><result column="PTYPE" jdbcType="DECIMAL" property="ptype"/><result column="DIRECTION" jdbcType="DECIMAL" property="direction"/><result column="INTRO" jdbcType="VARCHAR" property="intro"/><result column="UIDS" jdbcType="DECIMAL" property="uids"/><result column="RATE" jdbcType="VARCHAR" property="rate"/><result column="APPRAISE" jdbcType="VARCHAR" property="appraise"/><result column="CREATETIME" jdbcType="DATE" property="createtime"/><result column="ENDTIME" jdbcType="DATE" property="endtime"/><result column="MARK" jdbcType="CHAR" property="mark"/></resultMap><sql id="Base_Column_List">

		IDS, PNAME, PTYPE, DIRECTION, INTRO, UIDS, RATE,
		APPRAISE,CREATETIME,ENDTIME,MARK


  </sql><!--手动添加 start  --><!--方法selectAll 魏萍萍  --><select id="selectAll" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List"/>
    from IPMS.PROJET
  </select><!--手动添加 end  --><select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List"/>
    from IPMS.PROJET
    where IDS = #{ids,jdbcType=DECIMAL}
  </select><!-- 删除项目,左 --><delete id="deletePro" parameterType="java.lang.Integer">
    delete from IPMS.PROJET
    where IDS = #{ids,jdbcType=DECIMAL}
  </delete><!-- 插入项目 ,左--><insert id="insertpro" parameterType="com.ipms.model.Projet">
    insert into IPMS.PROJET (IDS, PNAME, PTYPE, 
      DIRECTION, INTRO,UIDS, 
      RATE, APPRAISE,CREATETIME,MARK)
    values (sq_projectmsg.nextval, #{pname,jdbcType=VARCHAR}, #{ptype,jdbcType=DECIMAL}, 
      #{direction,jdbcType=DECIMAL}, #{intro,jdbcType=VARCHAR}, #{uids,jdbcType=DECIMAL}, 
      #{rate,jdbcType=VARCHAR}, #{appraise,jdbcType=VARCHAR},sysdate,0)
  </insert><!-- 搜索项目,左 --><select id="searchPro" resultType="Map" parameterType="Map">
		 
        	
        	SELECT  p.ids ,p.pname,
		    	(SELECT d.dataname FROM Datadictory d WHERE p.ptype = d.ids)ptype 
     				 ,(SELECT dd.dataname FROM Datadictory dd WHERE p.direction = dd.ids) direction
         	,p.intro,(SELECT u.realname FROM Userinfo u WHERE u.ids = p.uids) uids,p.rate,
         			p.appraise, (CASE p.mark WHEN 
						'1' THEN '已通过' WHEN '0' THEN '未通过' END) mark FROM projet p 
         	    <where><if test="p2 != null">
         	            
         	            p.pname like '%'||#{p2}||'%'
         	         </if><if test="p1 != null">
         	             AND  p.ids IN (SELECT pj.pid FROM projetmember pj WHERE pj.uids = #{p1})
         	          
         	          </if><if test="p3 != null and p4 == 20">
         	            AND  p.uids IN
								(SELECT u.ids FROM userinfo u WHERE u.ids = #{p3} AND u.position = 5)
         	          </if></where></select><!-- 通过ids查询项目,左 --><select id="selectByids" parameterType="java.lang.Integer" resultType="Map">
  	     SELECT  p.ids ,p.pname,
		    	(SELECT d.dataname FROM Datadictory d WHERE p.ptype = d.ids)ptype 
     				 ,(SELECT dd.dataname FROM Datadictory dd WHERE p.direction = dd.ids) direction
         	,p.intro,(SELECT u.realname FROM Userinfo u WHERE u.ids = p.uids) uids,p.rate,
         			p.appraise FROM projet p 
  	 where p.ids = #{ids,jdbcType=DECIMAL}
  	</select><!-- end --><insert id="insertSelective" parameterType="com.ipms.model.Projet">
    insert into IPMS.PROJET
    <trim prefix="(" suffix=")" suffixOverrides=","><if test="ids != null">
        IDS,
      </if><if test="pname != null">
        PNAME,
      </if><if test="ptype != null">
        PTYPE,
      </if><if test="direction != null">
        DIRECTION,
      </if><if test="intro != null">
        INTRO,
      </if><if test="uids != null">
        UIDS,
      </if><if test="rate != null">
        RATE,
      </if><if test="appraise != null">
        APPRAISE,
      </if><if test="creatime != null">
        CREATETIME,
      </if><if test="endtime != null">
        ENDTIME,
      </if><if test="mark != null">
        MARK,
      </if></trim><trim prefix="values (" suffix=")" suffixOverrides=","><if test="ids != null">
        #{ids,jdbcType=DECIMAL},
      </if><if test="pname != null">
        #{pname,jdbcType=VARCHAR},
      </if><if test="ptype != null">
        #{ptype,jdbcType=DECIMAL},
      </if><if test="direction != null">
        #{direction,jdbcType=DECIMAL},
      </if><if test="intro != null">
        #{intro,jdbcType=VARCHAR},
      </if><if test="uids != null">
        #{uids,jdbcType=DECIMAL},
      </if><if test="rate != null">
        #{rate,jdbcType=VARCHAR},
      </if><if test="appraise != null">
        #{appraise,jdbcType=VARCHAR},
      </if><if test="createtime != null">
        #{createtime,jdbcType=DATE},
      </if><if test="endtime != null">
        #{endtime,jdbcType=DATE},
      </if><if test="mark != null">
        #{mark,jdbcType=CHAR},
      </if></trim></insert><!-- 编辑项目 ,左 --><update id="updatePro" parameterType="Map">
    update IPMS.PROJET
    <set><if test="p1 != null">
        PNAME = #{p1},
      </if><if test="p2 != null">
        PTYPE = (SELECT d.ids FROM Datadictory d WHERE d.dataname = #{p2}),
      </if><if test="p3 != null">
        DIRECTION = (SELECT d.ids FROM Datadictory d WHERE d.dataname = #{p3}),
      </if><if test="p4 != null">
        INTRO = #{p4},
      </if><if test="p5 != null">
        UIDS = (SELECT u.ids FROM Userinfo u WHERE u.realname = #{p5}),
      </if><if test="p6 != null">
        RATE = #{p6},
      </if><if test="p7 != null">
        APPRAISE = #{p7},
      </if></set>
    where IDS = #{p8}
  </update><!-- 审批项目,左 --><update id="updateMark" parameterType="java.lang.Integer">
  		   UPDATE IPMS.PROJET
  		    SET MARK = 1
  		     WHERE IDS = #{ids,jdbcType=DECIMAL} AND MARK = 0
  		</update><!-- end --><update id="updateByPrimaryKey" parameterType="com.ipms.model.Projet">
    update IPMS.PROJET
    set PNAME = #{pname,jdbcType=VARCHAR},
      PTYPE = #{ptype,jdbcType=DECIMAL},
      DIRECTION = #{direction,jdbcType=DECIMAL},
      INTRO = #{intro,jdbcType=VARCHAR},
      UIDS = #{uids,jdbcType=DECIMAL},
      RATE = #{rate,jdbcType=VARCHAR},
      APPRAISE = #{appraise,jdbcType=VARCHAR},
       CREATETIME = #{createtime,jdbcType=DATE},
        ENDTIME = #{endtime,jdbcType=DATE},
    where IDS = #{ids,jdbcType=DECIMAL}
  </update></mapper>

 DatabaseMapper.xml

<mapper namespace="com.ipms.dao.DatabaseMapper"><resultMap id="BaseResultMap" type="com.ipms.model.Database"><id column="IDS" jdbcType="DECIMAL" property="ids"/><result column="UIDS" jdbcType="DECIMAL" property="uids"/><result column="FILENAME" jdbcType="VARCHAR" property="filename"/><result column="FILEPATHS" jdbcType="VARCHAR" property="filepaths"/><result column="FILELENGTH" jdbcType="VARCHAR" property="filelength"/><result column="CREATETIME" jdbcType="TIMESTAMP" property="createtime"/><result column="FILETYPE" jdbcType="VARCHAR" property="filetype"/><result column="CLICKCOUNT" jdbcType="DECIMAL" property="clickcount"/><result column="MARK" jdbcType="DECIMAL" property="mark"/><result column="REACOUNT" jdbcType="DECIMAL" property="reacount"/><result column="ISHOME" jdbcType="DECIMAL" property="ishome"/><result column="BIGIMG" jdbcType="VARCHAR" property="bigimg"/><result column="WEN" jdbcType="VARCHAR" property="wen"/></resultMap><sql id="Base_Column_List">
		IDS, UIDS, FILENAME, FILEPATHS, FILELENGTH, CREATETIME, FILETYPE,
		CLICKCOUNT, MARK ,REACOUNT,ISHOME ,BIGIMG,
		WEN
	</sql><!--手动添加 start --><!--方法selectAll 魏萍萍 --><select id="selectAll" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List"/>
		from IPMS.DATABASE
	</select><!--手动添加 end --><select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
		select
		<include refid="Base_Column_List"/>
		from IPMS.DATABASE
		where IDS = #{ids,jdbcType=DECIMAL}
	</select><delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
		delete from IPMS.DATABASE
		where IDS = #{ids,jdbcType=DECIMAL}
	</delete><!-- 用来进行上传文件的添加,左竞一 --><insert id="addDatabase" parameterType="Map">
		
		INSERT INTO DATABASE (ids,uids,Filename,CREATETIME,Filetype,CLICKCOUNT,mark,
			REACOUNT,Wen) 
				VALUES (sq_database.nextval,(SELECT u.ids FROM Usertable u 
				WHERE u.username = #{p1}) ,
			#{p2},SYSDATE,#{p3},0,1,0,#{p4})
	</insert><!-- end --><!-- 分页用,计算每个技术方向所需要的,左竞一 --><select id="selectCount" resultType="Integer">
		SELECT COUNT(d.ids) FROM DATABASE d
		 WHERE d.filetype LIKE '%' || #{param1} || '%'
	</select><!-- 查找资料时用到的资料内容,左 --><select id="selectData" resultMap="BaseResultMap">
	          
			SELECT 
			 <include refid="Base_Column_List"/>
			
			 FROM 
				(SELECT d.*, ROWNUM rnum FROM database d WHERE ROWNUM <= #{param2}
			AND	d.filetype LIKE '%'||#{param1}||'%') r
					  WHERE r.rnum > #{param3} 
	      
	     </select><!-- 通过ids来查询文件简介 ,左--><select id="selectWen" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List"/>
    from IPMS.DATABASE
    where IDS = #{ids,jdbcType=DECIMAL}
  </select><!-- 通过ids来更新阅读量,左 --><update id="updateData" parameterType="java.lang.Integer">
  		   UPDATE DATABASE d SET d.clickcount = d.clickcount + 1,
 						d.reacount = d.reacount + 1 WHERE IDS = #{ids,jdbcType=DECIMAL}
  </update><!-- end --></mapper>