Dynamic SQL

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

Dynamic SQL

charlie
I need some help how to use dynamic SQL. I need further explanation
from what is detailed in MyBatis 3 User Guide.

I dont understand how to pass a variable list of a java bean to the
collection for it to iterate the list

I have a java been CompatibleCurrentInscopeOffer which has a variable
List subscriberBoltOnsList.

In my mapper.xml I have

<select id="selectBlog" parameterType="CompatibleCurrentInscopeOffer"
resultType="Integer">
SELECT OFFER_ID
FROM
OFFERS_REAL
WHERE
SPID  = 283
<if test="subscriberBoltOnsList != null">
            minus select INCOMPATIBLE_OFFER_ID
            from   COMPATIBILITY
            where  OFFER_ID in
            <foreach item="item" index="index"
collection="subscriberBoltOnsList"
                    open="(" separator="," close=")">
                    #{item}
            </foreach>
</if>

I would appreciate any explanation or examples

Cheers
Charlie
Reply | Threaded
Open this post in threaded view
|

RE: Dynamic SQL

Poitras Christian
Your SQL looks fine to me, but if "item" is a java bean, you may prefer to use a property of item instead of the whole object.

For example:
<foreach item="item" index="index" collection="subscriberBoltOnsList" open="(" separator="," close=")">
    #{item.myProperty}
</foreach>

Christian

-----Message d'origine-----
De : [hidden email] [mailto:[hidden email]] De la part de charlie
Envoyé : September-24-10 7:18 AM
À : mybatis-user
Objet : Dynamic SQL

I need some help how to use dynamic SQL. I need further explanation
from what is detailed in MyBatis 3 User Guide.

I dont understand how to pass a variable list of a java bean to the
collection for it to iterate the list

I have a java been CompatibleCurrentInscopeOffer which has a variable
List subscriberBoltOnsList.

In my mapper.xml I have

<select id="selectBlog" parameterType="CompatibleCurrentInscopeOffer"
resultType="Integer">
SELECT OFFER_ID
FROM
OFFERS_REAL
WHERE
SPID  = 283
<if test="subscriberBoltOnsList != null">
            minus select INCOMPATIBLE_OFFER_ID
            from   COMPATIBILITY
            where  OFFER_ID in
            <foreach item="item" index="index"
collection="subscriberBoltOnsList"
                    open="(" separator="," close=")">
                    #{item}
            </foreach>
</if>

I would appreciate any explanation or examples

Cheers
Charlie
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SQL

charlie
My subscriberBoltOnsList is an ArrayList of int's. I am getting the
following error

### Error querying database.  Cause: java.lang.NullPointerException
### The error may involve BlogMapper.selectBlog-Inline
### The error occurred while setting parameters
### Cause: java.lang.NullPointerException

On Sep 24, 12:49 pm, Poitras Christian <[hidden email]>
wrote:

> Your SQL looks fine to me, but if "item" is a java bean, you may prefer to use a property of item instead of the whole object.
>
> For example:
> <foreach item="item" index="index" collection="subscriberBoltOnsList" open="(" separator="," close=")">
>     #{item.myProperty}
> </foreach>
>
> Christian
>
> -----Message d'origine-----
> De : [hidden email] [mailto:[hidden email]] De la part de charlie
> Envoyé : September-24-10 7:18 AM
> À : mybatis-user
> Objet : Dynamic SQL
>
> I need some help how to use dynamic SQL. I need further explanation
> from what is detailed in MyBatis 3 User Guide.
>
> I dont understand how to pass a variable list of a java bean to the
> collection for it to iterate the list
>
> I have a java been CompatibleCurrentInscopeOffer which has a variable
> List subscriberBoltOnsList.
>
> In my mapper.xml I have
>
> <select id="selectBlog" parameterType="CompatibleCurrentInscopeOffer"
> resultType="Integer">
> SELECT OFFER_ID
> FROM
> OFFERS_REAL
> WHERE
> SPID  = 283
> <if test="subscriberBoltOnsList != null">
>             minus select INCOMPATIBLE_OFFER_ID
>             from   COMPATIBILITY
>             where  OFFER_ID in
>             <foreach item="item" index="index"
> collection="subscriberBoltOnsList"
>                     open="(" separator="," close=")">
>                     #{item}
>             </foreach>
> </if>
>
> I would appreciate any explanation or examples
>
> Cheers
> Charlie
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SQL

Riccardo Cossu
Could you debug the collection and see if there is a null element in
it? This seems the case.

On 24 Set, 14:03, charlie <[hidden email]> wrote:

> My subscriberBoltOnsList is an ArrayList of int's. I am getting the
> following error
>
> ### Error querying database.  Cause: java.lang.NullPointerException
> ### The error may involve BlogMapper.selectBlog-Inline
> ### The error occurred while setting parameters
> ### Cause: java.lang.NullPointerException
>
> On Sep 24, 12:49 pm, Poitras Christian <[hidden email]>
> wrote:
>
>
>
> > Your SQL looks fine to me, but if "item" is a java bean, you may prefer to use a property of item instead of the whole object.
>
> > For example:
> > <foreach item="item" index="index" collection="subscriberBoltOnsList" open="(" separator="," close=")">
> >     #{item.myProperty}
> > </foreach>
>
> > Christian
>
> > -----Message d'origine-----
> > De : [hidden email] [mailto:[hidden email]] De la part de charlie
> > Envoyé : September-24-10 7:18 AM
> > À : mybatis-user
> > Objet : Dynamic SQL
>
> > I need some help how to use dynamic SQL. I need further explanation
> > from what is detailed in MyBatis 3 User Guide.
>
> > I dont understand how to pass a variable list of a java bean to the
> > collection for it to iterate the list
>
> > I have a java been CompatibleCurrentInscopeOffer which has a variable
> > List subscriberBoltOnsList.
>
> > In my mapper.xml I have
>
> > <select id="selectBlog" parameterType="CompatibleCurrentInscopeOffer"
> > resultType="Integer">
> > SELECT OFFER_ID
> > FROM
> > OFFERS_REAL
> > WHERE
> > SPID  = 283
> > <if test="subscriberBoltOnsList != null">
> >             minus select INCOMPATIBLE_OFFER_ID
> >             from   COMPATIBILITY
> >             where  OFFER_ID in
> >             <foreach item="item" index="index"
> > collection="subscriberBoltOnsList"
> >                     open="(" separator="," close=")">
> >                     #{item}
> >             </foreach>
> > </if>
>
> > I would appreciate any explanation or examples
>
> > Cheers
> > Charlie
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SQL

charlie
I have debugged the list and it is not null, this is also check in the
if statement of the mapper.xml.

On Sep 24, 1:46 pm, Riccardo <[hidden email]> wrote:

> Could you debug the collection and see if there is a null element in
> it? This seems the case.
>
> On 24 Set, 14:03, charlie <[hidden email]> wrote:
>
>
>
> > My subscriberBoltOnsList is an ArrayList of int's. I am getting the
> > following error
>
> > ### Error querying database.  Cause: java.lang.NullPointerException
> > ### The error may involve BlogMapper.selectBlog-Inline
> > ### The error occurred while setting parameters
> > ### Cause: java.lang.NullPointerException
>
> > On Sep 24, 12:49 pm, Poitras Christian <[hidden email]>
> > wrote:
>
> > > Your SQL looks fine to me, but if "item" is a java bean, you may prefer to use a property of item instead of the whole object.
>
> > > For example:
> > > <foreach item="item" index="index" collection="subscriberBoltOnsList" open="(" separator="," close=")">
> > >     #{item.myProperty}
> > > </foreach>
>
> > > Christian
>
> > > -----Message d'origine-----
> > > De : [hidden email] [mailto:[hidden email]] De la part de charlie
> > > Envoyé : September-24-10 7:18 AM
> > > À : mybatis-user
> > > Objet : Dynamic SQL
>
> > > I need some help how to use dynamic SQL. I need further explanation
> > > from what is detailed in MyBatis 3 User Guide.
>
> > > I dont understand how to pass a variable list of a java bean to the
> > > collection for it to iterate the list
>
> > > I have a java been CompatibleCurrentInscopeOffer which has a variable
> > > List subscriberBoltOnsList.
>
> > > In my mapper.xml I have
>
> > > <select id="selectBlog" parameterType="CompatibleCurrentInscopeOffer"
> > > resultType="Integer">
> > > SELECT OFFER_ID
> > > FROM
> > > OFFERS_REAL
> > > WHERE
> > > SPID  = 283
> > > <if test="subscriberBoltOnsList != null">
> > >             minus select INCOMPATIBLE_OFFER_ID
> > >             from   COMPATIBILITY
> > >             where  OFFER_ID in
> > >             <foreach item="item" index="index"
> > > collection="subscriberBoltOnsList"
> > >                     open="(" separator="," close=")">
> > >                     #{item}
> > >             </foreach>
> > > </if>
>
> > > I would appreciate any explanation or examples
>
> > > Cheers
> > > Charlie- Hide quoted text -
>
> - Show quoted text -
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SQL

Riccardo Cossu
Hello,
I didn't mean to check if the list itself is null, but to check every
element included in the list and see if anyone is null.

Riccardo

On 24 Set, 15:04, charlie <[hidden email]> wrote:

> I have debugged the list and it is not null, this is also check in the
> if statement of the mapper.xml.
>
> On Sep 24, 1:46 pm, Riccardo <[hidden email]> wrote:
>
>
>
> > Could you debug the collection and see if there is a null element in
> > it? This seems the case.
>
> > On 24 Set, 14:03, charlie <[hidden email]> wrote:
>
> > > My subscriberBoltOnsList is an ArrayList of int's. I am getting the
> > > following error
>
> > > ### Error querying database.  Cause: java.lang.NullPointerException
> > > ### The error may involve BlogMapper.selectBlog-Inline
> > > ### The error occurred while setting parameters
> > > ### Cause: java.lang.NullPointerException
>
> > > On Sep 24, 12:49 pm, Poitras Christian <[hidden email]>
> > > wrote:
>
> > > > Your SQL looks fine to me, but if "item" is a java bean, you may prefer to use a property of item instead of the whole object.
>
> > > > For example:
> > > > <foreach item="item" index="index" collection="subscriberBoltOnsList" open="(" separator="," close=")">
> > > >     #{item.myProperty}
> > > > </foreach>
>
> > > > Christian
>
> > > > -----Message d'origine-----
> > > > De : [hidden email] [mailto:[hidden email]] De la part de charlie
> > > > Envoyé : September-24-10 7:18 AM
> > > > À : mybatis-user
> > > > Objet : Dynamic SQL
>
> > > > I need some help how to use dynamic SQL. I need further explanation
> > > > from what is detailed in MyBatis 3 User Guide.
>
> > > > I dont understand how to pass a variable list of a java bean to the
> > > > collection for it to iterate the list
>
> > > > I have a java been CompatibleCurrentInscopeOffer which has a variable
> > > > List subscriberBoltOnsList.
>
> > > > In my mapper.xml I have
>
> > > > <select id="selectBlog" parameterType="CompatibleCurrentInscopeOffer"
> > > > resultType="Integer">
> > > > SELECT OFFER_ID
> > > > FROM
> > > > OFFERS_REAL
> > > > WHERE
> > > > SPID  = 283
> > > > <if test="subscriberBoltOnsList != null">
> > > >             minus select INCOMPATIBLE_OFFER_ID
> > > >             from   COMPATIBILITY
> > > >             where  OFFER_ID in
> > > >             <foreach item="item" index="index"
> > > > collection="subscriberBoltOnsList"
> > > >                     open="(" separator="," close=")">
> > > >                     #{item}
> > > >             </foreach>
> > > > </if>
>
> > > > I would appreciate any explanation or examples
>
> > > > Cheers
> > > > Charlie- Hide quoted text -
>
> > - Show quoted text -
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SQL

charlie
I have changed the collection from an ArrayList to Integer [] and it
now works.

I have discover that if I create an ArrayList from Arrays.asList(int
[] mylist), an array of primitives, this will not work. When I create
a collection of Integer [] this worked, also when I create an
ArrayList from Arrays.asList(Integer [] mylist) an array of Integers
it worked.

I have concluded that the collection must be a collection of objects
not primitives.


On Sep 24, 2:11 pm, Riccardo <[hidden email]> wrote:

> Hello,
> I didn't mean to check if the list itself is null, but to check every
> element included in the list and see if anyone is null.
>
> Riccardo
>
> On 24 Set, 15:04, charlie <[hidden email]> wrote:
>
>
>
> > I have debugged the list and it is not null, this is also check in the
> > if statement of the mapper.xml.
>
> > On Sep 24, 1:46 pm, Riccardo <[hidden email]> wrote:
>
> > > Could you debug the collection and see if there is a null element in
> > > it? This seems the case.
>
> > > On 24 Set, 14:03, charlie <[hidden email]> wrote:
>
> > > > My subscriberBoltOnsList is an ArrayList of int's. I am getting the
> > > > following error
>
> > > > ### Error querying database.  Cause: java.lang.NullPointerException
> > > > ### The error may involve BlogMapper.selectBlog-Inline
> > > > ### The error occurred while setting parameters
> > > > ### Cause: java.lang.NullPointerException
>
> > > > On Sep 24, 12:49 pm, Poitras Christian <[hidden email]>
> > > > wrote:
>
> > > > > Your SQL looks fine to me, but if "item" is a java bean, you may prefer to use a property of item instead of the whole object.
>
> > > > > For example:
> > > > > <foreach item="item" index="index" collection="subscriberBoltOnsList" open="(" separator="," close=")">
> > > > >     #{item.myProperty}
> > > > > </foreach>
>
> > > > > Christian
>
> > > > > -----Message d'origine-----
> > > > > De : [hidden email] [mailto:[hidden email]] De la part de charlie
> > > > > Envoyé : September-24-10 7:18 AM
> > > > > À : mybatis-user
> > > > > Objet : Dynamic SQL
>
> > > > > I need some help how to use dynamic SQL. I need further explanation
> > > > > from what is detailed in MyBatis 3 User Guide.
>
> > > > > I dont understand how to pass a variable list of a java bean to the
> > > > > collection for it to iterate the list
>
> > > > > I have a java been CompatibleCurrentInscopeOffer which has a variable
> > > > > List subscriberBoltOnsList.
>
> > > > > In my mapper.xml I have
>
> > > > > <select id="selectBlog" parameterType="CompatibleCurrentInscopeOffer"
> > > > > resultType="Integer">
> > > > > SELECT OFFER_ID
> > > > > FROM
> > > > > OFFERS_REAL
> > > > > WHERE
> > > > > SPID  = 283
> > > > > <if test="subscriberBoltOnsList != null">
> > > > >             minus select INCOMPATIBLE_OFFER_ID
> > > > >             from   COMPATIBILITY
> > > > >             where  OFFER_ID in
> > > > >             <foreach item="item" index="index"
> > > > > collection="subscriberBoltOnsList"
> > > > >                     open="(" separator="," close=")">
> > > > >                     #{item}
> > > > >             </foreach>
> > > > > </if>
>
> > > > > I would appreciate any explanation or examples
>
> > > > > Cheers
> > > > > Charlie- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SQL

asherwin
You can't have a collection of primitives.. If you add primitives to a
collection they are autoboxed into their equivalent Objects.

I had an issue with iBatis 2.x and autoboxing (not this particular
problem) and the solution was to avoid nuances of autoboxing
altogether when dealing with things that will be passed down to iBatis

On Sep 24, 9:45 am, charlie <[hidden email]> wrote:

> I have changed the collection from an ArrayList to Integer [] and it
> now works.
>
> I have discover that if I create an ArrayList from Arrays.asList(int
> [] mylist), an array of primitives, this will not work. When I create
> a collection of Integer [] this worked, also when I create an
> ArrayList from Arrays.asList(Integer [] mylist) an array of Integers
> it worked.
>
> I have concluded that the collection must be a collection of objects
> not primitives.
>
> On Sep 24, 2:11 pm, Riccardo <[hidden email]> wrote:
>
>
>
> > Hello,
> > I didn't mean to check if the list itself is null, but to check every
> > element included in the list and see if anyone is null.
>
> > Riccardo
>
> > On 24 Set, 15:04, charlie <[hidden email]> wrote:
>
> > > I have debugged the list and it is not null, this is also check in the
> > > if statement of the mapper.xml.
>
> > > On Sep 24, 1:46 pm, Riccardo <[hidden email]> wrote:
>
> > > > Could you debug the collection and see if there is a null element in
> > > > it? This seems the case.
>
> > > > On 24 Set, 14:03, charlie <[hidden email]> wrote:
>
> > > > > My subscriberBoltOnsList is an ArrayList of int's. I am getting the
> > > > > following error
>
> > > > > ### Error querying database.  Cause: java.lang.NullPointerException
> > > > > ### The error may involve BlogMapper.selectBlog-Inline
> > > > > ### The error occurred while setting parameters
> > > > > ### Cause: java.lang.NullPointerException
>
> > > > > On Sep 24, 12:49 pm, Poitras Christian <[hidden email]>
> > > > > wrote:
>
> > > > > > Your SQL looks fine to me, but if "item" is a java bean, you may prefer to use a property of item instead of the whole object.
>
> > > > > > For example:
> > > > > > <foreach item="item" index="index" collection="subscriberBoltOnsList" open="(" separator="," close=")">
> > > > > >     #{item.myProperty}
> > > > > > </foreach>
>
> > > > > > Christian
>
> > > > > > -----Message d'origine-----
> > > > > > De : [hidden email] [mailto:[hidden email]] De la part de charlie
> > > > > > Envoyé : September-24-10 7:18 AM
> > > > > > À : mybatis-user
> > > > > > Objet : Dynamic SQL
>
> > > > > > I need some help how to use dynamic SQL. I need further explanation
> > > > > > from what is detailed in MyBatis 3 User Guide.
>
> > > > > > I dont understand how to pass a variable list of a java bean to the
> > > > > > collection for it to iterate the list
>
> > > > > > I have a java been CompatibleCurrentInscopeOffer which has a variable
> > > > > > List subscriberBoltOnsList.
>
> > > > > > In my mapper.xml I have
>
> > > > > > <select id="selectBlog" parameterType="CompatibleCurrentInscopeOffer"
> > > > > > resultType="Integer">
> > > > > > SELECT OFFER_ID
> > > > > > FROM
> > > > > > OFFERS_REAL
> > > > > > WHERE
> > > > > > SPID  = 283
> > > > > > <if test="subscriberBoltOnsList != null">
> > > > > >             minus select INCOMPATIBLE_OFFER_ID
> > > > > >             from   COMPATIBILITY
> > > > > >             where  OFFER_ID in
> > > > > >             <foreach item="item" index="index"
> > > > > > collection="subscriberBoltOnsList"
> > > > > >                     open="(" separator="," close=")">
> > > > > >                     #{item}
> > > > > >             </foreach>
> > > > > > </if>
>
> > > > > > I would appreciate any explanation or examples
>
> > > > > > Cheers
> > > > > > Charlie- Hide quoted text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SQL

asherwin
In reply to this post by charlie
Upon closer reading of your statement, you cannot do this:

final List list = Arrays.asList(new int[]{ 1, 2 });

You can't have primitives in a List (Collection), the JDK normally
autoboxes them.

In this scenario, the JDK does not know that you intended to create a
List<Integer> and to auto box the elements of your primitive array as
the elements of your list.

Instead, what has happened here is you've created a List instance
which has a single item, which is an Array Object of type int[] with
entries.. [1, 2]

On Sep 24, 9:45 am, charlie <[hidden email]> wrote:

> I have changed the collection from an ArrayList to Integer [] and it
> now works.
>
> I have discover that if I create an ArrayList from Arrays.asList(int
> [] mylist), an array of primitives, this will not work. When I create
> a collection of Integer [] this worked, also when I create an
> ArrayList from Arrays.asList(Integer [] mylist) an array of Integers
> it worked.
>
> I have concluded that the collection must be a collection of objects
> not primitives.
>
> On Sep 24, 2:11 pm, Riccardo <[hidden email]> wrote:
>
>
>
> > Hello,
> > I didn't mean to check if the list itself is null, but to check every
> > element included in the list and see if anyone is null.
>
> > Riccardo
>
> > On 24 Set, 15:04, charlie <[hidden email]> wrote:
>
> > > I have debugged the list and it is not null, this is also check in the
> > > if statement of the mapper.xml.
>
> > > On Sep 24, 1:46 pm, Riccardo <[hidden email]> wrote:
>
> > > > Could you debug the collection and see if there is a null element in
> > > > it? This seems the case.
>
> > > > On 24 Set, 14:03, charlie <[hidden email]> wrote:
>
> > > > > My subscriberBoltOnsList is an ArrayList of int's. I am getting the
> > > > > following error
>
> > > > > ### Error querying database.  Cause: java.lang.NullPointerException
> > > > > ### The error may involve BlogMapper.selectBlog-Inline
> > > > > ### The error occurred while setting parameters
> > > > > ### Cause: java.lang.NullPointerException
>
> > > > > On Sep 24, 12:49 pm, Poitras Christian <[hidden email]>
> > > > > wrote:
>
> > > > > > Your SQL looks fine to me, but if "item" is a java bean, you may prefer to use a property of item instead of the whole object.
>
> > > > > > For example:
> > > > > > <foreach item="item" index="index" collection="subscriberBoltOnsList" open="(" separator="," close=")">
> > > > > >     #{item.myProperty}
> > > > > > </foreach>
>
> > > > > > Christian
>
> > > > > > -----Message d'origine-----
> > > > > > De : [hidden email] [mailto:[hidden email]] De la part de charlie
> > > > > > Envoyé : September-24-10 7:18 AM
> > > > > > À : mybatis-user
> > > > > > Objet : Dynamic SQL
>
> > > > > > I need some help how to use dynamic SQL. I need further explanation
> > > > > > from what is detailed in MyBatis 3 User Guide.
>
> > > > > > I dont understand how to pass a variable list of a java bean to the
> > > > > > collection for it to iterate the list
>
> > > > > > I have a java been CompatibleCurrentInscopeOffer which has a variable
> > > > > > List subscriberBoltOnsList.
>
> > > > > > In my mapper.xml I have
>
> > > > > > <select id="selectBlog" parameterType="CompatibleCurrentInscopeOffer"
> > > > > > resultType="Integer">
> > > > > > SELECT OFFER_ID
> > > > > > FROM
> > > > > > OFFERS_REAL
> > > > > > WHERE
> > > > > > SPID  = 283
> > > > > > <if test="subscriberBoltOnsList != null">
> > > > > >             minus select INCOMPATIBLE_OFFER_ID
> > > > > >             from   COMPATIBILITY
> > > > > >             where  OFFER_ID in
> > > > > >             <foreach item="item" index="index"
> > > > > > collection="subscriberBoltOnsList"
> > > > > >                     open="(" separator="," close=")">
> > > > > >                     #{item}
> > > > > >             </foreach>
> > > > > > </if>
>
> > > > > > I would appreciate any explanation or examples
>
> > > > > > Cheers
> > > > > > Charlie- Hide quoted text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -