表为 tbl_xx省_xx类型，每个省份，类型一张表，很多张表，所以采用存储过程方式进行操作。
在用户修改一条记录时候，会备份数据到log表中，在update时候出错，insert into log没问题。
dao:
- Java code
public void updateMerchant(Merchant m,String province,String type) { Query query = this.getSession().getNamedQuery("updateMerchant"); query.setLong(0, m.getClId()); query.setString(1, m.getClName()); query.setString(2, m.getClCity()); query.setInteger(3, m.getClCategory()); query.setString(4, m.getClDetailaddress()); query.setString(5, m.getClNormaladdress()); query.setString(6, m.getClTelephone()); query.setDouble(7, m.getClLongitude()); query.setDouble(8, m.getClLatitude()); query.setString(9, m.getClTraffic()); query.setString(10, m.getClDescription()); query.setLong(11, m.getClImageid()); query.setInteger(12, m.getClRank()); query.setString(13, province); query.setString(14, type); query.executeUpdate(); }action:
- Java code
public String updateMerchant() throws Exception { try { //根据ID所查找出来的商家对象 Merchant merchant = (Merchant) ActionContext.getContext() .getSession().get("merchant"); //省份拼音转换,将省份拼音放入数据路,例如江苏省==jiangsu String provinceSpell = ActionContext.getContext().getSession() .get(province).toString(); //登陆的用户 User user = (User) ActionContext.getContext().getSession() .get("user"); //将省份拼音和商家类型放入session,更改后重新查询 ActionContext.getContext().getSession().put("province", provinceSpell); ActionContext.getContext().getSession().put("bigtype", bigtype); m.setClTime(merchant.getClTime()); //m为struts2 动态封装页面对象 service.updateMerchant(m, provinceSpell, bigtype); //将M表中的数据插入到mlog表中备份 mLog.setClName(merchant.getClName()); mLog.setClCity(merchant.getClCity()); mLog.setClCategory(merchant.getClCategory()); mLog.setClDetailaddress(merchant.getClDetailaddress()); mLog.setClNormaladdress(merchant.getClNormaladdress()); mLog.setClLongitude(merchant.getClLongitude()); mLog.setClLatitude(merchant.getClLatitude()); mLog.setClTraffic(merchant.getClTraffic()); mLog.setClDescription(merchant.getClDescription()); mLog.setClTime(merchant.getClTime()); mLog.setClTelephone(merchant.getClTelephone()); mLog.setClProvince(provinceSpell); mLog.setClType(bigtype); mLog.setClMerchantId(m.getClId()); mLog.setClUserId(user.getClId()); //保存数据至数据库 service.copyMerchantLog(mLog); } catch (Exception e) { e.printStackTrace(); return ERROR; } ActionContext.getContext().getSession().put("msg", "<script>alert('修改成功')</script>"); return SUCCESS; }存储过程:
- SQL code
DELIMITER $$DROP PROCEDURE IF EXISTS `sp_updatemerchant` $$CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_updatemerchant`(in id bigint(20),in name varchar(45),in city varchar(45),in category int(11),in detailaddress varchar(45),in normaladdress varchar(45),in telephone varchar(45),in longitude double,in latitude double,in traffic text,in description text,in imageid bigint(20),in rank int(11),in province varchar(45),in type VARCHAR(45))BEGINSET @sqltext = concat('update tbl_',province,'_',type,' set cl_name=\'',name,'\', cl_city=\'',city,'\', cl_category=',category,',cl_detailaddress=\'',detailaddress,'\',cl_normaladdress=\'',normaladdress,'\',cl_telephone=\'',telephone,'\',cl_longitude=',longitude,',cl_latitude=',latitude,',cl_traffic=\'',traffic,'\',cl_description=\'',description,'\',cl_imageid=',imageid,',cl_rank=',rank,' where cl_id =',id,';');PREPARE sqlstr from @sqltext;Execute sqlstr;END $$DELIMITER ;