Joining two tables with JPA

Question

I'm trying to use a @NamedQuery to join two tables, Address and Division. There are multiple divisions at the same address. At the moment I have
Division.java:

@Entity
@Table(name="division")
@NamedQuery(name="Division.mailingAddress", query="SELECT d, a from Division d LEFT JOIN d.addresses a")
public class Division implements Serializable{
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name="division_id")
    private Short divisionId;

    @Basic(optional = false)
    @Column(name="division_name")
    private String divisionName;

    @Basic(optional = false)
    @Column(name="address_id")
    private Short addressId;

    @OneToMany(mappedBy="divisionMailingAddress")
    private List<Address> addresses;

    //getters and setters
}

Addresses.java:

@Entity
@Table(name="address")
public class Address implements Serializable{
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GeneratedType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "address_id")
    private Short addressID;

    @Basic(optional = false)
    @Column(name = "mailing_address")
    private String mailingAddress;

    @ManyToOne
    private Division divisionMailingAddress;
}

The error I get when I run this is: Unknown column 't0.DIVISIONMAILINGADDRESS_division_id' in 'on clause'

What I'm trying to get is this line that works in MySQL: SELECT * FROM (division d join address a on d.address_id = a.address_id);
Currently the JPA is resolving the named query to:
SELECT t1."all columns", t0."all columns" from division t1 LEFT OUTER JOIN address t0 ON (t0.DIVISIONMAILINGADDRESS_division_id = t1.division_id) What is wrong with what I am doing? Does the JPA want the join coming from the address vs the division? How Do I get the call to be made off of address_id vs division_id?

UPDATE:

With great assistance here are what the two classes have been changed to accomplish what I had wanted the outcome to be.

Division.java:

@Entity
@Table(name="division")
@NamedQuery(name="Division.mailingAddress", query="SELECT d from Division d LEFT JOIN d.addressesM2O a")
public class Division implements Serializable{
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name="division_id")
    private Short divisionId;

    @Basic(optional = false)
    @Column(name="division_name")
    private String divisionName;

    @Basic(optional = false)
    @Column(name="address_id")
    private Short addressId;

    @ManyToOne(targetEntity = Address.java)
    @JoinColumn(name = "address_id", updatable=false, insertable=false)
    private Address addressesM2O;

    //getters and setters
}

Address.java:

@Entity
@Table(name="address")
public class Address implements Serializable{
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GeneratedType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "address_id")
    private Short addressID;

    @Basic(optional = false)
    @Column(name = "mailing_address")
    private String mailingAddress;

    @OneToMany(mappedBy="addressesM2O")
    private List<Division> divisionO2M;
}

The @NamedQuery was changed since d and a being present made the result list return a list of Object[]. Which was the object Division and Address. Thus is why d was kept and a was removed from the @NamedQuery.

Answer 1

I think the issue is that you are not mentioning the @JoinColumn while mentioning @ManyToOne in the Address class , due to which while making the join JPA itself is creating the foreign key DIVISIONMAILINGADDRESS_division_id.

Try to mention ManyToOne relationship in Address class as below and let me know if any issue occurs:

@ManyToOne(targetEntity = Division.class)
@JoinColumn(name= "division_id")//basically the name of the column here should be same as the one mentioned in your database