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(Mapmap);  }

2）然後創建接口的實現類：

  @Service  public class TestServiceImpl implements TestService {     /**   * 創建存儲過程   *   * @param map 接口配置和接口參數信息   * 參數詳解： type 輸入輸出參數，取值為 in,out   *       paraType 參數類型。取值為：1：int 2:double 3:varchar 4:datetime   * @return   */  @Override  @Transactional  public void createPro(Mapmap) {    ResultInfo resultInfo = new ResultInfo();    QtInterface qtInterface=new QtInterface();    qtInterface =buildInterface(map, qtInterface);// 加載接口配置信息    ListparaList = new ArrayList();    paraList = buildParam(map.get("jsonData"));// 加載接口配置信息    StringBuffer bf = new StringBuffer(); // 建立生成過程的語句    bf.append("create procedure 	");    bf.append(qtInterface.getName());    bf.append(" ");    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() +"' "); // 添加描述信息    bf.append("BEGIN ");    bf.append(qtInterface.getContent()); // 存儲過程內容    bf.append("; END;");    // 先執行刪除操作    jdbcTemplate.execute("drop procedure if exists " + qtInterface.getName() + " ;");    jdbcTemplate.execute(bf.toString());     }     /**   * 初始化接口配置信息   *   */  private QtInterface buildInterface(Mapmap, QtInterface qtInterface) {    // 接口配置名稱    if (map.get("name") != null && !"".equals(map.get("name "))) {      qtInterface.setName((String) map.get("name "));    }    //此處省略其他項，其他項的取值方法跟上面的一樣 …    return qtInterface;  }     /**   * 初始化接口配置參數明細   *   */    private ListbuildParam(String postData) {      Listlist = new ArrayList();      if(postData!=null &&!"".equals(postData)){        List<Map> listParam = (List<Map>) JsonMapper.fromJsonString(postData, ArrayList.class);        for (Mapmap : 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 Mapmap  ) {    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