hibernate - return insert script from a query result

Question

Is there an existing library/framework that I can leverage on that will allow me to generate an INSERT mysql scripts from a query result.

For example, in HQL I have:

SELECT book FROM book b WHERE b.created >= :today

Instead of a List<Book>, what I'll have are the texts:

INSERT INTO book VALUES (...)

Or anyway I can convert list of hibernate entity into a mysql script INSERT INTO script.

Any assistance will be appreciated.

Thank you!

Vlad Mihalcea · Accepted Answer · 2014-10-17 03:54:10Z

1

You could write a SELECT query like this:

SELECT 'INSERT INTO book (id, name, isbn) VALUES ('|| b.id ||','|| b.name ||','|| b.isbn ||');' FROM book b;

You need to pay attention to VARCHAR column, because the values need to be escaped with quotes.

answered Oct 17, 2014 at 3:54

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

this looks good, thanks, though it's returning null, but when I use concat() it works fine.

For null values you can use NULLIF.

kums · Accepted Answer · 2014-10-17 04:51:16Z

0

You can use mysqldump for this:

mysqldump -u username -p dbname book --compact --no-create-info --where="created >= '2014-10-01'" > dump.sql

This will generate the INSERT INTO statements for table rows matching the WHERE clause book.created >= '2014-10-01'

answered Oct 17, 2014 at 4:51

kums

2,7012 gold badges16 silver badges16 bronze badges

I'll be needing it via java code. And I need to not the whole database, I just want the create statements for the result of my query.

It will return only the selected rows of a single table

sorry missed that you have a where clause. but i'll be needing it via java code where the return will be a List<String> insertStatements