How to join entities through multiple (secondary) tables?


#1

Hi!

Can anyone help in this problem?

I have entities for table_action and table_list. I want to have in table_action entity a list of table_list elements. I cannot change the database model and I have to use hibernate.

TableAction.java :

@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinTable(name = "table_action2list", joinColumns = {
        @JoinColumn(name = "OBJID") }, inverseJoinColumns = {
                @JoinColumn(name = "list_objid", referencedColumnName = "objid", nullable = false, insertable = false, updatable = false),
                @JoinColumn(name = "list_type", referencedColumnName = "list_type", nullable = false, insertable = false, updatable = false),
                @JoinColumn(name = "list_kind", referencedColumnName = "list_kind", nullable = false, insertable = false, updatable = false) })
private Set<TableList> tableList = new HashSet<TableList>();

This doesn`t work because i cannot join table_action2plan. This is the sql which works corrently:

select rl.* 
from table_list rl
join table_action2list arc on arc.LIST_OBJID = rl.OBJID and arc.LIST_KIND = rl.LIST_KIND and arc.LIST_TYPE = rl.LIST_TYPE
join table_action2plan ac on ac.OBJID = arc.action2plan_objid 
join table_action ta on ta.OBJID = ac.ACTION_OBJID

table_action2plan and table_action2list are secondary tables in my entity but i am free to create entities for them if needed.
Is this possible in Hibernate and do you have ideas on how to solve the problem?

Thanks a lot.!


#2

Sure it can be implemented with Hibernate. You need to add the entities you have mapped so far, and we can later discuss about what exactly you are not able to map.


#3

@vlad Thank you for the quick response. I have added the two entities but it is still not clear how to map the tables.
Now I have entity for table_action, table_list, table_action2plan and table_action2list.
Now I want to have in TableAction a list of TableList. But how can I join the two tables table_action2plan and table_action2list in TableAction? As far as I know, I cannot have multiple @JoinTable annotation for the same object. I mean for

private Set<TableList> tableList = new HashSet<TableList>();

#4

I don’t see the code you added for the entities so far. After you add it, I’ll review it and see where the problem is.


#5

Sorry, this is the entity code:

TableAction2Plan.java

	@Id
	@Column(name = "OBJID")
	private Long objId;
	
	@Basic
	@Column(name = "ACTION_OBJID")
	private Integer actionObjId;
	
	@Basic
	@Column(name = "PLAN_OBJID")
	private Integer planId;

getters setters...

And for TableAction2List.java

	@Id
	@Column(name = "OBJID")
	private Long objId;

	@Basic
	@Column(name = "ACTION2PLAN_OBJID")
	private Integer action2Planbjid;

	@Basic
	@Column(name = "LIST_OBJID")
	private Integer listObjid;

	@Basic
	@Column(name = "LIST_TYPE")
	private Integer listType;

	@Basic
	@Column(name = "LIST_KIND")
	private Integer listKind;

getters/setters..

For TableList:
@Basic
@Column(name = “LIST_OBJID”)
private Integer listObjid;

@Basic
@Column(name = "LIST_TYPE")
private Integer listType;

@Basic
@Column(name = "LIST_KIND")
private Integer listKind;

In TableAction.java

@Id
@Column(name = "OBJID")
private Long objId;

… some other columns which are not related to other tables and the join

@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinTable(name = "table_action2list", joinColumns = {
        @JoinColumn(name = "OBJID"), ,@JoinColumn(name="ACTION2PLAN_OBJID") }, inverseJoinColumns = {
                @JoinColumn(name = "list_objid", referencedColumnName = "objid", nullable = false, insertable = false, updatable = false),
                @JoinColumn(name = "list_type", referencedColumnName = "list_type", nullable = false, insertable = false, updatable = false),
                @JoinColumn(name = "list_kind", referencedColumnName = "list_kind", nullable = false, insertable = false, updatable = false) })
private Set<TableList> tableList = new HashSet<TableList>();

#6

It’s hard to understand the mappings. You need to add the entire class with the mappings.


#7

Ok, here are the entities. Notice, that I am trying to solve the problem with secondary tables but I am open to any working solution.

@Entity
@Table(name="table_action")

@SecondaryTables({
  @SecondaryTable(name="table_action2list", pkJoinColumns={
      @PrimaryKeyJoinColumn(name="ACTION_OBJID", referencedColumnName="objid")})
	  
public class TableAction {


	@Id
	@Column(name = "OBJID")
	private Long objId;

	// TODO how to join action2Planobjid column from table_action2list?
	
	@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
	@JoinTable(name = "table_action2list", joinColumns = {
        @JoinColumn(name = "OBJID") }, inverseJoinColumns = {
                @JoinColumn(name = "list_objid", referencedColumnName = "objid", nullable = false, insertable = false, updatable = false),
                @JoinColumn(name = "list_type", referencedColumnName = "list_type", nullable = false, insertable = false, updatable = false),
                @JoinColumn(name = "list_kind", referencedColumnName = "list_kind", nullable = false, insertable = false, updatable = false) })
	private Set<TableList> tableList = new HashSet<TableList>();

	//getters and setters omitted
}
@Entity
@Table(name="table_action2list")

public class TableAction2List {


	@Id
	@Column(name = "OBJID")
	private Long objId;

	@Basic
	@Column(name = "ACTION2PLAN_OBJID")
	private Integer action2Planobjid;

	@Basic
	@Column(name = "LIST_OBJID")
	private Integer listObjid;

	@Basic
	@Column(name = "LIST_TYPE")
	private Integer listType;

	@Basic
	@Column(name = "LIST_KIND")
	private Integer listKind;

	//getters and setters omitted
}
@Entity
@Table(name="table_action2plan")

public class TableAction2Plan {


	@Id
	@Column(name = "OBJID")
	private Long objId;
	
	@Basic
	@Column(name = "ACTION_OBJID")
	private Integer actionObjId;
	
	@Basic
	@Column(name = "PLAN_OBJID")
	private Integer planId;

	//getters and setters omitted
}
@Entity
@Table(name="table_list")

public class TableList {


	@Id
	@Column(name = "OBJID")
	private Long objId;
	
	@Basic
	@Column(name = "LIST_TYPE")
	private Integer listType;

	@Basic
	@Column(name = "LIST_KIND")
	private Integer listKind;

	//getters and setters omitted
}

Thanks


#8

Try it like this:

@Entity
@Table(name="table_action")	  
public class TableAction {


	@Id
	@Column(name = "OBJID")
	private Long objId;
	
}

@Entity
@Table(name="table_action2plan")
public class TableAction2Plan {


	@Id
	@Column(name = "OBJID")
	private Long objId;
	
	@ManyToOne
	@JoinColumn(name = "ACTION_OBJID")
	private TableAction action;
	
	@Basic
	@Column(name = "PLAN_OBJID")
	private Integer planId;

	//getters and setters omitted
}

@Entity
@Table(name="table_action2list")
public class TableAction2List {


	@Id
	@Column(name = "OBJID")
	private Long objId;

	@ManyToOne
	@JoinColumn(name = "ACTION2PLAN_OBJID")
	private TableAction2Plan action2Plan;

	@Basic
	@Column(name = "LIST_OBJID")
	private Integer listObjid;

	@Basic
	@Column(name = "LIST_TYPE")
	private Integer listType;

	@Basic
	@Column(name = "LIST_KIND")
	private Integer listKind;
	
	@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, mappedBy="actionList")
	private List<TableList> lists = new ArrayList<>();

	//getters and setters omitted
}

@Entity
@Table(name="table_list")
public class TableList {


	@Id
	@Column(name = "OBJID")
	private Long objId;
	
	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumns{
		@JoinColumn(name = "objid", referencedColumnName = "objid", nullable = false, insertable = false, updatable = false),
		@JoinColumn(name = "list_objid", referencedColumnName = "list_objid", nullable = false, insertable = false, updatable = false),
		@JoinColumn(name = "list_kind", referencedColumnName = "list_kind", nullable = false, insertable = false, updatable = false)
	}
	private TableAction2List actionList;

	//getters and setters omitted
}

#9

Thanks @vlad

I will try the solution you proposed. However, I need the table list in TableAction. Because I have to use a HQL to select the table list elements from table_action. Can you please modify it in that way?


#10

You can use a HQL with this mapping too. The HQL does not drive an entity mapping.


#11

Yes, but I want to use “select a from TableAction a INNER JOIN a.tableList” and get the table list. I don`t want to select from TableList. It should automatically list all lists with action plans. Can this be achieved?
Thank you


#12

TableAction does not contain that relation, so you cannot do that. You need to join existing relations which follow the DB schema layout.


#13

Thank you @vlad for your help!


#14

You are very welcome.


#15

This may works.

You need to annotate your entity with JPA’s Table and SecondaryTable annotations and provide the names of the first and second table as the value of the name parameters.
You need to annotate each attribute which you want to map to the secondary table with a @Column annotation and set the name of the secondary table as the value of the table attribute.

vidmate