Hibernate CSV Loader

A typical issue when developing a web application is needing to add test data to a database or needing data to be added to a live database as it makes up an essential part of the application. Certain frameworks allow you to add data to a database through "*.sql" scripts. This can be frustrating when working with Hibernate as you may encounter syntax issues although this is unlikely. Also adding multiple insert statements into scripts is just simply unnecessary and very frustrating if its allot of data.

I avoid this headache through using a simple CSV loader class. I can then edit the CSV file through a spreadsheet package. I have found that strings are also surrounded by quotes by default which makes creating insert statements really simple.

Below is a dump of the class that I use to insert data into a database.

Here are some simple instructions when using the class:

1. The name of the csv file is the name of the table.
2. The first row of the table contains the name of the columns in the database.
3. If the database requires unique insert syntax just overload the class and add a new insert statement.
4. The CSV file should be in UTF8 format.
5. Has issues with some special characters. Certain quotes has a nasty habit of breaking the code.

Heres the class:

public class LoadDatabase {

private static Logger log = Logger.getLogger(LoadDatabase.class);
private static Session session = getSessionFactory().getCurrentSession();
private static String SQL_INSERT = "INSERT INTO ${table}(${keys}) VALUES(${values})";
private static final String TABLE_REGEX = "\\$\\{table\\}";
private static final String KEYS_REGEX = "\\$\\{keys\\}";
private static final String VALUES_REGEX = "\\$\\{values\\}";


/**
* To allow running from command line for when live system is being built
*
* @param args
* @throws Exception
*/

public static void main(String[] args) throws Exception {
// The only arguement is the directory containing the CSV files

String csvDirectory = args[0];
if (csvDirectory == null) {
throw new Exception(
"No CSV directory supplied to LoadDatabase class");
}
File csvFiles = new File(csvDirectory);
run(csvFiles);
}


public static void run(File csvDirectory) throws Exception {

if (!csvDirectory.exists() || !csvDirectory.isDirectory()) {
throw new Exception(
"You can only supply a directory to this LoadDatabase");
}


log.info("Retrieving list of csv files contained within :"
+ csvDirectory.getName());
CsvFilter csvFilter = new CsvFilter();
File[] csvFiles = csvDirectory.listFiles(csvFilter);
for (File csvFile : csvFiles) {
loadDatabaseData(csvFile);
}
}


/**
* This method assumes that Hibernate is set up already and can be loaded
* with data contained in the CSV file.
*
* @param csvFile
* @throws Exception
*/

private static void loadDatabaseData(File csvFile) throws Exception {
String filename = csvFile.getName();
log.info("Reading contents of :" + filename);

String databaseName = filename.substring(0, filename.indexOf("."));
log.info("Obtained database name: " + databaseName);

BufferedReader in = new BufferedReader(new FileReader(csvFile));

// First line will allways be the column names
String keys = in.readLine();
if (keys == null || keys.length() == 0) {
throw new Exception("No columns defined in :" + filename);
}

// trailing garbage plus " from column names
keys = keys.replaceAll("\"", "");
String insertTemplate = SQL_INSERT.replaceFirst(TABLE_REGEX, databaseName);
insertTemplate = insertTemplate.replaceFirst(KEYS_REGEX, keys);
String values = null;
while ((values = in.readLine()) != null) {
values = values.replaceAll("\"", "\\\'");
insertIntoDatabase(insertTemplate.replaceFirst(VALUES_REGEX, values));
}

}


/**
* Calls hibernate and inserts the datainto the database
*
* @param statement
*/

private static void insertIntoDatabase(String statement) {
log.debug(statement);
session.beginTransaction();
session.createSQLQuery(statement).executeUpdate();
session.flush();
log.debug("SQL SUCCEEDED");
}


/**
* Private class to allow only CSV files to be read. Could have been in a
* different class but thought it would be more portable this way.
*
* @author leighgordy
*
*/

private static class CsvFilter implements FilenameFilter {

public CsvFilter() {
}


public boolean accept(File dir, String name) {

if (name.toLowerCase().endsWith(".csv")) {
return true;
} else {
return false;
}
}

}
}