Criteria Query does not return any results

samuels · **Joined:** Wed May 28, 2008 7:18 am **Posts:** 6

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

Hibernate version: 3.2.5 ga

Mapping documents:
Snippet generated from xdoclet:

Code:

 

<hibernate-mapping>
  <class lazy="false" table="order_status" name="com.siim.blueocean.view.objects.CompletedAgainstScheduled">
    <id  name="id" type="long" column="id">
      <meta inherit="true" attribute="id">ID</meta>
      <generator class="native"/>
    </id>
    <property name="hospitalId" column="hospital_id" >
      <meta inherit="true" attribute="hospitalId">Hospital Id</meta>
    </property>
    <property name="modalityType" column="modality_type" >
      <meta inherit="true" attribute="modalityType">Modality Type</meta>
    </property>
    <property name="completedTimestamp" column="completed_ts"  formula="case when completed_ts != '0000-00-00 00:00:00' then completed_ts else null end"/>
    <property name="scheduledTimestamp" column="scheduled_ts" />
  </class>
</hibernate-mapping>

Name and version of the database you are using: MySQL 5.0

The generated SQL (show_sql=true):

Code:

select count(this_.scheduled_ts) as y0_, count(case when this_.completed_ts is not null then this_.completed_ts else null end) as y1_, this_.modality_type as y2_ from order_status this_ where this_.modality_typ
e in (?, ?, ?, ?, ?, ?, ?, ?) and this_.scheduled_ts>=? and this_.scheduled_ts<? and this_.hospital_id=? group by this_.modality_type

This query when executed at the database works as expected when parameters are passed. However, does not return anything during program execution.

Code:

public class CompletedAgainstScheduled extends BaseObject {
    /**
     * 
     */
    private static final long serialVersionUID = 6470860867871739372L;
    private Long id;
    private String hospitalId;
    private String modalityType;
    private Date completedTimestamp;
    private Date scheduledTimestamp;
    private int completed;
    private int scheduled;

    /**
     * @hibernate.meta attribute="id" value="ID"
     * @hibernate.id type="long" column="id" generator-class="native"
     * @return
     */
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }

    /**
     * @hibernate.meta attribute="hospitalId" value="Hospital Id"
     * @hibernate.property column="hospital_id"
     * @return
     */
    public String getHospitalId() {
        return hospitalId;
    }
    public void setHospitalId(String hospitalId) {
        this.hospitalId = hospitalId;
    }

    /**
     * @hibernate.meta attribute="modalityType" value="Modality Type"
     * @hibernate.property column="modality_type"
     * @return
     */
    public String getModalityType() {
        return modalityType;
    }
    public void setModalityType(String modalityType) {
        this.modalityType = modalityType;
    }

    /**
     * get and set completed orders
     * @return
     */
    public int getCompleted() {
        return completed;
    }
    public void setCompleted(int completed) {
        this.completed = completed;
    }

    /**
     * get and set scheduled orders
     * @return
     */
    public int getScheduled() {
        return scheduled;
    }
    public void setScheduled(int scheduled) {
        this.scheduled = scheduled;
    }

    /**
     * get and set completed orders
     * @hibernate.property column="completed_ts" formula="case when completed_ts != '0000-00-00 00:00:00' then completed_ts else null end"
     * @return
     */
    public Date getCompletedTimestamp() {
        return completedTimestamp;
    }
    public void setCompletedTimestamp(Date completedTimestamp) {
        this.completedTimestamp = completedTimestamp;
    }

    /**
     * get and set scheduled orders
     * @hibernate.property column="scheduled_ts"
     * @return
     */
    public Date getScheduledTimestamp() {
        return scheduledTimestamp;
    }
    public void setScheduledTimestamp(Date scheduledTimestamp) {
        this.scheduledTimestamp = scheduledTimestamp;
    }
}

Criteria generation code:

Code:

            Criteria criteria = getSession().createCriteria(CompletedAgainstScheduled.class)
                .setProjection(
                    Projections.projectionList()
                    .add(Projections.count("scheduledTimestamp"))
                    .add(Projections.count("completedTimestamp"))
                    .add(Projections.groupProperty("modalityType"))
                    ).add(Restrictions.in("modalityType", modArr))
                    .add(Restrictions.ge("scheduledTimestamp", new Timestamp(c.getTimeInMillis())))
                    .add(Restrictions.lt("scheduledTimestamp", new Timestamp(cd.getTimeInMillis())))
                    .add(Restrictions.eq("hospitalId",hospitalId));

            List<CompletedAgainstScheduled> l = criteria.list();

The count of schedued_timestamp and complete_timestamp should stored in the completed and scheduled attributes.

I have tried debugging the entire code but not able to figure out what the problem is. It should be a very minor stuff but not sure.

Any tips/help would be appreciated.

Thanks
Samuel

samuels · **Joined:** Wed May 28, 2008 7:18 am **Posts:** 6

I know it's not professional for me to post this reply or demand any help as you guys are working and are involved with something very important. But,
I have hit wall and have no clue how to resolve this issue. Can anyone please help me out? It's quite important for me to get this done.

samuels · **Joined:** Wed May 28, 2008 7:18 am **Posts:** 6

yipee!!! got it working..

Cameron McKenzie · **Posted:** Mon Jul 07, 2008 9:04 am

Don't be selfish! Let us know what the trick was. It'll help out other people running into the same problem.

sapatel · **Joined:** Thu Jul 10, 2008 12:47 pm **Posts:** 1

I have same problem and not able to figure out what is the cause.

loumaus · **Joined:** Tue Jul 15, 2008 1:39 pm **Posts:** 4

running into the same problem .. perhaps someone can help

this is the dao:

/**
* @see BookmarkDao#createNewFavoriteList()
*/
public FavoriteList createNewFavoriteList(final int pageSize) {

FavoriteListEntity fle = new FavoriteListEntity();
fle.setCreated(new Date());
fle.setState(FavoriteListState.DISABLED);
ht.persist(fle);

// select u.url, u.id, count(b.id)
// from url u, bookmark b
// where b.url=url
// and b.status=ONLINE
// group by u.url, u.id

List<BookmarkEntity> l = ht.executeFind(new HibernateCallback(){
public Object doInHibernate(Session session){
Criteria criteria = session.createCriteria(BookmarkEntity.class)
.setProjection(
Projections.projectionList()
.add(Projections.count("id").as("count"))
.add(Projections.groupProperty("url")))
.add(Expression.eq("state", BookmarkState.ONLINE));

return criteria.list();
}
});

This is the excerpt from the entity:
@Entity
@Table(name="sb_url")
public class URLEntity implements Serializable {

@Id
@GeneratedValue(generator="UrlSeq")
@javax.persistence.SequenceGenerator(name="UrlSeq",sequenceName="URL_SEQ", allocationSize=1)
private Long id;

@Column
private String url;

@Column
private Date created;

@OneToMany(mappedBy="url")
private Set<BookmarkEntity> bookmarks;

@Transient
private int count;

What do i want to achieve ???

The query should return a list of UrlEntity objects where the count is filled with the amount of matching bookmarks.

This query returns classes of type java.lang.Object instead of my UrlEntity.

If i remove the add(Projections.count("id").as("count")) from the criteria I the right classes are returned as part of the list (but of course not having the right context).

any idea ???