How can I retrieve the foreign key from a JPA ManyToOne mapping without hitting the target table?

Question

I have the following two annotated classes that I use to build a graph:

@Entity
@Table(name = "Edge")
public class Edge
{
    /* some code omitted for brevity */

    @ManyToOne
    @JoinColumn(name = "ixNodeFrom", nullable = false)
    private Node         _nodFrom;

    @ManyToOne
    @JoinColumn(name = "ixNodeTo", nullable = false)
    private Node         _nodTo;

    /* some code omitted for brevity */
}

@Entity
@Table(name = "Node")
public class Node
{
    /* some code omitted for brevity */

    @OneToMany(mappedBy = "_nodTo")
    private Set<Edge>    _rgInbound;

    @OneToMany(mappedBy = "_nodFrom")
    private Set<Edge>    _rgOutbound;

    /* some code omitted for brevity */
}

Now, when I build the graph, I issue two queries to fetch all rows from either table and set up the child / parent references, for which I need the ids stored in the Edge table.

Because I have defined the relation between the two tables in JPA, accessing the edge object to get the two nodes' ids triggers two SQL statements per edge, when the JPA provider lazily loads the associated nodes. Since I already have the node objects, and the ids have already been loaded from the edge table, I want to skip those queries, as they take an awfully long time for larger graphs.

I tried adding these lines to the Edge class, but then my JPA provider wants me to make one mapping read-only, and I can't seem to find a way how to do that:

@Column(name = "ixNodeTo")
private long _ixNodeTo;

@Column(name = "ixNodeFrom")
private long _ixNodeFrom;

I'm using Eclipselink and MySQL, if it matters.

Answer 1

Have you tried

@Column(name = "ixNodeTo", insertable = false, updatable = false)

Answer 2

I think you should try to optimize your query rather than change the mapping. For example, the following query fetches the whole graph at once (tested in Hibernate):

List<Node> nodes = em.createQuery(
            "SELECT DISTINCT n FROM Node n LEFT JOIN FETCH n._rgOutbound")
            .getResultList();

Answer 3

How can I retrieve the foreign key from a JPA ManyToOne mapping without hitting the target table?

In theory, a JPA provider should be able to not trigger a query when calling

someEdge.getNodeFrom().getId()

as it already has the id (as FK).

I'm 100% sure Hibernate can (assuming you're using property access). In the case of EclipseLink, I don't know (if it does, it will probably requires weaving).

Because I have defined the relation between the two tables in JPA, accessing the edge object to get the two nodes' ids triggers two SQL statements per edge, when the JPA provider lazily loads the associated nodes. Since I already have the node objects, and the ids have already been loaded from the edge table, I want to skip those queries, as they take an awfully long time for larger graphs.

Note that @ManyToOne uses an EAGER strategy by default. If you want to make it LAZY, you have to decalre it explicitly (but again, this will require weaving of your classes with EclipseLink).