CODEWITHSUNDEEP
mysqljsp
WTFZane
WTFZane

Reputation: 612

JSP,mysql - How to get and store variable from a fresh query result

i have an insert sql query. It works fine, I just want to get the p_id that it will produce and store it as a variable, then I will have another INSERT statement then I will put the variable of that ID. Im doing a System.out.println and it produce null

Here is the query that I will run, and it works fine, it inserts the data in the database. (The ID is auto_incremented so i didn't put that)

int i = st.executeUpdate("INSERT INTO logs_pms_t_project (p_name, p_objective) VALUES ('"+p_name+"','"+p_objective+"')");

My thoughts on how to get the p_id

String p_id= request.getParameter("p_id"); after the above query

Whereas I can now execute the the another query knowing that I have a variable in the p_id

int j = st.executeUpdate("INSERT INTO logs_pms_r_budget (budget_cost, p_id) VALUES ('"+p_budget+"','"+p_id+"')");

Upvotes: 1

Views: 771

Answers (1)

1000111
1000111

Reputation: 13519

Try the following:

int i = st.executeUpdate("INSERT INTO logs_pms_t_project (p_name, p_objective) 
        VALUES ('"+p_name+"','"+p_objective+"')",Statement.RETURN_GENERATED_KEYS);

ResultSet rs = st.getGeneratedKeys();
int lastInsertedID = -1;
if (rs.next()) {
     lastInsertedID = rs.getInt(1);
     System.out.println("Last Inserted ID = " + lastInsertedID);
}

It will return the last inserted ID(Primary key).

Note: ExecuteUpdate returns the number of affected rows.

A single insertion will always affect one row in your table. So the variable i contains value 1. But in order to get the last inserted primary key value you need to follow the above code snippet.

Upvotes: 1

Related Questions