阶段性 技术总结

（1）定义日志

private Logger logger = LoggerFactory.getLogger(this.getClass());

（2）打印日志

logger.info("活动组表中组ID为{}的活动为：{}",actGroupIds, actIds);

（3）定义常量[全大写,如果有两个字符串则使用下划线分割]

 public static final String SUCCESS = "success"; public static final int SUCCESS_CODE = 0;

（4）调用远程链接

 HttpResponse<JsonNode> response = Unirest.post(commonConfig.getUserCreateApiUrl()).     header("accept", "application/json").     field("userName", iUserName).     field("idNum", iIdNum).     field("idType", iIdType).     field("phoneNum", iPhoneNum).     field("channelId", cId).     field("channelUserId", cPId)     .asJson();

（5）常用的sql语句

1.传入list入参，返回一个类出参

<resultMap id="roleAttMap" type="com.tesla.smp.domain.global.RoleAttribute" >     <!--       WARNING - @mbggenerated       This element is automatically generated by MyBatis Generator, do not modify.     -->     <id column="ID" property="id" jdbcType="BIGINT" />     <result column="ATTRIBUTE_ID" property="attributeId" jdbcType="INTEGER" />     <result column="ROLE_ID" property="roleId" jdbcType="INTEGER" />   </resultMap> <!-- 根据角色ID和属性id查询角色属性列表 --> <select id="getRoleAttByIds"  resultMap="roleAttMap">    SELECT     id,role_id,attribute_id   FROM   ROLE_ATTRIBUTE         WHERE 1=1         AND ROLE_ID = #{rId}         AND ATTRIBUTE_ID IN         <foreach collection="ids" index="index" item="item" open="("  separator="," close=")">          #{item}         </foreach> </select>

2.入参是一个list，批量插入

<!--批量插入并返回插入成功的条数-->  <insert id="insertSASReviseData" parameterType="java.util.List">      insert into sas_achive_revise_batch     (   ID,   BATCH_NO,   REMD_USER_ID,   REMD_ID_NUM,   REMD_USER_NAME,   REMD_PHONE_NUM,   REMD_USER_TYPE,   ORG_CODE,   ORG_NAME,   EXT1,   EXT2,   EXT3,   CREATE_DATE  )  select      SAS_ACHIVE_REVISE_BATCH_SEQ.nextval,   A.*  from  (      <foreach collection="ets" item="item" index="index" separator="union all">         select    #{item.batchNo,jdbcType=VARCHAR},    #{item.remdUserId,jdbcType=VARCHAR},    #{item.remdUserIdNum,jdbcType=VARCHAR},    #{item.remdUserName,jdbcType=VARCHAR},    #{item.remdPhoneNum,jdbcType=VARCHAR},    #{item.remdUserType,jdbcType=VARCHAR},    #{item.orgCode,jdbcType=VARCHAR},    #{item.orgName,jdbcType=VARCHAR},    #{item.ext1,jdbcType=VARCHAR},    #{item.ext2,jdbcType=VARCHAR},    #{item.ext3,jdbcType=VARCHAR},    sysdate       from dual      </foreach>   )A </insert> 

3.插入前做一个完整的校验

<insert id="insertGift" parameterType="java.util.Map">insert into T_ACTY_GOODS_REL<trim prefix="(" suffix=")" suffixOverrides=","><if test="giftID != null">giftID,</if><if test="actyID != null">actyID,</if></trim><trim prefix="values (" suffix=")" suffixOverrides=","><if test="giftID != null">#{giftID,jdbcType=VARCHAR},</if><if test="actyID != null">#{actyID,jdbcType=VARCHAR},</if></trim></insert>

4.多表查询 入参为表名的list

<!-- 通过用户ID和角色ID查询userRoleId -->  <select id="selectUserRoleId" parameterType="java.lang.String" resultType="java.lang.String">  SELECT   ID  FROM  (  <foreach collection="userRoletables" item="item" separator="union all">   SELECT    ID   FROM ${item}   WHERE    USER_ID = #{userId}   AND    ROLE_ID = #{roleId}  </foreach>  ) </select>5.查询条件是一个list<select id="select" resultMap="userRoleAttributeMap">  SELECT  id,  user_role_id,  role_attribute_id,  attribute  FROM  ${tableName}  WHERE 1=1  AND user_role_id in  <foreach item="item" collection="ids" open="(" separator="," close=")">   #{item}  </foreach> </select>

6.入参是一个类 出参也是一个类 返回值包含数字到指定字符串的转换

 <resultMap id="activity" type="com.cmbc.smp.model.Activity">  <id column="ID" property="id" jdbcType="INTEGER" />  <result column="ROW_ID" property="rowId" jdbcType="VARCHAR" />  <result column="ACTIVITY_CODE" property="activityCode" jdbcType="VARCHAR" />  <result column="ACTIVITY_NAME" property="activityName" jdbcType="VARCHAR" />  <result column="ACTIVITY_DESCRIPTION" property="activityDescription" jdbcType="VARCHAR" />  <result column="INDIRECT_RECOMMEND" property="indirectRecommend" jdbcType="VARCHAR" />  <result column="CREATE_TIME" property="createTime" jdbcType="BIGINT" />  <result column="LAST_MODIFIED" property="lastModified" jdbcType="BIGINT" />  <result column="BANNER_PATH" property="bannerPath"  jdbcType="VARCHAR" /> </resultMap> <!-- 报表导出 selectActivitys --> <select id="selectActivitys" parameterType="java.lang.String" resultMap="activity">  select  ID,  ACTIVITY_CODE,  ACTIVITY_NAME,  ACTIVITY_DESCRIPTION,  CASE(INDIRECT_RECOMMEND) WHEN '1' THEN '是' ELSE '否' END AS INDIRECT_RECOMMEND,  to_char(CREATE_TIME,'yyyy-mm-dd hh24:mi:ss') CREATE_TIME ,  to_char(LAST_MODIFIED,'yyyy-mm-dd hh24:mi:ss') LAST_MODIFIED,  BANNER_PATH  from  ACTIVITY  where 1=1  <if test="activity.activityCode  != null and activity.activityCode != ''">   and ACTIVITY_CODE=#{activity.activityCode}  </if>  <if test="activity.activityName != null and activity.activityName != ''">   and ACTIVITY_NAME=#{activity.activityName}  </if>  <if test="activity.indirectRecommend != null and activity.indirectRecommend != ''">   and INDIRECT_RECOMMEND=#{activity.indirectRecommend}  </if>  <if test="activity.createTime != null and activity.createTime != ''">   and TO_CHAR(CREATE_TIME,'YYYY-MM-DD') = '${activity.createTime}'  </if>  <if test="activity.lastModified != null and activity.lastModified != ''">   and TO_CHAR(LAST_MODIFIED,'YYYY-MM-DD') = '${activity.lastModified}'  </if> </select>

7.更新数据，入参是一个类

<update id="editActivity" parameterType="com.cmbc.smp.model.Activity">  update  ACTIVITY  <set>   <if test="activity.activityName != null and activity.activityName != ''">    ACTIVITY_NAME=#{activity.activityName},   </if>   <if test="activity.activityDescription != null and activity.activityDescription != ''">    ACTIVITY_DESCRIPTION=#{activity.activityDescription},   </if>       LAST_MODIFIED=sysdate,   <if test="activity.indirectRecommend != null and activity.indirectRecommend != ''">    INDIRECT_RECOMMEND=#{activity.indirectRecommend},   </if>   <if test="activity.bannerPath != null and activity.bannerPath != ''">    BANNER_PATH=#{activity.bannerPath}   </if>  </set>  where ID = #{activity.id} </update>

8.删除数据

<delete id="deleteActivity" parameterType="java.lang.String">  delete  from  ACTIVITY  where  ID=#{id}  and ACTIVITY_CODE=#{activityCode}  and ACTIVITY_NAME=#{activityName}  and ACTIVITY_DESCRIPTION=#{activityDescription}  and INDIRECT_RECOMMEND=#{indirectRecommend} </delete>

9.模糊查询

<select id="selectBranchBankDoPagination" parameterType="com.cmbc.smp.model.Bank" resultMap="branchBank">  select  ID ,  BRANCH_NAME  from  ONLINE_BRA_OFF_REC_BRA  where 1=1  <if test="BranchBank.branchName  != null and BranchBank.branchName != ''">   and BRANCH_NAME like CONCAT(CONCAT('%','${BranchBank.branchName}'),'%')  </if>  order by ID asc </select>

10.left join 多表联合查询

<select id="selectRoleActivityDoPagination" parameterType="com.cmbc.smp.model.RoleActivity" resultType="java.util.Map">  select  e.ID,  e.ROLE_ID ,  e.ACTIVITY_ID ,  t.ROLE_NAME,  t.ROLE_TYPE,  t.ROLE_DESCRIPTION ,  p.ACTIVITY_CODE ,  p.ACTIVITY_NAME ,  p.ACTIVITY_DESCRIPTION ,  CASE(p.INDIRECT_RECOMMEND) WHEN '1' THEN '是' ELSE '否' END AS INDIRECT_RECOMMEND,  to_char(e.CREATE_TIME,'yyyy-mm-dd hh24:mi:ss') CREATE_TIME,  to_char(e.LAST_MODIFIED,'yyyy-mm-dd hh24:mi:ss')  LAST_MODIFIED  from  ROLE_ACTIVITY e  left join  ACTIVITY p  on e.ACTIVITY_ID=p.ID  left join  ROLE t  on e.ROLE_ID=t.ID  where 1=1  <if test="roleActivity.roleName  != null and roleActivity.roleName != ''">    and t.ROLE_NAME=#{roleActivity.roleName}  </if>  <if test="roleActivity.activityName  != null and roleActivity.activityName != ''">   and p.ACTIVITY_NAME=#{roleActivity.activityName}  </if>  <if test="roleActivity.createTime != null and roleActivity.createTime != ''">   and TO_CHAR(e.CREATE_TIME,'YYYY-MM-DD') = '${roleActivity.createTime}'  </if>  <if test="roleActivity.lastModified != null and roleActivity.lastModified != ''">   and TO_CHAR(e.LAST_MODIFIED,'YYYY-MM-DD') = '${roleActivity.lastModified}'  </if>  order by e.ID asc </select>

11.查询时多层数字到字符串的转换

<select id="selectUserRoleAttrDoPagination" parameterType="com.cmbc.smp.model.UserRoleAttribute"  resultType="java.util.Map">  select  *  from (  select  e.ID,e.USER_ROLE_ID,e.ROLE_ATTRIBUTE_ID, (CASE(w.ATTRIBUTE_name)    WHEN '分行名称' THEN    (select BRANCH_NAME from ONLINE_BRA_OFF_REC_BRA where e.ATTRIBUTE=id)    WHEN '支行名称' THEN    (select SUB_BRANCH_NAME from ONLINE_BRA_OFF_REC_SUB_BRA where e.ATTRIBUTE=id)     WHEN '机构名称' THEN    (     (CASE(f.role_name)     when '卡中心客户经理'     then (select ORG_NAME from ONLINE_REC_ORG  where e.ATTRIBUTE =id)     when '卡中心行编员工'     then (select ORG_NAME from ONLINE_BANK_EMP_REC_ORG  where e.ATTRIBUTE =id)     else e.ATTRIBUTE end)    )     else e.ATTRIBUTE end) ATTRIBUTE,  to_char(e.CREATE_TIME,'yyyy-mm-dd hh24:mi:ss') CREATE_TIME,  to_char(e.LAST_MODIFIED,'yyyy-mm-dd hh24:mi:ss') LAST_MODIFIED,  p.VALID,p.ID_TYPE,p.ID_NUM,p.USER_ID,p.ROLE_ID,  f.ROLE_NAME,f.ROLE_TYPE,f.ROLE_DESCRIPTION,  w.ATTRIBUTE_NAME,w.ATTRIBUTE_DESCRIPTION  from  ${userRoleAttribute.tableofUserRoleAttr} e  left join  ${userRoleAttribute.tableofUserRole} p  on e.USER_ROLE_ID=p.ID  left join  ROLE f  on  p.ROLE_ID=f.ID  left join  ROLE_ATTRIBUTE d  on e.ROLE_ATTRIBUTE_ID=d.ID  left join  ATTRIBUTE w  on d.ATTRIBUTE_ID=w.ID  where 1=1  <!-- 支持模糊查询 -->  <if test="userRoleAttribute.idNum  != null and userRoleAttribute.idNum != ''">   and p.ID_NUM=#{userRoleAttribute.idNum}  </if>  <if test="userRoleAttribute.roleId  != null and userRoleAttribute.roleId != ''">   and p.ROLE_ID=#{userRoleAttribute.roleId}  </if>  <if test="userRoleAttribute.attrId  != null and userRoleAttribute.attrId != ''">   and d.ATTRIBUTE_ID=#{userRoleAttribute.attrId}  </if>  <if test="userRoleAttribute.createTime  != null and userRoleAttribute.createTime != ''">   and TO_CHAR(e.CREATE_TIME,'YYYY-MM-DD') = '${userRoleAttribute.createTime}'  </if>  and p.VALID='1'  )  where 1=1 </select>