MySQL存储过程创建+项目实践操作

1.mysql创建存储过程

CREATE PROCEDURE join_blacklist(in cInformationId VARCHAR(36),in cBlacklistId VARCHAR(36),in blackType INT(11),in label INT(11),in textareas VARCHAR(600))
	BEGIN
		set @count = (select COUNT(*) from C_blacklist where C_information_id = cInformationId);
			IF @count = 0 THEN
					insert into C_blacklist(c_blacklist_id,C_information_id,cbt_id,C_label_id,remark) value(cBlacklistId,cInformationId,blackType,label,textareas);
			END IF;
	END;
DELIMITER; 

2.Mapping.xml

<insert id="joinBlacklistProcedure" parameterType="java.util.HashMap">
      {
        call join_blacklist
            (
              #{map.cInformationId},
              #{map.cBlacklistId},
              #{map.blackType},
              #{map.label},
              #{map.textareas}
            )
      }
    </insert>

3.Controller类

/**
 * 
 * @param cInformationId
 * @param blackType
 * @param label
 * @param textareas
 * @return
 */
@RequestMapping("/joinBlackList")
@ResponseBody
private Object joinBlackList(String cInformationId,Integer blackType ,Integer label ,String textareas){
    int i = -1;
    Map<String,Object> map = new HashMap<String,Object>();
    map.put("cBlacklistId",LittleUtil.createUUID());
    map.put("cInformationId",cInformationId);
    map.put("blackType",blackType);
    map.put("label",label);
    map.put("textareas",textareas);
    i = iClienteleListService.joinBlacklistProcedure(map);
    return JSONObject.toJSON(i);
}

4.数据库测试

   4.1当前表中数据

 

4.2 用存储过程往表中添加数据

CALL join_blacklist('fefb289b-1240-42e3-a197-bbc80db2b662','1',1,2,'测试数据');

4.3 再查询表中数据