getting count of HQL query

[Jj Dire]

I'm trying to do pagination so I need to know the count but it seems such a difficult task to find the count of a query which deferences another table. The query is

              StringBuffer buf = new StringBuffer ();
              buf.append ("from Patient as pat ")
               .append (" left join fetch pat.patientInsurances pi ")
               .append ("where pi.insuranceNumber like :insuranceNumber ")
               .append ("or (lower(pat.lastName) like lower(:lastName) or lower(pat.firstName) like lower(:firstName) or substring(pat.dateOfBirth,0,11) like :dob ")
               .append ("or (pat.patientId like :id)) or (pat.chartNumber like :chartNumber) ")
               .append (" order by pat.lastName, pat.firstName ");

            Query query = hsession.createQuery (buf.toString ());
            query.setString ("lastName", QueryUtil.replaceWildCard (searchCriteria));
            query.setString ("firstName", QueryUtil.replaceWildCard (searchCriteria));
            query.setString ("dob", QueryUtil.replaceWildCard (searchCriteria)); // date of birth.
            query.setString ("insuranceNumber", QueryUtil.replaceWildCard (searchCriteria));
            query.setString ("id", QueryUtil.replaceWildCard (searchCriteria));
            query.setString ("chartNumber", QueryUtil.replaceWildCard (searchCriteria));  

Patient POJO is

  public class Patient  implements java.io.Serializable {


     private int id;
     private int version;
     private Provider provider;
     private String patientId;
     private String firstName;
     private String lastName;
     private String middleName;
     private Date dateOfBirth;
     private String gender;
     private String sex;
     private String chartNumber;
     private Set<PatientInsurance> patientInsurances = new HashSet<PatientInsurance>(0);
 

Here's my attempt at finding the count of this but no matter what it doesn't like the patient_insurance dereferning. I tried select count(*) way and this way and neither seems to work

     hsession = HibernateUtil.getSession (dataSource);

            Criteria sessionCriteria = hsession.createCriteria(Patient.class);
            
            Criteria searchCriteria = sessionCriteria            
            		.setProjection(Projections.rowCount() )
//            searchCriteria.createAlias("patientInsurances", "pi");
            				.add( Restrictions.disjunction());
            searchCriteria.add( Restrictions.ilike("lastName",  QueryUtil.replaceWildCard (criteria.toUpperCase ())));
   			searchCriteria.add( Restrictions.ilike("firstName",  QueryUtil.replaceWildCard (criteria.toUpperCase ())));
            searchCriteria.add( Restrictions.ilike("chartNumber",  QueryUtil.replaceWildCard (criteria.toUpperCase ())));
            				
            SimpleDateFormat formatter = new SimpleDateFormat ("yyyy-mm-dd");
            formatter.setTimeZone (TimeZone.getDefault ());
            Date date = null;
            try {
				date = formatter.parse (criteria);
			} catch (ParseException e) {}
			if(date != null)
				searchCriteria.add( Restrictions.ilike("dateOfBirth", date));

			Integer id = null;
			try {
				id = Integer.valueOf(criteria);
			}
			catch(NumberFormatException nfe){}
			
			if(id != null)
				searchCriteria.add( Restrictions.ilike("id",  id));
			
	          if(criteria != null && criteria.length() > 0)
	            {
	            	searchCriteria.createAlias("patientInsurances", "pi")
	//	            		.add(Restrictions.eqProperty("id", "pi.patient.id"))
	            		.add( Restrictions.disjunction()
	            				.add(Restrictions.ilike("pi.insuranceNumber", QueryUtil.replaceWildCard (criteria.toUpperCase ()))));
	            	
	            }
 

I am totally lost and wondering what's an easy way just to get a count of a relatively simple query.

When doing select count(*) of the above query I get the "the owner fetched association was not present in the select list" error.

Thanks in advance.

[Jj Dire]

found solution by using createAlias

[Javid Jamae]

You should be able to do this with a "select count" query. What are you trying to get a count of exactly? Show the select query and the table structure (DDL).

[Jj Dire]

the query is shown in my original post. If I just add select count(*) to the first query shown above, hibernate will complain. I've managed to do them all using projections.
every member is a string except date and id (date and integer).

[Paul Sturrock]

Hurric hurric" please check your private messages.