Multi-thread/database design question

Hello, everyone. Unfortunately, I'm pretty new to databases - I've
been searching this and other newsgroups for information I need to
build a (relatively) small application in Java. I've gathered some
good information, and I was hoping someone could give me some
opinions/advice on my current ideas (sorry for the length).

I'm writing an application with an embedded db for a single user. The
project involves a piece of hardware (connected via USB/serial port)
that receives data from various robots out in a field. The data comes
in "randomly" (i.e. only when the robots do something important - not
necessarily consistent). The main table in the database is basically a
log for this data, and other tables contain other data for the robots.
The application is mainly a way for the user to monitor this data, but
it also lets him/her edit or add new data.

I decided to use Java (SE) with an embedded HSQL database. Based on my
research, it sounds like one connection is best for each object that
wants to hit the database. So I will certainly try to use thread and
connection pooling.

My overall design is this: some objects (mostly GUI objects) can spawn
a Reader thread that grabs info from the database and updates (each
with its own connection). Other objects (the hardware thread and other
GUI objects) can spawn a Writer thread to send data to the database
(again with separate connections). A central object basically handles
the messaging - Writers tell the central object that they've written
something, and then it passes the message along to interested Readers.

My biggest concern is with the Writer. I've read that the DBMS handles
most concurrency/sync issues, but should I create a Singleton Writer
(only one "modifier" connection) instead of multiple copies? What are
typical issues with multiple connections trying to edit a table? Are
there any other major issues that jump out with this overall design?
I'm sorry again for the length, and I appreciate any help. Thanks!

-Richard

Richard wrote:
>
> I decided to use Java (SE) with an embedded HSQL database. Based on my
> research, it sounds like one connection is best for each object that
> wants to hit the database. So I will certainly try to use thread and
> connection pooling.

Unless HSQL has improved radically, using multiple threads wont help. It
executes (and even parses) each statement while holding a lock for the
entire database.

I don't see any particular reason why you should want one connection per
object.

Tom Hawtin
--
Unemployed English Java programmer
http://jroller.com/page/tackline/