Creating and executing a stored procedure in spring boot

Question

I have a stored procedure written in a sql file which I want to execute through spring boot code using jdbc template. The stored procedure is like :

CREATE OR REPLACE FUNCTION DELETE_REDUNDANT_RECORDS()
RETURNS void AS
$func$
DECLARE
    interval_time            BIGINT DEFAULT 0;
    min_time                 BIGINT DEFAULT 0;
    max_time                 BIGINT DEFAULT 0;
    rec_old                  RECORD;
    rec_new                  RECORD;
    rec_start                RECORD;
    v_filename               VARCHAR(250);
    v_systemuid              VARCHAR(50);
    is_continous_record      BOOLEAN default false;

    cursor_file CURSOR FOR
        select distinct filename,systemuid from ASP.MONITORING_BOOKMARK_ORIGINAL;
    cursor_data CURSOR FOR
        select * from ASP.MONITORING_BOOKMARK_ORIGINAL where filename = v_filename and systemuid=v_systemuid order by mindatetime, maxdatetime;

BEGIN
    --open the file cursor to fetch the filename and systemuid
    OPEN cursor_file;
    -- logic for procedure
    CLOSE cursor_file;
END;
$func$
LANGUAGE plpgsql;

I have added this in a sql file in my spring boot project. I want to create a scheduler to create and execute this stored procedure using spring jdbc. The database used is Postgres. Is there a way available to do this. I have got references for calling a procedure but what I need is to create and execute the procedure.

Put the code in a String, then run it using Statement.execute(String) — user330315
– user330315, Commented Jul 26, 2019 at 10:16

Filippo Possenti · Accepted Answer · 2019-07-26 11:56:19Z

0

For running the procedure, Spring's SimpleJdbcCall could be what you are looking for. Its use should be fairly straightforward:

create an instance supplying a DataSource
call withCatalogName and withProcedureName as needed
call declareParameters as needed
call execute to run the procedure

For creating it, a JdbcTemplate and its own execute method should be sufficient.

Be aware that you might want to create the function once and only run it afterwards. Creating and running the procedure both as part of some scheduled operation is a quite uncommon requirement. You might want to re-assess the need to create the procedure each and every time if possible.

answered Jul 26, 2019 at 11:56

Filippo Possenti

1,41010 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating and executing a stored procedure in spring boot

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related