You're viewing help content for version:
- 6.4
- 6.3
- 6.2
- Older Versions
AEM Forms portal drafts and submissions component allows users to save their forms as drafts and submit later from any device. Also, users can view their submitted forms on portal. To enable this functionality, AEM Forms provides data and metadata services to store the data filled in by a user in the form and the form metadata associated with drafts and submitted forms. This data is stored in the CRX repository, by default. However, as users interact with forms through AEM publish instance, which is generally outside the enterprise firewall, organizations may want to customize data storage for it to be more secure and reliable.
The sample, discussed in this document, is a reference implementation of customized data and metadata services to integrate drafts and submissions component with a database. The database used in the sample implementation is MySQL 5.6.24. However, you can integrate the drafts and submissions component with any database of your choice.
Note:
The examples and configurations explained in this document are according to MySQL 5.6.24 and you must substitute them appropriately for your database system.
-
Download
-
Property Description Value Forms Portal Draft Data Service Identifier for draft data service draft.data.service formsportal.sampledataservice Forms Portal Draft Metadata Service Identifier for draft metadata service draft.metadata.service formsportal.samplemetadataservice Forms Portal Submit Data Service Identifier for submit data service submit.data.service formsportal.sampledataservice Forms Portal Submit Metadata Service Identifier for submit metadata service submit.metadata.service formsportal.samplemetadataservice Note:
The services are resolved by their names mentioned as value for the aem.formsportal.impl.prop key as follows:
@Service(value = {SubmitDataService.class, DraftDataService.class}) @Property(name = "aem.formsportal.impl.prop", value = "formsportal.sampledataservice") @Service(value = { SubmitMetadataService.class, DraftMetadataService.class }) @Property(name = "aem.formsportal.impl.prop", value = "formsportal.samplemetadataservice")
You can change names of the data and metadata tables.
To provide a different name for the metadata table:
- In the Web Console Configuration, find and click Forms Portal Metadata Service Sample Implementation. You can change the values of data source, metadata/additional metadata table name.
To provide a different name for the data table:
- In the Web Console Configuration, find and click Forms Portal Data Service Sample Implementation. You can change the values of data source and data table name.
Note: If you change the table names, provide them in the Form Portal configuration.
-
For JDBC connection, find and click to open Day Commons JDBC Connections Pool in edit mode in the Web Console Configuration. Specify the values for properties as described in the following table:
Property Value JDBC driver class com.mysql.jdbc.Driver JDBC connection URI
jdbc:mysql://[host]:[port]/[schema_name] Username A username to authenticate and perform actions on database tables Password Password associated with the username Datasource name A datasource name for filtering drivers from the data source pool
Note: The sample implementation uses FormsPortal as the datasource name.
Note:
The JDBC driver for MySQL is not provided with the sample. Ensure that you have provisioned for it and provide the required information to configure the JDBC connection pool.
-
For Apache Sling connection, find and click to open Apache Sling Connection Pooled DataSource in edit mode in the Web Console Configuration. Specify the values for properties as described in the following table:
Property Value Datasource name A datasource name for filtering drivers from the data source pool
Note: The sample implementation uses FormsPortal as the datasource name.
JDBC driver class com.mysql.jdbc.Driver JDBC connection URI
jdbc:mysql://[host]:[port]/[schema_name] Username A username to authenticate and perform actions on database tables Password Password associated with the username Transaction Isolation READ_COMMITTED Max Active Connections 1000 Max Idle Connections 100 Min Idle Connections 10 Initial Size 10 Max Wait 100000 Test on Borrow Checked Test while Idle Checked Validation Query Example values are SELECT 1(mysql), select 1 from dual(oracle), SELECT 1(MS Sql Server) (validationQuery) Validation Query timeout 10000 -
If you already have a table in the database schema, skip to the next step.
Otherwise, if you do not already have a table in the database schema, execute the following SQL statements to create separate tables for data, metadata, and additional metadata in the database schema:
CREATE TABLE `data` ( `owner` varchar(255) DEFAULT NULL, `data` longblob, `metadataId` varchar(45) DEFAULT NULL, `id` varchar(45) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `metadata` ( `formPath` varchar(1000) DEFAULT NULL, `formType` varchar(100) DEFAULT NULL, `description` text, `formName` varchar(255) DEFAULT NULL, `owner` varchar(255) DEFAULT NULL, `enableAnonymousSave` varchar(45) DEFAULT NULL, `renderPath` varchar(1000) DEFAULT NULL, `nodeType` varchar(45) DEFAULT NULL, `charset` varchar(45) DEFAULT NULL, `userdataID` varchar(45) DEFAULT NULL, `status` varchar(45) DEFAULT NULL, `formmodel` varchar(45) DEFAULT NULL, `markedForDeletion` varchar(45) DEFAULT NULL, `showDorClass` varchar(255) DEFAULT NULL, `sling:resourceType` varchar(1000) DEFAULT NULL, `attachmentList` longtext, `draftID` varchar(45) DEFAULT NULL, `submitID` varchar(45) DEFAULT NULL, `id` varchar(60) NOT NULL, `profile` varchar(255) DEFAULT NULL, `submitUrl` varchar(1000) DEFAULT NULL, `xdpRef` varchar(1000) DEFAULT NULL, `jcr:lastModified` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `ID_UNIQUE` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `additionalmetadatatable` ( `value` text, `key` varchar(255) NOT NULL, `id` varchar(60) NOT NULL, PRIMARY KEY (`id`,`key`), CONSTRAINT ‘additionalmetadatatable_fk’ FOREIGN KEY (`id`) REFERENCES `metadata` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `commenttable` ( `commentId` varchar(255) DEFAULT NULL, `comment` text DEFAULT NULL, `ID` varchar(255) DEFAULT NULL, `commentowner` varchar(255) DEFAULT NULL, `time` varchar(255) DEFAULT NULL );
-
If you already have the tables (data, metadata, and additionalmetadatatable) in the database schema, execute the following alter table queries:
ALTER TABLE `data` CHANGE `owner` `owner` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE metadata add markedForDeletion varchar(45) DEFAULT NULL
Note:
The ALTER TABLE metadata add query fails if you have already run it and the markedfordeletion column is present in the table.
ALTER TABLE `metadata`CHANGE `formPath` `formPath` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `formType` `formType` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `description` `description` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `formName` `formName` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `owner` `owner` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `renderPath` `renderPath` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `showDorClass` `showDorClass` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `sling:resourceType` `sling:resourceType` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `profile` `profile` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `submitUrl` `submitUrl` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `xdpRef` `xdpRef` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `additionalmetadatatable` CHANGE `value` `value` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, CHANGE `key` `key` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
The sample implementation is now configured, which you can use to list your drafts and submissions while storing all data and metadata in a database. Let’s now see how data and metadata services are configured in the sample.
The following code shows how the forms portal data service is customized for this sample implementation.
/************************************************************************* * * ADOBE CONFIDENTIAL * ___________________ * * Copyright 2015 Adobe Systems Incorporated * All Rights Reserved. * * NOTICE: All information contained herein is, and remains * the property of Adobe Systems Incorporated and its suppliers, * if any. The intellectual and technical concepts contained * herein are proprietary to Adobe Systems Incorporated and its * suppliers and are protected by trade secret or copyright law. * Dissemination of this information or reproduction of this material * is strictly forbidden unless prior written permission is obtained * from Adobe Systems Incorporated. **************************************************************************/ package com.adobe.fd.fp.customhandler; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.sql.DataSource; import org.apache.felix.scr.annotations.Component; import org.apache.felix.scr.annotations.Property; import org.apache.felix.scr.annotations.Reference; import org.apache.felix.scr.annotations.Service; import com.adobe.fd.fp.exception.FormsPortalException; import com.adobe.fd.fp.service.DraftDataService; import com.adobe.fd.fp.service.SubmitDataService; import com.adobe.granite.resourceresolverhelper.ResourceResolverHelper; import com.day.commons.datasource.poolservice.DataSourcePool; @Service(value = {SubmitDataService.class, DraftDataService.class}) @Property(name = "aem.formsportal.impl.prop", value = "formsportal.sampledataservice") @Component public class FormsPortalSampleDataServiceImpl implements SubmitDataService,DraftDataService { /** * Table name: data * Primary key: id * Columns: id, owner, data * Data source name: FormsPortal */ @Reference private DataSourcePool source; @Reference private ResourceResolverHelper resourceResolverHelper; // Returns a connection using the configured DataSourcePool private Connection getConnection() throws Exception{ try { // Inject the DataSourcePool right here! DataSource dataSource = (DataSource) source.getDataSource(FormsPortalConstants.DATA_SOURCE_NAME); Connection connection = dataSource.getConnection(); return connection; } catch (Exception e) { throw new Exception(e.getMessage(), e); } } /** * To save user data, this method takes * 1. id for the userdata (id will be null if you are creating this draft/submission instance for the first time) * 2. formName, form's name * 3. formdata, actual data to be stored * Here to maintain owner's information, this method is internally getting that information and saving it * * Leveraging (Insert into ..... On Duplicate Key Update ......) query to insert into table if there is no such instance. * Returns the data "id" */ public String saveData(String id, String formName, String formdata) throws FormsPortalException { Connection connection = null; PreparedStatement prStmt = null; try { String updateStatement = "INSERT INTO " + FormsPortalConstants.DATA_TABLE + "(id, data, owner)" + " VALUES((?), (?), (?))" + "ON DUPLICATE KEY UPDATE " + "data = (?)"; connection = getConnection(); // Setting auto commit false here to maintain atomic transactional behavior connection.setAutoCommit(false); prStmt = connection.prepareStatement(updateStatement); String userId = resourceResolverHelper.getResourceResolver().getUserID(); if((id == null || id.isEmpty()) && userId != null){ id = getId(); } prStmt.setString(1, id); prStmt.setString(2, formdata); prStmt.setString(3, userId); prStmt.setString(4, formdata); prStmt.execute(); /** * Committing after all the operations */ connection.commit(); return id; } catch (Exception e) { try { /** * In case of any error, rollback */ connection.rollback(); } catch (SQLException e1) { throw new FormsPortalException(e.getMessage(), e); } throw new FormsPortalException(e.getMessage(), e); } finally { try { /** * Close the statement and connection in finally block */ if(prStmt != null){ prStmt.close(); } if(connection != null){ connection.close(); } } catch (SQLException e) { throw new FormsPortalException(e.getMessage(), e); } } } /** * To get data stored against the id * This is the same data that we stored in saveData method * It takes only "id" as argument * Using logged-in user's information to verify if he is the authorized person to view this data * Returns byte array of data requested */ public byte[] getData(String id) throws FormsPortalException { Connection connection = null; PreparedStatement prStmt = null; ResultSet resultSet = null; try { String userId = resourceResolverHelper.getResourceResolver().getUserID(); connection = getConnection(); String getDataStmt = "SELECT " + FormsPortalConstants.DATA_COLUMN + " from " + FormsPortalConstants.DATA_TABLE + " WHERE id = (?) AND owner = (?)"; prStmt = connection.prepareStatement(getDataStmt); prStmt.setString(1, id); prStmt.setString(2, userId); resultSet = prStmt.executeQuery(); resultSet.next(); byte[] response = resultSet.getBytes(FormsPortalConstants.DATA_COLUMN); return response; } catch (Exception e) { throw new FormsPortalException(e.getMessage(), e); } finally { try { /** * Close the resultset, statement and connection in finally block */ if(resultSet != null){ resultSet.close(); } if(prStmt != null){ prStmt.close(); } if(connection != null){ connection.close(); } } catch (SQLException e) { throw new FormsPortalException(e.getMessage(), e); } } } /** * To delete the data that we have saved in saveData method * This method takes only "id" as argument * Again using logged-in user's information to verify if he is the authorized person to delete this data * Returns the status of delete operation */ public boolean deleteData(String id) throws FormsPortalException { Connection connection = null; PreparedStatement prStmt = null; try { String userId = resourceResolverHelper.getResourceResolver().getUserID(); connection = getConnection(); connection.setAutoCommit(false); String deleteStmt = "DELETE FROM " + FormsPortalConstants.DATA_TABLE + " WHERE id = (?) AND owner = (?)"; prStmt = connection.prepareStatement(deleteStmt); prStmt.setString(1, id); prStmt.setString(2, userId); prStmt.execute(); connection.commit(); return true; } catch (Exception e) { try{ /** * Rollback in case of any error */ connection.rollback(); } catch(SQLException e2){ throw new FormsPortalException(e.getMessage(), e); } throw new FormsPortalException(e.getMessage(), e); } finally { try { /** * Close the statement and connection in finally block */ if(prStmt != null){ prStmt.close(); } if(connection != null){ connection.close(); } } catch (SQLException e) { throw new FormsPortalException(e.getMessage(), e); } } } /** * Just like the user data, we need to save attachments uploaded alongwith the form. * To do so, we provide * 1. metadataId associated with the metadata of this draft/submission * 2. attachmentBytes, attachment data to be stored * Returns the attachment "id" */ public String saveAttachment(String metadataId, byte[] attachmentBytes) throws FormsPortalException { Connection connection = null; PreparedStatement prStmt = null; try { String id = getId(); String userId = resourceResolverHelper.getResourceResolver().getUserID(); connection = getConnection(); connection.setAutoCommit(false); String saveAttachmentStmt = "INSERT INTO " + FormsPortalConstants.DATA_TABLE + "(id, data, owner)" + " VALUES((?), (?), (?))"; prStmt = connection.prepareStatement(saveAttachmentStmt); prStmt.setString(1, id); prStmt.setBytes(2, attachmentBytes); prStmt.setString(3, userId); prStmt.execute(); connection.commit(); return id; }catch(Exception e){ try{ /** * Rollback in case of any error */ connection.rollback(); } catch(SQLException e2){ throw new FormsPortalException(e.getMessage(), e); } throw new FormsPortalException(e.getMessage(), e); } finally { try { /** * Close the statement and connection in finally block */ if(prStmt != null){ prStmt.close(); } if(connection != null){ connection.close(); } } catch (SQLException e) { throw new FormsPortalException(e.getMessage(), e); } } } /** * To get the attachment data * "id" associated with the attachment is passed as argument * Returns byte array of attachment data */ public byte[] getAttachment(String id) throws FormsPortalException { Connection connection = null; PreparedStatement prStmt = null; ResultSet resultSet = null; try { connection = getConnection(); String userId = resourceResolverHelper.getResourceResolver().getUserID(); String getAttachmentStmt = "SELECT " + FormsPortalConstants.DATA_COLUMN + " from " + FormsPortalConstants.DATA_TABLE + " where id = (?) AND owner = (?)"; prStmt = connection.prepareStatement(getAttachmentStmt); prStmt.setString(1, id); prStmt.setString(2, userId); resultSet = prStmt.executeQuery(); byte[] result = null; if(resultSet.next()){ result = resultSet.getBytes(FormsPortalConstants.DATA_COLUMN); } return result; }catch(Exception e){ throw new FormsPortalException(e.getMessage(), e); } finally { try { /** * Close the resultSet, statement and connection in finally block */ if(resultSet != null){ resultSet.close(); } if(prStmt != null){ prStmt.close(); } if(connection != null){ connection.close(); } } catch (SQLException e) { throw new FormsPortalException(e.getMessage(), e); } } } /** * To delete the attachment that we have saved in saveAttachment method * This method takes only "id" as argument * Again using logged-in user's information to verify if he is the authorized person to delete this attachment * Returns the status of delete operation */ public boolean deleteAttachment(String id) throws FormsPortalException { Connection connection = null; PreparedStatement prStmt = null; try { connection = getConnection(); connection.setAutoCommit(false); String userId = resourceResolverHelper.getResourceResolver().getUserID(); String deleteAttachmentStmt = "DELETE from " + FormsPortalConstants.DATA_TABLE + " where id = (?) AND owner = (?)"; prStmt = connection.prepareStatement(deleteAttachmentStmt); prStmt.setString(1, id); prStmt.setString(2, userId); prStmt.execute(); connection.commit(); return true; }catch(Exception e){ try{ /** * Rollback in case of any error */ connection.rollback(); } catch(SQLException e2){ throw new FormsPortalException(e2.getMessage(), e2); } throw new FormsPortalException(e.getMessage(), e); } finally { try { /** * Close the statement and connection in finally block */ if(prStmt != null){ prStmt.close(); } if(connection != null){ connection.close(); } } catch (SQLException e) { throw new FormsPortalException(e.getMessage(), e); } } } private String getId() { return String.valueOf(System.nanoTime()); } }
The following code shows how the forms portal metadata service is customized for this sample implementation.
/************************************************************************* * * ADOBE CONFIDENTIAL * ___________________ * * Copyright 2015 Adobe Systems Incorporated * All Rights Reserved. * * NOTICE: All information contained herein is, and remains * the property of Adobe Systems Incorporated and its suppliers, * if any. The intellectual and technical concepts contained * herein are proprietary to Adobe Systems Incorporated and its * suppliers and are protected by trade secret or copyright law. * Dissemination of this information or reproduction of this material * is strictly forbidden unless prior written permission is obtained * from Adobe Systems Incorporated. **************************************************************************/ package com.adobe.fd.fp.customhandler; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import java.util.Map; import javax.sql.DataSource; import org.apache.commons.lang.StringUtils; import org.apache.felix.scr.annotations.Component; import org.apache.felix.scr.annotations.Property; import org.apache.felix.scr.annotations.Reference; import org.apache.felix.scr.annotations.Service; import org.apache.sling.commons.json.JSONArray; import org.apache.sling.commons.json.JSONObject; import com.adobe.fd.fp.exception.FormsPortalException; import com.adobe.fd.fp.service.DraftMetadataService; import com.adobe.fd.fp.service.SubmitMetadataService; import com.adobe.granite.resourceresolverhelper.ResourceResolverHelper; import com.day.commons.datasource.poolservice.DataSourcePool; @Service(value = { SubmitMetadataService.class, DraftMetadataService.class }) @Property(name = "aem.formsportal.impl.prop", value = "formsportal.samplemetadataservice") @Component public class FormsPortalSampleMetadataServiceImpl implements SubmitMetadataService, DraftMetadataService { /** * Table name: metadata * Primary key: id * Columns: * Data source name: FormsPortal */ @Reference private DataSourcePool source; @Reference private ResourceResolverHelper resourceResolverHelper; /** * * @return a connection using the configured DataSourcePool * @throws FormsPortalException */ private Connection getConnection() throws FormsPortalException{ try { // Inject the DataSourcePool right here! DataSource dataSource = (DataSource) source.getDataSource(FormsPortalConstants.DATA_SOURCE_NAME); Connection connection = dataSource.getConnection(); return connection; } catch (Exception e) { throw new FormsPortalException(e.getMessage(), e); } } /** * To save metadata associated with a draft * This method takes a map as argument. This map consists of metadata properties as key and the values corresponds to property's value * The mandatory key in this map for a draft is "draftID" * In order to manage all the metadata in single table, we are having one additional property which is primary key of the table- "id". We will assign draftID to id attribute * This method returns draftID associated with this draft. draftID denotes the metadata id associated with the draft * Need to take care of type of properties. Right now value is only of type String and String[] */ public String saveMetadata(Map<String, Object> metadataMap) throws FormsPortalException { Connection connection = null; try{ connection = getConnection(); connection.setAutoCommit(false); String id = metadataMap.get(FormsPortalConstants.DRAFT_ID).toString(); metadataMap.put(FormsPortalConstants.ID, id); insertMetadata(id, metadataMap, connection); /** * Committing after all the operations */ connection.commit(); return id; } catch(Exception e){ try{ /** * In case of any error, rollback */ if(connection!=null){ connection.rollback(); } }catch(SQLException e2){ throw new FormsPortalException(e2.getMessage(), e2); } throw new FormsPortalException(e.getMessage(), e); } finally{ try { /** * Close the connection in finally block */ if(connection != null){ connection.close(); } } catch (SQLException e) { throw new FormsPortalException(e.getMessage(), e); } } } /** * To save metadata associated with a submission * This method takes a map as argument. This map consists of metadata properties as key and the values corresponds to property's value * If a key "submitID" is not present, we need to create one and do further processing * In order to manage all the metadata in single table, we are having one additional property which is primary key of the table- "id". We will assign submitID to id attribute * This method returns the metadata object of submitted form in JSON format. For adaptive form, this object will also be used for redirect URL creation * "submitID" is a must have key for this resultant object. * Need to take care of type of properties. Right now value is only of type String and String[] */ public JSONObject submitMetadata(Map<String, Object> metadataMap) throws FormsPortalException { Connection connection = null; PreparedStatement prStmt = null; try { connection = getConnection(); connection.setAutoCommit(false); String id = null; if(metadataMap.containsKey(FormsPortalConstants.SUBMIT_ID)){ id = metadataMap.get(FormsPortalConstants.SUBMIT_ID).toString(); } else { id = getId(); metadataMap.put(FormsPortalConstants.SUBMIT_ID, id); } metadataMap.put(FormsPortalConstants.ID, id); insertMetadata(id, metadataMap, connection); /** * Committing after all the operations */ connection.commit(); JSONObject submittedInstance = new JSONObject(); String getSubmittedInstance = "SELECT * FROM " + FormsPortalConstants.METADATA_TABLE + " WHERE id = (?)"; prStmt = connection.prepareStatement(getSubmittedInstance); prStmt.setString(1, id); ResultSet result = prStmt.executeQuery(); if(result.next()){ submittedInstance.put(FormsPortalConstants.SUBMIT_ID, result.getString(FormsPortalConstants.SUBMIT_ID)); submittedInstance.put(FormsPortalConstants.FORM_NAME, result.getString(FormsPortalConstants.FORM_NAME)); submittedInstance.put(FormsPortalConstants.OWNER, result.getString(FormsPortalConstants.OWNER)); submittedInstance.put(FormsPortalConstants.JCR_LASTMODIFIED, result.getString(FormsPortalConstants.JCR_LASTMODIFIED)); } if(result != null){ result.close(); } return submittedInstance; } catch(Exception e){ try{ /** * In case of any error, rollback */ if(connection!=null){ connection.rollback(); } }catch(SQLException e2){ throw new FormsPortalException(e2.getMessage(), e2); } throw new FormsPortalException(e.getMessage(), e); } finally{ try { /** * Close the statement and connection in finally block */ if(prStmt != null){ prStmt.close(); } if(connection != null){ connection.close(); } } catch (SQLException e) { throw new FormsPortalException(e.getMessage(), e); } } } /** * To list all the drafts associated with a user * This method will return as JSONArry of all the drafts where each draft is represented by a flat JSON object * This JSONArray is used for listing all the draft on the UI */ public JSONArray getDrafts(String cutPoints) throws FormsPortalException { Connection connection = null; try{ connection = getConnection(); return getItems(FormsPortalConstants.FP_DRAFT, cutPoints, connection); } catch(Exception e){ throw new FormsPortalException(e.getMessage(), e); } finally{ try { /** * Close the connection in finally block */ if(connection != null){ connection.close(); } } catch (SQLException e) { throw new FormsPortalException(e.getMessage(), e); } } } /** * To list all the submissions associated with a user * This method will return as JSONArry of all the submissions where each submission is represented by a flat JSON object * This JSONArray is used for listing all the submissions on the UI */ public JSONArray getSubmissions(String cutPoints) throws FormsPortalException { Connection connection = null; try{ connection = getConnection(); return getItems(FormsPortalConstants.FP_SUBMISSION, cutPoints, connection); } catch(Exception e){ throw new FormsPortalException(e.getMessage(), e); } finally{ try { /** * Close the connection in finally block */ if(connection != null){ connection.close(); } } catch (SQLException e) { throw new FormsPortalException(e.getMessage(), e); } } } /** * To delete metadata information associated with a draft or submission * This method uses logged-in user's information to verify whether this user is owner of this metadata or not to make sure he is the one authorized to delete * metadata id is provided to this method and the corresponding item is deleted * It returns the status of delete operation performed */ public boolean deleteMetadata(String id) throws FormsPortalException { Connection connection = null; PreparedStatement prStmt = null; try { String userId = resourceResolverHelper.getResourceResolver().getUserID(); connection = getConnection(); connection.setAutoCommit(false); String deleteStmt = "DELETE FROM " + FormsPortalConstants.METADATA_TABLE + " WHERE id = (?) AND owner = (?)"; prStmt = connection.prepareStatement(deleteStmt); prStmt.setString(1, id); prStmt.setString(2, userId); prStmt.execute(); connection.commit(); return true; } catch (Exception e) { throw new FormsPortalException(e.getMessage(), e); } finally { try { if(prStmt != null){ prStmt.close(); } if(connection != null){ connection.close(); } } catch (SQLException e) { throw new FormsPortalException(e.getMessage(), e); } } } /** * To get a metadata property stored for a draft/submission instance * This method will take draft/submission metadata id and the propertyName as arguments * This will return a string array. * If the property is single valued, it needs to return an array with only one element * If the property is multivalued, it needs to return an array with all the values * If the property does not exist, it is supposed to return an array with single empty value */ public String[] getProperty(String id, String propertyName) throws FormsPortalException{ Connection connection = null; PreparedStatement prStmt = null; try { connection = getConnection(); connection.setAutoCommit(false); String result = ""; String userId = resourceResolverHelper.getResourceResolver().getUserID(); List<String> columnsList = listAllColumns(connection); if(columnsList.contains(propertyName)){ String getPropertyStmt = "SELECT " + propertyName + " from " + FormsPortalConstants.METADATA_TABLE + " WHERE id = (?) AND owner = (?)"; prStmt = connection.prepareStatement(getPropertyStmt); prStmt.setString(1, id); prStmt.setString(2, userId); ResultSet resultSet = prStmt.executeQuery(); if(resultSet != null && resultSet.next()){ if(resultSet.getString(propertyName) != null){ result = resultSet.getString(propertyName); } /** * Special care for attachmentList only. This property can be multivalued and we need it in the same format that we had provided it while saving/submitting */ if(propertyName.equals(FormsPortalConstants.ATTACHMENT_LIST) && result != null){ result = result.replaceAll(" =.*?\\|", "|"); List<String> attachmentList = new ArrayList<String>(Arrays.asList(result.split("\\|"))); List<String> resultList = new ArrayList<String>(); for(String attachment: attachmentList){ if(attachmentList.contains(attachment + "%2F" + FormsPortalConstants.CONTENT_TYPE)){ resultList.add(attachment); } } String[] attachmentKeys = new String[resultList.size()]; attachmentKeys = resultList.toArray(attachmentKeys); return attachmentKeys; } } } else{ /** * We have stored several attachments in attachmentList column. If someone comes asking about those properties we need to get it from the attachment list cell */ String getAttachmentList = "SELECT " + FormsPortalConstants.ATTACHMENT_LIST + " from " + FormsPortalConstants.METADATA_TABLE + " WHERE id = (?) AND " + FormsPortalConstants.ATTACHMENT_LIST + " LIKE (?)"; prStmt = connection.prepareStatement(getAttachmentList); prStmt.setString(1, id); prStmt.setString(2, "%" + propertyName + "%"); ResultSet resultSet = prStmt.executeQuery(); if(resultSet.next()){ String attachmentStr = resultSet.getString(FormsPortalConstants.ATTACHMENT_LIST); if(attachmentStr != null && !attachmentStr.isEmpty()){ // Attachment list is stored as "a = b|c = d" // For example, the property asked will be "a". In order to get its value i.e. "b" we need to manipulate string. Which is as follows // 3 is added in the index to account for 2 whitespaces and 1 "=" mark String substr = attachmentStr.substring(attachmentStr.indexOf(propertyName) + propertyName.length() + 3); result = substr.substring(0, substr.indexOf("|") == -1 ? substr.length() : substr.indexOf("|")); } } } return new String[]{result}; } catch (SQLException e) { return new String[]{""}; } finally { try { if(prStmt != null){ prStmt.close(); } if(connection != null){ connection.close(); } } catch (SQLException e) { throw new FormsPortalException(e.getMessage(), e); } } } /** * To delete a property associated with a draft/submission * This method will take draft/submission metadata id and queried property's Name * This method returns status of the delete operation */ public boolean deleteProperty(String id, String propertyName) throws FormsPortalException { Connection connection = null; PreparedStatement prStmt = null; try { connection = getConnection(); connection.setAutoCommit(false); String userId = resourceResolverHelper.getResourceResolver().getUserID(); String deletePropertyStmt = "UPDATE metadata SET (?) = NULL WHERE id = (?) AND owner = (?)"; prStmt = connection.prepareStatement(deletePropertyStmt); prStmt.setString(1, propertyName); prStmt.setString(2, id); prStmt.setString(3, userId); boolean result = prStmt.execute(); connection.commit(); return result; } catch (Exception e) { throw new FormsPortalException(e.getMessage(), e); } finally { try { if(prStmt != null){ prStmt.close(); } if(connection != null){ connection.close(); } } catch (SQLException e) { throw new FormsPortalException(e.getMessage(), e); } } } private JSONArray getItems(String itemType, String cutPoints, Connection connection) throws FormsPortalException{ PreparedStatement prStmt = null; ResultSet resultSet = null; try{ String userId = resourceResolverHelper.getResourceResolver().getUserID(); String getItemsStmt = "Select * from " + FormsPortalConstants.METADATA_TABLE + " WHERE owner = (?) AND nodeType = (?) order by `" + FormsPortalConstants.JCR_LASTMODIFIED + "` asc"; prStmt = connection.prepareStatement(getItemsStmt); prStmt.setString(1, userId); prStmt.setString(2, itemType); resultSet = prStmt.executeQuery(); JSONArray items = new JSONArray(); while(resultSet.next()){ JSONObject item = new JSONObject(); List<String> cutPointsList = Arrays.asList(cutPoints.split(",")); for(String cutPoint : cutPointsList){ try{ if(resultSet.getString(cutPoint) == null){ item.put(cutPoint, ""); } else { item.put(cutPoint, resultSet.getString(cutPoint)); } } catch (SQLException e){ item.put(cutPoint, ""); } } item.put(FormsPortalConstants.JCR_LASTMODIFIED, resultSet.getString(FormsPortalConstants.JCR_LASTMODIFIED)); items.put(item); } if(resultSet != null){ resultSet.close(); } return items; } catch(Exception e){ throw new FormsPortalException(e.getMessage(), e); } finally{ try { if(resultSet != null){ resultSet.close(); } if(prStmt != null){ prStmt.close(); } }catch (SQLException e) { throw new FormsPortalException(e.getMessage(), e); } } } private List<String> listAllColumns(Connection connection) throws FormsPortalException{ try{ List<String> columnsList = new ArrayList<String>(); String getColumnsStmt = "SHOW COLUMNS FROM " + FormsPortalConstants.METADATA_TABLE; PreparedStatement prStmt = connection.prepareStatement(getColumnsStmt); ResultSet resultSet = prStmt.executeQuery(); while(resultSet.next()){ String name = resultSet.getString("Field"); columnsList.add(name); } return columnsList; } catch(Exception e){ throw new FormsPortalException(e.getMessage(), e); } } private void insertMetadata(String id, Map<String, Object> metadataMap, Connection connection) throws FormsPortalException { PreparedStatement prStmt = null; try { String insertStmt = "INSERT INTO " + FormsPortalConstants.METADATA_TABLE; List<String> columnsList = listAllColumns(connection); List<String> metadataKeys = new ArrayList<String>(metadataMap.keySet()); columnsList.retainAll(metadataKeys); String columnsStr = StringUtils.join(columnsList, "`,`"); int columnsCount = columnsList.size(); StringBuffer stmtBuffer = new StringBuffer(); stmtBuffer.append(insertStmt).append(" (" + "`" + columnsStr + "`" + ")"); String placeholder = "(?), "; String valuesPlaceHolders = StringUtils.repeat(placeholder, columnsCount); valuesPlaceHolders = valuesPlaceHolders.substring(0, valuesPlaceHolders.length()-2); stmtBuffer.append(" VALUES(" + valuesPlaceHolders +")"); String onDuplicateStmt = " ON DUPLICATE KEY UPDATE "; stmtBuffer.append(onDuplicateStmt); for(String column : columnsList){ stmtBuffer.append("`" + column + "`" + " = (?), "); } String statementString = stmtBuffer.toString(); //Removing trailing "," and " " statementString = statementString.substring(0, statementString.length()-2); prStmt = connection.prepareStatement(statementString); int count = 1; for(String column : columnsList){ String val = null; if(column.equals(FormsPortalConstants.ATTACHMENT_LIST) && metadataMap.get(FormsPortalConstants.ATTACHMENT_LIST) != null){ String[] attachmentList = (String[]) metadataMap.get(FormsPortalConstants.ATTACHMENT_LIST); List<String> attachmentListVal = new ArrayList<String>(); for(String attachmentKey : attachmentList){ String attachmentDataId = metadataMap.get(attachmentKey).toString(); attachmentListVal.add(attachmentKey + " = " + attachmentDataId); if(metadataMap.containsKey(attachmentKey + "%2F" + FormsPortalConstants.CONTENT_TYPE)){ String attachmentContentType = metadataMap.get(attachmentKey + "%2F" + FormsPortalConstants.CONTENT_TYPE).toString(); attachmentListVal.add(attachmentKey + "%2F" + FormsPortalConstants.CONTENT_TYPE + " = " + attachmentContentType); } } val = StringUtils.join(attachmentListVal, "|") + "|"; } else { val = metadataMap.get(column) != null ? metadataMap.get(column).toString() : null; } prStmt.setString(count, val); prStmt.setString(count + columnsCount, val); count++; } prStmt.execute(); } catch (Exception e) { throw new FormsPortalException(e); } finally { try { if(prStmt != null){ prStmt.close(); } } catch (SQLException e) { throw new FormsPortalException(e.getMessage(), e); } } } private String getId() { return String.valueOf(System.nanoTime()); } }
/************************************************************************* * * 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"; }