0

I am using this 1st query still cannot achieve what i want:

PreparedStatement id = con.prepareStatement("select c_id from customer where c_name=? ORDER by c_name desc ");
id.setString(1,m.getName());

I have a JSP form which is filling the (MySQL)customer table and c_id is only auto-incremented when the form is submitted on button click, but on submission, I want to do another thing which is to select latest entry of c_id which is filled in the table. Due to submission of an entry, a new auto-incremented c_id has been generated which I am getting through this query which is alright.

PreparedStatement id = con.prepareStatement("select c_id from customer where c_name=? ");
id.setString(1,m.getName());

Now the problem is that the (1st or 2nd)query gets the c_id by looking at c_name, if the c_name was never present in the table and has been entered the first time then it will surely get the latest c_id by looking at c_name, but what if the c_name was already present.

For example: If customer has c_id 10 and c_name jacob and if I entered the query for the first time I am able to select c_id 10 but now if I try to enter another entry having c_id 11 and c_name jacob I still get the c_id 10 here and here what I want is c_id 11 which is latest entered having same c_name.

Is there any way to do this?

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
ARSALAN
  • 13
  • 5
  • You can refer https://stackoverflow.com/questions/2857164/cannot-use-a-like-query-in-a-jdbc-preparedstatement/2857417#2857417 – Akash Jul 26 '17 at 18:29
  • 1
    MySQL has a `LAST_INSERT_ID()` function that will get the ID of the last record created. I think it will only work if you use the same connection that you used to do the insert (ie don't close the connection after the insert). – CptMisery Jul 26 '17 at 18:54
  • It worked for me by using this : PreparedStatement id = con.prepareStatement("select c_id from customer where c_name=? ORDER by c_id desc "); id.setString(1,m.getName()); – ARSALAN Jul 26 '17 at 19:13
  • That might become a problem. If Dave inserts a record for customer 1 and a millisecond later Suzy also inserts a record for customer 1, there is a good chance that your query to get the ID will find Suzy's record twice. – CptMisery Jul 26 '17 at 19:47

0 Answers0