Java JPA setParameter date object

Question

I have this function in my PostgreSQL database.

create or replace function borrowaCar(integer,integer,date) returns boolean language 'plpgsql' as'
declare 
    borrow boolean;
begin
     if exists(select * from availableCars where id=$2)then
            insert into Borrows (car_id_car, person_id_person, borrow_date, return_date) values ($2,$1,$3, NULL);
            update Car set is_borrow=true where id_car=$2;
            return true;
     end if;
    return false;
end;
';

When I build query I would like to add actual date in YYYY-MM-DD like 2017-09-06. I write this code:

Query query = entityManager.createNativeQuery("SELECT borrowaCar(?,?,?)");          
query.setParameter(1, idPerson);
query.setParameter(2, idCar);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = new Date();
query.setParameter(3, sdf.format(date));

But this solution returns error:

org.postgresql.util.PSQLException: ERROR: function borrowacar(integer, integer, character varying) does not exist Wskazówka: No function matches the given name and argument types. You might need to add explicit type casts.

Whats the problem?

If the type of borrow_date is date then do not use sdf.format and take date directly. If the type is charvar then change the function signature from int,int,date to int,int,String — Matthias H

Matthias H Matthias H · Accepted Answer · 2017-09-06T18:44:42

The Problem is that your defined function

borrowaCar(integer, integer, date)

does not match your Parameters:

borrowacar(integer, integer, character varying)

The call of sdf.format(date) returns a String and is interpreted as "character varying". You need to take the type definded in Column borrow_date of Table Borrows.

Java JPA setParameter date object

1 Answers