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()); }