java.sql.SQLException: Schema 'ROOT' does not exist

Question

i am using hibernate with embeded derby, and i want hibernate to create the database and the tables, so i tried the following configuration, but i am getting the error:

java.sql.SQLException: Schema 'ROOT' does not exist

here/s my configuration:

    <bean id="sessionFactory"
    class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">

    <property name="dataSource" ref="dataSource" />
    <property name="packagesToScan" value="com.myapp.domain" />


    <property name="hibernateProperties">
        <value>
            hibernate.dialect=org.hibernate.dialect.DerbyDialect
            hibernate.hbm2ddl.auto=create
            hibernate.show_sql=false
            hibernate.format_sql=false
        </value>
    </property>

</bean>

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

    <property name="driverClassName" value="org.apache.derby.jdbc.EmbeddedDriver" />

    <property name="url" value="jdbc:derby:test;create=true" />

    <property name="username" value="root" />

    <property name="password" value="root" />

</bean>

<bean id="transactionManager"
    class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory" />
</bean>


<bean class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />

UPDATE: it's my first use of derby so i maybe have some missing information, so i have a question:

do i have to Configure Embedded Derby as in here:

http://db.apache.org/derby/papers/DerbyTut/install_software.html

UPDATE 2: i removed the import.sql script file on classpath which is responsible for inserting demo data in the database, and i found out that there's an error in creating the database table:

1202 [main] INFO org.hibernate.tool.hbm2ddl.SchemaExport - Running hbm2ddl schema export
1202 [main] INFO org.hibernate.tool.hbm2ddl.SchemaExport - exporting generated schema to database
1359 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaExport - Unsuccessful: create table users (user_id bigint generated by default as identity unique, address varchar(255), email varchar(155) not null, mobile varchar(25), name varchar(25) not null, password varchar(255) not null, primary key (user_id))
1359 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaExport - Constraints 'SQL111223214919671' and 'SQL111223214919670' have the same set of columns, which is not allowed. 
1359 [main] INFO org.hibernate.tool.hbm2ddl.SchemaExport - schema export complete
1360 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Warning: 10000, SQLState: 01J01
1360 [main] WARN org.hibernate.util.JDBCExceptionReporter - Database 'test' not created, connection made to existing database instead.

Heavyhearted

never used derby. but did you defined the user "root" for the database (with access to the test base) – Stilt

well, i just tried the configuration as in here, and i thought that it should create the database with above user/pass #8459784 – Heavyhearted

heres an embedded driver tut: db.apache.org/derby/papers/DerbyTut/embedded_intro.html. But your code looks fine – Capsize

The faq says that the schema is created when something in that schema is created. Can you see what sql statements are executed? Maybe hibernate (or spring) starts with a drop here. – Capsize

@Vinze,@Capsize i updated the post, please advise. – Heavyhearted

Answer 1

after removing the import.sql file from classpath and running the application, i found that the error was that the databse is created, but the tables was not created by hbm2ddl due to the issue http://issues.apache.org/jira/browse/DERBY-789 stated here: constraint problems using apache derby and hbm2ddl

that the primary key cannot be defined as unique, since that is handled by the database itself.

so when i removed the unique attribute from the primary key, the error is gone.

Heavyhearted

Answer 2

Sorry, never used Derby myself, but I think the problem is that Hibernate cannot create any databases, database users or database schemas for you. This is just a guess, but to me the error message indicates that you also need a database schema for the user. Try to create the database, the database user root and a schema root beforehand, then connect with Hibernate to create the tables.

Loyceloyd

;create=true should take care of that – Capsize

@oers: Thanks, didn't see that. But I am quite sure hibernate cannot create a schema, and it looks like the schema for the user is missing. So, I would try with create=false and create the database and schema beforehand. – Loyceloyd

Answer 3

the easiest solution is to configure your database properties and make schema the same as user name but in capital litters ex: schema APP user app

hope my answer can help.

Adherent