jqb2jooq

jQuery QueryBuilder meets JOOQ without the work.

What is jQuery QueryBuilder?

jqb is a super handy library for your website that allow for the creation of queries and filters.

What is JOOQ?

To shamelessly steal their tagline "JOOQ: The easiest way to write SQL in Java". This sentiment I agree with completely. The JOOQ library provides a typesafe way to execute queries and is an absolute joy to use.

This project aims to be the glue between these two awesome products. jqb2jooq provides the means of defining a mapping from jQuery QueryBuilder filter fields to JOOQ auto-generated fields. Once you define this mapping jqb2jooq will handle the conversion of QueryBuilder json to a JOOQ condition, ready to be included in your JOOQ query.

Usage

Getting started is just as easy as the famed 5 Minute Wordpress Install (only you don't have to use Wordpress 😏).

First, add the following dependency to your pom.xml.

<dependency>
  <groupId>io.kowalski</groupId>
  <artifactId>jqb2jooq</artifactId>
  <version>1.0.3</version>
</dependency>

---

Second, you will need to define a mapping between jqb filter fields and JOOQ fields.

This is done by creating an enum that implements RuleTarget.java.

package io.kowalski.jqb2jooq.test;

import io.kowalski.jqb2jooq.RuleTarget;
import org.jooq.Condition;
import org.jooq.Field;

import static io.kowalski.jqb2jooq.test.jooq.Tables.*;

public enum TestFilterTargets implements RuleTarget {

  FULLNAME(EMPLOYEES.FULLNAME),
  DOB(EMPLOYEES.DOB),
  SALARY(PAYROLL.AMOUNT, PAYROLL.TYPE.eq("SALARY")),
  HOURLY(PAYROLL.AMOUNT, PAYROLL.TYPE.eq("HOURLY")),
  FOOD(EMPLOYEES.FAVORITE_FOOD);

  private final Field field;
  private final Condition[] implicitConditions;

  TestFilterTargets(Field field, Condition... implicitConditions) {
      this.field = field;
      this.implicitConditions = implicitConditions;
  }

  @Override
  public TestFilterTargets parse(String value) {
      return TestFilterTargets.valueOf(value);
  }

  @Override
  public Field getField() {
      return field;
  }

  @Override
  public Condition[] getImplicitConditions() {
      return implicitConditions;
  }

}

---

Last, but not least, you need to convert the json filter from jqb to a JOOQ condition.

jqb2jooq assumes that the json filter has been deserialized into:

Map<String, Object>

Here is a quick snippet of how to perform said task with gson.

java.lang.reflect.Type mapType = new com.google.gson.reflect.TypeToken<Map<String, Object>>() 
{}.getType();

Map<String, Object> filterMap = new Gson().fromJson(rawJson, mapType);

Once you have the filter deserialized all you have to do is...

org.jooq.Condition condition = JQB2JOOQ.parse(TestFilterTargets.class, filterMap);

// ...and immediately put it to work

try (DSLContext dsl = DSL.using(dataSource, SQLDialect.H2)) {
  List<Employees> employees = dsl.select().from(EMPLOYEES)
          .where(condition).fetchInto(Employees.class);
}

---

That's all folks!

I hope you enjoy using jqb2jooq! If you notice anything funky please file an issue. ❤️