Announcement Announcement Module
No announcement yet.
Calling native SQL query for updating Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Calling native SQL query for updating

    I continue using Spring Data JPA more and more. Maybe this question is a little more advanced...

    I have a stored procedure in my Postgres database that is called to add a new record to table Car. It is an SP because it encapsulates additional data logic that must always be executed when adding records to this table.

    How do I specify a method in my CarRepository interface that will call this SP? I tried with this:

    @Query("SELECT `Cars`.`CarAddMovement`(?1, ?2, ?3)")
    void addMovement(Long instrument_id,
                     Long account_id,
                     BigDecimal amount);
    This obviously does not work because what's inside the @Query annotation is native SQL and @Query expects JPA's query language (whatever it is called).

    Can I somehow specify native SQL for one of my repository methods? Hopefully via an annotation?

    Will I have to write an implementing class that will manually invoke the native SQL I have written above? Or can this somehow be magically delegated to Spring Data JPA?

    Thanks in advance.

  • #2
    I have worked this out. Here is what I did:
    1. Declare a CarRepositoryCustom interface that has a bunch of methods, including those where I want to use native SQL.
    2. Declare a CarRepositoryImpl class that implements my CarRepositoryCustom interface. In here I inject JPA's EntityManager using a @PersistenceContext annotation, and use it in my method to invoke the native SQL (which in my case is a stored procedure).
    3. When I declare my CarRepository interface, I have it extend both JpaRepository<Car, Long> and CarRepositoryCustom.

    Everything is picked up automatically by following the proper naming conventions. I had started naming my implementing class CarRepositoryCustomImpl, but that broke Spring Data's algorithm for finding a default repository implementation.

    Love Spring Data more and more every day!