Pass on getGeneratedKeys to next page

Question

I would like to pass on the getGneratedKeys which is also known as last_generated_id from the current Java class to the next class. However, I am quite clueless on how to store and collect the ID to the next Java class for this class to use.

The scenario is that insert.jsp will insert all the strings and int into my mysql database. The 2nd page UserImage.jsp will upload the image into the same table and same record as insert.jsp. This means that UserImage query would be an update query instead of an insert query.

Save.java (associated class for insert.jsp):

public String execute()
{ 
   int i = 0; //SELECT LAST_INSERT_ID();
try{
 Class.forName("com.mysql.jdbc.Driver");
java.sql.Connection con =DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?zeroDateTimeBehavior=convertToNull","root","8899");

String s = "insert into usertable(userNRIC, userName, userEmail, userPW, userContact, userAddress, catID, catTypeID, sectionID, status"
                        + ") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
PreparedStatement ps=con.prepareStatement(s, Statement.RETURN_GENERATED_KEYS);

ps.setString(1, mb.getUserNRIC());
ps.setString(2, mb.getUserName());
ps.setString(3, mb.getUserEmail());
ps.setString(4, mb.getUserPW());
ps.setInt(5, mb.getUserContact());
ps.setString(6, mb.getUserAddress());
ps.setInt(7, mb.getCatID());
ps.setInt(8, mb.getCatTypeID());
ps.setInt(9, mb.getSectionID());
ps.setString(10, mb.getStatus());       
ps.executeUpdate();
con.commit();
ResultSet rs = ps.getGeneratedKeys();
if (rs != null && rs.next()) {
    i = rs.getInt(1);
    //ps.setInt(i, mb.getUserID("UserID"));
}
    ps.close();
    con.close();

    } catch (Exception e) {
        e.printStackTrace();
        addActionError(e.getMessage());

    }
    return SUCCESS;
    
}

FileUploadAction.java (associated class for UserImage.jsp):

public String execute(){
    int i = 0;
    connection = getConnection();
    String query = "update usertable set userPhoto=? where userID=?";
    try {
        String filePath = servletRequest.getSession().getServletContext().getRealPath("/");
        System.out.println("Server path:" + filePath);
        File fileToCreate = new File(filePath, this.userImageFileName);

        FileUtils.copyFile(this.userImage, fileToCreate);
         //String uniqueFileName = fileToCreate.getPath();
        pst = connection.prepareStatement(query);
        pst.setString(1, this.userImageFileName);
       
        i = pst.executeUpdate();
        
    } catch (Exception e) {
        e.printStackTrace();
        addActionError(e.getMessage());

        return INPUT;
    }
    return SUCCESS;

}

Answer 1

Algo:

Record record = Fire the select query based on userID ordered by last insertion
If record is found then
    get the generated value from first record
    update the record
Else
    insert new record

---

As per your comment

User Journey : Student.jsp -> UserImage.jsp

Simply store the auto-generated random key as request attribute and pass it client side to the UserImage.jsp and send back to server when upload button is clicked.

Read more about Pass data from Java Servlet to JSP?

Answer 2

In the action class you should create a property (with getter and setter) to hold the value from getGeneratedKeys. Let it be last_generated_id which you initialize by the first action.

The action returns UserImage.jsp where you can use a hidden field bound to the action property that contains a value.

When you submit the form to the second action this value is populated to the second action property. So, you can get it easy there when you need to use it when a second action is executed.

Because you use different classes for the actions then you should create this property in both classes. Take care that a hidden field is properly bound and populated by OGNL via providing appropriate actions configuration.

Answer 3

Simply get last inserted idwhile update time.

Statement stmt = con.createStatement();
String query = "SELECT max(userID) FROM usertable";
int my_last_generated_id =  stmt.executeQuery(query).uniqueResult(); 

And Your UpdatePost

 pst = connection.prepareStatement(query);
 pst.setString(1, this.userImageFileName);
 pst.setInt(2, my_last_generated_id );
 i = pst.executeUpdate();