public void testLeftJoinNoRowsOrSingleRow() throws Exception {
SelectItem item1 = new SelectItem("foo", "f");
SelectItem item2 = new SelectItem("bar", "b");
SelectItem item3 = new SelectItem("baz", "z");
List<SelectItem> selectItems1 = Arrays.asList(item1, item2);
List<SelectItem> selectItems2 = Arrays.asList(item3);
DataSet ds1 = new EmptyDataSet(selectItems1);
DataSet ds2 = new EmptyDataSet(selectItems2);
DataSet joinedDs =
MetaModelHelper.getLeftJoin(
ds1, ds2, new FilterItem[] {new FilterItem(item2, OperatorType.EQUALS_TO, item3)});
assertEquals(SubSelectionDataSet.class, joinedDs.getClass());
assertEquals("[foo AS f, bar AS b, baz AS z]", Arrays.toString(joinedDs.getSelectItems()));
DataSetHeader header1 = new SimpleDataSetHeader(selectItems1);
Row row = new DefaultRow(header1, new Object[] {1, 2}, null);
ds1 = new InMemoryDataSet(header1, row);
joinedDs =
MetaModelHelper.getLeftJoin(
ds1, ds2, new FilterItem[] {new FilterItem(item2, OperatorType.EQUALS_TO, item3)});
assertEquals("[foo AS f, bar AS b, baz AS z]", Arrays.toString(joinedDs.getSelectItems()));
assertTrue(joinedDs.next());
assertEquals("Row[values=[1, 2, null]]", joinedDs.getRow().toString());
assertFalse(joinedDs.next());
}
public void testLeftJoin() throws Exception {
SelectItem si1 = new SelectItem(new MutableColumn("person_id", ColumnType.INTEGER));
SelectItem si2 = new SelectItem(new MutableColumn("person_name", ColumnType.VARCHAR));
SelectItem si3 = new SelectItem(new MutableColumn("person_age", ColumnType.INTEGER));
SelectItem si4 = new SelectItem(new MutableColumn("person_role_id", ColumnType.INTEGER));
SelectItem si5 = new SelectItem(new MutableColumn("role_id", ColumnType.INTEGER));
SelectItem si6 = new SelectItem(new MutableColumn("role_name", ColumnType.VARCHAR));
SelectItem si7 = new SelectItem(new MutableColumn("role_code", ColumnType.VARCHAR));
List<Object[]> data1 = new ArrayList<Object[]>();
data1.add(new Object[] {1, "peter", 18, 1});
data1.add(new Object[] {2, "tom", 19, 2});
data1.add(new Object[] {3, "betty", 19, null});
data1.add(new Object[] {4, "barbara", 17, 3});
data1.add(new Object[] {5, "susie", 18, 4});
List<Object[]> data2 = new ArrayList<Object[]>();
data2.add(new Object[] {1, "class president", "clpr"});
data2.add(new Object[] {2, "bad boy", "bb"});
data2.add(new Object[] {4, "trying harder", "try"});
DataSet ds1 = createDataSet(new SelectItem[] {si1, si2, si3, si4}, data1);
DataSet ds2 = createDataSet(new SelectItem[] {si5, si6, si7}, data2);
FilterItem[] onConditions = new FilterItem[1];
onConditions[0] = new FilterItem(si4, OperatorType.EQUALS_TO, si5);
DataSet result = MetaModelHelper.getLeftJoin(ds1, ds2, onConditions);
List<Object[]> objectArrays = result.toObjectArrays();
assertEquals(
"[1, peter, 18, 1, 1, class president, clpr]", Arrays.toString(objectArrays.get(0)));
assertEquals("[2, tom, 19, 2, 2, bad boy, bb]", Arrays.toString(objectArrays.get(1)));
assertEquals("[3, betty, 19, null, null, null, null]", Arrays.toString(objectArrays.get(2)));
assertEquals("[4, barbara, 17, 3, null, null, null]", Arrays.toString(objectArrays.get(3)));
assertEquals("[5, susie, 18, 4, 4, trying harder, try]", Arrays.toString(objectArrays.get(4)));
assertEquals(5, objectArrays.size());
}
