我创建了一个名为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说的,我创建了一个名为我的默认模式的用户,并通过此登录进行授权。这是在查询中省略模式名称的最简单的方法。但是如果我想使用多个模式,唯一的方法是显式地设置模式。
解决方案默认模式名称:
如果您尚未发出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.USERSeverything 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:
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查询中省略模式
发布评论