Results 1 to 6 of 6
  1. #1
    mane_uk is offline Member
    Join Date
    Nov 2011
    Posts
    3
    Rep Power
    0

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

    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:

    Java Code:
    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

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,185
    Rep Power
    20

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

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

  3. #3
    mane_uk is offline Member
    Join Date
    Nov 2011
    Posts
    3
    Rep Power
    0

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

    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.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,185
    Rep Power
    20

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

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

  5. #5
    mane_uk is offline Member
    Join Date
    Nov 2011
    Posts
    3
    Rep Power
    0

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

    Thanks anyway Tolls.

    Anyone else would know the answer?

    Cheers

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,185
    Rep Power
    20

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

    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.

Similar Threads

  1. Upload excel data to access database
    By ravikumar.achi in forum New To Java
    Replies: 20
    Last Post: 01-19-2012, 12:30 PM
  2. Apache Axis Web Service Create Error
    By richierich in forum Advanced Java
    Replies: 0
    Last Post: 04-19-2011, 08:58 AM
  3. Replies: 0
    Last Post: 12-18-2008, 08:13 AM
  4. File upload error
    By jegadeeshsp in forum Advanced Java
    Replies: 2
    Last Post: 08-20-2008, 06:13 AM
  5. how to upload a file along with html form data
    By pranith in forum Java Servlet
    Replies: 3
    Last Post: 07-30-2007, 03:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •