Announcement Announcement Module
Collapse
No announcement yet.
Database schema generation Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Database schema generation

    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

  • #2
    Schema Generation is Built In

    As you're following along with the tutorials, such the wedding sample http://blog.springsource.com/2009/05...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.

    Comment


    • #3
      Is this true for MSSQL?

      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

      Comment


      • #4
        Fail Fast Test

        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.

        Code:
        /* 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.

        Code:
        Tests run: 9, Failures: 0, Errors: 9, Skipped: 0, Time elapsed: 1.616 sec <<< FAILURE!
        testMarkerMethod(com.pipe.domain.MaterialIntegrationTest)  Time elapsed: 0.841 sec  <<< ERROR!
        java.lang.IllegalStateException: Failed to load ApplicationContext
        	at org.springframework.test.context.TestContext.getApplicationContext(TestContext.java:255)
        .
        .
        .
        Caused by: org.springframework.beans.factory.BeanCreationException: 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.BeanCreationException: 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.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:315)
        .
        .
        .
        Caused by: javax.persistence.PersistenceException: [PersistenceUnit: persistenceUnit] Unable to build EntityManagerFactory
        	at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(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:

        Code:
        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:
        Code:
        	<!--
        	<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.

        Code:
        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

        Comment


        • #5
          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/...hema-creation/

          Cheers,
          Jukka

          Comment


          • #6
            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

            Comment


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

              org.hibernate.dialect.SQLServerDialect

              Comment


              • #8
                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...Implementation

                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?

                Comment


                • #9
                  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

                  Comment


                  • #10
                    Thanks

                    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.

                    Comment


                    • #11
                      Schema generation problem but with MYSQL

                      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
                      Code:
                      database.password=
                      database.url=jdbc\:mysql\://localhost\:3306/roo3
                      database.username=root
                      database.driverClassName=com.mysql.jdbc.Driver
                      persistence.xml
                      Code:
                          <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:
                      Code:
                      log4j:WARN No appenders could be found for logger (org.springframework.test.context.junit4.SpringJUnit4ClassRunner).
                      Thanks,
                      Federico

                      Comment


                      • #12
                        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.

                        Comment


                        • #13
                          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

                          Comment


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

                            Comment

                            Working...
                            X