-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: Querying history tables with criteria
PostPosted: Tue Jul 20, 2004 11:26 am 
Beginner
Beginner

Joined: Sun Mar 28, 2004 9:01 am
Posts: 21
Hello,

I have the following table structure:

CREATE TABLE MEPPBX (
ID numeric PRIMARY KEY NOT NULL,
PHAR numeric NOT NULL,
BOX numeric NOT NULL,
BEGINN date NOT NULL,
END date NULL,
GRND numeric NULL,
FOREIGN KEY (PHAR) REFERENCES MEPP(ID),
FOREIGN KEY (BOX) REFERENCES MEBOX(ID));

create table MEBOX(
ID NUMBER(22) PRIMARY KEY not null,
BOX VARCHAR2(3) not null,
VERSION NUMBER(22))

CREATE TABLE MEPP (
ID numeric PRIMARY KEY NOT NULL,
);

The MEPPBX is the table that connects MEBOX with MEPP. Additionally it holds the beginn and end date where the MEBOX is valid for the MEPP.

No I want to find out the MEPP where the MEBOX has changed to MEBOX.BOx='TEST' within the last month!

Is this possible using a criteria query or either a HQL?

Thx's a lot for any help! Rene


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jul 20, 2004 11:42 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
No, it is not possible to write a HQL query unless you have existing Hibernate classes and mappings and can show them to us.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jul 20, 2004 12:01 pm 
Beginner
Beginner

Joined: Sun Mar 28, 2004 9:01 am
Posts: 21
... sorry Gavin here are the classes including xdoclet definitions. The id itself is in the AbstractModel. Thanks a lot for that quick help!!!!

/*
* @hibernate.class
* table="MEBOX"
*/
public class Box extends AbstractModel implements Serializable {
private String code;
private String status;
private String kurzbeschreibung;
private String langbeschreibung;
/**
* @hibernate.property column = "BOX" not-null = "true" length = "3"
*/
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
}

/*
* @hibernate.class
* table="MEPP"
*/
public class Packung extends AbstractModel implements Serializable {
private Collection boxCollection;
/**
* @return Returns the box.
* @hibernate.set inverse = "true" cascade = "save-update" order-by = "LABE"
* @hibernate.collection-key column = "ID"
* @hibernate.collection-one-to-many class = "at.sozvers.eko.model.PackungBox"
*/
public Collection getBoxCollection() {
return boxCollection;
}
public void setBoxCollection(Collection box) {
this.boxCollection = box;
}
}


/*
* @hibernate.class
* table="MEPPBX"
*/
public class PackungBox extends AbstractModel implements Serializable {
private Date datumBeginn;
private Date datumEnde;
private Aufnahmegrund aufnahmegrund;
private Box box;
private Packung packung;

/**
* @hibernate.property column = "LABE" not-null = "true"
*/
public Date getDatumBeginn() {
return datumBeginn;
}
public void setDatumBeginn(Date datumBE) {
this.datumBeginn = datumBE;
}
/**
* @hibernate.property column = "LAEN"
*/
public Date getDatumEnde() {
return datumEnde;
}
public void setDatumEnde(Date datumEN) {
this.datumEnde = datumEN;
}
/**
* @hibernate.one-to-one
* column = "PHAR"
* not-null = "true"
* class = "at.sozvers.eko.model.Packung"
*/
public Packung getPackung() {
return packung;
}
public void setPackung(Packung packung) {
this.packung = packung;
}
/**
* @hibernate.many-to-one
* column = "BOX"
* not-null = "true"
* class = "at.sozvers.eko.model.Box"
*/
public Box getBox() {
return box;
}
public void setBox(Box box) {
this.box = box;
}
}


I tried the following query but did not succeed (looking for all boxes that changed from something to 'N' within a specified time periode):

Criteria boxCollection = session.createCriteria(Packung.class)
.createCriteria("boxCollection");

boxCollection.add(
Expression.and(Expression.isNotNull("datumEnde"),
Expression.between("datumEnde", von, bis))
);

boxCollection
.createAlias("box", "box")
.add(
Expression.and(
Expression.and(
Expression.isNull("datumEnde"),
Expression.between("datumBeginn", von, bis)
),
Expression.eq("box.code", "N")
)
);
packungList = boxCollection.list();


Top
 Profile  
 
 Post subject: expecting 'elements' or 'indices' after
PostPosted: Wed Jul 21, 2004 2:28 am 
Beginner
Beginner

Joined: Sun Mar 28, 2004 9:01 am
Posts: 21
... seems that I'm getting closer to the result now:

I found out that I need a subselect for the query, so I have to use HQL. The following HQL should fit my needs:

FROM at.sozvers.eko.model.Packung p
WHERE p.boxCollection.box.box = 'N'
AND p.boxCollection.datumBeginn BETWEEN :datumVon AND :datumBis
AND p.boxCollection.datumEnde IS NULL
AND p IN
(FROM p.boxCollection pBox WHERE pBox.datumEnde IS NOT NULL)

The problem is, that I now encounter a QueryException saying the following:

expecting 'elements' or 'indices' after: box

What does this mean?

Thx Rene


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jul 21, 2004 3:42 am 
Beginner
Beginner

Joined: Sun Mar 28, 2004 9:01 am
Posts: 21
... now I got it:

FROM at.sozvers.eko.model.Packung p
JOIN FETCH p.boxCollection pBox
JOIN FETCH pBox.box box
WHERE box.code = 'N'
AND pBox.datumBeginn BETWEEN :datumVon AND :datumBis
AND pBox.datumEnde IS NULL
AND p IN (
SELECT pBox.packung FROM at.sozvers.eko.model.PackungBox pBox WHERE pBox.datumEnde IS NOT NULL)

The problem was the mapping of Packung:

wrong:
* @hibernate.collection-key
* column = "ID"
correct:
* @hibernate.collection-key
* column = "PHAR"


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.