{"id":22201,"date":"2014-03-03T13:00:31","date_gmt":"2014-03-03T11:00:31","guid":{"rendered":"http:\/\/www.javacodegeeks.com\/?p=22201"},"modified":"2014-03-03T10:56:36","modified_gmt":"2014-03-03T08:56:36","slug":"the-jdbc-boolean-compatibility-list","status":"publish","type":"post","link":"https:\/\/www.javacodegeeks.com\/2014\/03\/the-jdbc-boolean-compatibility-list.html","title":{"rendered":"The JDBC Boolean Compatibility List"},"content":{"rendered":"

Interestingly, boolean types have been introduced only late in the SQL standard, namely in SQL:1999<\/a>. Even today, not all databases natively support BOOLEAN<\/code> or BIT<\/code> types. Most importantly, we can still wait for them in Oracle for a while. Here\u2019s \u201cAsk Tom\u201d\u2018s point of view from 2002 on the subject: https:\/\/asktom.oracle.com\/pls\/apex\/f?p=100:11:0::::P11_QUESTION_ID:6263249199595<\/a><\/p>\n

\"fC69nJF\"<\/a><\/p>\n

\n

Oracle. Why U No Boolean?<\/p>\n<\/blockquote>\n

People have worked around this limitation by using numeric or string literals instead. For instance 1 \/ 0<\/code>, Y \/ N<\/code>, T \/ F<\/code> or the SQL standard 'true' \/ 'false'<\/code>.<\/p>\n

Booleans in JDBC<\/h2>\n

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

<\/div> <\/div><\/p>\n

But if you\u2019re storing boolean values in INTEGER<\/code>, CHAR(1)<\/code> or VARCHAR(1)<\/code> columns, things look differently in various databases. Consider the following example:<\/p>\n

CREATE TABLE booleans (\r\n  val char(1)\r\n);<\/pre>\n
And then, run this Java program (we\u2019re using jOOQ<\/a> to keep things concise)<\/p>\n
try {\r\n    DSL.using(configuration)\r\n       .execute(\r\n       \"insert into boolean (val) values (?)\", true);\r\n}\r\ncatch (Exception e) {\r\n    e.printStackTrace();\r\n}\r\n\r\nDSL.using(configuration)\r\n   .fetch(\"select * from booleans\");<\/pre>\n
Not all databases \/ JDBC drivers support the above. These databases will run the above program:<\/p>\n