Skip to main content

Long or int to declare JPA numeric primary keys?

Posted by felipegaucho on September 7, 2009 at 9:12 AM EDT

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 >>
Blog Links >>

Comments

your first point is ambiguous

by sle - 2009-09-17 06:43

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?

by fabriziogiudici - 2009-09-10 01:55

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

by grabity - 2009-09-08 08:34

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

by felipegaucho - 2009-09-08 08:52

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..