Category Archives: Spring Boot

Spring boot JDBC store procedure by Package Name

Make ready first your application pom.xml file for DB operation

<-- Spring boot data JPA and exclude default tomcat pool -->
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
	<exclusions>
		<exclusion>
			<groupId>org.apache.tomcat</groupId>
			<artifactId>tomcat-jdbc</artifactId>
		</exclusion>
	</exclusions>
</dependency>

<-- JDBC driver for Oracle download the jar from oracle site -->
<dependency>
		<groupId>com.oracle</groupId>
		<artifactId>ojdbc</artifactId>
		<version>7</version>
		<scope>system</scope>
		<systemPath>H:\jaforiqbal\ojdbc7.jar</systemPath>
</dependency>

Create Table Book

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

Create Procedure in a package whatever you name it

	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;

If you want to use cursor in your store procedure

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

o_name VARCHAR2(200);
o_price VARCHAR2(200);

BEGIN

  OPEN o_c_book FOR
  SELECT NAME , PRICE INTO o_name, o_price FROM BOOKS WHERE NAME LIKE '%' || p_name || '%';

END;

Create Rest Controller


import java.util.Optional;
import java.util.Map;


@RestController
public class JDBCService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private SimpleJdbcCall simpleJdbcCall;


    @RequestMapping(method = RequestMethod.GET, path = "/get-book-by-id/{id}")
    public Optional<Book> findBookById(@PathVariable Long id) {

        // o_name and O_NAME, same
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withCatalogName("PKG_EMPLOYEE").withProcedureName("get_book_by_id");
        SqlParameterSource in = new MapSqlParameterSource().addValue("p_id", id);
        Optional result = Optional.empty();

            Map out = simpleJdbcCall.execute(in);
                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);

        return result;

    }

    //Cursor based store procedure query
    @RequestMapping(method = RequestMethod.GET, path = "/get-book-by-name/{name}")
    public List<Book> findBookByName(@PathVariable String name) {

        // o_name and O_NAME, same
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        // Convert o_c_book SYS_REFCURSOR to List<Book>
        simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("get_book_by_name")
                .returningResultSet("o_c_book",
                        BeanPropertyRowMapper.newInstance(Book.class));


        simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withCatalogName("PKG_EMPLOYEE").withProcedureName("get_book_by_name");
        SqlParameterSource paramaters = new MapSqlParameterSource()
                .addValue("p_name", name);

        Map out = simpleJdbcCall.execute(paramaters);

        return (List<Book>) out.get("o_c_book");

    }

}

Create Book Bean

import java.math.BigDecimal;

public class Book {
    public Long id;
    public String name;
    public BigDecimal price;

    public Book(){

    }
    public Book(Long id, String name, BigDecimal price) {
        this.id = id;
        this.name = name;
        this.price = price;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public BigDecimal getPrice() {
        return price;
    }

    public void setPrice(BigDecimal price) {
        this.price = price;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", price=" + price +
                '}';
    }
}

For More Help Follow this site which is very helpful I found

spring-boot-jdbc-stored-procedure-examples