JPA 本机查询到来自 Oracle 中不同模式的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2216754/
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
JPA Native Query to tables from different schema in Oracle
提问by Pawel Szulc
I have table MV_ULICE in schema ADRESY. However in JPA I connect to database (Oracle) using different user then ADRESY. This user has privilege to access tables from schema ADRESY, so in JPA there was no problem with defining entities, since you can easily provide different schema in entity definition:
我在模式 ADRESY 中有表 MV_ULICE。但是，在 JPA 中，我使用不同的用户然后使用 ADRESY 连接到数据库（Oracle）。该用户有权从模式 ADRESY 访问表，因此在 JPA 中定义实体没有问题，因为您可以轻松地在实体定义中提供不同的模式：
@Entity
@Table(name = "MV_ULICE", schema = "ADRESY")
public class PoiStreet {
...
The problem started when I wanted to create Native Query using JPA. Query looks like this:
当我想使用 JPA 创建本机查询时，问题就开始了。查询如下所示：
final String queryString = "SELECT * "
+ "FROM MV_ULICE streets "
+ "WHERE CONNECT_BY_ISLEAF = 1 AND streets.status != 'H' "
+ "CONNECT BY NOCYCLE PRIOR streets.sym_ul = streets.symulold "
+ "START WITH streets.sym_ul = 'ulica'";
Query query = getEntityManager().createNativeQuery(
queryString, poi.domain.entities.streets.PoiStreet.class);
And this does not work. I simply get exception form Oracle "Table or view does not exist".
这是行不通的。我只是得到异常形式的 Oracle“表或视图不存在”。
I tried chanign MV_ULICE to ADRESY.MV_ULICE
我尝试将 MV_ULICE 更改为 ADRESY.MV_ULICE
final String queryString = "SELECT * "
+ "FROM ADRESY.MV_ULICE streets " + ...
but that did not help.
但这没有帮助。
So does anyone has experience with native queries on oracle with different schemas then user that is accessing the database? Please help :)
那么有没有人有过使用不同模式在 oracle 上进行本地查询的经验，然后是访问数据库的用户？请帮忙 ：）
采纳答案by Pawel Szulc
Ok, so the real solution to the problem is to actually use a solution that I misleadingly said that is not working.
好的，所以问题的真正解决方案是实际使用我误导性说不起作用的解决方案。
I took my quite time to find my mistake, but to tell the long story short using the standard notation SCHEMA.TABLENAME will work. So in my case the query should start like this:
我花了很多时间来发现我的错误，但是使用标准符号 SCHEMA.TABLENAME 来讲述长话短说是可行的。所以在我的情况下，查询应该像这样开始：
final String queryString = "SELECT * "
+ "FROM ADRESY.MV_ULICE streets "
回答by Pascal Thivent
I don't know if this is the best solution but I think that a Database link would work.
我不知道这是否是最好的解决方案，但我认为数据库链接会起作用。
First, connect to your "first" database and execute the following at the SQL prompt:
首先，连接到您的“第一个”数据库并在 SQL 提示符下执行以下命令：
CREATE DATABASE LINK mylink CONNECT TO scott IDENTIFIED BY tiger USING '(DESCRIPTION = (ADDRESS =
(PROTOCOL = TCP) (HOST = <hostname>)(PORT = <port>)) (CONNECT_DATA = (SID = <SID>)))'
where
在哪里
mylink Name of the link.
<hostname> host name where the database is installed
<port> TNS listener port of the database
<SID> database name
This statement creates a database link with the name 'mylink'. The link connects to the SCOTT user of the database installed in the host (<hostname>)
此语句创建一个名为“mylink”的数据库链接。链接连接到主机中安装的数据库的SCOTT用户( <hostname>)
Then, use the link name to reference the object on the remote database:
然后，使用链接名称引用远程数据库上的对象：
SELECT * FROM MV_ULICE@mylink
回答by Gary Myers
"This user has privilege to access tables from schema ADRESY"
“此用户有权访问模式 ADRESY 中的表”
Privileges are granted on individual schema objects, not an entire schema. I suspect that the user you are connecting as does not have privileges for this particular table/view.
权限是针对单个架构对象而不是整个架构授予的。我怀疑您正在连接的用户没有此特定表/视图的权限。
