Count number of affected rows over several queries passed to MySql in java code

Question

I would count the number of rows affected by the query provided as a prepared statement in java passed to a MySql engine.

Given the following sample queries passed to a prepared statement, I need to be able to count the total number of rows affected by all the queries.

//insert data into table X ;
//update records of table X ;

The queries are separated by ";" which is part of MySql syntax to support several CRUD operations over a particular PreparedStatement object. It seems when the "executeUpdate()" method is invoked, that is only the number of rows affected by the first query, i.e., insert to the table, gets returned. Do I miss something I was supposed to provide to get the number of total affected rows in such that query?

Here is the sample real code I am working on:

insert into Activity
select * from (select ?, ?, ?, ?) as temp
where not exists(select * from Activity where ActivityName=?);
update Activity
set EmployeeeName=?, DepartmentName=?, roleId=? 
where ActivityName=?;

I expect the minimum of 1 as the output while get the 0 instead.

Answer 1

1. Check what returns select * from Activity where ActivityName=?.

2. Check what returns select ... where ... (select ...).

After your explanation of your goal in the comments there are other solutions to add values that don't exists yet.

3. Define column ActivityName is a primary key (if not done yes). Then don't check anything, don't do any select, just do insert:

    try {
      // statement to insert like
      // insert into Activity (ActivityName, EmployeeeName, DepartmentName, roleId)
      //     values (?, ?, ?, ?)
    } catch ... {
      // If the exception is caused by primary key violation,
      // then such ActivityName already existed and we can ignore this exception
    }

Why not check first if such ActvityName already exists? Because if you have many requests in parallel from the same user or from many other users, then in the time between your check and between inserting new value some other request can insert this value, and you get primary key violation exception. That's why you need try/catch any way. That's why just do insert without any check and use proper try/catch.