FastOQL™

Fast Object Queries for Hibernate

Overcome performance and scalability problems of HQL queries without escaping to SQL: Write compact object queries with Hibernate and still get efficient SQL!

Documentation

This is short user guide to help you get started on. To learn more about features, syntax, and semantics of OQL click here.

Installation

FastOQL™ library comes as a jar file. All you need to do is to add it to your project's classpath. Additionally, FastOQL™ relies on Apache log4j library for logging purposes. You should have it in your project as well.

Configuration

There is not specific configuration required, as FastOQL™ uses the Hibernate configuration. You only need to add a license key property to your configuration file:

<property name="fastoql.key"> ... </property>

The license key property is required for FastOQL™ configuration. Enter either trial key or any number of purchased keys each in its own property element (with the same name attribute "fastoql.key").

The API

FastOQL™ is simple to use and its API (rs.sol.FastOQL) reflects that.

public static void configure(Configuration configuration);

Configures FastOQL™ using Hibernate's configuration. Regardless of whether you use annotations or XML to configure Hibernate, you only need to provide the org.hibernate.Configuration object - FastOQL™ will do the rest. You need to do this before creating any queries with FastOQL™.

public static SQLQuery createSQLQuery(String oql, Session session);

Creates a Hibernate SQLQuery for the given OQL string. Requires a valid Hibernate session on which this SQLQuery will be created. If OQL query has a limit/offset clause, setFirstResult and setMaxResults will be called on the created SQLQuery.

public static Object setParameter(SQLQuery q, String name, Object param);

Binds a value (Java object) to a named query parameter. You need to provide Hibernate's native SQL query object created using the previous method. If necessary, the parameter value is modified to fit the database. The modified value is returned. FastOQL™ supports only named parameters. In most cases this is just a wrapper for SQLQuery.setParameter() method.

Example:

FastOQL.configure(configuration);

String strOQLQuery = "select … from … where …";
org.hibernate.SQLQuery sqlQuery = FastOQL.createSQLQuery(strOQLQuery, hbnSession);
FastOQL.setParameter(sqlQuery, strParamName, paramObject);
sqlQuery.list();

You’ve probably noticed that FastOQL™ does not return object queries but SQL queries. Objects in the result set are represented with their object IDs (primary key values). To retrieve an entire object in Java, use the Hibernate’s service to load the object from its ID. In most cases of complex queries, you are interested in just a few attributes of the objects retrieved from the query, or for a small subset of objects retrieved by the query. For example, in typical complex searches, you fetch only a small subset of objects to render on a page (a window of e.g. 10-100 objects). In order to provide entire objects in its result set, Hibernate (usually unnecessarily) joins all tables from the class hierarchy and thus makes queries that are extremely inefficient. This is why we do it differently: in most such cases, it is more efficient to pose an optimized, minimal query that searches for the subset of objects that satisfy a complex criterion in a huge database, and then perform small, narrowed queries (or one compact query) to retrieve entire objects from the returned subset only.

Restrictions and Assumptions

Inheritance mapping

FastOQL™ supports only the table per subclass inheritance strategy. This strategy represents inheritance relationships using foreign key associations between superclass and subclass tables. Every class (including abstract ones) has its own table. Each table contains columns for each own (non-inherited) property, along with a primary key (which is a foreign key of the superclass table). You can use type discriminator if you wish, although it is not necessary here. FastOQL™ won't use it in the generated SQL queries.

Additionally, this strategy can be implemented as a mix of the single table strategy with the secondary table option for each subclass. FastOQL™ supports this method as well but, at this moment, only if it is implemented with the annotations.

Associations

  • One to one associations. Hiberanate allows three strategies for mapping one to one associations: with foreign key in one of the class tables, using an association table, and with a shared primary key. FastOQL™ does not support the shared primary key strategy. The other two strategies are supported. Associations can be directed or bidirectional.
  • Many to one / One to many associations. All combinations are supported, directed or bidirectional, mapped as a association table or as foreign key in one of the class tables.
  • Many to many associations are supported (both directed and bidirectional).

Many-valued associations can be mapped as bags, sets, lists or maps. Lists can be indexed (ordered) and maps can be sorted. For indexed lists and maps, Hibernate will add additional columns to association or class tables. This doesn't pose a problem for FastOQL™, but these additional columns will not be accessible directly through OQL queries.

At the moment, association tables are only supported if mapped through annotations. Ternary associations and additional columns in association tables are not supported.

Collections of value types and components

At the moment, FastOQL™ does not support collections of value types neither collections of components. These features are planned for future releases.

Data types

FastOQL™ supports all Hibernate's built-in mapping types. Java primitive mapping types, date and time mapping types, and JDK mapping types are fully supported. Binary mapping types can be fetched, but using these types as parameters or literal constants are discouraged (FastOQL™ does not guarantee correct behaviour). Custom data types and enumerations are not supported.

Primary keys

One peculiarity of FastOQL™ is that primary key is implicit. You cannot access a single entity using its primary key, but you can use class instance as a parameter value. Also, addressing the primary key column in the select clause of OQL query is implicit, for example: select t from Test t will create SQL select t.id from Test t.

Composite keys are not supported.

Aliases

FastOQL™ ignores aliases you use in select clause of OQL query. It will generate an alias for each column to ensure uniqueness required by Hibernate's SQLQuery. Remember this, if you want to refer to the columns in the tuple of the result set using an alias name. Generated aliases are in the form 'colN' where N is 0, 1, 2…

Database support

Although FastOQL™ works alongside Hibernate, it uses its own object-relational mapper. Currently, we support the following databases:

  • MySQL
  • PostgreSQL
  • Microsoft SQL Server
  • Oracle.

FastOQL™ Explorer

FastOQL™ Explorer is a lightweight, simple to use, Web-based console for browsing a Hibernate database through issuing OQL queries at runtime, like in classical SQL-based database administration (DBA) tools. It is a HTTP servlet and works as an extension to the FastOQL™ core engine.

Installation

FastOQL™ Explorer is in essence just a servlet which comes as a jar file. As such FastOQL™ Explorer requires to be part of a web application (it is dependent on the server libraries). Additionally, you'll need FastOQL™ core library to able to execute OQL queries, log4j library for logging purposes and Apache commons-lang library.

Configuration

You'll need to map FastOQL™ Explorer's servlet (rs.sol.fastoql.oqlexplorer.OQLServlet) to a URL of your choice. Here is an example of standard web.xml mapping:

  • <servlet>
    • <servlet-name>OQLServlet</servlet-name>
    • <servlet-class>rs.sol.fastoql.oqlexplorer.OQLServlet</servlet-class>
    • <init-param>
      • <param-name>sessionFactoryAttrName</param-name>
      • <param-value>HibernateSessionFactory</param-value>
    • </init-param>
  • </servlet>
  • <servlet-mapping>
    • <servlet-name>OQLServlet</servlet-name>
    • <url-pattern>/fastOqlExplorer</url-pattern>
  • </servlet-mapping>

You have probably noticed init parameter sessionFactoryAttrName. FastOQL™ Explorer needs valid Hibernate session to be able to execute OQL queries. It will look for Hibernate SessionFactory object as an servlet context attribute. By default this attribute is named HibernateSessionFactory but you can configure it to suit your application better. You'll need to set this attribute during context initialization.

That's it, you can access FastOQL™ Explorer through set URL (e.g. localhost:8080/MyWebApp/fastOqlExplorer). Type in "help" and you will get an embedded online help, and the rest is self-explanatory. You can see it in action here.