mapper中的List和Map集合以及now()使用

谁践踏了优雅 2022-05-20 09:48 90阅读 0赞

###  1.将javaBean转化为HashMap ###

public PageRet<BusiOpenRecordEntity> getLittleOpenRecords(BusiOpenRecordVo busiOpenRecordVo) {
            PageRet<BusiOpenRecordEntity> pageResult = new PageRet<BusiOpenRecordEntity>();
            Map<String, Object> param = new HashMap<String, Object>();
            param.put("userId", busiOpenRecordVo.getUserId());
            param.put("startIndex", (busiOpenRecordVo.getPageIndex() - 1) * busiOpenRecordVo.getPageSize());
            param.put("endIndex", busiOpenRecordVo.getPageSize());
            param.put("regionId", busiOpenRecordVo.getRegionId());
            param.put("cityId", busiOpenRecordVo.getCityId());
            param.put("projectCode", busiOpenRecordVo.getProjectCode());
            param.put("buildingCode", busiOpenRecordVo.getBuildingCode());
            param.put("devName", busiOpenRecordVo.getDevName());
            param.put("openWay", busiOpenRecordVo.getOpenWay());
            param.put("openStatus", busiOpenRecordVo.getOpenStatus());
            param.put("openDateStart", busiOpenRecordVo.getOpenDateStart());
            param.put("openDateEnd", busiOpenRecordVo.getOpenDateEnd());
            param.put("propertyCode", busiOpenRecordVo.getPropertyCode());
            param.put("custCode", busiOpenRecordVo.getCustCode());
            param.put("userName", busiOpenRecordVo.getUserName());
            Integer count = busiOpenRecordDao.countLittleOpenRecords(param);
            Integer pageIndex = busiOpenRecordVo.getPageIndex();
            Integer pageSize = busiOpenRecordVo.getPageSize();
            pageResult.setCurPage(pageIndex);
            pageResult.setPageSize(busiOpenRecordVo.getPageSize());
            Integer totalPageNum = (count + pageSize - 1) / pageSize;
            pageResult.setTotalPage(totalPageNum);
            pageResult.setTotalSize(count);
            List<BusiOpenRecordEntity> list = busiOpenRecordDao.selectLittleOpenRecords(param);
            pageResult.setResultList(list);
            return pageResult;
        }

### 2.List集合的使用         2.1    接口的实现类传入一个集合 ###

public void insertBatch(List<BusiOpenRecordEntity> busiOpenRecordEntities)
     {      
       busiOpenRecordDao.insertBatch(busiOpenRecordEntities);     
    }

2.2 mapper里面的执行语句,参数为list类型

<insert id="insertBatch" parameterType="java.util.List">
    	insert into t_busi_open_record(
    	<include refid="insertField"></include>
    	) values
    	<foreach collection="list" item="item" separator=",">
    	(
    		#{item.id},
    		#{item.devId},
    		#{item.devName},
    		#{item.regionId},
    		#{item.cityId},
    		#{item.projectCode},
    		#{item.buildingCode},
    		#{item.openWay},
    		#{item.propertyCode},
    		#{item.custCode},
    		#{item.createDate},
    		#{item.openDate},
    		#{item.openStatus},
    		#{item.reserveId},
    		#{item.userName},
    		#{item.status},
    		#{item.operateUser},
    	/*	#{item.operateDate},*/
    		now(),
    		#{item.openUnique}
    		
    		
    	)
    	</foreach>
    </insert>

2.3 动态sql

<sql id="queryOpenCondition">
            <if test="userId != null and userId != ''">
                and FIND_IN_SET(t.project_code, (SELECT project_codes FROM t_sym_user_area where user_id=#{userId})) 
            </if>
            <if test="projectCode != null and projectCode != ''">
                AND t.project_code = #{projectCode} 
            </if>
            <if test="buildingCode != null and buildingCode != ''">
                AND t.building_code = #{buildingCode} 
            </if>
            <if test="openWay != null and openWay != ''">
                AND t.open_way = #{openWay} 
            </if>
            <if test="propertyCode != null and propertyCode != ''">
                AND t.property_code = #{propertyCode} 
            </if>
            <if test="custCode != null and custCode != ''">
                AND t.cust_code = #{custCode} 
            </if>
            
            <if test="userName != null and userName != ''">
               AND t.user_name like CONCAT('%',#{userName},'%')
            </if>
            <if test="openStatus != null">
                AND t.open_status = #{openStatus} 
            </if>
            <if test="openDateStart != null and openDateStart != ''">
                <![CDATA[ AND t.open_date >= str_to_date(#{openDateStart}, '%Y-%m-%d %H:%i:%s')]]> 
            </if>
            <if test="openDateEnd != null and openDateEnd != ''">
                <![CDATA[ AND t.open_date <= str_to_date(#{openDateEnd}, '%Y-%m-%d %H:%i:%s')]]> 
            </if>
            <if test="devName != null and devName != ''">
               and d.dev_name like CONCAT('%',#{devName},'%')
            </if>    
        </sql>

2.4 now()使用，直接在数据库层操作

<update id="updateUser" parameterType="cn.zto.mybatis.pojo.User">
    	UPDATE tb_user
    	SET 
    	 user_name = #{userName},
    	 password = #{password},
    	 name = #{name},
    	 age = #{age},
    	 sex = #{sex},
    	 birthday = #{birthday},
    	 updated = NOW()
    	WHERE
    		id = #{id}
    </update>

### 3.执行验证 ###

修改mapper里面的sql语句，如何测试？  
首先进行白盒测试，然后去控制台查看sql打印语句；  
控制台：

select t.id as id, t.request_cust_code as requestCustCode, t.request_property_code as requestPropertyCode, t.status as status, t.handle_cust_code as handleCustCode, DATE_FORMAT(t.handle_time,'%Y-%m-%d %H:%i:%s') as handleTime, t.remarks as remarks, DATE_FORMAT(t.operate_date,'%Y-%m-%d %H:%i:%s') as operateDate, t.operate_user as operateUser ,c.cust_name as custName,c.main_mobile as mainMobile, p.property_name propertyName from t_app_little_todo as t LEFT JOIN t_crm_customer c on t.request_cust_code = c.cust_code left join t_crm_property p on p.property_code=t.request_property_code WHERE t.status = ? AND (t.request_cust_code = ? or t.handle_cust_code = ?) 
    19:05:09.007 [DubboServerHandler-192.xx.xx.1:6700-thread-18] DEBUG com.xxxx.plat.little.dao.AppLittleTodoDao.list - ==> Parameters: 0(Integer), 1001097667(String), 1001097667(String)

总结：至此，工作中经常用到的map和list类型，都做了简单的介绍，以及应用场景。希望对大家有所帮助！！