Talk directly with Alfresco database in Java Backed Webscript

[sihnu]

Hi,

I would like to do custom queries against Alfresco database without using the services provided by Alfresco API. I need to fetch some metadata from Alfresco tasks. What would be the best way to implement this? I found this blog post http://www.jorambarrez.be/blog/2014/01/17/execute-custom-sql-in-activiti/ which looked helpful but I don't know where I should define the Process Engine configuration for the mapping. Or if there is another solution I would be very keen to hear about it. Thanks in advance.

[romschn]

Just want to understand, Is there any specific reason you do not want to use Alfresco APIs and want to directly hit the database?

[sihnu]

Thanks for your reply. Really appreciate it.

Yes, we want to implement a specified and optimized query to fetch relatively large amount of data from multiple tables. Fetching the data using services is slow and probably gets slower as the time goes on because the amount of data just gets bigger.

[muralidharand]

What is your use case?If it is common case, we can raise enhancement request with alfresco to implement in the next release. So everyone will get befitted.
In general, it is not good to access alfresco database.

[sihnu]

Thank you for your reply.

No, it's not a common use case. It's query for Workflow tasks and very special case. I found this example but I'm having some difficulties to test it out. http://www.slideshare.net/slemarchand/using-mybatis-in-alfresco-custom-extensions-alfresco-devcon-20.... I have implemented the Query now and I'm trying to build a java backed webscript to test the query. I need to provide the sql session factory for Dao Implementation. How do I get the factory? The factory is defined in Spring configuration, I think… I just don't know how to fetch the factory.

[douglascrp]

Interesting… I didn't know that addon.
Thank you for sharing it.

Back to your question, I think what you need is to inject the factory bean using spring configuration, just like the addon does here:
https://github.com/slemarchand/social-tops/blob/master/social-tops-repo/config/alfresco/module/socia...

In your webscript bean, create a property and inject the factory using a tag like this:
https://github.com/slemarchand/social-tops/blob/master/social-tops-repo/config/alfresco/module/socia...

In the Java code, you have to create a set method just like this:
https://github.com/slemarchand/social-tops/blob/master/social-tops-repo/source/java/social_tops/webs...

[sihnu]

Finally got it working! Just had to familiarize myself a lil bit with the stuff going on there. Following the slides I was finally able to solve it. Thanks to douglascrp for trying to help me!