Java SE: A Tip for Joining Two Table Together in Java

1. Requirement Specification

    1) After we have loading data from two different file, and then assembly them into two different kind of JavaBean.

    2) We want to join the two list of JavaBean together into a single list of bean using the foreign key.

 

1. The first kind of JavaBean

package edu.xmu.tablejoin.TableJoin_Vo;

public class TableOneVo
{
	private String category;
	private String fdl;
	private String bu;
	private String frsValue;

	public TableOneVo()
	{
	}

	public TableOneVo(String category, String fdl, String bu, String frsValue)
	{
		super();
		this.category = category;
		this.fdl = fdl;
		this.bu = bu;
		this.frsValue = frsValue;
	}

	public String getCategory()
	{
		return category;
	}

	public void setCategory(String category)
	{
		this.category = category;
	}

	public String getFdl()
	{
		return fdl;
	}

	public void setFdl(String fdl)
	{
		this.fdl = fdl;
	}

	public String getBu()
	{
		return bu;
	}

	public void setBu(String bu)
	{
		this.bu = bu;
	}

	public String getFrsValue()
	{
		return frsValue;
	}

	public void setFrsValue(String frsValue)
	{
		this.frsValue = frsValue;
	}

}

 

2. The second of JavaBean

package edu.xmu.tablejoin.TableJoin_Vo;

public class TableTwoVo
{
	private String category;
	private String fdl;
	private String bu;
	private String leaValue;

	public TableTwoVo()
	{
	}

	public TableTwoVo(String category, String fdl, String bu, String leaValue)
	{
		this.category = category;
		this.fdl = fdl;
		this.bu = bu;
		this.leaValue = leaValue;
	}

	public String getCategory()
	{
		return category;
	}

	public void setCategory(String category)
	{
		this.category = category;
	}

	public String getFdl()
	{
		return fdl;
	}

	public void setFdl(String fdl)
	{
		this.fdl = fdl;
	}

	public String getBu()
	{
		return bu;
	}

	public void setBu(String bu)
	{
		this.bu = bu;
	}

	public String getLeaValue()
	{
		return leaValue;
	}

	public void setLeaValue(String leaValue)
	{
		this.leaValue = leaValue;
	}

}

 

3. The Key for Joining Two Table Together

package edu.xmu.tablejoin.TableJoin_Vo;

public class TableKey
{
	private String category;
	private String fdl;

	public TableKey()
	{
	}

	public TableKey(String category, String fdl)
	{
		this.category = category;
		this.fdl = fdl;
	}

	@Override
	public int hashCode()
	{
		final int prime = 31;
		int result = 1;
		result = prime * result
				+ ((category == null) ? 0 : category.hashCode());
		result = prime * result + ((fdl == null) ? 0 : fdl.hashCode());
		return result;
	}

	@Override
	public boolean equals(Object obj)
	{
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		TableKey other = (TableKey) obj;
		if (category == null)
		{
			if (other.category != null)
				return false;
		} else if (!category.equals(other.category))
			return false;
		if (fdl == null)
		{
			if (other.fdl != null)
				return false;
		} else if (!fdl.equals(other.fdl))
			return false;
		return true;
	}

	public String getCategory()
	{
		return category;
	}

	public void setCategory(String category)
	{
		this.category = category;
	}

	public String getFdl()
	{
		return fdl;
	}

	public void setFdl(String fdl)
	{
		this.fdl = fdl;
	}
}

 

4. The Third kind of JavaBean for Storing The Union of Joining the two table together

package edu.xmu.tablejoin.TableJoin_Vo;

public class TableUnionVo
{
	private String category;
	private String fdl;
	private String bu;
	private String frsValue;
	private String leaValue = "-";

	public TableUnionVo()
	{
	}

	public TableUnionVo(String category, String fdl, String bu,
			String frsValue, String leaValue)
	{
		super();
		this.category = category;
		this.fdl = fdl;
		this.bu = bu;
		this.frsValue = frsValue;
		this.leaValue = leaValue;
	}

	public String getCategory()
	{
		return category;
	}

	public void setCategory(String category)
	{
		this.category = category;
	}

	public String getFdl()
	{
		return fdl;
	}

	public void setFdl(String fdl)
	{
		this.fdl = fdl;
	}

	public String getBu()
	{
		return bu;
	}

	public void setBu(String bu)
	{
		this.bu = bu;
	}

	public String getFrsValue()
	{
		return frsValue;
	}

	public void setFrsValue(String frsValue)
	{
		this.frsValue = frsValue;
	}

	public String getLeaValue()
	{
		return leaValue;
	}

	public void setLeaValue(String leaValue)
	{
		this.leaValue = leaValue;
	}

	@Override
	public String toString()
	{
		return "TableGlobalVo [category=" + category + ", fdl=" + fdl + ", bu="
				+ bu + ", frsValue=" + frsValue + ", leaValue=" + leaValue
				+ "]";
	}

}

 

5.  The Main Function of Joining the Two Table Together into One Single Table

package edu.xmu.tablejoin.TableJoin_Core;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import edu.xmu.tablejoin.TableJoin_Vo.TableUnionVo;
import edu.xmu.tablejoin.TableJoin_Vo.TableKey;
import edu.xmu.tablejoin.TableJoin_Vo.TableOneVo;
import edu.xmu.tablejoin.TableJoin_Vo.TableTwoVo;

public class App
{
	public List<TableUnionVo> joinTable(List<TableOneVo> tableOneVoList,
			List<TableTwoVo> tableTwoVoList)
	{
		Map<TableKey, TableUnionVo> globalMap = new HashMap<TableKey, TableUnionVo>();

		for (TableOneVo tableOneVo : tableOneVoList)
		{
			String tableOneCategory = tableOneVo.getCategory();
			String tableOneFdl = tableOneVo.getFdl();
			String tableOneBu = tableOneVo.getBu();
			String tableOneFrsValue = tableOneVo.getFrsValue();

			TableUnionVo globalVo = new TableUnionVo();
			globalVo.setCategory(tableOneCategory);
			globalVo.setFdl(tableOneFdl);
			globalVo.setBu(tableOneBu);
			globalVo.setFrsValue(tableOneFrsValue);

			TableKey key = new TableKey();
			key.setCategory(tableOneCategory);
			key.setFdl(tableOneFdl);

			globalMap.put(key, globalVo);
		}

		for (TableTwoVo tableTwoVo : tableTwoVoList)
		{
			String tableTwoCategory = tableTwoVo.getCategory();
			String tableTwoFdl = tableTwoVo.getFdl();
			String tableTwoLeaValue = tableTwoVo.getLeaValue();

			TableKey key = new TableKey();
			key.setCategory(tableTwoCategory);
			key.setFdl(tableTwoFdl);

			TableUnionVo globalVo = null;

			if (null != (globalVo = globalMap.get(key)))
			{
				globalVo.setLeaValue(tableTwoLeaValue);
			}
		}

		Set<TableKey> keySet = globalMap.keySet();
		List<TableUnionVo> tableGlobalVoList = new ArrayList<TableUnionVo>();
		for (TableKey key : keySet)
		{
			tableGlobalVoList.add(globalMap.get(key));
		}

		return tableGlobalVoList;
	}
}

 

6. The Test Case for Validating the main function works.

package edu.xmu.tablejoin.TableJoin_Core;

import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import edu.xmu.tablejoin.TableJoin_Vo.TableUnionVo;
import edu.xmu.tablejoin.TableJoin_Vo.TableOneVo;
import edu.xmu.tablejoin.TableJoin_Vo.TableTwoVo;

public class AppTest
{
	@Test
	public void testJoin()
	{
		TableOneVo tableOneVo1 = new TableOneVo("AAA", "aaa", "111", "111");
		TableOneVo tableOneVo2 = new TableOneVo("AAA", "bbb", "111", "222");
		TableOneVo tableOneVo3 = new TableOneVo("AAA", "ccc", "111", "222");
		TableOneVo tableOneVo4 = new TableOneVo("BBB", "aaa", "111", "123");
		TableOneVo tableOneVo5 = new TableOneVo("BBB", "bbb", "222", "333");
		TableOneVo tableOneVo6 = new TableOneVo("CCC", "aaa", "333", "444");

		List<TableOneVo> tableOneVoList = new ArrayList<TableOneVo>();

		tableOneVoList.add(tableOneVo1);
		tableOneVoList.add(tableOneVo2);
		tableOneVoList.add(tableOneVo3);
		tableOneVoList.add(tableOneVo4);
		tableOneVoList.add(tableOneVo5);
		tableOneVoList.add(tableOneVo6);

		TableTwoVo tableTwoVo1 = new TableTwoVo("AAA", "aaa", "111", "111");
		TableTwoVo tableTwoVo2 = new TableTwoVo("AAA", "bbb", "111", "222");
		TableTwoVo tableTwoVo3 = new TableTwoVo("AAA", "ccc", "111", "222");
		TableTwoVo tableTwoVo4 = new TableTwoVo("BBB", "aaa", "111", "123");
		TableTwoVo tableTwoVo5 = new TableTwoVo("BBB", "bbb", "222", "333");
		TableTwoVo tableTwoVo6 = new TableTwoVo("DDD", "aaa", "333", "444");

		List<TableTwoVo> tableTwoVoList = new ArrayList<TableTwoVo>();

		tableTwoVoList.add(tableTwoVo1);
		tableTwoVoList.add(tableTwoVo2);
		tableTwoVoList.add(tableTwoVo3);
		tableTwoVoList.add(tableTwoVo4);
		tableTwoVoList.add(tableTwoVo5);
		tableTwoVoList.add(tableTwoVo6);

		App app = new App();
		List<TableUnionVo> tableGlobalVoList = app.joinTable(tableOneVoList,
				tableTwoVoList);

		for (TableUnionVo vo : tableGlobalVoList)
		{
			System.out.println(vo);
		}
	}
}