mysql使用小整理

今天，使用mysql，使用知识点有：

1.批量导入测试数据，即同一条数据重复插入若干遍，写了一个存储过程。

2.使用函数，自动创建不重复的主键id

遇到的问题，

1.写存储过程的时候，遇到了mysql的保留字，name,code，需要进行转义，使用反引号，位于键盘esc的下面`````这类符号

2.使用uuid()函数生成主键时，需要方法来去掉中间连接符-，使用replace()函数

3.使用spring mvc进行前台数据交互，使用百度API解析经纬度，无法进入action，2处地方修改，第一为web.xml的过滤器问题，第二是需要在xml文件中配置action文件。

今天使用mysql的存储过程，因为需要前台传参数（开始月份和截止月份），确定所计算的收费率，并且不同的参数所执行的sql是不一样的，所以前台使用拼接sql的方式调用存储过程：

String timeType=(String) parameter.get("timeType");String beginMonth=(String) parameter.get("beginMonth");String endMonth=(String) parameter.get("endMonth");String companyId=(String) parameter.get("companyId");String treeCode=(String) parameter.get("treeCode");String dictItemName=(String) parameter.get("dictItemName");String sql="call pro_month_rate ('"+timeType+"','"+beginMonth+"','"+endMonth+"','"+companyId+"','"+treeCode+"','"+dictItemName+"')";

使用一个list的object[]数组接收

List<Object[]> list=viewReportRateMonthDao.findListBySql(sql);

遍历集合，存入entity

for (Object[] objects : list) {ViewReportRateMonth entity=new ViewReportRateMonth();entity.setReceivableDetailId(objects[0].toString());entity.setCompanyId(objects[1].toString());entity.setCode(objects[2].toString());entity.setLevel2Name(objects[3].toString());entity.setThisMouth(objects[6].toString());entity.setDictItemName(objects[7].toString());entity.setDictItemCode(objects[8].toString());entity.setAmountReceivable(new BigDecimal(objects[9].toString()));entity.setActuallyAmount(new BigDecimal(objects[10].toString()));entity.setReceivableRate(objects[11].toString());entity.setTreeCode(objects[13].toString());listEntity.add(entity);}

上面是后台代码，

接下来是创建存储过程代码：

demo是

create procedure pro_month_rate(in timeType varchar(32), in beginMonth varchar(32),in endMonth varchar(32),in companyId varchar(32) ,in treeCode varchar(32),in dictItemName varchar(32))begin if timeType='month' THEN SELECT`vrr`.`receivable_detail_id` AS `receivable_detail_id`,`vrr`.`company_id` AS `company_id`,vrr.house_name as `code`,`vrr`.`level2_name` AS `level2_name`,vrr.level3_name,vrr.level4_name,`vrr`.`this_mouth` AS `this_mouth`,`vrr`.`dict_item_name` AS `dict_item_name`,`vrr`.`dict_item_code` AS `dict_item_code`,sum(`vrr`.`fee_amount`) AS `amount_receivable`,sum(IF ((`vrr`.`state` = '1'),(`vrr`.`fee_amount` + `vrr`.`pref_amount`),0)) AS `actually_amount`,concat(format(((sum(IF ((`vrr`.`state` = '1'),(`vrr`.`fee_amount` + `vrr`.`pref_amount`),0)) / sum((`vrr`.`fee_amount` + `vrr`.`payment_amount`))) * 100),0),'%') AS `receivable_rate`,`vrr`.`collect_date` AS `collect_date`,vrr.tree_codeFROM`view_report_base_rate` `vrr`where vrr.tree_code like CONCAT(treeCode,'%') and vrr.company_id=companyId and vrr.this_mouth= beginMonth and FIND_IN_SET(vrr.dict_item_name,dictItemName)GROUP BY`vrr`.`dict_item_name` ,vrr.company_id ,vrr.this_mouth ,vrr.level3_name ;ELSEIF timeType='year' THEN SELECT`vrr`.`receivable_detail_id` AS `receivable_detail_id`,`vrr`.`company_id` AS `company_id`,vrr.house_name as `code`,`vrr`.`level2_name` AS `level2_name`,vrr.level3_name,vrr.level4_name,`vrr`.`this_mouth` AS `this_mouth`,`vrr`.`dict_item_name` AS `dict_item_name`,`vrr`.`dict_item_code` AS `dict_item_code`,sum(`vrr`.`fee_amount`) AS `amount_receivable`,sum(IF ((`vrr`.`state` = '1'),(`vrr`.`fee_amount` + `vrr`.`pref_amount`),0)) AS `actually_amount`,concat(format(((sum(IF ((`vrr`.`state` = '1'),(`vrr`.`fee_amount` + `vrr`.`pref_amount`),0)) / sum((`vrr`.`fee_amount` + `vrr`.`payment_amount`))) * 100),0),'%') AS `receivable_rate`,`vrr`.`collect_date` AS `collect_date`,vrr.tree_codeFROM`view_report_base_rate` `vrr`where vrr.tree_code like CONCAT(treeCode,'%') and vrr.company_id=companyId and vrr.this_mouth like CONCAT(beginMonth,'%') and FIND_IN_SET(vrr.dict_item_name,dictItemName)GROUP BY`vrr`.`dict_item_name` ,vrr.company_id ,vrr.this_mouth ,vrr.level3_name ;else SELECT`vrr`.`receivable_detail_id` AS `receivable_detail_id`,`vrr`.`company_id` AS `company_id`,vrr.house_name as `code`,`vrr`.`level2_name` AS `level2_name`,vrr.level3_name,vrr.level4_name,`vrr`.`this_mouth` AS `this_mouth`,`vrr`.`dict_item_name` AS `dict_item_name`,`vrr`.`dict_item_code` AS `dict_item_code`,sum(`vrr`.`fee_amount`) AS `amount_receivable`,sum(IF ((`vrr`.`state` = '1'),(`vrr`.`fee_amount` + `vrr`.`pref_amount`),0)) AS `actually_amount`,concat(format(((sum(IF ((`vrr`.`state` = '1'),(`vrr`.`fee_amount` + `vrr`.`pref_amount`),0)) / sum((`vrr`.`fee_amount` + `vrr`.`payment_amount`))) * 100),0),'%') AS `receivable_rate`,`vrr`.`collect_date` AS `collect_date`,vrr.tree_codeFROM`view_report_base_rate` `vrr`where vrr.tree_code like CONCAT(treeCode,'%') and vrr.company_id=companyId and vrr.this_mouth >= beginMonth and vrr.this_mouth<= endMonth and FIND_IN_SET(vrr.dict_item_name,dictItemName)GROUP BY`vrr`.`dict_item_name` ,vrr.company_id ,vrr.this_mouth ,vrr.level3_name ;end if;end;

看着复杂，其实就是一个创建过程以及简单的if ,else逻辑

create procedure pro_month_rate()

begin
if timeType='month' THEN
select * from...

ELSEIF timeType='year' THEN
select * from ..   ; //另一段sql

else
select * from ...    ; //最后sql

end if;

end;

使用了一个函数FIND_IN_SET(table.field,'数组')，多个参数适用in和都好分开。

根据b表数据更新a表某个字段

update a inner join b on a.bid=b.id set a.x=b.x,a.y=b.y ;