JRel ; 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.

JRel is developed by ThimbleWare Inc, and released under the terms of the Apache License, Version 2.0.

See the feature list for a list of what JRel can do right now.

The Problem

... You have a SQL database, which is sort of relational, and your data is there -- you'd like to get it into your application and transform and manipulate it. Currently, this kind of data driven programming in Java applications takes one of the following forms:

  • SQL statements are stored as string literals in Java classes, or in property or XML files, are tokenized or substituted with values. Modifications to queries are performed as modifications to strings, and non-trivial modifications (such as adding a new JOIN, column attribute, or WHERE clause to a query) are inelegant string munging operations. As the queries are string literals, no compile-time syntax or type checking is possible.
  • One of several so-called "ORM" solutions (such as Hibernate, JPA, JDO, classic EJB, etc.) where data manipulation becomes subsumed under the mantel of POJO "persistence"; here data programming drops to the lowest-common denominator of operations that map to both the relational and object-oriented model.

JRel's Answer

JRel attempts to intervene in the so-called "object-relational impedance problem" on the relational, rather than object, side.

Because of their relational heritage, and because three decades of engineering have gone into their optimization and perfection, modern SQL databases are excellent general purpose data manipulation tools. The SQL language, however, suffers from inconsistency, inelegance, and, most importantly, from the fact it is a different programming language than that used for general application programming (such as Java or C#).

Generally, where you would have used hand-coded SQL in the past, you can use JRel; what you gain is:

  • Consistency of language in your application.
  • All the power of your Java IDE (such as Eclipse or IntelliJ)
  • Compile-time checking of syntactical correctness of your queries
  • Programmatic manipulation of queries at runtime; for example, adding new filtering criteria or attributes in response to input from a form, or type-safe substitution of values.
  • A more consistent syntax than that provided by SQL or its ORM competitors (HQL, JPQL, EJB-QL, etc.)

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

How can I get it?

You can retrieve the compiled JARs from the Maven 2 repository: http://thimbleware.com/maven/com/thimbleware/

To make use of jrel in your project, the easiest way is to use Maven 2.

To do this using Maven, add the ThimbleWare repository: 

    <repositories>
        <repository>
            <id>thimbleware.repo</id>
            <url>http://thimbleware.com/maven</url>
        </repository>
    </repositories>

And then add the dependency: 

    <dependency>
        <groupId>com.thimbleware.jrel</groupId>
        <artifactId>jrel</artifactId>
        <version>0.10</version>
    </dependency>

Source code can be retrieved from the Mercurial repository.

More Propaganda …

  • Source Code for how to checkout the development source code from revision control.
  • Downloads? for access to released and snapshots of JRel components.
  • Examples? for examples of JRel usage.