Introduction

The database framework provides an Object/Relational mapping tool that generates java objects corresponding to database tables. The database framework resides under the following packages:
fc.jdbc
contains misc. utility and connection pooling classes.
fc.jdbc.dbo
contains code that maps database tables to java objects (this is called Object/Relational mapping).
The database classes were written to be correct in a multithreaded environment, with full knowledge of the Java Memory Model. They are reliable and well-tested.

Additionally, the O/R tool is the best mapper available today. It keeps common tasks simple and transparent. Instead of the typical kitchen-sink approach taken by many O/R tools, functionality best left to straight JDBC/raw SQL, is not attempted. (the framework is equally notable for what it leaves out as what it leaves in).

Some features include:

  1. No messy XML configuration files. The O/R tool runs from the command file and uses a simple property file.
  2. Several options (as and when needed) to tweak the generated code. One can include/exclude tables and specify accessor method names in many different styles (lower, UPPER, Sentence, FirstUp, under_score).
  3. Objects are smart and their toString method prints object contents in a nicely formatted table.
  4. Since JDBC transactions are tied to a connection, all O/R generated methods take a explicit connection parameter, making transaction support built-in.
  5. PreparedStatements are used internally, as much as possible.
  6. The generated code is easy to read, fast and highly efficient. You can do thousands of transations a second, you are only bound by the DB throughput, not Java code performance. When updating themselves, modified objects only send modified data back to the server. Updated serial/auto-increment information is available in the object after it's been saved.
  7. This framework is database independent and tested against both PostgreSQL and MySQL. (There are some internal database specific parts not visible to the user. These are isolated in seperate classes and makes adding other databases easy).

    New note:molly was designed to be DB independent (and I believe has stronger, better tested code because of it). But, it turns out, MySQL is a shit database with a shit philosophy and design, right from it's shit beginning(s), so I've dropped all testing against MySQL (the framework will probably work fine with it though). Same goes for Oracle DB in my opinion. Just use PostgreSQL and be done with it, but then you already know that, don't you?

You can see more details on the advantages/limitations of O/R (well worth reading).


Running the O/R tool

For each database table Foo, two java classes are generated.
class Foo
represents an instance of a row in table Foo (each attribute of the table becomes an instance field in this class)
class FooMgr
contains static methods to query table Foo and save, insert, retrieve instances of class Foo

Let's see how this works.

To use the mapper, first (of course) make sure the molly framework is installed. Then have your favorite database up on running and reachable via a JDBC driver. To run the mapper, just say:

  java fc.jdbc.dbo.Generate -conf configuration_file

  configuration_file is the path to the generate configuration file.
The single configuration file specified to the fc.jdbc.dbo.Generate program controls all of the options to use when generating java classes. Many of these options can be left to their default value.

As with most classes in the molly framework, to see all the configuration and usage options, simply say:

  java fc.jdbc.dbo.Generate
It is a very good idea to do this at least once to get a feeling of what is possible.

Here are some sample (simplified) configuration files to get you started: PostgresQL and MySQL.

O/R in action

Step 1

For purposes of this discussion, we are going to use a sample database table called mollytest.

The SQL statements to create this table is shown below. (postgres/mysql differences in blue).

For PostgreSQL:
create table mollytest ( 
  uid            serial,
  name           varchar(99),
  email          varchar(99),
  password       varchar(99),
  created_on     date,
  is_active      boolean,
  PRIMARY KEY (uid) 
  );

For MySQL:
create table mollytest ( 
  uid            int auto_increment,
  name           varchar(99),
  email          varchar(99),
  password       varchar(99),
  created_on     date,
  is_active      bool, 
  PRIMARY KEY (uid) 
  );

Step 2

Now, we run the O/R generator.

PostgreSQL:

root@turing:/examples/dbo# java fc.jdbc.dbo.Generate -conf postgres.cnf

INFO    0  fc.util.FilePropertyMgrLoaded properties from file: postgres.cnf
INFO    0  New log level set to: LogLevel [3,INFO]
DriverManager.getConnection("jdbc:postgresql://localhost/test")
    trying driver[className=org.postgresql.Driver,org.postgresql.Driver@4741d6]
getConnection returning driver[className=org.postgresql.Driver,org.postgresql.Driver@4741d6]
INFO    0  Output Directory: .
INFO    1  >>>> Processing table: mollytest
JVM shutdown: fc.io.Log - closing all logs...

root@turing:/examples/dbo# ls

Count.java              Getall.java             Update.java
CreateTable.java        Getbykey.java           mollytest.java
Delete.java             Getusing.java           mollytestMgr.java
Deletebykey.java        Getwhere.java           mysql.cnf
Deletewhere.java        Save.java               postgres.cnf
Exists.java             Savetiming.java         runs
Foo.java                Test.java
MySQL:
root@turing:/examples/dbo# java fc.jdbc.dbo.Generate -conf mysql.cnf

INFO    0  fc.util.FilePropertyMgrLoaded properties from file: mysql.cnf
INFO    0  New log level set to: LogLevel [3,INFO]
DriverManager.getConnection("jdbc:mysql://localhost/?autoReconnect=true")
    trying driver[className=com.mysql.jdbc.Driver,com.mysql.jdbc.Driver@b166b5]
getConnection returning driver[className=com.mysql.jdbc.Driver,com.mysql.jdbc.Driver@b166b5]
INFO    1  Output Directory: .
INFO    1  >>>> Processing table: mollytest
JVM shutdown: fc.io.Log - closing all logs...

root@turing:/examples/dbo# ls

Count.java              Getall.java             Update.java
CreateTable.java        Getbykey.java           mollytest.java
Delete.java             Getusing.java           mollytestMgr.java
Deletebykey.java        Getwhere.java           mysql.cnf
Deletewhere.java        Save.java               postgres.cnf
Exists.java             Savetiming.java         runs
Foo.java                Test.java
It's interesting to note that despite the copius amounts of generated code, this entire process takes about a second.

Classes generated by the mapper are fully documented via javadoc comments. After they are generated, it is a good idea to run javadoc and view the method desciptions.

Take a minute and browse through:

  1. The source for the generated classes (mollytest.java and mollytestMgr.java).
  2. The javadoc for the above two classes.
To see examples of these generated classes being used, browse through the O/R in action links at the top of the page.