Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: Hibernate 3.1

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using: Oracle 9

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

I have a rather complex query that I would LOVE to be able to use Criteria or even HQL for. I have tried several ways to get this to work, but without success. I am joining (and would like it to fetch) 8 tables. I need it to order by fields that are not in the parent class, but in these other tables. I also need to be able to limit the number of rows returned from one of the tables (also not the parent) as well as limit the number coming from the parent.

This is the detail on what I need the query to return. I have two queries at this time. The first query is listed at the end and works exactly how it should. I am able to get back 100 customers based on the fields in the where clause (from different tables).

The main issues are really in the second query. I need to be able to join these 8 tables (fetch) and order by different fields in these additional tables. First, when I use criteria I am able to get back all of the data (it does the fetch correctly), but I can only use order by with the fields in the Customer (parent) table. When I use HQL I am able to order by an of the fields in any of the tables, but even with a fetch join I get a lazy loading, no session found error once I leave the data tier. I am using JUnit for unit testing and it works perfectly in my tests, but when I integrate it into the rest of the application, it will not pull back all of the data. I will only fetch all of the data from the last table joined.

In addition to this limitation is the following business requirement. In the second query the where clause includes all of the Customer Id's from the first query. I need to be able to limit the amount of rows coming back for each customer for one of the tables. Specifically, I want a max of 50 Vehicles per Customer.

Here are the following queries that I have tried (Note: neither of them include the max of 50 vehicles per customer -- as I am at a loss of how to do that with either Criteria or HQL.

Criteria:
Session session = this.getSession();
boolean orderBy = true;
boolean conditionFound = false;
List customerDetail = new ArrayList();
StringBuilder sbQuery = new StringBuilder();

Criteria custDetailCriteria = session.createCriteria(Customer.class);
custDetailCriteria.setFetchMode("customerPostalAddressSet",
FetchMode.JOIN);
custDetailCriteria.setFetchMode(
"customerPostalAddressSet.postalAddress", FetchMode.JOIN);
custDetailCriteria.setFetchMode("customerEmailAddressSet",
FetchMode.JOIN);
custDetailCriteria.setFetchMode("customerPhoneNumberSet",
FetchMode.JOIN);
custDetailCriteria.setFetchMode("customerProductAssociationSet",
FetchMode.JOIN);
custDetailCriteria.setFetchMode(
"customerProductAssociationSet.serializedProduct",
FetchMode.JOIN);

Disjunction disjunction = Restrictions.disjunction();

Iterator iCustomer = customerList.iterator();
while (iCustomer.hasNext())
{
Customer cust = (Customer) iCustomer.next();
Criterion custId = Restrictions.eq("customerId", cust
.getCustomerId());
if (cust.getCustomerId() != null)
{
disjunction.add(custId);
custDetailCriteria.add(disjunction);
}
}
custDetailCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

custDetailCriteria.addOrder(Order.asc("firstNm"));
//Unable to addOrder for other tables (i.e. addressLine1 from the address table)

customerDetail = custDetailCriteria.list();

HQL:

sbQuery.append("select distinct c from Customer c ");
sbQuery.append("inner join fetch c.customerPostalAddressSet cpa ");
sbQuery.append("inner join fetch cpa.postalAddress pa ");
sbQuery.append("inner join fetch c.customerPhoneNumberSet cpn ");
sbQuery.append("inner join fetch c.customerEmailAddressSet cea ");
sbQuery.append("inner join fetch c.customerProductAssociationSet cpta ");
sbQuery.append("inner join fetch cpta.serializedProduct sp ");
sbQuery.append("where ");
sbQuery.append("cpn.phoneRole = 'PRIMARY' ");
sbQuery.append("and ");

Iterator iCustomer = customerList.iterator();
int counter = 0;
while (iCustomer.hasNext())
{
Customer cust = (Customer) iCustomer.next();
if (cust.getCustomerId() != null)
{
if (conditionFound)
{
sbQuery.append("or ");
}
sbQuery.append("c.customerId = ? ");
conditionFound = true;
}
}


* Add order by

if (ciDto.getOrderByList() != null && ciDto.getOrderByList().size() > 0)
{
boolean orderByFound = false;
Iterator iOrderBy = ciDto.getOrderByList().iterator();
while (iOrderBy.hasNext())
{
OrderByDto field = (OrderByDto) iOrderBy.next();
if (orderByFound)
{
sbQuery.append(", ");
sbQuery.append(field.getAlias());
sbQuery.append(".");
sbQuery.append(field.getField());
sbQuery.append(" asc");
orderByFound = true;
}
else
{
sbQuery.append("order by ");
sbQuery.append(field.getAlias());
sbQuery.append(".");
sbQuery.append(field.getField());
sbQuery.append(" asc");
orderByFound = true;
}
}
}

Query query = session.createQuery(sbQuery.toString());
Iterator cIterator = customerList.iterator();
int count = 0;
while (cIterator.hasNext())
{
Customer customer = (Customer) cIterator.next();
query.setString(count,
(customer.getCustomerId().toString()));
count++;
}
customerDetail = query.list();*/

Like I stated before I am able to perform the order by without an issue, but I am not able to get back all of the data needed (outside of the test case) and get lazy loading, no session found whenever I try to access the data outside of the data tier.

I really want to be able to use Criteria or HQL for this. Please, please, please help!



*******************************************************
Start first Query
*******************************************************
Session session = this.getSession();
List customerList = new ArrayList();
List returnList = new ArrayList();
boolean conditionFound = false;
StringBuilder sbQuery = new StringBuilder();

sbQuery.append("select distinct c from Customer c ");
sbQuery.append("inner join c.customerPostalAddressSet cpa ");
sbQuery.append("inner join cpa.postalAddress pa ");
sbQuery.append("inner join c.customerPhoneNumberSet cpn ");
sbQuery.append("inner join c.customerEmailAddressSet cea ");
sbQuery.append("inner join c.customerProductAssociationSet cpta ");
sbQuery.append("inner join cpta.serializedProduct sp ");
sbQuery.append("where ");

if (ciDto.getCustomerId() != null
&& !(ciDto.getCustomerId().equals("")))
{
sbQuery.append("c.customerId = :customerId "); //Should this be like? or =?
conditionFound = true;
}
if (ciDto.getOriginalFirstNm() != null
&& !(ciDto.getOriginalFirstNm().equals("")))
{
if (conditionFound)
{
sbQuery.append("and ");
}
sbQuery.append("c.firstNm like :firstNm ");
conditionFound = true;
}
if (ciDto.getOriginalLastNm() != null
&& !(ciDto.getOriginalLastNm().equals("")))
{
if (conditionFound)
{
sbQuery.append("and ");
}
sbQuery.append("c.lastNm like :lastNm ");
conditionFound = true;
}
if (ciDto.getOriginalOrganizationNm() != null
&& !(ciDto.getOriginalOrganizationNm().equals("")))
{
if (conditionFound)
{
sbQuery.append("and ");
}
sbQuery.append("c.organizationNm like :organizationNm ");
conditionFound = true;
}
if (ciDto.getAddressLine1() != null
&& !(ciDto.getAddressLine1().equals("")))
{
if (conditionFound)
{
sbQuery.append("and ");
}
sbQuery.append("pa.addressLine1 = :addressLine1 ");
conditionFound = true;
}
if (ciDto.getCity() != null && !(ciDto.getCity().equals("")))
{
if (conditionFound)
{
sbQuery.append("and ");
}
sbQuery.append("pa.city = :city ");
conditionFound = true;
}
if (ciDto.getStateProvinceCd() != null
&& !(ciDto.getStateProvinceCd().equals("")))
{
if (conditionFound)
{
sbQuery.append("and ");
}
sbQuery.append("pa.stateProvinceCd = :stateProvinceCd ");
conditionFound = true;
}
if (ciDto.getPostalCd() != null && !(ciDto.getPostalCd().equals("")))
{
if (conditionFound)
{
sbQuery.append("and ");
}
sbQuery.append("pa.postalCd = :postalCd ");
conditionFound = true;
}
if (ciDto.getVin() != null && !(ciDto.getVin().equals("")))
{
if (conditionFound)
{
sbQuery.append("and ");
}
sbQuery.append("sp.vin = :vin ");
conditionFound = true;
}
Query query = session.createQuery(sbQuery.toString());
if (ciDto.getCustomerId() != null && !ciDto.getCustomerId().equals(""))
{
query.setString("customerId",
(ciDto.getCustomerId().toUpperCase() + "%"));
}
if (ciDto.getOriginalFirstNm() != null
&& !(ciDto.getOriginalFirstNm().equals("")))
{
query.setString("firstNm", (ciDto.getOriginalFirstNm()
.toUpperCase() + "%"));
}
if (ciDto.getOriginalLastNm() != null
&& !(ciDto.getOriginalLastNm().equals("")))
{
query.setString("lastNm",
(ciDto.getOriginalLastNm().toUpperCase() + "%"));
}
if (ciDto.getOriginalOrganizationNm() != null
&& !(ciDto.getOriginalOrganizationNm().equals("")))
{
query.setString("organizationNm", (ciDto
.getOriginalOrganizationNm().toUpperCase() + "%"));
}
if (ciDto.getAddressLine1() != null
&& !(ciDto.getAddressLine1().equals("")))
{
query.setString("addressLine1", (ciDto.getAddressLine1()
.toUpperCase() + "%"));
}
if (ciDto.getCity() != null && !(ciDto.getCity().equals("")))
{
query.setString("city", (ciDto.getCity().toUpperCase() + "%"));
}
if (ciDto.getStateProvinceCd() != null
&& !(ciDto.getStateProvinceCd().equals("")))
{
query.setString("stateProvinceCd", (ciDto.getStateProvinceCd()
.toUpperCase() + "%"));
}
if (ciDto.getPostalCd() != null && !(ciDto.getPostalCd().equals("")))
{
query.setString("postalCd",
(ciDto.getPostalCd().toUpperCase() + "%"));
}
if (ciDto.getVin() != null && !(ciDto.getVin().equals("")))
{
query.setString("vin", (ciDto.getVin().toUpperCase() + "%"));
}

query.setMaxResults(100);
customerList = query.list();
*******************************************************
End first Query
*******************************************************
Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html