Integration test causing a duplicate entry exception on insert

[stephaneeybert]

If you have a link to an example or blog article on creating a schema from within an integration test, I could make good use of it, otherwise no worries, I'll google.

[Alan Stewart]

Simply running your unit tests or spinning up a Roo-generated MVC web app will cause Hibernate to create the schema in the DB

[stephaneeybert]

Sorry for being so slow Alan, do I do this in the roo shell ? Is there a command to do it ?

I have reversed engineered the database schema but still wonder how to change the data source.

[Alan Stewart]

You can run "perform tests" in the Roo shell, or mvn test from the command line. To run the web app, do a mvn tomcat:run, providing you have generated the web tier.

To change the persistence setup, run "jpa setup --provider HIBERNATE --database HYPERSONIC_IN_MEMORY". It's all in the reference docs.

[stephaneeybert]

Oh ! Thanks Alan, I should have guessed it, have used the following script before:

project --topLevelPackage com.learnintouch.lms --packaging POM
module create --moduleName core --topLevelPackage com.learnintouch.lms.core
module create --moduleName ui --topLevelPackage com.learnintouch.lms.ui --packaging POM
module create --moduleName vaadin --topLevelPackage com.learnintouch.lms.ui.vaadin

module focus --moduleName core
jpa setup --provider HIBERNATE --database MYSQL
database properties list
database properties set --key database.username --value learnintouch
database properties set --key database.password --value mignet
database properties set --key database.url --value jdbc:mysql://localhost:3306/db_integration
database properties list
database introspect --schema db_integration
addon install id --searchResultId 01
database reverse engineer --package ~.domain --schema PUBLIC --testAutomatically --includeNonPortableAttributes

module focus --moduleName core
service --interface ~.service.PreferenceService --entity ~.domain.Preference

So it'll be a breeze I guess to change the data source now, more soon..

[stephaneeybert]

Hi Alan,

Some feedback for info only.

I ran my script:

script /home/stephane/dev/help/learnintouch.roo

but got the following message:

jpa setup --provider HIBERNATE --database HYPERSONIC_IN_MEMORY
Created core|ROOT/src/main/resources/META-INF/spring/database.properties
Updated core|ROOT/src/main/resources/META-INF/spring/applicationContext.xml
Created core|ROOT/src/main/resources/META-INF/persistence.xml
Updated core|ROOT/pom.xml [added dependencies org.hsqldb:hsqldb:1.8.0.10, org.hibernate:hibernate-core:3.6.9.Final, org.hibernate:hibernate-entitymanager:3.6.9.Final, org.hibernate.javax.persistence:hibernate-jpa-2.0-api:1.0.1.Final, org.hibernate:hibernate-validator:4.2.0.Final, javax.validation:validation-api:1.0.0.GA, cglib:cglib-nodep:2.2.2, javax.transaction:jta:1.1, org.springframework:spring-jdbc:${spring.version}, org.springframework:spring-orm:${spring.version}, commons-pool:commons-pool:1.5.6, commons-dbcp:commons-dbcp:1.3]
database properties list
database.driverClassName = org.hsqldb.jdbcDriver
database.password =
database.url = jdbc:hsqldb:mem:core
database.username = sa
database properties set --key database.username --value learnintouch
Updated core|ROOT/src/main/resources/META-INF/spring/database.properties
database properties set --key database.password --value xxxxxxxx
Updated core|ROOT/src/main/resources/META-INF/spring/database.properties
database properties set --key database.url --value jdbc:hsqldb:mem:db_integration
Updated core|ROOT/src/main/resources/META-INF/spring/database.properties
database properties list
database.driverClassName = org.hsqldb.jdbcDriver
database.password = mignet
database.url = jdbc:hsqldb:mem:db_integration
database.username = learnintouch
database introspect --schema db_integration
addon search --requiresDescription "#jdbcdriver,driverclass:org.hsqldb.jdbcDriver " found no matches
JDBC driver not available for 'org.hsqldb.jdbcDriver'
Script required 6.363 seconds to execute
Script execution aborted

[stephaneeybert]

I think the campari is not helping..

So I started over, this time not from an empty directory, but from my existing project directory and ll its already generated content.

In there I ran the command:

jpa setup --provider HIBERNATE --database HYPERSONIC_IN_MEMORY

which answered me with:

additionalBuildCommands element of the maven-eclipse-plugin required

[stephaneeybert]

I think I was meesing up with the types of packing, too many pom types lying around, I fixed that up.

I then ran the command:

data roo> jpa setup --provider HIBERNATE --database HYPERSONIC_IN_MEMORY
Updated data|SPRING_CONFIG_ROOT/database.properties
Updated data|SPRING_CONFIG_ROOT/applicationContext.xml
Updated data|SRC_MAIN_RESOURCES/META-INF/persistence.xml
Updated data|ROOT/pom.xml [added dependency org.hsqldb:hsqldb:1.8.0.10; skipped dependencies org.hibernate:hibernate-core:3.6.9.Final, org.hibernate:hibernate-entitymanager:3.6.9.Final, org.hibernate.javax.persistence:hibernate-jpa-2.0-api:1.0.1.Final, org.hibernate:hibernate-validator:4.2.0.Final, javax.validation:validation-api:1.0.0.GA, cglib:cglib-nodep:2.2.2, javax.transaction:jta:1.1, org.springframework:spring-jdbc:${spring.version}, org.springframework:spring-orm:${spring.version}, commons-pool:commons-pool:1.5.6, commons-dbcp:commons-dbcp:1.3; removed dependency mysql:mysql-connector-java:5.1.18]
data roo>

I also set a create in the hibernate.hbm2ddl.auto property.

I ran the command:

database properties set --key database.url --value jdbc:hsqldb:mem:db_integration

Then I ran the tests:

perform tests

But it said the tables were not found:

Caused by: java.sql.SQLException: Table not found in statement [select top ? mail0_.id as id63_, ma
il0_.admin_id as admin11_63_, mail0_.attachments as attachme2_63_, mail0_.body as body63_, mail0_.c
ategory_id as category12_63_, mail0_.creation_datetime as creation4_63_, mail0_.description as desc
ript5_63_, mail0_.locked as locked63_, mail0_.send_datetime as send7_63_, mail0_.subject as subject
63_, mail0_.text_format as text9_63_, mail0_.version as version63_ from mail mail0_]

I would think it is a user priviledge issue with the table object not granted access to.

I'll try with the user sa and empty password.

[stephaneeybert]

So I set up the sa user and his empty password:

data roo> database properties list
database.driverClassName = org.hsqldb.jdbcDriver
database.password =
database.url = jdbc:hsqldb:mem:data
database.username = sa

But I still get the same exception:

Caused by: java.sql.SQLException: Table not found in statement [select top ? mail0_.id as id20_, ma
il0_.admin_id as admin11_20_, mail0_.attachments as attachme2_20_, mail0_.body as body20_, mail0_.c
ategory_id as category12_20_, mail0_.creation_datetime as creation4_20_, mail0_.description as desc
ript5_20_, mail0_.locked as locked20_, mail0_.send_datetime as send7_20_, mail0_.subject as subject
20_, mail0_.text_format as text9_20_, mail0_.version as version20_ from mail mail0_]

[Alan Stewart]

This is outside the realm of Roo now - probably best to search the Hibernate forum at jboss.org