使用Spring SimpleJdbcCall调用存储函数

朱雀 2023-09-26 10:43 45阅读 0赞

此示例演示如何使用`SimpleJdbcCall`调用数据库函数。

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

### 例 ###

### 在 MySql 中创建数据库函数 ###

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

src/main/resources/sum-function.sql

DELIMITER //
    CREATE FUNCTION GET_SUM(first_num INT, second_num INT)
    	RETURNS INT
    	DETERMINISTIC
    		RETURN first_num + second_num;
    //
    DELIMITER ;

### 使用SimpleJdbcCall ###

package com.logicbig.example;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
    import org.springframework.jdbc.core.namedparam.SqlParameterSource;
    import org.springframework.jdbc.core.simple.SimpleJdbcCall;
    import org.springframework.stereotype.Component;
    
    import javax.sql.DataSource;
    
    @Component
    public class ClientBean {
    
        @Autowired
        private DataSource dataSource;
    
        public void findSum() {
            JdbcTemplate template = new JdbcTemplate(dataSource);
            SimpleJdbcCall call = new SimpleJdbcCall(template)
                    .withFunctionName("GET_SUM");
    
            SqlParameterSource paramMap = new MapSqlParameterSource()
                    .addValue("first_num", 55)
                    .addValue("second_num", 20);
    
            Integer sum = call.executeFunction(Integer.class, paramMap);
            System.out.println(sum);
        }
    }

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

输出

### 项目 ###

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-function-call</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>
        </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>