hql - elements function
in [Java Programming]
|
From: Peter Horlock on 3 Dec 2009 12:35 Jean-Baptiste Nizet wrote: > I don't use XML to write my mappings, but annotations, so I can't say > if there is a problem or not with your mapping. > But you keep saying "It's not working". If you're not more descriptive > about the problem you have, we won't be able to help you. What's the > problem ? > 1. You get an exception when executing the HQL query. If it's the > case, what's the message stack trace of the exception Well, so far I didn't post the exception, cause it won't tell you a lot. Anyway, here it comes: >nested exception is org.hibernate.HibernateException: Error in named >query: myQueryName Thanks, Peter
From: Jean-Baptiste Nizet on 4 Dec 2009 03:22 On 3 déc, 18:35, Peter Horlock <peter.horl...(a)googlemail.com> wrote: > Jean-Baptiste Nizet wrote: > > I don't use XML to write my mappings, but annotations, so I can't say > > if there is a problem or not with your mapping. > > But you keep saying "It's not working". If you're not more descriptive > > about the problem you have, we won't be able to help you. What's the > > problem ? > > 1. You get an exception when executing the HQL query. If it's the > > case, what's the message stack trace of the exception > > Well, so far I didn't post the exception, cause it won't tell you a lot. > Anyway, here it comes: > > >nested exception is org.hibernate.HibernateException: Error in named > >query: myQueryName > Indeed, but what's the stack trace? What's the query? What's the code of your entities?
From: Peter Horlock on 4 Dec 2009 08:06 Hi JB, here's as much as I could gather for you, hopefully this will help you to help me! ;-) The business concept: A book has one or more stories A Story is published in one or more books A Story has one ore more story components A book has one ore more readers A reader reads one or more books ----------- What I want to get: 1. Get me all Stories read by a certain reader, of the last year, of a certain book status which have not been set to "invalid". 2. Get me all Stories of a certain book, of the last year, of a certain book status which have not been set to "invalid". ------------ The hibernate hql queries I got at the moment: <query name="dwh.story.getByBookId">from Story as story join story.books as book with book.id=? where book.status=? and story.invalid=? and extract(year from sysdate) - extract(year from story.creation_date) = 0 order by story.creation_date desc </query> <query name="dwh.story.getByReaderId">from Story as story inner join story.books as book inner join book.readers as reader with reader.readerId = ? where book.status=? and story.invalid=? and extract(year from sysdate) - extract(year from story.creation_date) = 0 order by story.creation_date desc</query> ------------------------------------------------------ The Oracle Create Statements: CREATE TABLE STORY ( ID NUMBER NOT NULL, STORY_NAME VARCHAR2(128) NOT NULL, CREATION_DATE DATE DEFAULT sysdate NOT NULL, INVALID NUMBER(1,0) DEFAULT 0 NOT NULL, PRIMARY KEY(ID) ) CREATE TABLE BOOK ( ID NUMBER NOT NULL, STATUS NUMBER NULL, PUBLISHERID NUMBER NULL, BOOKNUMBER VARCHAR2(15) NULL, AGIOREDUZIERUNGPROZENT NUMBER NULL, WRITTEN_ON DATE NULL, WRITTEN_BY VARCHAR2(50) NULL, PRINTED DATE NULL CONSTRAINT PK_BOOK PRIMARY KEY(ID) ) ; ALTER TABLE BOOK ADD ( CONSTRAINT FK_BOOK_PUBLISHER FOREIGN KEY(PUBLISHERID) REFERENCES PUBLISHER(ID) NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE ) ; ALTER TABLE BOOK ADD ( CONSTRAINT FK_BOOK_BOOKSTATUS FOREIGN KEY(STATUS) REFERENCES BOOKSTATUS(ID) NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE ) ; CREATE TABLE BOOK_READER ( ID NUMBER NOT NULL, BOOKID NUMBER NOT NULL, READER_ID NUMBER NOT NULL, ROLE NUMBER NOT NULL, CONSTRAINT PK_BOOK_READER PRIMARY KEY(ID) ) ; ALTER TABLE BOOK_READER ADD ( CONSTRAINT FK_BOOK_READER_BOOK FOREIGN KEY(BOOKID) REFERENCES BOOK(ID) NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE ) ; ALTER TABLE BOOK_READER ADD ( CONSTRAINT FK_BOOK_ROLE FOREIGN KEY(ROLE) REFERENCES BOOKROLE(ID) NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE ) ; CREATE TABLE READER ( READER_ID NUMBER(10,0) NOT NULL, FIRSTNAME VARCHAR2(50) NULL, LASTNAME VARCHAR2(100) NULL, STREET VARCHAR2(200) NULL, ZIP VARCHAR2(100) NULL, CITY VARCHAR2(200) NULL, CONSTRAINT PK_READER PRIMARY KEY(READER_ID) ) ; CREATE TABLE _STORY_COMPONENT ( STORY_ID NUMBER NOT NULL, COMPONENT_NAME VARCHAR2(128) NOT NULL, PRIMARY KEY(STORY_ID,COMPONENT_NAME) ) ; ALTER TABLE _STORY_COMPONENT ADD ( CONSTRAINT _REP_CMPNT_FK1 FOREIGN KEY(STORY_ID) REFERENCES _STORY(ID) NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE ) ; CREATE TABLE NM_BOOK__STORY ( ID NUMBER NOT NULL, BOOK_ID NUMBER NOT NULL, STORY_ID NUMBER NOT NULL ) ; <class name="StoryComponent" table="_STORY_COMPONENT"> <composite-id> <key-property name="storyId" column="STORY_ID" /> <key-property name="componentName" column="COMPONENT_NAME" type="ComponentEnumUserType" /> </composite-id> </class> <class name="NMBookStory" table="NM_BOOK__STORY"> <id name="id" column="ID"> <generator class="sequence"> <param name="sequence">NM_BOOK__STORY_SEQ</param> </generator> </id> <property name="storyId" column="STORY_ID" /> <property name="bookId" column="BOOK_ID" /> </class> <class name="Story" table="_STORY"> <id name="id" column="ID"> <generator class="sequence"> <param name="sequence">STORY_SEQ</param> </generator> </id> <set name="books" table="NM_BOOK__STORY" cascade="all" lazy="false" order-by="STORY_ID asc"> <key column="STORY_ID" /> <many-to-many column="BOOK_ID" not-found="ignore" class="Book" /> </set> <property name="storyName" column="STORY_NAME" /> <property name="creationDate" column="CREATION_DATE" type="java.util.Date" /> <property name="invalid" column="INVALID" /> <set name="storyComponents" inverse="true" cascade="all" lazy="false"> <key column="STORY_ID" /> <one-to-many class="StoryComponent" /> </set> </class> <class name="Book" table="BOOK"> <id name="id" column="id"> <generator class="sequence"> <param name="sequence">book_sequence</param> </generator> </id> <property name="status" type="BookStatus" column="status" /> <many-to-one name="publisher" class="Publisher" column="publisherid" lazy="false" /> <property name="booknumber" type="string" column="booknumber" /> <set name="readers" table="BookReaders" cascade="all" lazy="false" order-by="role asc"> <key column="bookid" /> <many-to-many column="readerId" not-found="ignore" class="Reader" /> </set> <property name="writtenOn" column="WRITTEN_ON" type="java.util.Date" /> <property name="writtenBy" column="WRITTEN_BY" /> </class> <class name="Reader" mutable="false" table="READER" > <id name="readerId" column="READER_ID"> <generator class="native" /> </id> <property name="firstname" column="FIRSTNAME" /> <property name="lastname" column="LASTNAME" /> <property name="street" column="STREET" /> <property name="zipcode" column="ZIP" /> <property name="city" column="CITY" /> <many-to-one name="readerAddition" class="ReaderAddition" column="readerId" lazy="false" /> </class> ----------------------------------------------------------------- public class Story { private Long id; private Date creationDate = new Date(); private Set<Book> books; private String storyName; private boolean invalid; + other attributes, + public getters and setters, + hashCode & equals and toString method } ----------------------------------------------------------------- public class StoryComponent implements Serializable { private Long storyId; private ComponentEnum componentName; + other attributes, + public getters and setters, + hashCode & equals and toString method } ----------------------------------------------------------------- public class NMBookStory implements Serializable { private long id; private long bookId; private long storyId; + other attributes, + public getters and setters, + hashCode & equals and toString method } public class Book implements Serializable { private Long id; private BookStatus status; private Publisher publisher; private String booknumber; private Date writtenOn; private String writtenBy; + other attributes, + public getters and setters, + hashCode & equals and toString method } public class BookReader implements Serializable { private Long id; private Long readerId; private BookRole role; private Long bookId; + other attributes, + public getters and setters, + hashCode & equals and toString method } public class Reader implements Serializable { private Long readerId; private ReaderZusatz readerZusatz; private String city; private String firstname; private String lastname; private String street; private String zipcode; + other attributes, + public getters and setters, + hashCode & equals and toString method } ------------------ Thanks in advance, Peter
From: Jean-Baptiste Nizet on 5 Dec 2009 07:45 A I said earlier, I'm not familiar with the XML syntax for Hibernate mappings. But some things are wrong in what you sent. There is a many-to-many relationship between books and stories. You have two choices here 1. You use a pure join table between the two tables (with only foreign keys, and without any ID column). In this case, Hibernate will automatically insert new entries in the join table when you add books to a story. The other operations on the books collection will also be reflected in the database by Hibernate. But you must not map the join table with a Hibernate entity (i.e. the NMBookStory class must disappear). In this case, the join-talbe is transparent : it never appears in hibernate queries. 2. You map this many-to-many association with two one-to-many associations : one book has many NMBookStory instances, one story has many NMBookStory instances, and one NMBookStory has one book and one story. But the NMBookStory class must have a field of type Book and a field of type Story, not IDs. In this case, the NMBookStory class is not transparent, and you must use it in your Hibernate queries. The query I gave you earlier assume that you chose the first solution. For the many-to-many relationship between books and readers, it seems that this relationship has an additional characteristic : the role. In this case, you have no choice : you must map the join table with an entity (that you named BookReader). But the BookReader should have a field of type Book, and another of type Reader. Not two IDs. In short, you must model an object model, where objects are linked together directly. The ID of an entiy must only appear in this entity, and not anywhere else. I really suggest you read the Hibernate manual, because you missed some very important concepts. I'll assume you have the following entities public class Book { private long id; private Set<BookReader> bookReaders; // one-to-many, bidirectional // ... } public class Story { private long id; private Set<Book> books; // many-to-many, unidirectional private Set<StoryComponent> components; // one-to-many, bidirectional // ... } public class Reader { private long id; private Set<BookReader> bookReaders; // one-to-many, bidirectional // ... } public class BookReader { private long id; private Book book; // many-to-one, bidirectional private Reader reader; // many-to-one, bidirectional private int role; // ... } I suggest you use a surrogate ID for your StoryComponent entity, so you would have public class StoryComponent { private long id; private Story story; // many-to-one, bidirectional } For your first query : "Get me all Stories read by a certain reader, of the last year, of a certain book status which have not been set to "invalid". The query would look like this : select story from Story story inner join story.books book inner join book.bookReaders bookReader inner join bookReader.reader reader where reader.id = :readerId and book.status = :bookStatus and story.invalid = false and <constraint on the date> For your second query : "Get me all Stories of a certain book, of the last year, of a certain book status which have not been set to invalid" The query would look like this : select story from Story story inner join story.books book where book.id = :bookId and <constraint on the date> and book.status = :bookStatus and story.invalid = false JB. Peter Horlock a �crit : > Hi JB, > > here's as much as I could gather for you, > hopefully this will help you to help me! ;-) > > The business concept: > A book has one or more stories > A Story is published in one or more books > A Story has one ore more story components > A book has one ore more readers > A reader reads one or more books > ----------- > What I want to get: > 1. Get me all Stories read by a certain reader, of the last year, of a > certain book status which have not been set to "invalid". > 2. Get me all Stories of a certain book, of the last year, of a certain > book status which have not been set to "invalid". > ------------ > The hibernate hql queries I got at the moment: > > <query name="dwh.story.getByBookId">from Story as story join story.books > as book with book.id=? where book.status=? and story.invalid=? and > extract(year from sysdate) - extract(year from story.creation_date) = 0 > order by story.creation_date desc > </query> > <query name="dwh.story.getByReaderId">from Story as story inner join > story.books as book inner join book.readers as reader with reader.readerId > = ? where book.status=? and story.invalid=? and extract(year from sysdate) > - extract(year from story.creation_date) = 0 order by story.creation_date > desc</query> > > ------------------------------------------------------ > The Oracle Create Statements: > > CREATE TABLE STORY ( > ID NUMBER NOT NULL, > STORY_NAME VARCHAR2(128) NOT NULL, > CREATION_DATE DATE DEFAULT sysdate NOT NULL, > INVALID NUMBER(1,0) DEFAULT 0 NOT NULL, > PRIMARY KEY(ID) > ) > > > CREATE TABLE BOOK ( > ID NUMBER NOT NULL, > STATUS NUMBER NULL, > PUBLISHERID NUMBER NULL, > BOOKNUMBER VARCHAR2(15) NULL, > AGIOREDUZIERUNGPROZENT NUMBER NULL, > WRITTEN_ON DATE NULL, > WRITTEN_BY VARCHAR2(50) NULL, > PRINTED DATE NULL > CONSTRAINT PK_BOOK PRIMARY KEY(ID) > ) > ; > ALTER TABLE BOOK > ADD ( CONSTRAINT FK_BOOK_PUBLISHER > FOREIGN KEY(PUBLISHERID) > REFERENCES PUBLISHER(ID) > NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE ) > ; > ALTER TABLE BOOK > ADD ( CONSTRAINT FK_BOOK_BOOKSTATUS > FOREIGN KEY(STATUS) > REFERENCES BOOKSTATUS(ID) > NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE ) > ; > > > CREATE TABLE BOOK_READER ( > ID NUMBER NOT NULL, > BOOKID NUMBER NOT NULL, > READER_ID NUMBER NOT NULL, > ROLE NUMBER NOT NULL, > CONSTRAINT PK_BOOK_READER PRIMARY KEY(ID) > ) > ; > ALTER TABLE BOOK_READER > ADD ( CONSTRAINT FK_BOOK_READER_BOOK > FOREIGN KEY(BOOKID) > REFERENCES BOOK(ID) > NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE ) > ; > ALTER TABLE BOOK_READER > ADD ( CONSTRAINT FK_BOOK_ROLE > FOREIGN KEY(ROLE) > REFERENCES BOOKROLE(ID) > NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE ) > ; > > > > CREATE TABLE READER ( > READER_ID NUMBER(10,0) NOT NULL, > FIRSTNAME VARCHAR2(50) NULL, > LASTNAME VARCHAR2(100) NULL, > STREET VARCHAR2(200) NULL, > ZIP VARCHAR2(100) NULL, > CITY VARCHAR2(200) NULL, > CONSTRAINT PK_READER PRIMARY KEY(READER_ID) > ) > ; > > > CREATE TABLE _STORY_COMPONENT ( > STORY_ID NUMBER NOT NULL, > COMPONENT_NAME VARCHAR2(128) NOT NULL, > PRIMARY KEY(STORY_ID,COMPONENT_NAME) > ) > ; > ALTER TABLE _STORY_COMPONENT > ADD ( CONSTRAINT _REP_CMPNT_FK1 > FOREIGN KEY(STORY_ID) > REFERENCES _STORY(ID) > NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE ) > ; > > CREATE TABLE NM_BOOK__STORY ( > ID NUMBER NOT NULL, > BOOK_ID NUMBER NOT NULL, > STORY_ID NUMBER NOT NULL > ) > ; > > <class name="StoryComponent" > table="_STORY_COMPONENT"> > <composite-id> > <key-property name="storyId" column="STORY_ID" /> > <key-property name="componentName" column="COMPONENT_NAME" > type="ComponentEnumUserType" /> > </composite-id> > </class> > <class name="NMBookStory" > table="NM_BOOK__STORY"> > <id name="id" column="ID"> > <generator class="sequence"> > <param name="sequence">NM_BOOK__STORY_SEQ</param> > </generator> > </id> > <property name="storyId" column="STORY_ID" /> > <property name="bookId" column="BOOK_ID" /> > </class> > > <class name="Story" table="_STORY"> > <id name="id" column="ID"> > <generator class="sequence"> > <param name="sequence">STORY_SEQ</param> > </generator> > </id> > <set name="books" table="NM_BOOK__STORY" cascade="all" lazy="false" > order-by="STORY_ID asc"> > <key column="STORY_ID" /> > <many-to-many column="BOOK_ID" not-found="ignore" > class="Book" /> > </set> > <property name="storyName" column="STORY_NAME" /> > <property name="creationDate" column="CREATION_DATE" > type="java.util.Date" /> > <property name="invalid" column="INVALID" /> > <set name="storyComponents" inverse="true" cascade="all" lazy="false"> > <key column="STORY_ID" /> > <one-to-many class="StoryComponent" /> > </set> > </class> > > <class name="Book" table="BOOK"> > <id name="id" column="id"> > <generator class="sequence"> > <param name="sequence">book_sequence</param> > </generator> > </id> > <property name="status" type="BookStatus" column="status" /> > <many-to-one name="publisher" class="Publisher" > column="publisherid" lazy="false" /> > <property name="booknumber" type="string" column="booknumber" /> > <set name="readers" table="BookReaders" cascade="all" lazy="false" > order-by="role asc"> > <key column="bookid" /> > <many-to-many column="readerId" not-found="ignore" > class="Reader" /> > </set> > <property name="writtenOn" column="WRITTEN_ON" type="java.util.Date" /> > <property name="writtenBy" column="WRITTEN_BY" /> > > </class> > > <class name="Reader" mutable="false" > table="READER" > > <id name="readerId" column="READER_ID"> > <generator class="native" /> > </id> > <property name="firstname" column="FIRSTNAME" /> > <property name="lastname" column="LASTNAME" /> > <property name="street" column="STREET" /> > <property name="zipcode" column="ZIP" /> > <property name="city" column="CITY" /> > <many-to-one name="readerAddition" > class="ReaderAddition" column="readerId" lazy="false" /> > </class> > > ----------------------------------------------------------------- > public class Story > { > private Long id; > private Date creationDate = new Date(); > private Set<Book> books; > private String storyName; > private boolean invalid; > > + other attributes, + public getters and setters, + hashCode & equals and > toString method > } > ----------------------------------------------------------------- > public class StoryComponent implements Serializable > { > private Long storyId; > private ComponentEnum componentName; > > + other attributes, + public getters and setters, + hashCode & equals and > toString method > } > ----------------------------------------------------------------- > public class NMBookStory implements Serializable > { > private long id; > private long bookId; > private long storyId; > > + other attributes, + public getters and setters, + hashCode & equals and > toString method > } > public class Book implements Serializable > { > private Long id; > private BookStatus status; > private Publisher publisher; > private String booknumber; > private Date writtenOn; > private String writtenBy; > > + other attributes, + public getters and setters, + hashCode & equals and > toString method > } > public class BookReader implements Serializable > { > private Long id; > private Long readerId; > private BookRole role; > private Long bookId; > > + other attributes, + public getters and setters, + hashCode & equals and > toString method > } > public class Reader implements Serializable > { > > private Long readerId; > private ReaderZusatz readerZusatz; > private String city; > private String firstname; > private String lastname; > private String street; > private String zipcode; > > + other attributes, + public getters and setters, + hashCode & equals and > toString method > } > ------------------ > > > Thanks in advance, > > Peter
From: Peter Horlock on 7 Dec 2009 13:03
Hi JB, thanks for your great help so far! Now I managed to get the queries to run. This: > For your second query : "Get me all Stories of a certain book, of the > last year, of a certain book status which have not been set to invalid" > > The query would look like this : > select story from Story story > inner join story.books book > where book.id = :bookId > and <constraint on the date> > and book.status = :bookStatus > and story.invalid = false works perfectly. however, it seems that this one: > The query would look like this : > select story from Story story > inner join story.books book > inner join book.bookReaders bookReader > inner join bookReader.reader reader > where reader.id = :readerId > and book.status = :bookStatus > and story.invalid = false > and <constraint on the date> does need some improvement. What it currently does is, for every book it creates an entire Story Object, so instead of 1 Story Object that points to 6 different books it can be found in, I get 6 Story Objects EACH pointing to the same 6 different books. I know I could say "distinct", but I am not sure if that's the best way performance wise and so on, I guess it was better to change my query in some way, so that it won't retrieve duplicate data in the first place, and not to eliminate the duplicate data afterwards using the distinct keyword... Thanks in advance, Peter Jean-Baptiste Nizet wrote: > A I said earlier, I'm not familiar with the XML syntax for Hibernate > mappings. But some things are wrong in what you sent. > > There is a many-to-many relationship between books and stories. > You have two choices here > > 1. You use a pure join table between the two tables (with only foreign > keys, and without any ID column). In this case, Hibernate will > automatically insert new entries in the join table when you add books to > a story. The other operations on the books collection will also be > reflected in the database by Hibernate. But you must not map the join > table with a Hibernate entity (i.e. the NMBookStory class must > disappear). In this case, the join-talbe is transparent : it never > appears in hibernate queries. > > 2. You map this many-to-many association with two one-to-many > associations : one book has many NMBookStory instances, one story has > many NMBookStory instances, and one NMBookStory has one book and one > story. But the NMBookStory class must have a field of type Book and a > field of type Story, not IDs. In this case, the NMBookStory class is not > transparent, and you must use it in your Hibernate queries. > > The query I gave you earlier assume that you chose the first solution. > > For the many-to-many relationship between books and readers, it seems > that this relationship has an additional characteristic : the role. In > this case, you have no choice : you must map the join table with an > entity (that you named BookReader). But the BookReader should have a > field of type Book, and another of type Reader. Not two IDs. > > In short, you must model an object model, where objects are linked > together directly. The ID of an entiy must only appear in this entity, > and not anywhere else. I really suggest you read the Hibernate manual, > because you missed some very important concepts. > > I'll assume you have the following entities > > public class Book { > private long id; > private Set<BookReader> bookReaders; // one-to-many, bidirectional > // ... > } > > public class Story { > private long id; > private Set<Book> books; // many-to-many, unidirectional > private Set<StoryComponent> components; // one-to-many, bidirectional > // ... > } > > public class Reader { > private long id; > private Set<BookReader> bookReaders; // one-to-many, bidirectional > // ... > } > > public class BookReader { > private long id; > private Book book; // many-to-one, bidirectional > private Reader reader; // many-to-one, bidirectional > private int role; > // ... > } > > I suggest you use a surrogate ID for your StoryComponent entity, so you > would have > > public class StoryComponent { > private long id; > private Story story; // many-to-one, bidirectional > } > > > For your first query : "Get me all Stories read by a certain reader, of > the last year, of a certain book status which have not been set to > "invalid". > > The query would look like this : > select story from Story story > inner join story.books book > inner join book.bookReaders bookReader > inner join bookReader.reader reader > where reader.id = :readerId > and book.status = :bookStatus > and story.invalid = false > and <constraint on the date> > > For your second query : "Get me all Stories of a certain book, of the > last year, of a certain book status which have not been set to invalid" > > The query would look like this : > select story from Story story > inner join story.books book > where book.id = :bookId > and <constraint on the date> > and book.status = :bookStatus > and story.invalid = false > > JB. > > > Peter Horlock a �crit : >> Hi JB, >> >> here's as much as I could gather for you, >> hopefully this will help you to help me! ;-) >> >> The business concept: >> A book has one or more stories >> A Story is published in one or more books >> A Story has one ore more story components >> A book has one ore more readers >> A reader reads one or more books >> ----------- >> What I want to get: >> 1. Get me all Stories read by a certain reader, of the last year, of a >> certain book status which have not been set to "invalid". >> 2. Get me all Stories of a certain book, of the last year, of a certain >> book status which have not been set to "invalid". >> ------------ >> The hibernate hql queries I got at the moment: >> >> <query name="dwh.story.getByBookId">from Story as story join story.books >> as book with book.id=? where book.status=? and story.invalid=? and >> extract(year from sysdate) - extract(year from story.creation_date) = 0 >> order by story.creation_date desc >> </query> >> <query name="dwh.story.getByReaderId">from Story as story inner join >> story.books as book inner join book.readers as reader with >> reader.readerId >> = ? where book.status=? and story.invalid=? and extract(year from >> sysdate) >> - extract(year from story.creation_date) = 0 order by story.creation_date >> desc</query> >> >> ------------------------------------------------------ >> The Oracle Create Statements: >> >> CREATE TABLE STORY ( >> ID NUMBER NOT NULL, >> STORY_NAME VARCHAR2(128) NOT NULL, >> CREATION_DATE DATE DEFAULT sysdate NOT NULL, >> INVALID NUMBER(1,0) DEFAULT 0 NOT NULL, >> PRIMARY KEY(ID) >> ) >> >> >> CREATE TABLE BOOK ( >> ID NUMBER NOT NULL, >> STATUS NUMBER NULL, >> PUBLISHERID NUMBER NULL, >> BOOKNUMBER VARCHAR2(15) NULL, >> AGIOREDUZIERUNGPROZENT NUMBER NULL, >> WRITTEN_ON DATE NULL, >> WRITTEN_BY VARCHAR2(50) NULL, >> PRINTED DATE NULL >> CONSTRAINT PK_BOOK PRIMARY KEY(ID) >> ) >> ; >> ALTER TABLE BOOK >> ADD ( CONSTRAINT FK_BOOK_PUBLISHER >> FOREIGN KEY(PUBLISHERID) >> REFERENCES PUBLISHER(ID) >> NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE ) >> ; >> ALTER TABLE BOOK >> ADD ( CONSTRAINT FK_BOOK_BOOKSTATUS >> FOREIGN KEY(STATUS) >> REFERENCES BOOKSTATUS(ID) >> NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE ) >> ; >> >> >> CREATE TABLE BOOK_READER ( >> ID NUMBER NOT NULL, >> BOOKID NUMBER NOT NULL, >> READER_ID NUMBER NOT NULL, >> ROLE NUMBER NOT NULL, >> CONSTRAINT PK_BOOK_READER PRIMARY KEY(ID) >> ) >> ; >> ALTER TABLE BOOK_READER >> ADD ( CONSTRAINT FK_BOOK_READER_BOOK >> FOREIGN KEY(BOOKID) >> REFERENCES BOOK(ID) >> NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE ) >> ; >> ALTER TABLE BOOK_READER >> ADD ( CONSTRAINT FK_BOOK_ROLE >> FOREIGN KEY(ROLE) >> REFERENCES BOOKROLE(ID) >> NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE ) >> ; >> >> >> >> CREATE TABLE READER ( >> READER_ID NUMBER(10,0) NOT NULL, >> FIRSTNAME VARCHAR2(50) NULL, >> LASTNAME VARCHAR2(100) NULL, >> STREET VARCHAR2(200) NULL, >> ZIP VARCHAR2(100) NULL, >> CITY VARCHAR2(200) NULL, >> CONSTRAINT PK_READER PRIMARY KEY(READER_ID) >> ) >> ; >> >> >> CREATE TABLE _STORY_COMPONENT ( >> STORY_ID NUMBER NOT NULL, >> COMPONENT_NAME VARCHAR2(128) NOT NULL, >> PRIMARY KEY(STORY_ID,COMPONENT_NAME) >> ) >> ; >> ALTER TABLE _STORY_COMPONENT >> ADD ( CONSTRAINT _REP_CMPNT_FK1 >> FOREIGN KEY(STORY_ID) >> REFERENCES _STORY(ID) >> NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE ) >> ; >> >> CREATE TABLE NM_BOOK__STORY ( >> ID NUMBER NOT NULL, >> BOOK_ID NUMBER NOT NULL, >> STORY_ID NUMBER NOT NULL >> ) >> ; >> >> <class name="StoryComponent" >> table="_STORY_COMPONENT"> >> <composite-id> >> <key-property name="storyId" column="STORY_ID" /> >> <key-property name="componentName" column="COMPONENT_NAME" >> type="ComponentEnumUserType" /> >> </composite-id> >> </class> >> <class name="NMBookStory" >> table="NM_BOOK__STORY"> >> <id name="id" column="ID"> >> <generator class="sequence"> >> <param name="sequence">NM_BOOK__STORY_SEQ</param> >> </generator> >> </id> >> <property name="storyId" column="STORY_ID" /> >> <property name="bookId" column="BOOK_ID" /> >> </class> >> >> <class name="Story" table="_STORY"> >> <id name="id" column="ID"> >> <generator class="sequence"> >> <param name="sequence">STORY_SEQ</param> >> </generator> >> </id> >> <set name="books" table="NM_BOOK__STORY" cascade="all" >> lazy="false" >> order-by="STORY_ID asc"> >> <key column="STORY_ID" /> >> <many-to-many column="BOOK_ID" not-found="ignore" >> class="Book" /> >> </set> >> <property name="storyName" column="STORY_NAME" /> >> <property name="creationDate" column="CREATION_DATE" >> type="java.util.Date" /> >> <property name="invalid" column="INVALID" /> >> <set name="storyComponents" inverse="true" cascade="all" >> lazy="false"> >> <key column="STORY_ID" /> >> <one-to-many class="StoryComponent" /> >> </set> >> </class> >> >> <class name="Book" table="BOOK"> >> <id name="id" column="id"> >> <generator class="sequence"> >> <param name="sequence">book_sequence</param> >> </generator> >> </id> >> <property name="status" type="BookStatus" column="status" /> >> <many-to-one name="publisher" class="Publisher" >> column="publisherid" lazy="false" /> >> <property name="booknumber" type="string" column="booknumber" /> >> <set name="readers" table="BookReaders" cascade="all" >> lazy="false" >> order-by="role asc"> >> <key column="bookid" /> >> <many-to-many column="readerId" not-found="ignore" >> class="Reader" /> >> </set> >> <property name="writtenOn" column="WRITTEN_ON" >> type="java.util.Date" /> >> <property name="writtenBy" column="WRITTEN_BY" /> >> >> </class> >> >> <class name="Reader" mutable="false" >> table="READER" > >> <id name="readerId" column="READER_ID"> >> <generator class="native" /> >> </id> >> <property name="firstname" column="FIRSTNAME" /> >> <property name="lastname" column="LASTNAME" /> >> <property name="street" column="STREET" /> >> <property name="zipcode" column="ZIP" /> >> <property name="city" column="CITY" /> >> <many-to-one name="readerAddition" >> class="ReaderAddition" column="readerId" lazy="false" /> >> </class> >> >> ----------------------------------------------------------------- >> public class Story >> { >> private Long id; >> private Date creationDate = new Date(); >> private Set<Book> books; >> private String storyName; >> private boolean invalid; >> >> + other attributes, + public getters and setters, + hashCode & equals and >> toString method >> } >> ----------------------------------------------------------------- >> public class StoryComponent implements Serializable >> { >> private Long storyId; >> private ComponentEnum componentName; >> >> + other attributes, + public getters and setters, + hashCode & equals and >> toString method >> } >> ----------------------------------------------------------------- >> public class NMBookStory implements Serializable >> { >> private long id; >> private long bookId; >> private long storyId; >> >> + other attributes, + public getters and setters, + hashCode & equals and >> toString method >> } >> public class Book implements Serializable >> { >> private Long id; >> private BookStatus status; >> private Publisher publisher; >> private String booknumber; >> private Date writtenOn; >> private String writtenBy; >> >> + other attributes, + public getters and setters, + hashCode & equals and >> toString method >> } >> public class BookReader implements Serializable >> { >> private Long id; >> private Long readerId; >> private BookRole role; >> private Long bookId; >> >> + other attributes, + public getters and setters, + hashCode & equals and >> toString method >> } >> public class Reader implements Serializable >> { >> >> private Long readerId; >> private ReaderZusatz readerZusatz; >> private String city; >> private String firstname; >> private String lastname; >> private String street; >> private String zipcode; >> >> + other attributes, + public getters and setters, + hashCode & equals and >> toString method >> } >> ------------------ >> >> >> Thanks in advance, >> >> Peter |