I want to verify: Jdbc dynamic adapters is it recommended or not and when it should be used
Dynamic Adapters will be used if your WHERE statement is completely decided at run time based on your logic / Input pipeline.
It depends upon your requirement and the business logic that will build your where condition. Refer to the adapter documents.
Dynamic SQL adapter has its own advantages and disadvantages. As part of advantages, you can decide on querying the DB real time based on business conditions and helps to build dynamic solution with varying queries. It is not limited where clause only but entire query itself can be passed dynamically.
As part of standard practices and betterment, please try to use other adapter templates provided if that meets your business needs. There are many other adapter templates available Insert/Update/Custom SQL etc. Reason I say this is with Dynamic SQL there is a risk of SQL injection attack, if you aren’t handling your service with proper validation and security.
Again, you can refer the JDBC Adapter Guide for more details about technical implementation of any of these adapters.
Hope this helps.