jasim jasim - 1 month ago 7
Java Question

Is it possible to merge the four different MySQL Queries along with an if condition in between to avoid java codes?

here is my MySQL table:-

pk_id priority active total
--------------------------------
1 west 0 3
2 north 0 0
3 south 1 1
4 east 0 0


Below are my java codes with MySQL quires:-

String past_p;
String past_tot;
String new_p;
String new_tot;


//query 1: active= 1 means, past processed data.

String past_priority = "select priority, total from tbl_priority where active = 1";

Statement stmt = (Statement) conn.createStatement();
ResultSet Op=stmt.executeQuery(past_priority);
if(Op.next()){
past_p =Op.getString("priority");
past_tot =Op.getString("total ");
}
//query 2:

String new_priority = "Select priority, max(total) from tbl_priority";

ResultSet Np =stmt.executeQuery(new_priority );
if(Np.next()){

new_p =Np.getString("priority");
new_tot =Np.getString("total ");
}

if (past_tot >= new_tot){

return 0;
}

else{

//query 3:

String update_a= "update tbl_priority set active=0 where NOT (priority=?");


PreparedStatement upa = conn.prepareStatement(update_a);
upa.setString(1, new_p);
upa.executeUpdate();

// query 4:

String update_b= "update tbl_priority set active = 1 where priority =?";

PreparedStatement upb = conn.prepareStatement(update_b);
upb.setString(1, new_p);
upb.executeUpdate();
}


I am developing a real-time life saving application where time really matters.
I need to call this function in small intervals, hence i need to optimize the above codes to save processing power and time for execution.

Brief explanation of above function:

query 1:

I need to identify the total value of respective priority with active flag, active=1,

query 2/3/4:

I need the highest total value, have to compare it with the previous processed data. If it is larger that previous value, have to reset the flag of previous processed data and set active=1 with the current highest total value.

on the above example, the current highest (max(total)) is 'west' and past active is south. The active flag of south and west have to be changed after identifying that currently west holds highest value in total.

If those MySQL quires are merged, it will save processing speed and time for execution, right?

Is it possible to merge those MySQL quires and avoid java codes in between to get same result (maybe by using joint function)? I tried several times and failed.

Any piece of code is highly appreciated!

Thanks in Advance.

Answer

If you only have one active item at the time you could just look for the highest value of total and reset the status without any search and comparison.

SET @pk_id_tmp = (SELECT `pk_id` FROM `tbl_priority`  ORDER BY `total` DESC, `active` DESC LIMIT 1);

UPDATE `tbl_priority` 
SET `active` = CASE `pk_id` WHEN @pk_id_tmp THEN 1 ELSE 0 END
Comments