Migration of data from MySQL tables to JCR

[AEMexplorer]

I am new to AEM and will be working with AEM as a cloud service version for a migration project where the current implementation is not on AEM. In the current implementation, MySQL DB is used to store static data that is referred by the workflows to drive business logic. It is not the content data that has to be displayed on the website.

I am trying to understand if it is possible to migrate such data into JCR or if JCR is purely for the content that has to be displayed on the website? 

Is there any other service/product within the AEM as a cloud service that can be used instead of MySQL DB?

[SantoshSai]

Hi @AEMexplorer ,

It depends on the use case and other aspects - Unfortunately, in AEM there is no such OOTB functionality which can be used to convert MySQL DB to JCR. Though, to achieve this 

• Implement custom component/service which queries data from MySQL and then store the data in JCR using JCR NODE API [0]
• AEM allows you to establish a connection using DataSourcePool, please refer documentation [1]

OR

• You can also retrieve the data from the database as mentioned above then you can encode the data into JSON or XML and use that data to drive your business logic instead of saving in JCR. So it does not matter if the data is retrieved from a Relational Database or the JCR - either way - you have to retrieve it and process your business.

[0]: https://developer.adobe.com/experience-manager/reference-materials/spec/jsr170/javadocs/jcr-2.0/java...

[1]: https://experienceleague.adobe.com/docs/experience-manager-learn/cloud-service/networking/examples/s...

Hope that helps!

Regards,

Santosh

[tusharbias · Accepted solution]

On CS, there are couple of ways to do that:

1. You can create your own custom solution - service/scheduler to fetch the data from MySql. You can easily create DB connections and fetch the data. Refer below for more details: (it is a 6.5 documentation but majorly it will work) https://experienceleague.adobe.com/docs/experience-manager-65/communities/administer/mysql.html?lang...  

** If you don't have assets then this will work without any issue.

2. If you have also have assets in your DB, then first approach will not work as you cannot create binary from API within AEM. So the solution is stand alone application. i.e. You can create any stand alone application be it on Node, Tomcat, or any other webserver. Connect AEM using Token Based Authentication and from this external application you should read the data from DB and store it in AEM. 

Here is the sample application created on NodeJS to migrate assets, you can create similar application based on your need.

You should also refer (for assets) - https://experienceleague.adobe.com/docs/experience-manager-cloud-service/content/assets/admin/develo...