使用 Spring Boot 和 Spring JDBC 在 oracle 中设置默认模式 = SOMETHING
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37973425/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set default schema = SOMETHING in oracle using Spring Boot and Spring JDBC
提问by Jose Diaz Diaz
I am working now with oracle and spring jdbc but I don't want to use the schema in my sql statements:
我现在正在使用 oracle 和 spring jdbc，但我不想在我的 sql 语句中使用模式：
Example: Select * from SCHEMA.table
示例：从 SCHEMA.table 中选择 *
Is there any way to set default schema in application.properties or application.yml?
有没有办法在 application.properties 或 application.yml 中设置默认架构？
采纳答案by Luiggi Mendoza
Assuming you define your database connections using spring datasources, you can set the default schema when defining the datasource configuration:
假设您使用 spring 数据源定义数据库连接，您可以在定义数据源配置时设置默认模式：
spring.datasource.schema = #value for your default schema to use in database
You can find more info here: Spring Boot Reference Guide. Appendix A. Common application properties
您可以在此处找到更多信息：Spring Boot 参考指南。附录 A. 常见应用程序属性
After doing some research, looks like Oracle driver doesn't let you set a default schema to work with, as noted here:
经过一些研究，看起来 Oracle 驱动程序不允许您设置要使用的默认架构，如下所述：
Default Schema in Oracle Connection URL
From that post, you have two options:
从该帖子中，您有两个选择：
Execute this statement before executing your statements:
ALTER SESSION SET CURRENT_SCHEMA=yourSchemaCreate synonyms for your tables/views/etc (which I find really cumbersome if we're talking about lots of elements in your database).
在执行您的语句之前执行此语句：
ALTER SESSION SET CURRENT_SCHEMA=yourSchema为您的表/视图/等创建同义词（如果我们谈论的是数据库中的许多元素，我觉得这非常麻烦）。
I would advice using the first option. From what I see, Spring boot doesn't offer a simple way to execute a statement when retrieving the connection, so the best bet will be to use an aspect around the getConnectionmethod (or the method that retrieves the connection from the data source) and execute the statement there.
我会建议使用第一个选项。从我所见，Spring boot 并没有提供一种在检索连接时执行语句的简单方法，因此最好的办法是使用围绕该getConnection方法（或从数据源检索连接的方法）和在那里执行语句。
From your comment, an easier way to solve it is by using a script in spring.datasource.schema:
根据您的评论，更简单的解决方法是使用以下脚本spring.datasource.schema：
spring.datasource.schema = schema.sql
And then a file squema.sql with the following:
然后是一个包含以下内容的文件 squema.sql：
ALTER SESSION SET CURRENT_SCHEMA=mySchema
回答by Deepak
In spring boot, I've found another way of doing it,
在 spring boot 中，我找到了另一种方法，
@Bean
@ConfigurationProperties(prefix="spring.datasource")
public DataSource dataSource(@Value("${spring.datasource.schema}") String schema) {
DataSource datasource = DataSourceBuilder.create().build();
if(!schema.isEmpty() && datasource instanceof org.apache.tomcat.jdbc.pool.DataSource){
((org.apache.tomcat.jdbc.pool.DataSource) datasource).setInitSQL("ALTER SESSION SET CURRENT_SCHEMA=" + schema);
}
return datasource;
}
回答by Rakesh Mothukuri
I found another way to get around this by updating entity class with
我找到了另一种通过更新实体类来解决这个问题的方法
@Table(schema = "SCHEMA_NAME" ,name = "TABLE_NAME")
回答by ANooBee
If you are using hikari, use spring.datasource.hikari.schema=YOUR_SCHEMA. Works for me with SpringBoot + tomcat using Oracle.
如果您使用 hikari，请使用 spring.datasource.hikari.schema=YOUR_SCHEMA。使用 Oracle 与 SpringBoot + tomcat 配合使用。
回答by Michael R
I was having issues with the currently accepted answer; specifically, the schema would only be changed from the initial connection. If your app uses a connection pool, you need to configure the pool to apply SQL for each connection.
我对当前接受的答案有疑问；具体来说，模式只会从初始连接更改。如果您的应用程序使用连接池，则需要配置该池以对每个连接应用 SQL。
For instance, using the default jdbc pool in Spring Boot 1.5.x (Tomcat):
例如，使用 Spring Boot 1.5.x (Tomcat) 中的默认 jdbc 池：
spring.datasource.tomcat.init-s-q-l = ALTER SESSION SET CURRENT_SCHEMA=mySchema
回答by Jean de Lavarene
Connecting to the database as your user, you can create a trigger that will change the schema each time you login:
以您的用户身份连接到数据库，您可以创建一个触发器，每次登录时都会更改架构：
CREATE OR REPLACE TRIGGER LOGON_TRG
AFTER LOGON ON SCHEMA
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foo';
EXCEPTION
when others
then null;
END;
/
回答by David Bradley
Another option is to create a datasource wrapper. Create the datasource as normal and then create the wrapper that forwards all methods except for the getConnection methods. For those I just added SQL to set the schema. We have multiple datasources and this allowed us to specify a different schema for each datasource. If anyone knows if there's an issue with this I'd love comments. Or if there's an alternative that uses the properties.
另一种选择是创建一个数据源包装器。照常创建数据源，然后创建转发除 getConnection 方法之外的所有方法的包装器。对于那些我刚刚添加了 SQL 来设置架构的人。我们有多个数据源，这允许我们为每个数据源指定不同的模式。如果有人知道这是否有问题，我很乐意发表评论。或者，如果有使用这些属性的替代方法。
