Announcement Announcement Module
Collapse
No announcement yet.
SpringDataJpa - Cannot use Pageable with an Entity that has a composite key & Oracle Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SpringDataJpa - Cannot use Pageable with an Entity that has a composite key & Oracle

    This is a Spring Data JPA issue. When using Pageable with Oracle & Hibernate on an Entity where the primary key is a composite, I get an error : java.sql.SQLException: ORA-00907: missing right parenthesis (stack trace below). The Entity uses a @EmbeddedId that has a primary key class of two Strings. It seems like any Entity with a composite ID fails for me, when using Pageable

    The call fails with a:
    Code:
    findAll(new PageRequest(0,20))
    I'm using Spring-Data-Jpa 1.0.0.M2 release, Hibernate 3.6.4.Final (also tried 3.5.6-Final), and Oracle 11r2

    After some debugging, the Simple JPA Repository readPage method does a
    Code:
    Long total = getCountQuery(spec).getSingleResult();
    If you use a findAll(Pageable pageable) the getCountQuery ends up looking like:
    Code:
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Long> query = builder.createQuery(Long.class);
    Root<LabOrderCode> root = query.from(LabOrderCode.class);
    query.select(builder.count(root)).distinct(true);
    Long result = (Long) em.createQuery(query).getSingleResult();
    This code fails with Oracle but works with HSQL. I would think we want the Query to look like
    Code:
    select count(*) as result from (select distinct ....
    Can anyone PLEASE HELP? Is this a Hibernate issue or is there some plans to work around this issue? Is there anything more I can do to help?

    Thanks,

    Bucky Buchanan




    Generate SQL Code:
    Code:
    Hibernate: 
        select
            * 
        from
            ( select
                distinct count((laborderco0_.ANCILLARY_ABBR,
                laborderco0_.ORDER_CODE)) as col_0_0_ 
            from
                LAB_ORDER_CODE laborderco0_ ) 
        where
            rownum <= ?
    ERROR - ORA-00907: missing right parenthesis
    Stack trace:
    Code:
    org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select distinct count((laborderco0_.ANCILLARY_ABBR, laborderco0_.ORDER_CODE)) as col_0_0_ from LAB_ORDER_CODE laborderco0_]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
    	at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:629)
    	... lots more
    
    
    Caused by: java.sql.SQLException: ORA-00907: missing right parenthesis
    
    	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
    	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
    	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)
    	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)
    	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:185)
    	at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:503)
    	... lots more

  • #2
    I am experiencing similar behavior. Interested to see if this is an edge case.

    Comment


    • #3
      Composite key paging issue

      We could not use findAll(pageable) so we had to write hand coding to fetch the list of records then sublist and create a page.
      this is a bad work-around and we need to know if findAll(pageable) will still work with composit primary keys--basically the wrong formation of count query (is there a fix >> ???)

      PLEASE GUIDE

      Comment

      Working...
      X