I have a couple SQL tables say
table1
___________________________ _ _ _ _ _
|id | date | col1 | col2 |col3 |col4 ...
--------------------------------------
|1 | 1/11 | | | |
____________________________ _ _ _ _ _
and
table2
_________________________
|id | date | col1 | col2 |
--------------------------
|1 | 1/11 | ice | cone |
I want the ability to merge data from table2 into table1 based on the id and date - if the id and date match - then the data (col1 and col2) is copied over from table2 onto table1.
I have the SQL query to make this work
// merge_query1:
MERGE table1 t1
USING table2 t2
ON (t1.id = t2.id AND t1.date=t2.date )
WHEN MATCHED
THEN UPDATE SET
t1.col1 = t2.col1,
t1.col2 = t2.col2
;
now the question is how do I execute this using Spring JPA.
I was thinking something along the lines of writing a JPA Interface like so.
// How would I design this or something like this ?
// What pojo should be put instead of the question mark in JpaRepository<?, .. >
public interface MergeTables extends JpaRepository<?, Long> {
@Query(value = MERGE_TABLE2_WITH_TABLE1, nativeQuery = true)
void executeMergeTableQuery();
}
where the MERGE_TABLE2_WITH_TABLE1
String is
public static String MERGE_TABLE2_WITH_TABLE1 = "MERGE table1 t1
USING table2 t2
ON (t1.id = t2.id AND t1.date=t2.date )
WHEN MATCHED
THEN UPDATE SET
t1.col1 = t2.col1,
t1.col2 = t2.col2
;
";
So the question is How would I design so that the merge query is executed upon calling the above function or is there another way of doing something like this?
CodePudding user response:
Added
@Modified
and @Transactional
did the trick.