How do I map a List of Maps in Hibernate using annotations?

Question

I'm trying to map a List of Maps into a class using Hibernate annotations.

I'd like my final class to look something like this:

@Entity
@Table(name = "PERSON")
public class Person {
    @Id
    @GeneratedValue
    private long id;

    @OneToMany(mappedBy = "person")
    private List<Map<String, Trip>> trips; 
}

The Trip table has a trip number and multiple rows for each trip with the state the trip traveled through.

@Entity
@Table(name = "TRIP")
public class Trip {
    @Id
    @GeneratedValue
    private long id;

    @Column(name = "PERSON_ID")
    private Person person;

    @Column(name = "TRIP_NO")
    private int trip_no;

    @Column(name = "STATE")
    private String state;

    ...
}

So the TRIP table has these columns:

Name         Type
ID           NUMBER(9)
PERSON_ID    NUMBER(9)
TRIP_NO      LONG
STATE        VARCHAR2(16)
.
.
.

ID  PERSON_ID  TRIP_NO  STATE ...
1   1          1        MN    ...
2   1          1        WI    ...
3   1          2        ND    ...
4   1          2        MT    ...
5   2          1        IA    ...

So in the List of Maps, the TRIP_NO is the index of the List, and the STATE is the key to the Map. Person 1 took 2 trips, the first was to Minnesota and Wisconsin, the second trip to North Dakota and Montana. Person 2 took 1 trip to Iowa.

I'm having trouble navigating through to find how to specify this configuration.

Second question: Can it be done without JPA 2?

Answer 1

Here's my approach:

// composite primary key
public class TripId implements Serializable {
    private int id;
    private long trip_no;

    public TripId(int id, long trip_no) {
        this.id = id;
        this.trip_no = trip_no;
    }

    // getters, setters, hashCode, equals
}

EDIT: I had to update the entity model after Welshbard's questions from the comments below. Changes are:

• Person entity: Map<String, Trip> trips; replaced by List<Trip> trips;
• Trip entity: String state; field added as the replacement for trips key from Person entity
• constructors / setters / code snippet for populating data / output schema were updated accordingly

@Entity
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;

    @OneToMany(mappedBy = "person", cascade = CascadeType.ALL)
    private List<Trip> trips;

    public Person() {
        this.trips = new List<>();
    }

    public void setTrips(List<Trip> trips) {
        this.trips = trips;
    }

    ...
}

@Entity
@IdClass(TripId.class)
public class Trip {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;

    @Id
    private long trip_no;

    private String state;

    @ManyToOne
    private Person person;

    public Trip() {

    }

    public Trip(String state, long trip_no, Person person) {
        this.state = state;
        this.trip_no = trip_no;
        this.person = person;
    }

    ...
}

We can now make use of the above ORM and populate some data:

Person person1 = new Person();
Person person2 = new Person();

Trip mn1Trip1 = new Trip("MN", 1, person1);
Trip wi1Trip1 = new Trip("WI", 1, person1);
Trip nd2Trip1 = new Trip("ND", 2, person1);
Trip mt2Trip1 = new Trip("MT", 2, person1);
Trip ia1Trip2 = new Trip("IA", 1, person2);
// more than one trip by a person goes to the same state
Trip mn2Trip1 = new Trip("MN", 2, person1);
Trip ia2Trip2 = new Trip("IA", 2, person2);
Trip mn1Trip2 = new Trip("MN", 1, person2);

person1.setTrips(Arrays.asList(mn1Trip1, wi1Trip1, nd2Trip1, mt2Trip1, mn2Trip1));
person2.setTrips(Arrays.asList(ia1Trip2, ia2Trip2, mn1Trip2));

em.getTransaction().begin();
em.persist(person1);
em.persist(person2);
em.getTransaction().commit();

Finally, we can query the database to see the schema and how the data were populated:

Person table
============
id
--
 1
 7

Trip table
===========
id trip_no person_id state
-- ------- --------- -----
  9       2 IA            7
  4       2 ND            1
  8       1 IA            7
  6       2 MN            1
  2       1 MN            1
 10       1 MN            7
  3       1 WI            1
  5       2 MT            1

NOTE: ids are different but the schema was created properly and the data were populated as expected.

As for the questions from the comment below:

// Person class have only trips associated with that person
String jpql1 = "SELECT p.trips FROM Person p WHERE p.id = 1";
List<Trip> trips = em.createQuery(jpql1, Trip.class)
                     .getResultList();

// the code can look at the Trips to find out the trip_no
String jpql2 = "SELECT t.trip_no FROM Trip t JOIN Person p " +
               "WHERE p.id = :id AND t.state = :state";
List<Long> trip_nos = em.createQuery(jpql2, Long.class)
                        .setParameter("id", 1)
                        .setParameter("state", "MN")
                        .getResultList();

---

Regarding the second question:

Can it be done without JPA 2?

You could try with plain old JDBC and use java.sql.Statement to insert SQL statements.

Answer 2

Unless you alter your database schema what you want is not possible. Notice how the Hibernate documentation omits the collection type as valid content for a collection.

Naturally Hibernate also allows to persist collections. These persistent collections can contain almost any other Hibernate type, including: basic types, custom types, components and references to other entities. From Hibernate Collection Mapping

You can't do this with hibernate because your database design is wonky. Basically your TRIP table has too much information in it as it stores both trips and destinations.

Ideally you have a Trip and Destination.

The Trip consisting of an id, a person_id and a trip_no for the ordering in the list through @OrderBy.

The Destinations consist of an id, a trip_id, the state_id for use by the @MapKey and the other information for the destination.

Java code would be something like this. Didn't check for any errors. Just to give you the idea.

@Entity
@Table(name = "PERSON")
public class Person {
    @Id
    @GeneratedValue
    private long id;

    @OneToMany(mappedBy = "person")
    @OrderBy(clause=trip_no)
    private List<Trip> trips; 
}

@Entity
@Table(name = "TRIP")
public class Trip {
    @Id
    @GeneratedValue
    private long id;

    @Column(name = "PERSON_ID")
    private Person person;

    @Column(name = "TRIP_NO")
    private int trip_no;

    @OneToMany(mappedBy = "trip")
    @MapKey(name = "state")
    private Map<String,Destination> destinations
}

@Entity
@Table(name = "DESTINATION")
public class Destination {

    private Trip trip;

    @Column(name = "STATE")
    private String state;

    ...
}

Answer 3

I'm not sure because didn't test and haven't experienced exactly same thing before but I think using a List of Map<K,V>s for associative field would be a little complicated, you can have a Many-to-One rel for person in the Trip entity and then a One-to-Many for Trips in person entity:

@Entity
@Table(name = "TRIP")
public class Trip {    
    /*
    code
    */
    @ManyToOne(cascade=CascadeType.ALL)
    private Person person;

    /*
    code
    */
}

and in Person Entity don't use List<Map<String, Trip>> instead of it use @MapKey annotation for Map<String, Trip>:

@Entity
@Table(name = "PERSON")
public class Person {
    @Id
    @GeneratedValue
    private long id;

    @OneToMany(mappedBy = "person")
    @MapKey(name = "state")
    private Map<String, Trip> trips; 
}

However you need to review more, I hope this will be helpful.