Home » Eclipse Projects » EclipseLink » NamedEntityGraph hint does not use table joins(NamedEntityGraph hint does not use table joins)
NamedEntityGraph hint does not use table joins [message #1828104] |
Mon, 01 June 2020 19:58 |
Peter Coppens Messages: 1 Registered: June 2020 |
Junior Member |
|
|
Hello
I am struggling to get my head around the idea/usage/behaviour of NamedEntityGraph
Bottom line is I am trying to have EclipseLink generate 1 SQL with a join between two tables
Code fragments looks like so (Kotlin)
@NamedEntityGraphs(*[
NamedEntityGraph(
name = "user"
),
NamedEntityGraph(
name = "user-role-properties",
attributeNodes = [
NamedAttributeNode("name") ,
NamedAttributeNode("userRoles", subgraph = "roleproperties"),
NamedAttributeNode("userProperties", subgraph = "propertiesproperties")],
subgraphs = [
NamedSubgraph (name ="roleproperties", attributeNodes = [NamedAttributeNode("roleType")]),
NamedSubgraph (name ="propertiesproperties", attributeNodes = [NamedAttributeNode("properties")])
]
),
NamedEntityGraph(
name = "user-role",
attributeNodes = [
NamedAttributeNode("name") ,
NamedAttributeNode("userRoles", subgraph = "roleproperties")] ,
subgraphs = [
NamedSubgraph (name="roleproperties", attributeNodes = [NamedAttributeNode("roleType")])]
),
NamedEntityGraph(
name = "user-properties",
attributeNodes = [
NamedAttributeNode("name") ,
NamedAttributeNode("userProperties", subgraph = "propertiesproperties2")
],
subgraphs = [
javax.persistence.NamedSubgraph(name = "propertiesproperties2", attributeNodes = [javax.persistence.NamedAttributeNode("properties")])
]
)
])
@Entity
@Table(name = "public.user")
open class UserEntity(
@Id @Column(name="ID") var id: UUID?,
@Column (name="lock_version") var lockVersion: Int = 0,
var name: String?,
var state: String?,
var email: String?,
@Column (name="created_at") var createdAt: Timestamp?,
@Column (name="updated_at") var updatedAt: Timestamp?,
@Column (name="email_verified_at") var emailVerifiedAt: Timestamp?,
@Column (name="last_graph_downloaded_at") var lastGraphDownloadedAt: Timestamp?,
@OneToMany(fetch= FetchType.LAZY) @JoinColumn(name="user_id")
var userRoles: Set<UserRoleEntity>,
@OneToOne(fetch= FetchType.LAZY, optional = true ) @JoinColumn(name="id", insertable=false, updatable=false)
var userProperties: UserPropertiesEntity?
)
@Entity
@Table(name = "user_role")
open class UserRoleEntity(
@Id
var id: UUID?,
var user_id: UUID?,
@Column(name="role_type")
var roleType: String?
)
@Entity
@Table(name = "user_properties")
open class UserPropertiesEntity(
@Id @Column(name="user_id")
val userId: UUID?,
@Column(name="updated_by") var updatedBy: UUID?,
@Column(name="updated_at") var updatedAt: Timestamp?,
@Column(name="properties") var properties: String?
)
...
val graph = entityManager.getEntityGraph("user-properties")
val properties: MutableMap<String, Any> = HashMap()
properties["javax.persistence.fetchgraph"] = graph
val ue = entityManager.find(UserEntity::class.java, UUID.fromString(id), properties)
Static weaving seems successful
Running this code results in 4 SQL's
SELECT ID, NAME FROM public.user WHERE (ID = ?)
SELECT user_id, properties FROM user_properties WHERE (user_id = ?)
SELECT user_id, properties, updated_at, updated_by FROM user_properties WHERE (user_id = ?)
SELECT ID, created_at, EMAIL, email_verified_at, last_graph_downloaded_at, lock_version, NAME, STATE, updated_at FROM public.user WHERE (ID = ?)
If I do not set the NamedEntityGraph property I am only getting 2 SQL's (4th and 3rd)
So ideally, I want one SQL joining user and user_properties table
Anyone any tips?
Certainly appreciated!
Peter
|
|
|
Re: NamedEntityGraph hint does not use table joins [message #1828207 is a reply to message #1828104] |
Wed, 03 June 2020 23:50 |
Chris Delahunt Messages: 1389 Registered: July 2009 |
Senior Member |
|
|
EntityGraph is a fetch graph, telling what needs to be all pre-fetched and loaded , but doesn't specify 'how' to fetch things- that is completely left up to the mappings. If you want joining so it is all done in a single query, you'd need to specify fetch join/batching query hints, or add fetch join and BatchFetch annotations to your model. Without additional settings, you can't get it all read in with a single query.
As for getting the 4 queries when using your named graph: your named graph is defining properties, which explains the 1st and 2nd SQL statements. The remaining 3rd and 4th would get caused when touching anything unfetched in the returned object graph, as JPA treats them all as lazy, and accessing them forces fetching the entire object. Your code is likely triggering something on both these objects, forcing the additional SQL to be issued.
To prove it is your code and not some internal mechanism of EclipseLink triggering the 3rd and 4th statement, the easiest way I can think of is to remove one of the missing columns from the DB (I use views over my tables and just rename the table back when done). This will cause an exception, and the stack will show exactly where the 3rd (or 4th) statement is being issued from. If it is from the em.find call somehow, we'd need the stack trace to help narrow down why.
|
|
|
Goto Forum:
Current Time: Thu Dec 26 14:46:23 GMT 2024
Powered by FUDForum. Page generated in 0.03580 seconds
|