Call database function with Spring

Question

I need to query a function of the database, by SQL, something so simple as

@Query("SELECT random()")

how to implement a domain/repository or service method that do it?

An open question related to this, http://stackoverflow.com/q/41880120/287948 — Peter Krauss, Jan 26 '17 at 18:11
.. And get a bounty [here](http://stackoverflow.com/q/41837546/287948)! — Peter Krauss, Jan 27 '17 at 13:55

score 5 · Answer 1 · answered Jan 26 '17 at 05:47

5

There are various ways how to bind stored procedures with Spring Data JPA (using JPA 2.1).

Simplest example:

@Procedure("random")
Integer randomStoredProcedure();

answered Jan 26 '17 at 05:47

luboskrnac

23,973
10
81
92

Thanks to the docs.spring link and explain that "function" is "stored procedure" in the JPA jargon! Now a problem to implement it: it is possible to call at a controller? On my code `@Procedure` is "cannot resolve symbol 'Procedure'" – Peter Krauss Jan 26 '17 at 13:00
Correction, it is not integer, is double... So something like `@Procedure("random") double x = randomStoredProcedure();` – Peter Krauss Jan 26 '17 at 13:15
... But need to declare more things... where I most declare it? (at *controller*?, at *domain/repository*?) Examples of other thing to declare: `@NamedStoredProcedureQuery(name = "Double.random", procedureName = "random")` ... but where declare it? – Peter Krauss Jan 26 '17 at 13:17
what should be type of randomStoredProcedure () if db function is 'returns table' function ? – 9900kf Dec 24 '21 at 04:56
@9900kf try [this](https://stackoverflow.com/a/70845016/5788486). – Shekhar Rai Jan 25 '22 at 07:47

score 1 · Answer 2 · answered Jan 25 '22 at 07:46

In case, you don't have any Entity/DTO (non @Entity) objects and wondering how to map returned table from the function into your custom object.

@Repository
public class YourRepository {

    private final EntityManager entityManager;

    public(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public List<YourObject> findObjects() {
        List<Object[]> columns = entityManager
                .createStoredProcedureQuery("your_sql_func")
                .getResultList();
        return columns.stream().map(this::toYourObject).collect(Collectors.toList());
    }

    private YourObject toYourObject(Object[] columns) {
        return new YourObject((int)columns[0], (String)columns[1]);
    }

}

class YourObject {

    private int number;
    private String name;
    
    public YourObject(int number, String name) {
       this.number = number;
       this.name = name;
    }
    // getters/setters or use Lombok
}

If you have any parameters in your function then your query should look like this,

List<Object[]> columns = entityManager
     .createStoredProcedureQuery("your_sql_func")
     .registerStoredProcedureParameter("param1Name", String.class, ParameterMode.IN)
     .setParameter("param1Name", param1Value)
     .getResultList();

You could use native query as well,

List<Object[]> columns = entityManager
      .createNativeQuery("SELECT * FROM your_sql_func(:param1Name)")
      .setParameter("param1Name", param1Value)
      .getResultList();

Call database function with Spring

2 Answers2

Linked