CODEWITHSUNDEEP
javasqloracle-databasespring-boot
Rushali Atre
Rushali Atre

Reputation: 83

Inserting row into multiple table with same sequence number in java

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.

Upvotes: 1

Views: 1142

Answers (3)

Krishna
Krishna

Reputation: 473

Similar situation we handled as below

  1. Get next sequence value of 'dept_sequence' into a variable, for example X
  2. 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.

Upvotes: 0

eaolson
eaolson

Reputation: 15094

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)

Upvotes: 1

Brandon
Brandon

Reputation: 10028

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.

Upvotes: 2

Related Questions