Package com.veeva.vault.sdk.api.query

This package provides interfaces to perform various operations using VQL queries and retrieve information from query responses.

See: Description

Interface Summary

Interface	Description
Query	Represents a VQL query.
Query.Builder	Creates an instance of Query.
QueryCountRequest	Represents a VQL count query request which can be submitted through QueryService.count(QueryCountRequest).
QueryCountRequest.Builder	Creates an instance of QueryCountRequest.
QueryCountResponse	Represents the result of a count query execution.
QueryExecutionRequest	Represents a VQL query request which can be submitted through QueryService.query(QueryExecutionRequest).
QueryExecutionRequest.Builder	Creates an instance of QueryExecutionRequest.
QueryExecutionResponse	Represents the result of a VQL query execution.
QueryExecutionResult	Represents a single row in QueryExecutionResponse.
QueryOperation<T>	A sequence of instructions that can be chained together, building a query operation which can be executed with QueryOperation.execute().
QueryOperationError	Represents a VQL query operation error.
QueryResponse	Deprecated as of 21R3.4, see QueryService.query(String)
QueryResult	Deprecated as of 21R3.4, see QueryService.query(String)
QueryService	Service to execute VQL queries.
QueryValidationRequest	Represents a VQL query request which can be validated through QueryService.validate(QueryValidationRequest).
QueryValidationRequest.Builder	Creates an instance of QueryValidationRequest.
QueryValidationResponse	Represents the result of a query validation execution.

Enum Summary

Enum	Description
QueryEscapeOverride	Valid overrides for the default escape strategy in a VQL query.
QueryLogicalOperator	Valid logical operators for VQL queries.
QueryOperationErrorType	Error types that may occur during QueryOperation execution.
QueryTargetOption	Valid options to modify the scope of a VQL query target.

Package com.veeva.vault.sdk.api.query Description

This package provides interfaces to perform various operations using VQL queries and retrieve information from query responses. These operations include fetching data, calculating record counts, and validating queries.

Supported Objects

The following objects are available for query:

• Vault objects
• Documents
• Binders
• Relationships
• System objects:
  • user__sys
  • group__sys
  • group_membership__sys
  • doc_role__sys
  • binder_node__sys
• Workflow objects:
  • active_workflow__sys
  • inactive_workflow__sys
  • active_workflow_task__sys
  • inactive_workflow_task__sys
  • active_workflow_task_item__sys
  • inactive_workflow_task_item__sys

All other objects are not supported for query.

For help with VQL queries, refer to the VQL reference.

Using a QueryExecutionRequest to Execute a Record Query

The following example illustrates querying an object and creating a related record for all records returned by the query.

The Query.Builder is used to construct the query which is submitted in a QueryExecutionRequest. Success and error handlers are specified on the QueryOperation before execution.

 
      // Locate services
      RecordService recordService = ServiceLocator.locate(RecordService.class);
      QueryService queryService = ServiceLocator.locate(QueryService.class);
      LogService logService = ServiceLocator.locate(LogService.class);
      // Query the Country object to retrieve all active countries
      Query countryQuery = queryService.newQueryBuilder()
          .withSelect(VaultCollections.asList("id",
              "LONGTEXT(review_info__c) AS info"))
          .withFrom("country__v")
          .withWhere("status__v = 'active'")
          .build();
      QueryExecutionRequest queryExecutionRequest = queryService.newQueryExecutionRequestBuilder()
          .withQuery(countryQuery)
          .build();
      queryService.query(queryExecutionRequest)
          .onSuccess(queryExecutionResponse -> {
              List countryReviewRecords = VaultCollections.newList();
              queryExecutionResponse.streamResults().forEach(queryExecutionResult -> {
                  String countryId = queryExecutionResult.getValue("id", ValueType.STRING);
                  // When a function alias is used, retrieve the value using the alias
                  String reviewInfo = queryExecutionResult.getValue("info", ValueType.STRING);
                  Record record = recordService.newRecord("country_review__c");
                  record.setValue("country__c", countryId);
                  record.setValue("info__c", reviewInfo);
                  countryReviewRecords.add(record);
              });
              // Batch save new Country Review records
              recordService.batchSaveRecords(countryReviewRecords).ignoreErrors().execute();
          })
          .onError(queryOperationError -> {
              logService.error("Failed to query country records: " + queryOperationError.getMessage());
          })
          .execute();
 
 

Using a QueryCountRequest to Check For Duplicate Records

The following example illustrates executing a count query to enforce a uniqueness rule that spans multiple fields.

There cannot be multiple subject__v records that share the same first_name__v and last_name__v. The Query.Builder is used to iteratively build the WHERE clause and the query is submitted in a QueryCountRequest.

 
      // Locate services
      QueryService queryService = ServiceLocator.locate(QueryService.class);
      // Construct query to check for duplicate records
      Query.Builder queryBuilder = queryService.newQueryBuilder()
          .withSelect(VaultCollections.asList("id"))
          .withFrom("subject__v");
      recordTriggerContext.getRecordChanges().stream().forEach(recordChange -> {
          String firstName = recordChange.getNew().getValue("first_name__v", ValueType.STRING);
          String lastName = recordChange.getNew().getValue("last_name__v", ValueType.STRING);
          queryBuilder.appendWhere(QueryLogicalOperator.OR, "first_name__v = " + firstName + " AND last_name__v = " + lastName);
      });
      QueryCountRequest queryCountRequest = queryService.newQueryCountRequestBuilder()
          .withQuery(queryBuilder.build())
          .build();
      // Execute count query
      queryService.count(queryCountRequest)
          .onSuccess(queryCountResponse -> {
              if (queryCountResponse.getTotalCount() > 0) {
                  throw new RollbackException("DUPLICATE_RECORD", "Duplicate subject__v record detected.");
              }
          })
          .execute();
 
 

Using a QueryValidationRequest to Validate VQL Criteria

The following example illustrates using a QueryValidationRequest to validate a VQL WHERE clause that was provided by an Admin.

The kanban_config__c object allows Admins to configure records that control the contents of a kanban board. Each kanban board is targeted to an object, defined in the object_name__c field, and Admins can specify a VQL WHERE clause in the vql_criteria__c field to filter records. This snippet of VQL is validated when the kanban_config__c record is saved using a QueryValidationRequest.

 
      // Locate services
      QueryService queryService = ServiceLocator.locate(QueryService.class);
      // Construct VQL query to validate VQL criteria
      Query.Builder queryBuilder = queryService.newQueryBuilder()
          .withSelect(VaultCollections.asList("id"))
          .withFrom(objectName)
          .withWhere(vqlCriteria);
      QueryValidationRequest queryValidationRequest = queryService.newQueryValidationRequestBuilder()
          .withQuery(queryBuilder.build())
          .build();
      // Validate query
      queryService.validate(queryValidationRequest)
          .onError(queryOperationErrorResult -> {
              throw new RollbackException("INVALID_VQL_CRITERIA", "VQL criteria [" + vqlCriteria + "] is invalid.");
          })
          .execute();
 
 

Using a TokenRequest

The following example illustrates submitting a TokenRequest in a Query.Builder, which is then submitted in a QueryExecutionRequest, QueryCountRequest, and QueryValidationRequest.

 
      // Locate services
      TokenService tokenService = ServiceLocator.locate(TokenService.class);
      QueryService queryService = ServiceLocator.locate(QueryService.class);

      // Construct token request
      TokenRequest tokenRequest = tokenService.newTokenRequestBuilder()
          .withValue("Custom.username", username)
          .withValue("Custom.amount", amount)
          .build();

      // Construct query
      Query tokenQuery = queryService.newQueryBuilder()
          .withSelect(VaultCollections.asList("id", "name__v"))
          .withFrom("product__c")
          .withWhere("status__v = 'active'")
          .appendWhere(QueryLogicalOperator.AND, "username__c = ${Custom.username}")
          .appendWhere(QueryLogicalOperator.AND, "amount__c > ${Custom.amount})
          .build();

       // Execute query
       QueryExecutionRequest queryExecutionRequest = queryService.newQueryExecutionRequestBuilder()
           .withQuery(tokenQuery)
           .withTokenRequest(tokenRequest)
           .build();
       queryService.query(queryExecutionRequest).execute();

       // Execute count query
       QueryCountRequest queryCountRequest = queryService.newQueryCountRequestBuilder()
           .withQuery(tokenQuery)
           .withTokenRequest(tokenRequest)
           .build();
       queryService.count(queryCountRequest).execute();

      // Execute validation query
      QueryValidationRequest queryValidationRequest = queryService.newQueryValidationRequestBuilder()
          .withQuery(tokenQuery)
          .withTokenRequest(tokenRequest)
          .build();
      queryService.validate(queryValidationRequest).execute();
 
 

Batched Result Processing

QueryService returns query results in a Stream. There is no concept of result pages as seen in the VQL REST API. The following example demonstrates processing records in batches of 500 within the stream.

 
      queryService.query(queryRequest)
          .onSuccess(queryExecutionResponse -> {
              List<QueryExecutionResult> batch = VaultCollections.newList();
              Iterator<QueryExecutionResult> iterator = queryExecutionResponse.streamResults().iterator();
              while (iterator.hasNext()) {
                  batch.add(iterator.next());
                  if (batch.size() == 500) {
                      // Process batch of results
                      processBatch(batch);
                      batch.clear();
                  }
              }
              if (batch.size() > 0) {
                  // Process final batch of results
                  processBatch(batch);
              }
          })
          .execute();
 
 

Performance Consideration

Generally, it is more efficient to construct a query statement with a WHERE clause to retrieve all the records you want to work with rather than running queries in a loop. Reducing the number of queries improves performance and response time.