Win a copy of Event Streams in Action this week in the Java in General forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Knute Snortum
  • Rob Spoor
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Piet Souris
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

How to retrieve next free int (value) from database with Spring/Hibernate?

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi. I have made a Spring MVC form for creating a new Customer. Customer has an ID but also has a int value CustNumber which is also a primary key. I need to setup that every time when I open my customer form, in input field CustNumber, app generates next free value from column CustNumber in database.
I'm using just Spring MVC with Hibernate and I'm new in all of this, i just started learning Spring a week ago. I have my Customer controller, service, DAO, Customer class, my basic CRUD operations work. I just don't know how to use queries in Spring, raw queries that I used in Java. If somebody can help me to retrieve
next free CustNumber from database and automatically write it in input field CustNumber, I would be much grateful.
 
Saloon Keeper
Posts: 10415
223
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Let's take a step back. More important than 'how' is 'why'.

WHY do you want to fill in a customer number in a form when that number might get taken by a different customer in the mean time?

WHY do you have a customer number AND a customer ID? What's the difference?

[edit]

And welcome to CodeRanch!
 
Dejan Radovanovic
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is a part of a larger accounting application and I got the assignment  to put one part of it on the web using Spring. Two customers can have a same CustNum, but what separates them is their ID and CompanyID (multiple companies can use our program). Listen, I didn't write that app, my senior colleagues did, I just need to put a part of it on the web in the exact form. And in original form, when you click on CustomerRegister, it opens a form for creating and in the first field, writes next free CustNumber automatically.
 
Greenhorn
Posts: 18
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your primary key needs to be generated by the database, not in java.  That way you will have no issues with records being added by threads in race conditions.  There are two ways that I know of.

autoincrement:

Many databases have an autoincrement type field for this purpose.  Inserting the record in the table will result in the field being populated with the next available number.  Usually, you can return a handle from the insert statement that gives you immediate access to your new id.

https://www.w3schools.com/sql/sql_autoincrement.asp

sequences:

I have experience with Oracle and in it you use a sequence object.  You select from the sequence to get the next number in this case.  Here are some links for more info.

https://docs.oracle.com/cd/B28359_01/server.111/b28310/views002.htm#ADMIN11792
 
Stephan van Hulst
Saloon Keeper
Posts: 10415
223
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dejan Radovanovic wrote:Two customers can have a same CustNum, but what separates them is their ID and CompanyID (multiple companies can use our program).


Then the customer number can't be a primary key, because primary keys must be unique.

So please tell us whether the customer number is a primary key, or whether it can be a duplicate.
 
Dejan Radovanovic
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It can be a duplicate, it is not a primary key. Now, how can I retrieve next free value in Java Spring to my form view?
 
Rancher
Posts: 4177
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, since they can be duplicates then what defines the "next free value"?
 
Dejan Radovanovic
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Listen, friends, you're overthinking this way to much. If I have 5 Customers in my database, I need that when I go to my form page, in my input field for CustNumber , writes number 6. That is all. Jdbc template, JPA, what is the best way. I'm not using API, it is just a local mysql base, Spring and Hibernate.
 
Stephan van Hulst
Saloon Keeper
Posts: 10415
223
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you're not considering the edge cases enough. If a row is deleted, there will be gaps in your sequence of customer numbers. In the example you gave, if the customer with customer number 3 is deleted, is the next free customer number 3 or 6?

If the answer is 6, you can probably use a JPQL query similar to this:
 
Dejan Radovanovic
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If the customer with customer number 3 is deleted, the next free customer number should be 3.
 
Dave Tolls
Rancher
Posts: 4177
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dejan Radovanovic wrote:If the customer with customer number 3 is deleted, the next free customer number should be 3.



And that there is why we ask these questions.  That's an important requirement that completely changes likely solutions.

But also this doesn't explain why (or how) you can get duplicate numbers?
If they're allowed what is it that allows them?
 
Bartender
Posts: 20928
127
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think what you have is a lot like a large application that I supported for many years. It, too had multiple clients using the same application and database instance.

The primary key for that database user table (often used by other tables as a foreign key) was a compound key, where the key value was a concatenation of customer ID and user ID. Or in the case of some of the other tables, customer ID and table unique value.

This can be managed if you're using Spring JPA by using the JPA key generator annotation. The only really tricky part about this is that when you first create the record Entity it has no value for the second part of the compound key and will not until you actually commit the entity object to the database. And since the entity hashCode is (usually) generated from the key value, its internal ID changes when you do so. And this gets especially sticky when you are committing a connected web of related entities when the relating key hasn't been assigned yet.

Without going back and looking at old code, what I think I did for that was to commit the base record of the set first to get the generated key value, then updated the linked entities and committed the whole set. But be cautioned that if holes in your key values are intolerable that a failed commit might cause values to be assigned and then not used.
 
Dave Tolls
Rancher
Posts: 4177
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Will that work with the requirement that removing a customer from the middle of the list of customers (eg removing CustNum 3 when there are CustNums 4, 5 and 6 already), will make 3 the next valid number to use.

That looks like the code needs to backfill, and not just increment.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!