CODEWITHSUNDEEP
javasql-insert
Scorpion
Scorpion

Reputation: 3976

Insert Query Builder for java

I have a use case where in I need to read rows from a file, transform them using an engine and then write the output to a database (that can be configured). While I could write a query builder of my own, I was interested in knowing if there's already an available solution (library).

I searched online and could find jOOQ library but it looks like it is type-safe and has a code-gen tool so is probably suited for static database schema's. In the use case that I have db's can be configured dynamically and the meta-data is programatically read and made available for write-purposes (so a list of tables would be made available, user can select the columns to write and the insert script for these column needs to be dynamically created).

Is there any library that could help me with the use case?

Upvotes: 1

Views: 2579

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 220762

jOOQ (the library you referenced in your question) can be used without code generation as indicated in the jOOQ manual:

When searching through the user group, you'll find other users leveraging jOOQ in the way you intend

Upvotes: 1

Nick Holt
Nick Holt

Reputation: 34301

If I understand correctly you need to query the database structure, display the result to via a GUI and have the user map data from a file to that structure?

Assuming this is the case, you're not looking for a 'library', you're looking for an ETL tool.

Alternatively, if you're set on writing something yourself, the (very) basic way to do this is:

  • the structure of a database using Connection.getMetaData(). The exact usage can vary between drivers so you'll need to create an abstraction layer that meets your needs - I'd assume you're just interested in the table structure here.
  • the format of the file needs to be mapped to a similar structure to the tables.
  • provide a GUI that allows the user to connect elements from the file to columns in the table including any type mapping that is needed.
  • create a parametrized insert statement based on file element to column mapping - this is just a simple bit of string concatenation.
  • loop throw the rows in the file performing a batch insert for each.

My advice, get an ETL tool, this sounds like a simple problem, but it's full of idiosyncrasies - getting even an 80% solution will be tough and time consuming.

Upvotes: 2

Foredoomed
Foredoomed

Reputation: 2239

The setps you need to do is:

  1. read the rows

  2. build each row into an object

  3. transform the above object to target object

  4. insert the target object into the db

Among the above 4 steps, the only thing you need to do is step 3.

And for the above purpose, you can use Transmorph, EZMorph, Commons-BeanUtils, Dozer, etc.

Upvotes: 0

Related Questions