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

Download Microservices for Java Developers: A hands-on introduction to frameworks and containers. Brought to you in partnership with Red Hat.

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.

Download Modern Java EE Design Patterns: Building Scalable Architecture for Sustainable Enterprise Development.  Brought to you in partnership with Red Hat

Topics:

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

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}