topic Re: Talk directly with Alfresco database in Java Backed Webscript in Alfresco Archive

Talk directly with Alfresco database in Java Backed Webscript

sihnu — Tue, 26 Jan 2016 10:39:52 GMT

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-

Re: Talk directly with Alfresco database in Java Backed Webscript

romschn — Wed, 27 Jan 2016 05:27:25 GMT

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

Re: Talk directly with Alfresco database in Java Backed Webscript

sihnu — Wed, 27 Jan 2016 10:52:52 GMT

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.

Re: Talk directly with Alfresco database in Java Backed Webscript

muralidharand — Wed, 27 Jan 2016 16:32:30 GMT

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.

Re: Talk directly with Alfresco database in Java Backed Webscript

sihnu — Mon, 01 Feb 2016 12:59:56 GMT

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-2012-berlin. 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.

Re: Talk directly with Alfresco database in Java Backed Webscript

douglascrp — Mon, 01 Feb 2016 16:39:25 GMT

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/social_tops/webscript-context.xml

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/social_tops/webscript-context.xml#L7

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/webscript/DefinitionsGet.java#L21-L23

Re: Talk directly with Alfresco database in Java Backed Webscript

sihnu — Fri, 05 Feb 2016 12:07:26 GMT

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!