I really hope this q is Hibernate related coz I'm so lost here.

What I really want is keeping a time-specific data (hh:mm:ss)
in Database.

I'm using MS-SQL Server 2000, Hibernate 2.1 and Eclipse
I've tried this :


Txtion.java
import java.util.Date;
import java.sql.Time;

public class Txtion
{
/*other properties*/
private Time hourStart;
private Time hourEnd;

public Time getHourEnd()
{
return hourEnd;
}

public Time getHourStart()
{
return hourStart;
}

public void setHourEnd(Time end)
{
hourEnd = end;
}

public void setHourStart(Time start)
{
hourStart = start;
}
}

Txtion.hbm.xml
<hibernate-mapping>
<class name="Txtion" table="TRANSACTION">
<id name="id" column="ID">
<generator class="native" />
</id>
<property name="date" column="TRANSACTION_DATE" not-null="false" />
<property name="hourStart" column="HOUR_START" not-null="false" />
<property name="hourEnd" column="HOUR_END" not-null="false" />
</class>
</hibernate-mapping>

When I do this :
newTx = new Txtion();
newTx.setHourStart(Time.valueOf("21:46:00"));
newTx.setHourEnd(Time.valueOf("22:30:00"));

This is wat I got inside the Database :

Table :
TRANSACTION

HOUR_START : 1970-01-01 21:46:00.000
HOUR_END : 1970-01-01 22:30:00.000


While in fact I want just the time, not the date.
Question 1 : Do I have to declare type="timestamp" in HBM.XML?

Question 2 : or is it better to keep the whole date by using java.util.Date as the type of hourStart and hourEnd instead of java.sql.Time ??

Question 3 : or is there another way around??

OK let say I'm fine with dat.. now I have a problem extracting data from the database.

I wanna run a method whose arguments are String, so I could parse the String into a Date then retrieve it.

since '1970-01-01 21:46:00.000' is recorded in DB, then I assume this is the query would look like :

SELECT tx.* FROM TRANSACTION as tx,USERS as usr
WHERE tx.HOUR_START ='1970-01-01 21:46:00.000'
AND usr.USERNAME = 'Whonai';

that query works perfectly fine.. using query analyzer

now I have two (experimental) methods in my retrieval class :

GetTxtion.java
import net.sf.hibernate.*;

import java.util.*;
import java.io.*;
import java.text.*;


public class GetTxtion
{
protected SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.S");
private Txtion theTrax;
private List traxList;

public GetTxtion() throws IOException,HibernateException
{
Calendar tmp = Calendar.getInstance();
Date dt = null;
try {
dt = sdf.parse("1970-01-01 21:46:00.000");
}
catch (ParseException e) {e.printStackTrace();}

String s = "1970-01-01 21:46:00.000";

byUserAndDate("Whonai",s);
byUserAndDate("Whonai",dt); }



public List byUserAndDate(String name, String hrStart) throws HibernateException
{
System.out.println("Retrieving the transaction from a specific User by Date ");
sess = sessFact.openSession();

Transaction tx = null;
List theList;
try
{
tx = sess.beginTransaction();


String[] alias = { "usr","tx" };
Class[] cls = {User.class, Txtion.class};
String query =
"SELECT {tx.*} FROM TRANSACTION {tx},USERS {usr}
WHERE {tx}.HOUR_START ='"
+ hrStart +"' AND {usr}.USERNAME = '"+ name +"' ";
Query theQuery = sess.createSQLQuery(query, alias, cls);

theList = theQuery.list();
if (theList.size() == 0)
{
System.out.println("No transaction");
return null;
}
else
{
System.out.println("Transaction is successfully retrieved");
}
tx.commit();

}
catch(HibernateException he)
{
if(tx!=null) tx.rollback();
throw he;
}
finally
{
sess.close();
}
return theList;
}



public List byUserAndDate(String name, Date hrStart) throws HibernateException
{
System.out.println("Retrieving the transaction from a specific User by Date ");
sess = sessFact.openSession();

Transaction tx = null;
List theList;
try
{
tx = sess.beginTransaction();


String[] alias = { "usr","tx" };
Class[] cls = {User.class, Txtion.class};
String query =
"SELECT {tx.*} FROM TRANSACTION {tx},USERS {usr}
WHERE {tx}.HOUR_START ='"
+ hrStart +"' AND {usr}.USERNAME = '"+ name +"' ";
Query theQuery = sess.createSQLQuery(query, alias, cls);

theList = theQuery.list();
if (theList.size() == 0)
{
System.out.println("No transaction");
return null;
}
else
{
System.out.println("Transaction is successfully retrieved");
}
tx.commit();

}
catch(HibernateException he)
{
if(tx!=null) tx.rollback();
throw he;
}
finally
{
sess.close();
}
return theList;
}

public static void main(String[] args)
{
try {
new GetTransaction();
} catch (HibernateException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

}

The first method : (using string as date argument)

byUserAndDate("Whonai",s);
would result in these errors: Syntax error converting datetime from character string

Retrieving the transaction from a specific User by Date
Hibernate: SELECT tx.ID as ID1_, tx.USER_ID as USER_ID1_, tx.CATEGORY_ID as CATEGORY3_1_, tx.PROJECT_ID as PROJECT_ID1_, tx.LOCATION_ID as LOCATION5_1_, tx.TRANSACTION_DATE as TRANSACT6_1_, tx.HOUR_START as HOUR_START1_, tx.HOUR_END as HOUR_END1_, tx.DESCRIPTION as DESCRIPT9_1_, tx.RATE_VALUE as RATE_VALUE1_, tx.CURRENCY as CURRENCY1_, tx.DURATION as DURATION1_, tx.STATUS as STATUS1_ FROM TRANSACTION tx,USERS usr WHERE tx.HOUR_START ='1970-01-01 21?' AND usr.USERNAME = 'Whonai'
11:13:54,562 WARN JDBCExceptionReporter:38 - SQL Error: 241, SQLState: 22007
11:13:54,562 ERROR JDBCExceptionReporter:46 - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Syntax error converting datetime from character string.
11:13:54,578 WARN JDBCExceptionReporter:38 - SQL Error: 241, SQLState: 22007
11:13:54,578 ERROR JDBCExceptionReporter:46 - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Syntax error converting datetime from character string.
11:13:54,578 ERROR JDBCExceptionReporter:38 - SQLException occurred
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Syntax error converting datetime from character string.

and the second one :
byUserAndDate("Whonai",dt)

would result the same error..

if I remove the single quote at HOUR_START = ' " +hrStart + " ' ...so the query looks like this :
String query =
"SELECT {tx.*} FROM TRANSACTION {tx},USERS {usr} WHERE {tx}.HOUR_START = "+ hrStart +" AND {usr}.USERNAME = '"+ name +"' ";

they would say invalid parameter binding :

Hibernate: SELECT tx.ID as ID1_, tx.USER_ID as USER_ID1_, tx.CATEGORY_ID as CATEGORY3_1_, tx.PROJECT_ID as PROJECT_ID1_, tx.LOCATION_ID as LOCATION5_1_, tx.TRANSACTION_DATE as TRANSACT6_1_, tx.HOUR_START as HOUR_START1_, tx.HOUR_END as HOUR_END1_, tx.DESCRIPTION as DESCRIPT9_1_, tx.RATE_VALUE as RATE_VALUE1_, tx.CURRENCY as CURRENCY1_, tx.DURATION as DURATION1_, tx.STATUS as STATUS1_ FROM TRANSACTION tx,USERS usr WHERE tx.HOUR_START = Thu Jan 01 21? GMT+07? 1970 AND usr.USERNAME = 'Whonai'
11:21:05,687 WARN JDBCExceptionReporter:38 - SQL Error: 0, SQLState: 07009
11:21:05,687 ERROR JDBCExceptionReporter:46 - [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s).
11:21:05,703 WARN JDBCExceptionReporter:38 - SQL Error: 0, SQLState: 07009
11:21:05,703 ERROR JDBCExceptionReporter:46 - [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s).
11:21:05,718 ERROR JDBCExceptionReporter:38 - SQLException occurred
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s).



any help is appreciated.. thanks in advance.

Use parameter binding rather than creating the query each time (see section 8.3 of the Hibernate reference).

Alin.