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}
<!--新增-->
<!--,#{item.buildingCode}
#{item.reserveId}-->
)
</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类型,都做了简单的介绍,以及应用场景。希望对大家有所帮助!!