How to run an imported sql script in JPA using H2?

Question

I am developing a JPA application which uses H2 for the database. I want to create a backup (export as sql file) and import a backup from a webinterface.

The creation of the backup sql file and the upload of the sql file is working but I can't get the entityManager to properly execute the imported sql file.

I tried creating a file from the imported sql and then running it as follows:

public void applyBackup() {
    em.flush();
    Query query = em.createNativeQuery("DROP ALL OBJECTS");
    query.executeUpdate();

    query = em.createNativeQuery("RUNSCRIPT FROM 'backup.sql'");
    query.executeUpdate();
}

The problem here is, that the file I created from the imported sql is located in the server folder, while the RUNSCRIPT query tries to run it from the project folder.

My second attempt is parse the sql into a string and then executing this as a sql command:

  public void applyBackup(String sqlStr) {
    em.flush();
    Query query = em.createNativeQuery("DROP ALL OBJECTS");
    query.executeUpdate();

    query = em.createNativeQuery(sqlStr);
    query.executeUpdate();
}

This doesn't throw an exeception during execution but when I try to get data out of the database, it says that table xy is not found.

To create the backup I use the following query:

  Query query = em.createNativeQuery("SCRIPT TO 'backup.sql'");

As described here.

seems like you put the same code for importing sql and parsing it to string by mistake. — Dmitrii Bocharov
– Dmitrii Bocharov, Commented Feb 28, 2020 at 14:21
also it seems a duplicate of stackoverflow.com/questions/14358425/… — Dmitrii Bocharov
– Dmitrii Bocharov, Commented Feb 28, 2020 at 14:24
I edited my post. The solution in your suggested duplicate raises an SQLGrammarException. — hans
– hans, Commented Feb 28, 2020 at 14:38
If you have your backup as a File, you could try: RUNSCRIPT FROM ' + file.getAbsolutePath() + '? — areus
– areus, Commented Feb 28, 2020 at 15:35
The file is found using the absolute path but when trying to load the data from the database it says that table .. is not found. Interestingly the backup.sql in my resources folder, which is created automatically during the "SCRIPT TO" query works. The problem is, that I dont know how I can create an sql-file in my resources folder from the imported file and I also don't know if this is a viable solution when using the war file to run the program. — hans
– hans, Commented Feb 28, 2020 at 16:05

hans · Accepted Answer · 2020-02-28 18:58:14Z

0

I parsed the UploadedFile file's InputStream with a Scanner, which messed up the SQL.

answered Feb 28, 2020 at 18:58

hans

154 bronze badges

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

Collectives™ on Stack Overflow

How to run an imported sql script in JPA using H2?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related