Dynamic Apex: Sample using Schema Describe, Dynamic SOQL and Dynamic DML - El Toro - Find articles about Visualforce, Apex, Force.com and Salesforce in general

Print Preview

Dynamic Apex: Sample using Schema Describe, Dynamic SOQL and Dynamic DML

This sample shows how to use Dynamic Apex to store the Metaphone value of a field, and allow users to find recods, even if they have mispelled the words.

Metaphone is a standard technique that allows you to perform text searches even if you have mispelled the words.

The sample below uses a library written by Kyle Thornton to convert the value form a field (MetaphoneSource__c) and put it in another field (Metaphone__c).

This is what this interface looks like:

The first problem that we need to solve, is to decide which sObjects to show in the drop down picklist.... This is how I solved it:

public void FindObjects() {
	Schema.DescribeSObjectResult DR_SObject;
	Map<String, Schema.sObjectField> mapToken_Fields;
	Map<String, Schema.sObjectType> mapToken_SObjects;
	List<String> sortedNames = new List<String>();

	// Find all objects
	mapToken_SObjects = Schema.getGlobalDescribe();
	sortedNames = new List<String>();
	sortedNames.addAll(mapToken_SObjects.keyset());
	sortedNames.Sort();

	ListObjects = new List<SelectOption>();
	ListObjects.add(new SelectOption('', '-- NONE --', true));
	for (string nameSObject : sortedNames) {
		// Use sObject describe to find information about object...
		DR_SObject = mapToken_SObjects.get(nameSObject).getDescribe();
		if (DR_SObject.isSearchable() && DR_SObject.isUpdateable()) {
			// Field token map...
			mapToken_Fields = DR_SObject.fields.getMap();
			if (mapToken_Fields.containsKey('MetaphoneSource__c')) {
				ListObjects.add(new SelectOption(nameSObject, DR_SObject.getLabel()));
			}
		}
	}
}

We also need to do a SOQL query, but since the objects are not known, we need to do a Dynamic SOQL query:

public List<MySObject> Records {
	get {
		if ((Records == null) && (SelectedObject != '')) {
			Records = new List<MySObject>();
			// SOQL...
			String SOQL = 'SELECT Metaphone__c, MetaphoneSource__c FROM ' + SelectedObject;
			for (List<SObject> sobjs : Database.query(SOQL)) {
				for (SObject sobj : sobjs) {
					records.add(new MySObject(sobj));
				}
			}
		}
		return Records;
	}
	private set;
}

And of course we also need to make changes to the data, so we need Dynamic DML...

private PageReference ProcessRecords(Boolean isClear) {
	List<SObject> sobjs = new List<SObject>();
	for (MySObject mySobj : Records) {
		if (isClear) {
			mySObj.ResetMetaphone();
		} else {
			mySObj.CalculateMetaphone();
		}
		sobjs.add(mySobj.sobj);
	}
	// DML...
	Database.Update(sobjs, true);
	records = null;
	return null;    
}

This code is part of a controller in this Visualforce page

<apex:page controller="demoDynamicApex" tabStyle="ClassDemos__tab">
	<apex:sectionHeader title="Metaphone" subtitle="Dynamic Apex" />
	<apex:form id="TheForm">
		<apex:messages />
		<apex:pageBlock >
			<apex:pageBlockButtons location="top">
				<b>Object: </b>
				<apex:selectList value="{!SelectedObject}" size="1" >
					<apex:selectOptions value="{!ListObjects}" />
					<apex:actionSupport event="onchange" rerender="TheForm"/>
				</apex:selectList>
				<apex:commandButton value="Clear Values" action="{!ClearRecords}"
					disabled="{!SelectedObject == ''}" rerender="TheForm" status="TheStatus" />
				<apex:commandButton value="Process Records" action="{!ProcessRecords}"
					disabled="{!SelectedObject == ''}" rerender="TheForm" status="TheStatus" />
				<apex:actionStatus id="TheStatus" startText="Processing..." stopText="" />
			</apex:pageBlockButtons>
			<apex:pageBlockSection columns="1">
				<apex:pageblockTable value="{!Records}" var="r" rendered="{!SelectedObject != ''}" >
					<apex:column headerValue="Key" value="{!r.MetaphoneSource}" />
					<apex:column headerValue="Metaphone" value="{!r.Metaphone}" />
				</apex:pageblockTable>
			</apex:pageBlockSection>
		</apex:pageBlock>
	</apex:form>
</apex:page>

This is the full code for the controller

public class demoDynamicApex {
    public demoDynamicApex() {
        FindObjects();
        SelectedObject = ListObjects[0].getValue();
    }

// Dynamic Apex: SOQL Records    
    public List<MySObject> Records {
        get {
            if ((Records == null) && (SelectedObject != '')) {
                Records = new List<MySObject>();
                // SOQL...
                String SOQL = 'SELECT Metaphone__c, MetaphoneSource__c FROM ' + SelectedObject;
                for (List<SObject> sobjs : Database.query(SOQL)) {
                    for (SObject sobj : sobjs) {
                        records.add(new MySObject(sobj));
                    }
                }
            }
            return Records;
        }
        private set;
    }

// Dynamic Apex: DML Records
    private PageReference ProcessRecords(Boolean isClear) {
        List<SObject> sobjs = new List<SObject>();
        for (MySObject mySobj : Records) {
            if (isClear) {
                mySObj.ResetMetaphone();
            } else {
                mySObj.CalculateMetaphone();
            }
            sobjs.add(mySobj.sobj);
        }
        // DML...
        Database.Update(sobjs, true);
        records = null;
        return null;    
    }
    
// Dynamic Apex: Find Objects
    public void FindObjects() {
        Schema.DescribeSObjectResult DR_SObject;
        Map<String, Schema.sObjectField> mapToken_Fields;
        Map<String, Schema.sObjectType> mapToken_SObjects;
        List<String> sortedNames = new List<String>();

        // Find all objects
        mapToken_SObjects = Schema.getGlobalDescribe();
        sortedNames = new List<String>();
        sortedNames.addAll(mapToken_SObjects.keyset());
        sortedNames.Sort();

        ListObjects = new List<SelectOption>();
        ListObjects.add(new SelectOption('', '-- NONE --', true));
        for (string nameSObject : sortedNames) {
            // Use sObject describe to find information about object...
            DR_SObject = mapToken_SObjects.get(nameSObject).getDescribe();
            if (DR_SObject.isSearchable() && DR_SObject.isUpdateable()) {
                // Field token map...
                mapToken_Fields = DR_SObject.fields.getMap();
                if (mapToken_Fields.containsKey('MetaphoneSource__c')) {
                    ListObjects.add(new SelectOption(nameSObject, DR_SObject.getLabel()));
                }
            }
        }
    }

// Helper Classes: GUI Table (uses Dynamic Apex to set/get field values)
    public class MySObject {
        public String Metaphone { get; set; }
        public String MetaphoneSource { get; set; }
        public SObject sobj {
            get {
                // Dynamic Apex: Update field value
                if (sobj != null) sObj.put('Metaphone__c', Metaphone);
                return sobj;
            }
            set;
        }

        public MySObject(sObject sobj) {
            this.sObj = sobj;
            // Dynamic Apex: Get field values
            Metaphone = String.valueOf(sobj.get('Metaphone__c'));
            MetaphoneSource = String.valueOf(sobj.get('MetaphoneSource__c'));
        }
        public void ResetMetaphone() {
            Metaphone = '';
        }
        public void CalculateMetaphone() {
            Metaphone = demoMetaphoneUtil.calculate(MetaphoneSource);
        }
    }

    
// GUI
    public List<SelectOption> ListObjects { get; set; }
    public String SelectedObject {
        get;
        set {
            SelectedObject = value;
            Records = null;
        }
    }

    public PageReference ClearRecords() {
        return ProcessRecords(true);
    }
    public PageReference ProcessRecords() {
        return ProcessRecords(false);
    }
}

Finally, this is the class that calculates the Metaphone values, which I slightly modified from the code written by Kyle Thornton and posted in https://github.com/cloudspokes/apex-metaphone

public class demoMetaphoneUtil {
	public static String calculate(String compareString){
		//Start with a string that is all uppercase
		String startStr = compareString.toUpperCase();
 
		String cs = '';  //this will be our "compare string"
		String result = ''; //this is our result which we will return at the end of this method
		 
		//check each letter in the string to see if the next letter is the same, if so, drop it, unless it is C
		for(Integer i=0;i<startStr.length()-1;i++){
			if(!startStr.substring(i,i+1).equals(startStr.substring(i+1,i+2)) ||
					startStr.substring(i,i+1).equals('C')){
				cs += startStr.substring(i,i+1);
			}
		}
		//Add the last letter of the string to the cs string since we didn't get to it in the loop above
		cs += startStr.substring(startStr.length()-1, startStr.length());
		 
		//If the word begins with 'KN', 'GN', 'PN', 'AE', 'WR', drop the first letter.
		if( cs.startsWith('KN') ||
			cs.startsWith('GN') ||
			cs.startsWith('PN') ||
			cs.startsWith('AE') ||
			cs.startsWith('WR')
		  ){
			cs = cs.substring(1,cs.length());
		}
		 
		//Create a set containing the 5 vowels.  This will be used multiple times while checking for patterns
		Set<String> VOWELS = new Set<String>();
		VOWELS.add('A');
		VOWELS.add('E');
		VOWELS.add('I');
		VOWELS.add('O');
		VOWELS.add('U');
			 
		if(cs.endsWith('MB')){
				cs = cs.substring(0,cs.length()-1);
		}
		 
		//Create integer to hold length of the compare string.  cleans up the code a little.
		Integer cslen = cs.length();
		 
		//loop through the string and check each letter, matching against the metaphone patters.
		for(Integer i=0;i<cslen;i++){
				 
				//If string starts with vowel, keep it, otherwise drop it.
				if(i==0){
					for(String s : VOWELS){
						if(cs.substring(0,1).equals(s)){ result += s; }
					}
				}
				 
				if(cs.substring(i,i+1).equals('B')){
					result += 'B';
					continue;
				}
				 
				//'C' transforms to 'X' if followed by 'IA' or 'H' (unless in latter case, it is part of '-SCH-',
				//		in which case it transforms to 'K').
				//'C' transforms to 'S' if followed by 'I', 'E', or 'Y'. Otherwise, 'C' transforms to 'K'.
				//CH transforms to K if at the beginning
				if(cs.substring(i,i+1).equals('C')){
					if(i < cslen-2 && cs.substring(i+1,i+3).equals('IA')){
						result += 'X';
					}else if(i < cslen-1 && cs.substring(i+1,i+2).equals('H')){
						if(i>0 && cs.substring(i-1,i).equals('S')){
							result += 'K';
							i++;
						}else{
							result += 'X';
							i++;
						}
						}else if(i<cslen-1 &&
							 (cs.substring(i+1,i+2).equals('I') ||
							  cs.substring(i+1,i+2).equals('E') ||
							  cs.substring(i+1,i+2).equals('Y'))
						   ){
									 
						if(i>0 && cs.substring(i-1,i).equals('S')){
								//do nothing and drop C, it is slient
						}else{
								result += 'S';
						}
					}else{
						result += 'K';
					}
					continue;
				}
				 
				//'D' transforms to 'J' if followed by 'GE', 'GY', or 'GI'. Otherwise, 'D' transforms to 'T'.
				if(cs.substring(i,i+1).equals('D')){
					if(i<cslen-2 &&
						(
							cs.substring(i+1,i+3).equals('GE') ||
							cs.substring(i+1,i+3).equals('GY') ||
							cs.substring(i+1,i+3).equals('GI')
						)){
						result += 'J';
					}else{
						result += 'T';
					}
					continue;
				}
				 
				if(cs.substring(i,i+1).equals('F')){
					result += 'F';
					continue;
				}
				 
				//Drop 'G' if followed by 'H' and 'H' is not at the end or before a vowel.
				//Drop 'G' if followed by 'N' or 'NED' and is at the end.
				//'G' transforms to 'J' if before 'I', 'E', or 'Y', and it is not in 'GG'.
				//	Otherwise, 'G' transforms to 'K'. Reduce 'GG' to 'G'.
				if(cs.substring(i,i+1).equals('G')){
					if((i==cslen-2 && cs.substring(i,i+2).equals('GN')) ||
							(i==cslen-4 && cs.substring(i,i+4).equals('GNED'))){
						//do nothing (ie drop G) since G is followed by N or NED and is at the end
					}else if(i==0 && cslen >2 && cs.substring(1,2).equals('H') &&
							VOWELS.contains(cs.substring(2,3))){
						result += 'K'; //GH at the beginning should result in 'K'
						i++;           //we know H is next and is taken care of, so skip it
					}else if((i<cslen-2 && cs.substring(i+1,i+2).equals('H') &&
							!VOWELS.contains(cs.substring(i+2,i+3))) ||
										(i<cslen-1 && cs.substring(i+1,i+2).equals('H'))){
						//do nothing (ie drop G and H) since H is not followed by a vowel
						i++;
					}else if(i<cslen-1 &&
							  (cs.substring(i+1,i+2).equals('I')||
							   cs.substring(i+1,i+2).equals('E') ||
							   cs.substring(i+1,i+2).equals('Y')
							  )
							){
						result += 'J';
						if(cs.substring(i+1,i+2).equals('Y')){ //then y is considered a vowel here
								i++;
						}
					}else{
						result += 'K';
						//if(i<cslen-2 && cs.substring(i+1,i+2).equals('H')){ // H is not at end and is silent here
						//      i++;
						//      continue;
						//}
					}
					continue;
				}
				 
				//Drop 'H' if after vowel and not before a vowel.
				if(cs.substring(i,i+1).equals('H')){
					if(i>0 && VOWELS.contains(cs.substring(i-1,i))){
						//we have a vowel before H
						if((i<cslen-1 && !VOWELS.contains(cs.substring(i+1,i+2))) || i==cslen-1){
							//do nothing since a vowel is after H as well
						}else{
							result += 'H'; //H is at the end or not followed by a vowel
						}
					}else{
						result += 'H';
					}
					continue;
				}
				 
				if(cs.substring(i,i+1).equals('J')){
					result += 'J';
					continue;
				}
				 
				//'CK' transforms to 'K'.
				if(cs.substring(i,i+1).equals('K')){
					if(i>0 && cs.substring(i-1,i).equals('C')){
						//do nothing since the K is silent (the C was already transformed to K earlier
					}else{
						result +='K';
					}
					continue;
				}
				 
				if(cs.substring(i,i+1).equals('L')){
					result += 'L';
					continue;
				}
				 
				if(cs.substring(i,i+1).equals('M')){
					result += 'M';
					continue;
				}
				 
				if(cs.substring(i,i+1).equals('N')){
					result += 'N';
					continue;
				}
		 
				//'PH' transforms to 'F'. (the H will get dropped later)
				if(cs.substring(i,i+1).equals('P')){
					if(i<cslen-1 && cs.substring(i+1,i+2).equals('H')){
						result += 'F';
						i++;
					 }else{
						result += 'P';
					}
					continue;
				}
				 
		 
				//'Q' transforms to 'K'.
				if(cs.substring(i,i+1).equals('Q')){
					result += 'K';
					continue;
				}
				 
				if(cs.substring(i,i+1).equals('R')){
					result += 'R';
					continue;
				}
		 
				//'S' transforms to 'X' if followed by 'H', 'IO', or 'IA'.
				if(cs.substring(i,i+1).equals('S')){
					if( (i<cslen-1 && cs.substring(i+1,i+2).equals('H')) ||
						(i<cslen-2 && (cs.substring(i+1,i+3).equals('IO') || cs.substring(i+1,i+3).equals('IA') ))
					  ){
						result += 'X';
						if(cs.substring(i+1,i+2).equals('H')){
								i++;
						}
					}else{
						result += 'S';
					}
					continue;
				}
		 
				//'T' transforms to 'X' if followed by 'IA' or 'IO'. 'TH' transforms to '0'.
				//	Drop 'T' if followed by 'CH'.
				if(cs.substring(i,i+1).equals('T')){
					if(i<cslen-2 && (cs.substring(i+1,i+3).equals('IO') || cs.substring(i+1,i+3).equals('IA'))){
						result += 'X';
					}else if(i<cslen-1 && cs.substring(i+1,i+2).equals('H')){
						result += '0'; //this is a zero, representing 'theta'
						i++; //TH just became 0 so drop the H is no needed.
					}else if(i<cslen-2 && cs.substring(i+1,i+3).equals('CH')){
						//do nothing since T is followed by CH and thus gets dropped
					}else{
						result += 'T';
					}
					continue;
				}
		 
				//'V' transforms to 'F'.
				if(cs.substring(i,i+1).equals('V')){
					result += 'F';
					continue;
				}
		 
				//'WH' transforms to 'W' if at the beginning (done earlier). Drop 'W' if not followed by a vowel.
				if(cs.substring(i,i+1).equals('W')){
					if(i==0 && cslen>1 && cs.substring(i+1,i+2).equals('H')){
						result+='W';
						i++;
					}else if(i<cslen-1 && VOWELS.contains(cs.substring(i+1,i+2))){
						result += 'W';
					}
					continue;
				}
		 
				//'X' transforms to 'S' if at the beginning. Otherwise, 'X' transforms to 'KS'.
				if(cs.substring(i,i+1).equals('X')){
					if(i==0){
						result += 'S';
					}else{
						result += 'KS';
					}
					continue;
				}
				 
				//Drop 'Y' if not followed by a vowel.
				if(cs.substring(i,i+1).equals('Y')){
					if(i<cslen-1 && VOWELS.contains(cs.substring(i+1,i+2))){
						result += 'Y';
					}
					continue;
				}
				 
				//'Z' transforms to 'S'.
				if(cs.substring(i,i+1).equals('Z')){
					result += 'S';
					continue;
				}
				 
		}//end for loop
		return result;
	}
	 
	private static testmethod void testCalculate() {
		System.AssertEquals('B',demoMetaphoneUtil.calculate('bbb'));//removal of duplicate letters, and return B
		System.AssertEquals('N',demoMetaphoneUtil.calculate('KN')); //starts with KN       
		System.AssertEquals('AR',demoMetaphoneUtil.calculate('aR')); //starts with vowel
		System.AssertEquals('M',demoMetaphoneUtil.calculate('MB')); //drop B at end after M
		System.AssertEquals('X',demoMetaphoneUtil.calculate('CIA')); //CIA => X
		System.AssertEquals('TX',demoMetaphoneUtil.calculate('TCIA')); //CIA => X
		System.AssertEquals('X',demoMetaphoneUtil.calculate('CH')); //CH =>X
		System.AssertEquals('K',demoMetaphoneUtil.calculate('C')); //C =>K
		System.AssertEquals('S',demoMetaphoneUtil.calculate('CI')); //CI =>S
		System.AssertEquals('S',demoMetaphoneUtil.calculate('CE')); //CE =>S
		System.AssertEquals('S',demoMetaphoneUtil.calculate('CY')); //CY =>S
		System.AssertEquals('SK',demoMetaphoneUtil.calculate('SCH')); //SCH =>SK
		System.AssertEquals('JJ',demoMetaphoneUtil.calculate('DGE')); //DGE =>JJ
		System.AssertEquals('JJ',demoMetaphoneUtil.calculate('DGY')); //DGY =>JJK
		System.AssertEquals('JJ',demoMetaphoneUtil.calculate('DGI')); //DGI =>JJ
		System.AssertEquals('T',demoMetaphoneUtil.calculate('D')); //D =>T
		System.AssertEquals('F',demoMetaphoneUtil.calculate('F')); //F has no transform
		System.AssertEquals('K',demoMetaphoneUtil.calculate('GHA')); //GH Becomes K if at beginning
		System.AssertEquals('NT',demoMetaphoneUtil.calculate('GNED'));//Drop G if part of GNED and at end, D goes to T
		System.AssertEquals('N',demoMetaphoneUtil.calculate('GN'));//Drop G if part of GN and at end
		System.AssertEquals('NT',demoMetaphoneUtil.calculate('NIGHT'));//Drop GH if not followed by vowel
		System.AssertEquals('K',demoMetaphoneUtil.calculate('G'));//G => K otherwise
		System.AssertEquals('S',demoMetaphoneUtil.calculate('SOH'));//Drop H if after vowel but not before vowel
		System.AssertEquals('SH',demoMetaphoneUtil.calculate('SOHO'));//H stays if after vowel and before vowel
		System.AssertEquals('H',demoMetaphoneUtil.calculate('H'));//H => H
		System.AssertEquals('I',demoMetaphoneUtil.calculate('I'));//I at beginning = I
		System.AssertEquals('J',demoMetaphoneUtil.calculate('J'));//J => J
		System.AssertEquals('K',demoMetaphoneUtil.calculate('CK'));//CK => K and K=>K
		System.AssertEquals('K',demoMetaphoneUtil.calculate('K'));//CK => K and K=>K
		System.AssertEquals('L',demoMetaphoneUtil.calculate('L'));//L=>L
		System.AssertEquals('M',demoMetaphoneUtil.calculate('M'));//M=>M
		System.AssertEquals('N',demoMetaphoneUtil.calculate('N'));//N=>N
		System.AssertEquals('F',demoMetaphoneUtil.calculate('PH'));//PH=>F
		System.AssertEquals('P',demoMetaphoneUtil.calculate('P'));//P=>P
		System.AssertEquals('K',demoMetaphoneUtil.calculate('Q'));//Q=>K
		System.AssertEquals('R',demoMetaphoneUtil.calculate('R'));//R=>R
		System.AssertEquals('X',demoMetaphoneUtil.calculate('SH'));//SH=>X
		System.AssertEquals('X',demoMetaphoneUtil.calculate('SIO'));//SIO=>X
		System.AssertEquals('X',demoMetaphoneUtil.calculate('SIA'));//SIA=>X
		System.AssertEquals('S',demoMetaphoneUtil.calculate('S'));//S=>S
		System.AssertEquals('S',demoMetaphoneUtil.calculate('SCI'));//SCI=>S
		System.AssertEquals('X',demoMetaphoneUtil.calculate('TIA'));//TIA=>X
		System.AssertEquals('X',demoMetaphoneUtil.calculate('TIO'));//TIA=>X
		System.AssertEquals('0',demoMetaphoneUtil.calculate('TH'));//TIA=>X
		System.AssertEquals('X',demoMetaphoneUtil.calculate('TCH'));//T gets dropped and CH becomes X
		System.AssertEquals('T',demoMetaphoneUtil.calculate('T'));//T=>T
		System.AssertEquals('F',demoMetaphoneUtil.calculate('V'));//V=>F
		System.AssertEquals('W',demoMetaphoneUtil.calculate('WH'));//WH=>W
		System.AssertEquals('',demoMetaphoneUtil.calculate('W'));//W gets dropped if not followed by vowel
		System.AssertEquals('W',demoMetaphoneUtil.calculate('WA'));//WA=>W
		System.AssertEquals('S',demoMetaphoneUtil.calculate('X'));//X at beginning transforms to S
		System.AssertEquals('SKS',demoMetaphoneUtil.calculate('SAX'));//otherwise X transforms to KS
		System.AssertEquals('',demoMetaphoneUtil.calculate('Y'));//Y gets dropped if not followed by vowel
		System.AssertEquals('YS',demoMetaphoneUtil.calculate('YES'));//otherwise it is a Y
		System.AssertEquals('S',demoMetaphoneUtil.calculate('Z'));//Z=>S
	}
}

comments powered by Disqus

© El Toro . IT @ 2013
Andrés Pérez