常见的mybatis的xml语法

  1. for循环查找
    <if test="infoTemplateAll.templateTypes != null and infoTemplateAll.templateTypes.size() > 0">
    	AND template_type IN
     	<foreach collection="infoTemplateAll.templateTypes" separator="," index="index" item="item"     
     		open="("  close=")">
            #{infoTemplateAll.templateTypes[${index}]}
        </foreach>
     </if>
  2. 模糊查找
    <if test="infoTemplateAll.templateName != null">
        AND template_name LIKE '%${infoTemplateAll.templateName}%'
    </if>
  3. 查找时间段
    <if test="infoTemplateAll.createTimeEnd != null">
        <![CDATA[ AND create_time <= #{infoTemplateAll.createTimeEnd}]]>
    </if>
    <if test="infoTemplateAll.createTimeStart != null">
        AND create_time >= #{infoTemplateAll.createTimeStart}
    </if>
  4. 插入数据返回主键ID
    <insert id="insertSelective" parameterType="com.zbjk.lcs.bss.domain.infocenter.InfoRoleTemplate"
    	useGeneratedKeys="true" keyProperty="id">
        INSERT INTO info_role_template
     	<trim prefix="(" suffix=")" suffixOverrides=",">
      		<if test="id != null">
       			id,
      		</if>
      		<if test="roleId != null">
          		role_id,
      		</if>
     	</trim>
     	<trim prefix="values (" suffix=")" suffixOverrides=",">
      		<if test="id!=null">
          		#{id},
      		</if>
      		<if test="roleId!=null">
          		#{roleId}
      		</if>
     	</trim>
    </insert>
  5. 批量插入
    <insert id="insertSelectives" parameterType="java.util.List">
    	INSERT INTO oap_detail_income
    		(
    			income_seq_num,
    			interest_terms
    		)
    		VALUES
    			<foreach collection="list" item="file" index="index"
    			separator=",">
    			(
    			 #{file.incomeSeqNum},
    			 <choose>
    				<when test="file.interestTerms != null">
    					#{file.interestTerms},
    				</when>
    				<otherwise>
    					0,
    				</otherwise>
    			 </choose>
    			)
    		 </foreach>
    </insert>
  6. 表结构和实体映射
    <resultMap id="BaseResultMap" type="com.zbjk.lcs.oap.domain.appt.OapOrderAppt">
        <id property="id" column="id"/>
        <result property="apptSeqNum" column="appt_seq_num"/>
        <result property="bUserId" column="b_user_id"/>
    </resultMap>
  7. 引入通用查询结果列
    <sql id="Base_Column_List">
    	id,appt_seq_num,b_user_id
    </sql>
    <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer">
        SELECT
        <include refid="Base_Column_List"/>
        FROM oap_order_appt
        WHERE id = #{id}
    </select>
  8. 修改
    <update id="updateByPrimaryKeySelective" parameterType="com.zbjk.lcs.oap.domain.appt.OapOrderAppt">
        UPDATE oap_order_appt
        <set>
            <if test="apptSeqNum != null">
                appt_seq_num = #{apptSeqNum},
            </if>
        </set>
        WHERE id = #{id}
    </update>
  9. 插入的时候系统时间值可以直接用now()
  10. xml最外层包裹

<mapper namespace="com.zbjk.lcs.oap.mapper.appt.OapOrderApptMapper">
</mapper>
11.oracle计算查询距离当前时间前60天的数据
SELECT
       EXPIREDATE
FROM
       PRSP_SYN_PRODUCT
WHERE
       PP.DELETE_FLG = '0'
AND TO_DATE (TO_CHAR (SYSDATE, 'yyyyMMdd'),'yyyyMMdd) - TO_DATE (EXPIREDATE, 'yyyyMMdd') <= 60
AAAA.遇到的问题
    ①报错
Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error
将sql语句放到数据库里面执行是没有问题的。最后看xml里面写的,在句子后面加了一个;

 ②oracle查询sum和单个字段同时存在

select apdt,apkind,sum(subamt) sum2 from prsp.prsp_syn_trade where apkind in('012','022','056') and tradest = 'Y' and apdt between '20170101' and '20171231'
and custno = '10971358583' group BY apkind,tradest,apdt,custno;
单个字段必须是where条件后面的才可以,不然就报不是 GROUP BY 表达式
阅读更多
换一批

没有更多推荐了,返回首页