Select fetch one result returning null

Question

When I use this statement it does not return results.

Result<Record> result = (Result<Record>) jooq
    .select()
    .from("Employees")
    .where(DSL.cast("FirstName", MySQLDataType.BINARY)
       .eq(DSL.cast(firstName, MySQLDataType.BINARY)))
    .fetchOne();

I want to select only one Result.

Structure:

--
-- Table structure for table `Employees`
--

CREATE TABLE IF NOT EXISTS `Employees` (
`id` int(11) NOT NULL,
  `FirstName` varchar(100) NOT NULL,
  `LastName` varchar(150) NOT NULL,
  `Age` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

I want to select is Jack and it exists but it returns null.

Example:

SELECT * FROM Employees WHERE FirstName = Jack;

`nul`l means that no record was found - turn on Jooq's logging at DEBUG level and you will see what query is sent. That should help you understand what is going on... — assylias, Jan 30 '16 at 08:30
@Shrekt Would you please add some example data? What is in the table and what is the firstName? — longhua, Jan 30 '16 at 08:31
@Shrekt Have you generated the schema? I think you can try `jooq.select().from("Employees").where(Employees.FirstName.cast(MySQLDataType.BINARY).eq(DSL.cast(firstName, MySQLDataType.BINARY))).fetchOne();` — longhua, Jan 30 '16 at 08:58
Why do you need the cast? Such a simple query should look like `Result result = jooq.selectFrom(Tables.EMPLOYEE).where(Tables.EMPLOYEE.FIRST_NAME.eq(firstName)).fetchOne();` — assylias, Jan 30 '16 at 09:08
can i do it with out the schema? just a select to get it from the database like am trying to do? — Shrekt, Jan 30 '16 at 09:41

score 3 · Accepted Answer · edited May 23 '17 at 10:28

This:

// Assuming this static import
import static org.jooq.impl.DSL.*;

cast("FirstName", MySQLDataType.BINARY)

Will generate the following SQL

-- With a bind variable:
CAST(? AS BINARY)

-- If you're inlining bind variables:
CAST('FirstName' AS BINARY)

So, this is not referring to your `FirstName` column, but to the 'FirstName' string value. What you really wanted to do is this:

cast(field(name("FirstName")), MySQLDataType.BINARY);

Which will generate

CAST(`FirstName` AS BINARY)

A general note on case sensitivity

If you're using backticks around table / column names in your DDL, you should always be aware of case sensitivity with your object names also in jOOQ. Ideally, you'll use the DSL.name() method as indicated in my answer to create case sensitive names. This also applies to your Employees table, which is added to your SQL statement case insensitively:

from("Employees")       // Generates a "plain SQL", case-insensitive table Employees

I recommend you write this instead:

from(name("Employees")) // Generates a case-sensitive table identifier `Employees`

The reason why it works is because MySQL doesn't know case sensitive table names on Windows by default.

Manual references

I suggest reading the jOOQ manual sections about "plain SQL" and "identifiers" to help clarify things:

Select fetch one result returning null

1 Answers1

A general note on case sensitivity

Manual references