SQLAlchemy for Java
Anyone know of an equivalent of the SQLAlchemy Python data binding library for Java? SQLAlchemy maps Python classes to the records in a table. What distinguishes it from Java-based ORM tools that I’ve seen such as Hibernate is that in SQLAlchemy you can map any table, not just ones that actually exists in the database.
SQLAlchemy doesn’t view databases as just collections of tables; it sees them as relational algebra engines. Its object relational mapper enables classes to be mapped against the database in more than one way. SQL constructs don’t just select from just tables—you can also select from joins, subqueries, and unions. Thus database relationships and domain object models can be cleanly decoupled from the beginning, allowing both sides to develop to their full potential.
In other words, with SQLAlchemy you can make statements like SELECT Student.Name, Exam.Grade FROM Students, Exams WHERE Students.ID=Exams.Student_ID and map classes to the table this statement returns. Hibernate, by contrast, really, really believe in the concept of at least one-class per table. A single class that crosses multiple tables is beyond it. In Hibernate you have to map classes to the physical database tables (or perhaps views, not sure about that) and then do your queries against the objects in HQL instead of against the tables in SQL. The you spend a ridiculous amount of time logging the SQL statements it actually generates and trying to figure out how to optimize them. By contrast, since SQLAlchemy’s query language is SQL, it’s much easier to let the database do the heavy lifting.
The downside of SQLAlchemy is that it’s very easy to put yourself in a situation where updates just aren’t possible. However, for many applications read-only access is what you need anyway.
Maybe iBatis?
June 28th, 2007 at 3:11 PM
SQLAlchemy is what I’d call a first-order embedding of SQL in a OOP language (as opposed to things like JDBC which are a zeroth-order embedding). Hibernate and friends are just persistent objects, not access to SQL stores. I applaud its development and hope someone comes up with a static (i.e. Java) version soon.
June 28th, 2007 at 4:44 PM
JPA implementations support populating Java objects from columns in multiple “secondary tablesâ€, like your simple example. But they’re still going to write the SQL for you.
If you want to write your own SQL and populate existing Java objects from columns in the ResultSet, I think you’re looking for iBATIS.
June 29th, 2007 at 9:28 AM
The support for native queries has improved a lot since Hibernate 3. It is really no problem to map a read-only object to a query. A lot of examples can be found here: http://www.hibernate.org/hib_docs/v3/reference/en/html/querysql.html.
On the other hand the join-element allows it to map an object that crosses multiple tables. We did that a lot, because we had objects with some properties being subject to auditing and some properties being not. So we split up the object in one table with additional auditing information and one table without. We created a view (leaving out the auditing information) for the first table and some instead-of-triggers to make the view look and feel like a table. In the class-mapping, we sat dynamic-update and dynamic-insert to true. That in turn makes sure, that the view or the join-table only get updated if their respective properties were changed.
In SQL you end up writing a lot of joins when you query those objects. In HQL you look at objects, hence Hibernate writes the joins for you.
As to provide for later optimization we put all our queries into the mapping files. By doing so we could replace HQL-queries transparently with SQL-queries optimized for the RDBMS in use.
The only thing we stumbled upon was locking. If you lock such an object only the record in the master table gets locked rather than all the records in all the tables belonging to the object.