CODEWITHSUNDEEP
javamysqlhibernatejoin
sensitive_piece_of_horseflesh
sensitive_piece_of_horseflesh

Reputation: 977

hibernate - fetching data from two tables using criteria

I have a table DocMaster which has one to many mapping with DocMovement Table. DocMovement table has columns that contain sending and receiving user id with date of sending and receiving the document as separate columns.

//Doc_Mvmnt
    @ManyToOne
    @JoinColumn(name = "BARCODE", nullable=false)
    public Doc_Master doc_master;

    @ManyToOne
    @JoinColumn(name="RECIPIENT_DETAIL")
    private User_Details recipient_detail;

    @ManyToOne
    @JoinColumn(name="SENDER_DETAIL")
    private User_Details sender_detail;

    @Temporal(TemporalType.DATE)
    //@Type(type="date")
    @Column(name="RECIEVING_DATE")
    private Date recieving_date;

    @Temporal(TemporalType.DATE)
    @Column(name="SENDING_DATE")
    private Date sending_date;

//Doc_Master
    @Column(name = "BARCODE", nullable=false, unique=true)
    private String barcode;

    @ManyToOne
    @JoinColumn(name = "DEPT_ID")
    private Department department;

//Department
    @OneToMany(mappedBy= "department")
    private List<Doc_Master> documents = new ArrayList<>();

The problem I am facing is :

The user enters the department and range of dates. I want to display all the document movements withing this range of dates for that department.I am unable to write a criteria so that I can fetch the data I want.

Criteria criteria = session.createCriteria(Doc_Master.class);
criteria.add(Restrictions.eq("department", deptId));
criteria.add(Restrictions.between(/* */, fromDate, toDate)); // what to do here
List<Doc_Master> documents = (List<Doc_Master>)criteria.list();

Please help !!!

Upvotes: 0

Views: 4585

Answers (2)

Aravinthan K
Aravinthan K

Reputation: 1861

You need to Use createAlias. 

Criteria criteria = session.createCriteria(Doc_Master.class,"docMaster");
.createAlias("docMaster.docMovement","docMovement")
criteria.add(Restrictions.eq("docMaster.department",deptId));
criteria.add(Restrictions.between("docMovement.recieving_date", fromDate, toDate));

you need to set documentMovement object in documentMaster.

where

docMovement is object name in docMaster

Upvotes: 1

Ori Dar
Ori Dar

Reputation: 19000

The user enters the department and range of dates. I want to display all the document movements withing this range of dates for that department.

First of all, you should query Doc_Mvmnt and not Doc_Master (you should avoid underscores for class names):

Criteria criteria = session.createCriteria(Doc_Mvmnt.class);

Note that using your current mapping, you can't navigate from master to movement. It's a uni-directional association from movement to master.

Next, you are not querying for a department, but a department id:

criteria.add(Restrictions.eq("doc_master.department.id", deptId));

// what to do here

criteria.add(Restrictions.or(
    Restrictions.between("sending_date", fromDate, toDate),
    Restrictions.between("recieving_date", fromDate, toDate)));

Upvotes: 1

Related Questions