Code Bae Code Bae - 1 month ago 12
Java Question

How to Retrieve auto incremented ID value to a JLabel

I have a mysql Database which as Book Id (auto incrementing when I add data) and other rows. I have wrote a code to get the incremented value to a jLabel and it works fine too but still there is a one more little problem. assume I have entered 5 book details. so when I press the Save button (to save the details to database) it saves successfully and

jLabel
shows the incremented value as 6 (so it works fine).

Now the problem is, every time I closed my application and restart it, the jLabel shows the value as 1. Then when I save another book detail it skip to number 7 (which is the correct auto incremented ID). But Why is it showing the ID as 1, when I restart the application? It should read 7 even after I restart it. Here is the code sample. I have even call it in the class Constructor. But it doesn't seem to update. :(

private void autoGenerateId() {
if (true) {
try {
ResultSet result = new JDBC().getData("SELECT * FROM newbookstock");
if (!result.next()) {
jLabel5.setText("1");
} else {
ResultSet result2 = new JDBC().getData("SELECT LAST_INSERT_ID() AS bookid FROM newbookstock");
if (result2.next()) {
int id = result2.getInt("bookid") + 1;
String ss = String.valueOf(id);
jLabel5.setText(ss);
} else {
System.out.println("OOOOO");
}
}
} catch (Exception e) {
e.printStackTrace();
}
} else {
System.out.println("Set focus to Jtext1");
}
}

Answer

let's digest your code.

        ResultSet result = new JDBC().getData("SELECT * FROM newbookstock");
        if (!result.next()) {

This if condition will evaluate to false if the table is empty. So this is not where you are setting the label as 1

} else {
                ResultSet result2 = new JDBC().getData("SELECT LAST_INSERT_ID() AS bookid FROM newbookstock");

Ah, but what do you get here when you first start the program? 0. Why because you have not inserted any values into the table with your current connection.

What you ought to do is

 SELECT COUNT(*) FROM newbookstock

where you do

 SELECT * FROM newbookstoc

or perhaps you might want MAX(id) instead of count(*) but select * is definitelly not what you should be using.

and use that value as your initial value. In other news, you have

new JDBC().getData(...)

in two locations. You shouldn't open multiple connections like this. Open the connection once and reuse it.

update: your snippet to set the initial value might look like:

        JDBC conn = new JDBC();
        ResultSet result = jdbc.getData("SELECT COUNT(*) as C FROM newbookstock");
        jLabel5.setText(result2.getString("C"));