The Rotterdam JBug is happening June 20th and import.sql is a pretty neat feature for unit testing. Two unrelated infos in one entry today, let's call it macroblogging!

Rotterdam JBug on June 20th

The Benelux JBoss User Group is organizing an event on Friday June 20th 2008. There will be plenty of presentations:

  • JBoss Portal - Julien Viet and Thomas Heute - JBoss
  • Hibernate Search - Emmanuel Bernard - JBoss
  • Woman in IT - (special guest presentation) Clara Ko and Linda van der Pal -
  • JBoss Drools - Kris Verlaenen - JBoss

Here is more information, it's free of course but you need to register.

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 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.

09. Jun 2008, 23:49 CET | Link
Sakuraba | saku(AT)

I have come into issues with the import.sql mechanism. I am developing on my MacBook Pro, OSX 10.5 with JDK 5. The file is encoded in UTF-8 (Eclipse project-wide setting).

Whenever my import.sql file gets loaded by Hibernate, special german characters are not stored in my database (dev: embedded HSQL, prod: Oracle 10.5.1) correctly. There are some charset issues that I cannot resolve.

I tried every possible vm-argument and nothing did help.

The funny thing is, if I change the name of the file (so that Hibernate does not load it) and load the file myself and execute every line via a manual JDBC batch statement, everythng works fine (on the SAME file!).

I have seen a related issue that exists since a couple of years in Hibernate JIRA, but I dont know whether it got resolved.

Strange problem...

10. Jun 2008, 00:59 CET | Link

hey, can you add part of your file in the JIRA issue so that it gives an additional test point for us.

15. Jun 2008, 04:54 CET | Link
Geoffrey De Smet

Any reason why you're doing a delete from products, since hbm2ddl is on create or create-drop anyway?

26. Aug 2008, 15:33 CET | Link
Arron Ferguson | arron_ferguson(AT)

I did an interesting test with this importing of a rather large (16 MB) import.sql file. Here are the following specs of the system/app:

  • Ubuntu 7.10, 1 GB memory
  • Pentium 4, 3GHz, dual core
  • Tomcat 6
  • MySQL 5
  • Seam 2.0.2
  • JRE/JDK 1.6
  • Hibernate 3.2.4.sp1

At load time, I bring in a 16.5 MB import.sql file which contains a rather large number of insert statements (just over 100,000). You're probably wondering what on Earth am I importing/inserting right? I'm making use of the GeoIP CSV file (massaged a little so that it's now a bunch of insert statements instead of just comma separated values). It contains IP address ranges world-wide and where in the world those IP addresses are. Great for statistical purposes of who is visiting your site.

Anyways, I've run the test (i.e., loading my web app into Tomcat 6) about five times now and the average insert time for the import.sql is about 20 seconds (using my above posted specs). If I simply execute the statements myself via MySQL's LOAD DATA LOCAL INFILE command directly at the MySQL command line, the same operation is around 1/10 of a second. However, the 20 second delay is not bad when you consider the many layers that these 100,000 insert statements are traveling through.

If I run the web app without having Hibernate execute the insert statements in the import.sql file, the difference in memory is about 20 MB (without, web app is about 65 MB, with the web app is about 87 MB).

However, and this is more important to me, is the fact that the memory affected is only heap. The perm gen space (both read only, read and write) are not affected - I ran jconsole and had a look-see.

I'm sure for anyone who is a Hibernate master this is no real news. However, I am a Hibernate/Seam n00bie and so I really wasn't sure what was going on underneath the hood and I certainly wasn't sure what was going to happen with 100,000 inserts being performed (would the hamsters inside my computer spontaneously combust?). Glad to see that the perm gen memory wasn't affected and that this actually deploys in what I would consider a reasonable turn around time. :)

On a side note, it's a pain in the ass trying to manipulate columns in a CSV file that contains 100,000 rows (need to because Hibernate sorts columns per table alphabetically and MySQL can't selectively place LOAD DATA LOCAL INFILE rows arbitrarily). Most spreadsheet applications limit their row count to 65,536. Apparently M$ Office 2007 does but I'm not running Windows so I just copy/pasted into two separate files and moved columns around there.

06. Sep 2009, 17:12 CET | Link

HI ,

Is there any option to use load blobs into database with the import.sql mechanism, for example if you want to import some pictures on the deploy stage ?

Thanks in advance Roman

28. Apr 2010, 17:00 CET | Link
Arron Ferguson

Thanks Christian, that link is helpful.

05. Nov 2010, 12:48 CET | Link
George Kokkinos | george.kokkinos(AT)

Click HELP for text formatting instructions. Then edit this text and check the preview.Is there anyone who knows why using unicode characters in import.sql won't insert proper characters in database??


14. Oct 2014, 19:29 CET | Link

This is a very great post! Blogs are good in which we get lots of information and convert that information to knowledge Saya punya tips bagaimana cara merapatkan vagina secara alami serta aman tanpa efek samping menggunakan herbal Crystal X dari nasa. Dengan mengikuti aturan yang ada maka penyakit anda dapat teratasi dapatkan info selengkapnya di distributor crystal x pusat di jogja