Anyone know of an equivalent of the SQLAlchemy Python data binding library for Java? SQLAlchemy lets you map 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.