使用Spring StoredProcedure调用存储过程

傷城~ 2023-09-26 10:45 35阅读 0赞

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

在此示例中，我们将使用 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;
    import org.springframework.jdbc.object.GenericStoredProcedure;
    import org.springframework.jdbc.object.StoredProcedure;
    
    @Component
    public class ClientBean {
    
        @Autowired
        private DataSource dataSource;
    
        public void findSum() {
            StoredProcedure procedure = new GenericStoredProcedure();
            procedure.setDataSource(dataSource);
            procedure.setSql("GET_SUM_SP");
            procedure.setFunction(false);
    
            SqlParameter[] parameters = {
                new SqlParameter("first_num", Types.INTEGER),
                new SqlParameter("second_num", Types.INTEGER),
                new SqlOutParameter("result", Types.INTEGER)
    
            };
    
            procedure.setParameters(parameters);
    
            procedure.compile();
    
            Map<String, Object> result = procedure.execute(1, 2);
            System.out.println(result.get("result"));
    
        }
    
    }

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-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>
    
            
            <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>