aspose file tools*
The moose likes Object Relational Mapping and the fly likes query a database table using a foreign key ( where) using Hibernate. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "query a database table using a foreign key ( where) using Hibernate." Watch "query a database table using a foreign key ( where) using Hibernate." New topic
Author

query a database table using a foreign key ( where) using Hibernate.

Sammy Bill
Ranch Hand

Joined: Dec 29, 2008
Posts: 96
Hello,

i m new to hibernate, so the question might look stupid.

I have the two tables:

CREATE TABLE CUSTOMER (
USERNAME VARCHAR(50) PRIMARY KEY,
PASSWORD VARCHAR(50) NOT NULL,
)

CREATE TABLE CUSTOMERORDER (
ORDERID VARCHAR(50) PRIMARY KEY,
USERNAME VARCHAR(50) NOT NULL,
TOTALCOST DOUBLE NOT NULL,
FOREIGN KEY (USERNAME) REFERENCES CUSTOMER (USERNAME)
)

and i want to get a list of CustomerOrder that matches a given customer.

i did the following query:


but it is not working. i also try few different approaches but always get an empty list.
i know the problem is the query itself, nothing else.

any ideas??

Thanks

T
Javid Jamae
Author
Ranch Hand

Joined: May 14, 2008
Posts: 198
Your concatenating a Customer object to your SQL_QUERY string (which is really not a SQL query, but an HQL query), so it will append the toString() representation of that object.

What you want to do is:



BTW: Username is a terrible PK for your table. You should use a synthetic primary key instead. A username might change, be recycled over time, etc.


Author: JBoss in Action, javidjamae.com, @javidjamae on Twitter
Sammy Bill
Ranch Hand

Joined: Dec 29, 2008
Posts: 96
Thanks a lot for your quick replay.

unfortunately, when i try your code i get a SQLGrammarException: could not execute query.
any idea way?

thanks again.

Javid Jamae wrote:Your concatenating a Customer object to your SQL_QUERY string (which is really not a SQL query, but an HQL query), so it will append the toString() representation of that object.

What you want to do is:



BTW: Username is a terrible PK for your table. You should use a synthetic primary key instead. A username might change, be recycled over time, etc.

Javid Jamae
Author
Ranch Hand

Joined: May 14, 2008
Posts: 198
Could you show me the entire stack trace, and both mapping files?
Sammy Bill
Ranch Hand

Joined: Dec 29, 2008
Posts: 96
Javid Jamae wrote:Could you show me the entire stack trace, and both mapping files?


org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2231)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at com.shopping.db.DAO.getOrderByUserName(DAO.java:34)
at com.shopping.db.DAOTest.testGetOrderByUserName(DAOTest.java:18)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99)
at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)
at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)
at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:66)
at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)
at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: java.sql.SQLException: Wrong data type: type: DATE (91) expected: BIGINT value: 2009-01-01
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcResultSet.getColumnInType(Unknown Source)
at org.hsqldb.jdbc.jdbcResultSet.getLong(Unknown Source)
at org.hsqldb.jdbc.jdbcResultSet.getLong(Unknown Source)
at org.hibernate.type.LongType.get(LongType.java:51)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:173)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:105)
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2124)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1404)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1332)
at org.hibernate.loader.Loader.getRow(Loader.java:1230)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:603)
at org.hibernate.loader.Loader.doQuery(Loader.java:724)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
... 29 more


<hibernate-mapping>
<class name="com.shopping.db.Customer" table="CUSTOMER" schema="PUBLIC">
<id name="userName" type="string">
<column name="USERNAME" length="50" />
<generator class="assigned" />
</id>
<property name="password" type="string">
<column name="PASSWORD" length="50" not-null="true" />
</property>

</class>
</hibernate-mapping>


<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Feb 17, 2009 8:33:40 AM by Hibernate Tools 3.2.2.GA -->
<hibernate-mapping>
<class name="com.shopping.db.CustomerOrder" table="CUSTOMERORDER" schema="PUBLIC">
<id name="orderID" type="long">
<column name="ORDERID" length="50" />
<generator class="assigned" />
</id>
<many-to-one name="customer" class="com.shopping.db.Customer" fetch="select">
<column name="USERNAME" length="50" not-null="true" />
</many-to-one>
<property name="totalCost" type="float">
<column name="TOTALCOST" precision="0" scale="0" not-null="true" />
</property>
</class>
</hibernate-mapping>

by the way, i get the same error when i retrieve an order based on an orderID.


String SQL_QUERY =" from CustomerOrder as order where order.orderID ='" + orderID + "'";
Query query = session.createQuery(SQL_QUERY);
System.out.print(query.list().size());
CustomerOrder order = (CustomerOrder) query.list().get(0);
return order;

but when i retrieve from Customer using userName it works fine.
so i guess something is wrong with the CustomerOrder class.


Thanks for your help.
Javid Jamae
Author
Ranch Hand

Joined: May 14, 2008
Posts: 198
Caused by: java.sql.SQLException: Wrong data type: type: DATE (91) expected: BIGINT value: 2009-01-01


This is very suspect. Are you using a date somewhere?
Sammy Bill
Ranch Hand

Joined: Dec 29, 2008
Posts: 96
Javid Jamae wrote:
Caused by: java.sql.SQLException: Wrong data type: type: DATE (91) expected: BIGINT value: 2009-01-01


This is very suspect. Are you using a date somewhere?


ohh yeah, i m using a Date object for orderdate in the DB while i have a long for the date in the CustomerOrder object.

I will fix it and let you know what happend.

Thanks a lot.
Sammy Bill
Ranch Hand

Joined: Dec 29, 2008
Posts: 96
Javid Jamae wrote:
Caused by: java.sql.SQLException: Wrong data type: type: DATE (91) expected: BIGINT value: 2009-01-01


This is very suspect. Are you using a date somewhere?


I changed the Date to use the same type in both the DB and in the DTO and it works great.

Thanks lot for your help.
Javid Jamae
Author
Ranch Hand

Joined: May 14, 2008
Posts: 198
Just a suggestions, but in addition to using a synthetic PK, I would also suggest not using a floating point number for a monetary amount. Multiply by 100 and keep track of money in pennies instead of dollars. That way you eliminate many issues involved in doing floating point calculations and conversions.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: query a database table using a foreign key ( where) using Hibernate.