Spring Boot JDBC Stored Procedure Examples
By mkyong |
Viewed: 29,461 (+245 pv/w)
In this tutorial, we will show you how to use Spring Boot JDBC SimpleJdbcCall to call a stored procedure and stored function from a Oracle database.
Technologies used :
- Spring Boot 2.1.2.RELEASE
- Spring JDBC 5.1.4.RELEASE
- Oracle database 19c
- HikariCP 3.2.0
- Maven 3
- Java 8
Unlike JdbcTemplate, Spring Boot didn’t create any SimpleJdbcCall automatically, we have to create it manually.
1. Test Data
1.1 Create a table and save 4 books for testing.
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. Stored Procedure
2.1 A stored procedure to return a single result.
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 We can init the SimpleJdbcCall via @PostConstruct.
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. Stored Procedure #SYS_REFCURSOR
3.1 A stored procedure to return a ref cursor.
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 to map the cursor result to book object.
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. Stored Function
4.1 Create two functions for testing.
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. For Stored Function, call it with 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
}
}
In summary:
- For Stored Procedure,
SimpleJdbcCall.execute. - For Stored Function,
SimpleJdbcCall.executeFunction
Download Source Code
$ git clone https://github.com/mkyong/spring-boot.git
$ cd spring-jdbc/sp
$ cd spring-jdbc/sp
References
About Author
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.
Your tutorials are very useful. Could you please post how to call parameterized stored procedure having in and out parameters using Spring JdbcTemplate.
I have 3 select query in stored procedure, new SimpleJdbcCall(jdbcTemplate1).withSchemaName(“xxx”)
.withProcedureName(“Procedure”)
.returningResultSet(“aaa”, new classa())
.returningResultSet(“bbb”, new classb())
.returningResultSet(“ccc”, new classc())
if first select statement returned 0 rows, second select query result is mapped to first resultset. could you pls help me in this
Please help me one scenario that i was actually blocked
I want to pass Collection of elements to Oracle stored procedure as IN parameter using JPA .
but i couldn’t do that , getting lot of errors and blocked here from last couple of days
Simple jdbc call is not closing the connection.
How can we use pageable object with Store procedures? my query is complex.
how do we pass JSON to a stored procedure ? thanks
Send as var char and convert it into json in Oracle 12 c
Please upload the examples on spring boot with pl sql collections using custom Objects.and CURD Operations
Hi
Great Article. How could i possibly use Spring to execute Anonymous PL SQL block?