Hibernate many-to-many with map collection

Question

I have two tables "Station" and "Route". Route can contain many stations and every station can be a part of the different routes. The logical solution is to create a table with 'station_id' and 'route_id' fields and there are lot of examples on the internet how to organize all of this stuff with hibernate. But I need to store the order number of each station in the route, so 3rd table populates with one more field 'orderNumber', so we get the following structure:

RouteStations

• route_id
• station_id
• orderNumber

I created the next beans:

public class Station extends Identifier{

    private String title;
}

public class Route extends Identifier{
    private String name;
    private Map<Integer, Station> stations = new HashMap<Integer, Station>();
}

So I use the HashMap collection to store the orderNumber variable. Help me please to map such type of relation using hibernate or propose the other solution. Thanks.

Answer 1

Thanks a lot. But I think there shouldn't be an 'RouteAndStation' entity. I tried to map my 'Route' class on two tables in the database like this:

<class name="Route" table="Route">

    <id name="id" column="routeId" type="long" unsaved-value="-1">
            <generator class="native"/>
    </id>

    <property name="name" column="name" />

    <map name="stations" table="RouteStations" cascade="all">
            <key column="routeId" />
            <map-key-many-to-many column="stationId" class="Station"/>
            <element column="orderNumber" type="int"/>
        </map>

</class>

But when I save the Route object only one query performs by hibernate:

insert into Route (name) values (?)

Answer 2

Hummm,

I need to store the order number of each station in the route

Why do you do not use a List instead. You just have to create a linked table

@Entity
public class Route {

    private Integer id;

    private List<RouteAndStation> stationList = new ArrayList<RouteAndStation>();

    @Id
    public Integer getId() {
        return this.id;
    }

    @OneToMany(mappedBy="route")
    @Cascade(SAVE_UPDATE)
    public List<RouteAndStation> getStationList() {
        return this.stationList;
    }

    @Transient
    public List<Station> getStationByOrderNumber() {
        Collections.sort(this.stationList, new Comparator<RouteAndStation>() {
            public int compare(RouteAndStation o1, RouteAndStation o2) {
                return o1.getOrderNumber().compareTo(o2.getOrderNumber());
            }
        });

        List<Station> sList = new ArrayList();
        for(RouteAndStation routeAndStation: this.stationList)
            sList.add(routeAndStation.getStation());

        return sList;
    }

    public void addStation(Station station) {
        RouteAndStation routeAndStation = new RouteAndStation();

        routeAndStation.addStation(this, station, getStationList().size());

        getStationList().add(routeAndStation);
    }

}

And Station

@Entity
public class Station {

    private Integer id;

    private List<RouteAndStation> routeList = new ArrayList<RouteAndStation>();

    @Id
    public Integer getId() {
       return this.id;
    }

    @OneToMany(mappedBy="station")
    // DO YOU NEDD CASCADE ???
    @Cascade(SAVE_UPDATE)
    public List<RouteAndStation> getRouteList() {
        return this.routeList;
    }


}

RouteAndStation is implemented as follows

@Entity
public class RouteAndStation {

    private Route route;
    private Station station;

    private Integer stationNumber;

    private RouteAndStationId routeAndStationId;

    @EmbeddedId
    public RouteAndStationId getRouteAndStationId() {
        return this.routeAndStationId;
    }

    public void addStation(Route route, Station station, Integer stationNumber) {
        this.route = route;
        this.station = station;

        this.stationNumber = stationNumber;

        setRouteAndStationId(new RouteAndStationId(route.getId(), station.getId));
    }

    @ManyToOne(fetch=LAZY)
    @JoinColumn(name="ROUTE_ID", insertable=false, updateable=false)
    public Route getRoute() {
        return this.route;
    }

    @ManyToOne(fetch=LAZY)
    @JoinColumn(name="STATION_ID", insertable=false, updateable=false)
    public Station getStation() {
        return this.station;
    }

    @Embeddable
    public static class RouteAndStationId implements Serializable {

        private Integer routeId;
        private Integer stationId;

        // required no-arg constructor
        public RouteAndStationId() {}

        public RouteAndStationId(Integer routeId, Integer stationId) {
            this.routeId = routeId;
            this.stationId = stationId;
        }

        @Column(name="ROUTE_ID")
        public Integer getRouteId {
            return this.routeId;
        }

        @Column(name="STATION_ID")
        public Integer getStationId {
            return this.stationId;
        }

        public boolean equals(Object o) {
            if(o == null)
                return false;

            if(!(o instanceof RouteAndStationId))
                return false;

            RouteAndStationId other = (RouteAndStationId) o;
            if(!(getRouteId().equals(other.getRouteId())))
                return false;

            if(!(getStationId().equals(other.getStationId())))
                return false;

            return true;
        }

    }

}

I hope it can be useful to you

regards,

Answer 3

Since you have to store extra information in the RouteStation table it might be better to promote it to a full class. You will end up with something like this:

 public class Station extends Identifier{
    private String title;
    private List<RouteStation> routes;
 }

 public class Route extends Identifier{
    private String name;
    private List<RouteStation> stations;
 }

 Public class RouteStation{
     private Station station;
     private Route route;
     private Integer orderNumber;
 }

You can then make the mapping so that the RouteStation class owns the relationships between Route and Station. You now have to create two individual one-to-many bi directional mappings.