• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

What is the best way to store List<List<String>> in database?

 
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

Requirement: To store List<List<String>> in database. Here String represents the value fetched from excel file and List<String> represents a row containing cells in excel.
List<List<String>> represents all the records from a excel file.
There can be lacs of records in the excel data file.

Now, the data of this file has to be persisted into database without performance overhead. I have stored data of the file in List<List<String>> object. Table structure in which data needs to be stored is same as that of excel data file.
We can do a batch update for insert queries but since data size is more, I don't find it efficient.

If we can do this using some other technique like JPA or Hibernate or directly saving the file in database table with some kind of mapping between file columns and table columns, please let me know.

Thanks in advance.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The "best" way? Depends on your data and your database.

With Oracle, the fastest and most flexible way to do this with significant volumes of data is probably by loading the data directly from a CSV file into the database using an external table.

The general approach would be:

  • set up an Oracle directory definition in the database (one-off task)
  • copy your CSV file to the physical directory corresponding to the Oracle directory definition
  • create your external table
  • copy the data from the external table to a "real" table

  • Once you've set this up, it will be way faster than any row/cell-based process you can come up with in Java or Hibernate (e.g. I loaded 2.5 million rows of 60 columns on a slow machine in about 30 seconds the other day). The external table will end up with the same basic structure as your CSV file (the CSV header row will appear in the first row of the table if you don't exclude it from the loading process), but it's usually better to copy the data into a "real" table because it's more flexible e.g. you can add more data later, and it allows more performance optimisation e.g. use of indexes etc.

    But if you're not using Oracle, or you can't use a CSV file, you'll need some advice from a Java expert instead.

    Good luck!

     
    Don't get me started about those stupid light bulbs.
    reply
      Bookmark Topic Watch Topic
    • New Topic