Рубрики
Без рубрики

Примеры хранимых процедур Spring Boot JDBC

– Примеры хранимых процедур Spring Boot JDBC

В этом уроке мы покажем вам, как использовать Spring Boot JDBC SimpleJdbcCall для вызова хранимой процедуры и сохраненной функции из базы данных Oracle.

Используемые технологии:

  • Пружинный ботинок 2.1.2. ВЫПУСК
  • Весенний ВЫПУСК JDBC 5.1.4.
  • База данных Oracle 19c
  • ХикариКП 3.2.0
  • Мавен 3
  • Java 8

В отличие от JdbcTemplate , Spring Boot не создавал никаких 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 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 Мы можем инициализировать SimpleJdbcCall через @Постконструкция .

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 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 Хранимая процедура для возврата указателя ссылки.

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 для отображения результата курсора на книгу объект.

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
        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 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 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.выполнить функцию

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.выполнить .
  • Для Сохраненной Функции, Для Сохраненной Функции,

Скачать Исходный Код

Рекомендации

Оригинал: “https://mkyong.com/spring-boot/spring-boot-jdbc-stored-procedure-examples/”

Читайте ещё по теме: