基于springboot+mybatis调用MySQL存储过程

前言：

很多公司一般不使用JAVA写存储过程，因为写法较为复杂，不方便后期维护。
不排除一些公司项目会使用。
如果索引优化已经达到很好的性能，不建议使用。

以下示例供学习参考：
demo源码：https://gitee.com/chenwanye/spring-boot-demo-procedure

1.环境依赖

工具：JDK1.8、IDEA2023、maven3.5
依赖：
springboot 2.4.5、druid 1.1.16
mybatis-spring-boot-starter 2.1.4
mysql-connector-java 8.0.23
数据库：mysql 5.7

2.maven 依赖

<?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>
    <groupId>com.gitee</groupId>
    <artifactId>SpringBootDemo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>SpringBootDemo</name>
    <description>SpringBootDemo</description>
    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-boot.version>2.4.5</spring-boot.version>
    </properties>


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

        <!--单元测试 spring-boot-starter-test+junit -->
        <!--@Test需要-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--测试类 @RunWith(SpringRunner.class)需要-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <!--<scope>test</scope>-->
        </dependency>

        <!--数据库+数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.16</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!--controller控制层注解-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <scope>compile</scope>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>

    </dependencies>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>${spring-boot.version}</version>
                <configuration>
                    <mainClass>com.gitee.springbootdemo.SpringBootDemoApplication</mainClass>
                    <skip>true</skip>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>

        <resources>
            <resource>
                <directory>src/main/resources/</directory>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
    </build>
</project>

3.实体类model

package com.gitee.springbootdemo.model;
import java.io.Serializable;

public class Employee implements Serializable {
    private Integer id;  
    private String name;

    public Employee() {
    }

    public Employee(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

4.控制层controller

EmployeeController

package com.gitee.springbootdemo.controller;
import com.gitee.springbootdemo.service.EmployeeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/employee")
public class EmployeeController {

    @Autowired
    private  EmployeeService employeeService;
   /* private final EmployeeService employeeService;
    @Autowired
    public EmployeeController(EmployeeService employeeService) {
        this.employeeService = employeeService;
    }*/
  
    @GetMapping("/a")
    public String getEmployeeName() {
        String employeeName = employeeService.getEmployeeName(1);
        System.out.println(employeeName);
        return employeeName;
    }  
}

5.service层

可以再抽取一层，service接口+serviceImpl实现
这里我没有搞接口，直接实现

EmployeeService

package com.gitee.springbootdemo.service;

import com.gitee.springbootdemo.mapper.EmployeeMapper;
import com.gitee.springbootdemo.model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class EmployeeService {

    @Autowired
    private  EmployeeMapper employeeMapper;

    /*private final EmployeeMapper employeeMapper;

    @Autowired
    public EmployeeService(EmployeeMapper employeeMapper) {
        this.employeeMapper = employeeMapper;
    }*/

    public String getEmployeeName(int id) {
        Employee employee = new Employee();
        employee.setId(id);
        employeeMapper.getEmployeeName(employee);
        return employee.getName();
    }  
}

6.dao层+mapper配置

EmployeeMapper

package com.gitee.springbootdemo.mapper;

import com.gitee.springbootdemo.model.Employee;
import org.springframework.stereotype.Repository;

@Repository
public interface EmployeeMapper {
    //Employee  getEmployeeName(int id);
    void  getEmployeeName(Employee employee);
}

EmployeeMapper.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.gitee.springbootdemo.mapper.EmployeeMapper">

    <!--<resultMap id="BaseResultMap" type="com.gitee.springbootdemo.model.Employee">
        <result column="id" jdbcType="TINYINT" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
    </resultMap>
    <select id="getEmployeeName" resultMap="BaseResultMap">
        select
            id, name
        from employee
        where id = #{id}
    </select>-->
    <!-- 存储过程调用 -->
    <select id="getEmployeeName" statementType="CALLABLE">
        {call GetEmployeeName(#{id,mode=IN,jdbcType=INTEGER}, #{name,mode=OUT,jdbcType=VARCHAR})}
    </select>

</mapper>

7.启动类

package com.gitee.springbootdemo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.gitee.springbootdemo.mapper")
public class SpringBootDemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringBootDemoApplication.class, args);
    }

}

8. application.properties配置

# DataSource settings
server.port=8080
spring.datasource.url=jdbc:mysql://localhost:3306/ssm_db?useSSL=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# MyBatis settings
mybatis.type-aliases-package=com.gitee.springbootdemo.model
mybatis.mapper-locations=com/gitee/springbootdemo/dao/mapper/*.xml

9.建表语句+存储过程

-- 建库建表语句
CREATE DATABASE `ssm_db`;
USE ssm_db;
CREATE TABLE `employee` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 表数据
INSERT INTO ssm_db.employee
(id, name)
VALUES(1, 'A');
INSERT INTO ssm_db.employee
(id, name)
VALUES(2, 'B');

-- 存储过程
DELIMITER //
DROP PROCEDURE IF EXISTS GetEmployeeName//
CREATE PROCEDURE GetEmployeeName(IN empId INT, OUT empName VARCHAR(255))  
BEGIN  
    SELECT name INTO empName FROM employee WHERE id = empId;  
END //  
DELIMITER ;

10.启动程序进行测试

1、调出service窗口添加服务
方便启动测试，不搞也是可以的

使用postman进行测试，返回正常