Automatic substitution of parameters in PreparedStatement at Insert?

Question

Oracle

Greetings gentlemen!
I’ll make a reservation right away: in Java, and in general in programming, I am a beginner.

Introductory part:

In the presence of a huge number of xml files (tens of terabytes).
As a relational representation, one file is split into 90 tables.

In Java, insertion happens with a PreparedStatement with parameter substitution.
To minimize performance issues at this point,
all PreparedStatements are pre-generated as a Map<String,PreparedStatement>, where string is the name of the table.
XML parsing happens with the help of XMLStreamReader.
The logic of work consists in the following:
— Stumbled upon the data.
- Inserted a field into the corresponding PreparedStatement - pstmt.setXXX();
- We came across a closing tag, we execute execute.
- and so on

Example DML:

insert into t1 (id, p2, p2, p4...p19) values (?,?,?,?,?...?)<br>

Problem:
Not all xml files contain all tags, and accordingly all fields.
and when I do pstmt.execute (further planned executeBatch ), an error like " Missing IN or OUT parameter at index:: 2 " occurs.
That is, he swears at the lack of parameters.

Question:
Actually, how to force it to substitute NULL into empty parameters automatically?

If this is not possible, it is probably worth revisiting the architecture.
Use solutions like server side PL/SQL or dynamically generate a PreparedStatement or whatever.
Initially, nothing like this was planned due to the linearity of loading and the emphasis on performance.

Answer 1

there are several options for solving the problem
1) use existing ORM frameworks (see other answers)
2) write a "converter" that will convert data to a format that is understood by the standard data importers of your DBMS
3) write your own framework / data importer (smells like a bicycle, but can give a performance and flexibility bonus). the complexity of the implementation directly depends on the form in which the data is stored in XML files, because it is necessary to bind the xml elements to the fields of the tables in the subd. this option should be considered only if the creation of such mappings is easy to do manually or can be automated

Answer 2

Apparently, it's still worth revisiting the architecture . Apply Hibernate. This will also solve the current problem - Hibernate will generate the Statement itself and fill in the fields correctly, and as a bonus, it will transparently do the work “executeBatch was planned” for you. At the beginning of this long article of mine, there are a couple of paragraphs about how Hibernate itself optimized everything when batch loading, without any direct indications. The situation is almost one-on-one with yours - loading data from a text file into the database, also with caching of Statements. Hibernate made everything an order of magnitude faster.

Answer 3

Um, there’s no way to “force to substitute”, how is he supposed to guess which ones are missing? Why not just manually insert zeros (setNull(index, class/type), emnip), for parameters which are not currently available.