Using variable bindings in extended criteria queries ?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Using variable bindings in extended criteria queries ?

Jürgen Kärner
Hi all,

In the extended criteria queries, I usually build the SQL query
string, hardcode the variables into the string and create criteria
based on that query string (see example below).

                public Criteria andOpeatorCodeLike(String code, Long
operatorCodeType, Long operatorCodeClass) {
                        StringBuffer sb = new StringBuffer();

                        sb.append("EXISTS");
                        sb.append(" (SELECT code FROM operator_code ");
                        sb.append("  WHERE operator_id = o.id AND type_id = ");
                        sb.append(operatorCodeType);
                        sb.append("  AND code_class_id = ");
                        sb.append(operatorCodeClass);
                        sb.append("  AND code LIKE UPPER('");
                        sb.append(code);
                        sb.append("'))");

                        addCriterion(sb.toString());
                        return (Criteria) this;
                }

Considering DB performance, this is not a very effective way of
handling queries...

My question is - can I use database bindings in my extended
criterias.

The code could look something like this =>

                public Criteria andOpeatorCodeLike(String code, Long
operatorCodeType, Long operatorCodeClass) {
                        StringBuffer sb = new StringBuffer();

                        sb.append("EXISTS");
                        sb.append(" (SELECT code FROM operator_code ");
                        sb.append("  WHERE operator_id = o.id AND type_id = ? ");
                        sb.append("  AND code_class_id = ? ");
                        sb.append("  AND code LIKE UPPER( ? )) ");

                        // Bind the variables (in case of prepared
statements it gives huge performance advantage)

                        this.bind(operatorCodeType);
                        this.bind(operatorCodeClass);
                        this.bind(code);

                        addCriterion(sb.toString());
                        return (Criteria) this;
                }

Do you see what I mean ?
Is that possible in extended criteria queries ?

thanks a lot in advance,

Jürgen Kärner
Reply | Threaded
Open this post in threaded view
|

Re: Using variable bindings in extended criteria queries ?

Jeff Butler
So,  you are talking about code generated by the generator tool
(formerly Ibator).

The problem you might face is that the XML is not general enough to
handle all situations.  You can get close with code like this:

addCriterion("code_class_id =", operatorCodeClass);
addCriterion("operator_id = o.id AND type_id =", operatorCodeType);
addCriterion("code like", code.toUpperCase());

But this doesn't handle the subquery properly.

See this page for more information:

http://ibatis.apache.org/docs/tools/ibator/generatedobjects/extendingExampleClass.html


But the best solution is this - use the @SelectProvider annotation and
SelectBuilder tool in MyBatis to build the query string and
dynamically bind parameters through something like a mp parameter
object.  You can accomplish exactly what you want with that strategy.

Jeff Butler




On Wed, Sep 1, 2010 at 7:18 AM, Jürgen Kärner <[hidden email]> wrote:

> Hi all,
>
> In the extended criteria queries, I usually build the SQL query
> string, hardcode the variables into the string and create criteria
> based on that query string (see example below).
>
>                public Criteria andOpeatorCodeLike(String code, Long
> operatorCodeType, Long operatorCodeClass) {
>                        StringBuffer sb = new StringBuffer();
>
>                        sb.append("EXISTS");
>                        sb.append(" (SELECT code FROM operator_code ");
>                        sb.append("  WHERE operator_id = o.id AND type_id = ");
>                        sb.append(operatorCodeType);
>                        sb.append("  AND code_class_id = ");
>                        sb.append(operatorCodeClass);
>                        sb.append("  AND code LIKE UPPER('");
>                        sb.append(code);
>                        sb.append("'))");
>
>                        addCriterion(sb.toString());
>                        return (Criteria) this;
>                }
>
> Considering DB performance, this is not a very effective way of
> handling queries...
>
> My question is - can I use database bindings in my extended
> criterias.
>
> The code could look something like this =>
>
>                public Criteria andOpeatorCodeLike(String code, Long
> operatorCodeType, Long operatorCodeClass) {
>                        StringBuffer sb = new StringBuffer();
>
>                        sb.append("EXISTS");
>                        sb.append(" (SELECT code FROM operator_code ");
>                        sb.append("  WHERE operator_id = o.id AND type_id = ? ");
>                        sb.append("  AND code_class_id = ? ");
>                        sb.append("  AND code LIKE UPPER( ? )) ");
>
>                        // Bind the variables (in case of prepared
> statements it gives huge performance advantage)
>
>                        this.bind(operatorCodeType);
>                        this.bind(operatorCodeClass);
>                        this.bind(code);
>
>                        addCriterion(sb.toString());
>                        return (Criteria) this;
>                }
>
> Do you see what I mean ?
> Is that possible in extended criteria queries ?
>
> thanks a lot in advance,
>
> Jürgen Kärner
>
Reply | Threaded
Open this post in threaded view
|

Re: Using variable bindings in extended criteria queries ?

Jürgen Kärner

Thank you, Jeff !
I had a look at MyBatis 3 documentation and looks like select builder
is exactly what I need.

with best,
Jürgen

On 2 sept, 00:30, Jeff Butler <[hidden email]> wrote:

> So,  you are talking about code generated by the generator tool
> (formerly Ibator).
>
> The problem you might face is that the XML is not general enough to
> handle all situations.  You can get close with code like this:
>
> addCriterion("code_class_id =", operatorCodeClass);
> addCriterion("operator_id = o.id AND type_id =", operatorCodeType);
> addCriterion("code like", code.toUpperCase());
>
> But this doesn't handle the subquery properly.
>
> See this page for more information:
>
> http://ibatis.apache.org/docs/tools/ibator/generatedobjects/extending...
>
> But the best solution is this - use the @SelectProvider annotation and
> SelectBuilder tool in MyBatis to build the query string and
> dynamically bind parameters through something like a mp parameter
> object.  You can accomplish exactly what you want with that strategy.
>
> Jeff Butler
>
> On Wed, Sep 1, 2010 at 7:18 AM, Jürgen Kärner <[hidden email]> wrote:
> > Hi all,
>
> > In the extended criteria queries, I usually build the SQL query
> > string, hardcode the variables into the string and create criteria
> > based on that query string (see example below).
>
> >                public Criteria andOpeatorCodeLike(String code, Long
> > operatorCodeType, Long operatorCodeClass) {
> >                        StringBuffer sb = new StringBuffer();
>
> >                        sb.append("EXISTS");
> >                        sb.append(" (SELECT code FROM operator_code ");
> >                        sb.append("  WHERE operator_id = o.id AND type_id = ");
> >                        sb.append(operatorCodeType);
> >                        sb.append("  AND code_class_id = ");
> >                        sb.append(operatorCodeClass);
> >                        sb.append("  AND code LIKE UPPER('");
> >                        sb.append(code);
> >                        sb.append("'))");
>
> >                        addCriterion(sb.toString());
> >                        return (Criteria) this;
> >                }
>
> > Considering DB performance, this is not a very effective way of
> > handling queries...
>
> > My question is - can I use database bindings in my extended
> > criterias.
>
> > The code could look something like this =>
>
> >                public Criteria andOpeatorCodeLike(String code, Long
> > operatorCodeType, Long operatorCodeClass) {
> >                        StringBuffer sb = new StringBuffer();
>
> >                        sb.append("EXISTS");
> >                        sb.append(" (SELECT code FROM operator_code ");
> >                        sb.append("  WHERE operator_id = o.id AND type_id = ? ");
> >                        sb.append("  AND code_class_id = ? ");
> >                        sb.append("  AND code LIKE UPPER( ? )) ");
>
> >                        // Bind the variables (in case of prepared
> > statements it gives huge performance advantage)
>
> >                        this.bind(operatorCodeType);
> >                        this.bind(operatorCodeClass);
> >                        this.bind(code);
>
> >                        addCriterion(sb.toString());
> >                        return (Criteria) this;
> >                }
>
> > Do you see what I mean ?
> > Is that possible in extended criteria queries ?
>
> > thanks a lot in advance,
>
> > Jürgen Kärner