I am trying to query an oracle database that contains 3 tables, Company, Employee, and Product.

Company
companyId PK
locationId PK
other fields

Employee
ssn PK
companyId PK, FK
locationId PK, FK
other fields

Product
productId PK
companyId PK, FK
locationId PK, FK

My mapping for the company class is:

<class name="org.hibernate.testing.Company">
<composite-id name="companyPK" class="org.hibernate.testing.CompanyPK">
<key-property name="companyId" type="long" column="companyId"/>
<key-property name="locationId" type="long" column="locationId"/>
</composite-id>
<property name="name" column="companyName" type="string"
length="15" not-null="true"/>
<property name="address" column="address" type="string"
length="25" not-null="true"/>
<property name="startDate" column="startDate" type="timestamp"
not-null="true"/>

<set name="employees"
fetch="select"
lazy="false"
inverse="true"
cascade="all">
<key>
<column name="companyId"/>
<column name="locationId"/>
</key>
<one-to-many class="org.hibernate.testing.Employee" />
</set>

<set name="products"
fetch="select"
lazy="false"
inverse="true"
cascade="all">
<key>
<column name="companyId"/>
<column name="locationId"/>
</key>
<one-to-many class="org.hibernate.testing.Product"/>
</set>

I have a GUI that will used to query the db and can search on 1 to all the fields in the 3 tables. I was trying to use code like this, but was getting an error when creatingCriteria was called to products. products does not associate with employees, but company so I got an exception. Is there a way around this: company is the parent and then set up the criteria for 2 children?

List comps = session.createCriteria(Company.class)
.add(Expression.between("companyPK.locationId", sc.getMinLocationId(), sc.getMaxLocationId() ) )
.add( Expression.between("companyPK.companyId", sc.getMinCompanyId(), sc.getMaxCompanyId()))

.createCriteria("employees")
.add(Expression.between("employeePk.ssn", sc.getMinSsn(), sc.getMaxSsn()))


.createCriteria("company.products")
.add( Expression.between("productPrice", sc.getMinProductPrice(), sc.getMaxProductPrice()))


I then tried to do a straight query and was getting an error trying to query on a column that is of type timestamp in the db and my search class( sc ); I got this error message

Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxError: unexpected token: 8 near line 1, column 302 [from org.hibernate.testing.Company c where c.companyPK.companyId >= 0 and c.companyPK.companyId <= 4 and c.companyPK.locationId >= 0 and c.companyPK.locationId <= 4 and c.name = 'compName1' and c.address like '%Main%' and c.employees.employeePk.ssn between 0 and 7 and c.startDate between 292278994-08-17 00:12:55.192 and 292278994-08-17 00:12:55.807]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.iterate(SessionImpl.java:920)
at org.hibernate.impl.QueryImpl.iterate(QueryImpl.java:41)
at org.hibernate.testing.Main.selectDataUsingSearchClass(Main.java:394)
at org.hibernate.testing.Main.main(Main.java:42)

I tried it two ways
1
str += " and c.startDate between " + sc.getMinCompanyStartDate();
str += " and " + sc.getMaxCompanyStartDate();
2
str += " and c.startDate >= " + sc.getMinCompanyStartDate();
str += " and c.startDate <= " + sc.getMaxCompanyStartDate();



One last thing, I have not done db work in quite a while, but if I did a search for all Companys that had employees with name = "joe"

and the db had these values, I get 3 records returned as a result. How do I prevent record 1 from being returned in my query results twice?

company Table
cId + lId
0 1 ( record 1 )
0 2 ( record 2 )

employee Table
cId Id ssn name
0 1 1 joe
0 2 2 joe
0 1 3 joe


thanks

According to your debug SQL statement, it doesn't look like your timestamp
statements contain the required ' character before and after the date.
I just tried a query locally with my timestamps, and received an exception
without the ' character. I'm using PostgreSQL, but I think the same rules apply.

I didn't bother to count characters, but column 302 in your query looks
to be somewhere in your timestamp. :-)
Also, my timestamps are in a different format:
2006-04-05 15:03:22.36

as opposed to yours:
292278994-08-17 00:12:55.807

Do you specially format the year to be that long value?

Why can't I execute this code. I get this exeception when q.list() is called.

String str = "from Company where companyPK.companyId, companyPK.locationId IN (SELECT e.employeePk.companyId, e.employeePk.locationId from Employee e WHERE ssn>1)";

Query q = session.createQuery(str);
List ll = q.list();


Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxError: unexpected token: , near line 1, column 61 [from org.hibernate.testing.Company where companyPK.companyId, companyPK.locationId IN (SELECT e.employeePk.companyId, e.employeePk.locationId from org.hibernate.testing.Employee e WHERE ssn>1)]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:834)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.hibernate.testing.Main.selectDataUsingSearchClass(Main.java:434)
at org.hibernate.testing.Main.main(Main.java:43)

first mistake:
must be



Instead I would use a more object oriented style:

Thanks, that helped quite a bit. I was wondering if you could point me in the right directions from my previous post above,


One last thing, I have not done db work in quite a while, but if I did a search for all Companys that had employees with name = "joe"

and the db had these values, I get 3 records returned as a result. How do I prevent record 1 from being returned in my query results twice?

company Table
cId + lId
0 1 ( record 1 )
0 2 ( record 2 )

employee Table
cId Id ssn name
0 1 1 joe
0 2 2 joe
0 1 3 joe


I have to search my db from fields from a qui, they match up 1 to 1. So I could search on the name joe above, but I only want the companies that have employees with a name of joe. How do I not get the same company returned multiple times? Should I need to do nested select statements?

Thanks, that helped quite a bit. I was wondering if you could point me in the right directions from my previous post above,


One last thing, I have not done db work in quite a while, but if I did a search for all Companys that had employees with name = "joe"

and the db had these values, I get 3 records returned as a result. How do I prevent record 1 from being returned in my query results twice?

company Table
cId + lId
0 1 ( record 1 )
0 2 ( record 2 )

employee Table
cId Id ssn name
0 1 1 joe
0 2 2 joe
0 1 3 joe


I have to search my db from fields from a qui, they match up 1 to 1. So I could search on the name joe above, but I only want the companies that have employees with a name of joe. How do I not get the same company returned multiple times? Should I need to do nested select statements?

Which query are you using?

Don't forget to rate my answers if they helped you, please.

Well, I suppose you need something like:

HQL


SQL

I have my query returning what I want except I have run into problems when querying on the field of type java.sql.timestamp.

The db field is of type timestamp(6) and displays a value of

12-APR-06 12.17.42.354000 PM

I have created my own timestamp to query this and its toString() method prints

2006-04-12 12:36:51.706

They have a different format, but am not sure if that is the problem. My sql statment for the timestamp query is listed below

" and c.startDate between '" + sc.getMinCompanyStartDate() + "' and '" + sc.getMaxCompanyStartDate() + "'" +

Hibernate translates this to:

and (company0_.startDate between '2006-04-12 12:36:51.706' and '2006-04-12 12:50:06.264')

Is the problem the formating 2006-04-12 compared to 12-APR-06 or is it something else?

Two things:

1. (from hibernate reference) As in JDBC, always replace non-constant values by "?". Never use string manipulation to bind a non-constant value in a query! Even better, consider using named parameters in queries.
2. Have a look at javadoc for SimpleDateFormat to construct a java.util.Date from a String.

I was hoping to use createCriteria for my query and ran into a problem when I tried to add the products to it. I get an exception stating

Exception in thread "main" org.hibernate.QueryException: could not resolve property: products of: org.hibernate.testing.Employee

How do I associate it back to the Company class, since a company has 1 - many employees and products.

Company

Set employees
Set products



List l = session.createCriteria(Company.class)
.createCriteria("employees")
.add(Expression.between("employeePk.ssn", sc.getMinSsn(), sc.getMaxSsn() ))
.createCriteria("products")
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
.list();

I think you are looking for something like

but I'm not sure about setResultTransformer.