How to retreive row in DB Table just added now in Java

Question

Table

id int(11) No auto_increment Change Drop Primary Index Unique Fulltext

email varchar(45) latin1_swedish_ci No Change Drop Primary Index Unique Fulltext

billpayment tinyint(1) No Change Drop Primary Index Unique Fulltext

dispatch tinyint(1) No Change Drop Primary Index Unique Fulltext

address varchar(75) latin1_swedish_ci Yes NULL Change Drop Primary Index Unique Fulltext

phone int(11) Yes NULL Change Drop Primary Index Unique Fulltext

created_at datetime No Change Drop Primary Index Unique Fulltext

totalbillamount float Yes NULL Change Drop Primary Index Unique Fulltext

Java Code:

        sql = "insert into session_shopping (email,billpayment,dispatch,address,phone,created_at,totalbillamount) values(?,?,?,?,?,?,?)";
        ps = (PreparedStatement) con.prepareStatement(sql);
        ps.setString(1, email);
        ps.setBoolean(2, false);
        ps.setBoolean(3, false);
        ps.setString(4, "");
        ps.setInt(5, 0);
        java.util.Date date = new java.util.Date();
        long t = date.getTime();
        java.sql.Date sqlDate = new java.sql.Date(t);
        ps.setDate(6, sqlDate);
        ps.setFloat(7, 00.0f);
        int newId = ps.executeUpdate();
        System.out.println("newId" + newId);
        if (newId == 1) {
            sql = "select * from session_shopping where id = ?";
            ps = (PreparedStatement) con.prepareStatement(sql);
            ps.setInt(1, newId);
            ResultSet reS = ps.executeQuery();
            Session s = new Session();
            s.setId(reS.getInt("id"));
            s.setEmail(reS.getString("email"));
            System.out.println("retreived");
            return s;
        } else {
            System.out.println("unable to save");
        }

This code fails because int newId is boolean

What i want to do is. I want to retreive the row which i added just now. Please help me.

Answer 1

executeUpdate will return the number of rows affected, not the current row .

Try this

ResultSet rs = aStatement.executeQuery("SELECT LAST_INSERT_ID()");
while(rs.next()) 
{
  key =  rs.getInt(1);
}

Answer 2

The value returned by executeUpdate has nothing to do with your ID.

Before we go to getting your ID, you can start off by setting email in the Session from your email variable rather than pulling it back out of the database.

As things stand, the only way I can think of to get the newly inserted ID is by changing your SQL thus:

sql = "select max(id) from session_shopping";

This will give you problems if you get another insert before you pick out the maximum ID though. To prevent that, put the select in the same transaction as your insert.