hierarchy from a database table to java object

Question

I have a table as shown below: 'contract'

company    contractor  subcontractor

  intel         x            y
  intel         x            z   
  intel         x            a
  intel         y            a
  intel         y            b
  intel         p            q
  intel         p            r
  intel         q            s
  googl         m            n
  googl         n            r
  googl         n            f

another table 'main contractor'

 company    main_contractor

  intel          x
  googl          m

I need to extract data from this table in to a java object or json as a hierarchy using a java program.

[
 [Intel, [x, [y, [a,b],z,a]],

       [p, [q, [s],r]],
 ],
 [googl, [m, [n, [[r,f]]
         ]
  ]
]

How can I generate such a hierarchical data from the table using a java program? I dont' have special features available in SQL such as 'connect by' or 'with recursive' because I am using Ingres db.

Answer 1

It seems fairly straight forward, I modelled a company with a name and a set of subcontractors. Overriding hashCode and equals so equality depends only on the name for use in the hashSet.

static class Company {

    public String getName() {
        return name;
    }

    public Set<Company> getSubcontractors() {
        return subcontractors;
    }
    final String name;
    final Set<Company> subcontractors = new HashSet<>();
    public Company(String name) {
        this.name = name;
    }        

    @Override
    public int hashCode() {
        int hash = 7;
        hash = 83 * hash + Objects.hashCode(this.name);
        return hash;
    }

    @Override
    public boolean equals(Object obj) {
        if (obj == null) {
            return false;
        }
        if (getClass() != obj.getClass()) {
            return false;
        }
        final Company other = (Company) obj;
        if (!Objects.equals(this.name, other.name)) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "Company{" + "name=" + name + ", subcontractors=" + subcontractors + '}';
    }
}

Create all the company objects with this:

Connection con = DriverManager.getConnection(" ... ");
PreparedStatement stmt = con.prepareStatement("Select * from main_contractor");
ResultSet rs = stmt.executeQuery();
Map<String,Company> map = new HashMap<>();
List<Company> mainCompanies = new ArrayList<>();
while (rs.next()) {
    String companyName = rs.getString("company");
    Company company = new Company(companyName);
    map.put(companyName, company);
    mainCompanies.add(company);
    String mainContractor = rs.getString("main_contractor");
    Company contractor = new Company(mainContractor);
    company.getSubcontractors().add(contractor);
    map.put(mainContractor, contractor);
}
PreparedStatement stmt2 = con.prepareStatement("Select * from contract");
rs = stmt2.executeQuery();
while (rs.next()) {
    String contractorName = rs.getString("contractor");
    Company contractor = map.containsKey(contractorName)? map.get(contractorName): new Company(contractorName);
    map.put(contractorName, contractor);
    String subContractorName = rs.getString("subcontractor");
    Company subcontractor = map.containsKey(subContractorName)? map.get(subContractorName): new Company(subContractorName);
    contractor.getSubcontractors().add(subcontractor);
    map.put(subContractorName, subcontractor);
}

The final output you would want would just be to print the list mainCompanies.