Search code examples
javapostgresqljooq

Select as boolean using PostgreSQL 'crypt()' function

I'm trying to replicate the following SQL query using jOOQ, and I'm running into some errors. I'm want to see if a direct replacement is possible, before re-working the query if required, so just looking for some advice.

SELECT
    user_password_hash = crypt(?, user_password_hash) AS is_password_match
    roles
FROM system_users
WHERE user_name = ?;

I've tried the following jOOQ replacement, though it has compile errors:

DSL.using(connection, SQLDialect.POSTGRES)
    .select(SYSTEM_USERS.USER_PASSWORD_HASH.equal(crypt(password, SYSTEM_USERS.USER_PASSWORD_HASH.getName())), SYSTEM_USERS.ROLES)
    .from(SYSTEM_USERS)
    .where(SYSTEM_USERS.USER_NAME.equal(userName));

I believe the problem is the first part of the #select() call is being resolved as a Condition, rather than a Field. But I'm not sure how best to use the #equal() call to make it a Field.

Does anyone have any thoughts?

Solution

  • Use DSL.field(Condition) to turn a Condition into a Field<Boolean>