Data Source Connection with RESTful Web Services

Objective

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

Restful Web Services in AEM.
Swagger Implementation of Services ( YAML/JSON file).
Creation of Data Source with YMAL File.
Form Data Model Creation Using Restful Service Data Source
Adaptive Form Creation Using Form Data Model.

Introduction

We found many cases in AEM Form development where we used a Restfull web service to store and retrieve form data. We can use it to create a data source with the help of a swagger specification. This method helps us to use restful web services as an end point within or outside of AEM. So in this post, we will create a data source and a form data model with the help of restful web services and, using that form data model, we will create an adaptive form.

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

Restful Web Services in AEM

RESTful Web Services are basically REST Architecture based Web Services. In the REST architecture, everything is a resource. RESTful web services are light-weight, highly scalable, maintainable and are very commonly used to create APIs for web-based applications. We can use Swagger to create REST-based APIs with the help of the Swagger Specification. It is a set of open-source tools.

For this Article we will use AEM Servlet as Restful we services there we will implement doGet() and doPost() Methods to fetch and insert Scholar data from database. You are free to choose different Rest Web Services as per your needs for data source connection.

AEMScholarServlet.java

				
					package com.adobe.learning.core.servlets;

import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.servlets.ServletResolverConstants;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.osgi.framework.Constants;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.Servlet;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.Map;

@Component(service = Servlet.class, property = {Constants.SERVICE_DESCRIPTION + "=AEM Scholar Servlet for Data Source and FDM",
        ServletResolverConstants.SLING_SERVLET_PATHS + "=/bin/aemScholarServlet"})
public class AEMScholarServlet extends SlingAllMethodsServlet {

    private static final long serialVersionUID = 1L;
    protected static final Logger LOGGER = LoggerFactory.getLogger(AEMScholarServlet.class);

    @Reference
    DataSource dataSource;

    /***
     * Get Method to fetch scholar details with ID
     * @param request  Fetch ID from request parameters.
     * @param response Write JSON Response of Data.
     */
    @Override
    protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response) {
        try {
            int id = Integer.parseInt(request.getParameter("id"));
            JSONArray jsonArrayData = getSingleScholarData(id);
            response.setContentType("application/json");
            response.setCharacterEncoding("UTF-8");
            response.getWriter().write(String.valueOf(jsonArrayData));

        } catch (IOException e) {
            LOGGER.error("Error in response writing :{}", e.getMessage());
        }
    }

    /***
     * Post Method to update scholar data in MYSQL Database.
     * @param request  Fetch all Form parameter to insert.
     * @param response Response as true after successful insertion.
     */
    @Override
    protected void doPost(SlingHttpServletRequest request, SlingHttpServletResponse response) {

        LOGGER.info("### Inside doPost");
        Connection connection = getConnection();
        try (PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO AEM_SQL.SCHOLAR (ID, NAME, DOB, GENDER, GRADE) VALUES (?, ?, ?, ?, ?)")) {

            Map<String, String[]> allParameter = request.getParameterMap();
            Map.Entry<String, String[]> paramEntry = allParameter.entrySet().iterator().next();
            String allValues = paramEntry.getKey();
            JSONObject jsonObject = new JSONObject(allValues);

            preparedStatement.setInt(1, jsonObject.getInt("id"));
            preparedStatement.setString(2, jsonObject.getString("name"));
            preparedStatement.setString(3, jsonObject.getString("dob"));
            preparedStatement.setString(4, jsonObject.getString("gender"));
            preparedStatement.setString(5, jsonObject.getString("grade"));
            preparedStatement.executeUpdate();
            connection.commit();

            response.setContentType("application/json");
            response.setCharacterEncoding("UTF-8");
            response.getWriter().write(String.valueOf(true));
        } catch (IOException | SQLException | JSONException e) {
            LOGGER.error("Error in response writing :{}", e.getMessage());
        }
    }

    /***
     * Return JSON Array Data for specific ID
     * @return Return all JSON Array Data
     * @param id Take input as ID
     */
    public JSONArray getSingleScholarData(int id) {
        LOGGER.debug("### Inside my getData of aem_sql.scholar");
        JSONArray jsonArray = new JSONArray();
        Connection connection = getConnection();
        try (PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM AEM_SQL.SCHOLAR WHERE ID = ?")) {
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            jsonArray = getJsonData(resultSet);
        } catch (SQLException | JSONException e) {
            LOGGER.error("SQL Exception Occurred :{}", e.getMessage());
        }
        return jsonArray;
    }

    /***
     * Getting the connection to Data Source MYSQL.
     * @return the connection object.
     */
    public Connection getConnection() {
        LOGGER.debug("### Getting Connection :");
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            LOGGER.debug("Got connection");
        } catch (Exception e) {
            LOGGER.debug("Not able to get connection {}", e.getMessage());
        }
        return connection;
    }

    /***
     *  Fetch data in form of JSON Array from resultSet.
     * @param resultSet Take result of executed query.
     * @return Data in form of JSON Array.
     * @throws SQLException Throw SQL Exception.
     * @throws JSONException Throw JSON Exception.
     */
    public JSONArray getJsonData(ResultSet resultSet) throws SQLException, JSONException {
        JSONArray jsonArray = new JSONArray();
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        while (resultSet.next()) {
            int numColumns = resultSetMetaData.getColumnCount();
            JSONObject jsonObject = new JSONObject();
            eachRowData(numColumns, resultSet, resultSetMetaData, jsonObject);
            jsonArray.put(jsonObject);
        }
        return jsonArray;
    }

    /***
     * Fetch SQL data for all Column and Put in JSON Object.
     * @param numColumns Column in SQL Table.
     * @param resultSet Result of Executed Query.
     * @param resultSetMetaData ResultSet Metadata
     * @param jsonObject New Json Object
     * @throws SQLException Throw SQL Exception
     * @throws JSONException Throw JSON Exception
     */
    public void eachRowData(int numColumns, ResultSet resultSet, ResultSetMetaData resultSetMetaData, JSONObject jsonObject) throws SQLException, JSONException {
        for (int i = 1; i < numColumns + 1; i++) {
            String column_name = resultSetMetaData.getColumnName(i);
            if (resultSetMetaData.getColumnType(i) == Types.ARRAY) {
                jsonObject.put(column_name, resultSet.getArray(column_name));
            } else if (resultSetMetaData.getColumnType(i) == Types.BOOLEAN) {
                jsonObject.put(column_name, resultSet.getBoolean(column_name));
            } else if (resultSetMetaData.getColumnType(i) == Types.DOUBLE) {
                jsonObject.put(column_name, resultSet.getDouble(column_name));
            } else if (resultSetMetaData.getColumnType(i) == Types.FLOAT) {
                jsonObject.put(column_name, resultSet.getFloat(column_name));
            } else if (resultSetMetaData.getColumnType(i) == Types.INTEGER) {
                jsonObject.put(column_name, resultSet.getInt(column_name));
            } else if (resultSetMetaData.getColumnType(i) == Types.NVARCHAR) {
                jsonObject.put(column_name, resultSet.getNString(column_name));
            } else if (resultSetMetaData.getColumnType(i) == Types.VARCHAR) {
                jsonObject.put(column_name, resultSet.getString(column_name));
            } else if (resultSetMetaData.getColumnType(i) == Types.SMALLINT) {
                jsonObject.put(column_name, resultSet.getInt(column_name));
            } else if (resultSetMetaData.getColumnType(i) == Types.DATE) {
                jsonObject.put(column_name, resultSet.getDate(column_name));
            } else if (resultSetMetaData.getColumnType(i) == Types.TIMESTAMP) {
                jsonObject.put(column_name, resultSet.getTimestamp(column_name));
            } else {
                jsonObject.put(column_name, resultSet.getObject(column_name));
            }
        }
    }
}

In this class, We are using DataSource Object that provide connection to MYSQL. You can refer – Data Source Connection with MYSQL (Relational Databases) for more details on MYSQL and DataSurce.
Create scholar database table(id, name dob, gender and grade) to store new scholar details.

Swagger Implementation of Services ( YAML/JSON file)

Swagger is a set of open source tools for documenting REST-based APIs.
Simplify API development for users, teams, and enterprises with the Swagger open source and professional toolset.
Swagger offers a tool called the Swagger Editor which helps us design our APIs based on the OpenAPI specification.

Swagger Specification for AEMScholarServlet.java

Navigate to Swagger Editor
Modify the content as per Restful web service for GET and POST Services.

ScholarSwagger.ymal

				
					swagger: '2.0'
info:
  description: AEM Scholar SQL Swagger API
  version: 1.0.0
  title: AEM Scholar Swagger API
schemes:
  - http
produces:
  - application/json
consumes:
  - application/json
host: 'localhost:4502'
paths:
  /bin/aemScholarServlet:
    get:
      summary: Fetch Scholar Data with ID
      produces:
        - application/x-www-form-urlencoded
      consumes:
        - application/json
      parameters:
        - in: query
          name: id
          type: integer
          required: true
      responses:
        '200':
          description: All Scholar Details
          schema:
            type: array
            items:
              $ref: '#/definitions/Scholar'
        '405':
          description: Invalid Input
    post:
      summary: Add New Scholar
      operationId: addNewScholar
      consumes:
        - application/x-www-form-urlencoded
      produces:
        - application/json
      parameters:
      - in: "body"
        name: "body"
        description: "Add New Scholar in MYSQL Database"
        required: true
        schema:
          $ref: "#/definitions/Scholar"
      responses:
        '200':
          description: Scholar Details Added to MYSQL
        '405':
          description: Invalid Input
definitions:
  Scholar:
    type: object
    properties:
      id:
        type: integer
      name:
        type: string
      dob:
        type: string
      gender:
        type: string
      grade:
        type: string

Creation of Data Source with YMAL File

Cloud Configurations should be checked for folder in Configuration Browser( Tools –> General –> Configuration Browser). If not checked edit Configuration Properties.
Now, Navigate to Data Sources(Tools –> Cloud Services –> Data Sources) – http://localhost:4502/libs/fd/fdm/gui/components/admin/fdmcloudservice/fdm.html/conf
Select folder and click on create.
Give proper title and select service type as Restful web service, Click on next.
Browse ScholarSwagger.ymal file and select Basic Authentication.
Fill username and password and Click on create.
That’s it, Data Source with Restful web services created successfully.

Form Data Model Creation Using Restful Service Data Source

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, Select Data Source Configuration Folder and Click on Next.
Select Restful Service (e.g. Scholar Data Source) and create it.
Edit the Form Data Model, Select scholar 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.

Adaptive Form Using Form Data Model

Navigate to Adaptive form folder and start form creation using blank template.
Select the Form Data Model (Scholar FDM) and create the form.
Now Edit the form and drag Scholar in root panel.
Add submit button in the form.
Edit form container to configure submission.
Select Submit Action as “Submit Using Form Data Model” and Data Model as “Scholar 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

Conclusion

So in this post, we have covered the Data Source Creation with the help of Restful Web Services and using that Data Source we can create Form Data Model, Adaptive Form etc.
I hope you enjoyed this post. Your suggestions and feedback are always welcome.

M	T	W	T	F	S	S
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30