I need to retrieve the list of user tables from my database - in native sql I do this:

"SELECT table_schema, table_name, table_type " +
" FROM information_schema.tables " +
" WHERE table_schema = 'public' " +
" AND table_type = 'BASE TABLE'"

When I generate a hibernate query to do the same, it says the table isn't mapped. (see below). I don't have a class that maps to the system meta data. How do I retrieve a list of user tables using hibernate?


Hibernate version: 3.0

Full stack trace of any exception that occurs:
1:47:09,593 ERROR PARSER:35 - *** ERROR: information_schema.tables is not mapped.
11:47:09,593 DEBUG ErrorCounter:28 - information_schema.tables is not mapped.


Name and version of the database you are using: Postgresql 8.1

Since not all databases have system tables, I don't think Hibernate provides this functionality. You could map the tables, then run the queries through Hibernate, but I think it would be better for you to just query the database directly. I'd recommend using plain old JDBC, not Hibernate.

It's really interesting that you suggest this because that is what was going through my mind also and I actually did end up mapping the system table but that is a pain. Now my code has to have another entity class and mapping source for a table that I will never use except to get meta data from. This points up something that I find frustrating about Hibernate. I feel like my application is a mishmash of hibernate queries and native sql because of these types of things that Hibernate can't handle.

Yeah. As much as Hibernate helps, it can really limit what you can do. You could centralize all of your database calls (including Hibernate calls) in DAO objects. Then, the mishmashiness would be contained in those classes.

public class DatabaseTableUtility {
public static DatabaseTable getAll() {
// SQL code
}
public static DatabaseTable getByID(int id) {
// SQL code
}
}

// This one with Hibernate code
public class BusinessObjectUtility {
public static BO getAll() {}
public static BO getByID(int id) {}
public static List<BO> getByDateRange(Date a, Date b) {}
...
}

This way, you can use a collection of data access methods and not worry about it in the rest of your code. Also makes your code cleaner :-)

ha ha - yes, this is how we are doing it right now - the mess is contained in a single package but it's still messy and it bothers me