-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: need help with Date and Timestamp from String
PostPosted: Wed Mar 24, 2004 11:35 pm 
Beginner
Beginner

Joined: Wed Feb 25, 2004 5:54 am
Posts: 30
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??


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 25, 2004 12:26 am 
Beginner
Beginner

Joined: Wed Feb 25, 2004 5:54 am
Posts: 30
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 25, 2004 6:36 am 
jTDS Developer
jTDS Developer

Joined: Tue Feb 24, 2004 5:36 pm
Posts: 70
Location: Bucharest, Romania
Use parameter binding rather than creating the query each time (see section 8.3 of the Hibernate reference).

Alin.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.