+ Start a Discussion
Ravi23Ravi23 

How to write a where clause in Batch class string query?

Hi All,

         Below is my Batch class. Is that always we require to pull complete records in databse in query field or can we filter using where condition. If we can filter can some one help me on this as I am getting errors

global class batchContactAccountNameUpdate implements Database.Batchable<sObject> {
    global Database.QueryLocator start(Database.BatchableContext BC) {
       String query = 'SELECT Id, AccountId,Type__C  FROM Contact where Type__c=:Asian Entities';
        return Database.getQueryLocator(query);
    }
   
    global void execute(Database.BatchableContext BC, List<Contact> scope) {
         for(Contact c : scope)
             
         {
             if(c.AccountId==null){
             
             c.AccountId = '0011900000BiZzn';            
         }
         }
         update scope;
    }   
    
    global void finish(Database.BatchableContext BC) {
    }
}
Best Answer chosen by Ravi23
Alexander TsitsuraAlexander Tsitsura
Yes, you can use next query for it
String query = 'SELECT Id, AccountId,Type__C  FROM Contact where Type__c= \'Asian Entities\' AND AccountId = NULL';

 

All Answers

Alexander TsitsuraAlexander Tsitsura
Hi Ravi,

Try this
global class batchContactAccountNameUpdate implements Database.Batchable<sObject> {
    global Database.QueryLocator start(Database.BatchableContext BC) {
       String contactType = 'Asian Entities';
       String query = 'SELECT Id, AccountId,Type__C  FROM Contact where Type__c=:contactType';
        return Database.getQueryLocator(query);
    }
   
    global void execute(Database.BatchableContext BC, List<Contact> scope) {
         for(Contact c : scope)
             
         {
             if(c.AccountId==null){
             
             c.AccountId = '0011900000BiZzn';            
         }
         }
         update scope;
    }   
    
    global void finish(Database.BatchableContext BC) {
    }
}

Thank,
Alex
Vishal Negandhi 16Vishal Negandhi 16
Since your filter itself is a string, just change your query code to below:
 String query = 'SELECT Id, AccountId,Type__C  FROM Contact where Type__c= \'Asian Entities\'';

Using this \', we are escaping the string quotes so it takes your filter value as a string value. 

Hope this helps. 
Ravi23Ravi23
Hi Alex and Vishal thx for the help both of themwork. Can I also refine the query stil where it checks for type Asian Entities and accountId==null if so can you please help me on that as well it would be very helpful.
Alexander TsitsuraAlexander Tsitsura
Yes, you can use next query for it
String query = 'SELECT Id, AccountId,Type__C  FROM Contact where Type__c= \'Asian Entities\' AND AccountId = NULL';

 
This was selected as the best answer
Ravi23Ravi23
Hi Alex,

              Thanks a lot for your help it worked as expected. Can I request your final help how to write a test class and schedule this batch class if we write a schedule class for this batch class do we need to write test class for the schedule class also can you help me on the same?
Alexander TsitsuraAlexander Tsitsura
Yes, i can help you. I recomended you write shedule and batch in the same class, for it you need add Schedulable interface for you current class and impelemnt shedulable method
 
public void execute(SchedulableContext sc) {
	Database.executeBatch(new batchContactAccountNameUpdate());
}

For run shedule job you can use system.schedule method
Ravi23Ravi23
Alex I am trying to schedule at a particular interval so do I have to write my schedule class seperately or in same batch class? and do we need two test cases for schedule class and batch class?
Alexander TsitsuraAlexander Tsitsura
You can reshedule job, and it not required for create separate class. And yes you need create at least 2 test cases.

This is my example of reshedule job.
public void execute(SchedulableContext sc) {
    String jobId = String.valueOf(sc.getTriggerId());
	System.abortJob(jobId);

	Database.executeBatch(new batchContactAccountNameUpdate());

	// run new schedule job
	final DateTime d = DateTime.now().addMinutes(5);
	System.schedule(
		'My job',
		d.format('ss mm HH dd MM ? yyyy'),
		new batchContactAccountNameUpdate()
	);
}
Ravi23Ravi23
Hi Alex,

           I wrote a seperate schedule class as below

global class Scheduler_class implements Schedulable{

    public static String sched = '0 00 00 * * ?';  //Every Day at Midnight 

    global static String scheduleMe() {
        Scheduler_class SC = new Scheduler_class(); 
        return System.schedule('UpdateConstantContactAccountswithoutNames', sched, SC);
    }

    global void execute(SchedulableContext sc) {

        batchContactAccountNameUpdate b1 = new batchContactAccountNameUpdate();
        ID batchprocessid = Database.executeBatch(b1,50);           
    }
}

It is working fine can you help me in writing the tezt class for the batch class and schedule class
Alexander TsitsuraAlexander Tsitsura
You can use this example from https://developer.salesforce.com/docs/atlas.en-us.apex_workbook.meta/apex_workbook/apex_batch_2.htm for batch tests.
 
@isTest
private class TestCleanUpBatchClass {

    static testmethod void test() {
        // The query used by the batch job.
        String query = 'SELECT Id,CreatedDate FROM Merchandise__c ' + 
                   'WHERE Id NOT IN (SELECT Merchandise__c FROM Line_Item__c)';

       // Create some test merchandise items to be deleted
       //   by the batch job.
       Merchandise__c[] ml = new List<Merchandise__c>();
       for (Integer i=0;i<10;i++) {
           Merchandise__c m = new Merchandise__c(
               Name='Merchandise ' + i,
               Description__c='Some description',
               Price__c=2,
               Total_Inventory__c=100);
           ml.add(m);
       }
       insert ml;

       Test.startTest();
       CleanUpRecords c = new CleanUpRecords(query);
       Database.executeBatch(c);
       Test.stopTest();

       // Verify merchandise items got deleted 
       Integer i = [SELECT COUNT() FROM Merchandise__c];
       System.assertEquals(i, 0);
    }
}

 
Ravi23Ravi23
Hi Alex

       I tried the similar way it is giving me the below error

Constructor not defined: [batchContactAccountNameUpdate].&lt;Constructor&gt;(String)

Can you help me where I am going wrong?
Ravi23Ravi23
Alex finally I got it done using some examples. Thanks a lot for all the help. Have a good day
mohd zulfequar 7mohd zulfequar 7
Write code to retrieve the list of Merchandise bought by Customer James. Find out the merchandise name and price list.