Skip to main content

Long or int to declare JPA numeric primary keys?

Posted by felipegaucho on September 7, 2009 at 6:12 AM PDT

I have just two arguments to avoid using long as type of the primary keys of JPA Entities:

  1. Integers IDs allow tables with ~2 billion records. Yes, you read well: max(Integer) = 2,147,483,647
  2. the JPA pagination methods only accepts Integers Query setFirstResult(int startPosition) an  Query setMaxResults(int maxResult)

Summary:only adopt data types long for your tables primary keys if your tables will have ore than 2 billion records or if you are not planning to use the default pagination features of JPA.

Related Topics >>

Comments

your first point is ambiguous

Requiring ids > to max(Integer) doesn't mean having tables with more than 2 billions records. One can have only a few rows in a table but very big id values.

I have tables where data lives and dies often. Table is not very big but unique ids are growing fast. They haven't reached 2 billions yet but I guess, with time and increase of business, they will get there.

UUIDs?

I took the habit since 1.5 years to use strings for keys, that I populate with UUIDs. I've never run a performance comparison (both for JPA and the database) to see whether there's a big difference with integers. BTW UUIDs don't fit either in 64 bits.

Your second point

Wouldn't your second point only be valid if you're anticipating returning and paginating query results with more than 2 billion records? I can't think of a single situation where it would be a good idea to provide pagination of 2 billion results. I would be hard pressed to come up with many situations that use conventional RDBMS where JPA is more appropriate that would even require having a query return 2 billion records. That seems like a really good way to lock up your database from a memory, processor, I/O, and locking perspective.

agree

I completely agree with you grabity ... actually, the first argument itself is enough, but my attention for this little details came from the parameters of the pagination... then I included this little detail also.

A programmer dealing with billion of records will have many other problems before the unecessary autoboxing instructions..