Best practices to create a database "query" service?

[jorgemoralespou_2]

Hi,

I'm involved in a project where they are using SwitchYard, mostly to do database querying, insertions and updating. For this service there are some different possibilities or approaches, so I would like recommendations on them:

Query service:

As I've seen already, JPA reference binding stores JPA entities in Database, so I can not use it. The same with SQL reference binding. So as it is a query service, no "bundled" reference service can be used.

Service Bindings (JPA and SQL), retrieves entities from a database, but it seems that it is more like a polling rather than a querying.

Left options I see are using Camel components (with sql in the route, no jpa) or using Java code.

I would like to do something like: Getting a request from soap, from the id parameter from the request, get the Entity from database and return it.

Update service:

For this I see same restrictions and options.

I would like to do something like: Getting a request from soap, from the id parameter from the request, get the Entity from database, modify it with data from the SOAP payload and store/update it on DB and return it (or something else like an OK).

Insertion service:

This can be done with the JPA reference binding.

Enrichment service:

For this I would like to do some multiple retrievals of entities from Database, and multiple updates. For this I only find Java code as an option.

Probably using some external capability like Infinispan (JBDG) (and use it as an Enpoint) or Red Hat Data Virtualization could be a better fit for this kind of services.

Comments will be more than welcome.

[kcbabo]

SQL reference bindings can satisfy the query service requirement unless you need to accept arbitrary SQL to execute at runtime.  Of course, there are other things to worry about if you are arbitrarily executing SQL at runtime.

For the update service, you could use SQL as well.

Sort of depends on what you need to do in the enrichment service.  If you have a good idea how the enrichment logic can be broken down into independent service calls, then I would model it and then look to see how each call maps to a binding and what the data objects look like.  If it's unclear what the service view would be, then start with Java code and then take a step back and see if the code would benefit from being broken out into discrete services.

[jorgemoralespou_2]

Hi Keith,

Thanks for your answer. The key thing is that these kind of services often benefict from caching, whether is from the JPA provider or a first level cache provided via ISPN/JBDG. I wouldn't like to be hitting the database for every request if it is not necessary.

I think that probably doing everything in a Java Component and only using SwitchYard to expose this services is a better pattern than using Database as a reference service.

Of course, this assertion depends on the desired goal of simplicity against performance.

In fact, I don't see any benefit of doing this service in SwitchYard. Maybe doing this service as an WS/EJB and accesing through SwitchYard if you need things like, transformation, throttling, rtGovernance,...