Hi,
I have a problem with HSQLDB and Hibernate. With this String I want to get the number of objects that are stored in the database with some certain properties:
String hql="select count(*) from " +TRANSFER
+" where art='" +trf.getClass().getName()
+"' and startOrt='" +trf.getStartOrt()
+"' and zielOrt='" +trf.getZielOrt()
+"' and ankunftsZeit='" +trf.getAnkunftsZeit() // (*)
+"' and startZeit='" +trf.getStartZeit() //(**)
+"' and waehrung='" +trf.getPreis().getWaehrungsString()
+"' and betrag=" +trf.getPreis().getAmount();
int anzahl=((Integer)sess.iterate(hql).next() ).intValue();

Unfortunately it doesn't work. The methods marked with (*) and (**) return Calendar-objects. I tried another variation:
SimpleDateFormat format=new SimpleDateFormat("yy.MM.dd HH:mm:ss.SSSSSSSSS");
String ankunftsZeit=format.format(new Date(ankunft.getTimeInMillis()));
String start=format.format(new Date(startZeit.getTimeInMillis()));

String hql="select count(*) from " +TRANSFER
+" where art='" +trf.getClass().getName()
+"' and startOrt='" +trf.getStartOrt()
+"' and zielOrt='" +trf.getZielOrt()
+"' and ankunftsZeit='" +ankunftsZeit
+"' and startZeit='" +start
+"' and waehrung='" +trf.getPreis().getWaehrungsString()
+"' and betrag=" +trf.getPreis().getAmount();
int anzahl=((Integer)sess.iterate(hql).next() ).intValue();

I formatted the Calendar-objects. The format of hsqldb is yy.MM.dd HH:mm:ss.SSSSSSSSS. But this variation also doesn't work. The integer anzahl should contain the number of objects with the above properties. But it's always 0 although there is one object stored in the database having these properties.

Thanks
Barbara

Hibernate version: Hibernate 2.1
Code between sessionFactory.openSession() and session.close():
SimpleDateFormat format=new SimpleDateFormat("yy.MM.dd HH:mm:ss.SSSSSSSSS");
String ankunftsZeit=format.format(new Date(ankunft.getTimeInMillis()));
String start=format.format(new Date(startZeit.getTimeInMillis()));
System.out.println(ankunftsZeit +" " +start);
try {
sess=factory.openSession();
trans=sess.beginTransaction();
//Die Art eines Transfers muss gleichzeitig auch der genaue Name der Klasse sein
String hql="select count(*) from " +TRANSFER
+" where art='" +trf.getClass().getName()
+"' and startOrt='" +trf.getStartOrt()
+"' and zielOrt='" +trf.getZielOrt()
+"' and ankunftsZeit='" +ankunftsZeit
+"' and startZeit='" +start
+"' and waehrung='" +trf.getPreis().getWaehrungsString()
+"' and betrag=" +trf.getPreis().getAmount();
int anzahl=((Integer)sess.iterate(hql).next() ).intValue();
Require.that(anzahl==0,"Dieser Transfer existiert bereits.");
trans.commit();
}
catch (HibernateException he) {
he.printStackTrace();
}
finally{
sess.close();
}
saveNewObject(trf);


Name and version of the database you are using: Hsqldb 1.7.3 (?)


The generated SQL (show_sql=true):

create table Transfer (
ID bigint not null,
Art varchar(255) not null,
startOrt varchar(255) not null,
zielOrt varchar(255) not null,
ankunftsZeit timestamp not null,
startZeit timestamp not null,
bezeichnung varchar(255) not null,
waehrung varchar(3) not null,
betrag double not null,
airline varchar(255),
primary key (ID)
)

1. don't use iterate() but list()
2. use paramter placeholders not string concatenation

This also doesn't seem to work. Here is another example:
try {
sess=factory.openSession();
trans=sess.beginTransaction();
String hql="select count(*) from " +dbUser.getClass().getName()
+" where lname='" +dbUser.getLname() +"'";
Query q=sess.createQuery(hql);
List list=q.list();
System.out.println(list.size());
System.out.println(list.get(0).getClass());
Require.that(list.size()==0,"Es gibt bereits einen User mit diesem Loginnamen.");
trans.commit();
}
catch (HibernateException he) {
he.printStackTrace();
}
finally{
sess.close();
}
saveNewObject(dbUser);

Here I used query.list();
The size of the returned list is 1 although there is nothing stored in the Database.
System.out.println(list.get(0).getClass()) says that this object is an Integer. BTW: What do you mean with "use parameter placeholders"?

Thanks
Barbara