How to invoke stored function in java of PostgreSQL database with JDBCTemplate

Question

I have a stored function that takes below arguments in database.

CREATE OR REPLACE FUNCTION

public.abc(
  id integer,
  title text,
  description text,
  valid_until_date timestamp with time zone,
  user_is_ext boolean,
  remarks text)
{
    //statements
}

I need to invoke this stored function. I am able to invoke directly in database using below query:

select  "abc" (0,'title','description','2010-01-01 00:00:00+01',false,'text')

However i am not able to invoke using JDBC template in my SpringBoot application.

String sql="select  \"abc\" (?,?,?,?,?,?)";
List<Integer> ids=jdbcTemplate.query(sql,new Object[]{id,myObj.getTitle(),  myObj.getDescription(),  myObj.getValidDate(),  myObj.isUserExt(),  ,myObj.getRemarks()},new BeanPropertyRowMapper(Integer.class));

Can someone help me to figure out what is it that I am missing?

i get "The column index is out of range:" error. I tried using "update" instead of "query" int ind=jdbcTemplate.update(sql, id,myObj.getTitle(), myObj.getDescription(), myObj.getValidUntilDate(), myObj.isUserExt(), myObj.getRemarks());

then i get following error

""2017-07-10 14:51:16 [http-bio-8080-exec-60] ERROR c.s.k.l.exceptions.ExceptionHandlers --- A result was returned when none was expected. –

tried using SimpleJDBC call as mentioned on the comment. getting below error while passing timestamp as a parameter in SQLParameter object

""2017-07-10 16:18:16 [http-bio-8080-exec-97] ERROR c.s.k.l.exceptions.ExceptionHandlers --- Bad value for type timestamp : org.springframework.jdbc.core.SqlParameter@3a4cbd06

What happens if you it that way, do you get an error, or what else? — Mark Rotteveel
– Mark Rotteveel, Commented Jul 10, 2017 at 10:17
i get "The column index is out of range:" error. I tried using "update" instead of "query" int ind=jdbcTemplate.update(sql, id,myObj.getTitle(), myObj.getDescription(), myObj.getValidUntilDate(), myObj.isUserExt(), myObj.getRemarks()); then i get following error""2017-07-10 14:51:16 [http-bio-8080-exec-60] ERROR c.s.k.l.exceptions.ExceptionHandlers --- A result was returned when none was expected. — Linz
– Linz, Commented Jul 10, 2017 at 10:25
@Suganthan I tried that. how to pass TIMESTAMP_WITH_TIMEZONE in SQLParameter. I get below error for the following statement while executing: SqlParameter valid_until_date=new SqlParameter("2010-01-01 00:00:00+01", Types.TIMESTAMP_WITH_TIMEZONE); ""2017-07-10 16:18:16 [http-bio-8080-exec-97] ERROR c.s.k.l.exceptions.ExceptionHandlers --- Bad value for type timestamp : org.springframework.jdbc.core.SqlParameter@3a4cbd06 — Linz
– Linz, Commented Jul 10, 2017 at 10:59

Linz · Accepted Answer · 2017-07-12 06:02:08Z

1

Finally i resolved it!!

In my case I'm trying to invoke a stored function that returns an integer value. please find the code snippet below.

String sql="select * from \"stored_function_name\" (?,?,?,?,?,?,?,?,?)"; Integer result=jdbcTemplate.queryForObject(sql,Integer.class, new Object[] {all input parameters separated by coma});

Similarly we can use other variants of query.

Please make sure the parameters that you pass to function should have same datatype as in postgres database. If its a timestamp in db and you have date as string, you can convert it to timestamp using below code

Timestamp.valueOf("date_string in yyyy-mm-dd hh:mm:ss format")

Thank you everyone!!

answered Jul 12, 2017 at 6:02

Linz

3643 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

How to invoke stored function in java of PostgreSQL database with JDBCTemplate

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