Spring Boot JDBC存储过程示例

在本教程中，我们将向您展示如何使用Spring Boot JDBC SimpleJdbcCall从Oracle数据库调用存储过程和存储函数。

使用的技术：

• Spring Boot 2.1.2发布
• Spring JDBC 5.1.4.RELEASE
• Oracle数据库19c
• HikariCP 3.2.0
• Maven 3
• Java 8

与JdbcTemplate不同，Spring Boot不会自动创建任何SimpleJdbcCall ，我们必须手动创建它。

注意
该示例扩展了先前的Spring Boot JDBC示例 ，添加了对SimpleJdbcCall支持

1.测试数据

1.1创建一个表并保存4本书以进行测试。

CREATE TABLE BOOKS(
    ID NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
    NAME VARCHAR2(100) NOT NULL,
    PRICE NUMBER(15, 2) NOT NULL,
    CONSTRAINT book_pk PRIMARY KEY (ID)
);

List<Book> books = Arrays.asList(
			new Book("Thinking in Java", new BigDecimal("46.32")),
			new Book("Mkyong in Java", new BigDecimal("1.99")),
			new Book("Getting Clojure", new BigDecimal("37.3")),
			new Book("Head First Android Development", new BigDecimal("41.19"))
	);

	books.forEach(book -> {
		log.info("Saving...{}", book.getName());
		bookRepository.save(book);
	});

2.存储过程

2.1一个存储过程，返回单个结果。

CREATE OR REPLACE PROCEDURE get_book_by_id(
        p_id IN BOOKS.ID%TYPE,
        o_name OUT BOOKS.NAME%TYPE,
        o_price OUT BOOKS.PRICE%TYPE)
    AS
    BEGIN

        SELECT NAME , PRICE INTO o_name, o_price from BOOKS WHERE ID = p_id;

    END;

2.2我们可以通过@PostConstruct初始化SimpleJdbcCall 。

StoredProcedure1.java

package com.mkyong.sp;

import com.mkyong.Book;
import com.mkyong.repository.BookRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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.annotation.PostConstruct;
import java.math.BigDecimal;
import java.util.Map;
import java.util.Optional;

@Component
public class StoredProcedure1 {

    private static final Logger log = LoggerFactory.getLogger(StoredProcedure1.class);

    @Autowired
    @Qualifier("jdbcBookRepository")
    private BookRepository bookRepository;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private SimpleJdbcCall simpleJdbcCall;

    // init SimpleJdbcCall
    @PostConstruct
    void init() {
        // o_name and O_NAME, same
        jdbcTemplate.setResultsMapCaseInsensitive(true);

        simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("get_book_by_id");

    }

    private static final String SQL_STORED_PROC = ""
            + " CREATE OR REPLACE PROCEDURE get_book_by_id "
            + " ("
            + "  p_id IN BOOKS.ID%TYPE,"
            + "  o_name OUT BOOKS.NAME%TYPE,"
            + "  o_price OUT BOOKS.PRICE%TYPE"
            + " ) AS"
            + " BEGIN"
            + "  SELECT NAME, PRICE INTO o_name, o_price from BOOKS WHERE ID = p_id;"
            + " END;";


    public void start() {

        log.info("Creating Store Procedures and Function...");
        jdbcTemplate.execute(SQL_STORED_PROC);

        /* Test Stored Procedure */
        Book book = findById(2L).orElseThrow(IllegalArgumentException::new);
        
        // Book{id=2, name='Mkyong in Java', price=1.99}
        System.out.println(book);

    }

    Optional<Book> findById(Long id) {

        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("p_id", id);

        Optional result = Optional.empty();

        try {

            Map out = simpleJdbcCall.execute(in);

            if (out != null) {
                Book book = new Book();
                book.setId(id);
                book.setName((String) out.get("O_NAME"));
                book.setPrice((BigDecimal) out.get("O_PRICE"));
                result = Optional.of(book);
            }

        } catch (Exception e) {
            // ORA-01403: no data found, or any java.sql.SQLException
            System.err.println(e.getMessage());
        }

        return result;
    }

}

3.存储过程#SYS_REFCURSOR

3.1一个存储过程，返回一个ref游标。

CREATE OR REPLACE PROCEDURE get_book_by_name(
   p_name IN BOOKS.NAME%TYPE,
   o_c_book OUT SYS_REFCURSOR)
AS
BEGIN

  OPEN o_c_book FOR
  SELECT * FROM BOOKS WHERE NAME LIKE '%' || p_name || '%';

END;

3.2 BeanPropertyRowMapper将光标结果映射到book对象。

StoredProcedure2.java

package com.mkyong.sp;

import com.mkyong.Book;
import com.mkyong.repository.BookRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
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.annotation.PostConstruct;
import java.util.Collections;
import java.util.List;
import java.util.Map;

@Component
public class StoredProcedure2 {

    private static final Logger log = LoggerFactory.getLogger(StoredProcedure2.class);

    @Autowired
    @Qualifier("jdbcBookRepository")
    private BookRepository bookRepository;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private SimpleJdbcCall simpleJdbcCallRefCursor;

    // init SimpleJdbcCall
    @PostConstruct
    public void init() {
        // o_name and O_NAME, same
        jdbcTemplate.setResultsMapCaseInsensitive(true);

        // Convert o_c_book SYS_REFCURSOR to List<Book>
        simpleJdbcCallRefCursor = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("get_book_by_name")
                .returningResultSet("o_c_book",
                        BeanPropertyRowMapper.newInstance(Book.class));

    }

    private static final String SQL_STORED_PROC_REF = ""
            + " CREATE OR REPLACE PROCEDURE get_book_by_name "
            + " ("
            + "  p_name IN BOOKS.NAME%TYPE,"
            + "  o_c_book OUT SYS_REFCURSOR"
            + " ) AS"
            + " BEGIN"
            + "  OPEN o_c_book FOR"
            + "  SELECT * FROM BOOKS WHERE NAME LIKE '%' || p_name || '%';"
            + " END;";

    public void start() {

		log.info("Creating Store Procedures and Function...");
        jdbcTemplate.execute(SQL_STORED_PROC_REF);
		
        /* Test Stored Procedure RefCursor */
        List<Book> books = findBookByName("Java");

        // Book{id=1, name='Thinking in Java', price=46.32}
        // Book{id=2, name='Mkyong in Java', price=1.99}
        books.forEach(x -> System.out.println(x));

    }

    List<Book> findBookByName(String name) {

        SqlParameterSource paramaters = new MapSqlParameterSource()
                .addValue("p_name", name);

        Map out = simpleJdbcCallRefCursor.execute(paramaters);

        if (out == null) {
            return Collections.emptyList();
        } else {
            return (List) out.get("o_c_book");
        }

    }

}

4.存储功能

4.1创建两个测试功能。

CREATE OR REPLACE FUNCTION get_price_by_id(p_id IN BOOKS.ID%TYPE)
RETURN NUMBER
IS o_price BOOKS.PRICE%TYPE;
BEGIN
    SELECT PRICE INTO o_price from BOOKS WHERE ID = p_id;
    RETURN(o_price);
END;

CREATE OR REPLACE FUNCTION get_database_time
RETURN VARCHAR2
IS o_date VARCHAR2(20);
BEGIN
    SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO o_date FROM dual;
    RETURN(o_date);
END;

4.2。 对于存储函数，请使用SimpleJdbcCall.executeFunction调用

StoredFunction.java

package com.mkyong.sp;

import com.mkyong.repository.BookRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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.annotation.PostConstruct;
import java.math.BigDecimal;

@Component
public class StoredFunction {

    private static final Logger log = LoggerFactory.getLogger(StoredFunction.class);

    @Autowired
    @Qualifier("jdbcBookRepository")
    private BookRepository bookRepository;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private SimpleJdbcCall simpleJdbcCallFunction1;
    private SimpleJdbcCall simpleJdbcCallFunction2;

    // init SimpleJdbcCall
    @PostConstruct
    public void init() {

        jdbcTemplate.setResultsMapCaseInsensitive(true);

        simpleJdbcCallFunction1 = new SimpleJdbcCall(jdbcTemplate)
			.withFunctionName("get_price_by_id");
			
        simpleJdbcCallFunction2 = new SimpleJdbcCall(jdbcTemplate)
			.withFunctionName("get_database_time");
    }

    private static final String SQL_STORED_FUNCTION_1 = ""
            + " CREATE OR REPLACE FUNCTION get_price_by_id(p_id IN BOOKS.ID%TYPE) "
            + " RETURN NUMBER"
            + " IS o_price BOOKS.PRICE%TYPE;"
            + " BEGIN"
            + "  SELECT PRICE INTO o_price from BOOKS WHERE ID = p_id;"
            + "  RETURN(o_price);"
            + " END;";

    private static final String SQL_STORED_FUNCTION_2 = ""
            + " CREATE OR REPLACE FUNCTION get_database_time "
            + " RETURN VARCHAR2"
            + " IS o_date VARCHAR2(20);"
            + " BEGIN"
            + "  SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO o_date FROM dual;"
            + "  RETURN(o_date);"
            + " END;";

    public void start() {

        log.info("Creating Store Procedures and Function...");
        jdbcTemplate.execute(SQL_STORED_FUNCTION_1);
        jdbcTemplate.execute(SQL_STORED_FUNCTION_2);

        /* Test Stored Function 1 */
        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("p_id", 3L);
        BigDecimal price = simpleJdbcCallFunction1.executeFunction(BigDecimal.class, in);
        System.out.println(price);  // 37.3

        /* Test Stored Function 2 */
        String database_time = simpleJdbcCallFunction2.executeFunction(String.class);
        System.out.println(database_time); // e.g current date, 23-JUL-2019 05:08:44

    }

}

综上所述：

• 对于存储过程，请使用SimpleJdbcCall.execute 。
• 对于存储的函数， SimpleJdbcCall.executeFunction

下载源代码

$ git clone https://github.com/mkyong/spring-boot.git
$ cd spring-jdbc / sp

参考文献

• Oracle创建功能
• Oracle存储功能示例
• Oracle开发和使用存储过程
• JDBC CallableStatement –存储过程OUT参数示例
• Spring JDBC文档
• 使用SQL数据库
• Java JDBC教程
• 使用参考光标返回记录集

标签： jdbc oracle spring boot spring jdbc 存储函数 存储过程

翻译自: https://mkyong.com/spring-boot/spring-boot-jdbc-stored-procedure-examples/