Sample for integrating drafts & submissions component with database

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

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