I have a MySQL database where (most) tables are divided by the TENANT_ID column. Each table also has an ID field that uses AUTO_INCREMENT and is therefore unique to all partitions. The primary key of the database is a combination (ID, TENANT_ID) due to the requirement of MySQL to have part of the primary key section column.
In my Java code, I only displayed the ID column with @Id annotation. This was mainly to avoid a lot of problems around compound keys in Hibernate. The problem I am facing now is that most Hibernate SQL statements use only the identifier column. For example, the UPDATE statement generated by Hibernate will read as
UPDATE object SET value = ? WHERE ID = ?
However, since this query excludes any predicate on TENANT_ID, it does not fully exploit partitioning and will have to scan each section until it finds an identifier. I would like the generated query to give:
UPDATE object SET value = ? WHERE ID = ? AND TENANT_ID = ?
My question is whether there is an easy way to do this without resorting to composite keys in JPA, since I know that many people impede their use.
source share