Database schema generation

[springatized]

Is there a Roo command to generate a database schema from JPA entities? I've hit a brick wall trying to find anything in the help contents.

BTW, very impressed with Roo so far!

Cheers,
Jon

[Tom Sheehan]

As you're following along with the tutorials, such the wedding sample http://blog.springsource.com/2009/05/27/roo-part-2/?__utma=1.166142376600108220.1242443979.1251956139 .1252468862.28&__utmb=1.3.10.1252468862&__utmc=1&__utmx=-&__utmz=1.1251956555.27.4.utmcsr=google|utmccn=%28o rganic%29|utmcmd=organic|utmctr=spring%203.0%20vie w%20technology&__utmv=-&__utmk=193950637, you will find that tables are generated when you build the project with maven. After the build you can go back and check. Unless you're working with an HSQLDB In-Memory model, the tables should be there after the build.

[springatized]

Tom, thanks for the reply.

I was following the mentioned wedding blog and had no issues using the HYPERSONIC_PERSISTENT db. However, I tried again with MSSQL and that didn't work as the schema did not get created.

If I create the table by hand then all is well.

I've only used the maven commands specified in the blog, I assumed the tomcat:run would capture every goal necessary but maybe I'm missing something?

Cheers,
Jon

[Tom Sheehan]

Hello Jon,

Yes, mvn tomcat:run will cause the schema to be built, if all goes well. I have been able to generate schema with both HSQLDB and PostgreSQL (albeit with monkeying the PostgreSQL JDBC driver into the POM). In fact, if you have tests configured, all you have to do is run:

mvn test

I did a bit of research on this, and unfortunately, all did not go well. Here is some diagnostic data. And, I'm out of time for the moment.


/* pipe test project create script */
create project -topLevelPackage com.pipe
install jpa -provider HIBERNATE -database MSSQL

/* Database properties set directly */
new persistent class jpa -name ~.domain.Material -testAutomatically
add field string -fieldName name
add field string -fieldName materialType

/* quit and run [mvn test] */

The -testAutomatically clause generates 9 unit tests, all fail. One of the nice things about this is the sure-fire reports.



Tests run: 9, Failures: 0, Errors: 9, Skipped: 0, Time elapsed: 1.616 sec <<< FAILURE!
testMarkerMethod(com.pipe.domain.MaterialIntegrati onTest) Time elapsed: 0.841 sec <<< ERROR!
java.lang.IllegalStateException: Failed to load ApplicationContext
at org.springframework.test.context.TestContext.getAp plicationContext(TestContext.java:255)
.
.
.
Caused by: org.springframework.beans.factory.BeanCreationExce ption: Error creating bean with name 'transactionManager' defined in class path resource [META-INF/spring/applicationContext.xml]: Cannot resolve reference to bean 'entityManagerFactory' while setting bean property 'entityManagerFactory'; nested exception is org.springframework.beans.factory.BeanCreationExce ption: Error creating bean with name 'entityManagerFactory' defined in class path resource [META-INF/spring/applicationContext.xml]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: persistenceUnit] Unable to build EntityManagerFactory
at org.springframework.beans.factory.support.BeanDefi nitionValueResolver.resolveReference(BeanDefinitio nValueResolver.java:315)
.
.
.
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: persistenceUnit] Unable to build EntityManagerFactory
at org.hibernate.ejb.Ejb3Configuration.buildEntityMan agerFactory(Ejb3Configuration.java:677)
.
.
.
Caused by: org.hibernate.HibernateException: Dialect class not found:
at org.hibernate.dialect.DialectFactory.buildDialect( DialectFactory.java:81)


The bit I went after was the Dialect class not found. Roo writes a reference in the POM to use the jtds database driver. Hibernate.org specifies a certified driver here:
https://www.hibernate.org/382.html

Downloaded it and installed on local maven repository:


mvn install:install-file -DgroupId=microsoft -DartifactId=sqljdbc -Dversion=1.0.809.102 -Dpackaging=jar -Dfile="C:\Data\Install\db\Microsoft\Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.0\enu\sqljdbc.jar"

Then swapped drivers in the POM:

<!--
<dependency>
<groupId>net.sourceforge.jtds</groupId>
<artifactId>com.springsource.net.sourceforge.jtds</artifactId>
<version>1.2.2</version>
</dependency>
-->
<dependency>
<groupId>microsoft</groupId>
<artifactId>sqljdbc</artifactId>
<version>1.0.809.102</version>
</dependency>


I was hoping the database driver would help with the dialect problem. Nothing different jumps out in the exception, however.

Environment:
Roo version: 1.0.0.RC1 [rev 198]

I did not have MSSQL installed locally, so downloaded the default from Microsoft which is now their 2008 version. I suppose this could be the problem.


Apache Maven 2.2.1 (r801777; 2009-08-06 14:16:01-0500)
Java version: 1.6.0_07
Java home: C:\Java\JDK\jdk1.6.0_07\jre
Default locale: en_US, platform encoding: Cp1252
OS name: "windows vista" version: "6.0" arch: "x86" Family: "windows"

HTH: ts

[duckbill]

A Roo-generated app will by default instruct Hibernate to generate the schema at runtime (i.e. when running tests or deploying the app). This is controlled by the "hibernate.hbm2ddl.auto" property in persistence.xml.

If you want to store the schema to a file, have a look at the link below.
http://unmaintainable.wordpress.com/2008/04/12/hibernate3-schema-creation/

Cheers,
Jukka

[Tom Sheehan]

Thanks for the reply, Jukka. What I understood the problem was, however, that the tables were not being generated. And, this what I found the pipe sample.

Jon said, "If I create the table by hand then all is well.".

Regard,
Tom

[duckbill]

Have you checked the hibernate dialect property in persistence.xml for typos?

org.hibernate.dialect.SQLServerDialect

[Tom Sheehan]

Good catch: the value of the hibernate.dialect property was an empty string. It looks like there are some other connections being missed here.

Roo is generating: com.microsoft.sqlserver.jdbc.SQLServerDriver for the class name in database.properties. However, it is specifying the JDBC driver from jtds in the POM.

The jtds driver expects to use a driver from the net.sourceforge.jtds name space.

http://jtds.sourceforge.net/faq.html#driverImplementation

I only spent a few minutes with this, but if I plug in the dialect class name you suggested to the hibernate.dialect property and specify the Microsoft 1.x JDBC driver (that Hibernate certified) I have a class not found exception. For some reason it does not find com.microsoft.sqlserver.jdbc.SQLServerDriver.

Would you lean towards trying to get this to work with the jtds namespace or the Microsoft names space?

[duckbill]

I would go with jTDS (but the dialect as above). Just make sure the jtds jar is in your classpath, and that the db properties resemble the ones below.

Driver = net.sourceforge.jtds.jdbc.Driver
URL = jdbc:jtds:sqlserver://<server>[:<port>][/<database>]

Cheers,
Jukka

[springatized]

Thanks guys for your input, I haven't had time to try out the link Jukka proposed but I'll give that a go asap.

I ended up switching to JTDS and created my schema by hand, even with this minor delay I think Roo proved very worthy as a RAD tool and I'm sure I'll be using it in the future.

Thanks again.
Jon.

[fschroder]

I'm also having problems with schema generation, but with mysql.
I'm following the wedding tutorial, tweaking values as needed, using Roo 1.0.0.RC4 [rev 526]

database.properties


database.password=
database.url=jdbc\:mysql\://localhost\:3306/roo3
database.username=root
database.driverClassName=com.mysql.jdbc.Driver
persistence.xml


<persistence-unit name="persistenceUnit" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect"/>
<property name="hibernate.hbm2ddl.auto" value="create"/>
<property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
</properties>
</persistence-unit>
BTW: shouldn't MySQL5Dialect be the default value?

I don't see any references to hbm2ddl in the pom, but it did work with the hypersonic example. I can try to add it manually and create the schema using a maven command, but it seems this should work by default.
Any suggestion on what might be wrong?


Also I get this warning when running the test generated by the tool:


log4j:WARN No appenders could be found for logger (org.springframework.test.context.junit4.SpringJUn it4ClassRunner).


Thanks,
Federico

[Ben Alex]

The database DDL is being updated when Spring starts the LocalContainerEntityManagerFactoryBean which in turn starts Hibernate's session factory. The session factory uses a parsed Configuration object to update the database DDL. You don't need to do this from Maven manually.

Re the Log4J warning, are you using Roo RC4? It looks like https://jira.springsource.org/browse/ROO-256 to me, which was fixed in RC4.

[fschroder]

The database DDL is being updated when Spring starts the LocalContainerEntityManagerFactoryBean which in turn starts Hibernate's session factory. The session factory uses a parsed Configuration object to update the database DDL. You don't need to do this from Maven manually.
Thx, this is working now, I had to create the database by hand though (just the "create database xxx" part).


Re the Log4J warning, are you using Roo RC4? It looks like https://jira.springsource.org/browse/ROO-256 to me, which was fixed in RC4. Yes, it does look like that issue, but it is still present in 1.0.0.RC4 [rev 526]
I fixed it by copying log4j.properties to ./target/test-classes

Thanks a lot for the fast answer.
Federico

[Ben Alex]

Thanks for clarifying it is still present in RC4. I've reopened ROO-256 and we'll take another look.