JPA (Hibernate) generates wrong query using n:m association

Question

I got a predefined oracle-db and I'm trying to map following tables:

TradingSeries:

REF_TRADING_SERIES
(
    TRADING_SERIES_ID  NUMBER(15),
    // other attributes
)

TradingSession:

REF_TRADING_SESSION
(
    TRADING_SESSION_ID  NUMBER(15),
    // other attributes
)

The n:m association TradingComposition:

REF_TRADING_COMPOSITION
(
    TRADING_SERIES_ID   NUMBER(15),
    TRADING_SESSION_ID  NUMBER(15),
    ITS                 DATE,
    UTS                 DATE
)

My corresponding mapping-classes:

TradingSeries.class:

@Entity
@Table(name = "REF_TRADING_SERIES", schema = "XXX")
@Cacheable
public class TradingSeries implements java.io.Serializable {
    private static final long serialVersionUID = 1482491978920606855L;
    private long tradingSeriesId;
    // other attributes
    private List<TradingComposition> tradingComposition = new ArrayList<TradingComposition>(0);

    @Id
    @Column(name = "TRADING_SERIES_ID", unique = true, nullable = false, precision = 15, scale = 0)
    public long getTradeSessionId() {
        return this.tradingSeriesId;
    }

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "tradingCompositionId.tradingSeriesId")
    public List<TradingComposition> getTradingComposition() {
        return tradingComposition;
    }

TradingSession.class:

@Entity
@Table(name = "REF_TRADING_SESSION", schema = "XXX")
public class TradingSession implements java.io.Serializable {
    private static final long serialVersionUID = 9142731522041102660L;
    private long tradingSessionId;
    // other attributes
    private List<TradingComposition> tradingComposition = new ArrayList<TradingComposition>(0);

    @Id
    @Column(name = "TRADING_SESSION_ID", unique = true, nullable = false, precision = 15, scale = 0)
    public long gettradingSessionId() {
        return this.tradingSessionId;
    }

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "tradingCompositionId.tradingSessionId")
    public List<TradingComposition> getTradingComposition() {
        return tradingComposition;
    }

TradingComposition.class:

@Entity
@Table(name = "REF_TRADING_COMPOSITION", schema = "XXX")
public class TradingComposition implements java.io.Serializable {
    private static final long serialVersionUID = 9142731522041102660L;

    @EmbeddedId
    private TradingCompositionId tradingCompositionId;

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "ITS", length = 7)
    private Date its;

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "UTS", length = 7)
    private Date uts;

    @ManyToOne
    @PrimaryKeyJoinColumn(name = "TRADING_SERIES_ID")
    private TradingSeries tradingSeries;

    @ManyToOne
    @PrimaryKeyJoinColumn(name = "TRADING_SESSION_ID")
    private TradingSession tradingSession;

TradingCompositionId.class:

@Embeddable
public class TradingCompositionId implements Serializable {    
    private static final long serialVersionUID = -1546345156448039243L;

    @Column(name = "TRADING_SERIES_ID", nullable = false, precision = 15, scale = 0)
    private long tradingSeriesId;

    @Column(name = "TRADING_SESSION_ID", nullable = false, precision = 15, scale = 0)
    private long tradingSessionId;

Now when I am trying to load a TradingSeries Hibernate executes following query:

SELECT tradingcom0_.TRADING_SERIES_ID AS TRADING_SERIES_ID1_64_0_,
       tradingcom0_.TRADING_SERIES_ID AS TRADING_SERIES_ID1_63_0_,
       tradingcom0_.TRADING_SESSION_ID AS TRADING_SESSION_ID2_63_0_,
       tradingcom0_.TRADING_SERIES_ID AS TRADING_SERIES_ID1_63_1_,
       tradingcom0_.TRADING_SESSION_ID AS TRADING_SESSION_ID2_63_1_,
       tradingcom0_.ITS AS ITS3_63_1_,
       tradingcom0_.tradingSeries_TRADING_SERIES_ID
          AS tradingSeries_TRAD5_63_1_,
       tradingcom0_.tradingSession_TRADING_SESSION_ID
          AS tradingSession_TRA6_63_1_,
       tradingcom0_.UTS AS UTS4_63_1_,
       tradingser1_.TRADING_SERIES_ID AS TRADING_SERIES_ID1_64_2_,
       -- other attributes
       tradingses2_.TRADING_SESSION_ID AS TRADING_SESSION_ID1_65_3_,
       -- other attributes
  FROM XXX.REF_TRADING_COMPOSITION tradingcom0_
       LEFT OUTER JOIN
       XXX.REF_TRADING_SERIES tradingser1_
          ON tradingcom0_.tradingSeries_TRADING_SERIES_ID =
                tradingser1_.TRADING_SERIES_ID
       LEFT OUTER JOIN
       XXX.REF_TRADING_SESSION tradingses2_
          ON tradingcom0_.tradingSession_TRADING_SESSION_ID =
                tradingses2_.TRADING_SESSION_ID
 WHERE tradingcom0_.TRADING_SERIES_ID = ?

The result is an ORA-00972: identifier is too long, the problem is the generated tradingcom0_.tradingSession_TRADING_SESSION_ID. This contains the String tradingSession_ which is wrong. Of course tradingcom0_.tradingSeries_TRADING_SERIES_ID is incorrect, too. Also I'd like to know why hibernate selects the same fields multiple times (seen in the first rows in the query).

Could anyone suggest me how to solve this problem? Ehy is hibernate using the wrong columnnames?

Cheers, Philipp

Answer 1

Found the solution:

Ged rid of

@ManyToOne
@PrimaryKeyJoinColumn(name = "TRADING_SERIES_ID")
private TradingSeries tradingSeries;

@ManyToOne
@PrimaryKeyJoinColumn(name = "TRADING_SESSION_ID")
private TradingSession tradingSession;

In TradingComposition.class. PrimaryKeyJoinColumn was a really bad idea and is not needed here. Instead change TradingCompositionId.class to:

@ManyToOne
@JoinColumn(name = "TRADING_SERIES_ID", nullable = false)
private TradingSeries tradingSeries;

@ManyToOne
@JoinColumn(name = "TRADING_SESSION_ID", nullable = false)
private TradingSession tradingSession;

and remove the long-attributes tradingsSeriesId and tradingSessionId. In TradingSeries.class and TradingSession.class change the annotation to @OneToMany(fetch = FetchType.EAGER, mappedBy = "tradingCompositionId.tradingSeries"), respectively @OneToMany(fetch = FetchType.EAGER, mappedBy = "tradingCompositionId.tradingSession").

All this changes result in following SQL:

SELECT tradingcom0_.TRADING_SERIES_ID AS TRADING_SERIES_ID4_64_0_,
       tradingcom0_.TRADING_SERIES_ID AS TRADING_SERIES_ID4_63_0_,
       tradingcom0_.TRADING_SESSION_ID AS TRADING_SESSION_ID3_63_0_,
       tradingcom0_.TRADING_SERIES_ID AS TRADING_SERIES_ID4_63_1_,
       tradingcom0_.TRADING_SESSION_ID AS TRADING_SESSION_ID3_63_1_,
       tradingcom0_.ITS AS ITS1_63_1_,
       tradingcom0_.UTS AS UTS2_63_1_,
       tradingser1_.TRADING_SERIES_ID AS TRADING_SERIES_ID1_64_2_,
       -- other attributes
       tradingses2_.TRADING_SESSION_ID AS TRADING_SESSION_ID1_65_3_,
       -- other attributes
  FROM XXX.REF_TRADING_COMPOSITION tradingcom0_
       LEFT OUTER JOIN
       XXX.REF_TRADING_SERIES tradingser1_
          ON tradingcom0_.TRADING_SERIES_ID =
                tradingser1_.TRADING_SERIES_ID
       LEFT OUTER JOIN
       XXX.REF_TRADING_SESSION tradingses2_
          ON tradingcom0_.TRADING_SESSION_ID =
                tradingses2_.TRADING_SESSION_ID
    WHERE tradingcom0_.TRADING_SERIES_ID = ?

I still don't know why hibernate adds the multiple selects for attributes TRADING_SERIES_ID and TRADING_SESSION_ID...