JAVA mysql updating two tables?

Question

https://i.stack.imgur.com/Pd3pu.jpg

I have two comboboxes (ploegid and trainerid) stored with data. Don't worry about the ploegID and trainerID who are given as parameters are correct.

I want to set trainer_id on table ploeg where id = ? But I also want the connected table (persoon) to be updated!

Table persoon has to be like this: update persoon set ploeg_id = ? where ? is the parameter ploegID

how can i do this in one update statement? inner joins?

     public void voegTrainerAanPloeg(Integer ploegid, Integer trainerid) throws
  DBException {
  // connectie tot stand brengen (en automatisch sluiten)
  try (Connection conn = ConnectionManager.getConnection();) {
     // preparedStatement opstellen (en automtisch sluiten)
     try (PreparedStatement stmt = conn.
        prepareStatement(
           "update ploeg set trainer_id = ? where id=?");) {

           stmt.setInt(1, trainerid);
           stmt.setInt(2, ploegid);
           // execute voert elke sql-statement uit, executeQuery enkel de select
           stmt.execute();
        } catch (SQLException sqlEx) {
           throw new DBException("SQL-exception in verwijderRekening");
        }
  } catch (SQLException sqlEx) {
     throw new DBException(
        "SQL-exception in verwijderRekening - connection");
  }

 }

score 0 · Accepted Answer · edited May 23 '17 at 11:43

When you need to perform more than one operation at the same time like this (which is very common for relational databases), you should use Stored Procedures instead of executing each statement from your application. As stated here, you cannot update two tables in one statement.

Below from mySql docs:

Putting database-intensive operations into stored procedures lets you define an API for your database application. You can reuse this API across multiple applications and multiple programming languages. This technique avoids duplicating database code, saving time and effort when you make updates due to schema changes, tune the performance of queries, or add new database operations for logging, security, and so on.

Unfortunately I don't speak the language that your method name is written in, but let's pretend it was called "CreateUser()". By letting your database control the various updates/inserts than might be required to create a user by encapsulating it in a Stored Procedure, you don't need to worry about your question - how do do multiple things in one update statement.

In summary, people generally don't update multiple tables with one update statement from their application code - they execute a single Stored Procedure which will perform the multiple updates for them on the database.

This has the further advantage that you only make one call to the database for arbitrarily complex operations on your data.

I should mention that a Stored Procedure is a concept common to most relational databases, not just MySql — user1886323, Aug 03 '15 at 18:04
Alternatively, an ORM tool (e.g. a JPA implementation such an EclipseLink or Hibernate) can generate and execute the appropriate SQL to perform such updates for you. — James_D, Aug 03 '15 at 18:11
Is there no easy way ? like update persoon and ploeg set .. ? — Lowie Menu, Aug 03 '15 at 18:28
Check out the question I have linked to in my edited answer. It shows how to update two tables inside a single transaction, but no, you cannot update two tables in one statement. Anyway, Stored Procedures ARE easy, once you know how. They are just like methods, but for databases. — user1886323, Aug 03 '15 at 18:32

JAVA mysql updating two tables?

1 Answers1