Caused by: java.sql.SQLSyntaxErrorException: [SQL0205] Column MITMAS_MMCONO not in table OOLINE in schema

Question

I have 3 entity classes

Ooline:

@Entity
@IdClass(OolineId.class)
@NamedQuery(name="Ooline.findAll", query="SELECT o FROM Ooline o")
public class Ooline implements Serializable 
{
    private static final long serialVersionUID = 1L;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
        @JoinColumn(name="mitmas_mmcono" , referencedColumnName="mmcono"),
        @JoinColumn(name="mitmas_mmitno" , referencedColumnName="mmitno")
    })
    private Mitmas mitmas ;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
        @JoinColumn(name="mitfac_m9cono", referencedColumnName="m9cono"),
        @JoinColumn(name="mitfac_m9itno", referencedColumnName="m9itno"),
        @JoinColumn(name="mitfac_m9faci", referencedColumnName="m9faci")
    })
    private Mitfac mitfac ;

    @Id
    private String obitno;
    @Id
    private BigDecimal obcono;
    @Id
    private String obfaci;

    private BigDecimal obabno;

    //getter and setters

}

Mitfac:

@Entity
@IdClass(MitfacId.class)
@NamedQuery(name="Mitfac.findAll", query="SELECT m FROM Mitfac m")
public class Mitfac implements Serializable 
{
    private static final long serialVersionUID = 1L;

    @OneToMany(mappedBy="mitfac",fetch=FetchType.LAZY)
    private List<Ooline> oolineItem; 

    @Id
    private String m9itno;
    @Id
    private String m9faci;
    @Id
    private BigDecimal m9cono;

    private String m9acrf;

    private BigDecimal m9appr;

    //getter and setter

}

Mitmas:

@Entity
@IdClass(MitmasId.class)
@NamedQuery(name="Mitmas.findAll", query="SELECT m FROM Mitmas m")
public class Mitmas implements Serializable 
{
    private static final long serialVersionUID = 1L;

    @OneToMany(mappedBy="mitmas",fetch=FetchType.LAZY)
    private List<Ooline> ooline; 

    @Id
    private String mmitno;
    @Id
    private BigDecimal mmcono;

    private BigDecimal mmaad0;

    private BigDecimal mmaad1;

    private String mmaccg;

    //getter and setter methods
}

And following is my Repository file:

public interface OolineRepository extends JpaRepository<Ooline, String> 
{
    public static final String ORDER_LINE_QUERY = 
            "select x.obcono,x.oborno,x.obponr, x.obposx, x.obfaci, x.obitno, x.obitds, x.oborqt, "
            + "x.oborqa, x.obwhlo, x.obadid, x.obpopn, x.obrout, x.obmodl, x.oborst,"
            + " x.obnepr, x.obdwdz, x.obcodz, x.obpldt, x.obplhm,"
            + " x.obprrf, x.obspun, x.obrgdt, x.obrgtm, x.obwhsl from Ooline x "
            + "left join x.mitmas y  left join x.mitfac z "
            + "where x.obcono = :cono and x.oborno = :orno";

    @Query(ORDER_LINE_QUERY)
    public List<Ooline> getOolineLines(@Param("cono") BigDecimal cono, @Param("orno") String orno);
}

I am supposed to execute following query in SQL

"select x.oborno,x.obponr, x.obposx, x.obfaci, x.obitno, x.obitds, x.oborqt, x.oborqa,
x.obwhlo, x.obadid, x.obpopn, x.obrout, x.obmodl,x.oborst, x.obnepr, x.obdwdz, x.obcodz,
x.obpldt, x.obplhm,x.obprrf, x.obspun, x.obrgdt, x.obrgtm, x.obwhsl from Ooline x join
schema.mitmas on mmcono = obcono and mmitno = obitno left join  schema.mitfac on
m9cono = obcono and m9faci = obfaci and m9itno = obitno where obcono =1 and oborno = 'asasa'";

Can anyone verify if have constructed and mapped entity correctly . But query is not constructing the way I am expecting .Getting an error "Column MITMAS_MMCONO not in table OOLINE ." Can anyone help me in this

Answer 1

I have changed as following

@ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
        @JoinColumn(updatable=false,insertable=false,name="obcono",referencedColumnName="mmcono"),
        @JoinColumn(updatable=false,insertable=false,name="obitno",referencedColumnName="mmitno")
    })
    private Mitmas mitmas ;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
        @JoinColumn(updatable=false,insertable=false,name="obcono",referencedColumnName="m9cono"),
        @JoinColumn(updatable=false,insertable=false,name="obitno",referencedColumnName="m9itno"),
        @JoinColumn(updatable=false,insertable=false,name="obfaci",referencedColumnName="m9faci")
    })
    private Mitfac mitfac ;

Answer 2

The mapped entity is correct constructed, but the query is not prpeely defined. You want a left join from child enity Ooline to master entity Mitmas, and it tries to do that by joining on the primary key fields from Ooline to those from Mitmas (see generated sql) which is not correct. Why do you want a left join since you are not actually using anything from the master entity?