今天,使用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_code
FROM
`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_code
FROM
`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_code
FROM
`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 ;