Hibernate Tips: How to define schema and table names

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.

Get it now!


Related Articles

Responses

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  1. Thanks for sharing this valuable information

  2. What if schema name is dynamic ?

    1. 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 . A probably better option is to bootstrap Hibernate programmatically and set the default schema during that process.

      Regards,
      Thorben

  3. Took me a while to realise that this blog replaced the quote pairs. As displayed (copy/pasted), the @Table annotations do not compile.

  4. 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

    1. You can specify the schema for each entity as described in this article and then define a normal relationship between your entities.

      Regards,
      Thorben

  5. This works for hsql, but does not seem to work for mysql. Any thoughts.

    1. 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

      1. So what is the correct way to have this functionality if using MySQL?

  6. 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?