How can I copy “SHOW TABLES” to Hibernate?

Question

I am trying to iterate over all my tables so that I can trim each one (at the beginning of each of my JBehave tests).

I thought I could:

List<String> allTables = session.createSQLQuery("SHOW TABLES").list();

But hibernate throws an SQLGrammarException, complaining that "TABLE_NAME Column" was not found.

I assume this is because the query “show tables” does not actually return a list of rows. Is there any other way to get a list of all my tables using Hibernate?

Answer 1

Try something like this:

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE();

For columns (same situation with Hibernate) try:

SELECT column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=YOUR_TABLE_NAME

Answer 2

, hibernate.hbm2ddl.auto hibernate.cfg.xml Create.

<property name="hbm2ddl.auto">create</property>

, .

Answer 3

If you still have access to the Hibernate Configuration object, you can do this:

for (Iterator iter=configuration.getClassMappings(); iter.hasNext();) {
  PersistentClass persistentClass = (PersistentClass)iter.next();
  String table = persistentClass.getTable().getName();
  // Code to truncate table (or just use a query with session.executeUpdate)
}

It is assumed that you have one table for each object, and you only care about the tables that are displayed. Otherwise, you probably need to do something with the base connection and DatabaseMetaData, for example:

session.connection().getMetaData().getTables(catalog, schemaPattern, tableNamePattern, types)