@Test
public void testFilterMultiple() throws Exception {
Calendar c = Calendar.getInstance();
c.set(2015, 0, 0, 0, 0);
Timestamp date = new Timestamp(c.getTime().getTime());
DataSet result =
dataSetManager.lookupDataSet(
DataSetFactory.newDataSetLookupBuilder()
.dataset(EXPENSE_REPORTS)
.filter("date", greaterThan(date))
.filter("amount", lowerOrEqualsTo(120.35))
.filter("city", notEqualsTo("Barcelona"))
.buildLookup());
// printDataSet(result);
assertThat(result.getRowCount()).isEqualTo(2);
assertDataSetValue(result, 0, 0, "9.00");
assertDataSetValue(result, 1, 0, "10.00");
// The order of the filter criteria does not alter the result.
result =
dataSetManager.lookupDataSet(
DataSetFactory.newDataSetLookupBuilder()
.dataset(EXPENSE_REPORTS)
.filter("city", notEqualsTo("Barcelona"))
.filter("amount", lowerOrEqualsTo(120.35))
.filter("date", greaterThan(date))
.buildLookup());
// printDataSet(result);
assertThat(result.getRowCount()).isEqualTo(2);
assertDataSetValue(result, 0, 0, "9.00");
assertDataSetValue(result, 1, 0, "10.00");
}
@Test
public void testFilterByString() throws Exception {
DataSet result =
dataSetManager.lookupDataSet(
DataSetFactory.newDataSetLookupBuilder()
.dataset(EXPENSE_REPORTS)
.filter("city", equalsTo("Barcelona"))
.buildLookup());
// printDataSet(result);
assertThat(result.getRowCount()).isEqualTo(6);
assertDataSetValue(result, 0, 0, "1.00");
assertDataSetValue(result, 5, 0, "6.00");
}
@Test
public void testANDExpression() throws Exception {
DataSet result =
dataSetManager.lookupDataSet(
DataSetFactory.newDataSetLookupBuilder()
.dataset(EXPENSE_REPORTS)
.filter("amount", AND(greaterThan(100), lowerThan(150)))
.buildLookup());
// printDataSet(result);
assertThat(result.getRowCount()).isEqualTo(1);
assertDataSetValue(result, 0, 0, "1.00");
}
@Test
public void testLikeOperator() throws Exception {
DataSet result =
dataSetManager.lookupDataSet(
DataSetFactory.newDataSetLookupBuilder()
.dataset(EXPENSE_REPORTS)
.filter(likeTo("city", "Bar%"))
.column("id")
.sort("id", SortOrder.ASCENDING)
.buildLookup());
// printDataSet(result);
assertThat(result.getRowCount()).isEqualTo(6);
assertDataSetValue(result, 0, 0, "1.00");
assertDataSetValue(result, 5, 0, "6.00");
// Case sensitive
result =
dataSetManager.lookupDataSet(
DataSetFactory.newDataSetLookupBuilder()
.dataset(EXPENSE_REPORTS)
.filter(likeTo("city", "%L%", true))
.buildLookup());
assertThat(result.getRowCount()).isEqualTo(7);
// Case un-sensitive
result =
dataSetManager.lookupDataSet(
DataSetFactory.newDataSetLookupBuilder()
.dataset(EXPENSE_REPORTS)
.filter(likeTo("city", "%L%", false))
.buildLookup());
assertThat(result.getRowCount()).isEqualTo(26);
}
@Test
public void testFilterByDate() throws Exception {
Calendar c = Calendar.getInstance();
c.set(2015, 0, 0, 0, 0);
Timestamp date = new Timestamp(c.getTime().getTime());
DataSet result =
dataSetManager.lookupDataSet(
DataSetFactory.newDataSetLookupBuilder()
.dataset(EXPENSE_REPORTS)
.filter("date", greaterThan(new Timestamp(date.getTime())))
.buildLookup());
// printDataSet(result);
assertThat(result.getRowCount()).isEqualTo(15);
}
@Test
public void testFilterByNumber() throws Exception {
DataSet result =
dataSetManager.lookupDataSet(
DataSetFactory.newDataSetLookupBuilder()
.dataset(EXPENSE_REPORTS)
.filter("amount", between(100, 200))
.buildLookup());
// printDataSet(result);
assertThat(result.getRowCount()).isEqualTo(5);
assertDataSetValue(result, 0, 0, "1.00");
assertDataSetValue(result, 1, 0, "6.00");
assertDataSetValue(result, 2, 0, "10.00");
assertDataSetValue(result, 3, 0, "17.00");
assertDataSetValue(result, 4, 0, "33.00");
}
@Test
public void testCombinedExpression() throws Exception {
DataSet result =
dataSetManager.lookupDataSet(
DataSetFactory.newDataSetLookupBuilder()
.dataset(EXPENSE_REPORTS)
.filter(
"amount",
AND(
equalsTo("department", "Sales"),
OR(NOT(lowerThan(300)), equalsTo("city", "Madrid"))))
.buildLookup());
// printDataSet(result);
assertThat(result.getRowCount()).isEqualTo(7);
assertDataSetValue(result, 0, 0, "9.00");
assertDataSetValue(result, 6, 0, "28.00");
}
@Test
public void testFilterUntilToday() throws Exception {
DataSet result =
dataSetManager.lookupDataSet(
DataSetFactory.newDataSetLookupBuilder()
.dataset(EXPENSE_REPORTS)
.filter("date", timeFrame("10second"))
.buildLookup());
// assertThat(result.getRowCount()).isEqualTo(0);
}