CODEWITHSUNDEEP
javaspringpostgresqljdbc
David Edgar
David Edgar

Reputation: 505

Perform insertion of data into multiple tables using JDBC in one function

I need to perfom data base insertion in my spring boot webapp. What I have so far is a list of sport competitions and there respective informations.

one competition can hold at different time the same day. Fist thing annoying me is how do I store this times is DB. Does a new table needed here ? (I created a date table)

I searched around and I still don't figure out how to combine the insertion of competition info and its dates in my insert function at same time.

My insert function need some works, I need somme help.

this answer is good, but it's did not fill my requirements

My data base schema:

CREATE TABLE competition ( 
  competition_id integer PRIMARY KEY,
  nom varchar(128) NOT NULL,
); 


CREATE TABLE date ( 
  id integer PRIMARY KEY,
  date_time timestamptz,
  competition_id integer REFERENCES competition (competition_id)
);

Json data:

{
    "id": "420",
    "name": "SOCCER",
    "dates": [
        "2016-05-12T03:00:00.000Z"
        "2016-05-12T04:00:00.000Z"
        "2016-05-12T05:00:00.000Z"
    ]
},
{
    "id": "220",
    "name": "BASKETBALL",
    "dates": [
        "2016-05-12T03:00:00.000Z"
        "2016-05-12T04:00:00.000Z"
    ]
}

My competition Class:

public class Competition{
    private int id;
    private String name;
    private String[] dates;
    // setters ... getters
}

Function to insert data : 

private static final String INSERT_STMT =
      " insert into competition (id, name)"
    + " values (?, ?)"
    ;  


public int insert(Competition competition) {
    return jdbcTemplate.update(conn -> {
      PreparedStatement ps = conn.prepareStatement(INSERT_STMT);
      ps.setInt(1, competition.getId());
      ps.setString(2, competition.getName());
      return ps;
    });


    // insert also in date table ???
  }

Upvotes: 0

Views: 2250

Answers (2)

Felix
Felix

Reputation: 2396

First I would make the ID in the Date-Table Auto-Increment, so you don't have to give an ID for every date and go with this Base-Query:

private static final String INSERT_DATES = "INSERT INTO date (date_time, competition_id) VALUES ";

And then build the Statement like this:

public int insert(Competition competition) {
    // All local variables must be final because the lambdas will be executed at a undefined time
    final int id = competition.getId();
    final String name = competition.getName();

    final String[] dates = competition.getDates();
    final String dateValueStr = String.join(", ", Collections.nCopies(dates.length, "(?, ?)"));

    // Execute Updates
    int updatedRows1 = jdbcTemplate.update(conn -> {
        PreparedStatement ps = conn.prepareStatement(INSERT_STMT);
        ps.setInt(1, id);
        ps.setString(2, name);
        return ps;
    });

    if (updatedRows1 < 1)
    {
        // Something went wrong
        return -1;
    }

    int updatedRows2 = jdbcTemplate.update(conn -> {
        PreparedStatement ps = conn.prepareStatement(INSERT_DATES + dateValueStr);

        int idx = 1;
        for (String date : dates)
        {
            ps.setString(idx, date); // date_time
            idx++;
            ps.setInt(idx, competitionID); // competition_id
            idx++;
        }

        return ps;
    });

    if (updatedRows2 < 1)
    {
        // Something went wrong
        // Rollback the first insert, idk how its made
        return -1;
    }

    return updatedRows1 + updatedRows2;
}

Upvotes: 1

eg04lt3r
eg04lt3r

Reputation: 2610

At first if you need data consistency then you should wrap your insert statements with transaction. For insert data into multiple tables you should execute multiple insert statements, the same as you doing with sql. If you need return updated rows count you can create wrapper class where you can store it and return it.

Upvotes: 2

Related Questions