How would I use java.util.Collections to simulate an SQL INNER JOIN operation?

Question

How would I use Java Collections to simulate an SQL INNER JOIN operation?

---

In a database, I have:

TABLE Person

KEY  NAME
11   Senor
other non-important entries...

TABLE Thing

KEY  ITEM
AA   Moustache
BB   Sombrero
CC   HotSauce
other non-important entries...

TABLE PersonToThing

PERSON_KEY  THING_KEY  HAS
11          AA         Y
11          BB         N
11          CC         Y
other non-important entries...

---

I want to emulate the SQL statement:

SELECT Person.NAME, Thing.ITEM, PersonToThing.HAS 
FROM Person 
INNER JOIN PersonToThing ON Person.KEY=PersonToThing.PERSON_PKEY
INNER JOIN Thing ON Thing.KEY=PersonToThing.THING_KEY
WHERE Person.NAME="Senor";

Which yields the resultset:

NAME   ITEM       HAS
Senor  Moustache  Y
Senor  Sombrero   N
Senor  HotSauce   Y

I want to put each table in a Java Map.

I have exported the tables into INSERT TABLE statements.

I will populate the Maps by looping through the INSERT TABLE statements.

Running a relational database modelling system is simply not a possibility, unfortunately.

---

What I don't understand is how do I organize the Collections or Maps and how do I link them together to mimic the INNER JOIN operation?

---

Thank you in advance for your time and any help you can give.

Answer 1

In your example, there's a one to many relationship between persons and things. For me it's harder to think about this kind of relationship from the database perspective than it is from the Java/OOP perspective.

In your DB, your joining person table to the thing table to provide a list of the things each person has.

This could make its way into your application as a map of things, each with a list of the persons that have each thing, or as a map of persons, each with a list of things that they have.

So, in Java, you're essentially asking how to model this:

public class Person() {    
   private List<Thing> things;
}

...

public class SomeClass() {
    private List<Person> peopleWithThings;
}  

IMO, you could do this two ways -

1. Simply use the plain old Java objects in your domain as I did above
2. Use something like Guava's multimap to create a map of Strings (person names) to a list of things.

Using a multimap, you would have something like:

String key = "Senor";
Multimap<String, Thing> map = ArrayListMultimap.create();

map.put(key, thing1);
map.put(key, thing2);

assertEquals(2, map.size());

Answer 2

In set theory, inner join is essentially an intersection operation. The Java collections don't have the exact same set theory functions built in, but they do have similar functions for union (addAll) and intersection (retainAll). See this question for more details on how inner-join/intersection could be implemented with Set or other collections.

The main challenge here with using set theory is the presence of three different object types, none of which inherit each other, something one might expect in a proper relationship model. For example, it would simplify things greatly if both Person and Thing inherited from PersonToThing as a parent class:

class Person extends PersonToThing {
    // ...
}

class Thing extends PersonToThing {
    // ...
}

class PersonToThing {
    // now Person_Key and Thing_Key can be inherited
    String personKey;
    String thingKey;
    // etc...
}

Using this model, we can now have a collection of PersonToThing objects and properly illustrate both one-to-many relationships:

Set<PersonToThing> people = selectAllFrom("Person");
Set<PersonToThing> thing = selectAllFrom("Thing");
Set<PersonToThing> innerJoin = people;
people.addAll(thing);
innerJoin.retainAll(thing);

If you override Object's equals() function to check your key, you can perform the join however you like, including the Senor name filter there, or adding a utility function to make it a more reusable and user-friendly design:

@Override
public boolean equals(Object personToThing) {
    if (personToThing.getPersonKey() != null) {
        return personKey.equals(personToThing.getPersonKey());
    else
        return thingKey.equals(personToThing.getThingKey());
}

This is done because Set uses equals() to examine whether two objects are the same. This way, when it does that, we are comparing the keys as a join would do.

I left the details of the selectAllFrom() function abstract, as you didn't provide any database-specific boilerplate code, but it should be rather simple to implement however you need to.

Answer 3

Well, this seems not easy, but it is possible.
First some preparation - I am using Project Lombok in order to generate getters/setters and constructors using simple annotations, just create a Maven project and add this dependency to it:

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.4</version>
    <scope>provided</scope>
</dependency>

---

Below are definitions of our classes and tables with data:

@AllArgsConstructor @Getter
public static class Person {
    private String key, name;
}

@AllArgsConstructor @Getter
public static class Thing {
    private String key, item;
}

@AllArgsConstructor @Getter
public static class PersonToThing {
    private String personKey, thingKey, has;
}

static Collection<Person> tablePerson = Arrays.asList(
        new Person("11", "Senor"),
        new Person("22", "Tom"));

static Collection<Thing> tableThing = Arrays.asList(
        new Thing("AA", "Moustache"),
        new Thing("BB", "Sombrero"),
        new Thing("CC", "HotSauce"),
        new Thing("XX", "Not important")
);

static Collection<PersonToThing> tablePerson2Thing = Arrays.asList(
        new PersonToThing("11", "AA","Y"),
        new PersonToThing("11", "BB","N"),
        new PersonToThing("11", "CC","Y"));

---

Now a piece of code that performs a join on these three collections.

@AllArgsConstructor(staticName = "of") @Getter
public static class Tuple<V1,V2>{
    private V1 v1;
    private V2 v2;
}

@AllArgsConstructor(staticName = "of") @Getter
public static class Triple<V1,V2,V3>{
    private V1 v1;
    private V2 v2;
    private V3 v3;
}

public static void main(String[] args) {
    tablePerson.stream()
            // WHERE Person.NAME="Senor";
            .filter(x->x.getName()=="Senor")
            // INNER JOIN PersonToThing
            .flatMap( p -> tablePerson2Thing.stream()
                    .map(p2t-> Tuple.of(p,p2t))
                    // ON Person.KEY=PersonToThing.PERSON_PKEY
                    .filter(t->t.getV1().getKey()==t.getV2().getPersonKey())
            )
            // INNER JOIN Thing
            .flatMap( p2t-> tableThing.stream()
                    .map(t-> Triple.of(p2t.getV1(),p2t.getV2(),t))
                    // ON Thing.KEY=PersonToThing.THING_KEY
                    .filter(t->t.getV2().getThingKey()==t.getV3().getKey())
            )
            // SELECT Person.NAME, Thing.ITEM, PersonToThing.HAS 
            .forEach(x->System.out.println(x.getV1().getName()+ " / " + x.getV3().getItem() + " /  " + x.getV2().getHas()));
}

---

and a result is:

Senor / Moustache /  Y
Senor / Sombrero /  N
Senor / HotSauce /  Y