How can I globally specify my schema, rather than add it to every @Table annotation?

Question

I have all my tables in the same schema in a SQLServer database, so I would rather not have to specify the schema in every @Table annotation. I have put the schema name in my connection string:

spring.datasource.url=jdbc:sqlserver://localhost;instanceName=SQLEXPRESS;databaseSchema=TST;databaseName=TestDB;integratedSecurity=true;

When my annotation is @Table(name="MY_TABLE"), and Hibernate attempts an insert I get an Invalid object name 'MY_TABLE'. error message.

If the annotation is @Table(name="MY_TABLE", schema="TST") then the insert works as expected.

Does the SQLServer dialect not honor the schema in the connection string?

Here are all the Spring/Hibernate properties:

spring.datasource.url=jdbc:sqlserver://localhost;instanceName=SQLEXPRESS;databaseSchema=EXP;databaseName=YRC_PILOT2;integratedSecurity=true;
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.database-platform = org.hibernate.dialect.SQLServerDialect
spring.jpa.show-sql=true

If I have to specify the schema for every table, so be it. But that seems a bit kludgey if I ever want to switch schema names.

Possible duplicate of [Possible to set default schema from connection string?](http://stackoverflow.com/questions/3282665/possible-to-set-default-schema-from-connection-string) — alroc, Oct 25 '16 at 17:02
SQL Server doesn't support setting the schema in the connectionstring. You'll either have to keep doing what you're doing, or set the default schema for your application's SQL account. See http://stackoverflow.com/q/3282665/1324345 — alroc, Oct 25 '16 at 17:03

score 2 · Accepted Answer · answered Oct 25 '16 at 17:15

Use spring.jpa.properties.hibernate.default_schema=yourschemaname.

You can use spring.jpa.properties.* To set JPA native properties

From the Spring boot Docs:

all properties in spring.jpa.properties.* are passed through as normal JPA properties (with the prefix stripped)

See http://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#howto-configure-jpa-properties

score 1 · Answer 2 · edited May 23 '17 at 12:19

1

Thanks to datGnomeLife & Neil Stockton I started looking outside the connection string. That led me to How to set up default schema name in JPA configuration? which actually answered my question, which Possible to set default schema from connection string? did not.

edited May 23 '17 at 12:19

Community

1
1

answered Oct 25 '16 at 18:56

cneff

388
6
15

How can I globally specify my schema, rather than add it to every @Table annotation?

2 Answers2