Prerequisites

Before you begin, ensure that you have the following:

Step 1: Configure MySQL database as data source

You can configure different types of data sources to create a form data model. For this tutorial, we will configure the MySQL database that you configured and populated with sample data. For information about other supported data sources and how to configure them, see AEM Forms Data Integration.

Do the following to configure your MySQL database:

  1. Install JDBC driver for MySQL database as an OSGi bundle:

    1. Log in to AEM Forms Author Instance as an administrator and go to AEM web console bundles. The default URL is http://localhost:4502/system/console/bundles.

    2. Tap Install/Update. An Upload / Install Bundles dialog appears.

    3. Tap Choose File to browse and select the MySQL JDBC driver OSGi bundle. Select Start Bundle and Refresh Packages, and tap Install or Update. Ensure that the Oracle Corporation’s JDBC Driver for MySQL is active. The driver is installed.

  2. Configure MySQL database as a data source:

    1. Go to AEM web console at http://localhost:4502/system/console/configMgr.

    2. Locate Apache Sling Connection Pooled DataSource configuration. Tap to open the configuration in edit mode.

    3. In the configuration dialog, specify the following details:

      • Datasource name: You can specify any name. For example, specify WeRetailMySQL.
      • DataSource service property name: Specify name of the service property containing the DataSource name. It is specified while registering the data source instance as OSGi service. For example, datasource.name.
      • JDBC driver class: Specify Java class name of the JDBC driver. For MySQL database, specify com.mysql.jdbc.Driver.
      • JDBC connection URI: Specify connection URL of the database. For MySQL database running on port 3306 and schema weretail, the URL is: jdbc:mysql://[server]:3306/weretail?autoReconnect=true&useUnicode=true&characterEncoding=utf-8
      • Username: Username of the database. It is required to enable JDBC driver to establish a connection with the database.
      • Password: Password of the database. It is required to enable JDBC driver to establish a connection with the database.
      • Test on Borrow: Enable the Test on Borrow option.
      • Test on Return: Enable the Test on Return option.
      • Validation Query: Specify a SQL SELECT query to validate connections from the pool. The query must return at least one row. For example, select * from customerdetails.
      • Transaction Isolation: Set the value to READ_COMMITTED.

      Leave other properties with default values and tap Save.

    A configuration similar to the following is created.

    relational-database-data-source-configuration

Step 2: Create form data model

AEM Forms provides an intuitive user interface to create a form data model from configured data sources. You can use multiple data sources in a form data model. For our use case, we will use the configured MySQL data source.

Do the following to create form data model:

  1. In AEM author instance, navigate to Forms > Data Integration s.

  2. Tap Create > Form Data Model.

  3. In the Create Form Data Model dialog, specify a name for the form data model. For example, customer-shipping-billing-details. Tap Next.

  4. The select datasource screen lists all configured data sources. Select WeRetailMySQL data source and tap Create.

    data-source-selection

The customer-shipping-billing-details form data model is created.

Step 3: Configure form data model

Configuring form data model involves:

  • adding data model object and services
  • configuring read and write services for data model objects

Do the following to configure the form data model:

  1. On AEM author instance, navigate to Forms > Data Integrations. The default URL is http://localhost:4502/aem/forms.html/content/dam/formsanddocuments-fdm.

  2. The customer-shipping-billing-details form data model you created earlier is listed here. Open it in edit mode.

    The selected data source WeRetailMySQL is configured in the form data model.

    default-fdm

  3. Expand the WeRailMySQL data source tree. Select the following data model objects and services from weretail > customerdetails schema to form data model:

    • Data model objects:

      • id
      • name
      • shippingAddress
      • city
      • state
      • zipcode
    • Services:

      • get
      • update

    Tap Add Selected to add selected data model objects and services to the form data model.

    weretail-schema

    NOTE
    The default get, update, and insert services for JDBC datasources are provided out-of-the-box with form data model .
  4. Configure read and write services for the data model object.

    1. Select the customerdetails data model object and tap Edit Properties.

    2. Select get from the Read Service drop-down. The id argument, which is the primary key in the customerdetails data model object is added automatically. Tap aem_6_3_edit and configure the argument as follows.

      read-default

    3. Similarly, select update as the Write Service. The customerdetails object is added as an argument automatically. The argument is configured as follows.

      write-default

      Add and configure the id argument as follows.

      id-arg

    4. Tap Done to save the data model object properties. Then, tap Save to save the form data model.

      The get and update services are added as default services for the data model object.

      data-model-object

  5. Go to the Services tab and configure get and update services.

    1. Select the get service and tap Edit Properties. The properties dialog opens.

    2. Specify the following in the Edit Properties dialog:

      • Title: Specify title of the service. For example: Retrieve Shipping Address.

      • Description: Specify description containing detailed functioning of the service. For example:

        This service retrieves shipping address and other customer details from MySQL database

      • Output Model Object: Select schema containing customer data. For example:

        customerdetail schema

      • Return array: Disable the Return array option.

      • Arguments: Select argument named ID.

      Tap Done. Service to retrieve customer details from the MySQL database is configured.

      shiiping-address-retrieval

    3. Select the update service and tap Edit Properties. The properties dialog opens.

    4. Specify the following in the Edit Properties dialog:

      • Title: Specify title of the service. For example, Update Shipping Address.

      • Description: Specify description containing detailed functioning of the service. For example:

        This service updates shipping address and related fields in MySQL database

      • Input Model Object: Select schema containing customer data. For example:

        customerdetail schema

      • Output type: Select BOOLEAN.

      • Arguments: Select argument named ID and customerdetails.

      Tap Done. The update service to update customer details in the MySQL database is configured.

      shiiping-address-update

The data model object and services in the form data model are configured. You can now test the form data model.