Query filters

The query filters of the QuickBooks Online Java SDK enable your app to retrieve entities whose properties meet specified criteria. For example, your app can retrieve only those customer entities that have been created within the last twenty days. The SDK includes a QueryFilter toolkit which enables construction of static Intuit queries using fluent syntax. The following steps show how to define and execute queries.

1. Build the Context object.

Context is a parameter for all calls to QuickBooks Online Data Services.

1. Create the OAuth2Authorizer object. A valid OAuth2Authorizer object ensures that the end-user has authorized your app to access QuickBooks Online data. The following code creates an OAuth2Authorizer object:

1
2
3

String accessToken = ...

OAuth2Authorizer oauth = new OAuth2Authorizer(accessToken);

2. Create an instance of the Context class. The following code snippet creates a Context object:

1
2
3
4

String appToken = ...
String realmId = ...

Context context = new Context(oauth, appToken, ServiceType.QBO, realmId);

For more information, see Authorization.

2. Create the DataService object.

Create an instance of DataService by passing the ``Context``object created in Step 2 as the argument:

1

DataService service = new DataService(context);

3. Create the Query proxy entity.

Create a proxy instance for an entity by passing the Entity class as the argument.

The following example shows how to create a query proxy entity for a customer entity:

1

Customer customer = GenerateQuery.createQueryEntity(Customer.class);

The following example shows how to create a query proxy entity for an Invoice entity:

1

Invoice invoice = GenerateQuery.createQueryEntity(Invoice.class);

4. Generate the query string.

Add static imports for $ and select:

1
2

import static com.intuit.ipp.query.GenerateQuery.$;
import static com.intuit.ipp.query.GenerateQuery.select;

For example, the following query returns all Invoice entities with a Balance property greater than 1000:

1

String query = select($(invoice)).where($(invoice.getBalance()).gt(1000)).generate();

See Query code samples below for code samples to query Customer and Invoice entities using query filter clauses. For the corresponding QuickBooks Online API query code samples, see Query language operations.

5. Execute the query string.

Execute the generated queries in step 4 by passing the service object created in step 2 as an attribute:

1

QueryResult queryResult = service.executeQuery(query);

If the API does not return any results, then queryResult is null.

The filter in the where clause determines which entities are returned by a query. The filter criteria is based on the value of a property. For example, the following query returns all Invoice entities with a Balance property greater than 1000:

1

String query = select($(invoice)).where($(invoice.getBalance()).gt(1000)).generate();

The following statement gets properties for the Invoice entities whose Customer ID properties match the values of the in operator:

1

String query = select($(invoice)).where($(invoice.getId()).in(new String[]{"1234", "5678", "9012"})).generate();

When multiple filter statements are defined in a single statement as shown in this section, the SDK combines the statements using the AND operation. (The OR operation is not supported.) The following query returns all Invoice entities created during the specified time range:

1
2
3
4
5
6
7
8
9

Calendar start = Calendar.getInstance();
start.set(2009, 10, 14, 04, 05, 05);
Calendar end = Calendar.getInstance();
end.set(2012, 10, 14, 04, 05, 05);

String query = select($(invoice))
.where($(invoice.getMetaData().getCreateTime()).gte(start))
.where($(invoice.getMetaData().getCreateTime()).lte(end))
.generate();

The following statement gets all properties for the Customer entities whose first name starts with “G” and ends with “g”, and whose last name starts with “Kesl”:

1
2
3
4

String query = select($(customer))
.where($(customer.getGivenName()).contains("G*g"))
.where($(customer.getFamilyName()).contains("Kesl"))
.generate();

Get all properties for the Invoice entities with transaction dates between Jan. 1, 2011 and the current date:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

//calendar start variable in GMT timezone
Calendar start = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
//set the date and time values
start.set(2011, 1, 1, 0, 0, 0);
java.sql.Date startDate = new java.sql.Date(start.getTimeInMillis());
//calendar end variable in GMT timezone
Calendar end = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
java.sql.Date endDate = new java.sql.Date(end.getTimeInMillis());

String query = select($(invoice)).where($(invoice.getTxnDate()).gt(startDate)).where($(invoice.getTxnDate()).lt(endDate)).generate();

The following statement uses two && operators. It returns all Invoice entities specified by IDs of the in operator, created during the specified time:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

Calendar start = Calendar.getInstance();
   // set the date and time values
   start.set(1990, 12, 12, 12, 50, 30);
   Calendar end = Calendar.getInstance();
   end.set(1990, 12, 13, 12, 50, 30);
   Invoice invoice = Query.createQueryEntity(Invoice.class);
   String query = select($(invoice))
      .where($(invoice.getId()).in(new String[] { "QB:64523", "QB:18761", "QB:35767" }))
      .where($(invoice.getMetaData().getCreateTime()).gt(start))
      .where($(invoice.getMetaData().getCreateTime()).lt(end))
      .generate();

The following statement gets all pending Invoice entities:

1

String query = select($(invoice)).where($(invoice.getStatus()).eq(EntityStatusEnum.PENDING)).generate();

The following statement gets all Invoice entities with a transaction status of either Paid or Payable:

1

String query = select($(invoice)).where($(invoice.getTxnStatus()).in(new String[]{ "Paid", "Payable"})).generate();

The following statement gets all active Customer entities:

1

String query = select($(customer)).where($(customer.isActive()).eq(true)).generate();

The following statement gets the Customer entity for the specified identifier:

1

String query = select($(customer)).where($(customer.getId()).eq("123456")).generate();

The following statement gets the FamilyName, MiddleName, and GivenName properties of the Customer entities specified by the IDs of the In operator:

1
2

String query = select($(customer.getMiddleName()), $(customer.getFamilyName()), $(customer.getGivenName()))
.where($(customer.getId()).in(new String[]{ "12345", "23456", "34567", "45678", "56789", "67890" })).generate();

To sort the results of a query, include the OrderBy clause, specifying a property as the sort field. For example, the following query sorts Customer entities by the FamilyName property:

1

String query = select($(customer)).orderBy($(customer.getFamilyName())).generate();

The default sort order is ascending. To indicate descending sort order, call OrderByDescending as shown in the following example:

1

String query = select($(customer)).orderByDescending($(customer.getFamilyName())).generate();

1

String query = select($(customer)).where($(customer.getStatus()).eq(EntityStatusEnum.SYNCHRONIZED)).skip(0).take(10).generate();

The following query gets customers 21 - 25:

1

String query = select($(customer)).where($(customer.getStatus()).eq(EntityStatusEnum.SYNCHRONIZED)).skip(20).take(10).generate();

For large result sets, you may want to find out how many records will be returned by the query. Include the Count keyword in the query. The number of entities is returned in the response. The following query returns the total number of customers that belong to the QuickBooks company:

1

String query = selectCount(customer).generate();

The following query returns the number of customers in a pending state:

1

String query = selectCount(customer).where($(customer.getStatus()).eq(EntityStatusEnum.PENDING)).generate();