Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Problem with eclipselink, pagination, join fetch, field name alias(eclipselink, pagination, join fetch, field name alias)
Problem with eclipselink, pagination, join fetch, field name alias [message #1232218] Thu, 16 January 2014 13:00 Go to next message
M GS is currently offline M GSFriend
Messages: 2
Registered: January 2014
Junior Member
Hello,

I have a problem using eclipselink 2.5.1, Oracle database 11g, join fetchs, pagination, fetch type = LAZY and field name alias. Maybe is the normal behaviour and I am misunderstanding something. Maybe is related with bug https://bugs.eclipse.org/bugs/show_bug.cgi?id=337115. I don't know. In any case, if somebody can help me please, here is my scenario (sorry if it's too long):

Tables:
TESTCLASSB1(
  "IDTESTB1" NUMBER(19,0) NOT NULL ENABLE, 
  "FIELDX" VARCHAR2(255 BYTE), 
  "FIELDY" VARCHAR2(255 BYTE), 
  PRIMARY KEY ("IDTESTB1")
)
with only one row with values (1,null,null)

TESTCLASSB2(
  "IDTESTB2" NUMBER(19,0) NOT NULL ENABLE, 
  "FIELDX" VARCHAR2(255 BYTE), 
  "FIELDY" VARCHAR2(255 BYTE), 
  PRIMARY KEY ("IDTESTB2")
)
with only one row with values (1,null,null)

TESTCLASSA(
  "IDTESTA" NUMBER(19,0) NOT NULL ENABLE, 
  "IDTESTB1" NUMBER(19,0), 
  "IDTESTB2" NUMBER(19,0), 
  PRIMARY KEY ("IDTESTA")
)
with only one row with values (1,1,1)

Entities:
@Entity
@Table
public class TestClassB1 {
    @Id
    public Long idTestB1;  
    
    public String fieldX; 
    
    @Basic(fetch= FetchType.LAZY)
    public String fieldY;
    
    public TestClassB1(){
        super();
    }
}

@Entity
@Table
public class TestClassB2 {
    @Id
    public Long idTestB2;  
    
    public String fieldX; 
    
    @Basic(fetch= FetchType.LAZY)
    public String fieldY;
    
    public TestClassB2(){
        super();
    }
}

@Entity
@Table
public class TestClassA {
    @Id
    public  Long idTestA;
    
    @ManyToOne
    @JoinColumn(name = "IDTESTB1")
    public TestClassB1 b1;
    
    @ManyToOne
    @JoinColumn(name = "IDTESTB2")
    public TestClassB2 b2;
    
    public TestClassA() {
        super();
    }
}

My persistence.xml:
  <persistence-unit name="testPU" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <non-jta-data-source>java:/app/jdbc/testDS</non-jta-data-source>
    
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    
    <properties>
       <property name="eclipselink.target-server" value="WebLogic_10" />
       <property name="eclipselink.logging.level" value="WARNING"/>
       <property name="eclipselink.logging.level.sql" value="FINE"/>                    
    </properties>

  </persistence-unit>

The code executed:
        Long idTestA = 1L;
        
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("testPU");
        EntityManager em =  factory.createEntityManager();        
        
        TestClassA a = em.find(TestClassA.class, idTestA);
        
        CriteriaBuilder cb = em.getCriteriaBuilder();
                
        CriteriaQuery<TestClassA> query = cb.createQuery(TestClassA.class);        
        Root<TestClassA> aFrom = query.from(TestClassA.class);
                        
        Fetch<TestClassA, TestClassB1> fetchAa = aFrom.fetch(TestClassA_.b1);                        
        Fetch<TestClassA, TestClassB2> fetchAb = aFrom.fetch(TestClassA_.b2);
                
        query.select(aFrom);
        query.where(cb.equal(aFrom.get(TestClassA_.idTestA),idTestA));
        
        TypedQuery<TestClassA> tq = em.createQuery(query); 
        
        tq.setFirstResult(0);
        tq.setMaxResults(10);
        
        List<TestClassA> listTestA = tq.getResultList();
        
        em.close();
        factory.close();


With all this, I always get the exception:
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00918: columna definida de forma ambigua

Error Code: 918
Call: SELECT * FROM (SELECT a.*, ROWNUM rnum  FROM (SELECT t1.IDTESTA AS a1, t1.IDTESTB1 AS a2, t1.IDTESTB2 AS a3, t0.IDTESTB1, t0.FIELDX, t2.IDTESTB2, t2.FIELDX FROM TESTCLASSB1 t0, TESTCLASSB2 t2, TESTCLASSA t1 WHERE ((t1.IDTESTA = ?) AND ((t0.IDTESTB1 = t1.IDTESTB1) AND (t2.IDTESTB2 = t1.IDTESTB2)))) a WHERE ROWNUM <= ?) WHERE rnum > ?
        bind => [1, 10, 0]
Query: ReadAllQuery(referenceClass=TestClassA sql="SELECT * FROM (SELECT a.*, ROWNUM rnum  FROM (SELECT t1.IDTESTA AS a1, t1.IDTESTB1 AS a2, t1.IDTESTB2 AS a3, t0.IDTESTB1, t0.FIELDX, t2.IDTESTB2, t2.FIELDX FROM TESTCLASSB1 t0, TESTCLASSB2 t2, TESTCLASSA t1 WHERE ((t1.IDTESTA = ?) AND ((t0.IDTESTB1 = t1.IDTESTB1) AND (t2.IDTESTB2 = t1.IDTESTB2)))) a WHERE ROWNUM <= ?) WHERE rnum > ?")
        at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:378)
        at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
        at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:469)
        at TestServlet.test1(TestServlet.java:109)
        at TestServlet.service(TestServlet.java:45)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
        at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
        at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
        at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:300)

ORA-00918 translated is "column ambiguously defined"

Important things I have noted are:

  • Previosly to the execution of the query is retrieved from the data base with a "find" the object of the TestClassA that contains the objects of the classes TestClassB
  • Pagination is used
  • Both TestClassB (and their tables) have fields with the same name
  • One of these fields is lazy fetched
  • Only when pagination is used the query contains alias for the field names
  • The exception is throwed because at least two fields have the same name in the query without alias


Please, can somebody help with this?
Re: Problem with eclipselink, pagination, join fetch, field name alias [message #1232734 is a reply to message #1232218] Fri, 17 January 2014 15:48 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
This is the same issue described in the bug you linked to. Please vote for the bug. The only workarounds I can think of are to not use pagination with fetch joins to entities with similar fields, or to turn off pagination support being done at the database level by calling setShouldUseRownumFiltering(false) on the database platform. This will cause JDBC row filtering to be used instead which, unfortunately, might not be as efficient.

There might be a way to get EclipseLink to alias all fields in the statement, but I'm not sure how that could be done without going through the code - this is what would need to be done as part of a fix.

Best Regards,
Chris
Re: Problem with eclipselink, pagination, join fetch, field name alias [message #1232886 is a reply to message #1232734] Sat, 18 January 2014 01:06 Go to previous message
M GS is currently offline M GSFriend
Messages: 2
Registered: January 2014
Junior Member
Thank you Chris, I really appreciate your help.



Marc GS
Previous Topic:ClassCastException while using Criteria API with @ElementCollection
Next Topic:Lifecycle callbacks (preRemove, postRemove) not called on orphan removal
Goto Forum:
  


Current Time: Tue Nov 25 21:27:14 GMT 2014

Powered by FUDForum. Page generated in 0.03395 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software