Inserting row into multiple table with same sequence number in java

Question

I have to insert data into two tables department and employee one by one through java code. Each table has one common column dept_id which is primary key in department table and foreign key in employee table and refers from dept_id column of department table. there is one sequence dept_sequence defined on dept_id in department table.

Now, my current approach to insert data into both these tables is as below,

I use dept_sequence.nextval and dept_sequence.currval respectively for dept_id column to insert data into both these tables.

Map<String, Object> deptData = ImmutableMap.builder()
        .put("DEPT_NAME", "TEXTILE")
        .put("LOCATION", "PARIS")
        .build();
String insertToDeptSql = "INSERT INTO DEPARTMENT(DEPT_ID, DEPT_NAME, LOCATION) 
                          VALUES(dept_sequence.nextval, :DEPT_NAME, :LOCATION)";
namedParameterJdbcTemplate.update(insertToDeptSql , deptData);

Map<String, Object> empData = ImmutableMap.builder()
        .put("EMP_NAME", "John")
        .put("AGE", 15)
        .build();
String insertToEmpSql = "INSERT INTO EMPLOYEE(EMP_ID, DEPT_ID, EMP_NAME, AGE) 
                         VALUES(emp_sequence.nextval, dept_sequence.currval, :EMP_NAME, :AGE)";
namedParameterJdbcTemplate.update(insertToEmpSql, empData);

It works perfectly fine when there is one single transaction at a time. both the tables have correct dept_id values but it breaks in multi-transactional environment. the employee table do not receive same value of dept_sequence which is inserted in department table for one transaction. before inserting record into employee table, dept_sequence value is increased by a different transaction(new record insert into department table) which might be happening in a different system and employee table receive some increased value of sequence.

How we can implement this in such a way that the dept_id value remains same for in both the tables for same transaction.

NOTE: Actual data models are different, employee and department are just for example purpose so don't suggest any changes in the models and primary key, foreign key constraints as I am not allowed to do anything with actual model.

Tag only the database that you use. – forpas Dec 04 '21 at 17:14 — forpas, Dec 04 '21 at 17:14
@forpas using oracle...made correction – Rushali Atre Dec 04 '21 at 17:17 — Rushali Atre, Dec 04 '21 at 17:17

score 2 · Answer 1 · answered Dec 04 '21 at 18:00

String insertToDeptSql = "INSERT INTO DEPARTMENT(DEPT_ID, EMP_ID, EMP_NAME, DEPT_NAME, LOCATION) 
                          VALUES(dept_sequence.nextval, emp_sequence.currval, :EMP_NAME, :DEPT_NAME, :LOCATION)"

If your primary key constraint is on (dept_id, emp_id), then theoretically, you could run a separate SQL query to get dept_sequence.nextval, then pass the same value into each insert. But that is a very... unconventional use of sequences and I think there are simpler approaches.

I recommend re-evaluating the data model.

Your department table isn't storing departments. It's storing relationships of employees to departments. If employee A and employee B are in the same department, you don't have 2 departments.

What I would suggest you do is put a dept_id column on the employee table and then on the department table, drop the employee-related columns.

You'd end up with something like this:

Map<String, Object> deptData = ImmutableMap.builder()
        .put("DEPT_NAME", "MECH")
        .put("LOCATION", "PARIS")
        .build();
String insertToDeptSql = "INSERT INTO DEPARTMENT(DEPT_ID, DEPT_NAME, LOCATION) 
                          VALUES(dept_sequence.nextval, :DEPT_NAME, :LOCATION)";

jdbcTemplate.update(connection -> {
    PreparedStatement ps = connection
      .prepareStatement(INSERT_MESSAGE_SQL);
      ps.setString(1, message);
      return ps;
    }, keyHolder);
}
long departmentId = keyHolder.getKey().longValue();

Map<String, Object> empData = ImmutableMap.builder()
        .put("EMP_NAME", "John")
        .put("AGE", 15)
        .put("DEPARTMENT", departmentId)
        .build();
String insertToEmpSql = "INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, AGE, DEPARTMENT) 
                         VALUES(emp_sequence.nextval, :EMP_NAME, :AGE, :DEPARTMENT)";
jdbcTemplate.update(insertToEmpSql, empData);

You can repeat the last section for each employee of a department and reuse the departmentId.

score 1 · Answer 2 · answered Dec 04 '21 at 19:23

As Brandon has said, your data model isn't great. But to answer the question you're actually asking, basically, "How do I capture the just-inserted id value?" you use the RETURNING INTO clause for your first insert:

INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, AGE) 
VALUES(emp_sequence.nextval, :EMP_NAME, :AGE)
RETURNING EMP_ID INTO :x

In PL/SQL this is pretty trivial. To do it through JDBC, this is captured slightly differently, though getGeneratedKeys(). See Oracle's RETURNING INTO usage in Java (JDBC, Prepared Statement)

score 0 · Answer 3 · answered Dec 16 '21 at 05:25

Similar situation we handled as below

Get next sequence value of 'dept_sequence' into a variable, for example X
Use X in parent table as well as child table insert statements. Ensure both inserts are under single transaction scope

This will be clean and easy to manage parallel executions.

Inserting row into multiple table with same sequence number in java

3 Answers3