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
