Hibernate Tips: How to define schema and table names
Take your skills to the next level!
The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.
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?
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.
@Entity
@Table(name = "author", schema = "bookstore")
public class Author { … }
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.
Author a = new Author();
a.setFirstName("firstName");
a.setLastName("lastName");
em.persist(a);
a = em.createQuery("SELECT a FROM Author a WHERE firstName = `firstName`", Author.class).getSingleResult();
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.
06:27:24,009 DEBUG [org.hibernate.SQL] – insert into bookstore.author (firstName, lastName, version, id) values (?, ?, ?, ?) 06:27:24,022 DEBUG [org.hibernate.SQL] – select author0_.id as id1_0_, author0_.firstName as firstNam2_0_, author0_.lastName as lastName3_0_, author0_.version as version4_0_ from bookstore.author author0_ where author0_.firstName=’firstName’
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.
Hibernate Tips Book
Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.
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.
Thanks for sharing this valuable information
What if schema name is dynamic ?
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
Took me a while to realise that this blog replaced the quote pairs. As displayed (copy/pasted), the @Table annotations do not compile.
Fixed, thanks!
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
You can specify the schema for each entity as described in this article and then define a normal relationship between your entities.
Regards,
Thorben
This works for hsql, but does not seem to work for mysql. Any thoughts.
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
So what is the correct way to have this functionality if using MySQL?
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.
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?
Hi,
you can use a schema element in your mapping configuration as described here: https://thorben-janssen.com/mapping-definitions-jpa-hibernate-annotations-xml/#customizedEntityMapping
Regards,
Thorben