how to insert DEFAULT if property value is NULL?

[Paulo Lopes]

I am trying to simplify my insert statements and am passing a map to the insert xml mapper.

There I would like to do if the value of the java object is null then replace with DEFAULT so the database default value is inserted instead of null.

I can do this with a <case><when test="prop == null">DEFALUT</when><otherwise>#{prop}</otherwise></case> but looks to verbose... maybe there is a better way...

from the old ibatis api i think there was a nullValue attribute but from what i read is totally deprecated and gone from mybatis...

[Larry Meadors]

Back in the bad old days, nullValue was actually the opposite of what
you wanted - it mapped null db values to magic Java values.

Your <case> option may be the simplest way.

Larry


PS: I used to use Maps a LOT for parameters and results...I don't
recommend it. It tends to make things very very difficult to refactor.
It's quick to crank out code like this, but maintenance gets very
costly. YMMV. :)

[Guy Rouillier]

The OP didn't mention what DBMS he is using. Oracle, for example, has
an nvl function that will allow you to specify an alternative value if
the passed argument is null. That way you won't have to case each value
column.

--
Guy Rouillier

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[Larry Meadors]

But you won't get the database default that way, you get a default you
specify in the mapper.

If that's what's desired, then putting in a default before calling the
mapper would work as well.

Larry

> --
> 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/d/optout.

[Paulo Lopes]

I am using postgres, and it supports inserting default values like:

CREATE TABLE movies (
    name      varchar(40) DEFAULT 'once upon the time',
    comment   varchar(40)
);

So if i want to insert just a comment for the default movie i can do:
insert into movies (name, comment) values (default, 'boring');

If i want to specify the movie:
insert into movies (name, comment) values ('matrix', 'too much green');

So the idea is to have a generic insert xml and if a value is present use it, otherwise use the keyword DEFAULT

[Paulo Lopes]

On Thursday, May 8, 2014 10:22:22 PM UTC+2, Guy Rouillier wrote:

The OP didn't mention what DBMS he is using.  Oracle, for example, has
an nvl function that will allow you to specify an alternative value if
the passed argument is null.  That way you won't have to case each value
column.

I've tryed the nvl but i can't use default as the value since default is a keyword and the function expects a value...
 

[Jose María Zaragoza]

2014-05-09 9:23 GMT+02:00 Paulo Lopes <pml...@gmail.com>:
>
>
> On Thursday, May 8, 2014 10:22:22 PM UTC+2, Guy Rouillier wrote:
>>
>> The OP didn't mention what DBMS he is using. Oracle, for example, has
>> an nvl function that will allow you to specify an alternative value if
>> the passed argument is null. That way you won't have to case each value
>> column.

Are you try to use a custom typehandler ?
I don't like to use them for business logic but it's an option.
I prefer to format input/output data in domain classes

Regards

>
>
> I've tryed the nvl but i can't use default as the value since default is a
> keyword and the function expects a value...
>
>>
>>
>> On 5/8/2014 3:49 PM, Larry Meadors wrote:
>> > Back in the bad old days, nullValue was actually the opposite of what
>> > you wanted - it mapped null db values to magic Java values.
>> >
>> > Your <case> option may be the simplest way.
>> >
>> > Larry
>> >
>> >
>> > PS: I used to use Maps a LOT for parameters and results...I don't
>> > recommend it. It tends to make things very very difficult to refactor.
>> > It's quick to crank out code like this, but maintenance gets very
>> > costly. YMMV. :)
>> >
>>
>>
>> --
>> Guy Rouillier
>>
>> ---
>> This email is free from viruses and malware because avast! Antivirus
>> protection is active.
>> http://www.avast.com
>>