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部分代码：

<div class="form-group">
 <label for="name" class="col-sm-2 control-label">接口名称<a style="color:red;">*</a>:</label>
 <div class="col-sm-4">
   <input type="text" id="name" name="name" class="form-control"/>
 </div>
 <label for="status" class="col-sm-2 control-label">接口状态<a style="color:red;">*</a>:</label>
 <div class="col-sm-4" >
   <select id="status" disabled="disabled" class="form-control">
     <option value="0">保存</option>
     <option value="1">已创建</option>
   </select>

</div>
</div>
<div class="form-group">
 <label for="content" class="col-sm-2 control-label">接口内容<a style="color:red;">*</a>:</label>
 <div class="col-sm-10">
   <textarea id="content" name="content" rows="5" class="form-control"></textarea>
 </div>
</div>
<div class="form-group">
 <label for="explain_info" class="col-sm-2 control-label">接口说明:</label>
 <div class="col-sm-10">
   <textarea id="explain_info" name="explain_info" rows="3" class="form-control"></textarea>
 </div>
</div>
<div class="form-group">
 <label for="qtInterList" class="col-sm-2 control-label">接口参数:</label>
 <div class="col-sm-10">
   <div class="ibox-content" style="width:100％;">
     <table id="qtInterList" class="easyui-datagrid">
     </table>
   </div>
 </div>
</div>

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<String,Object> map);
}

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(Map<String,Object> map) {
 ResultInfo resultInfo = new ResultInfo();
 QtInterface qtInterface=new QtInterface();
 qtInterface =buildInterface(map, qtInterface);// 加载接口配置信息
 List<QtInterfaceParameter> paraList = new ArrayList<QtInterfaceParameter>();
 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("BEGIN\n");
 bf.append(qtInterface.getContent()); // 存储过程内容
 bf.append(";\nEND;");
 // 先执行删除操作
 jdbcTemplate.execute("drop procedure if exists " + qtInterface.getName() + " ;");
 jdbcTemplate.execute(bf.toString());

}

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

/**
* 初始化接口配置参数明细
*
*/
 private List<QtInterfaceParameter> buildParam(String postData) {
   List<QtInterfaceParameter> list = new ArrayList<QtInterfaceParameter>();
   if(postData!=null &&!"".equals(postData)){
     List<Map<String, Object>> listParam = (List<Map<String, Object>>) JsonMapper.fromJsonString(postData, ArrayList.class);
     for (Map<String, Object> 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<String, Object> 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

来源：https://www.cnblogs.com/Aimee-AI/p/10849198.html