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>