Java.sql.SQLException: ROOT schema does not exist

Question

I use hibernate with built-in derby, and I want hibernate to create the database and tables, so I tried the following configuration, but I get an 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: this is my first use of derby, so I might have some missing information, so I have a question:

I need to configure the embedded Derby, as here:

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

UPDATE 2: I deleted the import.sql script file in the classpath, which is responsible for inserting sample data into the database, and I found that there was an error 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. 

Answer 1

after removing the import.sql file from the class path and starting the application, I found that the error was that the database was being created, but the tables were not created by hbm2ddl due to the problem http://issues.apache.org/jira/ browse / DERBY-789 here: limitation issues with apache derby and hbm2ddl

that the primary key cannot be defined as unique because it is processed by the database itself.

so when i removed the unique attribute from the primary key, the error went away.

Answer 2

Sorry, I never used Derby, but I think the problem is that Hibernate cannot create databases, database users or database schemas for you. This is just an assumption, but for 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 the schema root in advance, then connect to Hibernate to create the tables.

Answer 3

The simplest solution is to set up your database properties and make the schema the same as the username, but as ex: uppercase APP user app

hope my answer can help.