Oracle i Hibernate

W 2 ostatnich projektach dość intensywnie wykorzystujemy Hibernate jako ORM dla Oracle 10.2.0.1.0. (sterownik JDBC w wersji 10.2.0.4.0.) Takie rozwiązanie nie zawsze okazało się bezproblemowe.

• "Wrong column type in {nazwa schematu.nazwa kolumny} for column {nazwa kolumny}. Found: date, expected: timestamp". Walidacja schematu przez Hibernate(hibernate.hbm2ddl.auto=validate) wyrzuca wyjątek przy mapowaniu
  

  @Temporal(TemporalType.TIMESTAMP)
  private java.util.Date published;
  

  
  gdy kolumna w bazie jest typu DATE.
  Oracle w wersji 9.2 wprowadził nowy typ danych TIMESTAMP, ale jednocześnie wprowadził mapowanie DATE =>java.sql.Date (
  javax.persistence.TemporalType.Date). Typ java.sql.Date posiada jedynie dane o dacie bez info o czasie, przy czym Oraclowy DATE przetrzymuje dane zarówno o dacie jak i o czasie. Sprawdzenie przez drvier JDBC czy DATE mapuje się na java.sql.TimeStamp(
  javax.persistence.TemporalType.TIMESTAMP) jest false i rzucany jest wyjątek. Rozwiązań tego problemu jest kilka, przy czym większość z nich polega na modyfikacji definicji kolumn i/lub zmian w kodzie. Na nasze potrzeby najsensowniejsze okazało się wprowadzenie property (jako property połączenia lub property systemowe) oracle.jdbc.V8Compatible=true. Oznacza to powrót do mapowania między DATE a java.sql.TimeStamp zgodnie z tym jak to było dla Oracle 8i (który nie miał typu TIMESTAMP). W przypadku konfiguracji Spring dla c3p0 definicja DataSource wygląda mniej więcej następująco:
  

  
  <bean class="com.mchange.v2.c3p0.ComboPooledDataSource" id="dataSourcePool" method="close">
  <property name="properties">
  <props>
          <prop key="oracle.jdbc.V8Compatible">true</prop>
  </props>
  </property>
  <property name="driverClass" value="${db.driver}"/>
  <property name="jdbcUrl" value="${db.url}"/>
  <property name="user" value="${db.username}"/>
  <property name="password" value="${db.pwd}"/>
  ...
  </bean>

  
  Ważne jest zachowanie kolejności: najpierw property properties wraz z entry oracle.jdbc.V8Compatible ustawione na true, a dopiero później pozostałe propertiesy.
  
• Zgodnie z FAQ oracle.jdbc.V8Compatible zostało wprowadzone aby zapewnić kompatybilność z Oracle 8.1, który nie posiadał typu Timestamp. Oznacza to, że przy przekazywaniu do bazy danych obiektu typu java.sql.Timestamp (z poziomu Hibernate w postaci property typu java.util.Timestamp lub property typu java.util.Calendar/ java.util.Date z annotacją @Temporal(TemporalType.TIMESTAMP) ) zostanie on przekonwertowany na typ bazodanowe DATE , który nie przechowuje milisekund. Czyli, jeśli mamy w bazie kolumne ts typu TIMESTAMP to pomimo mapowania:

  
  @Column(name="ts)
  java.sql.Timestamp ts;

  lub

  
  @Column(name="ts)
  @Temporal(TemporalType.TIMESTAMP)
  java.util.Date/java.util.Calendar ts;

  insert/update property ts zapisze do bazy danych date z czasem ale bez milisekund.
  Co ciekawsze przy odczycie, property ts posiada milisekundy !!! (zakres milisekund zależy od tego czy zamapowaliśmy na javowe Calendar, Date czy Timestamp) . Jeśli chcemy z poziomu JDBC przechowywać w Oracle daty wraz z czasem z milisekundami nie można użyć oracle.jdbc.V8Compatible=true. Aby móc jednocześnie walidować schemat wystarczy wskazać typ kolumny jako wartość columnDefinition w annotacji @Column:
  

  
  @Column(columnDefinition="date")
  @Temporal(TemporalType.TIMESTAMP)
  private java.util.Date d;

  Dodatkowo warto pamiętac, że w przypadku zapisywania do kolumny DATE wartości typu java.sql.Timestamp (czyli z poziomu Hibernate property typu java.sql.Timestamp lub java.util.Calendar/java.util.Date z annotacją @Temporal(TemporalType.TIMESTAMP) ) do bazy zawsze trafi data z czasem bez milisekund. Jest to logiczne gdyż DATE nie trzyma milisekund. Jednak gdy najpierw zapiszemy do bazy wiersz, którego składową jest kolumna DATE wypełniana z pozimou javy wartością typu java.sql.Timestamp ( z poziomu Hibernate property typu java.sql.Timestamp lub java.util.Calendar/java.util.Date z annotacją @Temporal(TemporalType.TIMESTAMP) ), a następnie będziemy chcieli tą samą wartość wykorzystać w warunku where, może się okazać, że nie znajdziemy już takiej krotki. ORACLE najpierw dokona "promocji" wartości w kolumnie DATE do typu parametru wejściowego. W naszym przypadku wartości w kolumnie typu DATE zostaną "wypromowane" do typu TIMESTAMP, ponieważ nasz wejściowy parametr typu java.sql.Timestamp zostanie zamapowany po stronie bazy na typ TIMESTAMP, przy czym proces "promowanie" dodatkowo uzupełni milisekund zerami. Następnie dopiero zostanie wykonane porównywanie. Nawet jeśli ręcznie "wyzerujemy" milisekundy naszego wejściowego java.sql.Timestamp i w ten sposób znajdziemy żądany wiersz, to trzeba pamiętac, że w zapytaniu ORACLE nie użyje indeksu założonego na kolumnie typu DATE- indeks jest założony na typie DATE a nie TIMESTAMP. W przypadku gdy mielibyśmy włączone oracle.jdbc.V8Compatible takich problemów udałoby się uniknąć, ponieważ sterownik JDBC zamapuje nam typ java.sql.Timestamp na DATE. Więcej na ten temat możemy znaleźć tu i tu
• "Wrong column type in {nazwa schematu.nazwa kolumny} for column {nazwa kolumny}. Found: char, expected: varchar2(255 char)". Walidacja schematu przez Hibernate(hibernate.hbm2ddl.auto=validate) wyrzuca wyjątek przy mapowaniu:
  

  
  @Column(name="name")
  private String name;

  gdy kolumna w bazie jest typu char(x) gdzie x>1.
  Problem wynika z błędu w Hibernate, który mapuje bazodanowy typ char na javowy Character.
  Gdy zmienimy mapowanie na
  

  
  @Column(name="name")
  private char name;

  
  wszystko jest oki, ale będziemy ograniczeni do przechowywania/odczytywania pojedyńczego znaku...
  Aby typ char(x) mapował się na String trzeba albo załadować patch opisany powyżej w zgłoszeniu, albo też wskazać konkretny typ bazdanowy na jaki będzie zamapowane nasze property.
  W tym drugim przypadku wystarczy dopisać columnDefinition
  

  
  @Column(name="name", columnDefinition="char")
  private String name;



• Criterion uwzględniające escapowanie znaków specjalnych do wyszukiwania za pomocą like: http://levanhuy.wordpress.com/2009/02/19/providing-an-escape-sequence-for-criteria-queries/
  Trzeba jednak kod trochę zmodyfikować aby escapował znak służący jako escape character, czyli w tym przypadku trzeba escapowac znak '\'.
  
• Wyszukiwanie Clob po zawartości. Posiadając mapowanie

  @Lob
  @Column(name = "CONTENT", nullable = false)
  private char[] content;
  

  do typu bazodanowego CLOB można wyszukiwać Cloby po zawartości. Nie chcę tutaj pisać na temat wydajności takiego rozwiązania, ale ogólnie jest to możliwe i działa. Wyszukiwanie przy pomocy like wygląda standardowo:
  

  List list = session.createQuery(" from Obj where content like ?").setParameter(0, "%Ala%".toCharArray()).list();
  

  
  W przypadku używanie Criteria najlepiej posłużyc się zmodyfikowaną klasą CriteriaEscape predstawioną powyżej. Dla Clob, które są zamapowane do char[] trzeba dokonać małej poprawki:
  

  public TypedValue[] getTypedValues(Criteria criteria,
  CriteriaQuery criteriaQuery) throws HibernateException {
  return new TypedValue[]{criteriaQuery.getTypedValue(criteria, propertyName, ("%" + value + "%").toCharArray())};
  }
  

  
  Idealnie wydaję się być usunięcie manualnej konkatenacji Stringów i użycie org.hibernate.criterion.MatchMode.toMatchString(). Nie tylko będzie to bardziej eleganckie, ale też bardziej funkcjonalne - można by przekazać implementacje org.hibernate.criterion.MatchMode w konstruktorze klasy i dzięki temu możemy łatwo sterować czy znak '%' ma być na początku/końcu czy i na początku i na końcu szukanej frazy.
  Trochę inaczej to wygląda gdy spróbujemy wyszukiwać Cloby za pomocą operatora równości. Próba wykonania kodu:
  

  Object object = currentSession.createQuery(" from Obj where content=?" ).setParameter(0, "ipsum".toCharArray()).setMaxResults(1).uniqueResult();
  

  
  kończy się wyjątkiem: java.sql.SQLException: ORA-00932: inconsistent datatypes: expected - got CLOB. Nie da się ukryć, że typy faktycznie nie pasują. Można się posiłkować Oraclową funkcją TO_CHAR. W Oracle 10.1.0.4.2 takie zapytanie przejdzie:
  

  
  Object object = currentSession.createQuery(" from Obj where to_char(content)=?" ).setParameter(0, "ipsum".toCharArray()).setMaxResults(1).uniqueResult();
  

  
  Oracle "po cichu" obetnie CLOB do pierwszych 4000 znaków i wtedy dokona porównania.
  Od wersji 10.2.0.1.0 jednak w takim przypadku dostaniemy błąd:
  SQL Error: ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: {wartość powyżej 4000}, maximum: 4000). Oracle tym razem już nie obetnie "po cichu" CLOB do 4000 znaków, ale można takie zachowanie na wymusić:
  

  Object object = currentSession.createQuery(" from Obj where to_char(substr(CONTENT,1,4000))=?" ).setParameter(0, "ipsum".toCharArray()).setMaxResults(1).uniqueResult();
  

  
  
• Przy okazji udało się natrafić na parę "niedoskonałości" Hibernate, które zostały już jakiś czas temu znaleziono, ale nie poprawione
  
  
  
  • Zdefiniowanie Criteria na klasie, która nie jest zmapowana, nie powoduje wystąpienia żadnego wyjątku. Jest to dziwne biorąc pod uwagę, że w HQL coś takiego nie przejdzie. Problem został już dawno zgłoszony
  • Hibernate generuje nie poprawne zapytanie w przypadku projekcji definiującej alias, który jest taki sam jak nazwa property a jednocześnie zdefinujemy restrykcji na tym property. Objawia się to wyjątkiem java.sql.SQLException: ORA-00904: "Y0_": invalid identifier. Dotyczy to jedynie aliasów dla root entity. Problem juz został dawno zgłoszony, przygotowano dla niego patch, ale bezpieczniej jest prefixowanie wszystkich restrykcji na root entity za pomocą aliasu"this"
    

    ProjectionList projectionList = Projections.projectionList().add( Projections.id().as( "id" ) ).add( Projections
                       .property( "name" ).as( "name" ) ).add( Projections.property( "surname" ).as( "surname" ) );
    List result = currentSession.createCriteria(Author.class)
     .setProjection( projectionList )
     .add( Restrictions.eq( "this.surname", "Borchardt") )
     .setResultTransformer( new AliasToBeanResultTransformer(Author.class) )
     .list();
    

    albo korzystanie z podlasy AliasedProjection napisanej przez Chris Federowicza i Kevin Schmidta
    

    
    ProjectionList projectionList = Projections.projectionList().add( new CustomPropertyAliasProjection("id","id"))
        .add( new CustomPropertyAliasProjection("name", "name")).add( new CustomPropertyAliasProjection("surname","surname" ));
    List result = currentSession.createCriteria(Author.class)
     .setProjection( projectionList )
     .add( Restrictions.like( "surname", "Borchardt") )
     .setResultTransformer( new AliasToBeanResultTransformer(Author.class) )
     .list();
    

    
    Problem ten nie dotyczy wszystkich baz, np HSQLDB radzi sobie bez problemu gdy warunek w where jest definiowany na aliasie a nie na kolumnie, czyli bład ten tam nie występuję. Ogólnie jest to kolejny przykład "rozjazdu" kiedy system pracuję z bazą A a na testy podpinamy bazę B.