Geek Answers Handbook

Insert / update a row based on its availability in the database

This is a common scenario, but I wanted to find out how it is a performance-optimized method and best practice .

I have a table with 4 columns: id, name and two other fields. Id is the PC, and the name is a unique key. I read the data from the excel file, fill in the values ​​of each row in the Domain object, and then save them. When saving, I want to see if the record already exists for the same name, and if it exists, I want to update it. Else will save it as a new entry.

I can do this with the usual select query for the name and check the null value and based on this insert or update, but I have thousands of lines that need to be read from excel files, and the required unnecessary functional requirement is performance .

So, please advise me which way is best to deal with this Senario? I haven't started coding my part of the persistence layer yet, so I can switch to ORM or regular jdbc as per your suggestion.

Edited: If I use the name as a primary key, then I think I can use saveOrUpdate or merge from ORM to completely fill my need. Is that a good idea ??? Thanks and greetings, Prasath.

+5
source share
2 answers

, / , .

, Oracle, .

: Excel CSV (/mydatadir/mydata.csv), Oracle :

create or replace directory data_dir as '/mydatadir/';
create table external_table (
  id number(18),
  name varchar2(30),
  otherfield1 varchar2(40),
  otherfield2 varchar2(40))
organization external (
  type oracle_loader
  default directory data_dir
  access parameters
  ( fields terminated by ',' )
  location ('mydata.csv')
)

( , )

:

merge into yourtable t
using external_table e
on t.name = e.name
when matched then
   update set t.id = e.id, 
              t.otherfield1 = e.otherfield1, 
              t.otherfield2 = t.otherfield2
when not matched then
   insert (t.id, t.name, t.otherfield1, t.otherfield2)
   values (e.id, e.name, e.otherfield1, e.otherfield2)

yourtable Oracle, .

EDIT:

merge JDBC ( Spring SimpleJdbcTemplate)

EDIT2:

MySQL :

insert into yourtable (id, name, otherfield1, otherfield2)
values (?, ?, ?, ?), 
       (?, ?, ?, ?), 
       (?, ?, ?, ?) --repeat for each row in the Excel sheet...
on duplicate Key update
set otherfield1 = values(otherfield1),
    otherfield2 = values(otherfield2)

JDBC , , () . 1 JDBC 100 Excel . ( UNIQUE , , , -).

MySQL , , , , . csv , .

+3

, excel.

Set dbSet=//fill it from SQl query;
Set newSet//fill it from the file;

newSet.removeAll(dbSet); //left non existing ones to be inserted.

originalNewSet ( )

originalNewSet.removeAll(insertingSet); //left records to be updated.
0

More articles:


All Articles