I have a MySQL database where (most) tables are partitioned on a column TENANT_ID. Each table also has an ID field which uses AUTO_INCREMENT and is therefore unique across all partitions. The database primary key is a combination (ID, TENANT_ID) due to MySQL's requirement to have the partition column part of the primary key.

In my Java code I have mapped only the ID column with the @Id annotation. This was mostly to avoid the many problems around composite keys in Hibernate. The problem I am facing now is that most SQL statements generated by Hibernate only use the ID column. For example, an UPDATE statement generated by Hibernate would read as

UPDATE object SET value = ? WHERE ID = ?

However, since this query excludes any predicate on TENANT_ID, it does not take full advantage of the partitioning and will need to scan every partition until it finds the ID. I would like for the generated query to yield:

UPDATE object SET value = ? WHERE ID = ? AND TENANT_ID = ?

My question is whether or not there is an easy way to do this without having to resort to composite keys in JPA as I know many people discourage their use.