Best way to truncate all tables with hibernate?

Question

I would like to truncate all my database tables between one integration test to another. What is the best way to do this using hibernate?

Currently I'm doing this:

public void cleanDatabase() {
        doWithSession(new Action1<Session>() {
            @Override
            public void doSomething(Session session) {
                SQLQuery query = session.createSQLQuery("truncate table stuff");
                // todo - generify this to all tables
                query.executeUpdate();
            }
        });

(doWithSession is a small wrapper that creates and closes a session). I could iterate on all my mapped objects using reflection ... I'm wondering if someone already solved this problem.

Answer 1

I guess you probably don't use Spring. If you did, Spring's transactional test support would be ideal.

In short: Spring automatically starts a transaction before each test case and automatically rolls it back after the test case, leaving you with an empty (or at least unchanged) database.

Perhaps you could just mimic that mechanism:

Open a transaction in a @Before method, roll it back in an @After method.

Answer 2

I wrote an integrator exactly for this purpose. Basically, we hook into the session factory creation flow, iterating over the table mappings found by the Hibernate, and the execute TRUNCATE TABLE xxx for each table. Since we couldn't truncate tables with foreign key constraints, foreign key checks disabled before truncation operation and then re-enabled.

static final class TruncatorIntegrator implements org.hibernate.integrator.spi.Integrator {

    @Override
    public void integrate(Metadata metadata,
                          SessionFactoryImplementor sessionFactory,
                          SessionFactoryServiceRegistry serviceRegistry) {
        try (Session session = sessionFactory.openSession()) {
            session.doWork(connection -> {
                try (PreparedStatement preparedStatement = connection.prepareStatement("SET FOREIGN_KEY_CHECKS = 0;")) {
                    preparedStatement.executeUpdate();
                    System.out.printf("Disabled foreign key checks%n");
                } catch (SQLException e) {
                    System.err.printf("Cannot disable foreign key checks: %s: %s%n", e, e.getCause());
                }

                metadata.collectTableMappings().forEach(table -> {
                    String tableName = table.getQuotedName();
                    try (PreparedStatement preparedStatement = connection.prepareStatement("TRUNCATE TABLE " + tableName)) {
                        preparedStatement.executeUpdate();
                        System.out.printf("Truncated table: %s%n", tableName);
                    } catch (SQLException e) {
                        System.err.printf("Couldn't truncate table %s: %s: %s%n", tableName, e, e.getCause());
                    }
                });

                try (PreparedStatement preparedStatement = connection.prepareStatement("SET FOREIGN_KEY_CHECKS = 1;")) {
                    preparedStatement.executeUpdate();
                    System.out.printf("Enabled foreign key checks%n");
                } catch (SQLException e) {
                    System.err.printf("Cannot enable foreign key checks: %s: %s%n", e, e.getCause());
                }
            });
        }
    }

    @Override
    public void disintegrate(SessionFactoryImplementor sessionFactory,
                             SessionFactoryServiceRegistry serviceRegistry) {
    }
}

Usage: We have to use this Integrator in session factory creation flow, and also need to create a new session factory for each test.

BootstrapServiceRegistry bootstrapServiceRegistry = new BootstrapServiceRegistryBuilder().applyIntegrator(new TruncatorIntegrator()).build();
StandardServiceRegistry registry = new StandardServiceRegistryBuilder(bootstrapServiceRegistry).build();
SessionFactory sessionFactory = new Configuration().buildSessionFactory(registry);

Answer 3

Have you looked at in memory mysql http://dev.mysql.com/downloads/connector/mxj/? also is it not possible to rollback after each test? I believe you can configure it like so.

Answer 4

You could probably drop and recreate the Hibernate schema using SchemaExport, although that seems pretty heavy-handed. Rolling back a transaction sounds like a better idea.

Answer 5

You could use a in memmory database and drop the complete database between your tests.

You can use that way if you have not so many but long tests.

But be aware that every database behaves a bit different then all other. So using an in memory database (for example HyperSQL) will not behave exactly like your normal data base in some cases - so it is not a correct integration test.