[JPA and stored procedure] #JPA #SQL

jap_stored_procedure.md

CREATE OR REPLACE FUNCTION backup_order(
  IN created_from TIMESTAMP,
  IN created_to TIMESTAMP,
  OUT backup_total BIGINT)
// ...


@Entity
@Table("order")
@NamedStoredProcedureQueries({
    @NamedStoredProcedureQuery(
        name="backupOrder,
        procedureName = "backup_order",
        parameters = {
            @StoredProcedureParameter(
                mode = ParameterMode.IN,
                type = java.util.Date.class,
                name = "created_from"),
            @StoredProcedureParameter(
                mode = ParameterMode.IN,
                type = java.util.Date.class, // error if java.time.Instant
                name = "created_to")),
            @StoredProcedureParameter(
                mode = ParameterMode.OUT,
                type = Long.class,           // "BIGINT" maps to 'Long'
                name = "backup_total"),
        }
    )
})
public class Order {
//..
}


@Repository
public interface OrderRepository extends JpaRepository<Order, UUID> {
  @Procedure(name = "backupOrder") // Logic name defined above
  long backupOrder(
      @Param("created_from") java.util.Date orderCreatedFrom,
      @Param("created_to") java.util.Date orderCreatedTo);
}