Get the database backup/dump with EclipseLink

Question

Is there a way of creating the Database Dump / Backup through EclipseLink entity manager? I want to add a functionality to my java (dropwizard) project that allows the administrator to download the current database (and perhaps import a new one in). I am using H2 Database.

Thanks

Answer 1

this is fairly easy for you to do, however I don't think the EntityManager does that

(This is not 100 % true - but I could not work out how, so I am presenting you the straight forward alternative that you can use with the EM as well)

So requisites:

You need to have the H2DB and you need to bind your datasource (or unwrap a connection from your entity manager). Then the command you want to run is:

SCRIPT TO '/path/to/my/file'

So for example, this is how I implemented it:

    @Inject
    private DataSource ds;

    @GET
    @Path("dump")
    public Response dump() {


        try (Connection c = ds.getConnection(); Statement s = c.createStatement()) {

            s.execute("SCRIPT TO '/home/artur/tmp/test/test.sql'");

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return Response.ok().build();
    }

So, a few points:

You don't need to access the datasource directly. A quick google revealed that eclipselink will give you access to your connection like this:

entityManager.getTransaction().begin();
java.sql.Connection connection = entityManager.unwrap(java.sql.Connection.class);
...
entityManager.getTransaction().commit();

Alternatively, this will also work (And does not use any connection/jdbc attributes):

emp.get().createNativeQuery("SCRIPT TO '/home/artur/tmp/test/test.sql'").getResultList();

I don't know exactly what you want to do, but you could:

• Pass the filename into the resource so your DBA knows where the dump is created?
• Write to a temp location, read the dump and return a stream (for download)

I don't think that you can get h2db to return the dump for you. However I might be wrong.

EDIT: I am wrong

The entityManager method will return a result list with the entire SQL dump. So you have it in the code and in a file (you could remove your file).

Just try it and see what the debugger spits out. Like I said, I am not using EclipseLink so the behaviour might be slightly different.

Have fun, I hope that solves your question :)

Artur