前端传参数进行Mybatis调用mysql存储过程执行返回值详解

查询数据库中的存储过程：

方法一：

?

1	select ` name ` from mysql.proc where db = 'your_db_name' and `type`; = 'PROCEDURE'

方法二：

?

1	show procedure status;

你要先在数据库中建一个表，然后创建存储过程

我建的表a_tmp,存储过程名称bill_a_forbusiness

执行语句：  CALL bill_a_forbusiness(44,44,52,47,44,46,52,52,349171)

存储过程调用方式：

?

1 2 3 4

CALL Pro_Get_CO2( '2018' , '' , '' , '' ); CALL Pro_Get_EnergyData( '2017' ); CALL Pro_Get_Carbon_OrgType( '2014' ); CALL 存储过程名(参数);

查看存储过程或函数的创建代码：

?

1 2	show create procedure proc_name; show create function func_name;

因为这个没有返回值所以需要先传参调用执行，再查询

前端代码：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172

< template >    < div class = "app-container" >      < el-form        :model = "queryParams"        ref = "queryForm"        :inline = "true"        v-show = "showSearch"        label-width = "68px"      >        < el-form-item label = "参数输入" prop = "a" >          < el-input            v-model = "queryParams.a"            placeholder = "请输入第一参数"            clearable            size = "small"            @ keyup.enter.native = "handleQuery"          />        </ el-form-item >        < el-form-item label = "参数输入" prop = "b" >          < el-input            v-model = "queryParams.b"            placeholder = "请输入第二参数"            clearable            size = "small"            @ keyup.enter.native = "handleQuery"          />        </ el-form-item >        < el-form-item label = "参数输入" prop = "c" >          < el-input            v-model = "queryParams.c"            placeholder = "请输入第三参数"            clearable            size = "small"            @ keyup.enter.native = "handleQuery"          />        </ el-form-item >        < el-form-item label = "参数输入" prop = "d" >          < el-input            v-model = "queryParams.d"            placeholder = "请输入第四参数"            clearable            size = "small"            @ keyup.enter.native = "handleQuery"          />        </ el-form-item >        < el-form-item label = "参数输入" prop = "e" >          < el-input            v-model = "queryParams.e"            placeholder = "请输入第五参数"            clearable            size = "small"            @ keyup.enter.native = "handleQuery"          />        </ el-form-item >        < el-form-item label = "参数输入" prop = "f" >          < el-input            v-model = "queryParams.f"            placeholder = "请输入第六参数"            clearable            size = "small"            @ keyup.enter.native = "handleQuery"          />        </ el-form-item >        < el-form-item label = "参数输入" prop = "g" >          < el-input            v-model = "queryParams.g"            placeholder = "请输入第七参数"            clearable            size = "small"            @ keyup.enter.native = "handleQuery"          />        </ el-form-item >        < el-form-item label = "参数输入" prop = "h" >          < el-input            v-model = "queryParams.h"            placeholder = "请输入第八参数"            clearable            size = "small"            @ keyup.enter.native = "handleQuery"          />        </ el-form-item >        < el-form-item label = "参数输入" prop = "abc" >          < el-input            v-model = "queryParams.abc"            placeholder = "请输入第九参数"            clearable            size = "small"            @ keyup.enter.native = "handleQuery"          />        </ el-form-item >        <!-- <el-form-item label="录入人" prop="userName">          <el-input            v-model="queryParams.userName"            placeholder="请输入办理人名字"            clearable            size="small"            @keyup.enter.native="handleQuery"          />        </el-form-item>        <el-form-item label="操作日期" prop="recordDate">          <el-date-picker clearable size="small"            v-model="queryParams.recordDate"            type="date"            value-format="yyyy-MM-dd"            placeholder="选择操作日期">          </el-date-picker>        </el-form-item> -->        < el-form-item >          < el-button            type = "primary"            icon = "el-icon-top-right"            size = "mini"            @ click = "handleQuery"            >传值/执行</ el-button          >          < el-button icon = "el-icon-refresh" size = "mini" @ click = "resetQuery"            >重置</ el-button          >          < el-button            type = "primary"            icon = "el-icon-search"            size = "mini"            @ click = "returnQuery"            >返回/查询</ el-button          >        </ el-form-item >      </ el-form >        < el-row :gutter = "10" class = "mb8" > < right-toolbar          :showSearch.sync = "showSearch"          @ queryTable = "getProcList"        ></ right-toolbar >      </ el-row >        < el-table        v-loading = "loading"        :data = "returnprocList"        @ selection-change = "handleSelectionChange"      >        <!-- <el-table-column type="selection" width="55" align="center" /> -->        < el-table-column          label = "序号"          align = "center"          prop = ""          type = "index"          width = "60"        />        < el-table-column label = "记录id" align = "center" prop = "Id" />        < el-table-column          label = "第一参数趟次"          align = "center"          prop = "a"          width = "200"        />        < el-table-column label = "第二参数趟次" align = "center" prop = "b" />        < el-table-column label = "第三参数趟次" align = "center" prop = "c" />        < el-table-column label = "第四参数趟次" align = "center" prop = "d" />   < el-table-column label = "第五参数趟次" align = "center" prop = "e" />        < el-table-column label = "第六参数趟次" align = "center" prop = "f" />        < el-table-column label = "第七参数趟次" align = "center" prop = "g" />        < el-table-column label = "第八参数趟次" align = "center" prop = "h" />        < el-table-column label = "趟次总金额" align = "center" prop = "abc" /> <!-- 刷新查询 -->      < pagination        v-show="total > 0"        :total="total"        :page.sync="queryparameters.pageNum"        :limit.sync="queryparameters.pageSize"        @pagination="getProcList"      /> </ template >

 端js代码：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115

<script> import {    listProc,    getProc,    delProc,    addProc,    updateProc,    exportProc,    returnProc, } from "@/api/stock/proc" ;   export default {    name: "Proc" ,    dicts: [ "record_type" ],    data() {      return {        // 遮罩层        loading: true ,        // 显示搜索条件        showSearch: true ,        // 总条数        total: 0,        // 存储过程表格数据        procList: [],        returnprocList: [],          // 查询参数        queryParams: {            a: null ,          b: null ,          c: null ,          d: null ,          e: null ,          f: null ,          g: null ,          h: null ,          abc: null ,          //C: null,        },        queryparameters:{          pageNum: 1,          pageSize: 10,          recordType: 1,          },        };    },    created() {      this .getList();      this .getProcList();      },    methods: {      /** 查询执行数据 */      getList() {        this .loading = true ;        listProc( this .queryParams).then((response) => {          this .procList = response.rows;          this .total = response.total;          this .loading = false ;        });      },       /** 查询返回列表 */      getProcList() {        this .loading = true ;        returnProc( this .queryparameters).then((response) => {          this .returnprocList = response.rows;          this .total = response.total;          this .loading = false ;        });      },          // 表单重置      reset() {        this .form = {          Id: null ,          recordType: null ,          a: null ,          b: null ,          c: null ,          d: null ,          e: null ,          f: null ,          g: null ,          h: null ,          abc: null ,          t: null ,          tc: null ,          min1: null ,          };        },      /** 搜索按钮操作 */      handleQuery() {        this .queryParams.pageNum = 1;        this .getList();      },       /** 返回刷新按钮操作 */       returnQuery() {        this .queryparameters.pageNum = 1;        this .getProcList();      },        /** 重置按钮操作 */      resetQuery() {        this .resetForm( "queryForm" );        this .handleQuery();      },   }; </script>

 接口代码：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

import request from '@/utils/request'   // 查询列表 export function listProc(query) {      return request({        url: '/stock/proc/list' ,        method: 'get' ,        params: query      })    }   // 查询 export function returnProc(query) {    return request({      url: '/stock/proc/query' ,      method: 'get' ,      parameters: query    }) }

Java代码：

controller:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

@RestController @RequestMapping ( "/stock/proc" ) public class StockProcController extends BaseController {      @Autowired      private IStockProcService stockProcService;        /**       * 查询列表       */      //@PreAuthorize("@ss.hasPermi('stock:proc:list')")      @GetMapping ( "/list" )      public TableDataInfo list(StockProc stockProc)      { startPage();              List<StockProc> paramlist = stockProcService.selectStockProcParamList(stockProc);              //return getDataTable(paramlist);          return null ;        } /**       * 获取外出申请详细信息       */     @PreAuthorize ( "@ss.hasPermi('stock:Proc:query')" )      @GetMapping ( "/query" )      public TableDataInfo getInfo(StockProc stockProc)      {          startPage();          List<StockProc> list = stockProcService.selectStockProcList(stockProc);            return getDataTable(list);      } }

实体层：

dao/dto

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162

package com.ruoyi.stock.domain;   import com.fasterxml.jackson.annotation.JsonFormat; import com.ruoyi.common.annotation.Excel; import com.ruoyi.common.core.domain.BaseEntity; import org.springframework.format.annotation.DateTimeFormat; import java.util.Date; /**   * 存储过程页面   *   */ public class StockProc extends BaseEntity {      private static final long serialVersionUID = 1L;        @Excel (name = "序号" ) //    @NotBlank(message = "该字段不能为空")      private int id;        /** 第一编号 */      @Excel (name = "第一参数趟次" )      private int a;        /** 第一编号 */      @Excel (name = "第二参数趟次" )      private int b ;      /** 第一编号 */      @Excel (name = "第三参数趟次" )      private int c;      /** 第一编号 */      @Excel (name = "第四参数趟次" )      private int d;      /** 第一编号 */      @Excel (name = "第五参数趟次" )      private int e;      /** 第一编号 */      @Excel (name = "第六参数趟次" )      private int f;      /** 第一编号 */      @Excel (name = "第七参数趟次" )      private int g;      /** 第一编号 */      @Excel (name = "第八参数趟次" )      private int h;      /** 第一编号 */      @Excel (name = "趟次总金额" )      private int abc;      /** 第一编号 */      @Excel (name = "趟" )      private int t;      /** 第一编号 */      @Excel (name = "趟次" )      private int tc;      /** 第一编号 */      @Excel (name = "小计" )      private int min1;        public int getId() {          return id;      }        public void setId( int id) {          this .id = id;      }        public int getA() {          return a;      }        public void setA( int a) {          this .a = a;      }        public int getB() {          return b;      }      public void setB( int b) {          this .b = b;      }        public int getC() {          return c;      }        public void setC( int c) {          this .c = c;      }        public int getD() {          return d;      }        public void setD( int d) {          this .d = d;      }        public int getE() {          return e;      }      public void setE( int e) {          this .e = e;      }      public int getF() {          return f;      }      public void setF( int f) {          this .f = f;      }      public int getG() {          return g;      }      public void setG( int g) {          this .g = g;      }      public int getH() {          return h;      }      public void setH( int h) {          this .h = h;      }      public int getAbc() {          return abc;      }      public void setAbc( int abc) {          this .abc = abc;      }      public int getT() {          return t;      }      public void setT( int t) {          this .t = t;      }      public int getTc() {          return tc;      }      public void setTc( int tc) {          this .tc = tc;      }      public int getMin1() {          return min1;      }      public void setMin1( int min1) {          this .min1 = min1;      }      @Override      public String toString() {          return "StockProc{" +                  "id=" + id +                  ", a=" + a +                  ", b=" + b +                  ", c=" + c +                  ", d=" + d +                  ", e=" + e +                  ", f=" + f +                  ", g=" + g +                  ", h=" + h +                  ", abc=" + abc +                  ", t=" + t +                  ", tc=" + tc +                  ", min1=" + min1 +                  '}' ;      } }

server层：

?

1 2 3 4 5 6 7 8 9

public interface IStockProcService {      /**       * 查询列表       * @return 记录集合       */      public List<StockProc> selectStockProcList(StockProc stockProc);      public List<StockProc> selectStockProcParamList(StockProc stockProc); }

Impl代码：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

@Service public class StockProcImpl implements IStockProcService {      @Autowired      private StockProcMapper stockProcMapper;      /**       *       * @param 列表记录       * @return       */      @Override      public List<StockProc> selectStockProcList(StockProc stockProc) {          //return stockProcMapper.selectStockProcList(stockProc);          return stockProcMapper.selectStockProcList(stockProc);      }      @Override      public List<StockProc> selectStockProcParamList(StockProc stockProc) {          return stockProcMapper.selectStockProcParamList(stockProc);          //return null;      } }

mapper代码：

?

1 2 3 4 5 6 7 8 9 10 11 12

public interface StockProcMapper {      /**       * 查询列表       *       * @param stockProc 记录       * @return 集合       */      public List<StockProc> selectStockProcList(StockProc stockProc);        public List<StockProc> selectStockProcParamList(StockProc stockProc); }

mybatis的xml文件：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

<? xml version = "1.0" encoding = "UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"          "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> < mapper namespace = "com.ruoyi.stock.mapper.StockProcMapper" >      < resultMap type = "StockProc" id = "StockProcResult" >          < result property = "Id"    column = "id" />          < result property = "a"    column = "a" />          < result property = "b"    column = "b" />          < result property = "c"    column = "c" />          < result property = "d"    column = "d" />          < result property = "e"    column = "e" />          < result property = "f"    column = "f" />          < result property = "g"    column = "g" />          < result property = "h"    column = "h" />          < result property = "abc"    column = "abc" />          < result property = "t"    column = "t" />          < result property = "tc"    column = "tc" />         < result property = "min1"    column = " min1" />      </ resultMap >      < sql id = "selectStockProcVo" >          SELECT             a,b,c,d,e,f,g,h,abc,t,tc,min1          FROM              a_tmp      </ sql > <!--使用数据库存储过程查询-->      < select id = "selectStockProcParamList" parameterType = "StockProc" resultMap = "StockProcResult" statementType = "CALLABLE" >          call bill_a_forbusiness(#{a},#{b},#{c},#{d},#{e},#{f},#{g},#{h},#{abc})      </ select >      <!--无参数查询-->      < select id = "selectStockProcList" parameterType = "StockProc" resultMap = "StockProcResult" >          < include refid = "selectStockProcVo" />      </ select > </ mapper >

最后便可以通过页面输入框的参数进行调用存储过程执行，然后点击查询返回结果列表。

到此这篇关于前端传参数进行Mybatis调用mysql存储过程执行返回值详解的文章就介绍到这了,更多相关Mybatis调用mysql内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.csdn.net/qq_45637260/article/details/126288734