Here is the source code:
Code:
DateTimeFormatter dateTimeFormatter = DateTimeFormat.forPattern("YYYY-MM-dd");
criteria.add(Restrictions.sqlRestriction("trunc(creation_datetime, 'YYYY-MM-DD') between to_date('" + dateTimeFormatter.print(fromDateTime) + "', 'YYYY-MM-DD') and to_date('" + dateTimeFormatter.print(toDateTime) + "', 'YYYY-MM-DD')"));
criteria.addOrder(Order.asc("firstname")).addOrder(Order.asc("lastname")).addOrder(Order.asc("email"));
The table structure:
Code:
create table mail_address (
id number(10) not null,
version number(10) not null,
firstname varchar2(255),
lastname varchar2(255),
email varchar2(255) not null,
constraint mail_address_u1 unique (email),
text_comment clob,
country varchar2(255),
subscribe number(1) not null check (subscribe in (0, 1)),
imported number(1) not null check (imported in (0, 1)),
creation_datetime date default NULL,
constraint mail_address_pk primary key (id)
);
create sequence sq_id_mail_address increment by 1 start with 1 nomaxvalue nocycle cache 10;
create or replace trigger tr_id_inc_mail_address
before insert
on mail_address
for each row
declare
begin
if (:new.id is null)
then
select sq_id_mail_address.nextval into :new.id from dual;
end if;
end;
The sql statement seen in the console log:
Quote:
testFindWithCreationDateTime(com.thalasoft.learnintouch.core.dao.MailAddressDaoTest) Time elapsed: 0.124 sec <<< ERROR!
org.springframework.dao.DataIntegrityViolationException: could not execute query; SQL [select this_.id as id45_0_, this_.version as version45_0_, this_.firstname as firstname45_0_, this_.lastname as lastname45_0_, this_.email as email45_0_, this_.text_comment as text6_45_0_, this_.country as country45_0_, this_.subscribe as subscribe45_0_, this_.imported as imported45_0_, this_.creation_datetime as creation10_45_0_ from mail_address this_ where trunc(creation_datetime, 'YYYY-MM-DD') between to_date('2012-12-03', 'YYYY-MM-DD') and to_date('2012-12-04', 'YYYY-MM-DD') order by this_.firstname asc, this_.lastname asc, this_.email asc]; nested exception is org.hibernate.exception.DataException: could not execute query
But this sql statement runs fine from the client console:
Quote:
SQL> select this_.id as id45_0_, this_.version as version45_0_, this_.firstname as firstname45_0_, this_.lastname as lastname45_0_, this_.email as email45_0_, this_.text_comment as text6_45_0_, this_.country as country45_0_, this_.subscribe as subscribe45_0_, this_.imported as imported45_0_, this_.creation_datetime as creation10_45_0_ from mail_address this_ where trunc(creation_datetime, 'YYYY-MM-DD') between to_date('2012-12-03', 'YYYY-MM-DD') and to_date('2012-12-04', 'YYYY-MM-DD') order by this_.firstname asc, this_.lastname asc, this_.email asc;
no rows selected
Login
FAQ
Search
