在DERBY查询中省略模式 [英] Omit schema in the DERBY Query

问题描述

我创建了一个名为movie_db的数据库，将默认模式设置为APP。
然后创建一个名为USERS的示例表。

我与数据库的连接如下：

 < bean id =dataSourceclass =org.springframework.jdbc.datasource.DriverManagerDataSource> 
 
< property name =driverClassNamevalue =org.apache.derby.jdbc.ClientDriver/> 
< property name =urlvalue =jdbc：derby：// localhost：1527 / movie_db/> 
< property name =usernamevalue =root/> 
< property name =passwordvalue =pass/> 
< / bean> 
  

现在我想写一些测试，并尝试执行以下查询：

  SELECT * FROM USERS; 
  

我得到了什么：

  java.sql.SQLSyntaxErrorException：Table / View'USERS'不存在。 
  

当我指定我正在使用的模式：

  SELECT * FROM APP.USERS 
  

如何在查询中省略模式名？ strong>
像Bryan说的，我创建了一个名为我的默认模式的用户，并通过此登录进行授权。这是在查询中省略模式名称的最简单的方法。但是如果我想使用多个模式，唯一的方法是显式地设置模式。

解决方案

默认模式名称：

1. 在连接到数据库之后发出SET SCHEMA语句。

如果您尚未发出SET SCHEMA语句，则Derby将使用您的用户名作为模式名称。

因此，如果您以用户APP登录，并且不发出SET SCHEMA语句，名称将是APP。

I have created a database named 'movie_db', set default schema to APP. Then created a sample table named 'USERS'.

My connection to DB is as follows:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

    <property name="driverClassName" value="org.apache.derby.jdbc.ClientDriver"/>
    <property name="url" value="jdbc:derby://localhost:1527/movie_db"/>        
    <property name="username" value="root"/>
    <property name="password" value="pass"/>
</bean>

Now I want to write some tests and try to execute the following query:

SELECT * FROM USERS;

What I get:

java.sql.SQLSyntaxErrorException: Table/View 'USERS' does not exist.

When I specify exactly the schema I'm using:

SELECT * FROM APP.USERS

everything works fine.

How can I omit schema name in my query?

UPDATE: Like Bryan said, I've created a user with the name of my default schema and authorize with this login. This is the most simple way to omit schema name in the query. But still if I want to use multiple schemas the only way is to set schema explicitly.

解决方案

There are basically two ways to control the default schema name:

1. Issue the SET SCHEMA statement after you have connected to the database.
2. Login as the user with the same name as the schema you wish to use.

If you haven't issued a SET SCHEMA statement, then Derby will use your username as the schema name.

So if you login as user "APP", and don't issue a SET SCHEMA statement, then your schema name will be APP.

这篇关于在DERBY查询中省略模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！