CODEWITHSUNDEEP
javapostgresqljdbcjdbctemplate
Linz
Linz

Reputation: 364

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

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

Upvotes: 0

Views: 1420

Answers (1)

Linz
Linz

Reputation: 364

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!!

Upvotes: 1

Related Questions