• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Please critique my table load approach

 
Ahsan Bagwan
Ranch Hand
Posts: 254
1
Java MySQL Database Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We've a requirement to load the MySQL tables through front end, given an excel file. For this most of the groundwork seems to have been done like uploading the file and reading the sheet in Java.

After this, we would like to form the insert statements from the data already parsed and subsequently process the query after adding all of it to the batch. However here we would like to send the user the updateCount[] array calculated statistics if all the insert statements are successful.

But for some reason, if the insert statements fail in the middle of the processing, we like to send the update statistics for successful records but at the same time give the user a friendly error message. Also, output the read Java rows that were not added to the db to a file so that the user can enter it again.

So I would like absolutely appreciate constructive advice regarding the steps and how to improve it if not a feasible one.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Could you load the data from a CSV file into one or more temporary tables? Then you can do the rest of the job in SQL, with a thin Java wrapper. This would be more efficient and maintainable, especially if you are dealing with significant volumes of data, as you can select multiple rows from your temporary table and insert them into your target table with a single SQL statement instead of processing each record individually via Java.
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all
I have tried to use Chris' logic[Please confirm my understanding of the mentioned logic is right] couple of years back.
I parsed the uploaded excel provided by user having 3000 odd rows on an average, inserted data in temp table by
Then in the same java method i did a
Just wanted to ask whether my approach is OK, because its taking significant time and the user is getting frustrated.

NOTE:Please do not consider the time taken to parse the excel. I just wanted to know the correct approach post-parsing.
 
K. Tsang
Bartender
Posts: 3583
16
Android Java
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Uploading the file takes time in itself. Plus processing it in Java takes further time. Not to mention the UI screen may just hang waiting for the process to complete.

If I were you, I would do the actual upload in a background thread then call a store procedure to insert data. When everything is done (success or fail) provide a link to some report page to show to the user the results.

About inserting data, such uploaded data should follow a "all or nothing" approach. If one record in the file has problem the entire file is rejected. This way the end user who's prepared the data doesn't need to take rows out messing up the "number" of records intended.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you can export your Excel data to CSV files, then you should able to load it straight into your temporary database table using the MySQL load syntax without using Java at all. Once the data is in the database, you can query it and move it around using SQL. Depending on what additional validation/processing you need to do, you might be able to move all your data into your target table in a single SQL statement. That's what SQL is for, after all.
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
sorry for the long gap
Thanks a lot Chris and Tsang
you provided some base to rework my logic.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic