PartitionKey support in find method in entitymanager

Hibernate ORM
1

I have below entity relationship (1:1)

Contact <—>ContactCustomField
Contact <—> ContactCustomField2.

All the entitites have account_id as partition_key. When we do em.find(SalesContact.class, id) the query generated does not have account_id filter in where clause condition.

Query generated

    select
        sc1_0.id,
        sc1_0.account_id,
        ccf1_0.id,
        ccf1_0.account_id,
        ccf1_0.contact_id,
        ccf2_0.id,
        ccf2_0.account_id,
        ccf2_0.contact_id,
        sc1_0.first_name,
        sc1_0.last_name 
    from
        contacts sc1_0 
    left join
        contact_custom_field2 ccf1_0 
            on sc1_0.account_id=ccf1_0.account_id 
            and sc1_0.id=ccf1_0.contact_id 
    left join
        contact_custom_fields ccf2_0 
            on sc1_0.account_id=ccf2_0.account_id 
            and sc1_0.id=ccf2_0.contact_id 
    where
        sc1_0.id=?

If i use criteria API to construct both id/account_id filter number of queries generated are more.

  • Loads contact using id/account_id

  • Loads contact custom field by explicitly fetching

  • Loads contact custom field 2 by explicitly fetching

  • Loads contact custom fields by joining contact / contact_custom_field2 / contact_custom_field

Hibernate: 
    select
        sc1_0.id,
        sc1_0.account_id,
        sc1_0.first_name,
        sc1_0.last_name 
    from
        contacts sc1_0 
    where
        sc1_0.id=? 
        and sc1_0.account_id=?
Hibernate: 
    select
        ccf1_0.id,
        ccf1_0.account_id,
        ccf1_0.contact_id 
    from
        contact_custom_field2 ccf1_0 
    where
        (
            ccf1_0.account_id, ccf1_0.contact_id
        ) in ((?, ?))
Hibernate: 
    select
        sc1_0.id,
        sc1_0.account_id,
        ccf1_0.id,
        ccf1_0.account_id,
        ccf1_0.contact_id,
        ccf2_0.id,
        ccf2_0.account_id,
        ccf2_0.contact_id,
        sc1_0.first_name,
        sc1_0.last_name 
    from
        contacts sc1_0 
    left join
        contact_custom_field2 ccf1_0 
            on sc1_0.account_id=ccf1_0.account_id 
            and sc1_0.id=ccf1_0.contact_id 
    left join
        contact_custom_fields ccf2_0 
            on sc1_0.account_id=ccf2_0.account_id 
            and sc1_0.id=ccf2_0.contact_id 
    where
        (
            sc1_0.account_id, sc1_0.id
        ) in ((?, ?))
Hibernate: 
    select
        ccf1_0.id,
        ccf1_0.account_id,
        ccf1_0.contact_id 
    from
        contact_custom_fields ccf1_0 
    where
        (
            ccf1_0.account_id, ccf1_0.contact_id
        ) in ((?, ?))

@mbladel @beikov

2

All the entitites have account_id as partition_key. When we do em.find(SalesContact.class, id) the query generated does not have account_id filter in where clause condition.

Think about it, where should Hibernate get this account id from that you want to filter by? If you always know this account id for every query, why don’t you make it part of the primary key by using an embedded id?

3

Thank you for the response. My question is regarding why there are more queries when using criteria API. The one to one associations loaded seperately also via Join.

4

You didn’t share the entity model or the query that you are doing, but note that a query will never add joins for associations that are marked as EAGER. Instead it will issue a separate select statement.

5

Below are the entities:

Contact

@Table(name = "contacts")
	@Entity
	public static class SalesContact
		implements Serializable {

		public SalesContact(){}

		@Column(name = "first_name")
		private String firstName;

		@Column(name = "last_name")
		private String lastName;

		@PartitionKey
		@Column(name = "account_id", nullable = false)
		private Long accountId;

		@Id
		@Column(name = "id")
		@GeneratedValue(strategy = GenerationType.IDENTITY)
		private Long id;

		@OneToOne(mappedBy = "contact", cascade = CascadeType.ALL)
		private ContactCustomField contactCustomFields = new ContactCustomField();

		@OneToOne(mappedBy = "contact", cascade = CascadeType.ALL)
		private ContactCustomField2 contactCustomField2 = new ContactCustomField2();

	}

ContactCustomField2

@Table(name = "contact_custom_field2")
	@Entity
	public static class ContactCustomField2{
		@Id
		@Column(name = "id")
		@GeneratedValue(strategy = GenerationType.IDENTITY)
		private Long id;

		public ContactCustomField2(){

		}

		@OneToOne(fetch = FetchType.LAZY)
		@JoinColumn(name = "contact_id", referencedColumnName = "id", nullable = false)
		@JoinColumn(name = "account_id", referencedColumnName = "account_id", nullable = false)
		private SalesContact contact;

	}

ContactCustomField

	@Table(name = "contact_custom_fields")
	@Entity
	public static class ContactCustomField{
		@Id
		@Column(name = "id")
		@GeneratedValue(strategy = GenerationType.IDENTITY)
		private Long id;

		public ContactCustomField(){

		}

		@OneToOne(fetch = FetchType.LAZY)
		@JoinColumn(name = "contact_id", referencedColumnName = "id", nullable = false)
		@JoinColumn(name = "account_id", referencedColumnName = "account_id", nullable = false)
		private SalesContact contact;

	}

Criteria Query:

CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<SalesContact> query = cb.createQuery(SalesContact.class);
		Root<SalesContact> root = query.from(SalesContact.class);

		// Define the predicates for the two attributes
		Predicate idPredicate = cb.equal(root.get("id"), id);
		Predicate accountIdPredicate = cb.equal(root.get("accountId"), accountId);

		// Combine the predicates using 'and' to form the final condition
		Predicate finalPredicate = cb.and(idPredicate, accountIdPredicate);

		// Add the final condition to the query
		query.where(finalPredicate);

		// Execute the query and return the result (if found)
		return em.createQuery(query).getSingleResult();
6

My id is a auto generated one. Embedded ids does not work well with Auto generated ids.

7

These associations are EAGER, so Hibernate will load them through a separate select statement. If you want the associations to be fetched through a join, you will have to fetch them in your criteria query:

root.fetch("contactCustomFields");
root.fetch("contactCustomField2");

My id is a auto generated one. Embedded ids does not work well with Auto generated ids.

You could use an id-class instead to workaround this current limitation.

	@Table(name = "contacts")
	@Entity
	@IdClass(SalesContactId.class)
	public static class SalesContact
		@Id
		@Column(name = "account_id", nullable = false)
		private Long accountId;

		@Id
		@Column(name = "id", nullable = false)
		@GeneratedValue(strategy = GenerationType.IDENTITY)
		private Long id;
	}
	public static class SalesContactId
		private Long accountId;
		private Long id;
	}
8

This works perfectly. Thank you!

This one throws below error with IdClass

org.hibernate.id.IdentifierGenerationException: Identity generation isn’t supported for composite ids
at org.hibernate.mapping.Component$ValueGenerationPlan.execute(Component.java:666)

9

Did you try the latest Hibernate version already?

10

Its with “6.3.0.CR1”

11

In that case, you can follow the issue [HHH-9662] - Hibernate JIRA and [HHH-15074] - Hibernate JIRA for progress on this topic.

12

https://hibernate.atlassian.net/browse/HHH-16855
Isnt this already a known issue.