Inserting row into multiple table with same sequence number in java-CodePudding

I have to insert data into two tables EMPLOYEE and DEPARTMENT one by one through java code. Each table has one column EMP_ID which is primary key in EMPLOYEE table and foreign key in DEPARTMENT table and refers from EMP_ID column of EMPLOYEE table. there is one sequence emp_sequence defined on EMP_ID in EMPLOYEE table.

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

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

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


Map<String, Object> deptData = ImmutableMap.builder()
        .put("EMP_NAME", "John")
        .put("DEPT_NAME", "MECH")
        .put("LOCATION", "PARIS")
        .build();
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)";
jdbcTemplate.update(insertToDeptSql , deptData);

It works perfectly fine when there is one single transaction at a time. both the tables have correct EMP_ID values but it breaks in multi transactional environment. the DEPARTMENT table do not receive same value of emp_sequence which is inserted in EMPLOYEE table for one transaction. emp_sequence value is increased by a different transaction which might be happening in a differeny system and DEPARTMENT table always receive some increased value of sequence for same employee.

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

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

CodePudding user response：

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.

CodePudding user response：

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)