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
January 11, 2017 at 2:17 pmThanks 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
July 27, 2018 at 7:20 amHi,
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
August 8, 2017 at 1:09 pmThis works for hsql, but does not seem to work for mysql. Any thoughts.
Thorben Janssen says
August 8, 2017 at 1:53 pmHi,
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
November 15, 2017 at 5:55 pmSo what is the correct way to have this functionality if using MySQL?
Tim says
November 16, 2017 at 7:17 pmIf 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
October 14, 2017 at 4:10 pmHibernate 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
July 27, 2018 at 7:16 amYou 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
December 11, 2017 at 9:45 pmTook 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
December 29, 2017 at 5:06 pmFixed, thanks!
Sanket says
July 26, 2018 at 9:25 pmWhat if schema name is dynamic ?
Thorben Janssen says
July 27, 2018 at 7:14 amAre 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