Ishkafel Ishkafel - 5 months ago 18
Java Question

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

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

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...