0

I have a custom Stored Procedure class which is extending jdbc.StoredProcedure but I have annotated this class with Spring @Component to bring this class bean into Spring context.

Why I am doing this?

  1. I wanted to add spring-retry on execute method which will work only on spring components
  2. I wanted to reused the compiled StoredProcedure instead of creating a new object and recompiling every time, in this way I can reuse the compiled StoredProcedure every time.

anything wrong with this kind of implementation? are there any issues we may see with this Spring component based StoredProcedure?

Ex:

    @Component
public class ExampleStoredProcedure extends StoredProcedure {
  @Autowired
  private DataSource dataSource;

  @Postconstruct
  public void init() {
     super.setDataSource(dataSource);
     setSql("stored_procedure_name");
//TODO      declare parameters
     compile();
  }

  public void execute(){
    //Todo set all parameters to ParameterSource
    super.execute(parameterSource);
  }
}
Jaini Naveen
  • 155
  • 1
  • 17
  • Why don't you just use something that is tested, documented, like this: https://www.baeldung.com/spring-data-jpa-stored-procedures and risk reinventing it without understanding transactions, race conditions, etc. – JohannesB Apr 18 '20 at 10:27
  • 1. Generally I don’t care about transaction management since it will be handled at DB level since it is a stored procedure 2. Are you referring race conditions on data source connections? – Jaini Naveen Apr 19 '20 at 23:06
  • 1
    Yes, see the comments to the answer here for more info https://stackoverflow.com/a/1531103/6250649 and even if your particular database driver is thread safe by locking it might be a bottleneck because only a single thread can call your stored procedure at a time – JohannesB Apr 20 '20 at 05:32

1 Answers1

0

Try implementing a layered application architecture where you annotate your services with spring retry like this example:

https://dzone.com/articles/spring-retry-way-to-handle-failures

These service methods can define transaction boundaries and call your data persistence layers methods that could be based on spring data's standardized ways to call stored procedures and manage your database connections etc.

See for more info on Spring and architecture for example this brief introduction:

https://www.petrikainulainen.net/software-development/design/understanding-spring-web-application-architecture-the-classic-way/

JohannesB
  • 2,214
  • 1
  • 11
  • 18