Pewnym popularnym rozwiązaniem, jest ustawienie specjalnego property dla ORACLE 9/10 JDBC driver: oracle.jdbc.V8Compatible=true. Konsekwencje użycia tego ustawienia można znaleźć tu.
Wyżej wymieniony problem można samemu zdiagnozować za pomocą testu.
Środowisko testowe korzysta z Oracle 10 g (10.2.0), JDBC Driver 10.2.0.4 a same testy są uruchamiane w JUNIT 4 za pomocą Spring TestContext Framework.
Encja, która będzie służyć do testów:
@Entity
@Table(name = "TestTable")
@SequenceGenerator(name = "seq_id", sequenceName = "SQ_TT")
public class TestEntity {
@Id
@GeneratedValue(generator = "seq_id")
Long i;
@Column
String state;
@Column(columnDefinition = "date")
@Temporal(TemporalType.TIMESTAMP)
Date d;
public TestEntity() {
}
public TestEntity( String state, Date d ) {
this.state = state;
this.d = d;
}
Test ukazujący w/w problem
@Test
public void test1(){
TestEntity t = new TestEntity("foo",new Date());
sessionFactory.getCurrentSession().save( t );
sessionFactory.getCurrentSession().flush();
sessionFactory.getCurrentSession().clear();
t = (TestEntity) sessionFactory.getCurrentSession().createQuery( "from TestEntity where d=?").setTimestamp( 0, t.getD()).uniqueResult();
assertThat( t, notNullValue() );
}
Test ten kończy się failure.
Można to łatwo poprawić, gdy "wyzerujemy" milisekundy
TestEntity t = new TestEntity("foo",new Date());
sessionFactory.getCurrentSession().save( t );
sessionFactory.getCurrentSession().flush();
sessionFactory.getCurrentSession().clear();
Calendar cal = Calendar.getInstance();
cal.setTime( t.getD() );
cal.set( Calendar.MILLISECOND, 0 );
t = (TestEntity) sessionFactory.getCurrentSession().createQuery( " from TestEntity where d=?").setTimestamp( 0, cal.getTime()).uniqueResult();
assertThat( t, notNullValue() );
Wykonanie tego testu kończy się sukcesem.
Na tym niestety problem się nie kończy. Pomimo tego, że udało nam się uzyskać poprawny wynik, to potrzeba "zerowania" milisekund jest strasznie upierdliwe, ale co gorsze zapytanie takie nie wykorzystują indeksu, który byłyby założone na kolumnie typu DATE. Można się o tym przekonać wykonując następujące polecenia (użytkownik system) :
select sql_id, child_number,sql_text from V$SQL where sql_text like '%from TestEntity%' order by last_load_time desc;
W wyniku dostaniemy sql_id oraz child_number, obie wartości służą jako input dla polecenia:
SELECT * FROM table(DBMS_XPLAN.display_cursor('<sql_id >',<child_number>));
W przypadku gdy mielibyśmy stworzony indeks np. na 2 kolumnach (jedna z nich będzie typu DATE), to zapytania z warunkiem where, które teoretycznie mogłyby wykorzystywać indeks (, np status=? and d>=? and d mogą się bardzo długo wykonywać.
Jeśli nawet ORACLE użyje indeksu to może zrobić to bardzo nieefektywnie.
Jest to spowodowane tym, że ORACLE będzie "promował" daty w indeksie do typu TIMESTAMP. Taka "promocja" powoduję, że przeglądnięcie indeksu jest nieefektywne. Widać to w planie zapytania:
|* 6 | INDEX RANGE SCAN | MOJ_INDEX | 741 | | |
--------------------------------------------------------------------------------------------------------
6 - access("state"=:3)
filter((INTERNAL_FUNCTION("D")>=:1 AND INTERNAL_FUNCTION("D")<:2))
"Promocja" typu jest prezentowana jako INTERNAL_FUNCTION
Rozwiązaniem problemu (brak użycia indeksu oraz potrzeba "zerowania" milisekund) byłoby "wypchnięcie" do bazy z poziomu JDBC wartości typu DATE a nie TIMESTAMP.
Można to zrobić poprzez:
- używanie funkcji to_date - wymaga to ręcznego wprowadzania daty w postaci String
@Test
public void testToDate(){
TestEntity t = new TestEntity("foo",new Date());
sessionFactory.getCurrentSession().save( t );
sessionFactory.getCurrentSession().flush();
sessionFactory.getCurrentSession().clear();
String dateInStringFormat = createDateFormat( "yyyy-MM-dd HH:mm:ss" ).format( t.getD() );
t = (TestEntity) sessionFactory.getCurrentSession().createQuery( " from TestEntity where d=to_date(?,'YYYY-MM-DD HH24:MI:SS')").setString( 0, dateInStringFormat ).uniqueResult();
assertThat( t, notNullValue() );
} - użycie funkcji cast as
- cast z TIMESTAMP na DATE nie tylko "rzutuje" jeden typ na drugi, ale także dokonuję zaokrąglenia:
Nie objedzie się w takim przypadku od "zerowania" milisekund. W przypadku Criteria API nie wiem w jaki sposób out-of -box używać wywołań funkcji. Chyba trzeba by stworzyć własnego Criterion
@Test
public void testCastMilis500andAbove(){
Calendar calendar = Calendar.getInstance();
calendar.setTime( new Date() );
calendar.set( Calendar.MILLISECOND, 500 );
TestEntity t = new TestEntity("foo",calendar.getTime());
sessionFactory.getCurrentSession().save( t );
sessionFactory.getCurrentSession().flush();
sessionFactory.getCurrentSession().clear();
t = (TestEntity) sessionFactory.getCurrentSession().createQuery( " from TestEntity where d= cast (? as date)").setTimestamp( 0, calendar.getTime()).uniqueResult();
assertThat( t, nullValue() );
}
@Test
public void testCastMillisBelow500(){
Calendar calendar = Calendar.getInstance();
calendar.setTime( new Date() );
calendar.set( Calendar.MILLISECOND, 499 );
TestEntity t = new TestEntity("foo",calendar.getTime());
sessionFactory.getCurrentSession().save( t );
sessionFactory.getCurrentSession().flush();
sessionFactory.getCurrentSession().clear();
t = (TestEntity) sessionFactory.getCurrentSession().createQuery( " from TestEntity where d= cast (? as date)").setTimestamp( 0, calendar.getTime()).uniqueResult();
assertThat( t, notNullValue() );
} - bezpośrednie użycie typu oracle.sql.DATE:
@Test
public void test3ImplicitOracleDateJDBC() throws SQLException{
TestEntity t = new TestEntity("foo",new Date());
sessionFactory.getCurrentSession().save( t );
sessionFactory.getCurrentSession().flush();
sessionFactory.getCurrentSession().clear();
SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
Timestamp ts = new Timestamp(t.getD().getTime());
oracle.sql.DATE d = new oracle.sql.DATE(ts);
long queryForLong = jdbcTemplate.queryForLong( "select count(* ) from TestTable where d=?", d );
assertThat( queryForLong, equalTo( 1l ) );
}
Skuteczne, ale niewygodne. W przypadku HQL trzeba by pewnie stworzyć custom type, który byłby użyty przy bindowaniu parametrów przy pomocy wywołania
Query setParameter(int position, Object val, Type type);
W przypadku użycia Criteria API chyba najwygodniejsze byłoby stworzenie własnego Criterion - przy użyciu driveraJDBC w wersji 11.1.0.7.0 zadziała taka konstrukcja:
@Test
public void testImplicitType() throws SQLException{
TestEntity t = new TestEntity("foo",new Date());
sessionFactory.getCurrentSession().save( t );
sessionFactory.getCurrentSession().flush();
sessionFactory.getCurrentSession().clear();
Connection connection = sessionFactory.getCurrentSession().connection();
PreparedStatement preparedStatement = connection.prepareStatement( "select count(*) from TestTable where d=?");
preparedStatement.setObject( 1,new Timestamp(t.getD().getTime()) ,java.sql.Types.DATE );
assertThat( preparedStatement.executeUpdate(), equalTo( 1 ));
} - Oracle JDBC 9/10 (dla 9 nie sprawdzałem) driver undocumented "feature":
@Test
public void testSetTimeMilis500andAbove(){
Calendar calendar = Calendar.getInstance();
calendar.setTime( new Date() );
calendar.set( Calendar.MILLISECOND, 500 );
TestEntity t = new TestEntity("foo",calendar.getTime());
sessionFactory.getCurrentSession().save( t );
sessionFactory.getCurrentSession().flush();
sessionFactory.getCurrentSession().clear();
t = (TestEntity) sessionFactory.getCurrentSession().createQuery( " from TestEntity where d= ? ").setTime( 0, calendar.getTime()).uniqueResult();
assertThat( t, notNullValue() );
}
@Test
public void testSetTimeMilisBelow500(){
Calendar calendar = Calendar.getInstance();
calendar.setTime( new Date() );
calendar.set( Calendar.MILLISECOND, 499 );
TestEntity t = new TestEntity("foo",calendar.getTime());
sessionFactory.getCurrentSession().save( t );
sessionFactory.getCurrentSession().flush();
sessionFactory.getCurrentSession().clear();
t = (TestEntity) sessionFactory.getCurrentSession().createQuery( " from TestEntity where d=?").setTime( 0, calendar.getTime()).uniqueResult();
assertThat( t, notNullValue() );;
}
Zgodnie z dokumentacją setTime() powinno zostać skonwertowane na TIME, ale konwersja jest robiona na DATE! Dla Criteria API trzeba by stworzyć własne Criterion, aby móc wymusić na Hibernate użycie setTime() - Hibernate out-of-box decyduje jaką metodę wywołac na PreparedStatement na podstawie typu/annotacji property na której ustawiamy warunek.
