Building many option in sql mapping

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

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!
Reply | Threaded
Open this post in threaded view
|

Re: Building many option in sql mapping

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!
Reply | Threaded
Open this post in threaded view
|

Re: Building many option in sql mapping

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
Reply | Threaded
Open this post in threaded view
|

Re: Building many option in sql mapping

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
>
Reply | Threaded
Open this post in threaded view
|

Re: Building many option in sql mapping

Guy Rouillier
In reply to this post by Ellison Alves
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