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

最新推荐文章于 2024-03-16 06:18:02 发布

veminhe

最新推荐文章于 2024-03-16 06:18:02 发布

阅读量1.3k

点赞数 5

分类专栏： Spring Boot 文章标签： spring boot mybatis

本文链接：https://blog.csdn.net/heming20122012/article/details/136077639

版权

Spring Boot 专栏收录该内容

24 篇文章 1 订阅

订阅专栏

本文介绍了在SpringBoot项目中配置MySQL存储过程、Maven依赖管理和使用mybatis、Druid数据源、Elasticsearch客户端以及Kafka集成。同时展示了如何通过Lombok和JDBC进行SQL日志监控和存储过程调用的测试。

摘要由CSDN通过智能技术生成

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();
    }
}

执行该方法，