Expression evaluation

[Guy Rouillier]

With 3.2, MyBatis added the ability to use different scripting
languages. What is the current status of expanding expression
evaluation capabilities? I have one very specific use case in mind.

We frequently have SQL statements with WHERE clauses containing "magic"
numbers, for example:

where region in
(
1,
2,
3
)

We obviously have meaning for these numbers. Typically, they are
primary keys in a reference table which translates them in to meaningful
strings, perhaps North, East, West, etc. We generate Java enums for
these references.

I'd like to be able to use these enums in the SQL. For example, instead
of "1", I might use #{com.mycompany.project.model.RegionEnum.NORTH}.
This is not possible with the current expression evaluator, which only
allows object property references. I could define MyBatis properties
and use those property names, but that is too much duplication.

I figured out how to reference enums in OGNL expressions:

@com.mycompany.project.model.RegionEnum@NORTH

Perhaps we could figure out some way to allow the scripting languages to
evaluate parameter expressions.

Thanks.

--
Guy Rouillier

[Frank Martínez]

Hi Guy,

You can use the bind tag (http://mybatis.github.com/mybatis-3/dynamic-sql.html#bind) :

<bind name="NORTH" value="@com.mycompany.project.model.RegionEnum@NORTH" />

then use #{NORTH} where you need.

If you use it in many places, you can set all aliases in a fragment and then include it wherever you need:

<sql id="MyEnumAliases">

  <bind name="NORTH" value="@com.mycompany.project.model.RegionEnum@NORTH" />

  <bind ...>

  <bind ...>

</sql>

so you can:

<select .....>

   <include refid="MyEnumAliases" />

   SELECT ...... WHERE xx IN ( #{NORTH}, #{SOUTH} )

</select>

What do you think?

Frank.

--
Guy Rouillier

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Frank D. Martínez M.

[Guy Rouillier]

On 3/25/2013 7:41 AM, Frank Mart�nez wrote:
> Hi Guy,
>
> You can use the bind tag
> (http://mybatis.github.com/mybatis-3/dynamic-sql.html#bind) :
>
> <bind name="NORTH"

> value="@com.mycompany.project.model.__RegionEnum@NORTH" />

>
> then use #{NORTH} where you need.
>
> If you use it in many places, you can set all aliases in a fragment and
> then include it wherever you need:
>
> <sql id="MyEnumAliases">
> <bind name="NORTH"

> value="@com.mycompany.project.model.__RegionEnum@NORTH" />

> <bind ...>
> <bind ...>
> </sql>
>
> so you can:
>
> <select .....>
> <include refid="MyEnumAliases" />
> SELECT ...... WHERE xx IN ( #{NORTH}, #{SOUTH} )
> </select>
>
> What do you think?

Frank, thanks for the idea. This would allow me to avoid magic numbers
in the SQL, but at the cost of having to duplicate my enum definitions
in bind statements. I don't really like that solution. Aside from all
the duplication, if my enums change, I'll have to also potentially
change all the bindings.

I see here
http://mybatis.github.com/mybatis-3/configuration.html#plugins that
ParameterHandler is a plugin, so I can override it. Let me try that.
My plan is to invoke the DefaultParameterHandler, and if that doesn't
find the parameter value, then search along the classpath.

What do you think of this approach? It would allow you to use enums
(and static class variables) without having to create bind variables for
each one.

--
Guy Rouillier

[Guy Rouillier]

On 3/25/2013 7:30 PM, Guy Rouillier wrote:
> I see here
> http://mybatis.github.com/mybatis-3/configuration.html#plugins that
> ParameterHandler is a plugin, so I can override it. Let me try that. My
> plan is to invoke the DefaultParameterHandler, and if that doesn't find
> the parameter value, then search along the classpath.
>
> What do you think of this approach? It would allow you to use enums
> (and static class variables) without having to create bind variables for
> each one.

I thought about this issue again tonight while I was out walking the
dog; I get all my insights then :). MyBatis 3.2 now permits pluggable
scripting engines. I just reread
http://code.google.com/p/mybatis/issues/detail?id=583 where Frank and
Eduardo discuss allowing the selected scripting engine to handle the ${}
expressions. I like that idea better than my original idea above. No
point in modifying the DefaultParameterHandler to do something the
scripting engines already do.

I'd like to create a patch for passing off expression evaluation to the
scripting engine. I haven't read the code to see how the SQL statements
are passed through the selected scripting engine. Do you just blindly
pass the statement to the engine, and then do the parameter handling on
whatever string gets returned? I'm wondering how you avoid looking for
language-dependent delimiters, like "#" for Velocity or "<" for OGNL. I
suppose I should start reading the code :).

Thanks.

--
Guy Rouillier

[Frank Martínez]

Hi Guy,

Maybe you are looking for something like this: https://github.com/mybatis/velocity-scripting/commit/9c2fb55193dff3dbe578f10fcb9dfa1dbcc0e88d

What do you think?

--
Guy Rouillier

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Eduardo Macarron]

If there is something I do really not like of MyBatis 3 is using OGNL
expresions inside dynamic params but custom expressions in xml
mappings and inline parameters.

It would be really great to be able to use the same language for both
but that is not easy at all.

Note that the custom expresions are used in two main points.
- The DefaultParameterHandler
- The ResultSetHandlers (Fast and nested)

The DefaultParameterHandler can be replaced by your own one with your
expressions but... that cannot be done with the ResultHandlers.

Ideas will be really welcome...


2013/4/18 Frank Martínez <mnes...@gmail.com>:

>> email to mybatis-user...@googlegroups.com.

>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>
>
>
> --
> Frank D. Martínez M.
>

> --
> You received this message because you are subscribed to the Google Groups
> "mybatis-user" group.
> To unsubscribe from this group and stop receiving emails from it, send an

> email to mybatis-user...@googlegroups.com.

[Guy Rouillier]

I took a look at the example that Frank pointed to in GitHub. It's a
step closer to solution, but I think we should be able to come up with
something more generalized and easier to use.

Rather than continue the discussion further here, I'll open a Jira issue
where we can identify some use cases and hopefully come up with a
desired solution.

Thanks.

On 4/18/2013 1:27 PM, Eduardo Macarron wrote:
> If there is something I do really not like of MyBatis 3 is using OGNL
> expresions inside dynamic params but custom expressions in xml
> mappings and inline parameters.
>
> It would be really great to be able to use the same language for both
> but that is not easy at all.
>
> Note that the custom expresions are used in two main points.
> - The DefaultParameterHandler
> - The ResultSetHandlers (Fast and nested)
>
> The DefaultParameterHandler can be replaced by your own one with your
> expressions but... that cannot be done with the ResultHandlers.
>
> Ideas will be really welcome...
>
>

> 2013/4/18 Frank Mart�nez <mnes...@gmail.com>:

>> Frank D. Mart�nez M.

>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "mybatis-user" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to mybatis-user...@googlegroups.com.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>

--
Guy Rouillier

[Guy Rouillier]

Please see https://github.com/mybatis/mybatis-3/issues/32. Thanks.

[Guy Rouillier]

Well, I'll be. Intentionally or otherwise, the current MyBatis
implementation (I tried 3.1.1) actually handles references to enums
inside ${}. Here is a working example:

<select id="selectAllAccounts" resultMap="AccountMap">
SELECT
id,
name,
${@org.sample.beans...@RESELLER.toInt()} as
account_type_id
FROM
account
WHERE
-- ${} works, #{} does not
account_type_id = ${@org.sample.beans...@RETAIL.toInt()}
</select>

[Frank Martínez]

Additiona info: You can pass objects from the scripting context to the parameterization context using bind:

<bind name="RESELER" value=" @org.sample.beans.AccountTypeE...@RESELLER.toInt()" />

Then you can use it in the parameterization contex:

#{RELESER}

Cheers,

Frank.

On Sun, Apr 21, 2013 at 11:52 PM, Guy Rouillier <guy.ro...@gmail.com> wrote:

Well, I'll be.  Intentionally or otherwise, the current MyBatis implementation (I tried 3.1.1) actually handles references to enums inside ${}.  Here is a working example:

   <select id="selectAllAccounts" resultMap="AccountMap">
      SELECT
         id,
         name,

         ${@org.sample.beans.Account...@RESELLER.toInt()} as account_type_id

      FROM
         account
      WHERE
        -- ${} works, #{} does not

        account_type_id = ${@org.sample.beans.Account...@RETAIL.toInt()}
        </select>

On 4/19/2013 2:17 AM, Guy Rouillier wrote:

Please see https://github.com/mybatis/mybatis-3/issues/32.  Thanks.

On 4/18/2013 8:24 PM, Guy Rouillier wrote:

I took a look at the example that Frank pointed to in GitHub.  It's a
step closer to solution, but I think we should be able to come up with
something more generalized and easier to use.

Rather than continue the discussion further here, I'll open a Jira issue
where we can identify some use cases and hopefully come up with a
desired solution.

Thanks.

On 4/18/2013 1:27 PM, Eduardo Macarron wrote:

If there is something I do really not like of MyBatis 3 is using OGNL
expresions inside dynamic params but custom expressions in xml
mappings and inline parameters.

It would be really great to be able to use the same language for both
but that is not easy at all.

Note that the custom expresions are used in two main points.
- The DefaultParameterHandler
- The ResultSetHandlers (Fast and nested)

The DefaultParameterHandler can be replaced by your own one with your
expressions but... that cannot be done with the ResultHandlers.

Ideas will be really welcome...

2013/4/18 Frank Martínez <mnes...@gmail.com>:

email to mybatis-user+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--
Frank D. Martínez M.

--
You received this message because you are subscribed to the Google
Groups
"mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it,
send an

email to mybatis-user+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--
Guy Rouillier

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--
Frank D. Martínez M.

[Guy Rouillier]

Thanks for pointing that out, Frank. Unfortunately, I don't think that
creating bind names will often be very useful. Consider the situation
where I have an IN clause:

where X in

(
1,
2,
3
)

and I want to instead use enum constants for these three magic numbers.
To use bind, I'd first have to create 3 bind names, one for each of
the magic numbers, and then I could use the bind name in place of the
number. That would look something like this:

<select id="selectAllAccounts" resultMap="AccountMap">

<bind name="RETAIL" value="
@org.sample.beans...@RETAIL.toInt()" />
<bind name="RESELLER" value="
@org.sample.beans...@RESELLER.toInt()" />
<bind name="AGENT" value="
@org.sample.beans...@AGENT.toInt()" />

...
where X in
(
${RETAIL},
${RESELLER},
${AGENT}
)

I'd actually save typing by just using the enum constants in the SELECT
statement directly:

<select id="selectAllAccounts" resultMap="AccountMap">

...
where X in
(
${@org.sample.beans...@RETAIL.toInt()},
${@org.sample.beans...@RESELLER.toInt()},
${@org.sample.beans...@AGENT.toInt()}
)

If you were going to use a constant more than once, then using a bind
name would provide some benefit.

While typing this up, I thought it would be handy if I could just put
the enum class name in the bind name, and then add the individual enum
on the end, something like this:

<select id="selectAllAccounts" resultMap="AccountMap">

<bind name="ACCTTYPE" value=" @org.sample.beans.AccountTypeEnum" />
...
where X in
(
${ACCT...@RETAIL.toInt()},
${ACCT...@RESELLER.toInt()},
${ACCT...@AGENT.toInt()}
)

Alas this doesn't work. Type aliases don't work either. Oh well, I'm
happy to be able to use enums at all. We should document that
unadvertised feature.

Thanks.

On 4/22/2013 9:51 AM, Frank Mart�nez wrote:
> Additiona info: You can pass objects from the scripting context to the
> parameterization context using bind:
>
> <bind name="RESELER"

> value=" @org.sample.beans....@RESELLER.toInt__()" />

>
> Then you can use it in the parameterization contex:
>
> #{RELESER}
>
> Cheers,
>
> Frank.
>
>
> On Sun, Apr 21, 2013 at 11:52 PM, Guy Rouillier <guy.ro...@gmail.com
> <mailto:guy.ro...@gmail.com>> wrote:
>
> Well, I'll be. Intentionally or otherwise, the current MyBatis
> implementation (I tried 3.1.1) actually handles references to enums
> inside ${}. Here is a working example:
>
> <select id="selectAllAccounts" resultMap="AccountMap">
> SELECT
> id,
> name,

> ${@org.sample.beans....@RESELLER.toInt__()}

> as account_type_id
> FROM
> account
> WHERE
> -- ${} works, #{} does not
> account_type_id =

> ${@org.sample.beans....@RETAIL.toInt()__}

> </select>
>
>
> On 4/19/2013 2:17 AM, Guy Rouillier wrote:
>

> Please see https://github.com/mybatis/__mybatis-3/issues/32
> <https://github.com/mybatis/mybatis-3/issues/32>. Thanks.

> 2013/4/18 Frank Mart�nez <mnes...@gmail.com
> <mailto:mnes...@gmail.com>>:

>
> Hi Guy,
>
> Maybe you are looking for something like this:

> https://github.com/mybatis/__velocity-scripting/commit/__9c2fb55193dff3dbe578f10fcb9dfa__1dbcc0e88d

> <https://github.com/mybatis/velocity-scripting/commit/9c2fb55193dff3dbe578f10fcb9dfa1dbcc0e88d>
>
>
>
> What do you think?
>
>
>
> On Thu, Apr 18, 2013 at 1:45 AM, Guy Rouillier
> <guy.ro...@gmail.com

> <mailto:guy.ro...@gmail.com>>

> wrote:
>
>
> On 3/25/2013 7:30 PM, Guy Rouillier wrote:
>
>
> I see here

> http://mybatis.github.com/__mybatis-3/configuration.html#__plugins

> <http://mybatis.github.com/mybatis-3/configuration.html#plugins>
> that
> ParameterHandler is a plugin, so I can
> override it. Let me try
> that. My
> plan is to invoke the
> DefaultParameterHandler, and if that doesn't
> find
> the parameter value, then search along the
> classpath.
>
> What do you think of this approach? It
> would allow you to use enums
> (and static class variables) without having
> to create bind
> variables for
> each one.
>
>
>
> I thought about this issue again tonight while I
> was out walking the
> dog;
> I get all my insights then :). MyBatis 3.2 now
> permits pluggable
> scripting
> engines. I just reread

> http://code.google.com/p/__mybatis/issues/detail?id=583

> mybatis-user+unsubscribe@__googlegroups.com
> <mailto:mybatis-user%2Bunsu...@googlegroups.com>.
> For more options, visit
> https://groups.google.com/__groups/opt_out
> <https://groups.google.com/groups/opt_out>.
>
>
>
>
>
> --
> Frank D. Mart�nez M.

>
> --
> You received this message because you are subscribed
> to the Google
> Groups
> "mybatis-user" group.
> To unsubscribe from this group and stop receiving
> emails from it,
> send an

> email to mybatis-user+unsubscribe@__googlegroups.com
> <mailto:mybatis-user%2Bunsu...@googlegroups.com>.
> For more options, visit
> https://groups.google.com/__groups/opt_out
> <https://groups.google.com/groups/opt_out>.

>
>
>
>
>
>
>
>
>
> --
> Guy Rouillier
>
> --
> You received this message because you are subscribed to the Google
> Groups "mybatis-user" group.
> To unsubscribe from this group and stop receiving emails from it,

> send an email to mybatis-user+unsubscribe@__googlegroups.com
> <mailto:mybatis-user%2Bunsu...@googlegroups.com>.
> For more options, visit https://groups.google.com/__groups/opt_out
> <https://groups.google.com/groups/opt_out>.
>
>
>
>
>
> --
> Frank D. Mart�nez M.

>
> --
> You received this message because you are subscribed to the Google
> Groups "mybatis-user" group.
> To unsubscribe from this group and stop receiving emails from it, send

> an email to mybatis-user...@googlegroups.com.