How to implement simple DB persistence

[tnorgd]

Dear Group,

I have a bunch of classes (say, 5-10) that I would like to serialize
into an SQL database. I decided to implement this on my own, because:
- I need to keep control over the data; not all the fields from a
class need to go into DB
- the DB storage format must be easy for humans who are browsing the
DB; there are also scripts in R for data analysis that will read from
the DB
- Stuff like Hibernate is a too heavy gun for it;

My original idea was to add a constructor initialized with SQL
connection, or a pair of static methods for reading from / writing to
a DB into each of my classes. I also plan to use an existing package
to facilitate SQL query building.

In general, I have three goals:
- write my own code that:
- will store the data in the format I need,
- spending as short time on it as necessary

Can you share some advices or some parts of working code for some
inspiration?

Tim

PS. Another issue with it is that my data comes from experiments and
each observation (rather than an object) is unique. What I mean is
that (as I understand) in typical persistence approach each Java
object can be serialized / deserialized as a unique entity. In my case
each row of data is unique and DB must store exactly one copy of it,
no matter how many objects keep this data. Luckily I have a way to
annotate the rows (e.g time stamp or measurementID)

 

[Alan Gutierrez]

Dear Group,

I have a bunch of classes (say, 5-10) that I would like to serialize
into an SQL database. I decided to implement this on my own, because:

- Stuff like Hibernate is a too heavy gun for it;

iBatis?

 

[Tom Anderson]

I have a bunch of classes (say, 5-10) that I would like to serialize
into an SQL database. I decided to implement this on my own, because:
- I need to keep control over the data; not all the fields from a
class need to go into DB
- the DB storage format must be easy for humans who are browsing the
DB; there are also scripts in R for data analysis that will read from
the DB
- Stuff like Hibernate is a too heavy gun for it;

How heavy do you think that gun is, and how light would a gun have to be
to be acceptable to you?

My original idea was to add a constructor initialized with SQL
connection, or a pair of static methods for reading from / writing to
a DB into each of my classes.

The latter, but don't make them statics in the domain model class, make
them instance methods on some sort of Persister object. Keep the
persistence logic as far from the domain model as you can.

Can you share some advices or some parts of working code for some
inspiration?

I got nothin'.

If you want to use foreign keys in the database, you may need to
topologically sort object graphs before persisting them. There are
well-known algorithms for this, but implementing them might be a pain.

PS. Another issue with it is that my data comes from experiments and
each observation (rather than an object) is unique. What I mean is that
(as I understand) in typical persistence approach each Java object can
be serialized / deserialized as a unique entity. In my case each row of
data is unique and DB must store exactly one copy of it, no matter how
many objects keep this data. Luckily I have a way to annotate the rows
(e.g time stamp or measurementID)

Object-relational mappers, like Hibernate and other JPA providers, also
have this concept of entity identity that's stronger (in a sense) than
simple object identity. This constraint would follow naturally from a JPA
implementation.

tom

--
The players listen very carefully and respectfully to all that the clever
men have to say about what is to happen in the next generation. They
players then wait until all the clever men are dead, and bury them
nicely. Then they go and do something else. -- The Napoleon of Notting
Hill, G. K. Chesterton

 

[Lew]

I have a bunch of classes (say, 5-10) that I would like to serialize
into an SQL database. I decided to implement this on my own, because:
- I need to keep control over the data; not all the fields from a
class need to go into DB
- the DB storage format must be easy for humans who are browsing the
DB; there are also scripts in R for data analysis that will read from
the DB
- Stuff like Hibernate is a too heavy gun for it;

My original idea was to add a constructor initialized with SQL
connection, or a pair of static methods for reading from / writing to
a DB into each of my classes. I also plan to use an existing package
to facilitate SQL query building.

In general, I have three goals:
- write my own code that:
- will store the data in the format I need,
- spending as short time on it as necessary

Can you share some advices or some parts of working code for some
inspiration?

Do not reinvent the wheel. While the DB binary formats are not
"human-readable", on account of they're optimized for computer use (e.g.,
CRUD), the existing lightweight databases are robust, largely standards
compliant and quite easy to use. Go with Derby (a.k.a. Java DB) - you have it
already and it'll save you time, energy and bugs.

 

[tnorgd]

I see all of you recommend using an existing tool rather than creating
a new one. OK, so I keep thinking of it. What would you suggest to
satisfy my following criteria:
- should be small. I checked that for Hibernate I have to download
56MB of compressed archive...
- must not use XML - I personally hate it. I would love to use
annotations or some text format for configurations (e.g. yaml)
- data stored in the resulting SQL tables must be human-readable. It
must be possible to log directly into a database and run an SQL query,
browse the results, etc.

Tim

 

[Robert Klemme]

I see all of you recommend using an existing tool rather than creating
a new one. OK, so I keep thinking of it. What would you suggest to
satisfy my following criteria:
- should be small. I checked that for Hibernate I have to download
56MB of compressed archive...

So it's small, isn't it?

- must not use XML - I personally hate it. I would love to use
annotations or some text format for configurations (e.g. yaml)

XML *is* a text format. But yes, you can uses JPA with annotations and
can probably get by without any XML. However, you are making your life
unnecessary hard by turning down what nowadays is an industry standard
(I mean XML of course).

- data stored in the resulting SQL tables must be human-readable. It
must be possible to log directly into a database and run an SQL query,
browse the results, etc.

AFAIK this is possible with all RDBMS.

Cheers

robert

 

[Lew]

So it's small, isn't it?

Particularly if you compare that to what you need if you don't
download Hibernate.

I am slightly puzzled by the OP's obsession with Hibernate. You can
do find with just JDBC and your flavorite favor of RDBMS (such as
Derby or Postgres). However you still need to map your object model
to your relational one, and a good object-relational mapper (ORM) tool
like Hibernate or EclipseLink or OpenJPA takes care of a lot of the
scut work for you.

Again, you don't need JPA or any other ORM to use a database from
Java. Read up on JDBC if you're comfortable with that approach. With
JDBC you don't use XML or annotations; everything is laboriously hand-
crafted into the code. With JPA you have a choice to mix and match
varying degrees of XML and annotation magic.

ORM or raw JDBC, either way you can pretty much plug in the database
of your choice. With JPA you hide the connection details in an XML
(gasp!) file called "persistence.xml" or "orm.xml". With Tomcat and
other app servers you hide the connection information in an XML
(gasp!) file such as "context.xml".

I want to drive a car but I don't want to use a garage for
maintenance. I personally hate it. I would love to use my own timing
light, tools, lift, ratchet wrench, computerized engine-diagnostic
unit, left-handed stembolt cutter, ...

XML *is* a text format.  But yes, you can uses JPA with annotations and
can probably get by without any XML.  However, you are making your life
unnecessary hard by turning down what nowadays is an industry standard
(I mean XML of course).

AFAIK this is possible with all RDBMS.

That is correct, Robert. I thought by "human-readable" the OP had
meant something different, but given his explanation shown here, it's
clear that not only relational but all database management systems
(DBMSes) with a JDBC driver fulfill the requirement.

tnorgd, anything you use would have to meet this last requirement
trivially. The whole freaking point of a DBMS is to persist data,
preferably with some integrity guarantees. That means that any
interface to the DBMS, be it their own query tool, a third-party SQL
client, ODBC access, JDBC access or what-have-you, must be able to
access the data regardless of which of those interfaces induces
changes.

Likewise the whole freaking point of JDBC is to connect a Java program
to a DBMS in a (nearly) portable fashion, segregating business logic
into the Java side and data matters into the DBMS side. Any solution
you use, JPA or not, will ultimately rest on JDBC calls. That's what
they're for.

For the DBMS I recommend Derby or Postgres. Others have spoken
favorably of H2 and SQLLite. YMMV.

 

[Robert Klemme]

I want to drive a car but I don't want to use a garage for
maintenance. I personally hate it. I would love to use my own timing
light, tools, lift, ratchet wrench, computerized engine-diagnostic
unit, left-handed stembolt cutter, ...






That is correct, Robert.

Thanks for the clarification, Lew - although I do see now that my
attempt at irony failed miserably.

Cheers

robert

 

[RedGrittyBrick]

I want to drive a car but I don't want to use a garage for
maintenance. I personally hate it. I would love to use my own timing
light, tools, lift, ratchet wrench, computerized engine-diagnostic
unit, left-handed stembolt cutter, ...

When I think about the tools I've accumulated in my own garage over the
years, I don't think this analogy has the witheringly sarcastic effect
you might have intended.

 

[Lew]

When I think about the tools I've accumulated in my own garage over the
years, I don't think this analogy has the witheringly sarcastic effect
you might have intended.

It wasn't meant to be sarcastic, let alone witheringly so, at all. /Au
contraire/, it was meant to illustrate a preference that someone might
reasonably aver but with clear burden. You have just proven out the analogy.