2013-03-30

UUID Values From JDBC to Postgres

I'm a big fan of UUIDs in general and especially for database record identifiers. If using a modern database such as Postgres that natively understands and stores the 128-bit value of a UUID, then you gain many benefits with the main cost being the double or quadruple storage space of the otherwise common 32-bit or 64-bit serial integer number.

Unfortunately, neither the SQL standard nor JDBC has seen fit to recognize UUIDs as a data type. So that means a second cost to using UUIDs in a database: Dragging those UUID values across the chasm of JDBC + SQL. One one end, we have Java which understands UUID as a built-in class (java.util.UUID). That's good. On the other end, we have Postgres which understands UUID as a native data type. That's good. The problem is in between.

The solution is that JDBC has a trap-door to handle data types unknown to SQL, called simply object. Let's look at an example, a snippet of Java code.

// Generate or obtain data to store in database.
java.util.UUID uuid = java.util.UUID.randomUUID(); // Generate a random UUID. 
String foodName = "Croissant";
// JDBC Prepared Statement.
PreparedStatement preparedStatement = conn.prepareStatement( "INSERT INTO food_ (pkey_, food_name_  ) VALUES (?,?)" );
int nthPlaceholder = 1; // 1-based counting (not an index).
preparedStatement.setObject( nthPlaceholder++, uuid ); 
preparedStatement.setString( nthPlaceholder++, foodName ); 
// Execute SQL.
if ( !( preparedStatement.executeUpdate() == 1 ) ) { 
  // If the SQL reports other than one row inserted…
  this.logger.error( "Failed to insert row into database." );
}

This code is simpler than you might expect. No need to transform the UUID as textual hex representation on the Java side. No need to cast the value on the Postgres side. By calling the ".setObject" method, and passing an actual UUID value, it all works.

Going the other direction, retrieving a UUID value from the database, call the getObject method and cast the result to a java.util.UUID object. Like so, where `rs` is a ResultSet:

java.util.UUID uuid = ( java.util.UUID ) rs.getObject( "uuid_" );

By the way, if you need to pass an empty or 'nil' value for a UUID, generate a UUID with all bits of all zero values. This is commonly understood to mean "no value" in UUID-speak. One way to generate such a UUID is:

java.util.UUID emptyUuid = new java.util.UUID( 0L, 0L );

105 comments:

  1. Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
    Selenium training in Chennai
    Selenium training in Bangalore
    Selenium training in Pune
    Selenium Online training

    ReplyDelete
  2. I am sure this post has helped me save many hours of browsing other related posts just to find what I was looking for. Many thanks!
    I am sure this post has helped me save many hours of browsing other related posts just to find what I was looking for. Many thanks!
    I am sure this post has helped me save many hours of browsing other related posts just to find what I was looking for. Many thanks!
    python Course in Pune
    python Course institute in Chennai
    python Training institute in Bangalore

    ReplyDelete
  3. Nice article.
    For data science training in Bangalore,
    VISIT:
    Data Science training in Bangalore

    ReplyDelete
  4. This is the exact information I am been searching for, Thanks for sharing the required infos with the clear update and required points. To appreciate this I like to share some useful information.python training in bangalore

    ReplyDelete
  5. Enjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck…

    Start your journey with Database Developer Training in Bangalore and get hands-on Experience with 100% Placement assistance from experts Trainers @Bangalore Training Academy Located in BTM Layout Bangalore.

    ReplyDelete
  6. iPage is offering the lowest price for a monthly package on the Black Friday Web Hosting Deals 2019 with $1.99 a month even on a twelve months package. iPage offers registration for their domain and has a very easy ways of building sites with their drag and drop site builder.

    ReplyDelete
  7. wonderful thanks for sharing an amazing idea. keep it...

    Learn DevOps from the Industry Experts we bridge the gap between the need of the industry. eTechno Soft Solutions provide the Best DevOps Training in Bangalore .

    ReplyDelete
  8. I am happy for sharing on this blog its awesome blog I really impressed. thanks for sharing.

    Start your journey with In Software Training in Bangalore and get hands-on Experience with 100% Placement assistance from experts Trainers @eTechno Soft Solutions Located in BTM Layout Bangalore.

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. Hi there! I could have sworn I’ve visited your blog before but after looking at a few of the posts I realized it’s new to me. Anyways, I’m certainly pleased I came across it and I’ll be book-marking it and checking back often!
    Gadgets

    ReplyDelete
  11. I'M NOW FULFILL FINANCIALLY BECAUSE OF THE LOAN I GOT FROM LFDS .I would like to bring this to the notice of the public about how i came in contact with LFDS after i lost my job and being denied loan by my bank and other financial institution due to my credit score. I could not pay my children's fees. I was behind on bills, about to be thrown out of the house due to my inability to pay my rent, It was during this period my kids were taken from me by foster care. Then i set out to seek for funds online where i lost $3,670 that i borrowed from friends which i was rip off by two online loan companies. Until i read about:Le_Meridian Funding Service  (lfdsloans@outlook.com / lfdsloans@lemeridianfds.com) somewhere on the internet, Still wasn't convince because of what i have been through until a relative of mine who is a clergy also told me about the ongoing loan scheme of LFDS at a very low interest rate of 1.9%% and lovely repayment terms without penalty for default of payment. I have no choice than to also contact them which i did through text +1-989-394-3740 and Mr Benjamin responded back to me That day was the I'M best and greatest day of my life which can never be forgotten when i receive a credit alert of $400,000.00 Usd loan amount i applied for. I utilized the loan effectively to pay up my debts and to start up a business and today i and my kids are so happy and fulfill. You can as well contact them through email: (lfdsloans@outlook.com / lfdsloans@lemeridianfds.com)  WhatsApptext  helpline: +1-989-394-3740 Why am i doing this? I am doing this to save as many that are in need of a loan not to be victim of scams on the internet. Thanks and God bless you all, I'm Oleksander Artem from Horizon Park BC , Ukrain.

    ReplyDelete
  12. Thanks for the informative article About Selenium. This is one of the best resources I have found in quite some time. Nicely written and great info. I really cannot thank you enough for sharing.
    Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

    ReplyDelete
  13. nice article!!!Infomative and meaningful

    https://devu.in/machine-learning-training-in-bangalore/

    ReplyDelete
  14. Nice Post thanks for the information, good information & very helpful for others,Thanks for Fantasctic blog and its to much informatic which i never think ..Keep writing and grwoing your selfThanks for sharing this valuable information on High DA. I hope you will keep us updated on it.Its is very fascinating the way that this blog were made. i am very interested to view how do this blog were made.I simply wanted to write down a quick word to say thanks to you for those wonderful tips and hints you are showing on this siteJava training in Chennai

    Java Online training in Chennai

    Java Course in Chennai

    Best JAVA Training Institutes in Chennai

    Java training in Bangalore

    Java training in Hyderabad

    Java Training in Coimbatore

    Java Training

    Java Online Training

    ReplyDelete
  15. it’s really nice and meaningful. it’s really cool blog. Linking is very useful thing.you have really helped lots of people who visit blog and provide them useful information.

    DevOps Training in Chennai

    DevOps Online Training in Chennai

    DevOps Training in Bangalore

    DevOps Training in Hyderabad

    DevOps Training in Coimbatore

    DevOps Training

    DevOps Online Training

    ReplyDelete
  16. Good post and informative. Thank you very much for sharing this good article, it was so good to read and useful to improve my knowledge as updated, keep blogging.Thank you for sharing wonderful information with us to get some idea about that content.

    Software Testing Training in Bangalore

    Software Testing Training

    Software Testing Online Training

    Software Testing Training in Hyderabad

    Software Testing Courses in Chennai

    Software Testing Training in Coimbatore


    ReplyDelete
  17. It’s hard to come by experienced people about this subject, but you seem like you know what you’re talking about. I have found something which helped me. Thank you.
    Java Training in Chennai

    Java Training in Velachery

    Java Training inTambaram

    Java Training in Porur

    Java Training in Omr

    Java Training in Annanagar




    ReplyDelete
  18. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging.
    Python Training in Chennai

    Python Training in Velachery

    Python Training in Tambaram

    Python Training in Porur

    Python Training in Omr
    Python Training in Annanagar

    ReplyDelete
  19. Cognex is the AWS Training in Chennai. Cognex offers so many services they are, microsoft azure, prince2 foundation, ITI v4 foundation course, etc

    ReplyDelete
  20. The information you have posted is very useful. The sites you have referred was good. Thanks for sharing. ExcelR Data Analytics Courses

    ReplyDelete
  21. Shreeja Health Care is leading manufacturer of Oil Making Machine. Shreeja Oil Extraction Machine is able to extract oil from various seeds like peanuts, Coconut, Sesame, Soybean, macadamia nuts, walnuts, sunflower seeds, vegetable seeds flaxseed et.

    ReplyDelete
  22. very interesting to read and useful stuff.thanks for sharing.keep it up.Angular training in Chennai

    ReplyDelete
  23. Nice blog post,
    Google Adwords Certification Course
    With our Google Adwords Certification Course in Hyderabad, the student will learn how to use PPC, CPC, CPM, CPA, Display Ads, Shopping Ad Campaign and he will also learn how to promote a website online.

    ReplyDelete

  24. I was really satisfied to discover this page. I needed to thank you for ones time for this awesome read!! I certainly preferred all of it and I have you saved to fav to see new data in your blog.
    best interiors

    ReplyDelete
  25. I discovered your blog while looking for the updates, I am glad to be here. Exceptionally helpful substance and furthermore effectively reasonable giving.. Trust me I did composed a post about instructional exercises for amateurs with reference of your blog…

    AWS Training in Hyderabad

    ReplyDelete
  26. thanks for the info. It’s hard to come by well-informed people in this particular topic. we are looking forward for more

    Machine learining Training in Hyderabad

    ReplyDelete
  27. Amazing Article! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.If you are Searching for info click on given link
    Data science course in pune

    ReplyDelete
  28. Hey! I'd like to start a blog so I can easily share my own experience and feelings online. Please let me know if you have any kind of ideas or tips for new aspiring blog owners. 토토사이트

    ReplyDelete
  29. Woah! I'm really enjoying the template/theme of this website.
    It's simple, yet effective. A lot of times it's difficult to get that "perfect balance" between usability and visual appearance.
    경마사이트
    경마

    ReplyDelete
  30. I really enjoyed reading your article. I found this as an informative and interesting post, so i think it is very useful and knowledgeable. I would like to thank you for the effort you made in writing this article. Elder Maxson Coat

    ReplyDelete
  31. jan adhar card very usefull in rajsthan govt. All Process in Download See Now

    ReplyDelete

  32. fcs ration card list is the most popular website in uttar pradesh govt

    ReplyDelete
  33. I love to recommend you Where can crawl Exciting Products latest Jackets, Coats and Vests Click Here James Bond Peacoat

    ReplyDelete
  34. This post is so interactive and informative.keep update more information...
    Data Science course in Tambaram
    Data Science course in Chennai

    ReplyDelete

  35. Very Informative and useful... Keep it up the great work. I really appreciate your post.
    It shows like you spend more effort and time to write this blog

    https://bangaloredigitalmarketing.com/
    https://bangaloredigitalmarketing.com/digital-marketing-courses-in-bangalore/
    https://bangaloredigitalmarketing.com/seo-company-in-bangalore/
    https://bangaloredigitalmarketing.com/social-media-marketing-agency-in-bangalore/

    ReplyDelete
  36. Thank you for this wonderful post, great article, keep up the excellent work. Check out our blog Best DU LLB Coaching in Delhi

    ReplyDelete
  37. This very informative and interesting blog. I have read many blog in days but your writing style is very unique and understanding. if you are interested in home appliances then click below.

    buy home appliances
    buy home appliances online

    ReplyDelete
  38. wordpress design agency in united states Need professional WordPress Web Design Services? We're experts in developing attractive mobile-friendly WordPress websites for businesses. Contact us today!

    ReplyDelete
  39. Vray 2.0 for SketchUp 2016 Full Version Vray 2.0 for SketchUp 2016 : V-Ray offers state-of-the-art rendering technology for your most demanding projects .RekordBox Dj License Key Generator

    ReplyDelete
  40. We can speak that Mixed In Key Crack is an inspiring software which will allow you to mix your sounds simply. Mixed In Key 10 Free Download can be second-hand in mixture. Mixed In Key Free Download

    ReplyDelete
  41. Thankyou for such informative blog. Keep sharing like these blogs.
    Java course in Noida

    ReplyDelete
  42. Thanks for the information... I really love your blog posts.

    ReplyDelete
  43. thank you so much for this amazing resource..

    ReplyDelete
  44. I really appreciate your efforts for writing this blog. Thanks for sharing. Home Cleaning Services in Delhi

    ReplyDelete
  45. You're absolutely right that using UUIDs as database record identifiers can have several benefits, such as uniqueness, compatibility across different systems, and reduced risk of collisions. However, you've correctly pointed out that there can be challenges when working with UUIDs in the context of JDBC and SQL.

    While UUIDs are not recognized as a standard data type in SQL or JDBC, there are ways to handle them effectively. Here are a few approaches you can consider:

    Storing UUIDs as String or Binary: Since UUIDs are typically represented as strings (e.g., "550e8400-e29b-41d4-a716-446655440000") or binary data, you can store them as such in your database. In the case of Postgres, you can use the uuid data type or store the UUID as a string or binary data in a column.

    Converting UUIDs in SQL: If you're working with databases that don't have native UUID support, you can store UUIDs as strings or binary data in a column and handle conversions in your SQL queries. For example, you can use functions like UUID_TO_BIN or BIN_TO_UUID to convert between binary and string representations of UUIDs.

    Custom Mapping in JDBC: JDBC provides mechanisms to handle non-standard data types through custom mappings. You can create a custom mapping between the UUID class in Java and the appropriate data type in your database. This allows you to work with UUIDs seamlessly in your Java code while transparently converting them to the corresponding database representation.

    Here's an example of registering a custom mapping for UUIDs in JDBC:
    java

    // Assuming you have a connection object named 'connection'
    java.util.Map> typeMap = connection.getTypeMap();
    typeMap.put("uuid", java.util.UUID.class);
    connection.setTypeMap(typeMap);

    By registering the custom mapping, you can retrieve UUID values from the database using the java.util.UUID class.

    It's worth noting that the specific approach you choose may depend on the database system you're using, as different databases have varying degrees of support for UUIDs.

    While working with UUIDs in the JDBC and SQL ecosystem may involve some additional considerations and conversions, it's certainly possible to utilize UUIDs effectively in your database design. The benefits they provide, such as uniqueness and compatibility, often outweigh the challenges involved in handling them across different layers of your application.
    Jobs Listings
    Visit Serafelagi.com!
    If you have any further questions or need more specific guidance, feel free to ask!

    ReplyDelete
  46. Amazing Article! I would like to thank you for the efforts you made to write this awesome article.
    Data science training institute in kukatpally

    ReplyDelete
  47. Thank you , it helps me alot for my research you have posted usefull information
    Exploring the Hardware Architecture of Embedded Systems

    ReplyDelete
  48. In navigating the challenges of UUID integration between Java and Postgres via JDBC, Crafted Software illustrates the intersection of modern database practices with Java development, offering insights that Cambridge Infotech can leverage for seamless data management solutions.

    ReplyDelete
  49. I am genuinely impressed with your efforts and thoroughly pleased to have visited this post. Your hard work truly shines through, and it made for an exceptionally engaging experience. fullstacktrainingcenter

    ReplyDelete
  50. Thank you for the incredibly informative article about Selenium. It stands out as one of the best resources I’ve encountered in quite some time. Your detailed insights and thorough coverage make it an invaluable asset for anyone looking to deepen their understanding of the topic. fullstacktrainingcenter

    ReplyDelete