I'm working on a business requirement that involves 3 tables: We have 3 database tables
- PRODUCTS ( 60 columns )
- CATEGORIZED_PRODUCTS ( 60 columns ): copy matched records from table PRODUCTS
- CATEGORIES
Whenever user creates a new category, we have to query for list of products from table PRODUCTS that matched with that category, Matched condition is defined by a list of filter columns from table PRODUCTS e.g: Color is yellow, weight is less than 10kg, etc...
Then we will use primary keys from returned result from table PRODUCTS, and create a batch INSERT into table CATEGORIZED_PRODUCTS
Basically, we have 3 SQL statement:
No. 1> Query for matched records from table PRODUCTS (This one is done by another API endpoint)
No. 2> Create new record in table Category
No. 3> Create records from table PRODUCTS into table CATEGORIZED_PRODUCTS
The problem is, the API that query for matched PRODUCTS took 22 seconds to response with 25000 records and that API from another project. But we (i.e: multiple Microservices APIs) share the same Database.
We cannot use Transaction (e.g @Transactional) here, because that would make end-user to wait for too long.
We need to provide a smooth front-end behavior to end-user, so that, we don't want to let them wait. Then I can only think about using a back-end non-blocking asynchronous mechanism that Backend API returns response right after the creation of Category (i.e: at this point, we skip No. 2 & 3 above )
Then, using Asynchronous (with retry 3 times in case of failure) for No. 2 & 3, then
Query for matched from table PRODUCTS, then
Create records which primary keys received from table PRODUCTS into table CATEGORIZED_PRODUCTS with 1 SQL statement.
We are using Spring Boot, and Spring JPA, Java 12, MySQL. Please suggest me some guideline to implement for using non-blocking asynchronous for this case.
Using an independent batch job project is not an option, because at the moment, it is out of scope for the timespan (due to corporate management) and also for the infrastructure of the system. We are also NOT able to create stored procedure or function in database.
PS: In case of "update" a CATEGORY, we have to delete records in table CATEGORIZED_PRODUCTS then, we process No. 2 & 3 again.
In case of "delete" a CATEGORY, we have to delete records in table CATEGORIZED_PRODUCTS then, we process No. 2 & 3 again.