title: Oracle "boolean" to Java Boolean - how to convert automatically
body: <p>I'm developing a DB layer, using JDBC (ojdbc6.jar) which allows to perform basic DB operations by getting and returning a Map containing Name/Value pairs. For example:</p>
  
  <pre><code>map.put("StrField", "str");
  map.put("IntField", 10);
  map.put("BoolField", true);
  DB.insert(tableName, map);
  ...
  map = DB.query("SELECT * from MY_TABLE.."); // assuming this returns only one row
  Boolean flag = map.get("BoolField");
  // etc...
  </code></pre>
  
  <p>The insert operation is easy to implement because conversion from Java primitive types to Oracle types is easy and intuitive. </p>
  
  <p>For the opposite direction (Oracle->Java) the correction is slightly more tricky. I managed to convert all types except "Boolean". Since there is no BOOLEAN data type in Oracle DB, I'm looking for a way to define a field so that my retrieval layer will clearly identify such a field as Boolean.</p>
  
  <p>The best way I found so far is to use something like this:</p>
  
  <pre><code>BOOL_FIELD CHAR(1) DEFAULT 0 NOT NULL CHECK (BOOL_FIELD in (0, 1))
  </code></pre>
  
  <p>Plus, I make sure that I don't use CHAR(1) anywhere in my DB, except when "Boolean" is needed. This is not a big limitation because CHAR(1) is not very useful for anything else anyway.</p>
  
  <p>And in my Java conversion code, I check if a particular returned field is <code>java.sql.Types.CHAR</code> (via <code>ResultSetMetaData.getColumnType()</code> call), and I also check that the size of the field is 1, in which case I can safely call <code>ResultSet.getBoolean()</code> and get field value as Java Boolean data type.</p>
  
  <p>So far, it works really well, but now I have a problem with external databases, i.e. databases, in which I can't control the table/column definition. For such databases, I'm OK not to have Boolean values at all, but I wonder how I can distinguish my "Boolean" from just a regular CHAR(1) that someone else is using in the external database.</p>
  
  <p>I could ignore this entirely, and in case of CHAR(1) get Boolean values true/false, but this wasn't my intention. For example, if a particular CHAR(1) column has a value of 'X' (impossible for my database, but valid case in external database), then my conversion code will return <code>true</code> instead of 'X'.</p>
  
  <p>I'm looking for some "metadata" or trick which I can add to my "BOOLEAN" DB field, so my JDBC layer could get this metadata and if it gets it - assume it's MY Boolean and not someone else's CHAR(1) which looks similar.</p>
  
  <p>Any ideas on such a trick (even if not 100% safe, but reasonably safe)?</p>
  
  <p>Thanks.</p>
  
  <p><strong>Edit</strong>
  Just as an example, using RAW(1) in Oracle would do the trick perfectly, because there is close-to-zero chance anyone, anywhere will be using RAW(1) for anything else. The problem with RAW(1) is that it's really hard to use in SQL queries, i.e. it's hard to do WHERE with such "boolean" definition.</p>