asked on

Vert.x - Preserve row order in bulk insert using JDBC driver

I am trying to do bulk insert in MSSQL database using vertx java and for some reason the insertion is not being done in the table in the same order I arrange the data in the List. It is very important for my business logic to have this order in the database.In the console it shows the order correctly but when I view it in the database the order is all mixed up.

public void insertChildUid(SortedSet<String> goodReadSet, Integer serialID) {
      LOG.debug(" serialID "+serialID);
      List<JsonArray> batch = new ArrayList<JsonArray>();
      
      int childSetSize = goodReadSet.size();
      LOG.debug(" childSetSize "+childSetSize);
      String[] goodReadArray = new String[childSetSize];
      goodReadArray = goodReadSet.toArray(goodReadArray);
      
      for (int i=0; i < childSetSize; i++) {
         String[] puidAndUid = new String[1];         
         puidAndUid = goodReadArray[i].split(":");
         String puid = puidAndUid[0];
         String uid = puidAndUid[1];

         batch.add(new JsonArray().add(uid).add(puid).add(serialID));
         //LOG.debug("counter "+i+" List<JsonArray> data "+batch);
         
      }
      String sql = "INSERT INTO serial"
                  + "   (uid, fnc, serialorderid)"
                  + " VALUES(?, ?, ?) ";
      
      LOG.debug("SQL QUERY :"+formatQuery(sql, batch));
      
      
      
      dbClient.getConnection(conn->{
            if(conn.failed()){
               LOG.error("Can't get Vertx connection",conn.cause());
            } else{
               LOG.debug(" Vertx connected for BATCH INSERT!!!!!!!!!");
               
                final SQLConnection connection = conn.result();                          
                connection.batchWithParams(sql,batch,(AsyncResult<List<Integer>> res) -> {
                if (res.succeeded()) {
                   List<Integer> result = res.result();
                   LOG.debug("result : "+result.size());
                   //LOG.debug("BATCH INSERT COMPLETE!!!!!!!!!");
                   LOG.debug(" BATCH INSERT COMPLETE FOR PUID!!!!!!!!! "+batch.get(0).getString(1));
                 } else {
                   // Failed!
                    LOG.debug("Failed! : "+res.cause());
                 }
               });
                connection.close();
            }
        });
      
   }

Open in new window

Below is my console output
*

11:41:04.569 [vert.x-eventloop-thread-1] DEBUG tntmanual.server.dao.SerialOrderDAO - childSetSize 10

11:41:04.570 [vert.x-eventloop-thread-1] DEBUG tntmanual.server.dao.SerialOrderDAO - SQL QUERY :INSERT INTO serial (uid, fnc, serialorderid) VALUES([["11980005339800793304","11980180231000164186",72], ["11980013237900080767","11980180231000164186",72], ["11980018018100688258","11980180231000164186",72], ["11980018208300933667","11980180231000164186",72], ["11980018213800239089","11980180231000164186",72], ["11980018531200237597","11980180231000164186",72], ["11980023406000938288","11980180231000164186",72], ["11980027390300806466","11980180231000164186",72], ["11980028239900198090","11980180231000164186",72], ["11980028320100716616","11980180231000164186",72]], {1}, {2})

$$$$$$$$ 11980183084300807471 $$$$$$$$$$$

11:41:04.571 [vert.x-eventloop-thread-1] DEBUG tntmanual.server.dao.OrderDAO - arraySize 5

11:41:04.572 [vert.x-eventloop-thread-1] DEBUG tntmanual.server.dao.OrderDAO - arraySize+childCounter 1510 Iterating over set: 5 CONSTR SerialOrderDAO!!

11:41:04.572 [vert.x-eventloop-thread-1] DEBUG tntmanual.server.dao.SerialOrderDAO - serialID 72

11:41:04.572 [vert.x-eventloop-thread-1] DEBUG tntmanual.server.dao.SerialOrderDAO - childSetSize 5

11:41:04.572 [vert.x-eventloop-thread-1] DEBUG tntmanual.server.dao.SerialOrderDAO - SQL QUERY :INSERT INTO serial (uid, fnc, serialorderid) VALUES([["11980053655800913678","11980183084300807471",72], ["11980080273700614629","11980183084300807471",72], ["11980082028600001574","11980183084300807471",72], ["11980082222200939079","11980183084300807471",72], ["11980088238100084516","11980183084300807471",72]], {1}, {2})

11:41:04.572 [vert.x-eventloop-thread-1] DEBUG tntmanual.server.dao.OrderDAO - serials are created and saved in database

11:41:04.573 [vert.x-eventloop-thread-1] DEBUG tntmanual.server.dao.SerialOrderDAO - Vertx connected for BATCH INSERT!!!!!!!!!

11:41:04.576 [vert.x-eventloop-thread-1] DEBUG tntmanual.server.dao.SerialOrderDAO - Vertx connected for BATCH INSERT!!!!!!!!!

11:41:04.585 [vert.x-eventloop-thread-1] DEBUG tntmanual.server.dao.SerialOrderDAO - result : 5

11:41:04.585 [vert.x-eventloop-thread-1] DEBUG tntmanual.server.dao.SerialOrderDAO - BATCH INSERT COMPLETE FOR PUID!!!!!!!!! 11980183084300807471

11:41:04.585 [vert.x-eventloop-thread-1] DEBUG tntmanual.server.dao.SerialOrderDAO - result : 10

11:41:04.585 [vert.x-eventloop-thread-1] DEBUG tntmanual.server.dao.SerialOrderDAO - BATCH INSERT COMPLETE FOR PUID!!!!!!!!! 11980180231000164186*

CEHJ

The resulting order of the inserted data is likely to be determined by the column indexes. What are they?

Zolf

ASKER

Thank you for your comments. What do you mean. If you meant the DB table, I do not have index defined for this table.

CEHJ

No primary key?

SHOW INDEX FROM yourDatebaseName.serial WHERE Key_name = 'PRIMARY';

Open in new window

If you want to be able to see insertion order, you'd be better off adding an appropriately named column that will contain a timestamp of NOW

Zolf

ASKER

Thanks again for your help. here is the DDL of my table

CREATE TABLE serial (
   id bigint NOT NULL IDENTITY(1,1),
   uid nvarchar(20),
   fnc nvarchar(56),
   serialorderid bigint NOT NULL,
   CONSTRAINT PK__serial__3213E83F843DE058 PRIMARY KEY (id),
   CONSTRAINT FK__serial__serialor__3A81B327 FOREIGN KEY (serialorderid) REFERENCES tntmanual.dbo.serialorder(id) ON DELETE RESTRICT ON UPDATE RESTRICT
) go;

Open in new window

Zolf

ASKER

I also tried with adding a column called dateInserted which shows the time the insert was performed but still no luck. as you can see the fnc column data is not saving them(highlighted values) together

CEHJ

Yes, so your data will be sorted by default by its primary key.

SELECT id, uid, fnc, serialorderid, dateInserted FROM serialdata ORDER BY dateInserted

Open in new window

if you want inserted order
You can add in fnc to that ORDER BY if you're interested in that

Zolf

ASKER

You see, i want to preserve the order while Inserting in database not while Select

CEHJ

Why? You realise what you're looking at is already a 'fictional' view of quite a different reality? It's not like appending rows into a csv file.

The only way that would happen is if there were no keys/indexes on the table

Zolf

ASKER

Thanks again for your comments. So, from what I understand is it is not in my control to control the Batch INSERT from my backend into the DB.

ASKER CERTIFIED SOLUTION

CEHJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zolf

ASKER

Oh ic. Thanks.

CEHJ

Zolf

ASKER

He is very helpful and his knowledge is very good in Java