Error while using DBUnit to upload data into my Apache Derby DB

[mane_uk]

Hi all,

I have just started coding in Java at work and have been given a task to upload an Excel File into an Apache Derby database.

I have tested the connection to my Derby DB and it works ok. I have manually "insert" and "select" results through Eclipse/Java so I know the problem is not the database.

I have then found a code example in the internet on how to use DBUnit to automatically upload an Excel File. I have therefore tweaked my code and the example creating the code below:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.File;
import java.io.FileInputStream;
import org.apache.derby.jdbc.EmbeddedDriver;
import javax.sql.DataSource;
import org.apache.derby.jdbc.EmbeddedDriver;
import org.dbunit.Assertion;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.excel.XlsDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.springframework.jdbc.datasource.DataSourceUtils;

public class LoadXLSUsingDBUnit { 

	// Connection variables
	private static final String CONN_URL= "jdbc:derby:\\\\eurfiler6home.fm.rbsgrp.net\\FDR\\MyDB;create=true;upgrade=true";
	private static EmbeddedDriver driver;
	private static Connection connect;
	private static Statement statement;
	
	// Variables copied from example
	private static DataSource dataSource;
	private final static String DATASET_PATH = "C:/dev/workspace/aaaatest/";
	  
	public static void main(String[] args) throws Exception {
		createConnection();
		setUpTestDataWithinTransaction();
	}

	private static void createConnection() throws Exception {
		// Create Connection to derby file base database
		driver = new EmbeddedDriver();
		connect = DriverManager.getConnection(CONN_URL);
		statement = connect.createStatement();
	}

  public static void setUpTestDataWithinTransaction() throws Exception {
    IDatabaseConnection dbUnitCon = new DatabaseConnection(connect);
    IDataSet dataSet = new XlsDataSet(new FileInputStream(DATASET_PATH
        + "TradeManagerXLHead.xls"));

    try {
      DatabaseOperation.CLEAN_INSERT.execute(dbUnitCon, dataSet);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      DataSourceUtils.releaseConnection(connect, dataSource);
    }
  }

}

But I am getting the following error:

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/poi/hssf/usermodel/HSSFWorkbook
at org.dbunit.dataset.excel.XlsDataSet.<init>(XlsData Set.java:56)
at LoadXLSUsingDBUnit.setUpTestDataWithinTransaction( LoadXLSUsingDBUnit.java:63)
at LoadXLSUsingDBUnit.main(LoadXLSUsingDBUnit.java:38 )
Caused by: java.lang.ClassNotFoundException: org.apache.poi.hssf.usermodel.HSSFWorkbook
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
... 3 more


I don't know even where to start to get this error sorted. Can you please help me out on this?

Many Thanks
mane_uk

[Tolls]

You need to downlaod the Apache POI jar since it appears that DBUnit uses it.

[mane_uk]

Thanks Tolls. It worked but... (there is always a but) I started having different error message. The first one was about running out of memory which I managed to fix but now I am having the following error message:

Exception in thread "main" java.lang.IllegalStateException: Cannot get a text value from a numeric cell
at org.apache.poi.hssf.usermodel.HSSFCell.typeMismatc h(HSSFCell.java:637)
at org.apache.poi.hssf.usermodel.HSSFCell.getRichStri ngCellValue(HSSFCell.java:714)
at org.apache.poi.hssf.usermodel.HSSFCell.getStringCe llValue(HSSFCell.java:697)
at org.dbunit.dataset.excel.XlsTable.createMetaData(X lsTable.java:85)
at org.dbunit.dataset.excel.XlsTable.<init>(XlsTable. java:62)
at org.dbunit.dataset.excel.XlsDataSet.<init>(XlsData Set.java:60)
at LoadXLSUsingDBUnit.setUpTestDataWithinTransaction( LoadXLSUsingDBUnit.java:78)
at LoadXLSUsingDBUnit.main(LoadXLSUsingDBUnit.java:56 )

I understand the problem being having a text entrance where I should have a numeric entrance, is that correct? Is it possible to find out, from the error description above, which cell or at least which row cause the error?

The excel spreadsheet I am uploading have 42206 rows and 60+ columns. I can't go 1 by 1 checking it.

Many Thanks.

[Tolls]

No idea I'm afraid.
I've not used DBUnit...only POI directly.

[mane_uk]

Thanks anyway Tolls.

Anyone else would know the answer?

Cheers

[Tolls]

This is the source for XlsTable (from the DBUnit online repository).
Looking at it, it's taking the first row as the column name, so presumes all cells are strings.

So check that first row and ensure all your column names are correct.