使用Spring SimpleJdbcCall调用存储过程

SimpleJdbcCall包装JdbcTemplate以简化调用存储过程或存储函数所需的代码。

在此示例中，我们将使用 MySql 数据库服务器作为数据源。

例

在 MySql 中创建数据库函数

将以下函数复制粘贴到 MySql 工作台并执行它。

src/main/resources/sum-procedure.sql

DELIMITER //
CREATE PROCEDURE GET_SUM_SP(IN first_num INT, IN second_num INT, OUT result INT)
  BEGIN
     SET result = first_num + second_num;
  END //
DELIMITER ;

使用SimpleJdbcCall

package com.logicbig.example;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.sql.Types;
import java.util.Map;

@Component
public class ClientBean {

    @Autowired
    private DataSource dataSource;
    private SimpleJdbcCall jdbcCall;

    @PostConstruct
    public void init() {
        JdbcTemplate template = new JdbcTemplate(dataSource);
        jdbcCall = new SimpleJdbcCall(template)
                .withProcedureName("GET_SUM_SP")
                .declareParameters(
                        new SqlParameter("first_num", Types.INTEGER),
                        new SqlParameter("second_num", Types.INTEGER),
                        new SqlOutParameter("result", Types.INTEGER));

    }

    public void findSum() {
        MapSqlParameterSource paramMap = new MapSqlParameterSource()
                .addValue("first_num", 5)
                .addValue("second_num", 20);
        Map<String, Object> resultMap = jdbcCall.execute(paramMap);
        resultMap.entrySet().forEach(System.out::println);
    }
}

Java 配置

package com.logicbig.example;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

@Configuration
@ComponentScan
public class AppConfig {

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName(com.mysql.cj.jdbc.Driver.class.getName());
        ds.setUrl("jdbc:mysql://localhost:3306/testdb?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useAffectedRows=true");
        ds.setUsername("root");
        ds.setPassword("root");
        return ds;
    }

    public static void main(String[] args) {
        AnnotationConfigApplicationContext context
                = new AnnotationConfigApplicationContext(AppConfig.class);
        context.getBean(ClientBean.class).findSum();
    }
}

输出

#update-count-1=0
result=25

项目

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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.logicbig.example</groupId>
    <artifactId>simple-jdbc-sp-call-mysql</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.3.23</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.23</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/javax.annotation/javax.annotation-api -->
        <dependency>
            <groupId>javax.annotation</groupId>
            <artifactId>javax.annotation-api</artifactId>
            <version>1.3.2</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.3</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>