Überblick zum Beispiel

Mit der Komponente für Entwurf und Übermittlung des AEM Forms Portals können Benutzer ihre Formulare als Entwürfe speichern und zu einem späteren Zeitpunkt von jedem beliebigen Gerät senden. Zudem können die Benutzer ihre übermittelten Formulare im Portal anzeigen. Um diese Funktion zu aktivieren, bietet AEM Forms Daten- und Metadatendienste an, um die vom Benutzer im Formular eingegebenen Daten sowie die mit Entwürfen und gesendeten Formularen verknüpften Metadaten zu speichern. Diese Daten werden standardmäßig im CRX-Repository gespeichert. Da die Benutzer mit den Formularen jedoch über eine AEM-Veröffentlichunginstanz interagieren, die in der Regel außerhalb der Unternehmens-Firewall liegt, sollten Sie die Datenspeicherung so anpassen, dass sie sicherer und zuverlässiger ist.

Das in diesem Dokument gezeigte Beispiel ist eine Referenzimplementierung benutzerdefinierter Daten- und Metadatendienste zur Integration der Komponente für Entwurf und Übermittlung in eine Datenbank. In der Beispielimplementierung wird die Datenbank MySQL 5.6.24 verwendet. Sie können die Komponente für Entwurf und Übermittlung jedoch in eine Datenbank Ihrer Wahl integrieren.

Hinweis:

Die Beispiele und Konfigurationen in diesem Dokument entsprechen MySQL 5.6.24 und Sie müssen sie für Ihr Datenbanksystem anpassen.

Beispiel installieren und konfigurieren

Führen Sie die folgenden Schritte aus, um das Beispiel zu installieren und zu konfigurieren:

  1. Laden Sie das folgende aem-fp-db-integration-sample-pkg-1.0.0.zip-Paket auf Ihr Dateisystem herunter.

    Herunterladen

  2. Gehen Sie zu AEM Package Manager unter http://[Host]:[Anschluss]/crx/packmgr/.

  3. Klicken Sie auf Paket hochladen.

  4. Wählen Sie das aem-fp-db-integration-sample-pkg-1.0.0.zip-Paket und klicken Sie auf OK.

  5. Klicken Sie neben dem Paket auf Installieren, um das Paket zu installieren.

  6. Gehen Sie unter http://[Host]:[Anschluss] ]/system/console/configMgr. zur AEM Web Console Configuration-Seite.

  7. Klicken Sie, um die Konfiguration des Forms Portals für Entwurf und Übermittlung im Bearbeitungsmodus zu öffnen.

  8. Geben Sie die Werte für die Eigenschaften an, wie in der folgenden Tabelle beschrieben:

    Eigenschaft Beschreibung Wert
    Forms Portal-Datendienst für Entwurf Bezeichner für den Datendienst für Entwurf draft.data.service formsportal.sampledataservice
    Forms Portal-Metadatendienst für Entwurf Bezeichner für den Metadatendienst für Entwurf draft.metadata.service formsportal.samplemetadataservice
    Forms Portal-Datendienst für Übermittlung Bezeichner für den Datendienst für Übermittlung submit.data.service formsportal.sampledataservice
    Forms Portal-Metadatendienst für Übermittlung Bezeichner für den Metadatendienst für Übermittlung submit.metadata.service formsportal.samplemetadataservice

    Hinweis:

    Die Dienste werden nach ihren Namen aufgelöst, die als Wert für den aem.formsportal.impl.prop-Schlüssel wie folgt erwähnt werden:

    @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. Belassen Sie die anderen Konfigurationen und klicken Sie auf Speichern.

  10. Klicken Sie, um Day Commons JDBC-Verbindungs-Pool im Bearbeitungsmodus zu öffnen.

  11. Geben Sie die Werte für die Eigenschaften an, wie in der folgenden Tabelle beschrieben:

    Eigenschaft Wert
    JDBC-Treiberklasse com.mysql.jdbc.Driver
    JDBC-Verbindungs-URI
    jdbc:mysql://[Host]:[Anschluss]/[Schemaname]
    Benutzername Ein Benutzername zur Authentifizierung und zum Durchführen von Aktionen auf Datenbanktabellen
    Kennwort Kennwort für den Benutzernamen
    Datenquellenname

    Ein Datenquellenname für das Filtern der Treiber aus dem Datenquellenpool

    Hinweis: In der Beispielimplementierung wird FormsPortal als Datenquellenname verwendet.

    Hinweis:

    Der JDBC-Treiber für MySQL wird nicht mit dem Beispiel geliefert. Stellen Sie sicher, dass Sie ihn bereitgestellt haben, und geben Sie die erforderlichen Informationen ein, um den JDBC-Verbindungspool zu konfigurieren.

  12. Belassen Sie die anderen Konfigurationen und klicken Sie auf Speichern.

  13. Führen Sie die folgenden SQL-Anweisungen aus, um separate Tabellen für die Daten und Metadaten im Datenbankschema zu erstellen:

    SQL-Anweisung für Datentabelle

    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-Anweisung für Metadatentabelle

    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;

Die Beispielimplementierung ist jetzt konfiguriert. Sie können sie verwenden, um Ihre Entwürfe und Übermittlungen aufzulisten, während Sie alle Daten und Metadaten in einer Datenbank speichern. Als Nächstes geht es darum, wie im Beispiel die Daten- und Metadatendienste konfiguriert werden.

Beispiel zum Forms Portal-Datendienst

Im folgenden Code wird gezeigt, wie der Forms Portal-Datendienst für diese Implementierung angepasst wird.

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

Beispiel zum Forms Portal-Metadatendienst

Im folgenden Code wird gezeigt, wie der Forms Portal-Metadatendienst für diese Implementierung angepasst wird.

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

In den Diensten verwendete FormsPortalConstants

Die folgenden FormsPortalConstants werden in der Beispielimplementierung in den Diensten verwendet:

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

Dieses Werk unterliegt den Bedingungen der Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.  Twitter™- und Facebook-Beiträge fallen nicht unter die Bedingungen der Creative Commons-Lizenz.

Rechtliche Hinweise   |   Online-Datenschutzrichtlinie