In these O/R in action examples, a java test program using the generated
O/R class is run from the command line. You can view the source of the program
by clicking on the program name. These programs are (mostly) quite small,
showing the full power of O/R. The program results are shown verbatim. Then, a
corresponding SQL result (typed in directly at the database prompt) is shown
afterword. (this shows independent verification of what the program actually
did).
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:
-
No messy XML configuration files. The O/R tool runs from the
command file and uses a simple property file.
-
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).
-
Objects are smart and their toString method prints object contents in a nicely formatted table.
-
Since JDBC transactions are tied to a connection, all O/R generated methods take a explicit connection parameter, making transaction support built-in.
-
PreparedStatements are used internally, as much as possible.
-
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.
-
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:
- The source for the generated classes (mollytest.java
and mollytestMgr.java).
- 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.
Advantages and Limitations of O/R mapping
PreparedStatements are essential for
all web based applications. They protect against SQL injection attacks.
This framework uses JDBC PreparedStatements internally (except when
marked otherwise in the API docs).
But JDBC prepared statements can be a hassle to type and maintain. The
main hassle being that we have to manually match the '?' with a position
number and a type. For example:
PreparedStatement ps = con.prepareStatement(
"select * from foo where " +
a=? //1
and b=? //2
and c=? //3
");
ps.setString(1, "abc");
ps.setInt(2, 123);
ps.setTimestamp(3, new java.sql.Timestamp());
We have to remember that the
? at position
2 is
an
integer.
No big deal for a small set of queries but when we have
lots of evolving and ad-hoc queries, this can get tiring. If
we change the query even a bit, then we have to modify the
parameter numbers by hand. For example, if we remove a parameter,
we have to renumber/reduce the numbers of all subsequent positions
by 1. If the type of some position changes (from say a date to
a timestamp), then we have to also remember to change setDate
to setTimestamp for that position.
Runtime errors: With PreparedStatements, if we mistakenly type the wrong
ordinal:
Good: ps.setInt(2, 123);
Bad: ps.setInt(3, 123);
or the wrong type:
Good: ps.setInt(2, 123);
Bad: ps.setInt(2, "123");
we get no warning until the query is run.
Here's the equivalent O/R way of doing the above:
foo f = new foo();
f.set_a("abc");
f.set_b(123);
f.set_c(new java.sql.Timestamp());
fooMgr.save(con, f);
There are a couple of immediate benefits:
-
Compile-time safety:
foo.set_Y(Y_TYPE);
If column Y and Y_TYPE do not match, a compile-time error is
generated. -
It's also easier to see what is being set, because instead of
using setInt(2, "abc"), we say set_a("abc");
- If a column is changed or removed, we don't have to renumber
anything.
-
Safety is guaranteed because PreparedStatements are used internally.
//This is obtained from an HTML form and can contain arbitrary sql-injection attacks.
String name = "foo'; drop table users; "
mollytest proto = new mollytest();
proto.set_name(name);
List list = mollytestMgr.getUsing(con, proto);
This is totally immune from any sql-injection attacks (the users table in this particular example cannot be dropped).
Limitations
O/R generated code is good for often-used common queries.
Queries that use arbitrary SQL, database functions, operators
etc., are best left to JDBC (which is very simple to use
anyway). I cannot stress this point enough ! Anything not-obvious after about 10 minutes of
O/R use is a candidate for straight JDBC.
NamedParamStatement and QueryReader are your friends and these make
working with JDBC queries much easier. Templated queries are thus stored in a file and
read/managed by these classes. The results of these can - optionally - be used by the O/R
framework and exposed as O/R objects, but the queries themselves are managed separeately.