storing data in a desktop app

[harryos]

hi
I am plannig to write a task /time tracking app in which a user can
add/edit tasks and later get reports about the tasks completed.I
thought of doing it as a web app with hsqldb to store data.Then ,when
I came to think about it,I wondered why I couldn't do this as a
desktop application.Is it a good idea to use rdbms in a desktop app?I
mean can I make my application in such a way that a user will not have
to do database setup separately.I am intending to give this
application to users who will have no knowledge about database systems
or their setup.Can I hide the db setup/access and everything related
to data storage inside my appliation and thus invisible to the user?Or
do I have to store data as csv?
Any help/advice most welcome
thanks & regards
harry

 

[markspace]

Is it a good idea to use rdbms in a desktop app?

I wrote a similar app a while back for myself. I didn't use a RDBMS.
It seemed far too complicated for just a little time keeping app.

Instead, I just used POJO domain objects, and serialized the object
graph to disc. Simple and easy. Now if you have more sophisticated
needs than I did, maybe an RDBMS makes sense. But I'd try it with POJOs
first, it might work just fine.

 

[Lew]

I am plannig to write a task /time tracking app in which a user can
add/edit tasks and later get reports about the tasks completed.I
thought of doing it as a web app with hsqldb to store data.Then ,when
I came to think about it,I wondered why I couldn't do this as a
desktop application.Is it a good idea to use rdbms [sic] in a desktop app?I

Use spaces after your punctuation, for God's sake!

Why wouldn't it be a good idea?

Did you Google for this first?

mean can I make my application in such a way that a user will not have
to do database setup separately.I am intending to give this\

Derby. It comes with the JDK already and can be embedded in your app just by
including the JAR file. No setup required.

application to users who will have no knowledge about database systems
or their setup.Can I hide the db setup/access and everything related
to data storage inside my appliation and thus invisible to the user?Or
do I have to store data as csv [sic]?
Any help/advice most welcome

Spaces after punctuation.

GIYF.

 

[David Segall]

I wrote a similar app a while back for myself. I didn't use a RDBMS.
It seemed far too complicated for just a little time keeping app.

Instead, I just used POJO domain objects, and serialized the object
graph to disc. Simple and easy. Now if you have more sophisticated
needs than I did, maybe an RDBMS makes sense. But I'd try it with POJOs
first, it might work just fine.

Why write the code required to try it with POJOs? An RDBMS provides
far more functionality and consists of documented, extensively tested,
code. Even a little time keeping app probably requires referential
integrity to ensure that, for example, the project you say you are
working on exists. You can use a tool like OpenOffice Base to write
test data, check your program's output and produce ad-hoc reports. In
addition, many of the embedded RDBMS's can be adapted for multiple
users with almost no extra effort. If you choose Derby and a modern
version of Java you already have it on your computer.
<http://profectus.com.au/ee_freedbms.html#Java>

 

[Lew]

Why write the code required to try it with POJOs? An RDBMS provides
far more functionality and consists of documented, extensively tested,
code. Even a little time keeping app probably requires referential
integrity to ensure that, for example, the project you say you are
working on exists. You can use a tool like OpenOffice Base to write
test data, check your program's output and produce ad-hoc reports. In
addition, many of the embedded RDBMS's can be adapted for multiple
users with almost no extra effort. If you choose Derby and a modern
version of Java you already have it on your computer.
<http://profectus.com.au/ee_freedbms.html#Java>

Looks like it's been a while since that site was updated. I suggest you
provide current links.

Here's a link for the current version of Derby / JavaDB:
http://www.oracle.com/technetwork/java/javase/downloads/index.html

You can find more documentation about the DBMS itself at:
http://db.apache.org/derby/

 

[Arved Sandstrom]

hi
I am plannig to write a task /time tracking app in which a user can
add/edit tasks and later get reports about the tasks completed.I
thought of doing it as a web app with hsqldb to store data.Then ,when
I came to think about it,I wondered why I couldn't do this as a
desktop application.Is it a good idea to use rdbms in a desktop app?I
mean can I make my application in such a way that a user will not have
to do database setup separately.I am intending to give this
application to users who will have no knowledge about database systems
or their setup.Can I hide the db setup/access and everything related
to data storage inside my appliation and thus invisible to the user?Or
do I have to store data as csv?
Any help/advice most welcome
thanks & regards
harry

Whatever you choose for persistent storage it's a good idea to separate
the implementation of it from the rest of your application. I recommend
reading http://en.wikipedia.org/wiki/Data_access_layer to start; it's a
short and sweet description of the concept that I'm talking about, the
Data Access Layer (DAL).

With a good DAL design & implementation, your business logic, when it
needs to find or save or remove domain objects, simply calls methods
like "save" or "find" or "delete" on DAL interfaces. It's transparent to
your business logic that JPA or straight JDBC or file storage is being used.

I'll caution you about the last paragraph, where it says that ORM tools
"provide data layers in this fashion". Well, no, they really don't. If
using JPA with certain popular Java ORMs, a common pattern of use sees
JPA calls sprinkled through all levels of an application. If you want to
have a proper DAL with JPA you still need to design and write a proper DAL.

Having said all that, I see no problems with using HSQLDB or Derby as
your persistence _implementation_. You can certainly make the overall
application very user-friendly.

AHS

 

[Arne Vajhøj]

I am plannig to write a task /time tracking app in which a user can
add/edit tasks and later get reports about the tasks completed.I
thought of doing it as a web app with hsqldb to store data.Then ,when
I came to think about it,I wondered why I couldn't do this as a
desktop application.Is it a good idea to use rdbms in a desktop app?I
mean can I make my application in such a way that a user will not have
to do database setup separately.I am intending to give this
application to users who will have no knowledge about database systems
or their setup.Can I hide the db setup/access and everything related
to data storage inside my appliation and thus invisible to the user?Or
do I have to store data as csv?

The big question is whether the data needs to be shared
between users.

If not then use an embedded Java database (HSQLDB, H2,
Derby aka Java DB etc.) in your desktop app. That will
be completely invisible to the user.

And I can not really see any reason not to use a database.
The JDBC API is simple and well documented. So are various
ORM frameworks and API's (like JPA).

If the users need to share data then you need a different
design.

Like one of these:

multiple instances desktop app---(JDBC)---database server

multiple instances desktop app---(SOAP/HTTP)---web service----database
server

where the databaser server is one of MySQL, SQLServer etc..

Arne

 

[Lew]

Whatever you choose for persistent storage it's a good idea to separate
the implementation of it from the rest of your application. I recommend
reading http://en.wikipedia.org/wiki/Data_access_layer to start; it's a
short and sweet description of the concept that I'm talking about, the
Data Access Layer (DAL).

With a good DAL design& implementation, your business logic, when it
needs to find or save or remove domain objects, simply calls methods
like "save" or "find" or "delete" on DAL interfaces. It's transparent to
your business logic that JPA or straight JDBC or file storage is being used.

I'll caution you about the last paragraph, where it says that ORM tools

Object-to-Relational Mapping

"provide data layers in this fashion". Well, no, they really don't. If
using JPA with certain popular Java ORMs, a common pattern of use sees
JPA calls sprinkled through all levels of an application. If you want to
have a proper DAL with JPA you still need to design and write a proper DAL.

Having said all that, I see no problems with using HSQLDB or Derby as
your persistence _implementation_. You can certainly make the overall
application very user-friendly.

+1.

As an advanced topic, there can be a DAL that is not monolithic. DAL
functionality can be implemented by service-specific instances that parallel
each other within the DAL.

There are pros and cons as with all tool selections.

ORM tools *can* provide a DAL "in this fashion" if you don't abuse them, e.g.,
by falling into the "sprinkle" trap.

Tools can be abused, and don't apply everywhere. JPA is for a common use
case, persistence-backed object models. It is not intended to present a
data-oriented persistence view to the application. People often attempt to
suborn JPA's automatic features in a misguided attempt to control data access.
This leads to clumsy, difficult-to-maintain data "layers".

With a solid object model, you accomplish Arved's advice to separate data
concerns cleanly from your application. For most scenarios the application
sees only objects and their relationships handed up by JPA. Corner cases you
hide behind service interfaces that live at roughly the same layer as the ORM
or barely above it.

As a rule of thumb, any type that knows about 'EntityManager' should be in the
same layer.

 

[Tom Anderson]

I am plannig to write a task /time tracking app in which a user can
add/edit tasks and later get reports about the tasks completed.I thought
of doing it as a web app with hsqldb to store data.Then ,when I came to
think about it,I wondered why I couldn't do this as a desktop
application.Is it a good idea to use rdbms in a desktop app?I mean can I
make my application in such a way that a user will not have to do
database setup separately.I am intending to give this application to
users who will have no knowledge about database systems or their
setup.Can I hide the db setup/access and everything related to data
storage inside my appliation and thus invisible to the user?Or do I have
to store data as csv?

You can do that, no problem. The embedded databases like HSQLDB will
create databases in local files, which behave just like any other files.

Don't use HSQLDB, though, use H2. It's faster, and has better SQL support.

tom

 

[Tom Anderson]

Why write the code required to try it with POJOs?

Because it's about six lines - three to save, three to load. Working with
an RDBMS is pretty easy, working with an ORM is even easier, but working
with objects and serialization is dumfoundingly easy.

An RDBMS provides far more functionality

True - and where that's needed, and RDBMS is a great way to get it.

and consists of documented, extensively tested, code.

As does serialization.

Even a little time keeping app probably requires referential integrity
to ensure that, for example, the project you say you are working on
exists.

Serialization provides that. Or rather, the Java object model provides
that, and serialization captures it.

I'm not saying that serialization is a good choice for all situations, but
it's an eminently reasonable thing to do for a simple app, or as a first
cut at storage in a complex app. Start with it, and add a database when
you have a reason to.

tom

 

[Arne Vajhøj]

You can do that, no problem. The embedded databases like HSQLDB will
create databases in local files, which behave just like any other files.

Don't use HSQLDB, though, use H2. It's faster, and has better SQL support.

HSQLDB is more used than H2.

But I agree that H2 is pretty cool.

I love ALLOW_LITERALS=NONE !

Arne

 

[markspace]

Why write the code required to try it with POJOs?

Tom has the right of it. I didn't write any code, it's all done for me
by the basic Java runtime. First with ObjectOutputStream, then with
XMLEncoder/Decoder.


<http://download.oracle.com/javase/6/docs/api/java/beans/XMLEncoder.html>


I'm curious what you thought I was actually advocating.

 

[Arne Vajhøj]

Because it's about six lines - three to save, three to load. Working
with an RDBMS is pretty easy, working with an ORM is even easier, but
working with objects and serialization is dumfoundingly easy.


True - and where that's needed, and RDBMS is a great way to get it.


As does serialization.


Serialization provides that. Or rather, the Java object model provides
that, and serialization captures it.

I'm not saying that serialization is a good choice for all situations,
but it's an eminently reasonable thing to do for a simple app, or as a
first cut at storage in a complex app. Start with it, and add a database
when you have a reason to.

XML serialization can be a solution where search and concurrency
are not issues.

I will argue against binary serialization for persistence. Too high
a risk of problems being able to access the data in the future.

Arne

 

[Lawrence D'Oliveiro]

In message

Is it a good idea to use rdbms in a desktop app?

Why not? SQLite3 is heavily used for precisely this sort of thing, for
example in Firefox/Iceweasel. It’s lightweight enough to be found on Android
mobile phones.

I mean can I make my application in such a way that a user will not have
to do database setup separately.

1) if (!DatabaseExists())
{
CreateDatabase();
} /*if*/

2) Follow conventions like the XDG Base Directory Specification
<http://standards.freedesktop.org/basedir-spec/latest/> to reduce clutter
and make it easy for users to backup, restore and transfer your data.

 

[Lawrence D'Oliveiro]

... but working with objects and serialization is dumfoundingly easy.

I like to keep external data formats separate from the internal structure of
my code. That way, changes to the latter don’t impact the former.

 

[David Segall]

Looks like it's been a while since that site was updated.

That's true but I checked the links on the page before I posted and
they all retrieved the site that I intended.

I suggest you
provide current links.

Here's a link for the current version of Derby / JavaDB:
http://www.oracle.com/technetwork/java/javase/downloads/index.html

That's a general download link but the specific JavaDB URL is the one
I provided. It resolves to

You can find more documentation about the DBMS itself at:
http://db.apache.org/derby/

That's the URL on my site.

I try to ensure that the information at <http://profectus.com.au>
is current or to tell a visitor that it is not. I do appreciate any
corrections or updates but it seems sensible to let Oracle provide the
redirects until they have settled their site.

 

[Lew]

That's true but I checked the links on the page before I posted and
they all retrieved the site that I intended.

That's funny, because the first link I clicked on that site went all 404 on me.

 

[David Segall]

That's funny, because the first link I clicked on that site went all 404 on me.

Please forward it to me. The address is david "at" profectus.com.au
but clicking on the email address at
<http://profectus.com.au/contact.html> is probably easier.

 

[Lew]

Please forward it to me. The address is david "at" profectus.com.au
but clicking on the email address at
<http://profectus.com.au/contact.html> is probably easier.

The link labeled "IBM DB2 Express-C" wouldn't come up.

The link to SmallSQL takes you to a page that lists the "Advatages
[sic]" of the product and points out that "It does not has [sic] a
network interface". I suggest you not include them any more.

McKoi DB tells us, "Note that Mckoi SQL Database hasn't seen a new
release since 2004." You should drop them, too.

 

[Lew]

Because it's about six lines - three to save, three to load. Working with
an RDBMS is pretty easy, working with an ORM is even easier, but working
with objects and serialization is dumfoundingly easy.


True - and where that's needed, and RDBMS is a great way to get it.


As does serialization.

That does not mean that serialization is easy to program.

It is not.

Serialization provides that. Or rather, the Java object model provides
that, and serialization captures it.

I'm not saying that serialization is a good choice for all situations, but
it's an eminently reasonable thing to do for a simple app, or as a first
cut at storage in a complex app. Start with it, and add a database when
you have a reason to.

Given the relative ease of RDBMS programming and the trickiness most
people ignore in handling serialization, plus the propensity of any
successful software package to grow and expand, starting with a
cleanly-separated persistence layer and an RDBMS is not a very risky
choice.

Serialization will get your prototype out the door, but somewhere in
Week 2 you're going to start trying to report on the persistent
information using /ad hoc/ query dimensions.

Java serialization also locks your class design into yet another
public interface, only this one includes the private implementation.
That's a heavy maintenance price to pay for trying to make life
easier.

At the very least, read the warning, cautions and idioms in /Effective
Java/ regarding serialization before you shoot yourself in the foot
with it.

Given the low overhead of RDBMSes and the plethora of frameworks such
as JPA to work with them, and the dangers and difficulties of
serialization, I would never consider serialization as a persistence
mechanism.