Query Multi-Select Picklists in SOQL

Create list of values to check for multi picklist

       Set<String> typeSet = new Set<String>();
       String allStr = 'All';
       typeSet.add('\''+ allStr + '\'');
       for (Financial_Goal__c goal  : Contact.FinancialGoals__r){
         typeSet.add('\''+ goal.Type__c + '\'');
       }

Query using INCLUDES and using String.join to create string of List.

List<Content__c> typeMappings = (List<Content__c>) Database.query(
contentMappingQueryFactory.setCondition('Status__c=\'Publish\'
AND Id!=:featureMappingId AND (Goal_Type_Attribute__c INCLUDES(' + String.join(goalTypeList, ',')+ ') 
AND Age_Attribute__c INCLUDES(' + String.join(ageList, ',')+ ') 
AND Employment_Attribute__c IN :employementList AND Gender_Attribute__c IN :genderList 
AND Net_Worth_Attribute__c IN :netWorthList)').setLimit(2).toSOQL());

Use , for OR and ; for AND to match values in picklist

OR:
will match for any value in picklist
String.join(goalTypeList, ',')

AND:
will match for all the value in picklist
String.join(goalTypeList, ';')

JPA Criteria Query Examples

Get All Accounts

@Override
public Collection<DWAccount> getListAccount() {
          CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
	   CriteriaQuery<DWAccount> criteriaBuilderQuery = criteriaBuilder.createQuery(DWAccount.class);
	   CriteriaQuery<DWAccount> accountCriteriaQuery = criteriaBuilderQuery.select(criteriaBuilderQuery.from(DWAccount.class));
	   return this.entityManager.createQuery(accountCriteriaQuery).setMaxResults(200).getResultList();
	}

Select * from Account where SSN=?

@Override
public DWAccount findAccountBySSN(DWAccount accountWithMatchingSSN) {
		CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
	    CriteriaQuery<DWAccount> criteriaQuery = criteriaBuilder.createQuery(DWAccount.class);
	    Root<DWAccount> root = criteriaQuery.from(DWAccount.class);
	    criteriaQuery.select(root);
	    criteriaQuery.where(criteriaBuilder.equal(root.get("ssn"), accountWithMatchingSSN.getSsn()));
	    List<DWAccount> retrievedMatchedSSNAccounts = this.entityManager.createQuery(criteriaQuery).getResultList();
	    if (!retrievedMatchedSSNAccounts.isEmpty())
	    	return retrievedMatchedSSNAccounts.get(0);
	    else
	    	return null;
}

Select * from DWLoan where aid=? and chngStatus is not null

	@Override
	@Transactional
	public void findAndUpdateLoanByAccountId(Map<DWAccount, UpsertResult> accountsLoadedResult) {
		for (DWAccount dwAccounts : accountsLoadedResult.keySet()){
			CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
			CriteriaQuery<DWLoan> criteriaQuery = criteriaBuilder.createQuery(DWLoan.class);
			Root<DWLoan> root = criteriaQuery.from(DWLoan.class);
			Predicate p = criteriaBuilder.conjunction();
			p = criteriaBuilder.and(criteriaBuilder.equal(root.get("aid"), dwAccounts.getId()), criteriaBuilder.isNotNull(root.get("chngStatus")));
			criteriaQuery.select(root);
			criteriaQuery.where(p);
			for (DWLoan dwLoan : this.entityManager.createQuery(criteriaQuery).getResultList()){
				dwLoan.setSfAmid(accountsLoadedResult.get(dwAccounts).getId());
				this.entityManager.merge(dwLoan);
			}
		}
	}

Count number of rows in Account Table

@Override
public int getAccountTotal() {
		Number result = (Number) this.entityManager.createNativeQuery("Select count(id) from DW$SF$ACCOUNT").getSingleResult();
		return result.intValue();
}

SELECT id,status,created_at from transactions where status=’1′ and currency=’USD’ and appId=’123′ order by id

Map<String, Object> params = ...;
CriteriaBuilder cb = em.getCriteriaBuilder();           
CriteriaQuery<Tuple> cq = cb.createTupleQuery();     
Root<Transaction> r = cq.from(Transaction.class);

Predicate p= cb.conjunction();
for (Map.Entry<String, Object> param: params.entrySet())
    p = cb.and(p, cb.equal(r.get(param.getKey()), param.getValue()));

cq.multiselect(r.get("id"), r.get("status"), r.get("created_at"))
    .where(p)
    .orderBy(cb.asc(r.get("id")));

List<Tuple> result = em.createQuery(cq).getResultList();

Static and dynamic SOQL queries and best practices

If you are building Dynamic SOQL Queries that can be using for Apex Classes and Batch Apex you can use the Database.query(”) function to build the query:

      private static final String soqlQuery = 'Select id, OwnerId from Account';
      public void updateAccountOwnerByOwnerName(String currentOwnerName, String newOwnerName)
      {
         Map<Account> updateAccountWithNewOwnerName = new Map();
         List<Account> accountsAssignedToCurrentOwner = Database.query(soqlQuery + ' where Owner.Name like \' currentOwnerName + \');
         User getNewOwnerId = new User(Name=newOwnerName);
         for (Account accountsToUpdateOwner : accountsAssignedToCurrentOwner)
         {
           accountsToUpdateOwner.OwnerId = getNewOwnerId.Id;
           updateAccountWithNewOwnerName.put(accountsToUpdateOwner.OwnerId, accountsToUpdateOwner);
         }
        update updateAccountWithNewOwnerName.values();
      }

Non-dynamic soql or static soql looks like:

List<Account> accounts = [select id from account limit 100];

Apex Batch returns Database.QueryLocator or a Iterable.

private String query = 'Select id from Account';
return Database.getQueryLocator(query);

Combining non-dynamic SOQL with dynamic SOQL by converting from [ ] to a String. Using Database.getQueryLocator this can be done:

Database.getQueryLocator([select id from account] + ' where Owner.Name =' + newOwnerName);

Dynamic SOQL Queries packages and security

For managed packaged development dynamic SOQL queries assist developers with creating soql queries on the fly without hard coding field names which may not be there in all orgs. When you install an managed package you may run into trouble is some fields do not exist. This is why dynamic SOQL queries can be beneficial by using the schema describe objects. The code below can help any developer to retrieve the fields from any object by providing the object name. From that you can build a dynamic soql query and also define the data type for each field. Be careful when using Dynamic SOQL queries as it can be prone to SOQL injection attacks. Make sure your return type is static and final.

Return the Schema.SObjectType for the specific Object’s ID we provide

public static Schema.SObjectType getObjectSchema(String objectSObjectName)
    {
        if (sobjectSchemaMap.isEmpty())
        	getSchemaMap();

        return sobjectSchemaMap.get(objectSObjectName);
    }

SchemaMap() method to return the Schema Map for your organization.

private static Map<String, Schema.SObjectType> sobjectSchemaMap;
public static Map getSchemaMap()
    {
        return sobjectSchemaMap == null ? Schema.getGlobalDescribe() : sobjectSchemaMap;
    }

Dynamically creating the SOQL Query when we have the queryFields, Object and Id.

private String query;
public String buildQueryAllString(List queryFields,DescribeSObjectResult obj, String theId)
    {
        query = QUERY_SELECT_STATEMENT[0];
        for(Schema.DescribeFieldResult dfr : queryFields)
            query = query + dfr.getName() + ',';

        query = query.subString(0,query.length() - 1);
        query = query + QUERY_SELECT_STATEMENT[1];
        query = query + obj.getName();
        query = query + QUERY_SELECT_STATEMENT[2];
        query = query + theId + '\'';
        return query;
    }

Putting it all together:
1. We provide and Record ID.
2. find the SObject for the ID provided.
3. Get the SObject Fields for the SObject
4. Create a SOQL Query
5. Run the SOQL Query to retrieve information.

public SObject processSchemaInfo(String id)
    {
    	try
    	{
        schemaMap = DynamicSOQL.getSchemaMap();
        sobjects = schemaMap.values();

        Schema.DescribeSObjectResult objDescribe;
        List tempFields;

        buildQuery = '';
        fields = new List();

        for(Schema.SObjectType objType : sobjects)
        {
            objDescribe = objType.getDescribe(); 
            String sobjectPrefix = objDescribe.getKeyPrefix();
            if(id != null && sobjectPrefix != null && id.startsWith(sobjectPrefix))
            {
                objectType = objDescribe.getLocalName();
                Map<String, Schema.SObjectField> fieldMap = objDescribe.fields.getMap();
                tempFields = fieldMap.values();
                for(Schema.SObjectField sof : tempFields)
                {
                    fields.add(sof.getDescribe());
                }
                buildQuery = buildQueryAllString(fields,objDescribe,id);
            }
        }

        return  Database.query(buildQuery);
       }
       catch(Exception ex)
       {
       		System.debug(ex.getMessage());
       		return null;
       }
    }