Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question you like me to answer, please leave a comment below.
Question:
How can I define the name of the database schema and table which will be used to persist my entity?
Don’t want to read? You can watch it here!
Solution:
You can define the schema and table name with the schema and name attributes of the javax.persistence.Table annotation. You can see an example of it in the following code snippet. You just have to add the @Table annotation to your entity class and set the name and schema attributes.
When you now use the entity, Hibernate uses the provided schema and table names to create the SQL statements.
The following code snippet persists a new Author entity and performs a query to get all Author entities with the given first name.
As you can see in the following log output, Hibernate persists the Author entity to the author table in the bookstore database schema and performs the SELECT statement on the same table.
Get this Hibernate Tip as a printable PDF!
Join the free Thoughts on Java Library to get access to lots of member-only content, like a printable PDF for this post, lots of cheat sheets and 2 ebooks about Hibernate.Learn more
If you want to dive deeper into Hibernate’s mapping annotations and other advanced features, you should have a look at my Advanced Hibernate Online Training. I just opened the registration and the early bird discount is available until October 24th.
Hibernate Tips Book
It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching and statically and dynamically defined queries.
Get it now as a paperback, ebook or PDF.
Coder says
Thanks for sharing the tip. Kindly let me know how the hbm.xml file will look for this case. Generally we provide the table name, schema and table column details in the hbm.xml file. Since now as per your tip we have now mentioned the schema name in the java persistence file do we need to provide the schema name in the hbm.xml file?
Thorben Janssen says
Hi,
you can use a schema element in your mapping configuration as described here: https://thoughts-on-java.org/mapping-definitions-jpa-hibernate-annotations-xml/#customizedEntityMapping
Regards,
Thorben
Cheenu says
This works for hsql, but does not seem to work for mysql. Any thoughts.
Thorben Janssen says
Hi,
I haven’t tried this on a MySQL but it’s probably because MySQL doesn’t provide real schema support. For MySQL, a schema is just a synonym for a database.
Regards,
Thorben
Tim says
So what is the correct way to have this functionality if using MySQL?
Tim says
If using MySQL, the correct way is to use @Table(name=”schema.table_name”), but you also have to make sure that you do not define a default database/schema in your data source url configuration. See https://stackoverflow.com/questions/47296766/spring-boot-jpa-does-not-prepend-schema-name-to-tables-in-query/47336516#47336516.
sohal amin says
Hibernate configuration for the multi schema and spring
I need to configure Hibernate with mysql and want to create two schema with relation. I used hibernate 5.2 version.
For ex.
Schema1 — Table1 — Table2
Schema2 — Table1 — Table2
Schema1.table has relation with schema2.table1.
How can I achieve this with hibernate and what kind of configuration I need to create. Which kind of notation I need to use with pojo.
Thanks
Thorben Janssen says
You can specify the schema for each entity as described in this article and then define a normal relationship between your entities.
Regards,
Thorben
jgabrielehay says
Took me a while to realise that this blog replaced the quote pairs. As displayed (copy/pasted), the @Table annotations do not compile.
Thorben Janssen says
Fixed, thanks!
Sanket says
What if schema name is dynamic ?
Thorben Janssen says
Are all your tables located in the same schema?
If that’s the case and you can adapt the configuration of your persistence layer, you could set the default schema in your persistence.xml with this property
Regards,
Thorben