Справочник по C#

    Исходники по языку программирования CSharp

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

    /
    /
    /
    73 Views

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

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

    • Spring Boot 2.1.2.RELEASE
    • Spring JDBC 5.1.4. РЕЛИЗ
    • База данных 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 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 с помощью @PostConstruct ,

    StoredProcedure1.java

    
    package com.csharpcoderr.sp;
    
    import com.csharpcoderr.Book;
    import com.csharpcoderr.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 и O_NAME, то же самое
    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', цена = 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: данные не найдены или 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.csharpcoderr.sp;
    
    import com.csharpcoderr.Book;
    import com.csharpcoderr.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 и O_NAME, то же самое
    jdbcTemplate.setResultsMapCaseInsensitive(true);
    
    // Конвертировать o_c_book SYS_REFCURSOR в 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 = 'Мышление на Java', цена = 46.32}
    // Book {id = 2, name = 'Mkyong in Java', цена = 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.executeFunction

    StoredFunction.java

    
    package com.csharpcoderr.sp;
    
    import com.csharpcoderr.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); // Например, текущая дата, 23 июля 2019 05:08:44
    
    }
    
    }
    

    В итоге:

    • Для хранимой процедуры, SimpleJdbcCall.execute ,
    • Для сохраненной функции, SimpleJdbcCall.executeFunction

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

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

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

    jdbc oracle spring boot spring jdbc хранимая функция хранимая процедура

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

    0.00 (0%) 0 votes

    moyadcode13
    • Facebook
    • Twitter
    • Google+
    • Linkedin
    • Pinterest