NamedJDBCTemplate batch insert with object members

Question

So just for simplicity, I wrote up a little example of what I am trying to do. I haven't been able to find a solution for this but I'm sure there is a solution. I am trying to batch insert a list of objects into a table that contains an id for some other object. Confusing to explain but if you look at the code it should be easy to see what I am trying to do:

public class Person{
    private Long id;
    private String firstName;
    private String lastName;
    private Animal animal;
    ... {getters/setters}
}

public class Animal{
    private Long id;
    private String species;
    private String name;
    ... {getters/setters}
}

Somewhere in my code:

List<Person> people = someList;
personsRepository.save(people);

My repository:

public class PersonsRepository{
    @Autowired(required=true)
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public void save(List<Person> people)
    {
        String sql = "insert into PERSONS(ID, FIRST_NAME, LAST_NAME, ANIMAL_ID) VALUES(:id, :firstName, :lastName, {{ animal.id }})";
        SqlParameterSource[] parameterSource = SqlParameterSourceUtils.createBatch(people.toArray());

        namedParameterJdbcTemplate.batchUpdate(sql, parameterSource);
    }
}

You will notice in my repository, I need to insert the ANIMAL_ID. How can I do that with a batch insert? I am using an oracle db in prod, but hsql (with oracle syntax) locally.

Answer 1

Depending on the database you want usually to use a sequence or an identity column, to have the value of the primary key generated on a concurrency-safe way, have a look at this answer for the pros and cons of sequences vs identity columns.

Other ways are available to generate keys but these are the most frequently used.