サンプルの概要

AEM Forms ポータルのドラフトと送信コンポーネントにより、フォームをドラフトとして保存し、任意のデバイスから後で送信することができます。また、ポータルにて送信済みのフォームを表示することもできます。この機能を有効にするため、AEM Forms では、ユーザーによってフォームに入力されたデータおよびドラフトおよび送信済みフォームに関連するメタデータを保存する、データおよびメタデータサービスを提供しています。このデータは、デフォルトで CRX レポジトリに格納されます。ただし、ユーザーが AEM のパブリッシュインスタンスを通じてフォームとやり取りを行うのは通常企業のファイアウォールの外側であるため、組織によっては、よりセキュアで信頼性のあるデータストレージが必要となる場合もあります。

このドキュメントで取り上げるサンプルは、ドラフト&送信コンポーネントをデータベースに統合するためのカスタマイズされたデータサービスおよびメタデータサービスのリファレンス実装です。サンプル実装で使用されているデータベースは MySQL 5.6.24 ですが、ドラフト&送信コンポーネントは、あらゆるデータベースに統合することができます。

注意:

このドキュメントで説明されている例および設定は、MySQL 5.6.24 に基づいているため、お使いのデータベースシステムに合わせてそれらを適切に置き換える必要があります。

サンプルのセットアップおよび設定

次の手順に従って、サンプルをインストール・設定します。

  1. パッケージ aem-fp-db-integration-sample-pkg-1.0.0.zip をファイルシステムにダウンロードします。

    ダウンロード

  2. AEM package manager(http:/[host]:[port]/crx/packmgr/)に移動します。

  3. パッケージをアップロード」をクリックします。

  4. パッケージ aem-fp-db-integration-sample-pkg-1.0.0.zip を参照して選択し、「OK」をクリックします。

  5. パッケージの隣にある「インストール」をクリックし、パッケージをインストールします。

  6. AEM Web Console Configuration ページ(http:///[host]:[port]system/console/configMgr)に移動します。

  7. Forms Portal Draft and Submission Configuration をクリックし、編集モードで開きます。

  8. 次の表の説明に従って、プロパティの値を指定します。指定します。

    プロパティ 詳細
    フォームポータル ドラフトデータサービス ドラフトデータサービス draft.data.service の識別子 formsportal.sampledataservice
    フォームポータル ドラフトメタデータサービス ドラフトメタデータサービス draft.metadata.service の識別子 formsportal.samplemetadataservice
    フォームポータル 送信データサービス 送信データサービス submit.data.service の識別子 formsportal.sampledataservice
    フォームポータル 送信メタデータサービス 送信メタデータサービス submit.metadata.service の識別子 formsportal.samplemetadataservice

    注意:

    サービスは、aem.formsportal.impl.prop キーの値として以下に記述されている名前によって解決されます。

    @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")
  9. 他の設定はそのままにし、「保存」をクリックします。

  10. Day Commons JDBC Connections Poolをクリックして、編集モードで開きます。

  11. 次の表の説明に従って、プロパティの値を指定します。指定します。

    プロパティ
    JDBC ドライバークラス com.mysql.jdbc.Driver
    JDBC 接続 URI
    jdbc:mysql://[ホスト]:[ポート]/[スキーマ名]
    ユーザー名 データベース表でのアクションを認証・実行するためのユーザー名
    パスワード ユーザー名に関連するパスワード
    データソース名

    データソースプールからドライバーをフィルターするためのデータソース名

    注意:サンプルでは、データソース名として「FormsPortal」を使用しています。

    注意:

    MySQL 向けの JDBC ドライバーは、サンプルでは提供されていません。それに対してのプロビジョニングを行い、JDBC 接続プールの設定に必要な情報を提供してください。

  12. 他の設定はそのままにし、「保存」をクリックします。

  13. 次の SQL ステートメントを実行し、データベススキーマ内にデータおよびメタデータ用に別々の表を作成します。

    データ表用の SQL ステートメント

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

    メタデータ表用の SQL ステートメント

    CREATE TABLE `metadata` (
    `formPath` varchar(100) DEFAULT NULL,
    `formType` varchar(100) DEFAULT NULL,
    `description` varchar(150) DEFAULT NULL,
    `formName` varchar(45) DEFAULT NULL,
    `owner` varchar(45) DEFAULT NULL,
    `enableAnonymousSave` varchar(45) DEFAULT NULL,
    `renderPath` varchar(100) 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,
    `showDorClass` varchar(45) DEFAULT NULL,
    `sling:resourceType` varchar(45) DEFAULT NULL,
    `attachmentList` longtext,
    `draftID` varchar(45) DEFAULT NULL,
    `submitID` varchar(45) DEFAULT NULL,
    `id` varchar(60) NOT NULL,
    `profile` varchar(45) DEFAULT NULL,
    `submitUrl` varchar(150) DEFAULT NULL,
    `xdpRef` varchar(250) DEFAULT NULL,
    `jcr:lastModified` varchar(45) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ID_UNIQUE` (`id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

データおよびメタデータをデータベースに保存しながら、ドラフトと送信をリスト表示するために使用できるサンプル実装が設定されました。サンプルでデータサービスとメタデータサービスがどのように設定されているか見てみましょう。

フォームポータルのデータサービスのサンプル

次のコードでは、フォームポータルのデータサービスが、このサンプルに対してどのようにカスタマイズされているかを見ることができます。

/*************************************************************************
*
* 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());
    }
}

フォームポータルのメタデータサービスのサンプル

次のコードでは、フォームポータルのメタデータサービスが、このサンプルに対してどのようにカスタマイズされているかを見ることができます。

/*************************************************************************
*
* 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

サンプル実装においてサービスで使用された FormsPortalConstants は次のとおりです。

/*************************************************************************
*
* 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";
}

本作品は Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License によってライセンス許可を受けています。  Twitter™ および Facebook の投稿には、Creative Commons の規約内容は適用されません。

法律上の注意   |   プライバシーポリシー