Database Reportng

Hi all, I have recently been give a project to create an invoicing system for a small business. The system needs to be able to easily add new customers and store them for usage within invoices. There also needs to be a form to enter invoice data and invoices need to be printable to a format which will match up with pre-printed invoice paper.

Naturally, I will need a database to accomplish this, but this database should be portable so it can be used on multiple machines (Linux and Windows, never simultaneously.) I attempted to create this entire system using LibreOffice Base but ran into problems using the Oracle Report Builder, namely its instability and inability to create fields which expand vertically to contain large strings. As a result, I am now looking to create the system using Java.

Due to the portability requirement, I was thinking of using a sqlite after reading that they can easily be moved between machines. Can others confirm that this would be a good choice? I only need 4 tables with simple relationships (Customer, Invoice, Transaction and TaxRate) and I am confident given my previous attempts that this table setup is sufficient. Also, is there a particular library recommended for interfacing with sqlite?

Where I really need advice is generating reports from records in the database. Can anyone suggest what I should be using to create reports for the invoices which I can configure to match up with pre-printed paper? Preferably a free solution. I've read that Jasper reporting engine is popular, but I could do with some pointers to get started as this will be the first time I'll have gone beyond basic database access using Java.

Thanks in advance for any help!

[bibiki]

I am not sure that I can help you much about reporting, but I think you should use Derby for database. It's easy to make use of, and as of Java, I think 7, it ships along with Java SDK. it is an embedded DB like sqlite, but is easier to work with. Last time I tried, I gave up on SQLite, and made use of Derby.

I completely agree with you, bibiki.

Derby (former Cloudscape from Informix/IBM) strictly follows the ANSI/ISO SQL and latest JDBC standard. And it is very easy to work with it and integrate it in Java. Btw, Derby is 100% Java implemented in contrast to SQLite which is C/C++ (and still lacking a native JDBC driver, only ODBC wrapper available).

One can easily switch between embedded mode and server mode, so Derby is a truly complete SQL database for Java applications.

Also see: Click Here and Click Here (and compare these specs with SQLite spec)

-- 1stDAN

As for a reporting tool, I would suggest JReport. However, your database comprices only four tables, so it seems to me there isn't that much for reporting. Maybe all reports / lists could easily be created with standard Java println, printf? If you create effective SQL select statements jointing appropriate tables together, the result sets are usually very close to required reports.

-- 1stDAN