Java Spring动态生成Mysql存储过程详解

一、 背景

　　由于公司业务需要动态配置一些存储过程来生成数据，之前尝试过使用jpa来完成，或多或少都存在一些问题，最后使用了spring的Jdbctemplate。

二、 环境

　　1.此随笔内容基于spring boot项目

　　2.数据库为mysql 5.7.9版本

　　3.jdk 版本为1.8

三、 说明

　　说明：为方便表示，下列存储过程在代码中的表示我称之为接口配置　　

四、 内容

　　1、定义接口和接口参数bean；

　　　　1）接口配置bean:

@Entity
@Table(name="qt_interface")
public class QtInterface {
 @Id
 private String id;
 private String name;
 private String content;
 private String info;
 private String status;
//此处省略get、set…
}

2）接口配置参数bean:

@Entity
@Table(name="qt_interface_parameter")
public class QtInterfaceParameter {
 @Id
 private String id;
 @Column(name="inter_id")
 private String interId;
 private String name; //参数名称
 private String explain_info; //参数描述
 private String type;// 输入输出类型
 private String paraType; // 参数类型
 private Integer paraLen;
//此处省略get、set…
}

2、编写页面输入接口配置的信息；

1）Html部分代码：

  接口名称*:
  
    
  
  接口状态*:
  
    
      保存
      已创建
    
 
  


  接口内容*:
  
    
  


  接口说明:
  
    
  


  接口参数:
  
    
      
    
  

2）Js部分代码太长，就只贴一个提交方法吧

function createProduce(inter_id) {
 var postData = {
    id: $("#inter_id").val(),
    item_id: $("#item_id").val(),
    name: $("#name").val(),
    content: $("#content").val(),
    explain_info: $("#explain_info").val(),
    jsondata: JSON.stringify(jsonData)// 参数明细信息,字段就是接口配置参数bean 中的字段信息
};
 
  $.ajax({
    url: Url + 'test/createPro',
    type: 'get', //GET
    async: false,  //或false,是否异步
    data: JSON.stringify(postData),
    timeout: 5000,  //超时时间
    dataType: 'json',  //返回的数据格式：    success:   function (result, textStatus, jqXHR) {
      if (result.result == "1") { // 编辑赋值
 layer.alert("创建成功", {icon: 0});
      } else {
 layer.alert("创建失败，请检查sql语句，注意结尾不能有分号！具体错误信息："+result.msg, {icon: 5});
      }
    },
    error: function (xhr, textStatus) {
      layer.alert(textStatus);
    }
  });
}

3、将数据上传到后台之后，后台生成存储过程。当然一般情况下，我们还是先把数据接口和接口明细数据持久化保存，再来执行创建操作，可以保证数据不会丢失。此处由于篇幅问题，我就省略了中间这一步。

1）创建一个service 的接口：

public interface TestService {
    ResultInfo createPro(Map map);
}

2）然后创建接口的实现类：

@Service
public class TestServiceImpl implements TestService {
 

@Override
@Transactional
public void createPro(Map map) {
  ResultInfo resultInfo = new ResultInfo();
  QtInterface qtInterface=new QtInterface();
  qtInterface =buildInterface(map, qtInterface);// 加载接口配置信息
  List paraList = new ArrayList();
  paraList = buildParam(map.get("jsonData"));// 加载接口配置信息
  StringBuffer bf = new StringBuffer(); // 建立生成过程的语句
  bf.append("create procedure t");
  bf.append(qtInterface.getName());
  bf.append("n");
  bf.append("(");
  String para_type = "";
  int i = 1;
  for (QtInterfaceParameter qt : paraList) {
    switch (qt.getParaType()) { // 参数类型
      case "1":
 para_type = "int";
 break;
      case "2":
 para_type = "double";
 break;
      case "3":
 para_type = "varchar(" + qt.getParaLen() + ")";
 break;
      case "4":
 para_type = "datetime";
 break;
      default:
 para_type = "varchar(255)";
 break;
    }
    if (i == paraList.size()) {
      bf.append("" + qt.getType() + " " + qt.getName() + " " + para_type + ") ");
    } else {
      bf.append("" + qt.getType() + " " + qt.getName() + " " + para_type + ", ");
    }
 
    i++;
  }
  bf.append(" COMMENT '"+ qtMonitorWarnInterface.getInfo() +"'n"); // 添加描述信息
  bf.append("BEGINn");
  bf.append(qtInterface.getContent()); // 存储过程内容
  bf.append(";nEND;");
  // 先执行删除操作
  jdbcTemplate.execute("drop procedure if exists " + qtInterface.getName() + " ;");
  jdbcTemplate.execute(bf.toString());
 
}
 

private QtInterface buildInterface(Map map, QtInterface qtInterface) {
  // 接口配置名称
  if (map.get("name") != null && !"".equals(map.get("name "))) {
    qtInterface.setName((String) map.get("name "));
  }
  //此处省略其他项，其他项的取值方法跟上面的一样 …
  return qtInterface;
}
 

  private List buildParam(String postData) {
    List list = new ArrayList();
    if(postData!=null &&!"".equals(postData)){
      List> listParam = (List>) JsonMapper.fromJsonString(postData, ArrayList.class);
      for (Map map : listParam) {
 QtInterfaceParameter para = new QtInterfaceParameter();
 // 接口配置参数名称
 if (map.get("name") != null && !"".equals(map.get("name "))) {
   para.setName((String) map.get("name "));
 }
 // 此处省略其他项，其他项的取值方法跟上面的一样 …
 list.add(para);
      }
    }
    return list;
  }

3） 添加控制器进行调用：

@Controller
@RequestMapping(value = "/test")
public class TestController {
@Autowired
private TestService testService;
 
@RequestMapping(value = "/createPro", method = RequestMethod.GET)
public ResultInfo createPro(@RequestBody Map map
) {
  ResultInfo resultInfo = new ResultInfo();
  try {
    testService.createPro(Id);
  resultInfo.setResult(1);
    resultInfo.setMsg("创建过程成功");
 
  } catch (Exception e) {
    resultInfo.setResult(-1);
    resultInfo.setMsg(e.getMessage());
  }
  return resultInfo;
 }
}

4）最后动态生成的SQL就是这个样子：

CREATE PROCEDURE `testbase`.`test`(in a_user_id varchar(100))
  COMMENT '测试接口'
BEGIN
select * from userInfo where user_id=a_user_id;
END

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持考高分网。