CriteriaBuilder call a select function

Question

I have a query that i want to call a function without parsing a parameter to the function. Example....

CREATE OR REPLACE function demo_fn()
RETURNS TABLE (
    id bigint,
 somename varchar,
 something varchar) AS $$
BEGIN
    RETURN QUERY SELECT DISTINCT c.id, c.somename , c.something FROM pluto c
    ORDER BY id ASC LIMIT 10; 
END;
$$ 
LANGUAGE plpgsql;

This query function is called like this select * from demo_fn(); which work perfectly well from the database. On the other hand, the method calling the function is like this....

CriteriaBuilder cb = em.getCriteriaBuilder();
cb.function("demo_fn", PlutoDTO.class, cb.parameter(null))));
CriteriaQuery cq = cb.createQuery(PlutoDTO.class);
Root root = cq.from(Pluto.class);

TypedQuery q = em.createQuery(cq);
List<PlutoDTO> pluto = q.getResultList();

When i run the function Caused by: java.lang.IllegalArgumentException: Error occurred validating the Criteria

Can someone please point me to the right direction Can't really see my way out here.....much appreciated

score 0 · Accepted Answer · answered Jul 28 '20 at 09:40

So it seems calling a function with criteria gets a little interesting, so what i did was, i used a prepared statement rather...

Session session = entityManager.unwrap(Session.class); //Session from Hibernate
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                PreparedStatement ps= connection.prepareStatement("select * from demo_fn()");
                ResultSet rs= ps.getResultSet();
                List<DemoDto> demoDtos = new ArrayList<>();
                while (rs.next()) {
                     DemoDto demoDto = new DemoDto();
                     demoDto.setDemoValueOne(rs.getString("Column_name_here"));
                     demoDtos.add(demoDto);
                       // Get the values from ResultSet in this case rs
                }
                //Have an impelementation Taking care of this
                //rs.close(); 
                //ps.close();
            }

Hope this get helpful to someone in the future, it really was a pain in a spin. Be careful when paginating though, its really flexible.

CriteriaBuilder call a select function

1 Answers1