Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

The JDBC Boolean Compatibility List

DZone's Guide to

The JDBC Boolean Compatibility List

· Java Zone
Free Resource

Just released, a free O’Reilly book on Reactive Microsystems: The Evolution of Microservices at Scale. Brought to you in partnership with Lightbend.

Interestingly, boolean types have been introduced only late in the SQL standard, namely in SQL:1999. Even today, not all databases natively support BOOLEAN or BIT types. Most importantly, we can still wait for them in Oracle for a while. Here’s “Ask Tom”‘s point of view from 2002 on the subject:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6263249199595

Oracle. Why U No Boolean?tweet this

People have worked around this limitation by using numeric or string literals instead. For instance 1 / 0Y / NT / F or the SQL standard 'true' / 'false'.

Booleans in JDBC

From a JDBC API perspective, boolean values can be set as bind values through PreparedStatement.setBoolean() or fetched from result sets through ResultSet.getBoolean(), and similar methods. If your database supports booleans, the Java boolean type nicely maps to SQL BOOLEAN – even if Java’s Boolean wrapper type would have been a better fit to respect NULLs.

But if you’re storing boolean values in INTEGERCHAR(1) or VARCHAR(1)columns, things look differently in various databases. Consider the following example:

CREATE TABLE booleans (
  val char(1)
);

And then, run this Java program (we’re using jOOQ to keep things concise)

try {
    DSL.using(configuration).execute(
        "insert into boolean (val) values (?)", true);
}
catch (Exception e) {
    e.printStackTrace();
}

DSL.using(configuration).fetch("select * from booleans");

Not all databases / JDBC drivers support the above. These databases will run the above program:

  • Firebird (inserts ‘Y’ or ‘N’)
  • HSQLDB (inserts ’1′ or ’0′)
  • IBM DB2 (inserts ’1′ or ’0′)
  • MariaDB (inserts ’1′ or ’0′)
  • Microsoft Access (inserts ’1′ or ’0′)
  • MySQL (inserts ’1′ or ’0′)
  • Oracle (inserts ’1′ or ’0′)
  • SQL Server (inserts ’1′ or ’0′)
  • Sybase (inserts ’1′ or ’0′)

… whereas these databases will throw an exception:

  • CUBRID
  • Derby
  • H2
  • Ingres
  • PostgreSQL
  • SQLite

Booleans in the SQL standard

It is worth mentioning, that the SQL standard specifies how to deal withboolean to string conversion in the specification of the CAST() function:

6.13 <cast specification>
[...]
10) If TD is fixed-length character string, 
   then let LTD be the length in characters
   of TD.
[...]
e) If SD is boolean, then
Case:
i) If SV is True and LTD is not less than 4, 
   then TV is 'TRUE' extended on the right by
   LTD–4
s.
ii) If SV is False and LTD is not less than 5,
   then TV is 'FALSE' extended on the right by
   LTD–5 <space>s.
iii) Otherwise, an exception condition is 
   raised: data exception — invalid character
   value for cast.

So, most Open Source databases show what could be interpreted as the “correct” behaviour, even if from a historic perspective, 1/0 should be accepted behaviours. Beware of this limitation when using an Open Source test database!

For more information about this and the H2 database, please refer to this thread on the H2 user group.

Strategies and techniques for building scalable and resilient microservices to refactor a monolithic application step-by-step, a free O'Reilly book. Brought to you in partnership with Lightbend.

Topics:

Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}