getting the result of sql command in java [duplicate]

Question

i need to get the last id entered in my data base witch is AUTO_INCREMENT so i did this

 String Var = "SELECT MAX(id) FROM goupe ; ";
     ResultSet vari=st.executeQuery(Var);
     while(vari.next()){
     nombre = vari.getInt("id");}
     String sql = "INSERT INTO Student(name,famillyname,email,password,module,speciality,card,id_goupe)VALUES('"+name+"','"+familly+"','"+email+"','"+pass+"','"+module+"','"+specialite+"','"+card+"','"+nombre+"');";
                st.execute(sql);

but i had this problem Column 'id' not found. so what should i do to have it right .

Give a name to your result, e.g SELECT MAX(id) AS maxid FROM goupe ..vari.getInt("maxid") — Arnaud
– Arnaud, Commented Jun 8, 2018 at 14:22
Alternatively, you could do vari.getInt(1) to get the value of the first column of the result. — SurfMan
– SurfMan, Commented Jun 8, 2018 at 14:24

Chris Parker · Accepted Answer · 2018-06-08 15:28:18Z

I have to say, there are a couple of really easy things you can do to greatly improve your code.

If your latest ID is generated elsewhere, then embed the query directly into the statement such that you don't need to go get it. That will reduce the risk of a race condition.
Use PreparedStatements. Let me ask you this question: What do you suppose is going to happen if one of your user's name is O'Ryan?

Since your code is just a snip, I also will only provide a snip:

int index = 1;
String sql = "INSERT INTO Student(name,famillyname,email,password,module,speciality,card,id_goupe)" + 
    "VALUES(?,?,?,?,?,?,?,(SELECT MAX(id) FROM goupe));";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(index++, name);
ps.setString(index++, familyname);
ps.setString(index++, email);
ps.setString(index++, password);
ps.setString(index++, module);
ps.setString(index++, speciality);
ps.setString(index++, card);
int rows = ps.executeUpdate();
if(rows == 1) {
    System.out.println("Successfully inserted row");
}

UkFLSUI · Accepted Answer · 2018-06-08 14:34:01Z

0

When you execute the query SELECT MAX(id) FROM goupe;, then in the returned table, the column name no longer remains as id.

So, the best approach is to provide a name for the column like below:

SELECT MAX(id) AS maxid FROM goupe;

Then, you can get the value using:

vari.getInt("maxid")

answered Jun 8, 2018 at 14:34

UkFLSUI

5,6906 gold badges38 silver badges50 bronze badges

Collectives™ on Stack Overflow

getting the result of sql command in java [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related