Why is Hibernate generating this SQL query?

Question

I'm using Hibernate and a MySql server. I use multiple databases as "namespaces" (e.g. users, transactions, logging etc.).

So, I configued Hibernate to NOT connect to a particular database :

url = jdbc:mysql://127.0.0.1/

The databases where tables are located are defined in the hbm files through the catalog attribute :

<class name="com.myApp.entities.User" table="user" schema="" catalog="users"> ...

When I want to load some data, everything works fine and Hibernate seems to generate the expected SQL queries (by using the catalog prefix in the table names) e.g. :

select id from users.user

However, when I try to add a new record, Hibernate don't use the from [catalog].[table_name] syntax anymore. So I get a MySQL error 'No database selected'.

select max(id) from user

Hibernate is trying the get the future id to create a new record, but it doesn't specify in which database is located the table, it should be :

select max(id) from users.user

Why is Hibernate generating this invalid query ? Have someone ever experienced this problem ?

Answer 1

You need to specify the schema for the generator. See this question on SO for a more detailed answer.