How to set parameter to postgres function using native query?

Question

I created a native query where I use the postgres function date_part(text, timestamp) . This is my code:

String sql = "SELECT "
                + "(SELECT coalesce(count(*), 0) FROM public.cliente) AS totalClientes, "
                + "(SELECT coalesce(count(*), 0) FROM cliente WHERE date_part('year', dt_cadastro) = date_part('year', :ano) ) AS totalNovosClientes";
                
Query query = entityManager.createNativeQuery(sql);
        query.setParameter("ano", "2021-01-01");

The problem I'm facing here is when I try to set the parameter of the date_part function. It says that I need to add explicit cast for the "ano" parameter. I've already tried the following, but none of them worked:

date_part('year', TIMESTAMP :ano)
query.setParameter("ano", "2021-01-01");
query.setParameter("ano", java.sql.Timestamp.valueOf("2021-01-01 10:10:10.0"), TemporalType.TIMESTAMP );

How to set the parameter properly?

Andrea Nagy · Accepted Answer · 2021-03-12 20:05:45Z

1

JPA specification does not support named parameters with native queries,but depending on what JPA implementation you use replacing the : in your sql string with # or ? might work.

In general for native queries the best practice is to use positional based parameters, e.g:

String sql = "SELECT "
    + "(SELECT coalesce(count(*), 0) FROM public.cliente) AS totalClientes, "
    + "(SELECT coalesce(count(*), 0) FROM cliente WHERE "
    + "date_part('year', dt_cadastro) = date_part('year', $1) ) AS totalNovosClientes";
                
Query query = entityManager.createNativeQuery(sql);
query.setParameter(1, "2021-01-01");

answered Mar 12, 2021 at 20:05

Andrea Nagy

1,24111 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

How to set parameter to postgres function using native query?

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