The Source for Java Technology Collaboration
User: Password:
Register | Login help    

Search

Online Books:
java.net on MarkMail:

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 >> Blogs      Databases      Java Enterprise      
Comments
Comments are listed in date ascending order (oldest first)

Your second point

Submitted by grabity on Tue, 2009-09-08 07: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

Submitted by felipegaucho on Tue, 2009-09-08 07: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..

UUIDs?

Submitted by fabriziogiudici on Thu, 2009-09-10 00: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 first point is ambiguous

Submitted by sle on Thu, 2009-09-17 05: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.




 

  
Feedback  | FAQ  | Terms of Use
Privacy  |  Trademarks | Site Map
 
Your use of this web site or any of its content or software indicates your agreement to be bound by these Terms of Participation.
 
Copyright © 2010, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Collabnet Logo Cognisync Communities
Powered by Sun Microsystems, Inc.,
CollabNet and Cognisync