Add default value when field is null with MyBatis

Question

I want to insert the default value from database when my field is null. I use an Oracle Database.

CREATE TABLE "EMPLOYEE" 
   ("COL1" VARCHAR2(800) NOT NULL ENABLE, 
    "COL2" VARCHAR2(100) DEFAULT NOT NULL 'toto', 
    CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY ("COL1")

with a simple SQL request, we can write:

insert into EMPLOYEE(COL1,COL2) values ('titi', default)

How can i do this with annotations MyBatis in Spring? I must create an HandlerType?

Answer 1

It should work if you omit the COL2 in your colum definition of the insert statement. Because the DB recognizes, that there is no value for the new row and it will apply the default value from the create table statement.

Have you tried something like this?

public interface EmployeeDAO {
   String INSERT = "insert into employee (col1) values (#{COL1})";

   @Insert(INSERT)
   public int insertDefault(PersonDO p) throws Exception;
}

Answer 2

In mapper XML, build dynamically the SQL (add the col2 column and value when not null):

insert into employee (col1<if test="col2 != null">, col2</if>) 
values (#{COL1}<if test="col2 != null">, #{col2}</if>)

EDIT: since value in annotation must be constant, I used to think dynamic SQl was not possible in annotation, but there is a trick I have found here: How to use dynamic SQL query in MyBatis with annotation(how to use selectProvider)? and checked it myself.

To use dynamic SQL this into an annotation, surround it with "script" tags:

@Insert("<script>insert into employee (col1<if test='col2 != null'>, col2</if>) 
    values (#{COL1}<if test='col2 != null'>, #{col2}</if>)</script>")

In tests, just escape double quotes " or replace them with simple quotes '