亲宝软件园·资讯

展开

Mybatis调用mysql

梦远星帆 人气:0

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

方法一：

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>

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

加载全部内容
相关教程
猜你喜欢
用户评论