How to use hibernate.default_schema and hibernate.default_catalog

Question

I am using Mysql and earlier in my hibernate configuration file I mentioned

<property name="hibernate.connection.url">jdbc:mysql://localhost/TestDB</property>

as the connection url where TestDB is the schema I am connecting to.

I want to specify the default schema in configuration file as

<property name="hibernate.connection.url">jdbc:mysql://localhost</property>
<property name="hibernate.default_schema">TestDB</property>

but it is not working in this way and It gives me an error saying that

java.sql.SQLException: No database selected

Can anyone help me with an example of how to use hibernate.default_schema , hibernate.default_catalog in hibernate configuration file?

Does this answer your question? [How to set up default schema name in JPA configuration?](https://stackoverflow.com/questions/2737420/how-to-set-up-default-schema-name-in-jpa-configuration) — Alexandru Somai, Feb 28 '20 at 09:07
@AlexandruSomai , In your example what is the value for "hibernate.connection.url" , Its omitted in your example . — RajGopalbh4, Feb 28 '20 at 09:40
I thought you had problems only using the `hibernate.default_schema` property ... — Alexandru Somai, Feb 28 '20 at 09:43
Try specifying the port in your connection url, like: `jdbc:mysql://localhost:3306` — Alexandru Somai, Feb 28 '20 at 09:44
@AlexandruSomai . Specifying the port number did not solve my problem . — RajGopalbh4, Feb 28 '20 at 11:31

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

You should use hibernate.default_catalog instead of hibernate.default_schema.

According to the MySql documentation the connection url should have the following format:

protocol//[hosts][/database][?properties]

where

database

The default database or catalog to open. If the database is not specified, the connection is made with no default database. In this case, either call the setCatalog() method on the Connection instance, or specify table names using the database name (that is, SELECT dbname.tablename.colname FROM dbname.tablename...) in your SQL statements. Opening a connection without specifying the database to use is, in general, only useful when building tools that work with multiple databases, such as GUI database managers.

Imagine we have the following MySql databases:

create database DB_A;
create database DB_B;

create table DB_A.TST_EMPLOYEE(
  emp_id int primary key,
  emp_name varchar(100)
);

create table DB_B.TST_EMPLOYEE(
  emp_id int primary key,
  emp_name varchar(100)
);

then we can specify the database connection in the hibernate.cfg.xml in the following way:

<property name="hibernate.connection.url">jdbc:mysql://localhost:3306</property>
<property name="hibernate.default_catalog">DB_A</property>
<property name="hibernate.connection.username">your_user</property>
<property name="hibernate.connection.password">your_pass</property>

declare entities for DB_A.TST_EMPLOYEE and DB_B.TST_EMPLOYEE tables in the following way:

@Entity
@Table(name = "TST_EMPLOYEE")
public class EmployeeA
{
   // ...
}

@Entity
@Table(name = "TST_EMPLOYEE", catalog = "DB_B")
public class EmployeeB
{
   // ...
}

and then use them in the usual way. Also for the native queries you can use the {h-catalog} placeholder for the default catalog specified in the hibernate.default_catalog property.

P.S. I have to say that the catalog and schema notions can have quite different meaning from database to database. See this for the reference.

How to use hibernate.default_schema and hibernate.default_catalog

1 Answers1