I'm developing an application with Hibernate3 and Mysql5, and I needed to work with a legacy database. To become things simple I've worked with Hibernate Tools to perform a reverse engineering to create my Entity Beans.
When trying to use one of my entities to perform a persist, I've got the exception below:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax
So I checked the Hibernate generated SQL to understand the problem and I found this statement:
Hibernate: insert into tutorial_db.tutorial_db.tutorial_table (employeeName, entryDate, hoursWorked) values (?, ?, ?)
At the above SQL I noted that my table was accessed by the following way:
tutorial_db.tutorial_db.tutorial_table Looking at that, we can note that the schema name was duplicated. So I changed the @Table attribute annotation from this
@Table(name = "tutorial_table", catalog = "tutorial_db") to this
@Table(name = "tutorial_table", schema = "tutorial_db")
After changing my problem was solved, so there is my question:
Could anyone explain me the difference between using catalog and schema attributes on @Table annotation at my Entity definition?
Thanks in advance
* I've explained with a lot of details so when somebody have the same problem, I think this text could provide enough key words to be used in a search.
I believe it was Sybase that I worked with where we needed to specify a SCHEMA and a CATALOG. Not that that answers the question.
I did find this when searching for a database schema vs. catalog:
A relational database contains a catalog that describes the various elements in the system. The catalog divides the database into sub-databases known as schemas. Within each schema are database objects -- tables, views and privileges.
The catalog itself is a set of tables with its own schema name - definition_schema. Tables in the catalog cannot be modified directly. They are modified indirectly with SQL-chema statements.