Web App Startup / populating database from .csv

[erikb1]

This probably isn't a Roo question per se, but I'd really appreciate it if someone could point me in the right direction here.

I have a larger list of static data in a text (.csv) file that needs to be imported into a database table as part of the initial startup / deployment of a Roo-generated web app. I'd be inclined to use an enum, but as I mentioned, the list is kind of big (~7,000 entries) and I'm too lazy to convert it by hand.

Are there any tutorials / books around that demonstrate how to do something like this? If not, where should I be looking at for:
(1) Info on hooking into the initial startup event (is this org.springframework.web.context.ContextLoaderServl et?)
(2) Info on how to import text file data into a table from JPA

I'm using Hibernate + Hypersonic / Persistent, in case that makes a difference (e.g., maybe it makes more sense to deploy an already-populated hypersonic database?).

TIA

-Erik

[hatim]

There are two ways of doing this, one would be database specific and other would be in Java


Your best bet would to use some sort of transformation tool to create an SQL file and put it in your classpath call it import.sql

Ofcourse you will need to know the schema of the database, which you can do by making hibernate log every SQL, then using find/replace or awk/sed/grep make up SQL statements for your objects. Ofcourse this assumed that you want this data to be put every time you start your application, for a normal application you will change your hibernate DDL generation to verify (and not create) so you wont loose this data once you manually import it.


Second way is similar with find and replace , but you come up with java statements following solution posted on this thread
http://forum.springsource.org/showthread.php?t=84312

This indeed will slow up your application depending upon how you iterate through the objects (compared to SQL, which will be faster since there is no hibernate involved for each line). You could actually read CSV file and persist object one by one (use Apache POI for CSV) or use a java file to manually insert things 7000 times (dirty solution, but mind you, each java file has a size limit...)

Hope it helps, let me know if you have any questions.

[erikb1]

Thanks, Hatim!

I had put together a .sql script for HSQLDB that imported a text table (from the file) into a database, running it directly from the hsqldb.jar. Something like this:

CREATE TABLE real (
   Id      char(3) NOT NULL,
   Comment    varchar(150) NULL);
   
CREATE TEXT TABLE tmp (
   Id      char(3) NOT NULL,
   Comment    varchar(150) NULL);

SET TABLE tmp SOURCE "temp.tab;ignore_first=true;encoding=UTF-8;fs=\t";

select count(*) from tmp;
select count(*) from real;

INSERT INTO real (
    Id,
    Comment
) SELECT
    Id,
    Comment
FROM tmp;

commit;

select count(*) from real;

Looking at the hsqldb database's .script file, I do see individual INSERT entries for all the ~7000 items in my text file (okay, it's really a .tab file, not a .csv ).

So I might be able to move out these INSERT entries out into a separate .sql file and populate the table that way. Another way to do it would be to hack the web app database's .script file (add in the .script statements from the test database). The drawback there would be that the data is no longer in a generic .sql format - so moving to another database takes a little more work.

If I go the .sql statement route, where do I need to add the hook in the web app to verify that the table is created (and call the .sql file if it isn't)? ContextLoaderServlet?

[hatim]

Let me answer you the way I understand your question (please correct me if don't understand you quite)

Well the databases in ROO does not have any script file and you dont need to create tables manually for Roo entities, they are generated via JPA annotations (since I use hibernate it moslty, in my case it is done by hbm2ddl, search Google for that)
So with JPA you control how database is formed (what table names would be, how would they be interrelated, what would be Id etc)

You will have to look into persistence context to see how tables are generated (they can be generated with drop-create, create , or verify only options, among others)

Once all the schema has been created your import.sql file can be executed every time your server start, I think you need to put it in WEB-INF

you could also look into integrating liquibase instead of hbm2ddl and create a change set and apply that. Ofcourse the easiest is to run your SQL file directly.

I think you can produce multiple SQL files for multiple databases. If you go into lots of SQL files and databases then you can lookinto other complex highly portable solutions. Usually I either run on Postgresql or in MSSQL so having SQL scripts works out for me rather well.