javaspringspring-bootspring-datajava-stored-procedures

Spring Boot - more cleaner or elegant way to pass multiple parameters to stored procedure spring data repository


how pass multiple parameters as DTO or Entity class in arguments method of Repository interface Spring Data

i have one procedure that receive multiple parameters of type IN and i have issue with my code in SonarQube Methods should not have too many parameters

My Entity:

@Entity
@NamedStoredProcedureQuery(name = "MySPName", procedureName = "MySPName", parameters = {
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "parameter1", type = String.class),
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "parameter2", type = String.class),
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "parameter3", type = String.class),
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "parameter4", type = String.class),
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "parameter5", type = String.class),
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "parameter6", type = String.class),
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "parameter7", type = String.class),
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "parameter8", type = String.class),
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "parameter9", type = String.class),
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "parameter10", type = String.class),
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "parameter11", type = String.class),
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "parameter12", type = String.class),
  @StoredProcedureParameter(mode = ParameterMode.IN, name = "parameter13", type = String.class),
  @StoredProcedureParameter(mode = ParameterMode.OUT, name = "resultCode", type = Integer.class),
  @StoredProcedureParameter(mode = ParameterMode.OUT, name = "resultMessage", type = String.class),
  @StoredProcedureParameter(mode = ParameterMode.OUT, name = "name", type = String.class)
})
public class MyEntitySP implements Serializable {

  @Id
  @Column(name = "resultCode")
  private Integer resultCode;

  @Column(name = "resultMessage")
  private String resultMessage;

  @Column(name = "name")
  private String name;
}

My Repository:

public class MyRepository extends JpaRepository<MyEntitySP, Integer> {
  @Procedure("MySPName")
  MyEntitySP callSP(@Param("parameter1") String parameter1, @Param("parameter2") String parameter2, @Param("parameter3") String parameter3, @Param("parameter4") String parameter4, @Param("parameter5") String parameter5, @Param("parameter6") String parameter6, @Param("parameter7") String parameter7, @Param("parameter8") String parameter8, @Param("parameter9") String parameter9, @Param("parameter10") String parameter10, @Param("parameter11") String parameter11, @Param("parameter12") String parameter12, @Param("parameter13") String parameter13) 
}

I like anything as maybe Class Parameters:

public class MyParameters {
  @Param("parameter1")
  private String parameter1;

  @Param("parameter2")
  private String parameter2;

  @Param("parameter3")
  private String parameter3;

  @Param("parameter4")
  private String parameter4;

  @Param("parameter5")
  private String parameter5;
 
  @Param("parameter6")
  private String parameter6;

  @Param("parameter7")
  private String parameter7;

  @Param("parameter8")
  private String parameter8;

  @Param("parameter9")
  private String parameter9;

  @Param("parameter10")
  private String parameter10;

  @Param("parameter11")
  private String parameter11;

  @Param("parameter12")
  private String parameter12;

  @Param("parameter13)
  private String parameter13;
}

and maybe Repository:

public class MyRepository extends JpaRepository<MyEntitySP, Integer> {
  @Procedure("MySPName")
  MyEntitySP callSP(MyParameters parameters) 
}

is it possible? - please help me*


Solution

  • It is possible to call a stored procedure using the @Query annotation to execute a native query (see: https://www.baeldung.com/spring-data-jpa-stored-procedures).

    By following that approach and using SPEL (see: https://spring.io/blog/2014/07/15/spel-support-in-spring-data-jpa-query-definitions) then I think you could have something like:

    public class MyRepository extends JpaRepository<MyEntitySP, Integer> {
      @Query("MY_PROCEDURE(:#{#parameters.parameter1}, 
                           :#{#parameters.parameter2}, 
                           ....", nativeQuery = true)
      MyEntitySP callSP(MyParameters parameters) 
    }
    

    I do not see any easy solution when using @Procedure but, being Spring, there may be some custom parameter resolver you can apply