4

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.

Improve this question
2
  • Do you really need these two columns (TENANT_ID and ID)? Couldn't you just drop the ID column altogether, and define TENANT_ID as the primary key? Commented Nov 14, 2012 at 11:05
  • Option 2: what about partitioning by ID instead of TENANT_ID? Commented Nov 14, 2012 at 11:08

1 Answer 1

Reset to default
0

You can use an embedded entity, for instance ObjectPK that encompasses the id and EntityId. than use @EmbeddedId to reference it from the Object entity.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.