JRel 0.10

JRel is a Java relational expression library.

JRel is an open source library for the Java language which can be used to construct database queries in a programmatic fashion using a subset of the relational algebra. These queries are expressions in the form of 100% native Java code, and are translated at runtime into conformant ANSI-SQL usable with a variety of SQL databases.

Example:

Query query = new Query()
.from(customer, product, order)
.restrict(customer.ID).eq(5))
.project(customer.FIRST_NAME, customer.ID, product.ID)
.join(product.ID.eq(order.PRODUCT_ID))
.join(customer.ID.eq(order.CUSTOMER_ID));

becomes:

SELECT c.first_name, c.id, p.id
FROM order AS o
JOIN product AS p ON p.id = o.product_id
JOIN customer AS c ON c.id = o.customer_id
WHERE c.id = 5

Now say at runtime your user wants to restrict the set of customers to those who are older than 18 years and then sort them by their age in years? No problem, it's easy to add...

query = query.restrict(customer.AGE_YEARS.gt(18))
.orderBy(customer.AGE_YEARS);

And the results:

SELECT c.first_name, c.id, p.id
FROM order AS o
JOIN product AS p ON p.id = o.product_id
JOIN customer AS c ON c.id = o.customer_id
WHERE c.id = 5 AND c.age_years > 18
ORDER BY c.age_years

Product's homepage

Here are some key features of "JRel":

· Compose queries, updates, and inserts out of Java expressions which map to a usable subset of the relational algebra but translate into standards compliant SQL.
· Operations include project, restrict, cross product, joins (natural & theta joins; inner, left outer, right outer, full outer), aggregate operations (sum, average, etc.), and sorting.
· Support for the creation of multiple SQL dialects for the handling of variances between database vendors. Comes with an abstract ANSI SQL implementation which can be easily extended for each vendor.
· Support for introspection of a database schema using JDBC to produce JRel descriptions of the schema to make it easy to produce queries.
· A reusable query expression visitor framework which in addition to being used to produce SQL could be used to rewrite/optimize queries, or work against in-memory native data-types instead of a database.

Requirements:

· Java 2 Standard Edition Runtime Environment

What's New in This Release: [ read full changelog ]

· This release adds support for SQL conditional (CASE) statements, support for CAST statements, full and proper support for subselects and subqueries, projection of expressions (such as arithmetic, string concatenation, etc.), major improvements to aggregation (sum, max, min, etc.) handling, and bugfixes to join handling.