How To Join Multiple table Using hibernate Criteria where entity relationship is Many to Many

Question

i have the following three tables with task and options in Many To Many relationship

task table

@Entity
@Table(name = "TASK")
public class Task implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "TASK_ID")
    private int task_ID;

    @Column(name = "TASK_NAME")
    private String task_Name;

    @OneToMany(mappedBy="task")
    private Set<Task_Options> task_options;

options table

@Entity
@Table(name = "OPTION")
public class Options implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "OPTION_ID")
    private int option_ID;

    @Column(name = "OPTION_VALUE")
    private String option_Value;

    @OneToMany(mappedBy = "options")
    private Set<Task_Options> task_options;

task_options table

@Entity
@Table(name = "TASK_OPTIONS")
public class Task_Options implements Serializable {

    @EmbeddedId
    private pk pk;

    @ManyToOne
    private Task task;

    @ManyToOne
    private Options options;

    public Task getTask() {
        return task;
    }

    public void setTask(Task task) {
        this.task = task;
    }

    public Options getOptions() {
        return options;
    }

    public void setOptions(Options options) {
        this.options = options;
    }

    public pk getPk() {
        return pk;
    }

    public void setPk(pk pk) {
        this.pk = pk;
    }



    /*Inner Class for Composite primary key*/
    @Embeddable
    public static class pk implements Serializable {

        private int Task_ID;        
        private int Option_ID;

        public int getTask_ID() {
            return Task_ID;
        }

        public void setTask_ID(int Task_ID) {
            this.Task_ID = Task_ID;
        }

        public int getOption_ID() {
            return Option_ID;
        }

        public void setOption_ID(int Option_ID) {
            this.Option_ID = Option_ID;
        }                

        public pk(int Task_ID, int Option_ID) {
            this.Task_ID = Task_ID;
            this.Option_ID = Option_ID;
        }

        @Override
        public int hashCode() {
            int hash = 7;
            hash = 47 * hash + this.Task_ID;
            hash = 47 * hash + this.Option_ID;
            return hash;
        }

        @Override
        public boolean equals(Object obj) {
            if (this == obj) {
                return true;
            }
            if (obj == null) {
                return false;
            }
            if (getClass() != obj.getClass()) {
                return false;
            }
            final pk other = (pk) obj;
            if (this.Task_ID != other.Task_ID) {
                return false;
            }
            if (this.Option_ID != other.Option_ID) {
                return false;
            }
            return true;
        }

    }

}

i need to write Hibernate criteria to run the following query:

 > select OPTION_VALUE from [dbo].[OPTION] AS op  INNER JOIN
    > [dbo].[TASK_OPTIONS] AS TSOP ON op.OPTION_ID = TSOP.OPTION_ID  INNER
    > JOIN [dbo].[TASK] AS tsk ON tsk.TASK_ID = TSOP.TASK_ID Where
    > Tsk.TASK_ID = 2

any Suggestions ?

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

I think this will do it :

Criteria criteria = session.createCriteria(Task_Options.class);
criteria.setFetchMode("options", FetchMode.EAGER);
criteria.setFetchMode("task", FetchMode.EAGER);
criteria.createAlias("task", "tsk")
criteria.add(Restrictions.eq("tsk.TASK_ID", 2));

see this answer to understand the usage of FetchMode.EAGER

This will select Task_Options you can get the Options from it and from Options get option_Value

How To Join Multiple table Using hibernate Criteria where entity relationship is Many to Many

1 Answers1