前端传参数进行Mybatis调用mysql存储过程执行返回值实例分析
这篇“前端传参数进行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存储过程执行返回值实例分析”这篇文章的内容，相信大家都有了一定的了解，希望小编分享的内容对大家有帮助，若想了解更多相关的知识内容，请关注蜗牛博客行业资讯频道。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：niceseo99@gmail.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
版权声明：如无特殊标注，文章均为本站原创，转载时请以链接形式注明文章出处。
评论