Omit schema in the DERBY Query

Question

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.

Answer 1

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.