QueryService

The Vault Java SDK QueryService allows developers to execute VQL queries from within custom vault extensions.

For full details on the interfaces and methods used, please review the Javadocs.

Trigger Logic

The vSDKQueryService trigger demonstrates this functionality by issuing a VQL query to determine if a record with the same name already exists in the affected vault. It then uses the queried data to modify fields on the new record before it is inserted - i.e., a BEFORE_INSERT operation.

Key Concepts

• Issue a VQL query to find existing vsdk_service_basics__c records with the same name.
• If a record with the same name is found:
  • Set the new record's name__v to Copy of: '<name__v>' X.
  • Increment 'X' if the record already has other related Copy of: '<name__v>' X records.
  • Set the new record as related to the original queried record via the related_to__c object reference field.
• If a record doesn't exist:
  • Insert the record with the name__v set as entered in the UI.

Query Setup

The below code initializes a QueryService object and then issues the following VQL query against the vault:

SELECT id, name__v, (SELECT id FROM vsdk_service_basics__cr WHERE name__v LIKE 'Copy of: 'name'%') FROM vsdk_service_basics__c WHERE name__v LIKE 'name'

This query is looking for a vsdk_service_basics__c record that has a name__v matching name. It is also performing a relationship subquery on the vsdk_service_basics__cr relationship (as defined by the related_to__c field) to find Copy of: 'name' records that are already related to this record.

Note: the QueryService.escape(string) escapes single quotes, backslashes, and other special characters for use within the query. This is necessary when a query contains these special characters that are within single quotes. It is considered best practice to use this function to ensure that your queries execute as expected.

//Set the query up. Verify these queries using the API. 
//The QueryService.escape(string) escapes single quotes and backslashes for use within the query.
QueryService queryService = ServiceLocator.locate(QueryService.class);
String query = "select id, name__v, "
	        + "(select id from vsdk_service_basics__cr where name__v like '" + queryService.escape("Copy of: '" + name + "'") + " %') "  
	        + "from vsdk_service_basics__c where name__v like '" + name + "'";
QueryResponse queryResponse = queryService.query(query);

Query Parsing

The QueryService.query(query) function returns a QueryResponse object that contains the complete result of the VQL query execution; there is a result returned for every row in the query.

To traverse through the QueryResponse and retrieve each resultant row, a Java Stream or Java Iterator must be initialized.

As the stream is traversed, each row is retrieved as a QueryResult that contains the field specified in the initial VQL query - ID, name__v, and the vsdk_service_basics__cr relationship.

The vsdk_service_basics__cr relationship must then be retrieved as a separate QueryResponse with a QueryResult.getSubqueryResponse(relationship) call. This is because the relationship subquery contains a result for every related record in the query.

Iterator Example

Iterator<QueryResult> iterator = queryResponse.streamResults().iterator();

//Iterate over the QueryResponse to grab each individual queried record.
//Once retrieved, you can access the subquery to find out how many "Copy of" records exist already.
//The subquery total value is used to increment the "Copy of" records when a user tries to 
//insert multiple records with the same name.
	            
while (iterator.hasNext()) {	            	
    QueryResult qr = (QueryResult) iterator.next();
    QueryResponse subQueryResponse = qr.getSubqueryResponse("vsdk_service_basics__cr");
	                
    //Change the name of the inserted record to "Copy of: '<name>' x".
    //Set the record as related to the queried record via the custom "related_to__c" object reference field.

    String id = qr.getValue("id", ValueType.STRING);
    inputRecord.getNew().setValue("name__v", "Copy of: '" + name + "' " + (subQueryResponse.getResultCount() + 1));
    inputRecord.getNew().setValue("related_to__c", id);
}

Stream Example

//Stream  over the QueryResponse to grab each individual queried record.
//Once retrieved, you can access the subquery to find out how many "Copy of" records exist already.
//The subquery total value is used to increment the "Copy of" records when a user tries to 
//insert multiple records with the same name.

queryResponse.streamResults().forEach(qr -> {
    QueryResponse subQueryResponse = qr.getSubqueryResponse("vsdk_service_basics__cr");
            
    //Change the name of the inserted record to "Copy of: '<name>' x".
    //Set the record as related to the queried record via the custom "related_to__c" object reference field.

    String id = qr.getValue("id", ValueType.STRING);
    inputRecord.getNew().setValue("name__v", "Copy of: '" + recordName + "' " + (subQueryResponse.getResultCount() + 1));
    inputRecord.getNew().setValue("related_to__c", id);
            
});

Modifying the Record

As the individual query results are parsed, the inserted record can be modified with respect to the queried field values. In a BEFORE_INSERT trigger, you can modify an in-flight record without having to update the record via the RecordService.

In this example, we change the inserted record's name to Copy of: '<name__v>' X where "X" is dependent on the number of existing related records. This name change only occurs when the inserted record's name matches a record that already exists in vault.