• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Why last_insert ID returns 0?

 
Ranch Hand
Posts: 1021
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a method which contains a query as follows ;



There are already many IDs inside the table tutor.

So, I do not know why it returns 0.

I did a check directly via MYSQL server by executing the same query above and all it returns are 0s in a column.

Please let me know what should be the right query in order to get the last inserted Id.

Tks.
 
Sheriff
Posts: 22781
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Did you already read all of the official documentation for LAST_INSERT_ID? You can find it at https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id.

I take it the primary key field is an AUTO_INCREMENT column? If not then you will never be able to successfully use LAST_INSERT_ID.
 
Sheriff
Posts: 7125
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please give us a little more context.  Are you issuing this right after an INSERT?  Is the INSERT failing?  Do you have a persistent connection?

Also, I don't think you need a FROM clause.
 
tangara goh
Ranch Hand
Posts: 1021
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Knute Snortum wrote:Please give us a little more context.  Are you issuing this right after an INSERT?  Is the INSERT failing?  Do you have a persistent connection?

Also, I don't think you need a FROM clause.



The first Insert is working fine now.

So, I have another method that retrieve just the last_insert_Id().

But, I think I can't open a new connection again right ?
 
Knute Snortum
Sheriff
Posts: 7125
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My understanding is that you should retrieve the last ID immediately after the INSERT.  Closing the connection or not having a persistent connection invalidate the call.  Opening another connection may do this too -- don't know.  Is there a reason you don't want to get the last ID immediately?
 
tangara goh
Ranch Hand
Posts: 1021
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Knute Snortum wrote:My understanding is that you should retrieve the last ID immediately after the INSERT.  Closing the connection or not having a persistent connection invalidate the call.  Opening another connection may do this too -- don't know.  Is there a reason you don't want to get the last ID immediately?



Hi Knute,

The reason is that I have use generatedKey to get the last_insert_id() but when I set this key to my class - Tutor field Id,and when I called the getMethod to retrieve the Id, it just give me 0.

Why is it so?

This is the same problem that I faced for my List<String>subjects and when I set the class to the subjects, when I use Tutor.getSubjects(), it will show me the name.

However, when I created a method with insertSubjectsTutor(int tutor_id, List<String>subjects>, it won't show up.

Thus, I am also trying Weld which uses dependency injection to see if the m.getTutorId() and m.getSubjects() will show returns the values in another method.

Am I in the right track or there is something that I have missed out such that the values are not returned?
 
Knute Snortum
Sheriff
Posts: 7125
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

tangara goh wrote:The reason is that I have use generatedKey to get the last_insert_id() but when I set this key to my class - Tutor field Id,and when I called the getMethod to retrieve the Id, it just give me 0.

Why is it so?


Is generatedKey a method?  If so I can think of several possible reasons:
1) generatedKey() is not called
2) generatedKey() or last_insert_id() are called after last_insert_id() is invalid.
3) Tutor ID is not set
4) the retrieve ID method tries to return a variable that has gone out of scope

And there are probably more.  It's your responsibility to debug the code, and the easiest way to do that is to place System.out.println() statements throughout the code.  For instance, make the first line of generatedKey() something like:
I'm remembering that this is a Spring project, so you'll use a logging system instead of SoP.

This is the same problem that I faced for my List<String>subjects and when I set the class to the subjects, when I use Tutor.getSubjects(), it will show me the name.

However, when I created a method with insertSubjectsTutor(int tutor_id, List<String>subjects>, it won't show up.

Thus, I am also trying Weld which uses dependency injection to see if the m.getTutorId() and m.getSubjects() will show returns the values in another method.

Am I in the right track or there is something that I have missed out such that the values are not returned?


It's hard to know unless we figure out the first problem.  Focus on one thing at a time.  Divide and conquer.
 
tangara goh
Ranch Hand
Posts: 1021
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Knute,

I could print out the generatedKey in the insert(Tutor tutor) method but my problem is that when I tried to call the tutor.getId() in another method, it just gives me 0, null pointer error.

I do not know the reason and I googled and checked that I have initialized the Tutor class.

So, what could be the problem?
 
Knute Snortum
Sheriff
Posts: 7125
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here's how to post a message to a thread that will get answered:
1) Show your code
2) Post a complete error message
 
Rob Spoor
Sheriff
Posts: 22781
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

tangara goh wrote:I could print out the generatedKey in the insert(Tutor tutor) method but my problem is that when I tried to call the tutor.getId() in another method, it just gives me 0, null pointer error.

I do not know the reason and I googled and checked that I have initialized the Tutor class.


But did you also set the id of the Tutor instance? If you use a framework like JPA that will be set for you, but usually only after the transaction ends. If you don't use a framework (and I'm assuming here that you don't), it's up to you to set the id.
 
tangara goh
Ranch Hand
Posts: 1021
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Rob Spoor wrote:

tangara goh wrote:I could print out the generatedKey in the insert(Tutor tutor) method but my problem is that when I tried to call the tutor.getId() in another method, it just gives me 0, null pointer error.

I do not know the reason and I googled and checked that I have initialized the Tutor class.


But did you also set the id of the Tutor instance? If you use a framework like JPA that will be set for you, but usually only after the transaction ends. If you don't use a framework (and I'm assuming here that you don't), it's up to you to set the id.



Hi Rob,

It is working now.

Can I know why JPA or Hibernate is being used when Java EE can do the same job, plus Java also has EJB that can do the same things JPA and Hibernate?

Is it because JPA is widely used in the market and it is more secured to use JPA or other technologies?
 
Knute Snortum
Sheriff
Posts: 7125
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I can answer this partly.  Hibernate uses an SQL-like language to return objects instead of result sets.  Working with objects in Java is a great advantage in Java (which is object-oriented) rather than JDBC.

My understanding is that JPA is a specification, and Hibernate is an implementation of it.  JPA is part of EJB.
 
Rob Spoor
Sheriff
Posts: 22781
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Correct.
 
reply
    Bookmark Topic Watch Topic
  • New Topic