Şu sürüme ilişkin yardım içeriğini görüntülüyorsunuz:

Sample overview

AEM Forms portal drafts and submissions component allows users to save their forms as drafts and submit later from any device. Also, users can view their submitted forms on portal. To enable this functionality, AEM Forms provides data and metadata services to store the data filled in by a user in the form and the form metadata associated with drafts and submitted forms. This data is stored in the CRX repository, by default. However, as users interact with forms through AEM publish instance, which is generally outside the enterprise firewall, organizations may want to customize data storage for it to be more secure and reliable.

The sample, discussed in this document, is a reference implementation of customized data and metadata services to integrate drafts and submissions component with a database. The database used in the sample implementation is MySQL 5.6.24. However, you can integrate the drafts and submissions component with any database of your choice.

Not:

The examples and configurations explained in this document are according to MySQL 5.6.24 and you must substitute them appropriately for your database system.

Set up and configure the sample

Perform the following steps to install and configure the sample:

  1. Download the following aem-fp-db-integration-sample-pkg-5.1.28.zip package to your file system.

    İndir

  2. Go to AEM package manager at http://[host]:[port]/crx/packmgr/.

  3. Click Upload Package.

  4. Browse to select the aem-fp-db-integration-sample-pkg-5.1.28.zip package and click OK.

  5. Click Install to next to the package to install the package.

  6. Go to AEM Web Console Configuration page at http://[host]:[port]/system/console/configMgr.

  7. Click to open Forms Portal Draft and Submission Configuration in edit mode.

  8. Specify the values for properties as described in the following table:

    Property Description Value
    Forms Portal Draft Data Service Identifier for draft data service draft.data.service formsportal.sampledataservice
    Forms Portal Draft Metadata Service Identifier for draft metadata service draft.metadata.service formsportal.samplemetadataservice
    Forms Portal Submit Data Service Identifier for submit data service submit.data.service formsportal.sampledataservice
    Forms Portal Submit Metadata Service Identifier for submit metadata service submit.metadata.service formsportal.samplemetadataservice

    Not:

    The services are resolved by their names mentioned as value for the aem.formsportal.impl.prop key as follows:

    @Service(value = {SubmitDataService.class, DraftDataService.class})
    @Property(name = "aem.formsportal.impl.prop", value = "formsportal.sampledataservice")
    @Service(value = { SubmitMetadataService.class, DraftMetadataService.class })
    @Property(name = "aem.formsportal.impl.prop", value = "formsportal.samplemetadataservice")

    You can change names of the data and metadata tables.

    To provide a different name for the metadata table:

    • In the Web Console Configuration, find and click Forms Portal Metadata Service Sample Implementation. You can change the values of data source, metadata/additional metadata table name.

    To provide a different name for the data table:

    • In the Web Console Configuration, find and click Forms Portal Data Service Sample Implementation. You can change the values of data source and data table name.

    Note: If you change the table names, provide them in the Form Portal configuration.

  9. Leave other configurations as is and click Save.

  10. The database connection can now be done in either of two ways:

    a) Via Day Commons JDBC Connections Pool

    b) Via Apache Sling Connection Pooled Data Source

  11. For JDBC connection, find and click to open Day Commons JDBC Connections Pool in edit mode in the Web Console Configuration. Specify the values for properties as described in the following table:

    Property Value
    JDBC driver class com.mysql.jdbc.Driver
    JDBC connection URI
    jdbc:mysql://[host]:[port]/[schema_name]
    Username A username to authenticate and perform actions on database tables
    Password Password associated with the username
    Datasource name

    A datasource name for filtering drivers from the data source pool

    Note: The sample implementation uses FormsPortal as the datasource name.

    Not:

    The JDBC driver for MySQL is not provided with the sample. Ensure that you have provisioned for it and provide the required information to configure the JDBC connection pool.

  12. Leave other configurations as is and click Save.

  13. For Apache Sling connection, find and click to open Apache Sling Connection Pooled DataSource in edit mode in the Web Console Configuration. Specify the values for properties as described in the following table:

    Property Value
    Datasource name

    A datasource name for filtering drivers from the data source pool

    Note: The sample implementation uses FormsPortal as the datasource name.

    JDBC driver class com.mysql.jdbc.Driver
    JDBC connection URI
    jdbc:mysql://[host]:[port]/[schema_name]
    Username A username to authenticate and perform actions on database tables
    Password Password associated with the username
    Transaction Isolation READ_COMMITTED
    Max Active Connections 1000
    Max Idle Connections 100
    Min Idle Connections 10
    Initial Size 10
    Max Wait 100000
    Test on Borrow Checked
    Test while Idle Checked
    Validation Query Example values are SELECT 1(mysql), select 1 from dual(oracle), SELECT 1(MS Sql Server) (validationQuery)
    Validation Query timeout 10000
  14. Note: The JDBC driver for MySQL is not provided with the sample. Ensure that you have provisioned for it and provide the required information to configure the JDBC connection pool.

    Leave other configurations as is and click Save.

  15. If you already have a table in the database schema, skip to the next step.

    Otherwise, if you do not already have a table in the database schema, execute the following SQL statements to create separate tables for data, metadata, and additional metadata in the database schema:

    SQL statement for data table

    CREATE TABLE `data` (
    `owner` varchar(255) DEFAULT NULL,
    `data` longblob,
    `metadataId` varchar(45) DEFAULT NULL,
    `id` varchar(45) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    SQL statement for metadata table

    CREATE TABLE `metadata` (
    `formPath` varchar(1000) DEFAULT NULL,
    `formType` varchar(100) DEFAULT NULL,
    `description` text,
    `formName` varchar(255) DEFAULT NULL,
    `owner` varchar(255) DEFAULT NULL,
    `enableAnonymousSave` varchar(45) DEFAULT NULL,
    `renderPath` varchar(1000) DEFAULT NULL,
    `nodeType` varchar(45) DEFAULT NULL,
    `charset` varchar(45) DEFAULT NULL,
    `userdataID` varchar(45) DEFAULT NULL,
    `status` varchar(45) DEFAULT NULL,
    `formmodel` varchar(45) DEFAULT NULL,
    `markedForDeletion` varchar(45) DEFAULT NULL,
    `showDorClass` varchar(255) DEFAULT NULL,
    `sling:resourceType` varchar(1000) DEFAULT NULL,
    `attachmentList` longtext,
    `draftID` varchar(45) DEFAULT NULL,
    `submitID` varchar(45) DEFAULT NULL,
    `id` varchar(60) NOT NULL,
    `profile` varchar(255) DEFAULT NULL,
    `submitUrl` varchar(1000) DEFAULT NULL,
    `xdpRef` varchar(1000) DEFAULT NULL,
    `jcr:lastModified` varchar(45) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ID_UNIQUE` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    SQL statement for additionalmetadatatable

    CREATE TABLE `additionalmetadatatable` (
    `value` text,
    `key` varchar(255) NOT NULL,
    `id` varchar(60) NOT NULL,
    PRIMARY KEY (`id`,`key`),
    CONSTRAINT ‘additionalmetadatatable_fk’ FOREIGN KEY (`id`) REFERENCES `metadata` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    SQL statement for comment table

    CREATE TABLE `commenttable` (
    `commentId` varchar(255) DEFAULT NULL,
    `comment` text DEFAULT NULL,
    `ID` varchar(255) DEFAULT NULL,
    `commentowner` varchar(255) DEFAULT NULL,
    `time` varchar(255) DEFAULT NULL
    );
  16. If you already have the tables (data, metadata, and additionalmetadatatable) in the database schema, execute the following alter table queries:

    SQL statement for altering the data table

    ALTER TABLE `data` CHANGE `owner` `owner` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

    SQL statement for altering the metadata table

    ALTER TABLE metadata add markedForDeletion varchar(45) DEFAULT NULL

    Not:

    The ALTER TABLE metadata add query fails if you have already run it and the markedfordeletion column is present in the table.   

    ALTER TABLE `metadata`CHANGE `formPath` `formPath` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 
    CHANGE `formType` `formType` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    CHANGE `description` `description` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 
    CHANGE `formName` `formName` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 
    CHANGE `owner` `owner` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 
    CHANGE `renderPath` `renderPath` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 
    CHANGE `showDorClass` `showDorClass` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    CHANGE `sling:resourceType` `sling:resourceType` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    CHANGE `profile` `profile` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 
    CHANGE `submitUrl` `submitUrl` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    CHANGE `xdpRef` `xdpRef` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

    SQL statement for altering the additionalmetadatatable table

    ALTER TABLE `additionalmetadatatable` CHANGE `value` `value` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `key` `key` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

The sample implementation is now configured, which you can use to list your drafts and submissions while storing all data and metadata in a database. Let’s now see how data and metadata services are configured in the sample.

Install mysql-connector-java-5.1.39-bin.jar file

Perform the following steps to install the mysql-connector-java-5.1.39-bin.jar file:

  1. Navigate to http://[server]:[port]/system/console/depfinder and search for com.mysql.jdbc package.

  2. In the Exported by column, check if the package is exported by any bundle.

    Proceed if the package is not exported by any bundle.

  3. Navigate to http://[server]:[port]/system/console/bundles and click Install/Update.

  4. Click Choose File and browse to select the mysql-connector-java-5.1.39-bin.jar file. Also, select Start Bundle and Refresh Packages checkboxes.

  5. Click Install or Update. Once complete, restart the server.

  6. (Windows only) Turn off the system firewall for your operating system. 

Sample forms portal data service

The following code shows how the forms portal data service is customized for this sample implementation.

/*************************************************************************
*
* ADOBE CONFIDENTIAL
* ___________________
*
*  Copyright 2015 Adobe Systems Incorporated
*  All Rights Reserved.
*
* NOTICE:  All information contained herein is, and remains
* the property of Adobe Systems Incorporated and its suppliers,
* if any.  The intellectual and technical concepts contained
* herein are proprietary to Adobe Systems Incorporated and its
* suppliers and are protected by trade secret or copyright law.
* Dissemination of this information or reproduction of this material
* is strictly forbidden unless prior written permission is obtained
* from Adobe Systems Incorporated.
**************************************************************************/
package com.adobe.fd.fp.customhandler;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.felix.scr.annotations.Component;
import org.apache.felix.scr.annotations.Property;
import org.apache.felix.scr.annotations.Reference;
import org.apache.felix.scr.annotations.Service;
import com.adobe.fd.fp.exception.FormsPortalException;
import com.adobe.fd.fp.service.DraftDataService;
import com.adobe.fd.fp.service.SubmitDataService;
import com.adobe.granite.resourceresolverhelper.ResourceResolverHelper;
import com.day.commons.datasource.poolservice.DataSourcePool;
 
@Service(value = {SubmitDataService.class, DraftDataService.class})
@Property(name = "aem.formsportal.impl.prop", value = "formsportal.sampledataservice")
@Component
public class FormsPortalSampleDataServiceImpl implements SubmitDataService,DraftDataService {
    /**
     * Table name: data
     * Primary key: id
     * Columns: id, owner, data
     * Data source name: FormsPortal
     */
    @Reference
    private DataSourcePool source;
     
    @Reference
    private ResourceResolverHelper resourceResolverHelper;
     
    // Returns a connection using the configured DataSourcePool
    private Connection getConnection() throws Exception{
        try {
            // Inject the DataSourcePool right here!
            DataSource dataSource = (DataSource) source.getDataSource(FormsPortalConstants.DATA_SOURCE_NAME);
            Connection connection = dataSource.getConnection();
            return connection;
        } catch (Exception e) {
            throw new Exception(e.getMessage(), e);
        }
    }
     
     
    /**
     * To save user data, this method takes
     * 1. id for the userdata (id will be null if you are creating this draft/submission instance for the first time)
     * 2. formName, form's name
     * 3. formdata, actual data to be stored
     * Here to maintain owner's information, this method is internally getting that information and saving it
     *
     *  Leveraging (Insert into ..... On Duplicate Key Update ......) query to insert into table if there is no such instance.  
     *  Returns the data "id"
     */
    public String saveData(String id, String formName, String formdata) throws FormsPortalException {      
        Connection connection = null;
        PreparedStatement prStmt = null;
        try {
            String updateStatement = "INSERT INTO " + FormsPortalConstants.DATA_TABLE + "(id, data, owner)" + " VALUES((?), (?), (?))" +
                    "ON DUPLICATE KEY UPDATE " + "data = (?)";
            connection = getConnection();
            // Setting auto commit false here to maintain atomic transactional behavior
            connection.setAutoCommit(false);
             
            prStmt = connection.prepareStatement(updateStatement);
            String userId = resourceResolverHelper.getResourceResolver().getUserID();
            if((id == null || id.isEmpty()) && userId != null){
                id = getId();
            }
            prStmt.setString(1, id);
            prStmt.setString(2, formdata);
            prStmt.setString(3, userId);
            prStmt.setString(4, formdata);
            prStmt.execute();
             
            /**
             * Committing after all the operations
             */
            connection.commit();           
            return id;
        } catch (Exception e) {
            try {
                /**
                 *  In case of any error, rollback
                 */
                connection.rollback();
            } catch (SQLException e1) {
                throw new FormsPortalException(e.getMessage(), e);
            }
            throw new FormsPortalException(e.getMessage(), e);
        } finally {
            try {
                /**
                 * Close the statement and connection in finally block
                 */
                if(prStmt != null){
                    prStmt.close();
                }
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                throw new FormsPortalException(e.getMessage(), e);
            }
        }
    }
    /**
     * To get data stored against the id
     * This is the same data that we stored in saveData method
     * It takes only "id" as argument
     * Using logged-in user's information to verify if he is the authorized person to view this data
     * Returns byte array of data requested
     */
    public byte[] getData(String id) throws FormsPortalException {
        Connection connection = null;
        PreparedStatement prStmt = null;
        ResultSet resultSet = null;
        try {
            String userId = resourceResolverHelper.getResourceResolver().getUserID();
            connection = getConnection();
             
            String getDataStmt = "SELECT " + FormsPortalConstants.DATA_COLUMN + " from " + FormsPortalConstants.DATA_TABLE + " WHERE id = (?) AND owner = (?)";
            prStmt = connection.prepareStatement(getDataStmt);
            prStmt.setString(1, id);
            prStmt.setString(2, userId);
             
            resultSet = prStmt.executeQuery();         
            resultSet.next();
            byte[] response =  resultSet.getBytes(FormsPortalConstants.DATA_COLUMN);
             
            return response;
        } catch (Exception e) {
            throw new FormsPortalException(e.getMessage(), e); 
        } finally {
            try {
                /**
                 * Close the resultset, statement and connection in finally block
                 */
                if(resultSet != null){
                    resultSet.close();
                }
                if(prStmt != null){
                    prStmt.close();
                }
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                throw new FormsPortalException(e.getMessage(), e);
            }
        }
    }
    /**
     * To delete the data that we have saved in saveData method
     * This method takes only "id" as argument
     * Again using logged-in user's information to verify if he is the authorized person to delete this data
     * Returns the status of delete operation
     */
    public boolean deleteData(String id) throws FormsPortalException {
        Connection connection = null;
        PreparedStatement prStmt = null;
        try {
            String userId = resourceResolverHelper.getResourceResolver().getUserID();
            connection = getConnection();
            connection.setAutoCommit(false);
             
            String deleteStmt = "DELETE FROM " + FormsPortalConstants.DATA_TABLE + " WHERE id = (?) AND owner = (?)";
            prStmt = connection.prepareStatement(deleteStmt);
            prStmt.setString(1, id);
            prStmt.setString(2, userId);
            prStmt.execute();
             
            connection.commit();
            return true;
        } catch (Exception e) {
            try{
                /**
                 * Rollback in case of any error
                 */
                connection.rollback();
            } catch(SQLException e2){
                throw new FormsPortalException(e.getMessage(), e);
            }
            throw new FormsPortalException(e.getMessage(), e);
        } finally {
            try {
                /**
                 * Close the statement and connection in finally block
                 */
                if(prStmt != null){
                    prStmt.close();
                }
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                throw new FormsPortalException(e.getMessage(), e);
            }
        }
    }
    /**
     * Just like the user data, we need to save attachments uploaded alongwith the form.
     * To do so, we provide
     * 1. metadataId associated with the metadata of this draft/submission
     * 2. attachmentBytes, attachment data to be stored
     * Returns the attachment "id"
     */
    public String saveAttachment(String metadataId, byte[] attachmentBytes) throws FormsPortalException {
        Connection connection = null;
        PreparedStatement prStmt = null;
        try {
            String id = getId();
            String userId = resourceResolverHelper.getResourceResolver().getUserID();
            connection = getConnection();
            connection.setAutoCommit(false);
             
            String saveAttachmentStmt = "INSERT INTO " + FormsPortalConstants.DATA_TABLE + "(id, data, owner)" + " VALUES((?), (?), (?))";
            prStmt = connection.prepareStatement(saveAttachmentStmt);
            prStmt.setString(1, id);
            prStmt.setBytes(2, attachmentBytes);
            prStmt.setString(3, userId);
            prStmt.execute();
             
            connection.commit();
            return id;
        }catch(Exception e){
            try{
                /**
                 * Rollback in case of any error
                 */
                connection.rollback();
            } catch(SQLException e2){
                throw new FormsPortalException(e.getMessage(), e);
            }
            throw new FormsPortalException(e.getMessage(), e); 
        } finally {
            try {
                /**
                 * Close the statement and connection in finally block
                 */
                if(prStmt != null){
                    prStmt.close();
                }
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                throw new FormsPortalException(e.getMessage(), e);
            }
        }
         
    }
     
    /**
     * To get the attachment data
     * "id" associated with the attachment is passed as argument
     * Returns byte array of attachment data 
     */
    public byte[] getAttachment(String id) throws FormsPortalException {
        Connection connection = null;
        PreparedStatement prStmt = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();          
             
            String userId = resourceResolverHelper.getResourceResolver().getUserID();
            String getAttachmentStmt = "SELECT " + FormsPortalConstants.DATA_COLUMN + " from " + FormsPortalConstants.DATA_TABLE + " where id = (?) AND owner = (?)";
            prStmt = connection.prepareStatement(getAttachmentStmt);
            prStmt.setString(1, id);
            prStmt.setString(2, userId);
            resultSet = prStmt.executeQuery();
                         
            byte[] result = null;
            if(resultSet.next()){
                result = resultSet.getBytes(FormsPortalConstants.DATA_COLUMN);
            }
            return result;
        }catch(Exception e){
            throw new FormsPortalException(e.getMessage(), e);
        } finally {
            try {
                /**
                 * Close the resultSet, statement and connection in finally block
                 */
                if(resultSet != null){
                    resultSet.close();
                }  
                if(prStmt != null){
                    prStmt.close();
                }
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                throw new FormsPortalException(e.getMessage(), e);
            }
        }
    }
         
    /**
     * To delete the attachment that we have saved in saveAttachment method
     * This method takes only "id" as argument
     * Again using logged-in user's information to verify if he is the authorized person to delete this attachment
     * Returns the status of delete operation
     */
    public boolean deleteAttachment(String id) throws FormsPortalException {
        Connection connection = null;
        PreparedStatement prStmt = null;
        try {
            connection = getConnection();
            connection.setAutoCommit(false);
             
            String userId = resourceResolverHelper.getResourceResolver().getUserID();
            String deleteAttachmentStmt = "DELETE from " + FormsPortalConstants.DATA_TABLE + " where id = (?) AND owner = (?)";
            prStmt = connection.prepareStatement(deleteAttachmentStmt);
            prStmt.setString(1, id);
            prStmt.setString(2, userId);           
            prStmt.execute();
             
            connection.commit();
            return true;
        }catch(Exception e){
            try{
                /**
                 * Rollback in case of any error
                 */
                connection.rollback();
            } catch(SQLException e2){
                throw new FormsPortalException(e2.getMessage(), e2);
            }
            throw new FormsPortalException(e.getMessage(), e); 
        } finally {
            try {
                /**
                 * Close the statement and connection in finally block
                 */
                if(prStmt != null){
                    prStmt.close();
                }
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                throw new FormsPortalException(e.getMessage(), e);
            }
        }
    }
     
    private String getId() {
        return String.valueOf(System.nanoTime());
    }
}

Sample forms portal metadata service

The following code shows how the forms portal metadata service is customized for this sample implementation.

/*************************************************************************
*
* ADOBE CONFIDENTIAL
* ___________________
*
*  Copyright 2015 Adobe Systems Incorporated
*  All Rights Reserved.
*
* NOTICE:  All information contained herein is, and remains
* the property of Adobe Systems Incorporated and its suppliers,
* if any.  The intellectual and technical concepts contained
* herein are proprietary to Adobe Systems Incorporated and its
* suppliers and are protected by trade secret or copyright law.
* Dissemination of this information or reproduction of this material
* is strictly forbidden unless prior written permission is obtained
* from Adobe Systems Incorporated.
**************************************************************************/
package com.adobe.fd.fp.customhandler;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.commons.lang.StringUtils;
import org.apache.felix.scr.annotations.Component;
import org.apache.felix.scr.annotations.Property;
import org.apache.felix.scr.annotations.Reference;
import org.apache.felix.scr.annotations.Service;
import org.apache.sling.commons.json.JSONArray;
import org.apache.sling.commons.json.JSONObject;
import com.adobe.fd.fp.exception.FormsPortalException;
import com.adobe.fd.fp.service.DraftMetadataService;
import com.adobe.fd.fp.service.SubmitMetadataService;
import com.adobe.granite.resourceresolverhelper.ResourceResolverHelper;
import com.day.commons.datasource.poolservice.DataSourcePool;
@Service(value = { SubmitMetadataService.class, DraftMetadataService.class })
@Property(name = "aem.formsportal.impl.prop", value = "formsportal.samplemetadataservice")
@Component
public class FormsPortalSampleMetadataServiceImpl implements SubmitMetadataService, DraftMetadataService {
     
    /**
     * Table name: metadata
     * Primary key: id
     * Columns:
     * Data source name: FormsPortal
     */
    @Reference
    private DataSourcePool source;
         
    @Reference
    private ResourceResolverHelper resourceResolverHelper;
     
    /**
     * 
     * @return a connection using the configured DataSourcePool
     * @throws FormsPortalException
     */
    private Connection getConnection() throws FormsPortalException{
        try {
            // Inject the DataSourcePool right here!
            DataSource dataSource = (DataSource) source.getDataSource(FormsPortalConstants.DATA_SOURCE_NAME);
            Connection connection = dataSource.getConnection();
            return connection;
        } catch (Exception e) {
            throw new FormsPortalException(e.getMessage(), e);
        }
    }
     
    /**
     * To save metadata associated with a draft
     * This method takes a map as argument. This map consists of metadata properties as key and the values corresponds to property's value
     * The mandatory key in this map for a draft is "draftID"
     * In order to manage all the metadata in single table, we are having one additional property which is primary key of the table- "id". We will assign draftID to id attribute
     * This method returns draftID associated with this draft. draftID denotes the metadata id associated with the draft
     * Need to take care of type of properties. Right now value is only of type String and String[]
     */
    public String saveMetadata(Map<String, Object> metadataMap) throws FormsPortalException {
        Connection connection = null;
        try{
            connection = getConnection();
            connection.setAutoCommit(false);
             
            String id = metadataMap.get(FormsPortalConstants.DRAFT_ID).toString();
            metadataMap.put(FormsPortalConstants.ID, id);
            insertMetadata(id, metadataMap, connection);
             
            /**
             * Committing after all the operations
             */
            connection.commit();           
            return id;
        } catch(Exception e){
            try{
                /**
                 *  In case of any error, rollback
                 */
                 if(connection!=null){
                    connection.rollback();
                 }
              }catch(SQLException e2){
                  throw new FormsPortalException(e2.getMessage(), e2);
              }
            throw new FormsPortalException(e.getMessage(), e);
        } finally{
            try {      
                /**
                 * Close the connection in finally block
                 */
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                throw new FormsPortalException(e.getMessage(), e);
            }
        }
    }
     
    /**
     * To save metadata associated with a submission
     * This method takes a map as argument. This map consists of metadata properties as key and the values corresponds to property's value
     * If a key "submitID" is not present, we need to create one and do further processing
     *  In order to manage all the metadata in single table, we are having one additional property which is primary key of the table- "id". We will assign submitID to id attribute
     *  This method returns the metadata object of submitted form in JSON format. For adaptive form, this object will also be used for redirect URL creation
     *  "submitID" is a must have key for this resultant object.
     *  Need to take care of type of properties. Right now value is only of type String and String[]
     */
    public JSONObject submitMetadata(Map<String, Object> metadataMap) throws FormsPortalException {
        Connection connection = null;
        PreparedStatement prStmt = null;
        try {
            connection = getConnection();
            connection.setAutoCommit(false);
             
            String id = null;
            if(metadataMap.containsKey(FormsPortalConstants.SUBMIT_ID)){
                id = metadataMap.get(FormsPortalConstants.SUBMIT_ID).toString();
            } else {
                id = getId();
                metadataMap.put(FormsPortalConstants.SUBMIT_ID, id);
            }          
            metadataMap.put(FormsPortalConstants.ID, id);
            insertMetadata(id, metadataMap, connection);
             
            /**
             * Committing after all the operations
             */
            connection.commit();           
             
            JSONObject submittedInstance = new JSONObject();                       
            String getSubmittedInstance = "SELECT * FROM " + FormsPortalConstants.METADATA_TABLE + " WHERE id = (?)";
            prStmt = connection.prepareStatement(getSubmittedInstance);
            prStmt.setString(1, id);
            ResultSet result = prStmt.executeQuery();
            if(result.next()){
                submittedInstance.put(FormsPortalConstants.SUBMIT_ID, result.getString(FormsPortalConstants.SUBMIT_ID));
                submittedInstance.put(FormsPortalConstants.FORM_NAME, result.getString(FormsPortalConstants.FORM_NAME));                              
                submittedInstance.put(FormsPortalConstants.OWNER, result.getString(FormsPortalConstants.OWNER));               
                submittedInstance.put(FormsPortalConstants.JCR_LASTMODIFIED, result.getString(FormsPortalConstants.JCR_LASTMODIFIED));
            }
            if(result != null){
                result.close();
            }
            return submittedInstance;
        } catch(Exception e){
            try{
                /**
                 *  In case of any error, rollback
                 */
                 if(connection!=null){
                    connection.rollback();
                 }
              }catch(SQLException e2){
                  throw new FormsPortalException(e2.getMessage(), e2);
              }
            throw new FormsPortalException(e.getMessage(), e);
        } finally{
            try {
                /**
                 * Close the statement and connection in finally block
                 */
                if(prStmt != null){
                    prStmt.close();
                }
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                throw new FormsPortalException(e.getMessage(), e);
            }
        }
    }
    /**
     * To list all the drafts associated with a user
     * This method will return as JSONArry of all the drafts where each draft is represented by a flat JSON object  
     * This JSONArray is used for listing all the draft on the UI
     */
    public JSONArray getDrafts(String cutPoints) throws FormsPortalException {
        Connection connection = null;
        try{
            connection = getConnection();
            return getItems(FormsPortalConstants.FP_DRAFT, cutPoints, connection);
        } catch(Exception e){          
            throw new FormsPortalException(e.getMessage(), e);
        } finally{
            try {  
                /**
                 * Close the connection in finally block
                 */
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                throw new FormsPortalException(e.getMessage(), e);
            }
        }  
    }
     
    /**
     * To list all the submissions associated with a user
     * This method will return as JSONArry of all the submissions where each submission is represented by a flat JSON object
     * This JSONArray is used for listing all the submissions on the UI 
     */
    public JSONArray getSubmissions(String cutPoints) throws FormsPortalException {
        Connection connection = null;
        try{
            connection = getConnection();
            return getItems(FormsPortalConstants.FP_SUBMISSION, cutPoints, connection);
        } catch(Exception e){      
            throw new FormsPortalException(e.getMessage(), e);
        } finally{
            try {
                /**
                 * Close the connection in finally block
                 */
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                throw new FormsPortalException(e.getMessage(), e);
            }
        }
    }
     
    /**
     * To delete metadata information associated with a draft or submission
     * This method uses logged-in user's information to verify whether this user is owner of this metadata or not to make sure he is the one authorized to delete
     * metadata id is provided to this method and the corresponding item is deleted
     * It returns the status of delete operation performed
     */
    public boolean deleteMetadata(String id) throws FormsPortalException {
        Connection connection = null;
        PreparedStatement prStmt = null;
        try {
            String userId = resourceResolverHelper.getResourceResolver().getUserID();
            connection = getConnection();
            connection.setAutoCommit(false);
             
            String deleteStmt = "DELETE FROM " + FormsPortalConstants.METADATA_TABLE + " WHERE id = (?) AND owner = (?)";
            prStmt = connection.prepareStatement(deleteStmt);
            prStmt.setString(1, id);
            prStmt.setString(2, userId);
            prStmt.execute();
             
            connection.commit();
            return true;
        } catch (Exception e) {
            throw new FormsPortalException(e.getMessage(), e); 
        } finally {
            try {
                if(prStmt != null){
                    prStmt.close();
                }
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                throw new FormsPortalException(e.getMessage(), e);
            }
        }
    }
    /**
     * To get a metadata property stored for a draft/submission instance
     * This method will take draft/submission metadata id and the propertyName as arguments
     * This will return a string array.
     * If the property is single valued, it needs to return an array with only one element
     * If the property is multivalued, it needs to return an array with all the values
     * If the property does not exist, it is supposed to return an array with single empty value
     */
    public String[] getProperty(String id, String propertyName) throws FormsPortalException{
        Connection connection = null;
        PreparedStatement prStmt = null;
        try {
            connection = getConnection();
            connection.setAutoCommit(false);
            String result = "";
            String userId = resourceResolverHelper.getResourceResolver().getUserID();
            List<String> columnsList = listAllColumns(connection);
            if(columnsList.contains(propertyName)){
                String getPropertyStmt = "SELECT " + propertyName + " from " + FormsPortalConstants.METADATA_TABLE + " WHERE id = (?) AND owner = (?)";
                prStmt = connection.prepareStatement(getPropertyStmt);
                prStmt.setString(1, id);
                prStmt.setString(2, userId);
                ResultSet resultSet = prStmt.executeQuery();               
                if(resultSet != null && resultSet.next()){
                    if(resultSet.getString(propertyName) != null){
                        result = resultSet.getString(propertyName);
                    }
                    /**
                     * Special care for attachmentList only. This property can be multivalued and we need it in the same format that we had provided it while saving/submitting
                     */
                    if(propertyName.equals(FormsPortalConstants.ATTACHMENT_LIST) && result != null){
                        result = result.replaceAll(" =.*?\\|", "|");
                        List<String> attachmentList = new ArrayList<String>(Arrays.asList(result.split("\\|")));
                        List<String> resultList = new ArrayList<String>();
                        for(String attachment: attachmentList){
                            if(attachmentList.contains(attachment + "%2F" + FormsPortalConstants.CONTENT_TYPE)){
                                resultList.add(attachment);
                            }
                        }
                         
                        String[] attachmentKeys = new String[resultList.size()];
                        attachmentKeys = resultList.toArray(attachmentKeys);
                        return attachmentKeys;
                    }
                }               
            } else{
                /**
                 * We have stored several attachments in attachmentList column. If someone comes asking about those properties we need to get it from the attachment list cell
                 */            
                String getAttachmentList = "SELECT " + FormsPortalConstants.ATTACHMENT_LIST + " from " + FormsPortalConstants.METADATA_TABLE + " WHERE id = (?) AND " + FormsPortalConstants.ATTACHMENT_LIST + " LIKE (?)";
                prStmt = connection.prepareStatement(getAttachmentList);
                prStmt.setString(1, id);
                prStmt.setString(2, "%" + propertyName + "%");
                ResultSet resultSet = prStmt.executeQuery();
                if(resultSet.next()){
                    String attachmentStr = resultSet.getString(FormsPortalConstants.ATTACHMENT_LIST);
                    if(attachmentStr != null && !attachmentStr.isEmpty()){
                        // Attachment list is stored as "a = b|c = d"
                        // For example, the property asked will be "a". In order to get its value i.e. "b" we need to manipulate string. Which is as follows
                        // 3 is added in the index to account for 2 whitespaces and 1 "=" mark
                        String substr = attachmentStr.substring(attachmentStr.indexOf(propertyName) + propertyName.length() + 3);
                        result = substr.substring(0, substr.indexOf("|") == -1 ? substr.length() : substr.indexOf("|"));
                    }
                }
            }
            return new String[]{result};
        } catch (SQLException e) {
            return new String[]{""};
        } finally {
            try {
                if(prStmt != null){
                    prStmt.close();
                }
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                throw new FormsPortalException(e.getMessage(), e);
            }
        }
    }
    /**
     * To delete a property associated with a draft/submission
     * This method will take draft/submission metadata id and queried property's Name
     * This method returns status of the delete operation
     */
    public boolean deleteProperty(String id, String propertyName) throws FormsPortalException {
        Connection connection = null;
        PreparedStatement prStmt = null;
        try {
            connection = getConnection();
            connection.setAutoCommit(false);
             
            String userId = resourceResolverHelper.getResourceResolver().getUserID();
            String deletePropertyStmt = "UPDATE metadata SET (?) = NULL WHERE id = (?) AND owner = (?)";
            prStmt = connection.prepareStatement(deletePropertyStmt);
            prStmt.setString(1, propertyName);
            prStmt.setString(2, id);
            prStmt.setString(3, userId);
            boolean result =  prStmt.execute();        
            connection.commit();
            return result;
        } catch (Exception e) {
            throw new FormsPortalException(e.getMessage(), e);
        } finally {
            try {
                if(prStmt != null){
                    prStmt.close();
                }
                if(connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                throw new FormsPortalException(e.getMessage(), e);
            }
        }
    }
    private JSONArray getItems(String itemType, String cutPoints, Connection connection) throws FormsPortalException{
        PreparedStatement prStmt = null;
        ResultSet resultSet = null;
        try{
            String userId = resourceResolverHelper.getResourceResolver().getUserID();
            String getItemsStmt = "Select * from " + FormsPortalConstants.METADATA_TABLE + " WHERE owner = (?) AND nodeType = (?) order by `" + FormsPortalConstants.JCR_LASTMODIFIED + "` asc";
             
            prStmt = connection.prepareStatement(getItemsStmt);
            prStmt.setString(1, userId);
            prStmt.setString(2, itemType);
             
            resultSet = prStmt.executeQuery();
            JSONArray items = new JSONArray();
            while(resultSet.next()){
                JSONObject item = new JSONObject();
                List<String> cutPointsList = Arrays.asList(cutPoints.split(","));
                for(String cutPoint : cutPointsList){
                    try{
                        if(resultSet.getString(cutPoint) == null){
                            item.put(cutPoint, "");
                        } else {
                            item.put(cutPoint, resultSet.getString(cutPoint));
                        }
                    } catch (SQLException e){                  
                        item.put(cutPoint, "");
                    }
                }
                item.put(FormsPortalConstants.JCR_LASTMODIFIED, resultSet.getString(FormsPortalConstants.JCR_LASTMODIFIED));
                items.put(item);
            }
            if(resultSet != null){
                resultSet.close();
            }
            return items;
        } catch(Exception e){
            throw new FormsPortalException(e.getMessage(), e);
        } finally{         
            try {
                if(resultSet != null){
                    resultSet.close(); 
                }
                if(prStmt != null){
                    prStmt.close();
                }
            }catch (SQLException e) {
                throw new FormsPortalException(e.getMessage(), e);
            }          
        }
    }
    private List<String> listAllColumns(Connection connection) throws FormsPortalException{
        try{
            List<String> columnsList = new ArrayList<String>();
            String getColumnsStmt = "SHOW COLUMNS FROM " + FormsPortalConstants.METADATA_TABLE;
            PreparedStatement prStmt = connection.prepareStatement(getColumnsStmt);
            ResultSet resultSet = prStmt.executeQuery();
            while(resultSet.next()){
                String name = resultSet.getString("Field");
                columnsList.add(name);
            }
            return columnsList;
        } catch(Exception e){
            throw new FormsPortalException(e.getMessage(), e);
        }
    }
     
    private void insertMetadata(String id, Map<String, Object> metadataMap, Connection connection) throws FormsPortalException {
        PreparedStatement prStmt = null;
        try {
            String insertStmt = "INSERT INTO " + FormsPortalConstants.METADATA_TABLE;
             
            List<String> columnsList = listAllColumns(connection);           
            List<String> metadataKeys = new ArrayList<String>(metadataMap.keySet());
             
            columnsList.retainAll(metadataKeys);
             
            String columnsStr = StringUtils.join(columnsList, "`,`");
            int columnsCount = columnsList.size();
            StringBuffer stmtBuffer = new StringBuffer();                  
            stmtBuffer.append(insertStmt).append(" (" + "`" + columnsStr + "`" + ")");
             
            String placeholder = "(?), ";
            String valuesPlaceHolders = StringUtils.repeat(placeholder, columnsCount);
            valuesPlaceHolders = valuesPlaceHolders.substring(0, valuesPlaceHolders.length()-2);
            stmtBuffer.append(" VALUES(" + valuesPlaceHolders +")");
                                             
            String onDuplicateStmt = " ON DUPLICATE KEY UPDATE ";
            stmtBuffer.append(onDuplicateStmt);
            for(String column : columnsList){
                stmtBuffer.append("`" + column + "`" + " = (?), ");
            }
             
            String statementString = stmtBuffer.toString();
            //Removing trailing "," and " "
            statementString = statementString.substring(0, statementString.length()-2);
            prStmt = connection.prepareStatement(statementString);
             
            int count = 1;
            for(String column : columnsList){
                String val = null;
                if(column.equals(FormsPortalConstants.ATTACHMENT_LIST) && metadataMap.get(FormsPortalConstants.ATTACHMENT_LIST) != null){
                    String[] attachmentList = (String[]) metadataMap.get(FormsPortalConstants.ATTACHMENT_LIST);
                    List<String> attachmentListVal = new ArrayList<String>();
                    for(String attachmentKey : attachmentList){
                        String attachmentDataId = metadataMap.get(attachmentKey).toString();
                        attachmentListVal.add(attachmentKey + " = " + attachmentDataId);
                         
                        if(metadataMap.containsKey(attachmentKey + "%2F" + FormsPortalConstants.CONTENT_TYPE)){
                            String attachmentContentType = metadataMap.get(attachmentKey + "%2F" + FormsPortalConstants.CONTENT_TYPE).toString();
                            attachmentListVal.add(attachmentKey + "%2F" + FormsPortalConstants.CONTENT_TYPE + " = " + attachmentContentType);
                        }                      
                    }
                    val = StringUtils.join(attachmentListVal, "|") + "|";
                } else {
                    val = metadataMap.get(column) != null ? metadataMap.get(column).toString() : null;
                }                              
                prStmt.setString(count, val);
                prStmt.setString(count + columnsCount, val);
                count++;
            }
            prStmt.execute();
        } catch (Exception e) {
            throw new FormsPortalException(e);
        } finally {
            try {
                if(prStmt != null){
                    prStmt.close();
                }
            } catch (SQLException e) {
                throw new FormsPortalException(e.getMessage(), e);
            }
        }
    }
     
    private String getId() {
        return String.valueOf(System.nanoTime());
    }
}

FormsPortalConstants used in the services

The FormsPortalConstants used in the services in the sample implementation are as follows:

/*************************************************************************
*
* ADOBE CONFIDENTIAL
* ___________________
*
*  Copyright 2015 Adobe Systems Incorporated
*  All Rights Reserved.
*
* NOTICE:  All information contained herein is, and remains
* the property of Adobe Systems Incorporated and its suppliers,
* if any.  The intellectual and technical concepts contained
* herein are proprietary to Adobe Systems Incorporated and its
* suppliers and are protected by trade secret or copyright law.
* Dissemination of this information or reproduction of this material
* is strictly forbidden unless prior written permission is obtained
* from Adobe Systems Incorporated.
**************************************************************************/
package com.adobe.fd.fp.customhandler;
public final class FormsPortalConstants {
    public static final String DATA_TABLE = "data";
    public static final String METADATA_TABLE = "metadata";
    public static final String DATA_COLUMN = "data";
    public static final String DATA_SOURCE_NAME = "FormsPortal";
    public static final String DRAFT_ID = "draftID";
    public static final String FORM_NAME = "formName";
    public static final String OWNER = "owner";
    public static final String JCR_LASTMODIFIED = "jcr:lastModified";
    public static final String SUBMIT_ID = "submitID";
    public static final String ID = "id";
    public static final String ATTACHMENT_LIST = "attachmentList";
    public static final String CONTENT_TYPE = "contentType";
    public static final String FP_DRAFT = "fp:Draft";
    public static final String FP_SUBMISSION = "fp:submittedForm";
}

Bu çalışma Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License kapsamında lisanslanmıştır  Creative Commons şartları, Twitter™ ve Facebook sitelerinde paylaşılanları kapsamaz.

Yasal Uyarılar   |   Çevrimiçi Gizlilik İlkesi