Data Source Connection with MYSQL (Relational Databases)

Contents

Objective

After reading this Article, You should have an Understanding of –

  • MySQL database and MySQL workbench.
  • Apache Sling Connection Pooled Data Source Configuration.
  • Form Data Model Creation Using RDBMS Data Source.
  • Adaptive Form Creation Using Form Data Model.

Introduction

In real life we came across so many time to fill the forms like Registration form, Application form etc. Once we fill it and click on submit, There can be many submit action as Send as Email, invoke a workflow etc. One of submit action is saving the data in RDBMS with Form Data Model. So here we will integrate database with AEM and successfully save form data in MYSQL Database.

Hence, without any further delay,  let’s get started :-

MYSQL Setup

      MYSQL Installation

  •  Navigate to MySQL Installer – https://dev.mysql.com/downloads/installer/
  • Select the Operation System and download it.
  • I will use window Operation system for this article, Double click on downloaded file and select Custom Installation Type.
  • Select MYSQL Server, MYSQL Workbench, MYSQL shell and continue the installation.
  • Select the default port 3306, recommended root password as “admin” (we can set different port and password as well) and complete the installation.

      Database and Table Creation

  •  In Window search type “MYSQL Workbench” and open it. Enter the default root password as “admin”.
  • we will see default schema as “sys”.
  • Right click on schema and create new schema (“aem_sql”).
  • Select and expand the schema, Right click on table and create new table (“student”).
  •  Now, we have successfully created student table, where we will store our form data.
  • Below are attachment for database and table creation.
Schema Creation
Schema Creation
Table Creation
Table Creation
Table Script
SQL Script

Configure Data Source

 There are many way to configure data source in AEM, In this article we will use Apache Sling Connection Pooled DataSource configuration properties through the AEM Felix Console (/system/console/configMgr).

  • Prerequisite is to deploy the appropriate MySQL drivers to AEM.
  • If you are facing issue to resolve connector/j dependency then Embed in All pom.xml as below :
  • Dependency in Core pom.xml
				
					<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>8.0.28</version>
	<exclusions>
		<exclusion>
			<groupId>com.google.protobuf</groupId>
			<artifactId>protobuf-java</artifactId>
		</exclusion>
	</exclusions>
</dependency>
				
			
  • Embed the dependency in All pom.xml
				
					<embedded>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<target>/apps/learning-packages/application/install</target>
</embedded>
				
			
  • Now, Navigate to configMgr  search for “Apache Sling Connection Pooled DataSource” and configure specific to your MYSQL Database.
  • For this article fill the following details :
    1. DataSource Name : Suitable Name (e.g. MYSQL Forms)
    2. Data Source Service : datasource.name
    3. JDBC Driver Class : com.mysql.cj.jdbc.Driver
    4. JDBC Connection URL : jdbc:mysql://localhost:3306/aem_sql?autoReconnect=true&useSSL=false&characterEncoding=utf8&useUnicode=true (3306 is localhost port and sql-aem is the schema in MYSQL)
    5. User Name : root
    6. Password : Password for User (e.g. admin)
Connection Pool
Connection Pool

Create Form Data Model

  • Navigate to Form Data Integration – http://localhost:4502/aem/forms.html/content/dam/formsanddocuments-fdm
  • Click on Create and select Form Data Model.
  • Give Proper FDM Title and Click on Next.
  • Select RDBMS Data Source (e.g. MYSQL Forms) and create it.
  • Select table data and service in Model.
  • Select the Model and click on edit properties to configure default read and write service and save it.
  • Select the service and click on edit properties to configure all required field and save the model.
  • Select the service and click on Test Service. It should successfully fetch and insert data in table.
Student FDM
FDM with Student Table
FDM INSERT Service
Testing Insert Service
FDM GET Service
Testing Get Service

Adaptive Form Using Form Data Model

  • Navigate to Adaptive form folder and start form creation using blank template.
  • Select the Form Data Model (Student FDM) and create the form.
  • Now Edit the form and drag student in root panel.
  • Add submit button in form.
  • Edit form container to configure submission.
  • Select Submit Action as “Submit Using Form Data Model” and Data Model as “Student FDM”
  • Save all the change and Navigate to Preview Mode.
  • Fill all data and submit the form, that’s all and data should successfully save in MYSQL Database.
Adaptive Form
Student Forms
Student Submit Action
Student Submit Action
Student Form Submission
Student Form Submission

Conclusion

  • So in this post, we have covered the Data Source Connection with MYSQL. I hope you enjoyed this post. Your suggestions and feedback are always welcome.

2 Comments

  1. Andy Charronsays:

    I tried this with the Cloud Service SDK but didn’t see the ‘Apache Sling Connection Pooled DataSource’ in ConfigMgr. There was a config titled ‘Day Commons JDBC Connections Pool’, but configuring that didn’t surface the Data Source when I tried to create a Data Model in Forms… does Cloud Service support MySQL connections?

Leave a Reply

Your email address will not be published.