How can I replicate "SHOW TABLES" in Hibernate?

Question

I'm trying to iterate over all of my tables so I can truncate each one (at the beginning of each of my JBehave tests).

I thought I would be able to:

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

But hibernate throws a SQLGrammarException, complaining that "Column 'TABLE_NAME' not found."

I guess this is because the "show tables" query doesn't actually return a list of strings. Is there another way to get a list of all my tables using Hibernate?

Answer 1

if you want truncate all of your table you can set hibernate.hbm2ddl.auto in hibernate.cfg.xml by value Create.

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

but if you want just get all table name and truncate some of them this method don't work.

Answer 2

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)
}

That assumes you have one table per entity and you only care about the tables that are mapped. Otherwise, you probably need to do something with the underlying connection and the DatabaseMetaData, e.g.:

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

Answer 3

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