jOOQ: How to Create Select Query with MySQL “BINARY” Operator?

Question

Question

How can I create any one of the following two (equivalent) MySQL statements with jOOQ?

SELECT * FROM `tbl` WHERE `col` = BINARY 'foobar   ';
SELECT * FROM `tbl` WHERE `col` = CAST('foobar   ' AS BINARY);

Background

I’d like to compare arbitrary strings, possibly including (significant) trailing whitespaces. Unfortunately, MySQL ignores trailing whitespaces by default when comparing with =. As far as I’ve seen from this question, such comparisons are only possible using the BINARY operator in MySQL.

What I’ve tried already

I’ve tried to use the DSL.cast() method in jOOQ:

myDb.selectFrom(TBL)
  .where(TBL.COL
     .eq(DSL.cast("foobar   ", MySQLDataType.BINARY)))
  .fetchOne();
// → compiler error: “The method eq(String) in the type Field<String> is not
//   applicable for the arguments (Field<byte[]>)”

myDb.selectFrom(TBL)
  .where(DSL.cast(TBL.COL, MySQLDataType.BINARY)
     .eq("foobar   "))
  .fetchOne();
// → compiler error: “The method eq(byte[]) in the type Field<byte[]> is not
//   applicable for the arguments”

myDb.selectFrom(TBL)
  .where(DSL.cast(TBL.COL, MySQLDataType.BINARY)
     .eq(DSL.cast("foobar   ", MySQLDataType.BINARY)))
  .fetchOne();
// → runtime error: “org.jooq.exception.DataTypeException: Cannot convert from
//   foobar    (class java.lang.String) to class [B”

Workaround

My last resort would be to change my query to use LIKE instead of =. That’d be a hack, though, as I would then have to always quote any wildcards in my string first and I would also face a performance penalty :-|

Answer 1

Your third example should work and is probably a bug, which I've registered as Issue #3255:

myDb.selectFrom(TBL)
  .where(DSL.cast(TBL.COL, MySQLDataType.BINARY)
     .eq(DSL.cast("foobar   ", MySQLDataType.BINARY)))
  .fetchOne();

As always, if you're missing a feature in jOOQ, or if you've encountered a bug, you can resort to using plain SQL as documented here:

• http://www.jooq.org/doc/latest/manual/sql-building/plain-sql/

An example to work around In your case:

myDb.selectFrom(TBL)
  .where(TBL.COL
     .eq(DSL.field("BINARY ?", String.class, "foobar   ")))
  .fetchOne();

Or:

myDb.selectFrom(TBL)
  .where("{0} = BINARY {1}", TBL.COL, DSL.val("foobar   "))
  .fetchOne();