Building many option in sql mapping

[Ellison Alves]

Hello guys,

I want to put different options in sql mapping by a interface where
there are checkboxes.
The final sql statetement would be like the line below:

SELECT * FROM table WHERE checkbox1 = 0 OR checkbox2 = 1 OR checkbox3
= 3;

I want to do all mappings into xml. Therefore, I've looked for foreach
but i don't know if it could resolve this question.

Thanks!

[Brian Hurley]

I think that you need to look at your design a little more.

However, your saying that you have a table with the columns checkbox1,
checkbox2 and checkbox3 that are probably numeric.  So just make a
select that binds the map/object to those columns as you want.

SELECT * FROM table WHERE checkbox1 = #{checkbox1} OR checkbox2 =
#{checkbox2} OR checkbox3 = #{checkbox3};

What I would probably do (without knowing more about your data) is to
use an attribute table design.

create table attr_table (
   attr_id number(1) not null,
   attr_type varchar(30) not null,
   attr_value varchar(1024) not null,
   attr_date date not null );

Then use a select like this..

select * from attr_table where attr_value in (...values...) where
attr_type = 'myattribute_type';

Brian

On Wed, Sep 8, 2010 at 12:02 PM, Ellison Alves <[hidden email]> wrote:

> Hello guys,
>
> I want to put different options in sql mapping by a interface where
> there are checkboxes.
> The final sql statetement would be like the line below:
>
> SELECT * FROM table WHERE checkbox1 = 0 OR checkbox2 = 1 OR checkbox3
> = 3;
>
> I want to do all mappings into xml. Therefore, I've looked for foreach
> but i don't know if it could resolve this question.
>
> Thanks!

[Ellison Alves]

Hi Brian,

However, your saying that you have a table with the columns checkbox1,
checkbox2 and checkbox3 that are probably numeric.

No, isn't it.

I was talking about a interface of system.

Well, i want that an user can use a filter to query something in DB. (So, i've used to checkboxes on system's interface).

These checkboxes represents a state of entity.

So i want to build an sql on execution time.

2010/9/8 Brian Hurley <[hidden email]>

I think that you need to look at your design a little more.

However, your saying that you have a table with the columns checkbox1,
checkbox2 and checkbox3 that are probably numeric.  So just make a
select that binds the map/object to those columns as you want.

SELECT * FROM table WHERE checkbox1 = #{checkbox1} OR checkbox2 =
#{checkbox2} OR checkbox3 = #{checkbox3};

What I would probably do (without knowing more about your data) is to
use an attribute table design.

create table attr_table (
  attr_id number(1) not null,
  attr_type varchar(30) not null,
  attr_value varchar(1024) not null,
  attr_date date not null );

Then use a select like this..

select * from attr_table where attr_value in (...values...) where
attr_type = 'myattribute_type';

Brian

On Wed, Sep 8, 2010 at 12:02 PM, Ellison Alves <[hidden email]> wrote:
> Hello guys,
>
> I want to put different options in sql mapping by a interface where
> there are checkboxes.
> The final sql statetement would be like the line below:
>
> SELECT * FROM table WHERE checkbox1 = 0 OR checkbox2 = 1 OR checkbox3
> = 3;
>
> I want to do all mappings into xml. Therefore, I've looked for foreach
> but i don't know if it could resolve this question.
>
> Thanks!

--
Ellison Alves de Souza

[Brian Hurley]

If you look at the Dynamic SQL and Select Builder pages of the MyBatis
docs you should see examples of how to build a sql statement on the
fly.  One of these should fit your needs.


Brian



On Wed, Sep 8, 2010 at 3:25 PM, Ellison Alves <[hidden email]> wrote:

> Hi Brian,
> However, your saying that you have a table with the columns checkbox1,
> checkbox2 and checkbox3 that are probably numeric.
>
> No, isn't it.
> I was talking about a interface of system.
> Well, i want that an user can use a filter to query something in DB. (So,
> i've used to checkboxes on system's interface).
> These checkboxes represents a state of entity.
> So i want to build an sql on execution time.
>
> 2010/9/8 Brian Hurley <[hidden email]>
>>
>> I think that you need to look at your design a little more.
>>
>> However, your saying that you have a table with the columns checkbox1,
>> checkbox2 and checkbox3 that are probably numeric.  So just make a
>> select that binds the map/object to those columns as you want.
>>
>> SELECT * FROM table WHERE checkbox1 = #{checkbox1} OR checkbox2 =
>> #{checkbox2} OR checkbox3 = #{checkbox3};
>>
>> What I would probably do (without knowing more about your data) is to
>> use an attribute table design.
>>
>> create table attr_table (
>>   attr_id number(1) not null,
>>   attr_type varchar(30) not null,
>>   attr_value varchar(1024) not null,
>>   attr_date date not null );
>>
>> Then use a select like this..
>>
>> select * from attr_table where attr_value in (...values...) where
>> attr_type = 'myattribute_type';
>>
>> Brian
>>
>> On Wed, Sep 8, 2010 at 12:02 PM, Ellison Alves <[hidden email]>
>> wrote:
>> > Hello guys,
>> >
>> > I want to put different options in sql mapping by a interface where
>> > there are checkboxes.
>> > The final sql statetement would be like the line below:
>> >
>> > SELECT * FROM table WHERE checkbox1 = 0 OR checkbox2 = 1 OR checkbox3
>> > = 3;
>> >
>> > I want to do all mappings into xml. Therefore, I've looked for foreach
>> > but i don't know if it could resolve this question.
>> >
>> > Thanks!
>
>
>
> --
> Ellison Alves de Souza
>

[Guy Rouillier]

On 9/8/2010 1:02 PM, Ellison Alves wrote:

> Hello guys,
>
> I want to put different options in sql mapping by a interface where
> there are checkboxes.
> The final sql statetement would be like the line below:
>
> SELECT * FROM table WHERE checkbox1 = 0 OR checkbox2 = 1 OR checkbox3
> = 3;
>
> I want to do all mappings into xml. Therefore, I've looked for foreach
> but i don't know if it could resolve this question.

Take a look at the Dynamic SQL section of the documentation.  It allows
you to optionally include conditions in your WHERE clause based on the
conditions you specify.

--
Guy Rouillier