Load text file (id, value) → Perth → Insert to DB (Oracle) (In case of duplicate id, add value in Update)
I would like to create a batch of processing, so please tell me how to process it efficiently.
Assume that the number of operations per process → does not exceed 10,000
Expected Steps
I'm thinking about how to deal with it.
The following SQL events occur:
MERGE INTO USERS
USING DUAL
ON (id='001')
WHEN MATCHED THEEN
UPDATE SET no='002', from_date=TO_DATE('20-07-0100:00:00','YY-MM-DD HH24:MI:SS')
WHEN NOT MATCHED THEEN
INSERT USERS(id, no, from_date) VALUES('02', 't002', TO_DATE('20-07-0100:00', 'YY-MM-DD HH24:MI:SS'));
Read the text file from java and answer the sequence of flows that call the JDBC with the assumption that you can.
If you don't think hard about increasing transactions, you can handle them efficiently.
At most 10,000 transactions, you can ignore the risk that a single transaction overloads the DB.
OK:In other words, this pseudocode is efficient.(try-catch-finally and rollback are omitted)
// Create a connection first and do not auto-commit
Connection conn = DriverManager.getConnection();
conn.setAutoCommit(false);
BufferedReader br = new BufferedReader (new FileReader (new File(hoge)));
String line = br.readLine();
while(line!=null){
String sql=myParser(line); // Create merge statements with your own parser
Statement statement = conn.createStatement();
statement.executeUpdate(sql);
statement.close();
line=br.readLine();
}
conn.commit(); // Last transaction terminated
conn.close();
NG:This pseudocode is not efficient
BufferedReader br = new BufferedReader (new FileReader (new File(hoge)));
String line = br.readLine();
while(line!=null){
String sql=myParser(line); // Create merge statements with your own parser
// Automatically commit by creating transactions line by line
Connection conn = DriverManager.getConnection();
Statement statement = conn.createStatement();
statement.close();
conn.close();
statement.executeUpdate(sql);
line=br.readLine();
}
insert all:If you want efficient data processing, consider using insert all.
insert all
into hoge values (1, 'fuga')
into hoge values(2, 'piyo')
select * from dual;--- The last select statement is not optional
merge?: The merge statement is more efficient than selecting the insert/update or merge statement every time you create sql.
After editing SQL, there is an ORA-00926:missing VALUES keyword error.
*The SQL statement disappeared in the latest edit, so I rolled it back.
The reason for the error is that the INSERT below WHEN NOT MATCHED THEEN has the USERS table name.
The Merge statement omits the table name.
Try rewriting the INSERT line as shown below.
INSERT USERS(id, no, from_date) VALUES('02', 't002', TO_DATE('20-07-0100:00', 'YY-MM-DD HH24:MI:SS'));
Corrected: INSERT(id, no, from_date) VALUES('02', 't002', TO_DATE('20-07-0100:00', 'YY-MM-DD HH24:MI:SS'));
© 2024 OneMinuteCode. All rights reserved.