当前位置： 首页 > article >正文

spring boot学习第十二篇:mybatis框架中调用存储过程控制事务性

article 2024/11/14 20:18:02

1、MySQL方面，已经准备好了存储过程，参考：MYSQL存储过程（含入参、出参）-CSDN博客

2、pom.xml文件内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.hmblogs</groupId>
    <artifactId>hmblogs</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>hmblogs</name>
    <description>hmblogs</description>
    <properties>
        <java.version>8</java.version>
        <druid.version>1.2.8</druid.version>
        <log4jdbc.version>1.16</log4jdbc.version>
        <es.version>7.9.2</es.version>
    </properties>
    <dependencies>
        <!-- druid数据源驱动 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- mybatis -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!--Mysql依赖包-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!--lombok插件-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!--监控sql日志-->
        <dependency>
            <groupId>org.bgee.log4jdbc-log4j2</groupId>
            <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
            <version>${log4jdbc.version}</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.9</version>
        </dependency>

        <dependency>
            <groupId>redis.clients</groupId>
            <artifactId>jedis</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.kafka</groupId>
            <artifactId>kafka-clients</artifactId>

        </dependency>

        <dependency>
            <groupId>org.springframework.kafka</groupId>
            <artifactId>spring-kafka</artifactId>

        </dependency>

        <!-- high client-->
        <dependency>
            <groupId>org.elasticsearch.client</groupId>
            <artifactId>elasticsearch-rest-high-level-client</artifactId>
            <version>${es.version}</version>
            <exclusions>
                <exclusion>
                    <groupId>org.elasticsearch</groupId>
                    <artifactId>elasticsearch</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.elasticsearch.client</groupId>
                    <artifactId>elasticsearch-rest-client</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!-- rest-high-level-client 依赖如下2个jar -->
        <dependency>
            <groupId>org.elasticsearch</groupId>
            <artifactId>elasticsearch</artifactId>
            <version>${es.version}</version>
        </dependency>
        <dependency>
            <groupId>org.elasticsearch.client</groupId>
            <artifactId>elasticsearch-rest-client</artifactId>
            <version>${es.version}</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-mongodb</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.security.oauth.boot</groupId>
            <artifactId>spring-security-oauth2-autoconfigure</artifactId>
            <version>2.4.0</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

3、application.yml文件内容如下：

server:
  port: 8081
  servlet.context-path: /

#配置数据源
spring:
  datasource:
    druid:
      db-type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: net.sf.log4jdbc.sql.jdbcapi.DriverSpy
      url: jdbc:log4jdbc:mysql://${DB_HOST:localhost}:${DB_PORT:3306}/${DB_NAME:eladmin}?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false
      username: ${DB_USER:root}
      password: ${DB_PWD:demo}
  redis:
    host: localhost
    port: 6379
    password: demo
    database: 10
  data:
    mongodb:
      host: 43.138.0.199
      port: 27017
      username: hmblogs
      password: demo
      database: hmblogs
      authentication-database: admin

es:
  host: 43.138.0.199
  port: 9200
  scheme: http
  user: elastic
  password: demo

4、BackendApplication文件内容如下：

package com.hmblogs.backend;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class BackendApplication {

    public static void main(String[] args) {
        SpringApplication.run(BackendApplication.class, args);
    }

}

5、StockMapper.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.hmblogs.backend.dao.StockMapper">
    <resultMap id="BaseResultMap" type="com.hmblogs.backend.entity.Stock">
        <id column="id" property="id"/>
        <result column="quantity" property="quantity"/>
    </resultMap>

    <sql id="BaseResultMap">
        id, quantity
    </sql>

    <select id="findAll" resultMap="BaseResultMap">
        select
        <include refid="BaseResultMap"/>
        from t_stock

    </select>

    <select id="findById" resultMap="BaseResultMap"  parameterType="com.hmblogs.backend.entity.Stock">
        select
        <include refid="BaseResultMap"/>
        from t_stock
        where id=#{id}
    </select>

    <update id="updateStockById" parameterType="com.hmblogs.backend.entity.Stock">
        update t_stock set quantity=quantity-1 where id=#{id}
    </update>

    <select id="invokeStockProdudure" statementType="CALLABLE" parameterType="java.util.Map" resultType="java.util.Map">
        CALL stock_produdure(
            #{id1,mode=IN,jdbcType=INTEGER},
            #{quantity1,mode=IN,jdbcType=INTEGER},
            #{id2,mode=IN,jdbcType=INTEGER},
            #{quantity2,mode=IN,jdbcType=INTEGER},
            #{result,mode=OUT,jdbcType=INTEGER}
            );
    </select>

</mapper>

6、StockMapper文件内容如下：

package com.hmblogs.backend.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hmblogs.backend.entity.Stock;
import org.apache.ibatis.annotations.Mapper;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Mapper
public interface StockMapper extends BaseMapper<Stock> {
    List<Stock> findAll();

    Stock findById(Stock stock);

    Integer updateStockById(Stock stock);

    Map<String,Integer> invokeStockProdudure(Map<String,String> param);
}

7、Stock文件内容如下：

package com.hmblogs.backend.entity;


import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName("t_stock")
public class Stock {

    @TableId(value="id", type = IdType.AUTO)
    private Integer id;

    private Integer quantity;

}

8、StockServiceImpl代码如下：

package com.hmblogs.backend.service;

import com.hmblogs.backend.dao.StockMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.Map;

@Slf4j
@Service
public class StockServiceImpl {

    @Autowired
    private StockMapper stockMapper;

    public void operateProcedure(){
        HashMap<String,String> param = new HashMap<>();
        param.put("id1","7000");
        param.put("quantity1","15");
        param.put("id2","7001");
        param.put("quantity2","18");
        Map<String,Integer> result = stockMapper.invokeStockProdudure(param);
        log.info("result:"+result);
    }
}

9、测试类ProcedureTest内容如下：

package com.hmblogs.backend.util;

import com.hmblogs.backend.service.StockServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.HashMap;
import java.util.Map;

@Slf4j
//@Component
@RunWith(SpringRunner.class)
@SpringBootTest
public class ProcedureTest {

    @Autowired
    private StockServiceImpl stockServiceImpl;

    @Test
    public void testProcedure() {
        // 请求参数
        stockServiceImpl.operateProcedure();
    }
}

执行该方法，

查看数据：

10、StockServiceImpl内代码改动一下，

执行测试方法，结果如下：

查看数据，没有id为8000的记录


http://www.kler.cn/a/233758.html

相关文章：

  • 【mysql】使用宝塔面板在云服务器上安装MySQL数据库并实现远程连接
  • 使用pytest+openpyxl做接口自动化遇到的问题
  • Java学习--网络编程
  • 信捷 PLC C语言 POU 指示灯交替灭0.5秒亮0.5秒(保持型定时器)
  • 期权懂|期权新手入门教学：期权合约有哪些要素？
  • PHP爬虫快速获取京东商品详情（代码示例）
  • 六、滚动条操作——调整图像亮度
  • 《Docker极简教程》--Docker环境的搭建--在Linux上搭建Docker环境
  • 架设游戏服务器租用价格？腾讯云和阿里云价格对比
  • 跟着cherno手搓游戏引擎【23】项目维护、2D引擎之前的一些准备
  • 小程序配置服务器域名流程指南
  • 机器学习2---逻辑回归(基础准备）
  • 新概念英语第二册（62）
  • vim常用命令以及配置文件
  • 物联网中基于WIFI的室内温度检测系统设计
  • Blender 的重拓扑功能中的参数，
  • c++中的模板（1） -- 什么是模板
  • Kotlin和Java 单例模式
  • 【Linux】Shell编程
  • 【ASP.NET Core 基础知识】--部署和维护--日志记录和错误处理
  • C++中类的6个默认成员函数【构造函数】 【析构函数】
  • Java串口通信技术探究3：RXTX库线程 优化系统性能的SerialPortEventListener类
  • k8s-常用工作负载控制器(更高级管理Pod)
  • 基于 SpringBoot 和 Vue.js 的权限管理系统部署教程
  • 备战蓝桥杯---动态规划之背包问题引入
  • 基于opencv-python模板匹配的银行卡号识别（附源码）