JPA Entity Manager - How to run SQL script file?

Question

I have an SQL script file which drops and recreates various tables as well as inserts various records into these tables. The script runs fine when executing in the SQL query console however I need it to be executed by the Entity Manager.

Any idea's on how I would be able to do this?

Thanks,

H

what is your main problem? you want to run `SQL Script File` programmatically? How `EntityManager` run `SQL Script File`? — Zaw Than oo, Jul 25 '14 at 02:51
Yes, so I have a .sql file containing a load of SQL queries. I want to be able to run this file programmatically. — Harvey Sembhi, Jul 28 '14 at 09:50
http://stackoverflow.com/questions/1044194/running-a-sql-script-using-mysql-with-jdbc — Zaw Than oo, Jul 28 '14 at 10:05

score 2 · Answer 1 · answered Aug 10 '17 at 18:41

Late to the party but here's how I do it. Couple of things to note here:

My SQL file ("sql-queries.sql") is on the classpath - you could do this any other way that will get you an input stream...
My SQL file has 1 statement per line
I'm manually beginning/committing transactions, one for each line/statement in the file

Here's the method to execute the file:

void executeStatements(BufferedReader br, EntityManager entityManager) throws IOException {
    String line;
    while( (line = br.readLine()) != null )
    {
        entityManager.getTransaction().begin();
        entityManager.createNativeQuery(line).executeUpdate();
        entityManager.getTransaction().commit();
    }
}

Here's how I call it:

        InputStream sqlFileInputStream = Thread.currentThread().getContextClassLoader()
                .getResourceAsStream("geo-data.sql");

        // Convert input stream to something that can be read line-by-line

        BufferedReader sqlFileBufferedReader = new BufferedReader( new InputStreamReader(sqlFileInputStream));
        executeStatements(sqlFileBufferedReader, dao.getEntityManager());

I tested nominally with 1 transaction instead of 1 per statement (note that this means that 1 bad query will break everything) and the time to execute is the same:

void executeStatements(BufferedReader br, EntityManager entityManager) throws IOException {
    String line;
    entityManager.getTransaction().begin();
    while( (line = br.readLine()) != null )
    {
        entityManager.createNativeQuery(line).executeUpdate();
    }
    entityManager.getTransaction().commit();
}

JPA Entity Manager - How to run SQL script file?

1 Answers1

Linked