jQuery QueryBuilder meets JOOQ without the work.
Clone or download
Latest commit 73dbf61 Jan 2, 2019
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
src Published 1.0.3 Jan 2, 2019
.gitignore Do not version .idea folder Nov 19, 2018
.travis.yml Cleanup + more tests Jan 16, 2018
LICENSE Create LICENSE Jan 13, 2018
README.md Published 1.0.3 Jan 2, 2019
pom.xml Published 1.0.3 Jan 2, 2019

README.md

jqb2jooq

jQuery QueryBuilder meets JOOQ without the work.

Maven Central Build Status Coverage Status

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. ❤️