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

    查询数据库中的存储过程

    方法一：

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

    方法二：

             show procedure status;

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

     

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

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

    存储过程调用方式：

    CALL Pro_Get_CO2('2018','','','');

    CALL Pro_Get_EnergyData('2017');

    CALL Pro_Get_Carbon_OrgType('2014');

    CALL 存储过程名(参数);
     

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

    show create procedure proc_name;
    show create function func_name;

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

    前端代码：

    1. <template>
    2.   <div class="app-container">
    3.     <el-form
    4.       :model="queryParams"
    5.       ref="queryForm"
    6.       :inline="true"
    7.       v-show="showSearch"
    8.       label-width="68px"
    9.     >
    10.       <el-form-item label="参数输入" prop="a">
    11.         <el-input
    12.           v-model="queryParams.a"
    13.           placeholder="请输入第一参数"
    14.           clearable
    15.           size="small"
    16.           @keyup.enter.native="handleQuery"
    17.         />
    18.       el-form-item>
    19.       <el-form-item label="参数输入" prop="b">
    20.         <el-input
    21.           v-model="queryParams.b"
    22.           placeholder="请输入第二参数"
    23.           clearable
    24.           size="small"
    25.           @keyup.enter.native="handleQuery"
    26.         />
    27.       el-form-item>
    28.       <el-form-item label="参数输入" prop="c">
    29.         <el-input
    30.           v-model="queryParams.c"
    31.           placeholder="请输入第三参数"
    32.           clearable
    33.           size="small"
    34.           @keyup.enter.native="handleQuery"
    35.         />
    36.       el-form-item>
    37.       <el-form-item label="参数输入" prop="d">
    38.         <el-input
    39.           v-model="queryParams.d"
    40.           placeholder="请输入第四参数"
    41.           clearable
    42.           size="small"
    43.           @keyup.enter.native="handleQuery"
    44.         />
    45.       el-form-item>
    46.       <el-form-item label="参数输入" prop="e">
    47.         <el-input
    48.           v-model="queryParams.e"
    49.           placeholder="请输入第五参数"
    50.           clearable
    51.           size="small"
    52.           @keyup.enter.native="handleQuery"
    53.         />
    54.       el-form-item>
    55.       <el-form-item label="参数输入" prop="f">
    56.         <el-input
    57.           v-model="queryParams.f"
    58.           placeholder="请输入第六参数"
    59.           clearable
    60.           size="small"
    61.           @keyup.enter.native="handleQuery"
    62.         />
    63.       el-form-item>
    64.       <el-form-item label="参数输入" prop="g">
    65.         <el-input
    66.           v-model="queryParams.g"
    67.           placeholder="请输入第七参数"
    68.           clearable
    69.           size="small"
    70.           @keyup.enter.native="handleQuery"
    71.         />
    72.       el-form-item>
    73.       <el-form-item label="参数输入" prop="h">
    74.         <el-input
    75.           v-model="queryParams.h"
    76.           placeholder="请输入第八参数"
    77.           clearable
    78.           size="small"
    79.           @keyup.enter.native="handleQuery"
    80.         />
    81.       el-form-item>
    82.       <el-form-item label="参数输入" prop="abc">
    83.         <el-input
    84.           v-model="queryParams.abc"
    85.           placeholder="请输入第九参数"
    86.           clearable
    87.           size="small"
    88.           @keyup.enter.native="handleQuery"
    89.         />
    90.       el-form-item>
    91.       
    92.  
    93.       <el-form-item>
    94.         <el-button
    95.           type="primary"
    96.           icon="el-icon-top-right"
    97.           size="mini"
    98.           @click="handleQuery"
    99.           >传值/执行
    100.         >
    101.         <el-button icon="el-icon-refresh" size="mini" @click="resetQuery"
    102.           >重置
    103.         >
    104.         
    105.         <el-button
    106.           type="primary"
    107.           icon="el-icon-search"
    108.           size="mini"
    109.           @click="returnQuery"
    110.           >返回/查询
    111.         >
    112.       el-form-item>
    113.     el-form>
    114.  
    115.     <el-row :gutter="10" class="mb8">
    116. <right-toolbar
    117.         :showSearch.sync="showSearch"
    118.         @queryTable="getProcList"
    119.       >right-toolbar>
    120.     el-row>
    121.  
    122.     <el-table
    123.       v-loading="loading"
    124.       :data="returnprocList"
    125.       @selection-change="handleSelectionChange"
    126.     >
    127.       
    128.       <el-table-column
    129.         label="序号"
    130.         align="center"
    131.         prop=""
    132.         type="index"
    133.         width="60"
    134.       />
    135.       <el-table-column label="记录id" align="center" prop="Id" />
    136.       <el-table-column
    137.         label="第一参数趟次"
    138.         align="center"
    139.         prop="a"
    140.         width="200"
    141.       />
    142.       <el-table-column label="第二参数趟次" align="center" prop="b" />
    143.       <el-table-column label="第三参数趟次" align="center" prop="c" />
    144.       <el-table-column label="第四参数趟次" align="center" prop="d" />
    145.  <el-table-column label="第五参数趟次" align="center" prop="e" />
    146.       <el-table-column label="第六参数趟次" align="center" prop="f" />
    147.       <el-table-column label="第七参数趟次" align="center" prop="g" />
    148.       <el-table-column label="第八参数趟次" align="center" prop="h" />
    149.       <el-table-column label="趟次总金额" align="center" prop="abc" />
    151.     <pagination
    152.       v-show="total > 0"
    153.       :total="total"
    154.       :page.sync="queryparameters.pageNum"
    155.       :limit.sync="queryparameters.pageSize"
    156.       @pagination="getProcList"
    157.     />
    158. template>

     端js代码：

     接口代码：

     

    1. import request from '@/utils/request'
    2.  
    3. // 查询列表
    4. export function listProc(query) {
    5.     return request({
    6.       url: '/stock/proc/list',
    7.       method: 'get',
    8.       params: query
    9.     })
    10.   }
    11.  
    12.  
    13.  
    14. // 查询
    15. export function returnProc(query) {
    16.   return request({
    17.     url: '/stock/proc/query',
    18.     method: 'get',
    19.     parameters: query
    20.   })
    21. }

    Java代码：

    controller:

    1. @RestController
    2. @RequestMapping("/stock/proc")
    3. public class StockProcController extends BaseController
    4. {
    5.     @Autowired
    6.     private IStockProcService stockProcService;
    7.  
    8.     /**
    9.      * 查询列表
    10.      */
    11.     //@PreAuthorize("@ss.hasPermi('stock:proc:list')")
    12.     @GetMapping("/list")
    13.     public TableDataInfo list(StockProc stockProc)
    14.     { 
    15. startPage();
    16.             List paramlist = stockProcService.selectStockProcParamList(stockProc);
    17.             //return getDataTable(paramlist);
    18.         return null;
    19.  
    20.     }
    21. /**
    22.      * 获取外出申请详细信息
    23.      */
    24.    @PreAuthorize("@ss.hasPermi('stock:Proc:query')")
    25.     @GetMapping("/query")
    26.     public TableDataInfo getInfo(StockProc stockProc)
    27.     {
    28.         startPage();
    29.         List list = stockProcService.selectStockProcList(stockProc);
    30.         
    31.         return getDataTable(list);
    32.     }
    33. }

    实体层：

    dao/dto

    1. package com.ruoyi.stock.domain;
    2.  
    3. import com.fasterxml.jackson.annotation.JsonFormat;
    4. import com.ruoyi.common.annotation.Excel;
    5. import com.ruoyi.common.core.domain.BaseEntity;
    6. import org.springframework.format.annotation.DateTimeFormat;
    7.  
    8. import java.util.Date;
    9.  
    10. /**
    11.  * 存储过程页面
    12.  *
    13.  */
    14. public class StockProc extends BaseEntity {
    15.     private static final long serialVersionUID = 1L;
    16.  
    17.     @Excel(name = "序号")
    18. //    @NotBlank(message = "该字段不能为空")
    19.     private int id;
    20.  
    21.     /** 第一编号 */
    22.     @Excel(name = "第一参数趟次")
    23.     private int a;
    24.  
    25.     /** 第一编号 */
    26.     @Excel(name = "第二参数趟次")
    27.     private int b ;
    28.     /** 第一编号 */
    29.     @Excel(name = "第三参数趟次")
    30.     private int c;
    31.     /** 第一编号 */
    32.     @Excel(name = "第四参数趟次")
    33.     private int d;
    34.     /** 第一编号 */
    35.     @Excel(name = "第五参数趟次")
    36.     private int e;
    37.     /** 第一编号 */
    38.     @Excel(name = "第六参数趟次")
    39.     private int f;
    40.     /** 第一编号 */
    41.     @Excel(name = "第七参数趟次")
    42.     private int g;
    43.     /** 第一编号 */
    44.     @Excel(name = "第八参数趟次")
    45.     private int h;
    46.     /** 第一编号 */
    47.     @Excel(name = "趟次总金额")
    48.     private int abc;
    49.     /** 第一编号 */
    50.     @Excel(name = "趟")
    51.     private int t;
    52.     /** 第一编号 */
    53.     @Excel(name = "趟次")
    54.     private int tc;
    55.     /** 第一编号 */
    56.     @Excel(name = "小计")
    57.     private int min1;
    58.  
    59.     public int getId() {
    60.         return id;
    61.     }
    62.  
    63.     public void setId(int id) {
    64.         this.id = id;
    65.     }
    66.  
    67.     public int getA() {
    68.         return a;
    69.     }
    70.  
    71.     public void setA(int a) {
    72.         this.a = a;
    73.     }
    74.  
    75.     public int getB() {
    76.         return b;
    77.     }
    78.  
    79.     public void setB(int b) {
    80.         this.b = b;
    81.     }
    82.  
    83.     public int getC() {
    84.         return c;
    85.     }
    86.  
    87.     public void setC(int c) {
    88.         this.c = c;
    89.     }
    90.  
    91.     public int getD() {
    92.         return d;
    93.     }
    94.  
    95.     public void setD(int d) {
    96.         this.d = d;
    97.     }
    98.  
    99.     public int getE() {
    100.         return e;
    101.     }
    102.  
    103.     public void setE(int e) {
    104.         this.e = e;
    105.     }
    106.  
    107.     public int getF() {
    108.         return f;
    109.     }
    110.  
    111.     public void setF(int f) {
    112.         this.f = f;
    113.     }
    114.  
    115.     public int getG() {
    116.         return g;
    117.     }
    118.  
    119.     public void setG(int g) {
    120.         this.g = g;
    121.     }
    122.  
    123.     public int getH() {
    124.         return h;
    125.     }
    126.  
    127.     public void setH(int h) {
    128.         this.h = h;
    129.     }
    130.  
    131.     public int getAbc() {
    132.         return abc;
    133.     }
    134.  
    135.     public void setAbc(int abc) {
    136.         this.abc = abc;
    137.     }
    138.  
    139.     public int getT() {
    140.         return t;
    141.     }
    142.  
    143.     public void setT(int t) {
    144.         this.t = t;
    145.     }
    146.  
    147.     public int getTc() {
    148.         return tc;
    149.     }
    150.  
    151.     public void setTc(int tc) {
    152.         this.tc = tc;
    153.     }
    154.  
    155.     public int getMin1() {
    156.         return min1;
    157.     }
    158.  
    159.     public void setMin1(int min1) {
    160.         this.min1 = min1;
    161.     }
    162.  
    163.     @Override
    164.     public String toString() {
    165.         return "StockProc{" +
    166.                 "id=" + id +
    167.                 ", a=" + a +
    168.                 ", b=" + b +
    169.                 ", c=" + c +
    170.                 ", d=" + d +
    171.                 ", e=" + e +
    172.                 ", f=" + f +
    173.                 ", g=" + g +
    174.                 ", h=" + h +
    175.                 ", abc=" + abc +
    176.                 ", t=" + t +
    177.                 ", tc=" + tc +
    178.                 ", min1=" + min1 +
    179.                 '}';
    180.     }
    181.  
    182.  
    183. }

    server层：

    1. public interface IStockProcService
    2. {
    3.  
    4.  
    5.     /**
    6.      * 查询列表
    8.      * @return 记录集合
    9.      */
    10.     public List selectStockProcList(StockProc stockProc);
    11.  
    12.     public List selectStockProcParamList(StockProc stockProc);
    13. }

    Impl代码：

    1. @Service
    2. public class StockProcImpl implements IStockProcService {
    3.     @Autowired
    4.     private StockProcMapper stockProcMapper;
    5.  
    6.     /**
    7.      *
    8.      * @param 列表记录
    9.      * @return
    10.      */
    11.     @Override
    12.     public List selectStockProcList(StockProc stockProc) {
    13.         //return stockProcMapper.selectStockProcList(stockProc);
    14.         return stockProcMapper.selectStockProcList(stockProc);
    15.     }
    16.  
    17.     @Override
    18.     public List selectStockProcParamList(StockProc stockProc) {
    19.         return stockProcMapper.selectStockProcParamList(stockProc);
    20.         //return null;
    21.     }
    22. }

    mapper代码：

    1. public interface StockProcMapper
    2. {
    3.  
    4.  
    5.     /**
    6.      * 查询列表
    7.      * 
    8.      * @param stockProc 记录
    9.      * @return 集合
    10.      */
    11.     public List selectStockProcList(StockProc stockProc);
    12.  
    13.     public List selectStockProcParamList(StockProc stockProc);
    14. }

    mybatis的xml文件：

    2. mapper
    3. PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    5. <mapper namespace="com.ruoyi.stock.mapper.StockProcMapper">
    6.  
    7.     <resultMap type="StockProc" id="StockProcResult">
    8.         <result property="Id"    column="id"/>
    9.         <result property="a"    column="a"/>
    10.         <result property="b"    column="b"/>
    11.         <result property="c"    column="c"/>
    12.         <result property="d"    column="d"/>
    13.         <result property="e"    column="e"/>
    14.         <result property="f"    column="f"/>
    15.         <result property="g"    column="g"/>
    16.         <result property="h"    column="h"/>
    17.         <result property="abc"    column="abc"/>
    18.         <result property="t"    column="t"/>
    19.         <result property="tc"    column="tc"/>
    20.        <result property="min1"    column=" min1"/>
    21.  
    22.     resultMap>
    23.     <sql id="selectStockProcVo">
    24.         SELECT
    25.            a,b,c,d,e,f,g,h,abc,t,tc,min1
    26.         FROM
    27.             a_tmp
    28.  
    29.     sql>
    30.  
    32.  
    33.     <select id="selectStockProcParamList" parameterType="StockProc" resultMap="StockProcResult" statementType="CALLABLE">
    34.  
    35.         call bill_a_forbusiness(#{a},#{b},#{c},#{d},#{e},#{f},#{g},#{h},#{abc})
    36.  
    37.     select>
    38.     
    39.     <select id="selectStockProcList" parameterType="StockProc" resultMap="StockProcResult">
    40.         <include refid="selectStockProcVo"/>
    41.  
    42.     select>
    43.  
    44. mapper>

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

  • 相关阅读:
    Java基础-static关键字
    grafana api创建dashboard 记录
    成人自考-英语二-连词
    Git分支与Git标签的介绍及其场景应用
    面试八股 | 计算机网络 | TCP三次握手
    2024年腾讯云优惠政策_腾讯云TOP10优惠活动
    Session会话机制的应用(用户登录)
    KF、EKF、IEKF、UKF卡尔曼滤波器
    配置高级 --------打包与运行---配置高级---多环境开发---日志
    进度条小程序
  • 原文地址：https://blog.csdn.net/qq_45637260/article/details/126288734