iBatis for Java [READ ONLY]
  1. iBatis for Java [READ ONLY]
  2. IBATIS-333

Patch which allow to subsititute property with column name from ResultMap in dynamic sql

    Details

    • Type: New Feature New Feature
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.2.0
    • Fix Version/s: None
    • Component/s: Core
    • Labels:
      None

      Description

      In situtation, when i need to implement sorting, I need to create such monster, like abator creates for me. But
      if i only need sorting, here should be mapping beetween columns and properties (if they specified explicitly
      in ResultMap). In this case, i can write

      <resultMap id="dynamicAccountResult" class="testdomain.Account">
      <result column="ACC_ID" property="id"/>
      <result column="ACC_FIRST_NAME" property="firstName"/>
      <result column="ACC_LAST_NAME" property="lastName"/>
      <result column="ACC_EMAIL" property="emailAddress"/>
      </resultMap>

      <select id="dynamicInsertColumnName"
      parameterClass="string"
      resultMap="dynamicAccountResult">
      select
      ACC_ID,
      ACC_FIRST_NAME,
      ACC_LAST_NAME,
      ACC_EMAIL
      from ACCOUNT
      <dynamic>
      <insertColumnName prepend="where"> = 1</insertColumnName> <!-- ibatis replace this with column mapped for "id" passed as unnamed property -->
      <insertColumnName prepend="order by"/>
      </dynamic>
      </select>

      public void testInsertColumnName() throws SQLException

      { List list = sqlMap.queryForList("dynamicInsertColumnName", "id"); // Here we pass our property name assertEquals(1, list.size()); }

      SQL will be

      select
      ACC_ID,
      ACC_FIRST_NAME,
      ACC_LAST_NAME,
      ACC_EMAIL
      from ACCOUNT
      where ACC_ID = 1
      order by ACC_ID

      In any case, we can pass some AccountCriteria which will hold a list with soring properties names and we can iterate in dynamic sql
      and get multiply field "order by" clause

      PS: Not all things tested yet. No errors thrown. This is alpha of this feature. This is proof of conecpt (due of little amount of structural
      changes in other ibatis classes). If developers wan't this feature and agree with my decisions, I'll implement all other bells and whishes
      like error checking, tests and may be other extensions for this tag.

        Activity

        Hide
        Larry Meadors added a comment -

        I do not really see the value of this patch, to me it seems to obfuscate more than clarify.

        Why not just use the $substitution$ syntax? It's more direct (i.e., it doesn't rely on a result map), and more clear (i.e., if the parameter x is "ACC_ID", then $x$ becomes ACC_ID).

        Show
        Larry Meadors added a comment - I do not really see the value of this patch, to me it seems to obfuscate more than clarify. Why not just use the $substitution$ syntax? It's more direct (i.e., it doesn't rely on a result map), and more clear (i.e., if the parameter x is "ACC_ID", then $x$ becomes ACC_ID).
        Hide
        Andrey added a comment -

        Simple. Because when iBatis lives behind DAO only POJO properties seen. It is really bad idea to use SQL fields in external world.

        Let's imagine, we have jsp table. How many code You write to check, that substitution is correct? That there is no sql injection?
        If field name changed?
        With this feature your can write generic code, which will sort (not in all case, but in many) only
        by property name and nothing know about internal database fields.

        I know, we can use $subsitution$ and all fields in SQL represent with "as alias". But this is double work. I already have described
        fields <-> properties, why I can't use them? And in any case $substitution$ is very insecure. I use it only in special cases and
        always this usage doesn't cross DAO boundaries...

        Show
        Andrey added a comment - Simple. Because when iBatis lives behind DAO only POJO properties seen. It is really bad idea to use SQL fields in external world. Let's imagine, we have jsp table. How many code You write to check, that substitution is correct? That there is no sql injection? If field name changed? With this feature your can write generic code, which will sort (not in all case, but in many) only by property name and nothing know about internal database fields. I know, we can use $subsitution$ and all fields in SQL represent with "as alias". But this is double work. I already have described fields <-> properties, why I can't use them? And in any case $substitution$ is very insecure. I use it only in special cases and always this usage doesn't cross DAO boundaries...
        Hide
        Joris Portegies Zwart added a comment -

        I agree with Andrey.

        In our application we need to support showing PaginedResultLists in the GUI, with the requirement that the user can sort on any column by clicking on the column header.

        Since the list is paginated, we cannot sort in memory on the frontend, so we need to re-query the database, adding the proper ORDER BY clause. As Andrey pointed out, it is a very bad idea to have knowledge of the actual database column names in the frontend, so we would like to be able to tell iBatis to sort on a given Java property, and let iBatis figure out to which this property is mapped.

        Show
        Joris Portegies Zwart added a comment - I agree with Andrey. In our application we need to support showing PaginedResultLists in the GUI, with the requirement that the user can sort on any column by clicking on the column header. Since the list is paginated, we cannot sort in memory on the frontend, so we need to re-query the database, adding the proper ORDER BY clause. As Andrey pointed out, it is a very bad idea to have knowledge of the actual database column names in the frontend, so we would like to be able to tell iBatis to sort on a given Java property, and let iBatis figure out to which this property is mapped.
        Hide
        Clinton Begin added a comment -

        I agree with everyone I think....but I don't see a need to change iBATIS to support this.

        You can use column aliases to order your columns. Make them match the property names and voila....no worries.

        In fact if you do that (using the example above), you could eliminate the result map and have less code overall.

        And as for substitution/sql injection – all of your validation concerns are aleviated when you check for what you WANT as opposed to what you don't allow. Simple security best practices. Many security mistakes of the last 40 years were due to people trying to guess at every possible invalid value as opposed to the one value they were looking for. In this case you know EXACTLY what you allow – property names on a class. So don't worry about quotes or anything else....just do this:

        ClassInfo info = ClassInfo.getInstance(Employee.class);
        info.hasReadableProperty(orderByPropertyName);

        I guarantee you won't be able to inject SQL past a validation rule like that, nor will it be anything other than a property value. I wouldn't even worry about a nice exception in that case, I'd just blow the top at the browser level....hackers don't get pretty error messages (or any useful information).

        Cheers,
        Clinton

        Show
        Clinton Begin added a comment - I agree with everyone I think....but I don't see a need to change iBATIS to support this. You can use column aliases to order your columns. Make them match the property names and voila....no worries. In fact if you do that (using the example above), you could eliminate the result map and have less code overall. And as for substitution/sql injection – all of your validation concerns are aleviated when you check for what you WANT as opposed to what you don't allow. Simple security best practices. Many security mistakes of the last 40 years were due to people trying to guess at every possible invalid value as opposed to the one value they were looking for. In this case you know EXACTLY what you allow – property names on a class. So don't worry about quotes or anything else....just do this: ClassInfo info = ClassInfo.getInstance(Employee.class); info.hasReadableProperty(orderByPropertyName); I guarantee you won't be able to inject SQL past a validation rule like that, nor will it be anything other than a property value. I wouldn't even worry about a nice exception in that case, I'd just blow the top at the browser level....hackers don't get pretty error messages (or any useful information). Cheers, Clinton
        Hide
        Andrey added a comment -

        Ok. I think that exactly that feature is not very impotant (and have some disadvatages),
        but some public api to get result mapping will be very usefull. I think

        Show
        Andrey added a comment - Ok. I think that exactly that feature is not very impotant (and have some disadvatages), but some public api to get result mapping will be very usefull. I think
        Hide
        Andrey added a comment -

        One more proposal.

        How about more flexible way: "meta-properties".

        Consider example:

        <meta-property-generator>
        <meta-property name="sort" value="#column_name#"/> <!-- this line declares, that any request for "sort" metaproperty return column_name metaproperty -->
        <meta-property name="display_tag" value="#column_name#_tag"/> <!-- this line declares, that any request for "display-tag" metaproperty return column_name metaproperty fix tag suffix -->
        <meta-property name="some_restricted" explicit="true"/> <!-- prohibit autogenerated property, thow excetpion -->
        </meta-property-generator>

        <resultMap id="salaryReportResult" class="testdomain.SalaryReport">
        <result column="ACC_ID" property="id">
        <meta-property name="column_name">ACC_ID</meta-property> <!-- This property can be implicitly generated by iBatis-->
        <meta-property name="sort">ACC_ID desc</meta-property> <!-- This property is user defined, but if no specifed, will be generated from meta-generator -->
        <meta-property name="display_tag">account-id-tag</meta-property> <!-- This property is name from .properties to display in grid -->
        </result>
        <result column="ACC_FIRST_NAME" property="firstName"/>
        <result column="ACC_SALARY_PCT" property="salaryPct"/>
        <meta-property name="sort">SALARY * 0.5</meta-property> <!-- some wierd usage, where we can't use column alias, but full expression needed (like in group by) -->
        </result>
        </resultMap>

        Later we can refer to this properties by special syntax or by more simple syntax, f.e.
        1. Special:
        @id.sort@

        2. Like property
        #id.meta.sort#

        3. Any other way which developers will perefer.

        If this proposal is interesting, I'll create new JIRA and try to implement this.

        PS: this will add flexibility to iBatis and no need to depend strictly on property names in sql's,
        allow to define many things in one place, define database specific things in sqlmap, not in
        calling classes, etc.

        Show
        Andrey added a comment - One more proposal. How about more flexible way: "meta-properties". Consider example: <meta-property-generator> <meta-property name="sort" value="#column_name#"/> <!-- this line declares, that any request for "sort" metaproperty return column_name metaproperty --> <meta-property name="display_tag" value="#column_name#_tag"/> <!-- this line declares, that any request for "display-tag" metaproperty return column_name metaproperty fix tag suffix --> <meta-property name="some_restricted" explicit="true"/> <!-- prohibit autogenerated property, thow excetpion --> </meta-property-generator> <resultMap id="salaryReportResult" class="testdomain.SalaryReport"> <result column="ACC_ID" property="id"> <meta-property name="column_name">ACC_ID</meta-property> <!-- This property can be implicitly generated by iBatis--> <meta-property name="sort">ACC_ID desc</meta-property> <!-- This property is user defined, but if no specifed, will be generated from meta-generator --> <meta-property name="display_tag">account-id-tag</meta-property> <!-- This property is name from .properties to display in grid --> </result> <result column="ACC_FIRST_NAME" property="firstName"/> <result column="ACC_SALARY_PCT" property="salaryPct"/> <meta-property name="sort">SALARY * 0.5</meta-property> <!-- some wierd usage, where we can't use column alias, but full expression needed (like in group by) --> </result> </resultMap> Later we can refer to this properties by special syntax or by more simple syntax, f.e. 1. Special: @id.sort@ 2. Like property #id.meta.sort# 3. Any other way which developers will perefer. If this proposal is interesting, I'll create new JIRA and try to implement this. PS: this will add flexibility to iBatis and no need to depend strictly on property names in sql's, allow to define many things in one place, define database specific things in sqlmap, not in calling classes, etc.
        Hide
        Andrey added a comment -

        and will not break backward compatibility

        Show
        Andrey added a comment - and will not break backward compatibility

          People

          • Assignee:
            Unassigned
            Reporter:
            Andrey
          • Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:

              Development