您好,登录后才能下订单哦!
这篇“前端传参数进行Mybatis调用mysql存储过程执行返回值实例分析”文章的知识点大部分人都不太理解,所以小编给大家总结了以下内容,内容详细,步骤清晰,具有一定的借鉴价值,希望大家阅读完这篇文章能有所收获,下面我们一起来看看这篇“前端传参数进行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;
因为这个没有返回值所以需要先传参调用执行,再查询
前端代码:
<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代码:
<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>
接口代码:
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:
@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
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层:
public interface IStockProcService { /** * 查询列表 * @return 记录集合 */ public List<StockProc> selectStockProcList(StockProc stockProc); public List<StockProc> selectStockProcParamList(StockProc stockProc); }
Impl代码:
@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代码:
public interface StockProcMapper { /** * 查询列表 * * @param stockProc 记录 * @return 集合 */ public List<StockProc> selectStockProcList(StockProc stockProc); public List<StockProc> selectStockProcParamList(StockProc stockProc); }
mybatis的xml文件:
<?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存储过程执行返回值实例分析”这篇文章的内容,相信大家都有了一定的了解,希望小编分享的内容对大家有帮助,若想了解更多相关的知识内容,请关注亿速云行业资讯频道。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。