Uploaded image for project: 'OpenJPA'
  1. OpenJPA
  2. OPENJPA-2558

Implement a way to select the db representation of Boolean values

    Details

    • Type: New Feature
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.3.0, 2.4.0
    • Fix Version/s: 2.2.3, 2.4.0
    • Component/s: jdbc
    • Labels:
      None

      Description

      Many Databases do not natively support the SQL-99 BOOLEAN datatype. Boolean and boolean fields in JPA entities will thus get represented as another type in the database. OpenJPA currently has a hardcoded mapping to int 1/0 in the base DBDictionary.

      But many existing databases don't use NUMBER(1) as column type but e.g. CHAR(1) (because it uses less space in the index).
      There are also all sorts of possible representations where one of those is usually consistently used throughout a customer application:

      • Native Boolean -> PreparedStatement#setBoolean
      • Int 0/1 -> PreparedStatement#setInt
      • String "Y"/"N" -> PreparedStatement#setString
      • String "T"/"F" -> PreparedStatement#setString
      • String "y"/"n" -> PreparedStatement#setString
      • String "t"/"f" -> PreparedStatement#setString

      And I've also seen translated ones: "J"/"N" in german apps for "Ja"/ "Nein" (yes/no).

      So it should be possible to even add your own custom BooleanRepresentation.

      1. OJ2558-2.2.x.patch
        31 kB
        Jody Grassel
      2. OPENJPA-2558.patch
        16 kB
        Mark Struberg

        Issue Links

          Activity

          Hide
          struberg Mark Struberg added a comment -

          With this patch you can add something like

          <property name="openjpa.jdbc.DBDictionary"
              value="(BitTypeName=CHAR(1),BooleanTypeName=CHAR(1),BooleanRepresentation=STRING_10)"/>
          
          Show
          struberg Mark Struberg added a comment - With this patch you can add something like <property name= "openjpa.jdbc.DBDictionary" value= "(BitTypeName=CHAR(1),BooleanTypeName=CHAR(1),BooleanRepresentation=STRING_10)" />
          Hide
          ilgrosso Francesco Chicchiriccò added a comment -

          It would save some custom code for making things working with Oracle DB in Apache Syncope, thanks Mark Struberg!

          Show
          ilgrosso Francesco Chicchiriccò added a comment - It would save some custom code for making things working with Oracle DB in Apache Syncope, thanks Mark Struberg !
          Hide
          jira-bot ASF subversion and git services added a comment -

          Commit 1652761 from Mark Struberg in branch 'openjpa/trunk'
          [ https://svn.apache.org/r1652761 ]

          OPENJPA-2558 implement BooleanRepresentation which can be switched via config

          Each DBDictionary has it's own default BooleanRepresentation but can easily get changed by the user
          e.g. via
          <property name="openjpa.jdbc.DBDictionary"
          value="(BitTypeName=CHAR(1),BooleanTypeName=CHAR(1),BooleanRepresentation=STRING_10)"/>

          Show
          jira-bot ASF subversion and git services added a comment - Commit 1652761 from Mark Struberg in branch 'openjpa/trunk' [ https://svn.apache.org/r1652761 ] OPENJPA-2558 implement BooleanRepresentation which can be switched via config Each DBDictionary has it's own default BooleanRepresentation but can easily get changed by the user e.g. via <property name="openjpa.jdbc.DBDictionary" value="(BitTypeName=CHAR(1),BooleanTypeName=CHAR(1),BooleanRepresentation=STRING_10)"/>
          Hide
          struberg Mark Struberg added a comment -

          I found a few more locations where we don't distinguish on how Booleans get stored in the database.
          E.g. in SQLBuffer#372

          else if (type == Boolean.class) {
              Boolean b = (Boolean) o;
              // We store B(b)ooleans as ints. Convert 
              _sql.append((b.booleanValue() ? "1" : "0"));
          

          That would already have broken PostgreSQL who stores Booleans as BOOLEAN (means "true"/"false" in the query string).
          Is this part of the SQLBuffer still being used?

          Show
          struberg Mark Struberg added a comment - I found a few more locations where we don't distinguish on how Booleans get stored in the database. E.g. in SQLBuffer#372 else if (type == Boolean .class) { Boolean b = ( Boolean ) o; // We store B(b)ooleans as ints. Convert _sql.append((b.booleanValue() ? "1" : "0" )); That would already have broken PostgreSQL who stores Booleans as BOOLEAN (means "true"/"false" in the query string). Is this part of the SQLBuffer still being used?
          Hide
          jpaheath Heath Thomann added a comment -

          Hey Mark! I'm not sure exactly how to answer your last question. The code you point out is the very exact code Rick updated/added recently via the other JIRA related to this one (i.e. OPENJPA-2534), see this commit:

          https://fisheye6.atlassian.com/changelog/openjpa?cs=1636474

          So this code is new, and is needed, but maybe needs to be changed to fix into your new code??

          Hope that helps.

          Thanks,

          Heath

          Show
          jpaheath Heath Thomann added a comment - Hey Mark! I'm not sure exactly how to answer your last question. The code you point out is the very exact code Rick updated/added recently via the other JIRA related to this one (i.e. OPENJPA-2534 ), see this commit: https://fisheye6.atlassian.com/changelog/openjpa?cs=1636474 So this code is new, and is needed, but maybe needs to be changed to fix into your new code?? Hope that helps. Thanks, Heath
          Hide
          struberg Mark Struberg added a comment -

          Actually this has not that much to do with my feature. This has been broken before. Even before I introduced the BooleanRepresentation we had DBDictionaries which did PreparedStatement#setBoolean(true/false) and not int 1/0!. Basically all databases which natively support the SQL-99 BOOLEAN datatype. For all those databases this line in the SQLBuffer is/was just broken I fear.

          Show
          struberg Mark Struberg added a comment - Actually this has not that much to do with my feature. This has been broken before. Even before I introduced the BooleanRepresentation we had DBDictionaries which did PreparedStatement#setBoolean(true/false) and not int 1/0!. Basically all databases which natively support the SQL-99 BOOLEAN datatype. For all those databases this line in the SQLBuffer is/was just broken I fear.
          Hide
          curtisr7 Rick Curtis added a comment -

          > For all those databases this line in the SQLBuffer is/was just broken I fear.
          Yes, that is most likely true. I vaguely remember when I made the change for OPENJPA-2534 fearing where else we might be broken when interacting with booleans.

          Show
          curtisr7 Rick Curtis added a comment - > For all those databases this line in the SQLBuffer is/was just broken I fear. Yes, that is most likely true. I vaguely remember when I made the change for OPENJPA-2534 fearing where else we might be broken when interacting with booleans.
          Hide
          struberg Mark Struberg added a comment -

          do you have an idea how to solve it? Should be easy to enhance BooleanRepresentation to have a T getTrue() and T getFalse() in the interface. But how do we tell the SQLBuffer what the current DBDictionary is? Didn't find the 'nearest' path to this information.

          Show
          struberg Mark Struberg added a comment - do you have an idea how to solve it? Should be easy to enhance BooleanRepresentation to have a T getTrue() and T getFalse() in the interface. But how do we tell the SQLBuffer what the current DBDictionary is? Didn't find the 'nearest' path to this information.
          Hide
          curtisr7 Rick Curtis added a comment -

          > But how do we tell the SQLBuffer what the current DBDictionary is?
          I don't entirely follow? It appears that SQLBuffer already has a reference to DBDictionary

          Show
          curtisr7 Rick Curtis added a comment - > But how do we tell the SQLBuffer what the current DBDictionary is? I don't entirely follow? It appears that SQLBuffer already has a reference to DBDictionary
          Hide
          struberg Mark Struberg added a comment -

          oh, didn't see. In that case it would be pretty easy to do. Let me hack that.

          Show
          struberg Mark Struberg added a comment - oh, didn't see. In that case it would be pretty easy to do. Let me hack that.
          Hide
          jira-bot ASF subversion and git services added a comment -

          Commit 1654352 from Mark Struberg in branch 'openjpa/trunk'
          [ https://svn.apache.org/r1654352 ]

          OPENJPA-2558 add getRepresentation to allow BooleanRepresentation be used in SQLBuffer

          Show
          jira-bot ASF subversion and git services added a comment - Commit 1654352 from Mark Struberg in branch 'openjpa/trunk' [ https://svn.apache.org/r1654352 ] OPENJPA-2558 add getRepresentation to allow BooleanRepresentation be used in SQLBuffer
          Hide
          jira-bot ASF subversion and git services added a comment -

          Commit 1654356 from Mark Struberg in branch 'openjpa/trunk'
          [ https://svn.apache.org/r1654356 ]

          OPENJPA-2558 fix eol-style

          Show
          jira-bot ASF subversion and git services added a comment - Commit 1654356 from Mark Struberg in branch 'openjpa/trunk' [ https://svn.apache.org/r1654356 ] OPENJPA-2558 fix eol-style
          Hide
          jira-bot ASF subversion and git services added a comment -

          Commit 1654485 from Mark Struberg in branch 'openjpa/trunk'
          [ https://svn.apache.org/r1654485 ]

          OPENJPA-2558 add proper logging for BooleanRepresentation

          Show
          jira-bot ASF subversion and git services added a comment - Commit 1654485 from Mark Struberg in branch 'openjpa/trunk' [ https://svn.apache.org/r1654485 ] OPENJPA-2558 add proper logging for BooleanRepresentation
          Hide
          jpaheath Heath Thomann added a comment -

          Mark,
          The changes in DBDictionary make use of a method called 'getUserClassLoader'. This method doesn't exists on 2.2.x. It was added in trunk via JIRA OPENJPA-2057. If you want this feature to be back ported to 2.2.x, can you find a different solution so as not to depend on 'getUserClassLoader'. I suppose one option is to backport OPENJPA-2057. But a) I don't know of I'd feel comfortable doing this in a service release version, and b) seems a bit funny back porting it just for the purposes of this JIRA.

          Thanks,

          Heath

          Show
          jpaheath Heath Thomann added a comment - Mark, The changes in DBDictionary make use of a method called 'getUserClassLoader'. This method doesn't exists on 2.2.x. It was added in trunk via JIRA OPENJPA-2057 . If you want this feature to be back ported to 2.2.x, can you find a different solution so as not to depend on 'getUserClassLoader'. I suppose one option is to backport OPENJPA-2057 . But a) I don't know of I'd feel comfortable doing this in a service release version, and b) seems a bit funny back porting it just for the purposes of this JIRA. Thanks, Heath
          Hide
          fyrewyld Jody Grassel added a comment -

          Backport of the fix to OpenJPA 2.2.x.

          Show
          fyrewyld Jody Grassel added a comment - Backport of the fix to OpenJPA 2.2.x.
          Hide
          fyrewyld Jody Grassel added a comment -

          Hi, Mark. I've attached a backport of the fix for 2.2.x to this JIRA for review at your pleasure.

          Show
          fyrewyld Jody Grassel added a comment - Hi, Mark. I've attached a backport of the fix for 2.2.x to this JIRA for review at your pleasure.
          Hide
          jira-bot ASF subversion and git services added a comment -

          Commit 1686870 from Heath Thomann in branch 'openjpa/branches/2.2.x'
          [ https://svn.apache.org/r1686870 ]

          OPENJPA-2558: Implement a way to select the db representation of Boolean values.

          Show
          jira-bot ASF subversion and git services added a comment - Commit 1686870 from Heath Thomann in branch 'openjpa/branches/2.2.x' [ https://svn.apache.org/r1686870 ] OPENJPA-2558 : Implement a way to select the db representation of Boolean values.
          Hide
          jira-bot ASF subversion and git services added a comment -

          Commit 1708660 from Jody Grassel in branch 'openjpa/branches/2.2.x'
          [ https://svn.apache.org/r1708660 ]

          OPENJPA-2558: Set ConfigurationImpl serialVersionUID (ammendment)

          Show
          jira-bot ASF subversion and git services added a comment - Commit 1708660 from Jody Grassel in branch 'openjpa/branches/2.2.x' [ https://svn.apache.org/r1708660 ] OPENJPA-2558 : Set ConfigurationImpl serialVersionUID (ammendment)

            People

            • Assignee:
              struberg Mark Struberg
              Reporter:
              struberg Mark Struberg
            • Votes:
              3 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development