import.sql and hibernate.hbm2ddl.auto doesn't work

[k1ruha]

Hello.

I'm new to Seam and trying to use a feature to populate db with import.sql file. In my project I use Jboss 4.2.2, Seam 2.0.2SP1 and Postgres 8.2.

I put import.sql into root of a jar file that is packacked into ear, persistence.xml looks like this:

<persistence>
    <persistence-unit name="comix">
        <provider>
            org.hibernate.ejb.HibernatePersistence
        </provider>
        <jta-data-source>java:/comixDS</jta-data-source>
        <properties>
            <property name="hibernate.dialect"
                      value="org.hibernate.dialect.PostgreSQLDialect"/>
            <property name="hibernate.hbm2ddl.auto"
                      value="create-drop"/>
            <property name="hibernate.show_sql" value="true"/>
        </properties>
    </persistence-unit>
</persistence>

On server startup, tables and sequences are created, but tables are empty. Is there any way to check or debug, what is a reason why import.sql is ignored?

Any help or suggest is appreciated.

Thanks in advance.

[admin.admin.email.tld]

http://in.relation.to/9081.lace

import.sql: easily import data in your unit tests

Hibernate has a neat little feature that is heavily under-documented and unknown. You can execute an SQL script during the SessionFactory creation right after the database schema generation to import data in a fresh database. You just need to add a file named import.sql in your classpath root and set either create or create-drop as your hibernate.hbm2ddl.auto property.

I use it for Hibernate Search in Action now that I have started the query chapter. It initializes my database with a fresh set of data for my unit tests. JBoss Seam also uses it a lot in the various examples. import.sql is a very simple feature but is quite useful at time. Remember that the SQL might be dependent on your database (ah portability!).

#import.sql file
delete from PRODUCTS
insert into PRODUCTS (PROD_ID, ASIN, TITLE, PRICE, IMAGE_URL, DESCRIPTION) values ('1', '630522577X', 'My Fair Lady', 19.98, '630522577X.jpg', 'My Fair blah blah...');
insert into PRODUCTS (PROD_ID, ASIN, TITLE, PRICE, IMAGE_URL, DESCRIPTION) values ('2', 'B00003CXCD', 'Roman Holiday ', 12.98, 'B00003CXCD.jpg', 'We could argue that blah blah');

For more information about this feature, check Eyal's blog, he wrote a nice little entry about it. Remember if you want to add additional database objects (indexes, tables and so on), you can also use the auxiliary database objects feature.

The import.sql is picked up my hibernate and insert statements are committed for the booking example, however, when I launch the HSQLDB manager, I don't even see the tables (Customer and Hotel).  strange...

[admin.admin.email.tld]

Not sure if this applies exactly the same with EntityManager/JPA in Seam, but...

create

Hibernate will create the database when the entity manager factory is created (actually when Hibernate's SessionFactory is created by the entity manager factory). If a file named import.sql exists in the root of the class path ('/import.sql') Hibernate will execute the SQL statements read from the file after the creation of the database schema. It is important to remember that before Hibernate creates the schema it empties it (delete all tables, constraints, or any other database object that is going to be created in the process of building the schema).

create-drop

Same as 'create' but when the entity manager factory (which holds the SessionFactory) is explicitly closed the schema will be dropped. 

http://www.jroller.com/eyallupu/entry/hibernate_s_hbm2ddl_tool

[k1ruha]

Hello Arbi,

As I wrote in first post, schema is created correctly, so I don't think this is a problem of hibernate's hbm2dll. Moreover I tried to insert bad sql statement into import.sql and don't get any errors or exceptions. Seems hibernate doesn't find this file or refuses to run it(

[infinity2heaven]

Not sure where exactly your import.sql is present, it obviously is not picking it up on startup. I use it consistently in my webap. I have it one level above META-INF (or same location as seam.properties). Can you check once again?

[k1ruha]

This is simplified structure of my app, seems exactly like u described.

+ comix.ear
  | comix-web.war
  | comix-ejb.jar
    | META-INF
    | seam.properties
    | import.sql

[admin.admin.email.tld]

Seam in Action:

If the database is slated to be created when Hibernate initializes, the seed data in the import.sql at the root of the classpath is loaded into the database automatically.

Have you tried any other app that seeds the db with import.sql and HSQLDB (e.g. booking example)?

Try to isolate the problem...

[k1ruha]

Seems I have found a problem - in import.sql statements were splitted into a few lines.

[kangtown]

hi, Kiryl,

I had the exactly same problem as you. I was using jboss4.2.2, seam 2.1.0 SP1 and postgresql 7.4.19.

My problem was fixed when I changed value of hibernate.hbm2ddl.auto from "create_drop" to "create".

Yes, I know the documentation of hbm2ddl says both create_drop and create should run import.sql after a database is created. The only difference between them is whether drop the database after the em factory is closed. But appeared to be not the case to me. When I was using create_drop, it simply ignore import.sql after the database schema was created.

I am sure whether this is a bug of hibernate, JPA or something specific to postgresql - due to the fact that both of us were using postgresql.

Kang

[kangtown]

sorry, I meant to say I am sure whether this is a bug of hibernate, JPA or something specific to postgresql - due to the fact that both of us were using postgresql.

Kang

[bking007]

Changing “hibernate.hbm2ddl.auto” property to “create” worked for me. Thanks!!