How to join entities through multiple (secondary) tables?

KirshiYin · November 19, 2018, 11:19am

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.

dbmodell

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.!

vlad · November 19, 2018, 11:38am

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.

KirshiYin · November 19, 2018, 1:01pm

@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>();

vlad · November 19, 2018, 1:47pm

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.

KirshiYin · November 19, 2018, 2:07pm

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>();

vlad · November 19, 2018, 4:02pm

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

KirshiYin · November 19, 2018, 7:46pm

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

vlad · November 19, 2018, 8:15pm

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
}

KirshiYin · November 20, 2018, 9:48am

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?

vlad · November 20, 2018, 10:31am

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

KirshiYin · November 20, 2018, 10:39am

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

vlad · November 20, 2018, 12:13pm

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

KirshiYin · November 27, 2018, 12:48pm

Thank you @vlad for your help!

vlad · November 27, 2018, 12:54pm

You are very welcome.

Topic		Replies	Views
join two persisted entities with relationships ManyToOne bidirectional and jointable Hibernate ORM	1	355	May 18, 2020
Cannot insert null into table.column ORA-01400 Hibernate ORM	7	8246	December 15, 2018
How to join more tables to the same id (FK)? Hibernate ORM	3	788	May 12, 2021
JoinColumn on multiple fields not works Hibernate ORM	1	327	February 25, 2021
Join Multiple Entity N+1 Problem Hibernate ORM	0	390	February 20, 2020

How to join entities through multiple (secondary) tables?

Related Topics